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

问题描述

  select ax as column from table1 a where ay in（< ;以逗号分隔的长参数列表>）
 union all 
从table2中选择bx作为列b通过in（< long逗号分隔的参数列表>）
  

我将 hive.exec.parallel 设置为 true 这是帮助我实现union all之间的两个查询之间的并行性。

但是，我的 IN 子句有许多逗号分隔值，每个值在1个作业中取一次，然后是下一个值。

是否有任何hive参数，如果启用它可以帮助我并行地获取 IN 中的参数的数据， code>子句？

目前，我遇到的解决方案是使用 = 多个而不是一个 IN 子句。

解决方案

在不同的查询中多次读取相同的数据以实现更好的并行性。调整适当的映射器和减速器并行度。

首先，启用带向量化的PPD，使用CBO和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的设置示例：

  set tez.grouping.max-size = 67108864; 
 set tez.grouping.min-size = 32000000; 
  

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

  set mapreduce.input.fileinputformat.split.minsize = 16777216; -  16 MB 
 set mapreduce.input.fileinputformat.split.minsize = 1073741824; -  1 GB 
  

- 缩减者的示例设置：

  set hive.exec.reducers.bytes.per.reducer = 67108864; - 减少此数量以增加缩减器的数量
  

使用这些设置进行播放。成功标准是更多的mappers / reducers，并且您的map和reduce阶段运行速度更快。阅读本文以更好地了解如何调整Tez： https://community.hortonworks.com/articles/14309/demystify- tez-tuning-step-by-step.html

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>)

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

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.

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

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.

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; 

Example settings for Mappers on Tez:

set tez.grouping.max-size=67108864;
set tez.grouping.min-size=32000000;

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

set mapreduce.input.fileinputformat.split.minsize=16777216; -- 16 MB
set mapreduce.input.fileinputformat.split.minsize=1073741824; -- 1 GB

--example settings for reducers:

set hive.exec.reducers.bytes.per.reducer=67108864; --decrease this to increase the number of reducers

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

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屋！