Hive - Huge 10TB table repartitioning (Adding new partition columns)

Question

Techies,

Background - We have 10TB existing hive table which has been range partitioned on column A. Business case has changes which now require adding of partition column B in addition to Column A.

Problem statement - Since data on HDFS is too huge and needs to be restructured to inherit the new partition column B, we are facing difficulty to copy over table onto backup and re-ingest using simple IMPALA INSERT OVERWRITE into main table.

We want to explore if there is/ are efficient way to handle adding over partition columns to such huge table

Gomz Gomz · Accepted Answer · 2019-04-06T12:17:20

Alright!

If I understand your situation correctly, you have a table backed by 10 TB of data in HDFS with partition on Column A and you want to add the partition also on Column B.

So, if Column B is going to be the sub partition, the HDFS directory would look like user/hive/warehouse/database/table/colA/colB or /colB/colA otherwise (considering it as an managed table).

Restructuring the HDFS directory manually won't be a great idea because it will become a nightmare to scan the data on all files and organize it accordingly in its corresponding folder.

Below is one way of doing it,

1. Create a new table with new structure - i.e., with partitions on Col A and Col B.

CREATE TABLE NEWTABLE ( COLUMNS ... ) PARTITON ON ( COL_A INT, COL_B INT )

2.a. Insert data from the old table to the new table (created in Step #1) like below,

INSERT INTO NEWTABLE 
SELECT * FROM OLDTABLE

But Yes, this step is going to consume a lot of resources during execution if not handled properly, space in HDFS for storing the results as data for NEWTABLE and of-course the time.

OR

2.b. If you think that HDFS will not have enough space to hold all the data or resource crunch, I would suggest you to this INSERT in batches with removal of old data after each INSERT operations.

INSERT INTO NEWTABLE 
SELECT * FROM OLDTABLE
WHERE COL_A='abc'

DELETE FROM OLDTABLE
WHERE COL_A='abc'

INSERT INTO NEWTABLE 
SELECT * FROM OLDTABLE
WHERE COL_A='def'

DELETE FROM OLDTABLE
WHERE COL_A='def'

.
.
.
so on.

This way, you can unload HDFS with already handled data and balancing the space.

If you follow step 2.b. then you can write a Script to automate this process by passing the partition names (derived from SHOW PARTITIONS) dynamically for each run. But, try the first two attempts manually before going with automation to make sure things go as expected.

Let me know if it helps!

Hive - Huge 10TB table repartitioning (Adding new partition columns)

1 Answers