CODEWITHSUNDEEP
hadoophive
Saurab
Saurab

Reputation: 2051

Hive partition table by month from daily timestamp?

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.

Upvotes: 1

Views: 2731

Answers (1)

sumitya
sumitya

Reputation: 2691

CAUTION:- You need to escape date column(by using ` i.e. backtick around column name) in create statement. Because date is a datatype in hive.

You can create partitions dynamically:- by setting below parameter in query.

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

Along with that you need to select only month part from source table:-

insert into table sales partition(date) select columns...,SUBSTR(date,5,2) from source_table

This insert statement will create partitions like.

show partitions sales

date=01 date=02 date=03 date=04

Upvotes: 3

Related Questions