从SQL到HiveQL应改变的几个习惯

HiveQL非常像SQL，但二者并非等价，若不注意期间的一些差异，容易导致HiveQL的语义错误，或降低运行效率等问题。本文将逐步汇集HiveQL应用中发现的差异，整理与此以便查阅。更多HiveQL的语法知识见http://wiki.apache.org/Hadoop/Hive/LanguageManual

1,别名

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;

2,虚拟列

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表的所有分区，有多少天就查多少天，那个场景无法想象

陷阱 select * from r_winner_details r join t_users s on r.seller_id=s.user_id where r.pt='20091029000000' 因为上句的含义是将r_winner_details表的数据与t_users表数据卖家的数字id进行join, 之后筛选出pt为1029那天的分区结果. 因此语句一旦运行, 会导致r_winner_details所有分区数据被加载, 导致Map数上万. 请切记, 正确的写法是: select * from (select * from r_winner_details where pt='20091029000000') r join t_users s on r.seller_id=s.user_id

3,IN

SQL中可以使用IN操作符来规定多个值：

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');

HiveQL目前是不支持IN操作符的，需要通过转换为多个OR连接的条件：

SELECT * FROM Persons WHERE LastName = 'Adams' OR LastName = 'Carter';

4,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;

OutOfMemory 在JOIN的实践中，时常发现OutOfMemory的异常，或是task"跑不动"的情况，总结发现，一旦这类问题出现，他们JOIN的key的值多半是异常的（乱码或是null），因此在应用的过程尤其要注意过滤掉异常的key数据。 若不是异常数据，可尝试调换join两表前后顺序解决。

5,分号字符

分号是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码进行转义，那么上述语句应写成：

SELECT concat(property,concat('\073',zoo))  FROM auctions;

八进制ASCII码 本人尝试了用十六进制的ASCII码，但Hive会将其视为字符串处理并未转义，貌似仅支持八进制，原因不详。这个规则也适用于其他非SELECT语句，如CREATE TABLE中需要定义分隔符，那么对不可见字符做分隔符就需要用八进制的ASCII码来转义。

6,Insert

HiveQL中Insert的作用不同于SQL中的, 那么通过HiveQL中实现新增数据该如何做呢?
假设Hive中有表p1,

hive> DESCRIBE p1;OKid      intvalue   int

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的应用, 即将原有数据集和新增数据集进行结合, 然后重写表.

列陷阱 次序 INSERT OVERWRITE TABLE在插入数据时,是按照后面的SELECT语句中的字段顺序插入的. 也就说, 当id 和value 的位置互换, 那么value将被写入id, 同id被写入value. 初始值 INSERT OVERWRITE TABLE在插入数据时, 后面的字段的初始值应注意与表定义中的一致性. 例如, 当为一个STRING类型字段初始为NULL时: NULL AS field_name // 这可能会被提示定义类型为STRING, 但这里是void CAST(NULL AS STRING) AS field_name // 这样是正确的 又如, 为一个BIGINT类型的字段初始为0时: CAST(0 AS BIGINT) AS field_name

7,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

Full or Left Merge是选择Full Outer Join还是Left Outer Join? 这需要开发者对更新的数据比较了解. 通常, 要更新的数据若只有对已有数据的部分更新, 则应选用Left Outer Join; 而当要更新的数据有新数据需要增加时, 则应选用Full Outer Join.

8,IS [NOT] NULL

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