文档章节

一例 Hive join 优化实战

大数据之路
 大数据之路
发布于 2014/08/29 04:19
字数 1979
阅读 11482
收藏 17

由于 hive 与传统关系型数据库面对的业务场景及底层技术架构都有着很大差异,因此,传统数据库领域的一些技能放到 Hive 中可能已不再适用。关于 hive 的优化与原理、应用的文章,前面也陆陆续续的介绍了一些,但大多都偏向理论层面,本文就介绍一个实例,从实例中一步步加深对 hive 调优的认识与意识。

1、需求

需求我做了简化,很简单,两张表做个 join,求指定城市,每天的 pv,用传统的 RDBMS SQL 写出来就这样的:

SELECT t.statdate,
       c.cname,
       count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON (t.area1= c.cname
                                OR t.area2 =c.cname
                                OR t.area3 = c.cname)
WHERE t.statdate>='20140818' and t.statdate<='20140824'
  AND platform='pc'
GROUP BY t.statdate,
         c.cname;
怎么样?根据 SQL 看懂需求没问题吧?

2、非等值 join 问题

然后把这条 SQL 贴到 hive 中去执行,然后你会发现报错了:

FAILED: SemanticException [Error 10019]: Line 5:32 OR not supported in JOIN currently 'cname'
这是因为 hive 受限于 MapReduce 算法模型,只支持 equi-joins(等值 join),要实现上述的非等值 join,你可以采用笛卡儿积( full Cartesian product )来实现:

SELECT t.statdate,
       c.cname,
       count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t
WHERE t.statdate>='20140818'
  AND t.statdate<='20140824'
  AND platform='pc'
  AND (t.area1= c.cname
       OR t.area2 =c.cname
       OR t.area3 = c.cname)
GROUP BY t.statdate,
         c.cname;
然后再拿着这条语句执行下。

3、优化:reduce side join VS Cartesian product

如果你真的把这条语句放到 Hive 上执行,然后恰好你有张表还非常大,那么恭喜你。。。集群管理员估计会找你的麻烦了。。。

友情提示:笛卡儿积这种语句在 Hive 下慎用,大数据场景下的 m * n 映射结果你懂的。。。对此,Hive 特意提供了一个环境变量:hive.mapred.mode=strict; 防止笛卡儿积的执行:

FAILED: SemanticException [Error 10052]: In strict mode, cartesian product is not allowed. If you really want to perform the operation, set hive.mapred.mode=nonstrict

从 2 中的观察得知我们在 on 后面跟 join 条件,走的是 reduce side join,如果你在 where 后跟则是走 Cartesian product,但是这里单条 sql 又没法实现 reduce side join,还有没有其它办法呢?

4、改写非等值 join:union all

既然不允许非等值 join,那我们换一下思路,多个子查询 union all,然后汇总:

SELECT dt,
       name,
       count(cid)
