How to create partitions (year,month,day) in hive from date column which have MM/dd/yyyy format

Question

Data loaded on a daily basis. Need to create a partition with the date column.

Date
3/15/2021 8:02:32 AM
12/21/2020 12:20:41 PM

Answer 1

You need to convert the table into a partition to the table. Then change the loading sql so that it inserts into the table properly.

1. Create a new table identical to original table and make sure the exclude partition column from list of columns and add it in partitioned by like below.

create table new_tab() partitioned by ( partition_dt string );

2. Load data into new_tab from original table. Make sure last column in your select clause is the partitioned col.

set hive.exec.dynamic.partition.mode=nonstrict;
insert into new_table partition(partition_dt ) 
select src.*, from_unixtime(unix_timestamp(dttm_column),'MM/dd/yyyy') as partition_dt from original_table src;

3. Drop original table and rename new_table as original table.

drop table original_table ;
alter table new_table rename to original_table ;