526互联

Hive事务表转换为非事务表

发布时间 2024-01-02 15:01:51作者: dogplay

环境：hive3.1.0

由于建表时默认会建为非事务表
CREATE TABLE bucket_text_table2(column1 string,column2 string,column3 int) CLUSTERED BY (column3) into 5 BUCKETS STORED AS TEXTFILE;
执行完成后，查看默认建表语句：

+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `bucket_text_table2`(                 |
|   `column1` string,                                |
|   `column2` string,                                |
|   `column3` int)                                   |
| CLUSTERED BY (                                     |
|   column3)                                         |
| INTO 5 BUCKETS                                     |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION                                           |
|   'hdfs://ambari-hadoop1:8020/warehouse/tablespace/managed/hive/test.db/bucket_text_table2' |
| TBLPROPERTIES (                                    |
|   'bucketing_version'='2',                         |
|   'transactional'='true',                          |
|   'transactional_properties'='insert_only',        |
|   'transient_lastDdlTime'='1704166322')            |
+----------------------------------------------------+

其中表的transactional被设置为true，代表表为事务表
尝试通过alter table set properties来进行修改

0: jdbc:hive2://ambari-hadoop1:2181,ambari-ha> alter table bucket_text_table2 set tblproperties('transactional'='false');
INFO  : Compiling command(queryId=hive_20240102143335_121e893a-fb40-4853-876b-c6da7edf0ddd): alter table bucket_text_table2 set tblproperties('transactional'='false')
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO  : Completed compiling command(queryId=hive_20240102143335_121e893a-fb40-4853-876b-c6da7edf0ddd); Time taken: 0.04 seconds
INFO  : Executing command(queryId=hive_20240102143335_121e893a-fb40-4853-876b-c6da7edf0ddd): alter table bucket_text_table2 set tblproperties('transactional'='false')
INFO  : Starting task [Stage-0:DDL] in serial mode
ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Cannot change 'transactional' without 'transactional_properties'
INFO  : Completed executing command(queryId=hive_20240102143335_121e893a-fb40-4853-876b-c6da7edf0ddd); Time taken: 0.033 seconds
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Cannot change 'transactional' without 'transactional_properties' (state=08S01,code=1)

修改结果ERROR，看来需要同时修改transactional_properties属性值，重新调整

0: jdbc:hive2://ambari-hadoop1:2181,ambari-ha> alter table bucket_text_table2 set tblproperties('transactional'='false','transactional_properties'='{}');
INFO  : Compiling command(queryId=hive_20240102143717_f10054c2-f69f-4d53-aa61-86fe98edaf64): alter table bucket_text_table2 set tblproperties('transactional'='false','transactional_properties'='{}')
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO  : Completed compiling command(queryId=hive_20240102143717_f10054c2-f69f-4d53-aa61-86fe98edaf64); Time taken: 0.024 seconds
INFO  : Executing command(queryId=hive_20240102143717_f10054c2-f69f-4d53-aa61-86fe98edaf64): alter table bucket_text_table2 set tblproperties('transactional'='false','transactional_properties'='{}')
INFO  : Starting task [Stage-0:DDL] in serial mode
ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Cannot convert an ACID table to non-ACID
INFO  : Completed executing command(queryId=hive_20240102143717_f10054c2-f69f-4d53-aa61-86fe98edaf64); Time taken: 0.022 seconds
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Cannot convert an ACID table to non-ACID (state=08S01,code=1)

Cannot convert an ACID table to non-ACID很明显，不支持将事务表改变为非事务表。
我在以上表转换时，表中是有数据的，那么如果是空表是否还支持事务表转换至非事务表呢？测试后发现也不行，那么如果要将事务表转为非事务表怎么办呢？

  1. 创建一个新的非事务表。
  2. 将数据从原事务表导入到新的非事务表。
  3. 如果需要，你可以删除原事务表。