2.3.6 hadoop体系之离线计算-Hive数据仓库-Hive查询语法

落日映苍穹つ 2022-12-15 12:39 126阅读 0赞

**目录**

1.SELECT

2.查询语法

2.1 全表查询

2.2 选择特定列

2.3 列别名

3.常用函数

4.LIMIT语句

5.WHERE语句

5.1 比较操作符

5.2 查询实例

6.LIKE 和 RLIKE

7.逻辑运算符

8.分组

8.1 GROUP BY 语句

8.2 HAVING 语句

9.join

9.1 等值join

9.2 表的别名

9.3 内连接

9.4 左外连接

9.5 右外连接

9.6 多表连接

10.排序

10.1 全局排序

10.2 按照别名排序

10.3 多个列排序

10.4 每个MapReduce内部排序（Sort By）局部排序

10.5 分区排序（DISTRIBUTE BY）

10.6 CLUSTER BY

--------------------

# 1.SELECT #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70][]

# 2.查询语法 #

## 2.1 全表查询 ##

select \* from score;

## 2.2 选择特定列 ##

select s\_id ,c\_id from score;

## 2.3 列别名 ##

1）重命名一个列。 2）便于计算。 3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

select s\_id as myid ,c\_id from score;

# 3.常用函数 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 1][]

# 4.LIMIT语句 #

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select \* from score limit 3;

# 5.WHERE语句 #

1. 使用WHERE 子句，将不满足条件的行过滤掉。 2. WHERE 子句紧随 FROM 子句。

**案例实操**

查询出分数大于60的数据 select \* from score where s\_score > 60;

## **5.1 比较操作符** ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 2][]

## 5.2 查询实例 ##

*  查询分数等于80的所有的数据 select \* from score where s\_score = 80;
 *  查询分数在80到100的所有数据 select \* from score where s\_score between 80 and 100;
 *  查询成绩为空的所有数据 select \* from score where s\_score is null;
 *  查询成绩是80和90的数据 select \* from score where s\_score in(80,90);

# 6.LIKE 和 RLIKE #

*  1. 使用LIKE运算选择类似的值
 *  2. 选择条件可以包含字符或数字：% 代表零个或多个字符(任意个字符)     \_ 代表一个字符。
 *  3.RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

**案例实操：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 3][]

# 7.逻辑运算符 #

![20201016160117160.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 4][]

# 8.分组 #

## 8.1 GROUP BY 语句 ##

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 5][]

## 8.2 HAVING 语句 ##

having与where不同点

*  1. where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
 *  2. where后面不能写分组函数，而having后面可以使用分组函数。
 *  3. having只用于group by分组统计语句。

**案例实操：**

![20201016160310170.png][]

# 9.join #

## 9.1 等值join ##

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

![20201016161253290.png][]

## 9.2 表的别名 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 6][]

## 9.3 内连接 ##

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select \* from techer t inner join course c on t.t\_id = c.t\_id;

## 9.4 左外连接 ##

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

查询老师对应的课程

select \* from techer t left join course c on t.t\_id = c.t\_id;

## 9.5 右外连接 ##

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

select \* from teacher t right join course c on t.t\_id = c.t\_id;

## 9.6 多表连接 ##

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生

select \* from teacher t **left join** course c on t.t\_id = c.t\_id **left join** score s on s.c\_id = c.c\_id **left join** student stu on s.s\_id = stu.s\_id;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表 teacher和表course进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表score进行连接操作。

# 10.排序 #

## 10.1 全局排序 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 7][]

## 10.2 按照别名排序 ##

按照分数的平均值排序

select s\_id ,avg(s\_score) avg from score group by s\_id order by avg;

## 10.3 多个列排序 ##

按照学生id和平均成绩进行排序

select s\_id ,avg(s\_score) avg from score group by s\_id order by s\_id,avg;

## 10.4 每个MapReduce内部排序（Sort By）局部排序 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 8][]

## 10.5 分区排序（DISTRIBUTE BY） ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 9][]

## 10.6 CLUSTER BY ##

当distribute by和sort by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC 或者DESC。

![2020101616451248.png][]=

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70]: /images/20221123/2c02cedff5f4494aa3ad6609a7cc1a79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 1]: /images/20221123/c273fd585c0e4523ad1bd370f26a283f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 2]: /images/20221123/b8b2303a432e47318e9feb98c4388a79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 3]: /images/20221123/9f9d103c529b439489ba2385d112fb24.png
[20201016160117160.png]: /images/20221123/0f49eec0b56b4e4e9ff42f0d81f3c093.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 4]: /images/20221123/cf0ddef986a84e7d8bb610abbf61db30.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 5]: /images/20221123/998bb7a95f114380bda522397a901217.png
[20201016160310170.png]: /images/20221123/4d272a0a82e343188d41356538dcaf8c.png
[20201016161253290.png]: /images/20221123/3e26543bf30a45dabe92424ff1e69000.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 6]: /images/20221123/92a382ba0fdd48b68c7b3e9312c382c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 7]: /images/20221123/eaa54d342a234ad4826ace3c9a9f7e02.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 8]: /images/20221123/5735ed96a5934101a7f8e5b0fad240d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1eWViaXViaXU_size_16_color_FFFFFF_t_70 9]: /images/20221123/4497d368683c4fecb8134b12940cbd60.png
[2020101616451248.png]: /images/20221123/ad911b24682e44968ccd4a39c8ee5a0f.png