Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

Create hive partition by month


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.



@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!