FROM
  (SELECT t.statdate dt,
          c.cname name,
          t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT t.statdate dt,
                    c.cname name,
                    t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT t.statdate dt,
                    c.cname name,
                    t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc') tmp_trackflow
GROUP BY dt,
         name;

5、优化:map side join

上述语句走的是 reduce side join,从我们的需求及业务得知,tmpdb.city 是一张字典表,数据量很小,因此我们可以试试把上述的语句改写成 mapjoin:

SELECT dt,
       name,
       count(cid)
FROM
  (SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                            c.cname name,
                            t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                                      c.cname name,
                                      t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                                      c.cname name,
                                      t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc') tmp_trackflow
GROUP BY dt,
         name;

6、优化无极限:开启 parallel 和 控制 reduce 个数

上述语句执行时,你可以看到执行计划和状态信息,以及结合你的 union all 语句可知,三个 union 语句之间没有依赖关系,其实是可以并行执行的:

explain SQL...
...
STAGE DEPENDENCIES:
  Stage-11 is a root stage
  Stage-1 depends on stages: Stage-11
  Stage-2 depends on stages: Stage-1
  Stage-3 depends on stages: Stage-2, Stage-6, Stage-9
  Stage-12 is a root stage
  Stage-5 depends on stages: Stage-12
  Stage-6 depends on stages: Stage-5
  Stage-13 is a root stage
  Stage-8 depends on stages: Stage-13
  Stage-9 depends on stages: Stage-8
  Stage-0 is a root stage
...
我们在 SQL 前加上如下环境变量选项:

set mapred.reduce.tasks=60;
set hive.exec.parallel=true;
让执行计划中的 Stage-11、Stage-12、Stage-13 并行执行,并控制好 reduce task 个数。

完整的语句如下:

hive -e "
SET mapred.reduce.tasks=60;


SET hive.exec.parallel=TRUE;


SELECT dt,
       name,
       count(cid)
FROM
  (SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                            c.cname name,
                            t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                                      c.cname name,
                                      t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc'
   UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
                                      c.cname name,
                                      t.cookieid cid
   FROM tmpdb.city c
   JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
   WHERE t.statdate>='20140818'
     AND t.statdate<='20140824'
     AND platform='pc') tmp_trackflow
GROUP BY dt,
         name;

" > a1.txt

最后的优化效果是:2 中的语句三个小时没出结果。。。5 比 4 快 8 倍左右,6 比 5 快 2 倍左右,最终 10min 出结果。

7、最后的问题:

在 6 的语句执行的时候你会发现,其扫描了 三遍 源文件。而 hive 本身是对 union all 的 join 做了优化的,当多个 union all 子查询同一张表时,只扫描一次源文件,但这里为什么会三个子查询各扫描一次呢?

可能是这里的 union all 子查询使用了 join 的缘故,导致 hive 的 union all 执行计划优化失效了。

关于这块怎么能优化成只扫描一次源文件,或者你有更好的优化方案,欢迎留言交流。

8、关于 hive 中的 笛卡尔集( full Cartesian product )

在JION接连查询中没有ON连接key,而通过WHERE条件语句会产生笛卡尔集。
Hive本身是不支持笛卡尔集的,不能用select T1.*, T2.* from table1, table2这种语法。但有时候确实需要用到笛卡尔集的时候,可以用下面的语法来实现同样的效果:
select T1.*, T2.* from table1 T1 join table2 T2 where 1=1;
注意在Hive的Strict模式下不能用这种语法,因为这样会产生笛卡尔集,而这种模式禁止产生笛卡尔集。需要先用set hive.mapred.mode=nonstrict;设为非strict模式就可以用了,或者将where改为on连接。
select T1.*, T2.* from table1 T1 join table2 T2 on  T1.id=T2.id;

9、关于Strict Mode

Hive中的严格模式可以防止用户发出(可以有问题)的查询无意中造成不良的影响。 将hive.mapred.mode设置成strict可以禁止三种类型的查询:
1)、在一个分区表上,如果没有在WHERE条件中指明具体的分区,那么这是不允许的,换句话说,不允许在分区表上全表扫描。这种限制的原因是分区表通常会持非常大的数据集并且可能数据增长迅速,对这样的一个大表做全表扫描会消耗大量资源,必须要再WHERE过滤条件中具体指明分区才可以执行成功的查询。
2)、第二种是禁止执行有ORDER BY的排序要求但没有LIMIT语句的HiveQL查询。因为ORDER BY全局查询会导致有一个单一的reducer对所有的查询结果排序,如果对大数据集做排序,这将导致不可预期的执行时间,必须要加上limit条件才可以执行成功的查询。
3)、第三种是禁止产生笛卡尔集。在JION接连查询中没有ON连接key而通过WHERE条件语句会产生笛卡尔集,需要改为JOIN...ON语句。

10、Refer:

[1] Hive Query- Joining two tables on three joining conditions with OR operator

http://stackoverflow.com/questions/16272804/hive-query-joining-two-tables-on-three-joining-conditions-with-or-operator

[2] LanguageManual JoinOptimization

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+JoinOptimization

[3] hive 执行计划

http://yychao.iteye.com/blog/1749562

[4] Hive SQL解析/执行计划生成流程分析

http://yanbohappy.sinaapp.com/?p=265

[5] 数据仓库中的SQL性能优化(Hive篇)

http://www.zihou.me/html/2014/02/12/9207.html

[6] Hive优化以及执行原理

http://www.smartcitychina.cn/upload/2014-01/14012015376829.pdf

[7] Hive作业优化总结

