HiveQL:https://cwiki.apache.org/confluence/display/Hive/LanguageManual
引言
HiveQL非常像SQL,但二者并非等价,若不注意期间的一些差异,容易导致HiveQL的语义错误,或降低运行效率等问题。本文将逐步汇集HiveQL应用中发现的差异,整理与此以便查阅。更多HiveQL的语法知识见http://wiki.apache.org/hadoop/Hive/LanguageManual
别名SQL中对列取别名,可如下应用:
SELECT user_type type FROM s_auction_auctions;但Hive解析上述语句时,会提示:
FAILED: Parse Error: line 1:7 cannot recognize input 'user_type' in select expression
此时,应改为:
SELECT user_type AS type FROM s_auction_auctions;虚拟列
Hive中有个"虚拟列"的概念,此列并未在表中真正存在,其用意是为了将Hive中的表进行分区(partition),这对每日增长的海量数据存储而言是非常有用的。为了保证HiveQL的高效运行,强烈推荐在where语句后使用虚拟列作为限定。拿web日志举例,在Hive中为web日志创建了一个名为web_log表,它有一个虚拟列logdate,web_log表通过此列对每日的日志数据进行分区。因此,在对web_log表执行select时,切记要在where后加上logdate的限定条件,如下:
SELECT url FROM web_log WHERE logdate='20090603';若是没有logdate作为限定,Hive默认查询web_log表的所有分区,有多少天就查多少天,那个场景无法想象
IN
SQL中可以使用IN操作符来规定多个值:
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');HiveQL目前是不支持IN操作符的,需要通过转换为多个OR连接的条件:
SELECT * FROM Persons WHERE LastName = 'Adams' OR LastName = 'Carter';INNER JOIN
SQL中对两表内联可以写成:
SELECT a.col, b.col FROM t1 a, t2 b WHERE a.id=b.id;但这在HiveQL中是不支持的,需转为JOIN关键字的写法,如:
SELECT a.col, b.col FROM t1 a JOIN t2 b ON a.id=b.id;分号字符
分号是SQL语句结束标记,在HiveQL中也是,但是在HiveQL中,对分号的识别没有那么智慧,例如:
SELECT concat(property,concat(';',zoo)) FROM auctions;这个语句尝试将商品表中的两个属性相关的字段值用分号进行连接,但HiveQL在解析语句时提示:
FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification
可以推断,Hive解析语句的时候,只要遇到分号就认为语句结束,而无论是否用引号包含起来。
解决的办法是,使用分号的八进制的ASCII码进行转义,那么上述语句应写成:
Insert
HiveQL中Insert的作用不同于SQL中的, 那么通过HiveQL中实现新增数据该如何做呢?
假设Hive中有表p1,
hive> SELECT * FROM p1;OK3 41 22 3
现增加一条记录:
hive> INSERT OVERWRITE TABLE p1 SELECT id, value FROM ( SELECT id, value FROM p1 UNION ALL SELECT 4 AS id, 5 AS value FROM p1 limit 1 ) u;结果是:
hive>SELECT * FROM p1; OK3 44 52 31 2其中的关键在于, 关键字UNION ALL的应用, 即将原有数据集和新增数据集进行结合, 然后重写表.
Merge
现在通过下列语句模拟需要Merge的数据集:
hive> SELECT id, value FROM ( SELECT id, (value-1) AS value FROM p1 WHERE (id%2)=0 UNION ALL SELECT 5 AS id, 6 AS value FROM p1 limit 1 ) u;OK4 42 25 6下面用这个结果集合并到p1中:
hive> INSERT OVERWRITE TABLE p1 SELECT coalesce(n.id, o.id), coalesce(n.value, o.value) FROM p1 o FULL OUTER JOIN ( SELECT id, value FROM ( SELECT id, (value-1) AS value FROM p1 WHERE (id%2)=0 UNION ALL SELECT 5 AS id, 6 AS valueFROM p1 limit 1 ) u ) n ON o.id=n.id;hive> SELECT * FROM p1;OK1 22 23 44 45 6
IS [NOT] NULL
SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.