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

最新推荐文章于 2023-08-05 22:39:05 发布

weixin_34112900

最新推荐文章于 2023-08-05 22:39:05 发布

阅读量105

点赞数

文章标签： python 大数据

原文链接：https://my.oschina.net/zhongl/blog/32492

版权

2019独角兽企业重金招聘Python工程师标准>>>

2009-10-30 @ taobao

引言

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

陷阱

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

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;

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

分号字符

分号是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码来转义。

Insert

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

hive> DESCRIBE p1;
OK
id      int
value   int

hive> SELECT * FROM p1;
OK
3       4
1       2
2       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;                                
OK
3       4
4       5
2       3
1       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

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;
OK
4       4
2       2
5       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 value FROM p1 limit 1
          ) u 
       ) n ON o.id=n.id;

hive> SELECT * FROM p1;
OK
1       2
2       2
3       4
4       4
5       6

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