hive(0.9.0):
1.支持equality joins, outer joins, and left semi joins
2.只支持等值条件
3.支持多表join
原理
hive执行引擎会将HQL“翻译”成为map-reduce任务,如果多张表使用同一列做join则将被翻译成一个reduce,否则将被翻译成多个map-reduce任务。
eg:
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)将被翻译成1个map-reduce任务
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
将被翻译成2个map-reduce任务
这个很好理解,一般来说(map side join除外,后面会介绍),map过程负责分发数据,具体的join操作在reduce完成,因此,如果多表基于不同的列做join,则无法在一轮map-reduce任务中将所有相关数据shuffle到统一个reducer
对于多表join,hive会将前面的表缓存在reducer的内存中,然后后面的表会流式的进入reducer和reducer内存中其它的表做join。
eg:
- SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
在reducer中,a、b表待join的数据会放在内存中。
这会引起一些问题,如果reducer个数不足或者a、b表数据过大,则可能oom
因此,我们需要将数据量最大的表放到最后,或者通过“STREAMTABLE”显示指定reducer流式读入的表
eg:
SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)此时,b、c表数据在reducer将放在内存中
Outer join
Outer join包括left、right、full outer join,其目的是针对不匹配的情况做一些控制。
表a:
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)LEFT OUTER JOIN:如果a.key中找不到对应的b.key,则输出a.val,NULL
LEFT OUTER JOIN可以用来代替not in(not in 在Hive0.8才支持)
eg:
select a.key from a left outer join b on a.key=b.key where b.key1 is null
实例:
- hive> select * from a ;
- OK
- key value
- 1 a
- 2 b
- 3 c
- Time taken: 0.155 seconds
- hive> select * from b;
- OK
- key value
- 1 d
- 2 e
- 4 f
- hive> SELECT a.value, b.value FROM a LEFT OUTER JOIN b ON (a.key=b.key);
- OK
- value value
- a d
- b e
- c NULL
- hive> SELECT a.value, b.value FROM a RIGHT OUTER JOIN b ON (a.key=b.key);
- OK
- value value
- a d
- b e
- NULL f
- hive> SELECT a.value, b.value FROM a FULL OUTER JOIN b ON (a.key=b.key);
- OK
- value value
- a d
- b e
- c NULL
- NULL f
Left Semi Join
hive之前(现已支持!)不支持in/exists,left semi join是in/exists更有效率的实现。
eg:
SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);可以使用如下语句代替:
SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)
Map Side Join
假如join两张表,其中有一张表特别小(可以放到内存中),那么可以使用Map-side join。Map side join是在mapper中做join,原理是将其中一张join表放到每个mapper任务的内存中,从而不用reducer任务,在mapper中就完成join。Map side join不适合FULL/RIGHT OUTER JOIN,理由大家思考下。
示例:
SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key
Bucketed Map Join
Bucketed map join是一种特殊的map side join,其针对的是所有的表都使用待join的key作为bucket列,并且bucket数量彼此有倍数关系的场景。在这种场景下,由于不需要将整张表导入内存,只需要将相应的bucket导入内存,因此,适宜一些数据量比较大的表。
例如,Table a使用key作为bucket列,共有8个bucket,Table b也是用key作为bucket列,有16个bucket,则使用Map side join,a只需要将b对应的2个bucket放入内存即可,如下:
SELECT /*+ MAPJOIN(b) */ a.key, a.valueFROM a join b on a.key = b.key
在不一点left semi join的原理:
只用B表的join字段做reduce端的过滤,感觉不是semi join这个词的意思
这里有个left semi join的explain:
STAGE PLANS: Stage: Stage-4 Conditional Operator Stage: Stage-1 Map Reduce Alias -> Map Operator Tree: t1:t TableScan alias: t Select Operator expressions: expr: dt type: string expr: regexp_extract(params, '&orderNo=([^&]*)', 1) type: string outputColumnNames: _col1, _col2 Reduce Output Operator key expressions: expr: lower(trim(_col2)) type: string sort order: + Map-reduce partition columns: expr: lower(trim(_col2)) type: string tag: 0 value expressions: expr: _col1 type: string expr: _col2 type: string t2:t TableScan alias: t Filter Operator predicate: expr: (substring(ordercreatetime, 0, 10) = '2014-11-01') type: boolean Select Operator expressions: expr: orderno type: string outputColumnNames: _col0 Group By Operator bucketGroup: false keys: expr: _col0 type: string mode: hash outputColumnNames: _col0 Reduce Output Operator key expressions: expr: lower(trim(_col0)) type: string sort order: + Map-reduce partition columns: expr: lower(trim(_col0)) type: string tag: 1 Reduce Operator Tree: Join Operator condition map: Left Semi Join 0 to 1 condition expressions: 0 {VALUE._col1} {VALUE._col2} 1 handleSkewJoin: false outputColumnNames: _col1, _col2 Select Operator expressions: expr: _col1 type: string expr: _col2 type: string outputColumnNames: _col0, _col1 File Output Operator compressed: false GlobalTableId: 0 table: input format: org.apache.hadoop.mapred.TextInputFormat output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Stage: Stage-0 Fetch Operator limit: -1
相关推荐
NULL 博文链接:https://weigang-gao.iteye.com/blog/2260663
4. Hive Join 29 5. HIVE参数设置 31 6. HIVE UDF 33 6.1 基本函数 33 6.1.1 关系操作符 33 6.1.2 代数操作符 34 6.1.3 逻辑操作符 35 6.1.4 复杂类型操作符 35 6.1.5 内建函数 36 6.1.6 数学函数 36 6.1.7 集合函数...
4. Hive Join 29 5. HIVE参数设置 31 6. HIVE UDF 33 6.1 基本函数 33 6.1.1 关系操作符 33 6.1.2 代数操作符 34 6.1.3 逻辑操作符 35 6.1.4 复杂类型操作符 35 6.1.5 内建函数 36 6.1.6 数学函数 36 6.1.7 集合函数...
facebook hive中的各种join策略的slides,reporter是个Chinese。
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 ...
hive sql + left join 数据缺失
利用Hive进行复杂用户行为大数据分析及优化案例(全套视频+课件...14_Hive中的数据倾斜及解决方案-三种join方式 15_Hive中的数据倾斜及解决方案-group by 16_Hive中使用正则加载数据 17_Hive中使用Python脚本进行预处理
Hive: Join Strategies. Facebook summit-2011
hive练习数据和hive练习题包含了hive练习数据,hive数据的建表ddl和hive练习题,非常适合做hive练习,新手培训,快速...LEFT SEMI JOIN Hive当前没有实现 IN/EXISTS 子查询,可以用 LEFT SEMI JOIN 重写子查询语句。
set hive.map.aggr = true; //是否在 Map 端进行聚合,默认为 True ;该设置会消耗更多的内存。 set hive.groupby.mapaggr.checkinterval = 100000000; //在 Map 端进行聚合操作的条目数目 set hive.groupby....
hive调优总结,网络上分享的hive常见优化细节,join、shuffle优化等等。很不错
50.Hive中的数据倾斜及解决方案-三种join方式 51.Hive中的数据倾斜及解决方案-group by 52.Hive中使用正则加载数据 53. Hive中使用Python脚本进行预处理 第5章:Zeus任务资源调度工具 54.资源任务调度框架介绍 55....
Join 查找操作的基本原则:应该将条目少的表/子查询放在 Join 操作符的左边。原因 是在 Join 操作的 Reduce 阶段,位于 Join 操作符左边的表的内容会被加载进内存,将 条目少的表放在左边,可以有效减少发生内存...
IT十八掌第三期配套课堂笔记 1、Hive工作原理、类型及特点 2、Hive架构及其文件格式 ...4、Hive的JOIN详解 5、Hive优化策略 6、Hive内置操作符与函数 7、Hive用户自定义函数接口 8、Hive的权限控制
里面 一个例子,说了几个需求,基本能符合,我要使用的功能,左外连接,还有exists替代方案,都很实用,结合文档看一下。
hive帮助文档中文版,添加了目录方便查找定位,分享出来以供交流使用。 包括hive基本结构、基本操作,select,join,参数设置等等用法
hive数据仓库的调优,大小表Join,小文件调优,实现原理
SQL left join用法,初学者应用
String hql = "select id,name from (select id from table_1 where id={p0}) t1 inner join (select name --this is name\n from table_2) t2"; // 获取id字段的血缘 LineageNode idNode = Delegate.getDelegate...
Hive提供了与HBase的集成,使得能够在HBase表上使用hive sql 语句进行查询 插入操作以及进行Join和Union等复杂查询、同时也可以将hive表中的数据映射到Hbase中