Hive- how do I "create table as select.." with partitions from original table?

Question

I need to create a "work table" from our hive dlk. While I can use:

create table my_table as
select *
from dlk.big_table

just fine, I have problem with carrying over partitions (attributes day, month and year) from original "big_table" or just creating new ones from these attributes. Searching the web did not really helped me answer this question- all "tutorials" or solutions deal either with create as select OR creating partitions, never both. Can anybody here please help?

Answer 1

Creating partitioned table as select is not supported. You can do it in two steps:

1. create table my_table like dlk.big_table; This will create table with the same schema.

2. Load data.

   set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict;

   insert overwrite table my_table partition (day, month, year) select * from dlk.big_table;