How to quickly copy a partitioned table (including data) in Hive

Transferred from: http://lxw1234.com/archives/2015/09/484.htm

Keywords: Hive replication table

There are times when the need to replicate tables is encountered in hive, which refers to duplicating table structures and data.

If it is for a non-partitioned table, it is easy to use the CREATE TABLE new_table as SELECT * from old_table;

So what if it's a partitioned table?

The first way to think of it might be:

First create a new_table with the same old_table structure, including the partition; You can use the CREATE table new_table like old_table;

Next, use dynamic partitioning to insert the old_table data into the new_table.

This method is certainly possible, but it may not be the quickest.

Can actually do this:

1. CREATE TABLE new_table like old_table;

2. Use the Hadoop FS-CP command to copy the folder of the old_table corresponding HDFs directory to the new_table corresponding HDFs directory;

3. Use msck REPAIR table new_table; fix partition metadata for new table;

See Example:

There is a partition table T1, only two partitions, each of which has one data, as follows:

2. Hive> show Partitions t1;
4. Ok
6. PT=----
8. PT=----
10. Time taken: 0.11 seconds, fetched: 2 row(s)
12. Hive> desc t1;
14. Ok
16. ID String
18. PT String
22. # Partition Information
24. # col_name Data_type Comment
28. PT String
30. Time taken: 0.123 seconds, fetched: 7 Row(s)
32. Hive> Select * from t1;
34. Ok
36. X -one- One
38. Y ----
40. Time taken: 0.095 seconds, fetched: 2 row(s )
42. Hive>

Create a new table with the same table structure T2;

2. Hive> CREATE table t2 like T1;
4. Ok
6. Time taken: 0.162 seconds
8. Hive> desc T2;
10. Ok
12. ID String
14. PT String
18. # Partition Information
20. # col_name Data_type Comment
24. PT String
26. Time taken: 0.139 seconds, fetched: 7 Row(s)
28. Hive> show Partitions t2;
30. Ok
32. Time taken: 0.082 seconds

Use the Hadoop fs-cp command to copy all the folders T1 corresponding to the HDFs directory to the T2 corresponding HDFs directory:

2. [[email protected] ~]$ Hadoop FS -CP /hivedata/warehouse/ Liuxiaowen. DB/T1/*/hivedata/warehouse/liuxiaowen.db/t2/
4. [Email protected] ~]$ Hadoop fs-ls/hivedata/warehouse/liuxiaowen.db/t2/
6. Found 2 Items
8. Drwxr-xr-x-Liuxiaowen Liuxiaowen 0 2015-09-11 17:17/hivedata/warehouse/liuxiaowen.db/t2/pt=2015-09-11
10. Drwxr-xr-x-Liuxiaowen Liuxiaowen 0 2015-09-11 17:17/hivedata/warehouse/liuxiaowen.db/t2/pt=2015-09-12

Use msck REPAIR table T2 in hive to fix partition metadata for new table T2;

2. Hive> show Partitions t2;
4. Ok
6. Time taken: 0.082 seconds
8. Hive> msck REPAIR TABLE T2;
10. Ok
12. Partitions not in metastore: T2:pt=--- One T2:pt=----
14. Repair: Added partition to Metastore T2:pt=- ---
16. Repair: Added partition to Metastore T2:pt=12
18. Time taken: 0.249 seconds, fetched: 3 row(s)
20. Hive> show Partitions t2;
22. Ok
24. PT=----
26. PT=----
28. Time taken: 0.068 seconds, fetched: 2 row(s )
30. Hive> Select * from T2;
32. Ok
34. X -one- One
36. Y ----
38. Time taken: 0.123 seconds, fetched: 2 row(s )
40. Hive>

OK, the new table T2 has been copied, it and T1 have the same table structure, partition structure, partition and data.

How to quickly copy a partitioned table (including data) in Hive