Hive事务表转换为非事务表
环境：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很明显，不支持将事务表改变为非事务表。
我在以上表转换时，表中是有数据的，那么如果是空表是否还支持事务表转换至非事务表呢？测试后发现也不行，那么如果要将事务表转为非事务表怎么办呢？
- 创建一个新的非事务表。
- 将数据从原事务表导入到新的非事务表。
- 如果需要，你可以删除原事务表。
