八、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查询中还包括: