Hive：分区表表结构和数据复制
2021-08-10 本文已影响0人
xiaogp
摘要：Hive，Shell
Hive表复制语句
Hive复制表包括两种
-
复制表结构：只复制表结构到一个新表，新表没有数据为空，复制的表结构中能够保留下分区信息分区字段，使用LIKE关键字
CREATE TABLE copy_table_name like table_name
-
复制表数据：复制表的部分或者全部表结构和表结构中字段的数据到一张新表，但是对分区表分区会失效，分区字段会作为一个普通字段，使用AS字段
CREATE TABLE copy_table_name AS SELECT * FROM table_name
非分区表的完全复制
对于非分区表如果要完全复制一张表到另一张表，直接使用CREATE TABLE ... AS语句即可，比如以下复制一个表的两个字段以及字段的值到另一个表
hive> DESC sqoop_test;
OK
industry_code string
rank string
inc string
detail string
hive> CREATE TABLE copy_sqoop_test AS SELECT rank, inc FROM sqoop_test;
hive> DESC copy_sqoop_test;
OK
rank string
inc string
# 和sqoop_test数量一致
hive> select count(*) from copy_sqoop_test;
分区表的完全复制
对于分区表如果使用CREATE TABLE ... AS语句则分区失效，但是可以执行不报错，且字段和数据都能完全复制
hive> SHOW PARTITIONS pira_risk_score_industry;
OK
dt=20201209
dt=20201210
dt=20201211
dt=20201212
...
以上有一张分区表，以dt字段作为分区，使用CREATE TABLE ... AS进行全表复制
hive> CREATE TABLE copy_pira_risk_score_industry AS SELECT * FROM pira_risk_score_industry;
Time taken: 26.307 seconds
检查表结构和表数据量都没有问题
hive> DESC copy_pira_risk_score_industry;
OK
industry_code string
industry_name string
finance_score double
manage_score double
business_score double
position_score double
environ_score double
credit_score double
score double
rowkey string
dt string
Time taken: 0.061 seconds, Fetched: 11 row(s)
hive> SELECT COUNT(*) FROM copy_pira_risk_score_industry;
OK
1980
Time taken: 19.854 seconds, Fetched: 1 row(s)
检查分区，报错此表不是分区表，is not a partitioned table，但是在表结构中确实存在本来的分区字段dt，此时dt字段的分区功能失效，但是数据保留住了
hive> SHOW PARTITIONS copy_pira_risk_score_industry;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Table copy_pira_risk_score_industry is not a partitioned table
分区表的全称复制且带有分区的操作方法需要使用LIKE语句复制到分区信息，具体步骤如下
- 复制表结构和分区信息
- 将数据复制到HDFS对应Hive的表数仓路径
- 修复表的分区元数据
第一步复制得到一张空表，具有原表的表结构和分区信息
hive> CREATE TABLE copy_pira_risk_score_industry LIKE pira_risk_score_industry;
OK
Time taken: 0.626 seconds
hive> desc copy_pira_risk_score_industry;
OK
industry_code string
industry_name string
finance_score double
manage_score double
business_score double
position_score double
environ_score double
credit_score double
score double
rowkey string
dt string
# Partition Information
# col_name data_type comment
dt string
Time taken: 0.078 seconds, Fetched: 16 row(s)
下一步使用hdfs命令操作将原表在hdfs的存储路径复制到新表的路径，一张表的存储路径是一个目录，该目录下还存在子目录，每一个子目录代表一个分区，在分区目录下就是数据文件，数据文件是part开头格式，由Hive的分桶策略将同一分区下的数据进行划分
[hdfs@cloudera01 ~]$ hdfs dfs -cp /user/hive/warehouse/test.db/pira_risk_score_industry/* /user/hive/warehouse/test.db/copy_pira_risk_score_industry/
复制语句采用*通配符将原表目录下所有文件复制到新表路径下，查看新表的hdfs路径下数据文件
[hdfs@cloudera01 ~]$ hdfs dfs -ls /user/hive/warehouse/test.db/copy_pira_risk_score_industry/
Found 99 items
drwxr-xr-x - hdfs hive 0 2021-08-10 10:51 /user/hive/warehouse/test.db/copy_pira_risk_score_industry/dt=20201209
drwxr-xr-x - hdfs hive 0 2021-08-10 10:51 /user/hive/warehouse/test.db/copy_pira_risk_score_industry/dt=20201210
drwxr-xr-x - hdfs hive 0 2021-08-10 10:51 /user/hive/warehouse/test.db/copy_pira_risk_score_industry/dt=20201211
drwxr-xr-x - hdfs hive 0 2021-08-10 10:51 /user/hive/warehouse/test.db/copy_pira_risk_score_industry/dt=20201212
drwxr-xr-x - hdfs hive 0 2021-08-10 10:51 /user/hive/warehouse/test.db/copy_pira_risk_score_industry/dt=20201213
此时新表虽然对应数仓目录下有数据文件，但是在Hive客户端还是查不到数据为空表，因为一个个数据分区不存在在新表的元数据中，数据是以分区目录为单位聚合的，新表目前查不到一个分区则自然查不到数据
hive> select * from copy_pira_risk_score_industry limit 5;
OK
Time taken: 0.082 seconds
下一步修复表的分区元数据，使用MSCK REPAIR TABLE命令
hive> MSCK REPAIR TABLE copy_pira_risk_score_industry;
OK
Partitions not in metastore:
copy_pira_risk_score_industry:dt=20201209
copy_pira_risk_score_industry:dt=20201210
copy_pira_risk_score_industry...
Repair: Added partition to metastore copy_pira_risk_score_industry:dt=20201209
Repair: Added partition to metastore copy_pira_risk_score_industry:dt=20201210
Repair: Added partition to metastore copy_pira_risk_score_industry:dt=20201211
Repair: Added partition to metastore copy_pira_risk_score_industry:dt=20201212
...
由输出的执行过程可见MSCK REPAIR TABLE命令先检查了表的分区信息是否存在在元数据，然后对不存在的分区信息进行修复，修复后该表即可正常使用
hive> show partitions copy_pira_risk_score_industry;
OK
dt=20201209
dt=20201210
dt=20201211
dt=20201212
...
MSCK REPAIR TABLE和ALTER TABLE ADD PARTITION
MSCK REPAIR TABLE的作用是只需要只用这一个命令就可以快速，自动化地添加（修复）全部分区，在Hive中如果先建分区表，并且以数据拷贝到对应HDFS目录这种方式作为初始化，需要手动添加分区才能使用，如果分区过多，使用ALTER TABLE ADD PARTITION极为不变，下面做一下测试看ALTER TABLE ADD PARTITION是否也能完成分区表的完全复制
# 重新复制表结构
hive> CREATE TABLE copy_pira_risk_score_industry LIKE pira_risk_score_industry;
OK
Time taken: 0.255 seconds
[hdfs@cloudera01 ~]$ hdfs dfs -cp /user/hive/warehouse/test.db/pira_risk_score_industry/* /user/hive/warehouse/test.db/copy_pira_risk_score_industry/
下一步采用手动添加1个分区dt='20201209'
hive> ALTER TABLE copy_pira_risk_score_industry ADD PARTITION (dt='20201209');
OK
Time taken: 0.201 seconds
hive> show partitions copy_pira_risk_score_industry;
OK
dt=20201209
hive> select count(*) from copy_pira_risk_score_industry;
OK
20
验证了手动分区能够完成，MSCK REPAIR TABLE只是自动化的扫描一遍数仓目录下的分区信息(dt='20201209' 到 dt='20210317')，如果编写一个Shell脚本也能实现如下
#!/bin/bash
hive -e "CREATE TABLE test.copy_pira_risk_score_industry LIKE test.pira_risk_score_industry"
if [ $? -ne 0 ];then
echo "复制表结构出错"
exit $?
fi
hdfs dfs -cp /user/hive/warehouse/test.db/pira_risk_score_industry/* /user/hive/warehouse/test.db/copy_pira_risk_score_industry/
if [ $? -ne 0 ];then
echo "复制表数据到指定数仓目录错误"
exit $?
fi
min_dt=20201209
max_dt=20210317
while [[ "$min_dt" < "$max_dt" || "$min_dt" == "$max_dt" ]]
do
hive -e "ALTER TABLE test.copy_pira_risk_score_industry ADD PARTITION(dt='${min_dt}')"
echo "已创建分区${min_dt}"
min_dt=`date -d "$min_dt 1day" +%Y%m%d`
done
sudo -u hdfs bash copy_partitions_table.sh
运行这个Shell脚本后能达到同样的效果，但是这个脚本执行了15分钟，需要频繁启动和关闭Hive进程
