如何调整 hive 以查询元数据?

问题描述

如果我在具有某些分区列的表上运行下面的 hive 查询，我想确保 hive 不进行全表扫描，而只是从元数据本身找出结果.有什么办法可以启用此功能吗?

In case I am running a below hive query on table with certain partitioned column, I want to make sure hive does not do full table scan and just figure out the result from meta data itself. Is there any way to enable this ?

Select max(partitioned_col) from hive_table ;

现在，当我运行这个查询时，它会启动 map reduce 任务，我确信它会进行数据扫描，同时它可以很好地从元数据本身中找出价值.

Right now , when I am running this query , its launching map reduce tasks and I am sure its doing data scan while it can very well figure out the value from metadata itself.

推荐答案

每次更改数据时计算表统计信息.

Compute table statistics every time you changed data.

ANALYZE TABLE hive_table PARTITION(partitioned_col) COMPUTE STATISTICS FOR COLUMNS;

启用 CBO 和统计数据自动收集:

Enable CBO and statistics auto gathering:

set hive.cbo.enable=true;
set hive.stats.autogather=true;

使用这些设置通过统计启用 CBO:

Use these settings to enable CBO using statistics:

set hive.compute.query.using.stats=true;
set hive.stats.fetch.partition.stats=true;
set hive.stats.fetch.column.stats=true;

如果没有任何帮助我建议应用这种方法来快速找到最后一个分区:使用 shell 脚本从表位置解析最大分区键.下面的命令将打印所有表文件夹路径、排序、采用最新排序、采用最后一个子文件夹名称、解析分区文件夹名称和提取值.您只需要初始化TABLE_DIR变量并将分区子文件夹的数量放在路径中:

If nothing helps I'd recommend to apply this approach for finding last partition fast:Parse max partition key using shell script from the table location.The command below will print all table folder paths, sort, take latest sorted, take last subfolder name, parse partition folder name and extract value. All you need is to initialize TABLE_DIR variable and put the number of partition subfolder in the path:

last_partition=$(hadoop fs -ls $TABLE_DIR/* | awk '{ print $8 }' | sort -r | head -n1 | cut -d / -f [number of partition subfolder in the path here] | cut -d = -f 2

然后使用 $last_partition 变量作为

Then use $last_partition variable to pass to your script as

  hive -hiveconf last_partition="$last_partition" -f your_script.hql

这篇关于如何调整 hive 以查询元数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！