1. 几个表进行join,然后过滤 等价于 分别过滤为小表后,再join?
并不完全。
2)确实比1)效率高,
但要注意一些NULL值过滤。否则2)得到的结果比1)多
2. left join 的 不等值连接 等价于 left join where 不等值条件?
并不。
可以把不等值挪到case when中。where会丢失左表的数据
3. join 时 无on连接条件,表示的是笛卡儿积。
强行连接,m*n
4. 使用UDF,替代那些经常调用的语句。(提高代码的可维护和重复可用,与效率无关)
5. 对查询频繁使用,值的惟一性比较高的字段 设置索引。
6. 充分使用分区列,对数据进行裁剪。
7. 索引列
1)可以设置是 允许NULL,但在查询时会因为其允许使用NULL而放弃索引。
2)如果列本身含有NULL值,创建索引会失败;
8. Hive的map join 解决超大表与极小表的join问题
原理: MAPJION会把小表全部读入内存中
在map阶段直接拿另外一个表的数据和内存中表数据做匹配
由于在map是进行了join操作,省去了reduce运行的效率也会高很多
select /*+ mapjoin(A)*/ -- 通过hint的方式指定join
f.a,f.b
from A t join B f on ( f.a=t.a and f.ftime=20110802)
map join的另外一个很大的好处是:
能够进行不等值的join操作。
如果将不等条件写在where中,那么mapreduce过程中会进行笛卡尔积,运行效率特别低;
如果使用mapjoin操作,在map的过程中就完成了不等值的join操作,效率会高很多。
7. hive其他tips:
1)列裁剪: 少用*号全查询,只读取需要的列。
2)分区裁剪: 过滤掉不必要的分区。
3)数据量大的情况下,慎用count(distinct),容易产生倾斜问题。(是按group by分组,按distinct排序)
4)合并小文件
- 是否合并Map输出文件:hive.merge.mapfiles=true(默认值为真)
- 是否合并Reduce 端输出文件:hive.merge.mapredfiles=false(默认值为假)
- 合并文件的大小:hive.merge.size.per.task=256*1000*1000(默认值为 256000000)
5)join 时应该将条目少的表/子查询放在 Join 操作符的左边。
原因是在 Join 操作的 Reduce 阶段,位于 Join 操作符左边的表的内容会被加载进内存,将条目少的表放在左边,可以有效减少发生 OOM 错误的几率。
6) hive 不能做不等值join
其他的SQL 与 hive 之间的连接方式+连接条件+过滤条件 的转化,要考虑清楚其逻辑关系。数据是否一致(注意null值,重复数据等)
7) 更多请参考