Hive查询基础(SELECT)_hive select

转载自 lxw1234@qq.com的博客

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

基础查询语法
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]
1
2
3
4
5
6
7
8
9

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

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
1
2
3
4
5
6
7
8
9

使用ORDER BY

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

使用SORT BY

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

设定了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
1
2
3
4
5
6
7
8
9

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

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;
1
2
3
4

执行后在本地的/tmp/lxw1234目录中生成了000000_0和000001_0两个文件：

cat 000000_0
8
2
6

cat 000001_0
1
9
3
5
1
2
3
4
5
6
7
8
9
10

##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
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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

SELECT col
FROM (
  SELECT a+b AS col
  FROM t1
) t2
1
2
3
4
5

另外，从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)
1
2
3
4
5
6
7

虚拟列
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
1
2
3
4
5
6
7
8