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

1.3k Views Asked by At

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
1

There are 1 best solutions below

0
Koushik Roy On

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 );
  1. 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;
  1. Drop original table and rename new_table as original table.
drop table original_table ;
alter table new_table rename to original_table ;