首页 > 代码库 > hive 笔记
hive 笔记
下面以sales和things表为例。这两个表定义如下：
hive> SELECT * FROM sales;
Joe 2
Hank 4
Ali 0
Eve 3
Hank 2
hive> SELECT * FROM things;
2 Tie
4 Coat
3 Hat
1 Scarf
1. Inner joins
hive> SELECT sales.*, things.*
> FROM sales JOIN things ON (sales.id = things.id);
Joe 2 2 Tie
Hank 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
以集合论的语言描述，可以表示为：
也就说，只有两个表匹配上，有交集，才会出现在最终结果中。
2. LEFT OUTER JOIN
hive> SELECT sales.*, things.*
> FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);
Ali 0 NULL NULL
Joe 2 2 Tie
Hank 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
以集合论的语言描述，可以表示为：
也就是说，左表（sales）和右表（things）有交集的，会出现在最终结果里，如上面图中的黑色区域。此外，出现在sales（图中红色的部分），但是没有出现在things，最终结果会出现sales的记录，而things的部分则以NULL补充。
3. RIGHT OUTER JOIN
hive> SELECT sales.*, things.*
> FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);
NULL NULL 1 Scarf
Joe 2 2 Tie
Hank 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
以集合论的语言描述，可以表示为：
RIGHT OUTER JOIN只是将LEFT OUTER JOIN的两个表对换而已。
4. FULL OUTER JOIN
hive> SELECT sales.*, things.*
> FROM sales FULL OUTER JOIN things ON (sales.id = things.id);
Ali 0 NULL NULL
NULL NULL 1 Scarf
Joe 2 2 Tie
Hank 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat
以集合论的语言描述，可以表示为：
FULL OUTER JOIN可以理解为LEFT OUTER JOIN + RIGHT OUTER JOIN。
5. LEFT SEMI JOIN
Hive doesn’t support IN subqueries (at the time of this writing), but you can use a LEFT SEMI JOIN to do the same thing.
Consider this IN subquery, which finds all the items in the things table that are in the sales table:
SELECT *
FROM things
WHERE things.id IN (SELECT id from sales);
We can rewrite it as follows:
hive> SELECT *
> FROM things LEFT SEMI JOIN sales ON (sales.id = things.id);
2 Tie
3 Hat
4 Coat
从LEFT SEMI JOIN的定义看，只有出现在sales中的id，才会出现在最终结果里。且由于采用的是IN关键字，因此，从IN (SELECT id from sales)出来的id已经是去过重的。
6. Map joins
hive> SELECT /*+ MAPJOIN(things) */ sales.*, things.*
> FROM sales JOIN things ON (sales.id = things.id);
Joe 2 2 Tie
Hank 4 4 Coat
Eve 3 3 Hat
Hank 2 2 Tie
Map joins主要是一种优化操作，通过避免reduce端的操作，加速job运行时间。实际中，如何一个表的大小在几十M，均可以通过map join加速。
Map join的实际操作应该是，将小表通过分布式缓存发布到集群上。每个mapper将该小表的join key加载到自己的内存中，通过类似hash表的方式进行存储。Mapper每读进一条记录，就会查查当前记录的key是否在该hash表中，如果在，则输出。这样就避免了reduce的操作。
7. UNION ALL
假设表sales用示意图描述如下，因为表本来就是关系，因此用矩形表示：
things表如下：
则UNION ALL的效果如下：
也就是将两张表追加到了一起。
8. Subqueries
需要注意的是hive只支持在FROM子句中使用Subqueries。
因为Subqueries子查询相对复杂，这里重点介绍下如何写好Subqueries。
以下是一个复杂的hive例子：
SELECT * FROM
(
SELECT uid,sessionid,stepid,time,position,source,action,request,response,cellphone,other FROM log_route_car_filtered WHERE dt=‘20140517‘
UNION ALL
SELECT client.uid,client.sessionid,client.stepid,client.time,client.position,client.source,client.action,
client.request,client.response,client.cellphone,client.other
FROM log_client_filtered client
WHERE (dt=‘20140517‘ AND other[‘date‘]=‘20140517‘)
OR (dt=‘20140518‘ AND other[‘date‘]=‘20140517‘)
LEFT SEMI JOIN
(
SELECT DISTINCT uid, sessionid FROM log_route_car_filtered WHERE dt=‘20140517‘
) unique_route
ON (client.uid=unique_route.uid AND client.sessionid=unique_route.sessionid)
) merge
WHERE uid RLIKE ‘^[\\w-]+$‘
DISTRIBUTE BY uid
SORT BY uid,sessionid,CAST(stepid AS INT),time;
为了方便叙述，我用不同的颜色标记了query的主要结构。
外头的SELECT * FROM从一个复杂的UNION ALL结果中提取结果，并通过WHERE，DISTRIBUTE BY和SORT BY对结果进行过滤。
这里的主要问题是UNION ALL后的SELECT语句存在问题，会导致hive执行报错，而且错误信息很模糊。
因此我们把这部分结果单独拿出来进行分析：
SELECT client.uid,client.sessionid,client.stepid,client.time,client.position,client.source,client.action,
client.request,client.response,client.cellphone,client.other
FROM log_client_filtered client
WHERE (dt=‘20140517‘ AND other[‘date‘]=‘20140517‘)
OR (dt=‘20140518‘ AND other[‘date‘]=‘20140517‘)
LEFT SEMI JOIN
(
SELECT DISTINCT uid, sessionid FROM log_route_car_filtered WHERE dt=‘20140517‘
) unique_route
ON (client.uid=unique_route.uid AND client.sessionid=unique_route.sessionid)
LEFT SEMI JOIN的语法框架如下：
SELECT * FROM tabel1 LEFT SEMI JOIN table2 ON (tabel1.id = tabel2.id);
不包括WHERE子句，但在这里我们又想通过WHERE子句过滤包含指定日期的结果，怎么办呢？
这时我们可以将WHERE子句包装进一个Subquery，如下：
SELECT * FROM
(
SELECT uid,sessionid,stepid,time,position,source,action,.request,response,cellphone,other
FROM log_client_filtered
WHERE (dt=‘20140517‘ AND other[‘date‘]=‘20140517‘) OR (dt=‘20140518‘ AND other[‘date‘]=‘20140517‘)
) client
LEFT SEMI JOIN
(
SELECT DISTINCT uid, sessionid FROM log_route_car_filtered WHERE dt=‘20140517‘
) unique_route
ON (client.uid=unique_route.uid AND client.sessionid=unique_route.sessionid)
这样一个查询就满足了LEFT SEMI JOIN的语法结构，问题解决。
PS：这里我只说明了map join的操作原理，大家可以想想其它join操作的实现过程，应该都是不难的，网上资料也很多。
hive 笔记
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。