文档章节

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

zhongl
 zhongl
发布于 2011/09/23 11:00
字数 1550
阅读 14254
收藏 10

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.

IS [NOT] NULL

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

© 著作权归作者所有

上一篇: MAPJOIN
下一篇: HiveQL 初级实战
zhongl

zhongl

粉丝 98
博文 28
码字总数 16630
作品 5
杭州
高级程序员
私信 提问
Hadoop Hive sql 语法详解

Hive 是基于Hadoop 构建的一套数据仓库分析系统,它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据,可以将结构化的数据文件映射为一张数据库表,并提供完整的SQL查询功...

luanpeng825485697
2018/05/30
0
0
Hive学习总结之四:HiveQL查询操作

第三部分:HiveQL 查询操作 Hive的SQL操作 •基本的Select 操作 SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM table_reference [WHERE where_condition] [GROUP BY col_list ......

酷帅CoolBash
2013/01/30
1K
0
Impala SQL 语言参考

Impala SQL 语言参考 Cloudera Impala 的查询语言是基于 SQL 的。为了保护用户在技能和查询设计方面的已有投资,Impala 提供与 Hive 查询语言(HiveQL)的高度兼容: 因为使用与 Hive 记录表结...

weiqingbin
2013/12/20
11.1K
1
Java私塾:Hive QL——深入浅出学Hive

目录: 初始Hive Hive安装与配置 Hive 内建操作符与函数开发 Hive JDBC hive参数 Hive 高级编程 Hive QL Hive Shell 基本操作 hive 优化 Hive体系结构 Hive的原理 配套视频课程 第一部分:D...

sunfei
2013/12/10
874
0
Hive 基础(2):库、表、字段、交互式查询的基本操作

1、命令行操作 (1)打印查询头,需要显示设置: set hive.cli.print.header=true; (2)加"--",其后的都被认为是注释,但 CLI 不解析注释。带有注释的文件只能通过这种方式执行: hive -f ...

大数据之路
2014/06/02
17.2K
0

没有更多内容

加载失败,请刷新页面

加载更多

玩转 Springboot 2 之热部署(DevTools)

Devtools 介绍 SpringBoot 提供了热部署的功能,那啥是热部署累?SpringBoot官方是这样说的:只要类路径上的文件发生更改,就会自动重新启动应用程序。在IDE中工作时,这可能是一个有用的功能...

桌前明月
39分钟前
3
0
CSS--列表

一、列表标识项 list-style-type none:去掉标识项 disc:默认实心圆 circle:空心圆 squire:矩形 二、列表项图片 list-style-img: 取值:url(路径) 三、列表项位置 list-style-position:...

wytao1995
今天
10
0
linux 命令-文本比较comm、diff、patch

本文原创首发于公众号:编程三分钟 今天学了三个文本比较的命令分享给大家。 comm comm 命令比较相同的文本 $ cat charabc$ cat chardiffadc 比如,我有两个文件char和chardiff如上,...

编程三分钟
今天
9
0
QML教程

https://blog.csdn.net/qq_40194498/article/category/7580030 https://blog.csdn.net/LaineGates/article/details/50887765...

shzwork
今天
7
0
HA Cluster之5

对于使用heartbeat v2版的CRM配置的集群信息都是保存在一个名为cib.xml的配置文件中,存放在/var/lib/heartbeat/crm/下。CIB:Cluster Information Base,由于xml文件配置不是那么方便,所以...

lhdzw
今天
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部