Exceptions When Delete rows from Hive Table
It’s straight forward to delete data from a traditional Relational table using SQL. However, delete rows from Hive Rows can cause several exceptions.
For examples, let see we have a imps_part table, we want to delete the values in the Table. You will get the exception:
When you run the simple delete command, we get: FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations
1 |
hive> delete from imps_part; |
Some one suggest to use the following command:
1 2 3 |
hive> INSERT OVERWRITE TABLE imps_part > SELECT * FROM imps_part > WHERE id = 1; |
This will result in the following exception:
FAILED: SemanticException 1:23 Need to specify partition columns because the destination table is partitioned. Error encountered near token ‘imps_part’
After run the following commands, we get: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict
1 2 3 |
hive> INSERT OVERWRITE TABLE imps_part PARTITION(date, country) > SELECT id, user_id, user_lang, user_device, time_stamp, url > FROM imps_part; |
After set the value for country:
1 2 3 |
hive> INSERT OVERWRITE TABLE imps_part PARTITION(date, country = 'US') > SELECT id, user_id, user_lang, user_device, time_stamp, url > FROM imps_part; |
We got: FAILED: SemanticException Line 0:-1 Dynamic partition cannot be the parent of a static partition ”US”
The correct query to delete all the rows from a partitioned Hive Table.
TRUNCATE table <table_name>
How to delete some rows from hive Table:
1 |
The best approach is to partition your data such that the rows you want to drop are in a partition themselves. You can then drop the partition without impacting the rest of your table. This is a fairly sustainable model, even if your dataset grows quite large. |
Reference:
http://stackoverflow.com/questions/24211372/loading-data-from-one-hive-table-to-another-with-partitionhttp://unmeshasreeveni.blogspot.com/2014/11/updatedeleteinsert-in-hive-0140.html