`
x10232
  • 浏览: 57194 次
  • 来自: 北京
社区版块
存档分类
最新评论

hive in/not in替换以及hive join优化

    博客分类:
  • hive
 
阅读更多

目前hive不支持 in或not in 中包含查询子句的语法,所以只能通过left join实现。

假设有一个登陆表login(当天登陆记录,只有一个uid),和一个用户注册表regusers(当天注册用户,字段只有一个uid),这两个表都包含一个字段,uid。 

in查询

如果要查询当天登陆的注册用户,需要用in查询,hive sql如下:

select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is not null

如果login表和regusers表按天分区,字段是dt,那么查询2013年1月1号当天登陆的注册用户,hive sql如下:

select login.uid from login day_login left outer join 
   (select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is not null

 

not in查询

如果要查询当天登陆的老用户(这里假设非当天注册用户就是老用户),需要用not in查询,hive sql如下:

select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is null;

如果login表和regusers表按天分区,字段是dt,那么查询2013年1月1号当天登陆的老用户,hive sql如下:

select login.uid from login day_login left outer join 
   (select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is null;



Hive join优化
========================================================

由 于 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 中去执行,然后你会发现报错了:

 

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

 

1
2
3
4
5
6
7
8
9
10
11
12
13
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; 防止笛卡儿积的执行:

 

1
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,然后汇总:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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 语句之间没有依赖关系,其实是可以并行执行的:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 前加上如下环境变量选项:

 

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

完整的语句如下:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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/#

 

原文地址:file:///C:/Users/58/Desktop/hive%20in_not%20in%E6%9B%BF%E6%8D%A2%E4%BB%A5%E5%8F%8Ahive%20join%E4%BC%98%E5%8C%96.htm

分享到:
评论

相关推荐

    hive-exec-*.jar包

    Missing Hive Execution Jar: /hive/hive1.2.1/lib/hive-exec-*.jar

    Hive Beeline连接报错:Could not open client transport with JDBC Uri: jdbc:hive2://hcmaster:10000/default

    在使用Hive的Beeline客户端进行连接时,可能会遇到错误提示"Could not open client transport with JDBC Uri: jdbc:hive2://hcmaster:10000/default"。这个错误通常意味着Beeline无法通过JDBC URL成功建立到Hive ...

    hive sql + left join 数据缺失

    ### Hive SQL Left Join 数据缺失问题解析 #### 一、问题背景 在大数据处理场景中,Hive 是一种广泛使用的工具,用于对存储在 Hadoop 文件系统中的数据进行数据汇总、查询和分析。随着Hive版本的不断更新,部分用户...

    hive优化总结

    Hive优化总结是Hive性能优化的总结,涉及HIVE的参数设置、HQL语言的写法、JOIN操作的优化、MapReduce操作的优化、列裁剪、分区裁剪等多个方面。 1. 配置文件优化 Hive的配置文件hive-site.xml是Hive的核心配置文件...

    cdh6.3.2升级hive至3.x后登陆HiveServer2连接失败.doc

    cp /opt/cloudera/parcels/CDH/lib/hive/conf/hive-env.sh /opt/cloudera/parcels/CDH/lib/hive/conf/hive-env.sh.bak ``` 2. **编辑并注释掉 `export HIVE_OPTS`**: - 使用文本编辑器打开 `hive-env.sh` 文件...

    hive客户端安装_hive客户端安装_hive_

    1. 将下载的Hive压缩包解压到指定目录,例如:/usr/local/hive。 2. 配置环境变量,在~/.bashrc或~/.bash_profile文件中添加以下内容: ``` export HIVE_HOME=/usr/local/hive export PATH=$PATH:$HIVE_HOME/bin ```...

    Hive优化.docx

    针对Hive的性能优化,可以从多个角度入手,特别是在处理复杂的Join操作时。以下是根据标题、描述以及部分内容提炼出的关键知识点: 1. **Join操作优化**: - **Join顺序调整**:在Join操作中,应将较小的表或子...

    hive3.1.2编译.doc

    1. `druid-handler/src/java/org/apache/hadoop/hive/druid/serde/DruidScanQueryRecordReader.java` 2. `llap-server/src/java/org/apache/hadoop/hive/llap/daemon/impl/AMReporter.java` 3. `llap-server/src/...

    Hive优化方法整理

    Hive 优化方法整理 Hive 优化方法整理是 Hive 数据处理过程中的重要步骤,涉及到 Hive 的类 SQL 语句本身进行调优、参数调优、Hadoop 的 HDFS 参数调优和 Map/Reduce 调优等多个方面。 Hive 类 SQL 语句优化 1. ...

    hive所需jar包

    1. **Hive Common JAR (hive-common.jar)**:这是Hive的基础库,包含了很多通用的功能和辅助类,如Hive的配置管理、网络通信、日志记录以及元数据处理等。它是Hive运行时不可或缺的一部分,为其他Hive组件提供基础...

    hive参数优化总结

    Hive 参数优化总结 Hive 是一个基于 Hadoop 的数据仓库工具,用于对大规模数据进行查询、分析和处理。为了提高 Hive 的性能和效率,参数优化是非常重要的一步。本文档将总结 Hive 参数优化的相关知识点,并对 Hive ...

    Hive查询优化整理与Hive简易版思维导图

    这个思维导图可能包含了Hive的基本概念、重要组件、查询语法、优化技巧以及错误排查等方面的内容。通过思维导图,你可以直观地掌握Hive的核心知识结构,快速定位到具体问题,提高学习和工作效率。 总结,Hive查询...

    hive高级分析函数与优化.rar_hive_hive 函数优化_hive 分析 大数据_hive高级优化_oldwlc

    高级优化包括表和列的压缩、数据倾斜处理、Join优化、SubQuery优化、以及使用Explain命令分析查询计划等。压缩可以减小存储空间,数据倾斜优化是解决数据分布不均问题,Join优化涉及MapJOIN、BroadcastJOIN等,...

    Hive思维导图之Hive优化

    Hive思维导图之Hive优化

    Hive和Hase配置

    1.拷贝hbase-0.94.1.jar和zookeeper-3.4.3.jar到hive/lib下。 注意:如hive/lib下已经存在这两个文件的其他版本,建议删除后使用hbase下的相关版本。 //在/home/hadoop/hive-0.9.0/lib 下,备份两个jar包 mv ...

    Hive on Spark EXPLAIN statement

    Hive on Spark EXPLAIN statement : 讲述了 Common Join / Map join / Bucket Map Join / Sorted Merge Bucket Map Join / skew join 在explain 中的 树结构 。In Hive, command EXPLAIN can be used to show the ...

    centos下hive连接mysql驱动 ,mysql-connector-java-8.0.26-1.el7.noarch.

    centos 下hive连接mysql驱动 ,mysql-connector-java-8.0.26-1.el7....4.按你的实际需求,将此mysql-connector-java.jar文件复制到你需要的地方,一班是在大数据组件的conf目录,比如hive是/usr/software/hive312/lib/

    Hive优化以及执行原理

    6. **使用索引**:虽然Hive的索引功能有限,但在某些场景下,例如优化join条件,使用bucketing和sort by可以提升性能。 7. **SQL语句优化**:避免使用子查询,使用连接(JOIN)代替子查询,合理使用WHERE和HAVING,...

    Hive 优化以及执行原理

    4. **优化器**:Hive的CBO(Cost-Based Optimizer)根据数据统计信息和资源成本估算最优执行计划,包括选择最佳的JOIN顺序、分桶和分区等。 5. **执行引擎**:Hive可以使用多种执行引擎,如Tez和Spark,它们能提供...

Global site tag (gtag.js) - Google Analytics