Hive HQL & JOIN & explode

HQL语法

HQL, Hibernate Query Language的缩写，提供更加丰富灵活、更为强大的查询能力；HQL更接近SQL语句查询语法。

1. 查询语句

在SQL中，查询语句一般为：

select
from
join
on
where 
group by
[grouping sets/with cube/with rollup]
having
order by/sort by 
limit
union/union all
;

执行顺序：-----------
FROM 
<left_table>
ON 
<join_condition>
<join_type>
 JOIN 
<right_table>
WHERE 
<where_condition>
GROUP BY 
<group_by_list>
HAVING 
<having_condition>
SELECT
DISTINCT 
<select_list>
ORDER BY 
<order_by_condition>
LIMIT 
<limit_number>

这样的形式，在hive中大部分相同
对子查询的支持也并不友好
注意事项：
尽量不要使用子查询、尽量不要使用 in 和 not in
查询尽量避免join连接查询，但是这种操作咱们是永远避免不了的。
查询永远是小表驱动大表（永远是小结果集驱动大结果集）
几种常见的join：
内连接（inner join）
外连接（outer join）
左连接（left join）从hive0.8开始
右连接（right join）
全连接（full join）

上周，我读到一篇文章，认为还有比维恩图更好的解释方式。我发现确实如此，换一个角度解释，更容易懂。

所谓"连接"，就是两张表根据关联字段，组合成一个数据集。问题是，两张表的关联字段的值往往是不一致的，如果关联字段不匹配，怎么处理？比如，表 A 包含张三和李四，表 B 包含李四和王五，匹配的只有李四这一条记录。

很容易看出，一共有四种处理方法。
只返回两张表匹配的记录，这叫内连接（inner join）。
返回匹配的记录，以及表 A 多余的记录，这叫左连接（left join）。
返回匹配的记录，以及表 B 多余的记录，这叫右连接（right join）。
返回匹配的记录，以及表 A 和表 B 各自的多余记录，这叫全连接（full join）。

下图就是四种连接的图示。我觉得，这张图比维恩图更易懂，也更准确。

上图中，表 A 的记录是 123，表 B 的记录是 ABC，颜色表示匹配关系。返回结果中，如果另一张表没有匹配的记录，则用 null 填充。

这四种连接，又可以分成两大类：内连接（inner join）表示只包含匹配的记录，外连接（outer join）表示还包含不匹配的记录。所以，左连接、右连接、全连接都属于外连接。

此外，还存在一种特殊的连接，叫做"交叉连接"（cross join），指的是表 A 和表 B 不存在关联字段，这时表 A（共有 n 条记录）与表 B （共有 m 条记录）连接后，会产生一张包含 n x m 条记录的新表（见下图）。

常用：
left join
inner join

left join:以左表为基本表，右表关联不上用null替代
left join \ left semi join \ left outer join

left semi join 和 left join 区别：
1、都是左表连接，但是semi join右表关联不左表也不会出来，left join不一样
2、semi join只能查询左表信息，left join可以查询所有
3、semi join是left join的一种优化
4、semi join一般使用查询存在的情况

1.1 表查询

准备阶段：
准备数据
数据表hivedata1

1,a
2,b
3,c
4,d
7,y
8,u

数据表hivedata2

2,bb
3,cc
7,yy
9,pp

建表语句

create table if not exists u1(
id int,
name string
)
row format delimited fields terminated by ','
;

create table if not exists u2(
id int,
name string
)
row format delimited fields terminated by ','
;

导入数据

load data local inpath '/home/hivedata1' into table u1;
load data local inpath '/home/hivedata2' into table u2