Copying the Hive External table from one database to another database.

Question

Hi Guys,

I got a request to copy one of the large hive external table that has 72883 partitions from one database to another database. I am new to the Hive database so I need your help in copying that table. I have done the following steps so far and not sure about next step.

Do I need to add those 72883 partitions manually or is there another way to add those partitions to the target table.

Your early response is very much appreciated.

Steps:

1. created the external table in new databases with the same definition of the source table,but with different location.

2. Copy the CSV files of source table location to target table location.

Regards

~Suresh D

Shelton · Accepted Answer

@Uppal

Great if all went well we usually run msck repair table daily once you have loaded a new partition in HDFS location.

Why you need to run msck Repair table statement every time after each ingestion?

Hive stores a list of partitions for each table in its metastore. If, however, new partitions are directly added to HDFS , the metastore (and hence Hive) will not be aware of these partitions unless the user runs either of below ways to add the newly add partitions.

msck will add metadata about partitions to the Hive metastore for partitions for which such metadata doesn't already exist
If you will find the need remember to do that else accept the answer and close the thread :

Shelton · Answer

@Uppal

Best way to duplicate a partitioned table in Hive

Create the new target table with the schema from the old table the describe formatted could help with the SQL
Use hadoop fs -cp to copy all the partitions from source to the target table
Run MSCK REPAIR TABLE table_name; on the target table

HTH

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.