Configuring legacy CREATE TABLE behavior

After you upgrade to CDP Private Cloud Base and migrate old tables, the legacy CREATE TABLE behavior of Hive is no longer available by default and you might want to switch to the legacy behavior. Legacy behavior might solve compatibility problems with your scripts during data migration, for example, when running ETL.

In CDP, running a CREATE TABLE statement by default creates a full ACID table for ORC file format and insert-only ACID table for other file formats. You can change the default behavior to use the legacy CREATE TABLE behavior. When you configure legacy behavior, CREATE TABLE creates external tables with the purge functionality enabled (external.table.purge = 'true'). Therefore, when the table is dropped, data is also deleted from the file system.

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 selected, clear the selection to enable 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)
Legacy behavior is enabled and the CREATE TABLE statement now creates external tables with the external.table.purge table property set to true.