Search code examples
apache-sparkhivecreate-tabledatabase-partitioninghive-partitions

Hive - Copy database schema with partitions and recreate in another hive instance

I have copied the data and folder structure for a database with partitioned hive tables from one HDFS instance to another.

How can I do the same with the hive metadata? I need the new HDFS instance's hive to have this database and its tables defined using their existing partitioning just like it is in the original location. And, of course, they need to maintain their original schemas in general with the hdfs external table locations being updated.

Happy to use direct hive commands, spark, or any general CLI utilities that are open source and readily available. I don't have an actual hadoop cluster (this is cloud storage), so please avoid answers that depend on map reduce/etc (like Sqoop).

Solution

  • Use Hive command:

    SHOW CREATE TABLE tablename;

    This will print create table sentence. Copy and change table type to external, location, schema, column names if necessary, etc and execute.

    After you created the table, use this command to create partitions metadata

     MSCK [REPAIR] TABLE tablename;

    The equivalent command on Amazon Elastic MapReduce (EMR)'s version of Hive is:

    ALTER TABLE tablename RECOVER PARTITIONS;

    This will add Hive partitions metadata. See manual here: RECOVER PARTITIONS