Apache Hive Join 优化[译]

和其他SQL数据库一样,Hive可以进行多表Join。Join操作尤其是Join大表的时候代价是非常昂贵的。Hadoop平台上的Hive使得数据处理轻松方便,但有时我们也会因此忘记了语句的优化。在本文中,我们会看到Hive中Join优化的最佳实现,这将提升Hive的查询速度。

在Hive中,我们可以使用 repartition join, replication join 和 semi joins进行优化,本文将展示这些操作。

在处理之前,我们先建立一些数据:

1
2
3
4
5
6
7
8
9
Hive> create table emp(
id INT,
name STRING,
salary INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
Loading the data to table ‘emp’:
LOAD DATA LOCAL INPATH ‘/input’ INTO TABLE emp;
Check whether the data is loaded into the table or not.
SELECT * FROM emp;

emp

1
2
3
4
5
6
7
8
Hive> create table dept(
id INT,
dept_name STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;
Loading the data to table ‘dept’:
LOAD DATA LOCAL INPATH ‘/inp’ INTO TABLE dept;
Check whether the data is loaded into the table or not.
SELECT * FROM dept;

dept

表Join的顺序(大表放在后面)

与其他优调一样,了解系统的内部工作机制非常重要。当Hive执行Join时,需要选择哪个表被流式传输(stream),哪个表被缓存(cache)。 Hive将JOIN语句中的最后一个表用于流式传输,因此我们需要确保这个流表在两者之间是最大的。

让我们看下这两张示例表。「emp」表包含了department id, employee name, 和 employee salary。对于任何机构来说,这张表一直是持续增长的。不过「dept」表相对来说是没有什么变动的。因此当这两张表join的时候大表放在后面,查询语句如下:

1
SELECT emp.id,name,salary,dept_name FROM dept JOIN emp ON (dept.id = emp.id);

join_1

当然你也可以手动指定需要stream的表。

1
SELECT /*+ STREAMTABLE(emp) */ emp.id,name,salary,dept_name FROM dept JOIN emp ON (dept.id = emp.id);

join_2

Map Side Join

也叫做replicated join,map-side join是一个特殊类型的join,即在MapReduce job中将一个较小的表加载到内存,然后在map阶段执行join。因为没有reducer的关系,比正常的join要快很多。(译者注:对于小表Join数据倾斜的情况这种Map-Side也很好用)

有一点要注意,那个表需要足够小以便于放在内存中。建议进行配置然后让Hive自动转换这样的map-side join。下面是一张没用map-side的图片。
map_side_3

注意上图中标黄部分。你可以看到“number of reducer”的数量是1,这将降低join操作的速度。

现在,我们执行map-side join,可以在hive-site.xml中或者直接在Hive终端中进行设置。

1
2
hive> set hive.auto.convert.join=true;
hive> set hive.auto.convert.join.noconditionaltask=true;

map_side_1

注意高亮的部分,并没有reducer。所以map-side join比较快。

Sort-Merge-Bucket (SMB) Map Join

它是另一种Hivejoin优化技术,使用这个技术的前提是所有的表都必须是桶分区(bucket)和排序了的(sort)。在这种情况下,连接非常有效,因为它们需要简单地合并预排序的表。

让我们创建bucket表,在创建之前,你先要做如下设置:

1
2
hive> set hive.enforce.bucketing=true;
hive> set hive.enforce.sorting=true;

smb_1

创建bucket表的语句:

1
2
3
4
5
6
7
8
9
10
create table buck_emp(
id int,
name string,
salary int)
CLUSTERED BY (id)
SORTED BY (id)
INTO 4 BUCKETS;
We need to use regular INSERT statement to insert into bucketed table.
INSERT OVERWRITE TABLE buck_emp
SELECT * FROM emp;

smb_2

同样的,我们创建另外一个「dept」的bucket表。

1
2
3
4
5
6
7
8
create table buck_dept(
id int,
dept_name string)
CLUSTERED BY (id)
SORTED BY (id)
INTO 4 BUCKETS;
INSERT OVERWRITE TABLE buck_dept
SELECT * FROM dept;

现在我们进行一些设置,然后执行SMB Map join。

1
2
3
4
5
6
hive>set hive.enforce.sortmergebucketmapjoin=false;
hive>set hive.auto.convert.sortmerge.join=true;
hive>set hive.optimize.bucketmapjoin = true;
hive>set hive.optimize.bucketmapjoin.sortedmerge = true;
hive>set hive.auto.convert.join=false; // if we do not do this, automatically Map-Side Join will happen
SELECT u.name,u.salary FROM buck_dept d INNER JOIN buck_emp u ON d.id = u.id;

smb_3

继续查看黄色高亮部分,可以发现有4个map任务执行(我们设置了4个桶),这比通常的jion操作快多了。

原文:Join Optimization in Apache Hive

打赏支持:支付宝/微信,感谢赏口饭吃