目前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 与传统关系型数据库面对的业务场景及底层技术架构都有着很大差异,因此,传统数据库领域的一些技能放到 Hive 中可能已不再适用。关于 hive 的优化与原理、应用的文章,前面也陆陆续续的介绍了一些,但大多都偏向理论层面,本文就介绍一个实例,从实例中一步步加深对 hive 调优的认识与意识。 1、需求需求我做了简化,很简单,两张表做个 join,求指定城市,每天的 pv,用传统的 RDBMS SQL 写出来就这样的:
2、非等值 join 问题然后把这条 SQL 贴到 hive 中去执行,然后你会发现报错了:
3、优化:reduce side join VS Cartesian product如果你真的把这条语句放到 Hive 上执行,然后恰好你有张表还非常大,那么恭喜你。。。集群管理员估计会找你的麻烦了。。。 友情提示:笛卡儿积这种语句在 Hive 下慎用,大数据场景下的 m * n 映射结果你懂的。。。对此,Hive 特意提供了一个环境变量:hive.mapred.mode=strict; 防止笛卡儿积的执行:
从 2 中的观察得知我们在 on 后面跟 join 条件,走的是 reduce side join,如果你在 where 后跟则是走 Cartesian product,但是这里单条 sql 又没法实现 reduce side join,还有没有其它办法呢? 4、改写非等值 join:union all既然不允许非等值 join,那我们换一下思路,多个子查询 union all,然后汇总:
5、优化:map side join上述语句走的是 reduce side join,从我们的需求及业务得知,tmpdb.city 是一张字典表,数据量很小,因此我们可以试试把上述的语句改写成 mapjoin:
6、优化无极限:开启 parallel 和 控制 reduce 个数上述语句执行时,你可以看到执行计划和状态信息,以及结合你的 union all 语句可知,三个 union 语句之间没有依赖关系,其实是可以并行执行的:
完整的语句如下:
最后的优化效果是: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条件语句会产生笛卡尔集。 9、关于Strict ModeHive中的严格模式可以防止用户发出(可以有问题)的查询无意中造成不良的影响。 将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 [2] LanguageManual JoinOptimization https://cwiki./confluence/display/Hive/LanguageManual+JoinOptimization [3] hive 执行计划 [4] Hive SQL解析/执行计划生成流程分析 http://yanbohappy.sinaapp.com/?p=265 [5] 数据仓库中的SQL性能优化(Hive篇) http://www./html/2014/02/12/9207.html [6] Hive优化以及执行原理 http://www./upload/2014-01/14012015376829.pdf [7] Hive作业优化总结 http://my.oschina.net/yangzhiyuan/blog/262910 [8] Hive连接产生笛卡尔集 http://blog./2013/10/17/cartesian-product-in-hive-inner-join/# |
|
来自: SparkStreaming > 《hive》