Dynamic partitioning with dervied column

Question

I am trying to insert data into a Hive table through Dynamic partitioning the table is

CREATE EXTERNAL TABLE target_tbl_wth_partition( booking_id string, code string, txn_date timestamp, logger string, ) partition by (txn_date date,txn_hour int)

Values

txn_date=20160216 txn_hour=12

CREATE EXTERNAL TABLE stg_target_tbl_wth_partition( booking_id string, code string, txn_date timestamp, logger string, )

insert overwrite table target_tbl_wth_partition partition(txn_date,hour(txn_date)) select booking_id,code,txn_date,logger from stg_target_tbl_wth_partition;

I am not able to insert with derived columns in Dynamic partition. Any help on how to proceed with such case will be helpful.

Regards, Rakesh

I suggest you read a few papers on how Hive stores partitioned data, what is the difference between external and managed Hive tables, how you write INSERT queries for partitioned tables, and the expected format for date literals. But I guess you won't do that, anyway. — Samson Scharfrichter

Samson Scharfrichter Samson Scharfrichter · Accepted Answer · 2016-02-18T16:56:37

I suggest you start from something like that...

CREATE TABLE blahblah (...)
PARTITIONED BY (aaa STRING, bbb STRING)
;
SET hive.exec.dynamic.partition = true
;
SET hive.exec.dynamic.partition.mode = nonstrict
;
INSERT INTO TABLE blahblah PARTITION (aaa, bbb)
SELECT ...,
  SUBSTRING(aaabbb,1,5) as aaa,
  SUBSTRING(aaabbb,7,2) as bbb
FROM sthg
;

...and make it work; then you can start experimenting some weird and unsupported syntax and see what works and what does not.

Dynamic partitioning with dervied column

1 Answers