How can I disable transactions for a Hive table?

user_233196 0
Viewed 3829

I have a Hive table that was originally created as transactional, but I want to disable transactions on the table because they are not actually needed.

I tried to disable them using ALTER TABLE, but I got an error:

hive> ALTER TABLE foo SET TBLPROPERTIES('transactional'='false');
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. TBLPROPERTIES with 'transactional'='true' cannot be unset

I am using Hive 2.3.2

hiveddl hive
2 Answers

According to the documentation changing TBLPROPERTIES ("transactional"="false") is not allowed.

You can re-create the table.

Do table backup first:

create table bkp_table as 
select * from your_table;

Then drop table and create again without transactional property. Reload data from backup.

Or make a new table, load data from old one, delete old, rename new.

user_27003440

You have to re-create the table.

First backup table if you want. then, DROP TABLE

Create Table with TBLPROPERTIES ( 'transactional'='false' )

CREATE TABLE your_table(
  `col` string, 
  `col2` string)
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'
TBLPROPERTIES ( 
  'transactional'='false' 
)

You can Choose Input and Output format

user_79077820
Related