Using the filters, select the configuration to see the resulting table properties.

Database Property Filters

CREATE DATABASE xyz WITH DBPROPERTIES ('property'='value')
ALTER DATABASE xyz SET DBPROPERTIES ('property'='value')
Database Property Value
defaultTableType
EXTERNAL_TABLES_ONLY

Session Filters

SET property=value;
Session Properties Value
Legacy Create Behavior
'hive.create.as.external.legacy'
Transactional Insert Only
'hive.create.as.insert.only'
Transactional Tables
'hive.create.as.acid'

Assumptions

Resulting Table Properties

Submitted Create Create(d) Result
Create Statement External Storage Type External Storage Type Transactional Acid Type Purge Flag Metastore Translated Error


Definitions

Property Description Notes
External/Transactional When the table is 'External', all data will live in the location define in the metastore property hive.metastore.warehouse.external.dir unless the table is created with a LOCATION element.
A Transactional table a Hive ACID table. The table property transactional=true has been set and the table data will reside in the metastores property hive.metastore.warehouse.dir directory. 		A LOCATION element of a table 'create' statement can only be used for "External" tables. If you wish to use an alternate location for 'Transactional' tables, set the MANAGEDLOCATION element at the database level.
Storage Type Identifies to table storage serde. If the storage type is NOT declared during the 'CREATE', defaults are used in various scenarios.
Acid Type For ACID tables, this table property transactional_properties identifies the type of ACID support There are two types:
  • default - Full ACID support. INSERT, UPDATE, DELETE support. Only available for ORC file formats.
  • insert_only - Insert Only ACID support. INSERT and Append operations supported. Available for ALL file formats.
Purge Flag For "External" tables, when the table property external.table.purge is true the table/partitions data will be deleted when the table/partition(s) are dropped.
Metastore Translated If the table property TRANSLATED_TO_EXTERNAL is 'TRUE', the metastore converted the table from an 'External' table to a 'Manaaged' table before storing the schema.
Error Under some conditions, the properties conflict and won't allow the table to be created.