微信搜索lxw1234bigdata | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 赞助作者:赞助作者

[一起学Hive]之九-Hive的查询语句SELECT

Hive lxw1234@qq.com 33810℃ 2评论

关键字:Hive SELECT、ORDER BY、SORT BY、DISTRIBUTE BY、CLUSTER BY、Hive子查询、Hive虚拟列

八、Hive的查询语句SELECT

在所有的数据库系统中,SELECT语句是使用最多,也最复杂的一块,Hive中的查询语句SELECT支持的语法当然也比较复杂,本文只能尽力去介绍。

8.1 基础查询语法

Hive中的SELECT基础语法和标准SQL语法基本一致,支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等;
语法如下:

[WITH CommonTableExpression (, CommonTableExpression)*]  
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[CLUSTER BY col_list
  | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

下面介绍Hive中比较特殊的一些查询语法。

8.2 ORDER BY和SORT BY

ORDER BY用于全局排序,就是对指定的所有排序键进行全局排序,使用ORDER BY的查询语句,最后会用一个Reduce Task来完成全局排序。
SORT BY用于分区内排序,即每个Reduce任务内排序。
看下面的例子:
原表数据为:

hive> select * from lxw1234_com;
OK
5
3
6
2
9
8
1

使用ORDER BY

hive> select * from lxw1234_com order by id;
1
2
3
5
6
8
9

使用SORT BY

hive> set mapred.reduce.tasks=2;
hive> select * from lxw1234_com sort by id;
2
5
6
9
1
3
8

设定了2个reduce,从结果可以看出,每个reduce内做了排序。
如果reduce数为1,那么ORDER BY和SORT BY的结果是一样的:

hive> set mapred.reduce.tasks=1;
hive> select * from lxw1234_com sort by id;
1
2
3
5
6
8
9

真实业务环境中,我们的需求大多需要使用ORDER BY全局排序来完成。

8.3 DISTRIBUTE BY和CLUSTER BY

distribute by:按照指定的字段或表达式对数据进行划分,输出到对应的Reduce或者文件中。
cluster by:除了兼具distribute by的功能,还兼具sort by的排序功能。

##distribute by

hive> set mapred.reduce.tasks=2;
hive>INSERT overwrite LOCAL directory '/tmp/lxw1234/' 
SELECT id FROM lxw1234_com 
distribute BY id;

执行后在本地的/tmp/lxw1234目录中生成了000000_0和000001_0两个文件:
cat 000000_0
8
2
6
cat 000001_0 ##id%2=1的记录
1
9
3
5

##cluster by

hive> set mapred.reduce.tasks=2;
hive> INSERT overwrite LOCAL directory '/tmp/lxw1234/' 
SELECT id FROM lxw1234_com 
CLUSTER BY id;
cat 000000_0
2
6
8
cat 000001_0
1
3
5
9

注意:使用cluster by之后,每个文件中的id都进行了排序,而distribute by没有。

8.4 子查询

子查询和标准SQL中的子查询语法和用法基本一致,需要注意的是,Hive中如果是从一个子查询进行SELECT查询,那么子查询必须设置一个别名。

SELECT col
FROM (
  SELECT a+b AS col
  FROM t1
) t2

另外,从Hive0.13开始,在WHERE子句中也支持子查询,比如:

SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);

SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

还有一种将子查询作为一个表的语法,叫做Common Table Expression(CTE):

with q1 as (select * from src where key= '5'),
q2 as (select * from src s2 where key = '4')
select * from q1 union all select * from q2;

with q1 as ( select key, value from src where key = '5')
from q1
insert overwrite table s1
select *;

8.5 虚拟列

Hive查询中有两个虚拟列:
INPUT__FILE__NAME:数据对应的HDFS文件名;
BLOCK__OFFSET__INSIDE__FILE:该行记录在文件中的偏移量;

hive> select id,INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE from lxw1234_com;
5       hdfs://cdh5/tmp/lxw1234/1.txt   0
3       hdfs://cdh5/tmp/lxw1234/1.txt   2
6       hdfs://cdh5/tmp/lxw1234/1.txt   4
2       hdfs://cdh5/tmp/lxw1234/1.txt   6
9       hdfs://cdh5/tmp/lxw1234/1.txt   8
8       hdfs://cdh5/tmp/lxw1234/1.txt   10
1       hdfs://cdh5/tmp/lxw1234/1.txt   12

Hive查询中还包括:
Hive的内置操作和函数: http://lxw1234.com/archives/2015/06/251.htm
Hive窗口分析函数:http://lxw1234.com/archives/tag/hive-window-functions

 

 

Hive相关文章(持续更新)

一起学Hive系列

—-Hive概述,Hive是什么

—-Hive函数大全-完整版

—-Hive中的数据库(Database)和表(Table)

—-Hive的安装配置

—-Hive的视图和分区

—-Hive的动态分区

—-向Hive表中加载数据

Hive分析函数系列

Hive索引

hive优化之——控制hive任务中的map数和reduce数

 

 

如果觉得本博客对您有帮助,请 赞助作者

转载请注明:lxw的大数据田地 » [一起学Hive]之九-Hive的查询语句SELECT

喜欢 (13)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(2)个小伙伴在吐槽
  1. 博主,请教一下啊,8.3中 distribute by 和cluster by 那一节中,设置reduce数目为2之后,结果就按照偶数奇数分类了,请问这个id%2=1 是从哪里来的?请指教 :smile:
    cady20152016-08-29 10:09 回复
    • 即:Hash(id)%reduceNum
      lxw1234@qq.com2016-08-29 13:31 回复