一起学Hive——使用MSCK命令修复Hive分区

最近在使用Hive的过程中，在备份数据时，经常会使用cp或mv命令来拷贝数据，将数据拷贝到我们新建备份表的目录下面，如果不是分区表，则上面的操作之后，新建的备份表可以正常使用，但是如果是分区表的，一般都是使用alter table add partition命令将分区信息添加到新建的表中，每添加一条分区信息就执行一个alter table add partition命令，如果分区数量少还好办，但是遇到分区数量多的情况，特别是分区数量大于50的情况，如果还是使用alter命令添加分区，那是一件耗时耗力的事情，还容易出错。

幸运的是Hive提供了MSCK命令，用于修复表的分区，我们先看Hive官方是如果介绍MCSK命令的：

Hive stores a list of partitions for each table in its metastore. If, however, new partitions are directly added to HDFS (say by using hadoop fs -put command), the metastore (and hence Hive) will not be aware of these partitions unless the user runs ALTER TABLE table_name ADD PARTITION commands on each of the newly added partitions.
However, users can run a metastore check command with the repair table option:
MSCK REPAIR TABLE table_name;
which will add metadata about partitions to the Hive metastore for partitions for which such metadata doesn't already exist. In other words, it will add any partitions that exist on HDFS but not in metastore to the metastore. See HIVE-874 for more details. When there is a large number of untracked partitions, there is a provision to run MSCK REPAIR TABLE batch wise to avoid OOME. By giving the configured batch size for the property hive.msck.repair.batch.size it can run in the batches internally. The default value of the property is zero, it means it will execute all the partitions at once.
The equivalent command on Amazon Elastic MapReduce (EMR)'s version of Hive is:
ALTER TABLE table_name RECOVER PARTITIONS;
Starting with Hive 1.3, MSCK will throw exceptions if directories with disallowed characters in partition values are found on HDFS. Use hive.msck.path.validation setting on the client to alter this behavior; "skip" will simply skip the directories. "ignore" will try to create partitions anyway (old behavior). This may or may not work.

翻译成中文的大概意思就是：Hive将每个表的分区信息保存在metastore中，如果通过hadoop fs -put命令直接将分区信息添加到HDFS，metastore是不会感知到这些新增的分区，除非执行了ALTER TABLE table_name ADD PARTITION命令。但是用户可以运行metastore检查命令MSCK REPAIR TABLE table_name;该命令将关于分区的元信息添加到Hive metastore中，这是对于那些没有元信息的分区来说的。换句话说，就是将任何存在于HDFS上但不在metastore上的分区添加到metastore。

下面介绍如果使用MSCK命令，创建了一个bigdata17_partition表，然后通过cp命令将几个目录的文件拷贝到bigdata17_partition目录下面，然后执行show partitions bigdata17_partition命令，将不会显示分区的信息：

hive> show partitions bigdata17_partition;
OK
Time taken: 1.121 seconds

然后执行MSCK REPAIR TABLE bigdata17_partition;命令添加分区：

hive> MSCK REPAIR TABLE bigdata17_partition;
18/10/11 17:27:15 WARN log: Updating partition stats fast for: bigdata17_partition;
18/10/11 17:27:15 WARN log: Updated size to 10124
18/10/11 17:27:15 WARN log: Updating partition stats fast for: bigdata17_partition;
18/10/11 17:27:15 WARN log: Updated size to 21234
18/10/11 17:27:15 WARN log: Updating partition stats fast for: bigdata17_partition;
18/10/11 17:27:15 WARN log: Updated size to 346783
18/10/11 17:27:15 WARN log: Updating partition stats fast for: bigdata17_partition;
18/10/11 17:27:15 WARN log: Updated size to 2532162
18/10/11 17:27:15 WARN log: Updating partition stats fast for: bigdata17_partition;
18/10/11 17:27:15 WARN log: Updated size to 2901198
18/10/11 17:27:15 WARN log: Updating partition stats fast for: bigdata17_partition;
18/10/11 17:27:15 WARN log: Updated size to 3129087
18/10/11 17:27:15 WARN log: Updating partition stats fast for: bigdata17_partition;
18/10/11 17:27:15 WARN log: Updated size to 23190876
OK
Partitions not in metastore:    bigdata17_partition;:dt=2018-09-15    bigdata17_partition;:dt=2018-09-16    bigdata17_partition;:dt=2018-09-17    bigdata17_partition;:dt=2018-09-18    bigdata17_partition;:dt=2018-09-19     bigdata17_partition;:dt=2018-09-20    bigdata17_partition;:dt=2018-09-21
Repair: Added partition to metastore bigdata17_partition;:dt=2018-09-15
Repair: Added partition to metastore bigdata17_partition;:dt=2018-09-16
Repair: Added partition to metastore bigdata17_partition;:dt=2018-09-17
Repair: Added partition to metastore bigdata17_partition;:dt=2018-09-18
Repair: Added partition to metastore bigdata17_partition;:dt=2018-09-19
Repair: Added partition to metastore bigdata17_partition;:dt=2018-09-20
Repair: Added partition to metastore bigdata17_partition;:dt=2018-09-21
Time taken: 0.613 seconds, Fetched 8 row(s)

通过上述的结果可以看到已经将bigdata17_partition表的分区信息添加到Hive metastore中，和add partition命令比起来既方便又高效。

有点需要注意的是，分区的目录结构必遵循
/partition_name=partition_value/结构，否则msck无法自动添加分区，只能使用add partition命令。