Changing the partition spec of a hive table and move data

Question

I have a external hive table employee which is partitioned by extract_timestamp (yyyy-mm-dd hh:mm:ss) as below.

empid   empname     extract_time
1       abc         2019-05-17 00:00:00
2       def         2019-05-18 14:21:00

I am trying to remove the partition by extract_time and change it to year,month and day partition. I am following the below method for this.

1. Create a new table employee_new with partitions year month and day

 create external table employee_new
(empid int,
 empname string
)
partitioned by (year int,month int,day int)
location '/user/emp/data/employee_new.txt';

2. insert overwrite into employee_new by selecting data from employee table

insert overwrite into employee_new as select*,year(extract_time),month(extract_time)
,day(extract_time) 
from employee

3. Drop employee and employee_new and create employee table on top of /user/emp/data/employee_new.txt

Please let me know if this method is efficient and if there are any better ways to do the same.

Few questions: What is the meaning of extract_time? What is the usage scenario for partition columns: new and old one? And what is the size of table and total number of records? — leftjoin
Extract time is a timestamp of the source file from which the data is extracted. Each hour there will be a new file. Since the target table is partitioned by timestamp, many partitions are created dynamically resulting in too many small unwanted partitions. We want to partition based on day, month and year so that the number of partitions can be reduced. Each hourly file will have 10-25 records. — SreeVik
Please answer all my questions it will help to built optimal partitioning. Usage scenario for partition columns? and zize. Right now it seems that you do not need to partition by year, month, day. Why do you want to partition by these columns. Is data intersected in initial partitions? — leftjoin
scenario: We want to retrieve data based on a day year and month. Columns : 30-40. Size : Table has around 1 million records. It is growing daily as we get 24 files daily. Is data intersected in initial partitions? : Yes it is. Every hour was a new partition earlier and this is creating too many small partitions. However data is being retrieved from the table based on a day or month or year and not based on the hour. So queries trying to retrieve data from this table for a day runs for a long time. — SreeVik
Are you going to redesign upstream process to write hourly in the daily table folders, right? and the data s intersecting, not unique in the source files because there are updates on the existing data, right? — leftjoin

leftjoin leftjoin · Accepted Answer · 2019-05-18T20:17:45

Partition by date yyyy-MM-dd only, if possible, if upstream process can write hour files to daily folders. For such a small table partitioning by year, month and day separately seems overkill. It will be still too many folders. If table is partitioned by date yyyy-MM-dd, partition pruning will work for your usage scenario because you are querying by day or year or month.

To filter by year in this case you will provide

where date >= '2019-01-01' and date < '2020-01-01' condition,

to filter by month:

where date >= '2019-01-01' and date < '2020-02-01'

and day: where date = '2019-01-01'

Filesystem listing will work much faster.

And if it is not possible to redesign upstream process to write to yyyy-MM-dd folders then your new design as you described in the question (yyyy/MM/dd folders) is the only solution.

Changing the partition spec of a hive table and move data

1 Answers