Configuring legacy CREATE TABLE behavior
After you upgrade to CDP and migrate old tables, you might want to briefly switch to Hive legacy behavior. Legacy behavior might solve compatibility problems with your scripts during data migration, for example, when running ETL.
By default, executing a CREATE TABLE statement creates a managed Apache Hive 3 table in the Hive metastore. You can change the default behavior to use the legacy CREATE TABLE behavior. When you configure legacy behavior, CREATE TABLE generates external tables. Legacy behavior is recommended only during upgrading due to the advantages of full ACID transactional tables over external tables.
Using ACID-compliant, transactional tables causes no performance or operational overload. Bucketing is not necessary.
If you are a Spark user, switching to legacy behavior is unnecessary. Calling ‘create table’ from SparkSQL, for example, creates an external table after upgrading to CDP as it did before the upgrade.
Configure legacy CREATE TABLE behavior
When you configure legacy behavior, CREATE TABLE
creates an external table in your specified warehouse, which is
/warehouse/tablespace/external/hive by default.
To configure legacy behavior at the session level, you can pass a property to HiveServer (HS2) in
the Beeline connection string when you launch Hive. Alternatively, you can pass the
property on the Hive command line to switch to the old behavior. You can also
configure legacy create table behavior at the site level by configuring properties
in Cloudera Manager. When configured at the site level, legacy behavior persists
from session to session.
Choose one of the following ways to configure legacy CREATE TABLE
You can purge from the table from the file system and metastore. You can change the DROP behavior, to remove metadata only.
To configure legacy behavior in any JDBC client, include
hiveCreateAsExternalLegacy=truein the connection string. For example, in Beeline, include the connection string to launch Hive:
beeline -u jdbc:hive2://10.65.13.98:10000/default;hiveCreateAsExternalLegacy=true \ -n <your user name> -p
To configure legacy behavior within an existing beeline session, set
hive.create.as.external.legacy=true. For example:
hive> SET hive.create.as.external.legacy=true;
Override the configured legacy behavior at the session level (only) to
create a managed table by using the MANAGED keyword.
CREATE MANAGED TABLE test (id INT);When your session ends, the create legacy behavior also ends. If you issue a CREATE TABLE statement, Hive creates either an insert-only or full ACID table, depending on how you set the following table properties:
- In Cloudera Manager > Clusters > Hive On Tez, search for hive.create.
Configure properties in one of the following ways:
- If Create Tables as ACID Insert Only and Create Tables as Full ACID properties appear and are checked, uncheck the properties.
- If your version of Cloudera Manager does not expose these
properties, in the HiveServer2 Advanced Configuration Snippet Safety
Value for hive-site.xml, add the properties and values.
<property> <name>hive.create.as.insert.only</name> <value>false</value> </property> <property> <name>hive.create.as.acid</name> <value>false</value> </property>