How to dynamically partition the table based on week by using date in Hive

Question

There is a "Results" table which contains Id and Date columns.

create table Results(Id int, Date String)

row format delimited fields terminated by ','

stored as textfile;

Id Date

11 2012-04-06

12 2012-05-08

13 2013-02-10

14 2013-05-06

15 2013-08-22

16 2014-04-01

17 2014-05-06

18 2014-06-03

19 2014-07-24

20 2014-08-26

How to store the above data into "Historical" table by dynamically partitioning based on year and week no from the above date column.

In the Historical table it should contain partitions based on year & week, output must be

Historical partition

2012 partition contains 2 partitions

2013 partition contains 3 partitions

2014 partition contains 5 partitions

Answer 1

as you want to do dynamic partition we need to do this

-- Set following two properties for your Hive session:

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstric;

-- Create an Historical table with partition as below

hive> create table Historical (Id int, Date String) partitioned by (year_part string, week_no int) row format delimited fields terminated by ',';

--Load the Data into Historical table and insert from Results table such that data is partitioned in Historical table depending upon the year from date and dynamically found week number based on the date in Results table. --Do make sure that the column on which you want to partition should come last in select statements. If there are series of column then there order in partition(col3,col4) should match in select statement.

hive> insert overwrite table Historical partition(year_part, week_no) select id, date, year(date) as year_part, WEEKOFYEAR(date) as week_no from Results;

-- Now verify the partition created properly and data populated is correct too or not.