Search code examples
sqlhadoophiveparquetimpala

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?

Solution

  • 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).