Support Questions

Find answers, ask questions, and share your expertise

Create hive partition by month

avatar
Contributor

Is it possible to create partition like 01 from date like 2017-01-02' where 01 is month ? I have daily sales record and I need to do query like select * from sales where month = '01'. So it will be better if I could partition my daily sales by month.but my data has date of format 2017-01-01 and doing create table tl (columns ......) partitioned by (date <datatype> ) will create partition on daily basis which is the last thing I want . I need to create partition dynamically.

1 REPLY 1

avatar

@Saurab Dahal Yes its achievable. But there are few tweeks which has to be done. Partitioned table should be created with additional field("month") along with sale_date. Create the hive table with month as partitioned column. When inserting into the table, extract only the month from sales_date and pass it to insert statement.

Insert into table table_name partitioned(month) select col1,col2,MONTH(sales_date),sale_date from source_table;

Above command should work. Make sure the below property is enabled.

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict
Hope It helps!