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

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

阅读量：4208 次

发布时间：2019-05-26

本文共 2863 字，大约阅读时间需要 9 分钟。

八、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个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

cat 000001_0 ##id%2=1的记录

##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查询中还包括：

转载地址：http://rvrli.baihongyu.com/

你可能感兴趣的文章