第三部分:HiveQL 查询操作
Hive的SQL操作
•基本的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] |
1. 使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL,表示查询所有记录。DISTINCT表示去掉重复的记录。 2. Where 条件:类似我们传统SQL的where 条件 3. 0.9版本支持between,还不支持EXIST ,NOT EXIST 4. ORDER BY与SORT BY的不同:ORDER BY 全局排序,只有一个Reduce任务,SORT BY 只在本机做排序 Limit:Limit 可以限制查询的记录数,如:SELECT * FROM t1 LIMIT 5 |
•基于Partition的查询
如下面的查询语句:SELECT day_table.* FROM day_table WHERE day_table.dt>= ’2008-08-08′; dt是day_table表的分区字段。 |
•Join语句
•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 子句中写 •容易混淆的问题是表分区的情况 • 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′) 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 …
|
第四部分:注意HiveQL区别于SQL的一些情况
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.