MSCK REPAIR TABLE在后台做了什么，为什么这么慢? [英] What does MSCK REPAIR TABLE do behind the scenes and why it's so slow?

问题描述

我知道MSCK REPAIR TABLE使用外部表的当前分区更新元存储.

I know that MSCK REPAIR TABLE updates the metastore with the current partitions of an external table.

为此，您只需要在表的根文件夹上执行ls(假设表仅由一列进行分区)，并获取其所有分区，显然是< 1s操作.

To do that, you only need to do ls on the root folder of the table (given the table is partitioned by only one column), and get all its partitions, clearly a < 1s operation.

但是实际上，该操作可能要花费很长的时间才能完成(甚至需要

But in practice, the operation can take a very long time to execute (or even timeout if ran on AWS Athena).

所以我的问题是MSCK REPAIR TABLE在幕后实际上是做什么的，为什么?

So my question is, what does MSCK REPAIR TABLE actually do behind the scenes and why?

MSCK修复表如何找到分区?

How does MSCK REPAIR TABLE find the partitions?

与之相关的其他数据:

_{我们的数据全部存储在S3上，在EMR(Hive)或Athena(Presto)上运行时速度都很慢，表中有约450个分区，每个分区上平均有90个文件，总共3 GB一个分区，文件采用Apache Parquet格式}

推荐答案

从某种意义上来说，它读取目录结构，从中创建分区，然后更新配置单元元存储是正确的.实际上，最近对该命令进行了改进，以从元存储中也删除不存在的分区.您给出的示例非常简单，因为它只有一层分区键.考虑具有多个分区键的表(在实践中通常使用2-3个分区键). msck repair将必须对表目录下的所有子目录进行全树遍历，解析文件名，确保文件名有效，检查分区在元存储中是否已存在，然后添加元存储中唯一不存在的分区.请注意，文件系统上的每个列表都是到名称节点的RPC(对于HDFS)或对于S3或ADLS的Web服务调用，这可能会增加大量时间.另外，为了弄清楚该分区是否已经存在于metastore中，它需要对metastore知道的表的所有分区进行完整列出.这两个步骤都可能会增加在大型表上执行命令所花费的时间.最近，Hive 2.3.0改进了msck修复表的性能(有关更多详细信息，请参见HIVE-15879).您可能需要调整hive.metastore.fshandler.threads和hive.metastore.batch.retrieve.max以提高命令的性能.

You are right in the sense it reads the directory structure, creates partitions out of it and then updates the hive metastore. In fact more recently, the command was improved to remove non-existing partitions from metastore as well. The example that you are giving is very simple since it has only one level of partition keys. Consider table with multiple partition keys (2-3 partition keys is common in practice). msck repair will have to do a full-tree traversal of all the sub-directories under the table directory, parse the file names, make sure that the file names are valid, check if the partition is already existing in the metastore and then add the only partitions which are not present in the metastore. Note that each listing on the filesystem is a RPC to the namenode (in case of HDFS) or a web-service call in case of S3 or ADLS which can add to significant amount of time. Additionally, in order to figure out if the partition is already present in metastore or not, it needs to do a full listing of all the partitions which metastore knows of for the table. Both these steps can potentially increase the time taken for the command on large tables. The performance of msck repair table was improved considerably recently Hive 2.3.0 (see HIVE-15879 for more details). You may want to tune hive.metastore.fshandler.threads and hive.metastore.batch.retrieve.max to improve the performance of command.

这篇关于MSCK REPAIR TABLE在后台做了什么，为什么这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！