09 hive查询语法

一、select语句

 

1.1  基本语法

 

# 基本语法
SELECT [ALL | DISTINCT]select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING where_condition]
[ORDER BY col_list]
[CLUSTER BY col_list
  | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

 

解释（掌握）:

1、ORDER BY用于全局排序，就是对指定的所有排序键进行全局排序，使用ORDER BY的查询语句，最后会用一个Reduce Task来完成全局排序。

2、sort by用于分区内排序，即每个Reduce任务内排序。，则sort by只保证每个reducer的输出有序，不保证全局有序。局部排序，但是reduce只有一个话和order by功能一样，做全局排序

3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。类似MR的k2进行分区

4、cluster by(字段) 除了具有Distribute by的功能外，还兼具sort by的排序功能。

因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by 。 如果distribute by字段和sort by字段等价于cluster by字段 ，只能升序排列，不能降序排列。

 

 

---

---

 

二、运算符

 

2.1 比较运算符

 

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING 类型	B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

# 查找成绩中含9的所有成绩数据 select * from scorewhere cast(s_score as string) rlike'[9]';

 

 

2.2逻辑运算符

 

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

 

---

 

三、分组

 

当使用分组操作，在 select 之后 和 from 之前的，如果想使用某一个字段，必须在group by存在的字段，否则会报错。

 

# 分组字段中必须要有查询的字段 select c_id from score group by s_id,c_id; # 可以再聚合函数中，使用其他字段 select count(c_id) from score group by s_id;

 

1、having与where不同点

（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。

（2）where后面不能写分组函数，而having后面可以使用分组函数。

（3）having只用于group by分组统计语句。

---

 

 

四、关联操作Join

 

JOIN语句右外连接（RIGHT OUTER JOIN）

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

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

 

内连接（INNER JOIN）

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

select * from teacher t inne join course c on t.t_id = c.t_id;

 

左外连接（LEFT OUTER JOIN）

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

查询老师对应的课程

select * from teacher t leftjoin course c on t.t_id = c.t_id;

 

满外连接（FULL OUTER JOIN）

       满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

SELECT * FROM teacher t FULL JOIN course c ON t.t_id = c.t_id ;

 

多表连接

注意：连接 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;进行连接操作。

 

 

---

 

 

五、排序

 

5.1 Order By-全局排序

 

Order By：全局排序，一个reduce

 

 

5.2 Sort By-每个MapReduce内部局部排序（开启本地模式）

 

Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序。

1)设置reduce个数  set mapreduce.job.reduces=3; 2)查看设置reduce个数  set mapreduce.job.reduces; 3）查询成绩按照成绩降序排列  select * from score sort by s_score; 4)将查询结果导入到文件中（按照成绩降序排列）  insert overwrite local directory '/export/server/hivedatas/sort' select * from score sort by s_score;

 

5.3   distribute By-分区排序

Distribute By：类似MR中partition，进行分区，结合sort by使用。

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

 

案例

先按照学生id进行分区，再按照学生成绩进行排序。

1)设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去 set mapreduce.job.reduces=7; 2)通过distribute by进行数据的分区 insert overwrite local directory '/export/server/hivedatas/sort'  select * from score distribute by s_id sort by s_score;

 

5.4 Cluster By

cluster by除了具有distribute by的功能外还兼具sort by的当distribute by和sort by字段相同时，可以使用cluster by方式。

功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。

 

以下两种写法等价:

select * from score cluster by s_id;  select * from score distribute by s_id sort by s_id;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---