In the previous post, we discussed about HIVE transactional tables; how to create it, properties and configurations required and example of HIVE transactional table DDL with INSERT/UPDATE/DELETE/MERGE statements on HIVE ACID table. In this post, we will talk about limitations with HIVE transactional tables and possible solutions if any.
[Error 10302]: Updating values of bucketing columns is not supported.
ACID table must be bucketed to enable transactional property, you cannot run UPDATE command to set BUCKETED column. In the table DDL, you can see “CLUSTERED BY (pres_bs) INTO 4 BUCKETS”. Now we cannot run UPDATE command with set pres_bs=’any_value’.
Below query is Not Allowed
update usa_prez_tx set pres_bs='NY' where pres_out is NULL;
Solution: Change bucketed column if you really have to run UPDATE on that column.
[Error 10292]: Updating values of partition columns is not supported.
Similar to previous constraint, if you have created partitioned table then you cannot run UPDATE command to set PARTITION column to new value. If it is non-transactional , then anyways you would be re-writing entire partition. So won’t get this error in non-ACID tables.
Solution: Change partitioned column if you really have to run UPDATE on that column.
[Error 10295]: INSERT OVERWRITE not allowed on table with OutputFormat that implements AcidOutputFormat while transaction manager that supports ACID is in use.
You cannot execute INSERT OVERWRITE command if you are using hive ACID tables.
Below query is Not Allowed
INSERT OVERWRITE TABLE usa_prez_tx select tb2.pres_id, tb2.pres_name, tb2.pres_dob, tb2.pres_bp, tb1.state_abbr, tb2.pres_in, tb2.pres_out from usa_prez_tx tb2 left outer join usa_states tb1 on tb2.pres_bs = tb1.state_name;
Solution: Run Truncate & INSERT in place of INSERT OVERWRITE query.
[Error 10265]: This command is not allowed on an ACID table usa_prez_tx with a non-ACID transaction manager. Failed command: null
You cannot use ACID table to load other tables , non-ACID in non-ACID session [hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DummyTxnManager]
Below query is Not Allowed
insert into usa_prez_nontx select * from usa_prez_tx;
usa_prez_nontx is non transactional table
usa_prez_tx is transactional table
Other than that you may encounter LOCKING related issues while working with ACID tables in HIVE. Although HIVE ACID makes life easy for developer and in writing queries but it comes with some limitations and with future versions of HIVE queries will become more stable.
