How to insert from a select query with dynamic partitioning on a column in Hive?
You should specify explicitly all the column names you are inserting into. For example, your command should be something like this:
INSERT OVERWRITE TABLE exampledb.exampletablePARTITION(my_part)(key_1, key_2, col_1, col_2, my_part)SELECT key_1, key_2, col_1, col_2, SUBSTR(key_2, -3)FROM exampledb.exampletable_temp;This should work.
UPDATE
I tried to create a test case, and INSERT OVERWRITE doesn't seem to work, but INSERT INTO is working. A workaround could be to delete all data from the destination table with TRUNCATE TABLE exampledb.exampletable, or delete all data from a specific partition with TRUNCATE TABLE test6 PARTITION (my_part = '001');, then run an INSERT INTO:
INSERT INTO exampledb.exampletablePARTITION(my_part)(key_1, key_2, col_1, col_2, my_part)SELECT key_1, key_2, col_1, col_2, SUBSTR(key_2, -3)FROM exampledb.exampletable_temp;