HIVE常用语句示例

（ 1 ）按页面类型统计 pv
select pageType, count(pageType) from ext_trackflow where statDate = '20140521' group by pageType;
Note ：一般 SELECT 查询会扫描整个表，使用 PARTITIONED BY 子句建表，查询就可以利用分区剪枝（ input pruning ）的特性
Hive 当前的实现是，只有分区断言出现在离 FROM 子句最近的那个 WHERE 子句中，才会启用分区剪枝

（ 2 ）导出查询结果到本地的两种方式
INSERT OVERWRITE LOCAL DIRECTORY '/home/jun06/tmp/110.112.113.115' select area1, count(area1) from ext_trackflow where statDate = '20140521' group by area1 having count(area1) > 1000;

hive -e 'use ecdata; select area1, count(area1) from ext_trackflow where statDate = '20140521' group by area1 having count(area1) > 1000;' > a.txt

（ 3 ） map 数据结构的查询与使用
select trackURLMap, extField, unitParamMap, queryParamMap from ext_trackflow where statDate = '20140525' and size(unitParamMap)!=0 limit 10;

（ 4 ）下面的查询语句查询销售记录最大的 5 个销售代表。
SET mapred.reduce.tasks = 1;
SELECT * FROM test SORT BY amount DESC LIMIT 5;

（ 5 ）将同一表中数据插入到不同表、路径中：
FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

（ 6 ）用 streaming 方式将文件流直接插入文件：
hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';

（ 7 ） Hive 只支持等值连接（ equality joins ）、外连接（ outer joins ）和（ left semi joins ）。 Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务

LEFT ， RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况
LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现
join 时，每次 map/reduce 任务的逻辑是这样的： reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统
实践中，应该把最大的那个表写在最后
（ 8 ） join 查询时，需要注意几个关键点
只支持等值 join
SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b
ON (a.id = b.id AND a.department = b.department)
可以 join 多于 2 个表，例如
SELECT a.val, b.val, c.val FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
Note ：如果 join 中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务
（ 9 ） LEFT ， RIGHT 和 FULL OUTER
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写
容易混淆的问题是表分区的情况
SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key)
WHERE a.ds='2010-07-07' AND b.ds='2010-07-07'
如果 d 表中找不到对应 c 表的记录， d 表的所有列都会列出 NULL ，包括 ds 列。也就是说， join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。这样的话， LEFT OUTER 就使得查询结果与 WHERE 子句无关
解决办法
SELECT c.val, d.val FROM c LEFT OUTER JOIN d
ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07')
（ 10 ） LEFT SEMI JOIN
LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、 SELECT 子句或其他地方过滤都不行
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)
（ 11 ）从 SQL 到 HiveQL 应转变的习惯
① Hive 不支持传统的等值连接
• SQL 中对两表内联可以写成：
• select * from dual a,dual b where a.key = b.key;
• Hive 中应为
• select * from dual a join dual b on a.key = b.key;
而不是传统的格式：
SELECT t1.a1 as c1, t2.b1 as c2FROM t1, t2
WHERE t1.a2 = t2.b2
②分号字符
•分号是 SQL 语句结束标记，在 HiveQL 中也是，但是在 HiveQL 中，对分号的识别没有那么智慧，例如：
• select concat(key,concat(';',key)) from dual;
•但 HiveQL 在解析语句时提示：
FAILED: Parse Error: line 0:-1 mismatched input '' expecting ) in function specification
•解决的办法是，使用分号的八进制的 ASCII 码进行转义，那么上述语句应写成：
• select concat(key,concat(' \073 ',key)) from dual;