并行执行带有 IN 子句参数的 Hive 查询 [英] Execute Hive Query with IN clause parameters in parallel

问题描述

我有一个像下面这样的 Hive 查询:

I am having a Hive query like the one below:

select a.x as column from table1 a where a.y in (<long comma-separated list of parameters>)
union all
select b.x as column from table2 b where b.y in (<long comma-separated list of parameters>)

我已将 hive.exec.parallel 设置为 true，这有助于我在 union all 之间的两个查询之间实现并行.

I have set hive.exec.parallel as true which is helping me achieve parallelism between the two queries between union all.

但是，我的 IN 子句有许多逗号分隔值，每个值在 1 个作业中取一次，然后取下一个值.这实际上是按顺序执行的.

But, my IN clause has many comma separated values and each value is taken once in 1 job and then the next value. This is actually getting executed sequentially.

是否有任何 hive 参数可以帮助我为 IN 子句中的参数并行获取数据?

Is there any hive parameter which if enabled can help me fetch data parallelly for the parameters in the IN clause?

目前，我的解决方案是多次使用 = 而不是一个 IN 子句触发选择查询.

Currently, the solution I am having is fire the select query with = multiple times instead of one IN clause.

推荐答案

无需在单独的查询中多次读取相同的数据即可实现更好的并行性.为此调整适当的映射器和化简器并行度.

There is no need to read the same data many times in separate queries to achieve better parallelism. Tune proper mapper and reducer parallelism for the same.

首先，使用矢量化启用 PPD，使用 CBO 和 Tez:

First of all, enable PPD with vectorizing, use CBO and Tez:

SET hive.optimize.ppd=true;
SET hive.optimize.ppd.storage=true;
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled = true;
SET hive.cbo.enable=true;
set hive.stats.autogather=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.partition.stats=true;
set hive.execution.engine=tez;
SET hive.stats.fetch.column.stats=true;
SET hive.tez.auto.reducer.parallelism=true; 

Tez 上 Mappers 的示例设置:

Example settings for Mappers on Tez:

set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
set tez.grouping.max-size=32000000;
set tez.grouping.min-size=32000;

如果您决定在 MR 而不是 Tez 上运行 Mappers 的示例设置:

Example settings for Mappers if you decide to run on MR instead of Tez:

set mapreduce.input.fileinputformat.split.minsize=32000; 
set mapreduce.input.fileinputformat.split.maxsize=32000000; 

--reducer 的示例设置:

--example settings for reducers:

set hive.exec.reducers.bytes.per.reducer=32000000; --decrease this to increase the number of reducers, increase to reduce parallelism

玩这些设置.成功标准是更多的 mapper/reducer，并且您的 map 和 reduce 阶段运行得更快.

Play with these settings. Success criteria is more mappers/reducers and your map and reduce stages are running faster.

阅读这篇文章以更好地了解如何调整 Tez:https://community.hortonworks.com/articles/14309/demystify-tez-tuning-step-by-step.html

Read this article for better understanding of how to tune Tez: https://community.hortonworks.com/articles/14309/demystify-tez-tuning-step-by-step.html

这篇关于并行执行带有 IN 子句参数的 Hive 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！