Hive----join语句

有A表和B表，共有字段id、name，交叉部分（黄色）表示id相同：

• 查询各颜色部分数据：

1. 红：select       * from A left join B on A.id=B.id where B.id is null ;
2. 绿：select * from A right       join B on A.id=B.id where       A.id is null ;
3. 黄：select * from A join B on A.id=B.id ;
4. 红+黄：select * from A left       join B on A.id=B.id ;
5. 绿+黄：select * from A rigth       join B on A.id=B.id ; 【or】select * from B left       join A on A.id=B.id ;
6. 红+绿：select * from A full       outer join B on A.id=B.id where A.id       is null or      B.id is null ;
7. 红+黄+绿：select * from A full       outer join B on A.id=B.id ;

• left semi       join

• select  A.id,A.value from A  B on A.id=B.id ;
• <==>
• select  A.id,A.value from A where  A.id in (select B.id from B);
•  
•  
•  

• cross join

• CROSS  JOIN是sql中的一种连接方式，区别于内连接和外连接，对于cross join连接来说，其实使用的就是笛卡尔连接。在SQL中，当CROSS  JOIN不使用WHERE子句时，CROSS JOIN产生了一个结果集，该结果集是两个关联表的行的乘积。通常，如果每个表分别具有n和m行，则结果集将具有n*m行；

----------------------------------------------------------------------------------------------------------

• 谈谈如何对join操作进行优化？
• join优化是个复杂的问题，可以从以下几点进行优化：
• 1）小表前置
• 大小表在join的时候，应该将小表放在前面，Hive在解析带join的SQL语句时，会默认将最后一个表作为大表，将前面的表作为小表并试图将它们读进内存。如果表顺序写反，大表在前面，可能会引发OOM。
• 2）key值相同
• 多表join的时候尽量使用相同的key来关联，这样会将会将多个join合并为一个MR job来处理。
• 3）利用map join特性
• map  join特别适合大小表join的情况。Hive会将大表和小表在map端直接完成join过程，消灭reduce，效率很高。Hive 0.8版本之前，需要加上map  join的暗示，以显式启用map join特性，具体做法是在select语句后面增加/*+mapjoin(需要广播的较小表)*/。
• map  join的配置项是hive.auto.convert.join，默认值true；还可以控制map join启用的条件，hive.mapjoin.smalltable.filesize，当较小表大小小于该值就会启用map join，默认值25MB。

两表join要考虑两个问题：

1. 根据所需求的数据范围选择用什么join（主要是join、left join、full outer join）
2. 根据两表的粒度判断：如果是大粒度join小粒度而取大粒度，要防止出现数据扩容，需要对大表聚合去重；剩下的情况可不用考虑

案例：

本案例中使用了 union all （查询合并）、distinct（去重）、cross join（笛卡尔积）

SELECT

b.class,a.blood,COUNT(s.id) AS num

FROM

(SELECT 'A'  AS blood UNION ALL

SELECT 'B' UNION ALL

SELECT 'C' UNION ALL

SELECT 'D' )a

CROSS JOIN

(SELECT  DISTINCTclass  from stud) b

LEFT JOIN stud s

ON a.blood=s.blood AND s.class=b.class

GROUP BY b.class,a.blood

order by b.class,a.blood;