Configuring legacy CREATE TABLE behavior after upgrading from CDH

After you upgrade to CDP Private Cloud Base and migrate old tables, the legacy CREATE TABLE behavior of Hive is enabled. Legacy behavior might solve compatibility problems with your scripts during data migration, for example, when running ETL. Learn how to configure the legacy CREATE TABLE behavior to create ACID transactional tables by default.

Because the legacy behavior is enabled by default after the upgrade, running a CREATE TABLE statement creates an external table in your specified warehouse, which is /warehouse/tablespace/external/hive. You can create managed Apache Hive 3 tables by either configuring the legacy behavior or by overriding the default behavior using the MANAGED keyword:
CREATE MANAGED TABLE test (id INT);

You can configure legacy CREATE TABLE behavior at the site level by configuring properties in Cloudera Manager. When configured at the site level, the behavior persists from session to session.

  1. In Cloudera Manager, click Clusters and select the Hive on Tez service.
  2. From the Hive on Tez service, go to the Configuration tab and search for hive.create.

    Configuring Hive CREATE TABLE behavior
  3. If the following properties are unchecked, select the properties to disable the legacy CREATE TABLE behavior.
    • Default Table Format - Create Tables as ACID Insert Only (hive.create.as.insert.only)
    • Default Table Format - Create Tables as Full ACID (hive.create.as.acid)
    • If you only select hive.create.as.insert.only then all tables created are insert-only ACID by default, regardless of file format.
    • If you only select hive.create.as.acid then tables created with ORC format are full ACID by default and all other tables are external tables with purge (external.table.purge = 'true') by default.
Legacy behavior is configured to allow the CREATE TABLE statement to create either an insert-only or full ACID table, depending on the file format.