How to partition a table by month and day in hive

Question

I have created a table with:

CREATE EXTERNAL TABLE extab (
vendorID string, 
orderID string , 
ordertime string
) 
location '/common_folder/data'

Then I created a partition by month and day

CREATE EXTERNAL TABLE part_extab(
endorID string, 
orderID string , 
ordertime string
) 
PARTITIONED by (month string, day string)
location '/common_folder/data'

Then insert data into the partitioned table

INSERT OVERWRITE TABLE 
select vendorId, orderId, ordertime , month, day
FROM extab

HOW do I extract month , day from ordertime ??

leftjoin leftjoin · Accepted Answer · 2018-09-01T15:18:42

Use dynamic partition load. If your date in correct format, month() and day() functions will work:

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

INSERT OVERWRITE TABLE part_extab partiion (month, day)
select vendorId, orderId, ordertime , 
       lpad(month(ordertime),2,0) as month,  
       lpad(day(ordertime),2,0) as day
FROM extab;

Alternatively you can use substr() for extract month and day, like in this answer

How to partition a table by month and day in hive

1 Answers