I need to make backup from partitioned table (Hive)-CodePudding

I need to make backup data from partitioned table which has over 500 partitions. My table has partitioning by date_part like "date_part = 20221101" or "date_part = 20221102" etc. I need to take 30 partitions from 20221101 to 20221130 and make copy to another new backup-table.

If I do something like this:

create table <backup_table> as
select * from <data_table> where date_part between 20221101 and 20221130

at the output I get non-partitioned <backup_table> and idk is it good way or not but i guess partitioned <backup_table> will be more better.

If I try to do:

create table <bacup_table> like <data_table>;
insert overwrite table <backup_table> partition (`date_part`)
select * from <data_table> where date_part between 20221101 and 20221130;

At the output I get error like need to specify partition columns...

If I go another way:

create table <bacup_table> like <data_table>;
insert overwrite table <backup_table> partition (`date_part`)
select field1, field2...,
date_part
from <data_table> where date_part between 20221101 and 20221130;

I get another errors like "error running query" or "...nonstrick mode..." or something else. I've tried a lot of hive settings but it still not work :(

Thats why I need your help to do it correctly.

CodePudding user response：

enable dynamic partition and copy the data.

SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
SET hive.mapred.mode = nonstrict;