一、课前准备
1. 安装hive环境
2. 掌握hive sql DDL和DML操作
3. 掌握hive sql 的分区表创建和使用
二、课堂主题
本课堂主要围绕hive的查询操作进行讲解，主要包括以下几个方面
1. select 查询语句中的基本查询
2. select查询语句的分组
3. select查询语句中的join
4. select查询语句中的排序
三、课堂目标
1. 掌握hive中select 查询语句中的基本查询
2. 掌握hive中select查询语句的分组
3. 掌握hive中select查询语句中的join
4. 掌握hive中select查询语句中的排序
四、知识要点
1. 基本查询
注意
SQL语言大小写不敏感
SQL可以写在一行或者多行
关键字不能被缩写也不能分行
各字句一般要分行写
使用缩进提高语句的可读性
1.1 全表和特定列查询
1.2 列起别名
1.3 常用函数
开启hive的本地local模式运行：
set hive.exec.mode.local=true；
1. count
2. max
3. min
4. sum
5. avg
1.4 limit语句
1.5 where语句
1.6 算术运算符
+、-、*、/、%、&、|、^、-取反
| 描述 | |
|---|---|
| A+B | A和B 相加 |
| A-B | A减去B |
| A*B | A和B 相乘 |
| A/B | A除以B |
| A%B | A对B取余 |
| A&B | A和B按位取与 |
| A|B | A和B按位取或 |
| A^B | A和B按位取异或 |
| ~A | A按位取反 |
1.7 比较运算符
| 支持的数据类型 | 描述 | |
|---|---|---|
| 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关键字则可达到相反的效果。like不是正则，而是通配符 |
| A RLIKE B, A REGEXP B | STRING 类型 | B是一个正则表达式，如果A与其匹配，则返回true；反之返回false。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 |
1.8 逻辑运算符
| 操作 | 描述 | |
|---|---|---|
| A AND B | 逻辑并 | 如果A和B都是true则为true，否则false |
| A OR B | 逻辑或 | 如果A或B或两者都是true则为true，否则false |
| NOT A | 逻辑否 | 如果A为false则为true,否则false |
2. 分组
2.1 group by 语句
2.2 having语句
having与where不同点
where针对表中的列发挥作用，查询数据；
having针对查询结果中的列发挥作用，筛选数据
where后面不能写分组函数，而having后面可以使用分组函数
having只用于group by分组统计语句
例：求每个部门的平均薪水大于2000的部分
select deptno，avg（sal） avg_sal from emp group by deptno having avg_sal >2000 ；
3 join语句
3.1 等值join
hive支持通常的sql join语句，但是只支持等值连接，不支持非等值连接
3.2 表的别名
3.3 内连接inner join
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
join 默认是inner join
select * from teacher as t inner join course as c on t.id=c.id；
3.4 左外连接left outer join
左外连接：join操作符左边表中符合where子句的所有记录将会被返回。
3.5 右外连接 right outer join
右外连接：join操作符右边表中符合where子句的所有记录将会被返回。
3.6 满外连接 full outer join
满外连接：将会返回所有表中符合where语句条件的所有记录，如果任一表的指定字段没有符合条件的值的话
那么就使用null替代
3.7 多表连接
多个表使用join进行连接
4. 排序
4.1 order by 全局排序
order by 说明
全局排序，只有一个reduce
使用order by子句排序
asc 升序 默认
desc 降序
select * from tb order by score desc；
4.2 按照别名排序
4.3 多个列排序
select * from tb order by score，age；
4.4 每个MapReduce内部排序（Sort By）局部排序
sort by：每个reduce内部进行排序，对全局结果集来说不是排序
1、设置reduce个数
set mapreduce.job.reduces=3
2、查看设置reduce个数
set mapreduce.job.reduces;
3、查询成绩按照成绩降序排序
select * from tb sort by score
4.5 distribute by分区排序
字段.hashcode % reduce个数=reduce编号
distribute by：类似MR中partition，采集hash算法，在map端将查询的结果中hash值相同的结果分发到对应的
reduce文件中。结合sort by使用。
注意：
hive要求 distribute by语句要写在sort by语句之前
4.6 cluster by
当distribute by 和sort by字段相同时，可以使用cluster by方式
五、拓展
5.1 case when then 语句
5.2 什么情况下hive可以避免进行mapreduce
1. 直接查询表的数据 不会进行mapreduce
2. 查询语句中的过滤条件只是分区字段的情况下，不会进行mapreduce
此外，如果设置属性set hive.exec.mode.local.auto=true ；
hive还是会尝试使用本地模式
=================================================================
前几天回答了一个问题，感觉自己特别SB；
问我：a表 field1 1,2,3
join
b表 field1 1,2,2,4
输出表记录是什么。
我：毫不犹豫；1,2,2,3
后来我才知道，自己相当然的认为是left join ，（这个用的比较多）
其实是inner join
==============================================================