Exceptions When Delete rows from Hive Table

June 25, 2016 Author: david

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

hive> delete from imps_part;

1	hive> delete from imps_part;

Some one suggest to use the following command:

hive> INSERT OVERWRITE TABLE imps_part
 > SELECT * FROM imps_part
 > WHERE id = 1;

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

hive> INSERT OVERWRITE TABLE imps_part PARTITION(date, country)
 > SELECT id, user_id, user_lang, user_device, time_stamp, url
 > FROM imps_part;

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:

hive> INSERT OVERWRITE TABLE imps_part PARTITION(date, country = 'US')
 > SELECT id, user_id, user_lang, user_device, time_stamp, url
 > FROM imps_part;

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:

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.

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.