Skip to content
Advertisement

Setting transactional-table properties results in external table

I am creating a managed table via Impala as follows:

CREATE TABLE IF NOT EXISTS table_name
    STORED AS parquet
    TBLPROPERTIES ('transactional'='false', 'insert_only'='false')
    AS ...

This should result in a managed table which does not support HIVE-ACID. However, when I run the command I still end up with an external table. Why is this?

Advertisement

Answer

I found out in the Cloudera documentation that neglecting the EXTERNAL-keyword when creating the table does not mean that the table definetly will be managed:

When you use EXTERNAL keyword in the CREATE TABLE statement, HMS stores the table as an external table. When you omit the EXTERNAL keyword and create a managed table, or ingest a managed table, HMS might translate the table into an external table or the table creation can fail, depending on the table properties.

Thus, setting transactional=false and insert_only=false leads to an External Table in the interpretation of the Hive Metastore.

Interestingly, only setting TBLPROPERTIES ('transactional'='false') is completly ignored and will still result in a managed table having transactional=true).

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement