[Hive] Error Table is marked as a managed table but is not transactional

Spark3로 managed table create하는데 아래와 같은 에러 발생

 

spark-sql> CREATE TABLE spark_catalog.db1_test.example_hive1 (          >     id INT,          >     name STRING,          >     age INT          > )          > STORED AS parquet;

 

 

• Error

Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Table db1_test.example_hive1 failed strict managed table checks due to the following reason: Table is marked as a managed table but is not transactional.)
        at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:939)
        at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:944)
        at org.apache.spark.sql.hive.client.Shim_v0_12.createTable(HiveShim.scala:614)
        at org.apache.spark.sql.hive.client.HiveClientImpl.$anonfun$createTable$1(HiveClientImpl.scala:565)
        at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
        at org.apache.spark.sql.hive.client.HiveClientImpl.$anonfun$withHiveState$1(HiveClientImpl.scala:298)
        at org.apache.spark.sql.hive.client.HiveClientImpl.liftedTree1$1(HiveClientImpl.scala:229)
        at org.apache.spark.sql.hive.client.HiveClientImpl.retryLocked(HiveClientImpl.scala:228)
        at org.apache.spark.sql.hive.client.HiveClientImpl.withHiveState(HiveClientImpl.scala:278)
        at org.apache.spark.sql.hive.client.HiveClientImpl.createTable(HiveClientImpl.scala:563)
        at org.apache.spark.sql.hive.HiveExternalCatalog.$anonfun$createTable$1(HiveExternalCatalog.scala:286)
        at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
        at org.apache.spark.sql.hive.HiveExternalCatalog.withClient(HiveExternalCatalog.scala:101)
        ... 60 more

 

확인해보니 hive.strict.managed.tables 이 옵션값이 true일 경우 

managed table만들 때 Transactional property가 무조건 포함되어야 한다고 한다.

 

하지만 이 옵션이 true이면 spark, trino 등으로 hive를 사용할 때 여러 제약사항이 존재하기 때문에 아래처럼 fales로 설정한다.

 

옵션값 설명

 

1. Strict Checks:
   • When hive.strict.managed.tables is set to true, Hive enforces strict checks on managed tables:
     • Transactional Requirement: Any managed table must be transactional if you intend to perform operations such as updates or deletes. This means that if you attempt to create a managed table without enabling transactional features, you will receive an error (as seen in your previous query).
     • Prevention of Unintentional Data Loss: This configuration prevents modifications that could inadvertently lead to data loss.
2. Relaxed Checks:
   • When hive.strict.managed.tables is set to false, Hive relaxes these constraints:
     • Managed tables can be created with the default settings without enforcing the need for transactional properties.
     • This might lead to scenarios where non-transactional tables are handled in ways that could cause confusion or unintentional data manipulation.