http://my.oschina.net/yangzhiyuan/blog/262910

[8] Hive连接产生笛卡尔集

http://blog.javachen.com/2013/10/17/cartesian-product-in-hive-inner-join/#

© 著作权归作者所有

大数据之路
粉丝 1605
博文 514
码字总数 333288
作品 0
武汉
架构师
私信 提问
加载中

评论(2)

b
bee_xi
ecdata.ext_trackflow表 如果存在某一行area1=area2=area3
步骤: 4、改写非等值 join:union all
不成立的
l
liuhui_306
博主执行10min有多少条数据?集群有多大?
Hive从入门到实战视频教程【40讲全】

1、Hive是什么,Hive的体系结构,Hive和Hadoop的关系 2、Hive的元数据存储、Hive的数据存储、Hive和RDBMS的区别 3、JDK安装 4、Hadoop集群搭建-1 5、Hadoop集群搭建-2 6、Hadoop集群搭建-3 ...

tom先生
2014/11/26
48
0
大数据教程(12.1)hive中SQL操作补充知识

上一篇博客分享了hive的基础操作知识,本节博主将继续补充分享一些hive的SQL操作知识。 一、保存select查询结果的几种方式: 二、Hive Join操作 三、具体实例: 1、获取已经分配班级的学生姓...

em_aaron
01/26
21
0
Hive中的所有Join

内连接:inner join --join优化:在进行join的时候,大表放在最后面 --但是使用 /+streamtable(大表名称)/ 来标记大表,那么大表放在什么位置都行了 select /+streamtable(s)/ s.ymd,d.divid...

qhaiyan
2016/12/03
24
0
Apache Spark 系列技术直播 - Spark SQL进阶与实战

Spark SQL进阶与实战 Spark相关组件介绍 Spark及其依赖组件 Hive Metastore介绍 Spark Thrift Server介绍 表与ETL Spark表基本概念 Spark建表最佳实践 Spark ETL最佳实践 动态分区表示例分析...

开源大数据
2018/12/05
0
0
大数据经典学习路线(及供参考)之 一

1.Linux基础和分布式集群技术 学完此阶段可掌握的核心能力: 熟练使用Linux,熟练安装Linux上的软件,了解熟悉负载均衡、高可靠等集群相关概念,搭建互联网高并发、高可靠的服务架构; 学完此...

柯西带你学编程
2018/05/22
0
0

没有更多内容

加载失败,请刷新页面

加载更多

还为PDF转Word抓狂?以下神器让你在职场倍受欢迎!

身在职场的你,是否一直在琢磨:如何能让自己在公司更受欢迎?如何才能在办公室里混个好人缘?如何在同事圈里留个好印象?其实,想要让自己成为受欢迎的人,只要让自己成为大家需要的人不就行...

foxit2world
23分钟前
7
0
AndServer+Service打造Android服务器实现so文件调用

so 文件调用 随着 Android 移动安全的高速发展,不管是为了执行效率还是程序的安全性等,关键代码下沉 native 层已成为基本操作。 native 层的开发就是通指的 JNI/NDK 开发,通过 JNI 可以实...

夜幕NightTeam
25分钟前
4
0
Docker下kafka学习三部曲之二:本地环境搭建

在上一章《 Docker下kafka学习,三部曲之一:极速体验kafka》中我们快速体验了kafka的消息分发和订阅功能,但是对环境搭建的印象仅仅是执行了几个命令和脚本,本章我们通过实战来学习如何编写...

程序员欣宸
25分钟前
4
0
萌新推荐!不再为Excel转换PDF发愁,Aspose.Cells for .NET一步到位!

Aspose.Cells for .NET(点击下载)是Excel电子表格编程API,可加快电子表格管理和处理任务,支持构建具有生成,修改,转换,呈现和打印电子表格功能的跨平台应用程序。 将Excel工作簿转换为...

mnrssj
26分钟前
6
0
对于绘画小白怎么画制服?该注意什么?

怎样制作学生服装?想必绘画初学者们常常会想的问题吧,不知道怎样才能画好人物的衣服,别着急,今日就在这儿讲一些关于如何绘画学生衣服校服的教程给我们!期望能够帮到你们! 轻便西装是不...

热爱画画的我
31分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部