HiveQL 数据查询

HiveQL 查询操作

SQL操作
•基本的Select 操作
•基于Partition的查询
•Join

基本的Select操作

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[   CLUSTER BY col_list
  | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

•使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录。DISTINCT表示去掉重复的记录
•Where 条件
•类似我们传统SQL的where条件
•目前支持 AND,OR ,0.9版本支持between
•IN,NOT IN
•不支持EXIST,NOT EXIST

ORDER BY与SORT BY的不同
•ORDER BY 全局排序，只有一个Reduce任务
•SORT BY 只在本机做排序

Limit
•Limit 可以限制查询的记录数
SELECT * FROM t1 LIMIT 5
•实现Top k 查询
•下面的查询语句查询销售记录最大的 5 个销售代表。

SET mapred.reduce.tasks = 1 
SELECT * FROM test SORT BY amount DESC LIMIT 5

REGEX Column Specification
SELECT语句可以使用正则表达式做列选择，下面的语句查询除了ds 和hr 之外的所有列：
SELECT `(ds|hr)?+.+` FROM test

基于Partition的查询
•一般 SELECT 查询会扫描整个表，使用PARTITIONED BY 子句建表，查询就可以利用分区剪枝（input pruning）的特性
•Hive 当前的实现是，只有分区断言出现在离 FROM子句最近的那个WHERE 子句中，才会启用分区剪枝

Join
语法

join_table: 
   table_reference JOIN table_factor [join_condition] 
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition 
  | table_reference LEFT SEMI JOIN table_reference join_condition 

table_reference: 
    table_factor 
  | join_table 

table_factor: 
    tbl_name [alias] 
  | table_subquery alias 
  | ( table_references ) 

join_condition: 
    ON equality_expression ( AND equality_expression )* 

equality_expression: 
    expression = expression

•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 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统
•实践中，应该把最大的那个表写在最后

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)

•如果join中多个表的 join key是同一个，则join会被转化为单个 map/reduce 任务

LEFT，RIGHT和FULL OUTER
•例子

•SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

•如果你想限制join的输出，应该在 WHERE 子句中写过滤条件——或是在join子句中写
•容易混淆的问题是表分区的情况

  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')

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)

UNION ALL
•用来合并多个select的查询结果，需要保证select中字段须一致

select_statement UNION ALL select_statement UNION ALL select_statement ...

从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;

分号字符
•分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：
select concat(key,concat(';',key)) from dual;
•但HiveQL在解析语句时提示：
FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification
•解决的办法是，使用分号的八进制的ASCII码进行转义，那么上述语句应写成：
select concat(key,concat('\073',key)) from dual;

IS [NOT] NULL
•SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.