CODEWITHSUNDEEP
hadoophive
yanachen
yanachen

Reputation: 3753

How can insert into the table with the original day as partition in Hive?

create  table h5_qti_desc
( h5id string,
  query string,
  title string,
  item string,
  query_ids string,
  title_ids string,
  item_ids string,
  label bigint
)PARTITIONED BY (day string)  LIFECYCLE 160;

insert overwrite into h5_qti_desc
select * from aaa
;

I create a table named h5_qti_desc, and I want to insert into it from another aaa table, which has the field of day and there is no partition in aaa. Table aaa has several days, like '20171010','20171015'...
How can I insert into h5_qti_desc with day as partition once, and the days in aaa acted as day in h5_qti_desc's partition.

Upvotes: 0

Views: 224

Answers (1)

Sandeep Singh
Sandeep Singh

Reputation: 7990

You can use Hive dynamic partition functionality to insert data. Dynamic-partition insert (or multi-partition insert) is designed to solve this problem by dynamically determining which partitions should be created and populated while scanning the input table.

Below is an example of loading data to all partitions using one insert statement:

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

hive>INSERT OVERWRITE TABLE h5_qti_desc PARTITION(day)
             SELECT * FROM aaa
             DISTRIBUTE day;

Upvotes: 2

Related Questions