Hive学习---3、DML（Data Manipulation Language）数据操作、查询

1、DML（Data Manipulation Language）数据操作

1.1 Load

load语句可将文件导入到Hive表中
1、语法

load data [local] inpath 'filepath' [overwrite] into table tablename [partition(partcol1=val1,partcol2=val2...)]

2、关键字说明
（1）local：表示从本地加载数据到Hive表，否则从HDFS加载数据到Hive表。
（2）OverWrite：表示覆盖表中已有的数据，否则表示追加
（3）Partition：表示上传到指定分区，若目标是分区表，需指定分区

3、案例实操
（1）创建一张表

create table student(
    id int, 
    name string
) 
row format delimited fields terminated by '\t';

（2）加载本地文件到Hive中

load data local inpath '/opt/module/datas/student.txt' into table student;

（3）加载HDFS文件到Hive中

• 上传文件到HDFS

hadoop fs -put /opt/module/datas/student.txt /user/zhm

• 加载HDFS上数据，导入完成后去HDFS上查看文件是否还存在

load data inpath '/user/zhm/student.txt' 
into table student;

（4）加载数据覆盖表中已有的数据

load data inpath '/user/zhm/student.txt' 
overwrite into table student;

1.2 Insert

1.2.1 将查询结果插入表中

1、语法

insert (into | overwrite) table tablename [partition(partcol1=val1,partcol2=val2...)] select_statement

2、关键字说明
（1）Into：将结果追加到目标表中
（2）OverWrite：用结果覆盖原有数据
3、案例
（1）创建一张表

create table student1(
    id int, 
    name string
) 
row format delimited fields terminated by '\t';

（2）将查询结果插入数据

insert overwrite table student3 
select 
    id, 
    name 
from student;

1.2.2 将给定values插入表中

1、语法

insert (into |overwrite ) table tablename [partition(partcol1=val1,partcol2=val2)...] values values_row[,values_row...]

2、案例

insert into table student1 values(1,'zhm'),(2,'zhm1');

1.2.3 将查询结果写入目标路径

1、语法

insert OverWrite [local] Directory  directory [row format rwo_format] [stored as file_format] select_statement

2、案例

insert overwrite local directory '/opt/module/datas/student' ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
select id,name from student;

1.3 Export&Import

Export导出语句可将表的数据和元数据信息一并导出到HDFS路径，Import可将Export导出的内容导入Hive，表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移。

1、语法

--导出
export table tablename To 'export_target_path'

--导入
Import [external] table new_or_original_tablename from 'source_path' [location 'import_target_path']

2、案例

--导出
export table default.student to '/user/hive/warehouse/export/student';

--导入
import table student2 from '/user/hive/warehouse/export/student';

2、查询

2.1 基础语法

select [all|distinct] select_expr,select_expr,...
from table_name 	--从哪个表查
[where where_condition] --过滤
[group by col_list]		--分组查询
[having col_list]		--分组后过滤
[order by col_list]		--排序
[cluster by col_list	
| [distribute by col_list] [sort by col_list]]
[limit number]      --限制输出的行数

2.2 基本查询（select…from）

2.2.1 全表查询和特定列查询

1、全表查询

select * from emp;

2、选择特定列查询

select empno, ename from emp;

注意：
（1）SQL语言的大小写不敏感
（2）SQL可以写在一行或多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写
（5）使用缩进提高语句的可读性

2.2.2 列别名

（1）重命名一个列
（2）便于计算
（3）紧跟列名，也可以在列名和别名之间加入关键字‘as’

select 
    ename AS name, 
    deptno dn 
from emp;

2.2.3 Limit 语句

典型的查询会返回多行数据。Limit子句用于限制返回的行数

select * from emp limit 2,3; -- 表示从第2行（不包括）开始，向下抓取3行

2.2.4 Where语句

（1）使用where子句将不满足条件的行过滤
（2）where子句紧随from子句
注意：where 子句中不能使用别名

--查询出薪水大于1000的所有员工
select * from emp where sal > 1000;

2.2.5 关系运算符

1、基本语法
如下操作符主要用于where和having语句中

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回true，反之返回false
A<=>B	基本数据类型	如果A和B都为null或者都不为null，则返回true，如果只有一边为null，返回false
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是基于java的正则表达式，如果A与其匹配，则返回true；反之返回false。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

--查询薪水大于1000，部门是30
select 
    * 
from emp 
where sal > 1000 and deptno = 30;

--查询薪水大于1000，或者部门是30
select 
    * 
from emp 
where sal>1000 or deptno=30;

--查询除了20部门和30部门以外的员工信息
select 
    * 
from emp 
where deptno not in(30, 20);

2.2.6 逻辑运算函数

1、基本语法

操作符	含义
and	逻辑并
or	逻辑或
not	逻辑否

2.2.7 聚合函数

1、语法

函数名	函数作用
count(*)	表示统计所有行数，包含null值；
count(某列)	表示该列一共有多少行，不包含null值
max()	求最大值，不包含null，除非所有值都是null
min()	求最小值，不包含null，除非所有值都是null
sum()	求和，不包含null
avg()	求平均值，不包含null

--求总行数（count）
select count(*) cnt from emp;

--求工资的最大值（max）
select max(sal) max_sal from emp;

--求工资的最小值（min）
select min(sal) min_sal from emp;

--求工资的总和（sum）
select sum(sal) sum_sal from emp; 

--求工资的平均值（avg）
select avg(sal) avg_sal from emp;

2.3 分组

2.3.1 group by

Group by 语句通常会和聚合函数一起使用，按照一个或者多个队列结果进行分组，然后对每个组执行聚合操作。

--计算emp表每个部门的平均工资。
select 
    t.deptno, 
    avg(t.sal) avg_sal 
from emp t 
group by t.deptno;

--计算emp每个部门中每个岗位的最高薪水
select 
    t.deptno, 
    t.job, 
    max(t.sal) max_sal 
from emp t 
group by t.deptno, t.job;

2.3.2 Having语句

1、Having和where的不同点
（1）where后面不能写分组聚哈函数，而Having后面可以使用分组聚合函数
（2）having只用于group by 分组统计语句

--求每个部门的平均工资
select 
    deptno, 
    avg(sal) 
from emp 
group by deptno;

--求每个部门的平均薪水大于2000的部门。
select 
    deptno, 
    avg(sal) avg_sal 
from emp 
group by deptno  
having avg_sal > 2000;

2.4 Join

2.4.1 等值Join

Hive支持通常的sql join语句，但是只支持等值连接，不支持非等值连接。

--根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称。
select 
    e.empno, 
    e.ename, 
    d.dname 
from emp e 
join dept d 
on e.deptno = d.deptno;

2.4.2 表的别名

1、好处
（1）使用别名可以简化查询
（2）区分字段来源

--合并员工表和部门表。
select 
    e.*,
    d.* 
from emp e 
join dept d 
on e.deptno = d.deptno;

2.4.3 内连接

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

select 
    e.empno, 
    e.ename, 
    d.deptno 
from emp e 
join dept d 
on e.deptno = d.deptno;

2.4.4 左外连接

join操作符左边表中符合where子句的所有记录将会被返回。

select 
    e.empno, 
    e.ename, 
    d.deptno 
from emp e 
left join dept d 
on e.deptno = d.deptno;

2.4.5 右外连接

join操作符右边表中符合where子句的所有记录将会被返回。

select 
    e.empno, 
    e.ename, 
    d.deptno 
from emp e 
right join dept d 
on e.deptno = d.deptno;

2.4.6 满外连接

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

select 
    e.empno, 
    e.ename, 
    d.deptno 
from emp e 
full join dept d 
on e.deptno = d.deptno;

2.4.7 多表连接

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

select 
    e.ename, 
    d.dname, 
    l.loc_name
from emp e 
join dept d
on d.deptno = e.deptno 
join location l
on d.loc = l.loc;

大多数情况下，Hive会对每对join连接对象启动一个MapReduce任务。本立会首先启动一个MapReduce job对表e和表d进行连接操作，然后再启动一个MapReduce job将第一个MapReduce job的输出和表1进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

2.4.8 笛卡尔积

1、笛卡尔积会在下面条件产生
（1）省略连接条件
（2）连接条件无效
（3）所以表中的所有行互相连接

select 
    empno, 
    dname 
from emp, dept;

2.4.9 联合（union和union all）

1、union和union all上下拼接
union和union all都是上下拼接sql的结果，这点和join是有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重。
union和union all上下拼接sql结果时有两个要求：
（1）两个sql结果，列的个数必须相同
（2）两个sql结果，上下所对应列的类型必须一致

select 
    *
from emp
where deptno=30
union
select 
    *
from emp
where deptno=40;

2.5 排序

2.5.1 全局排序（Order by）

只有一个Reduce
1、使用Order by 子句排序
asc（ascend）：升序（默认）
desc（descend）：降序
2、Order by 子句在select语句的末尾

--查询员工信息按工资升序排列
select 
    * 
from emp 
order by sal;

--查询员工信息按工资降序排列
select 
    * 
from emp 
order by sal desc;

--按照别名排序案例实操
select 
    ename, 
    sal * 2 twosal 
from emp 
order by twosal;

--多个列排序案例实操
select 
    ename, 
    deptno, 
    sal 
from emp 
order by deptno, sal;

2.5.2 每个Reduce内部排序（Sort by）

对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用Sort by。
Sort by为每个reduce产生一个排序文件。每个Reduce内部进行排序，对全局结果集来说不是排序。
1、设置reduce个数

set mapreduce.job.reduces=3;

2、查看设置reduce个数

set mapreduce.job.reduces;

3、根据部门编号降序查看员工信息

select 
    * 
from emp 
sort by deptno desc;

2.5.3 分区（Distribute by）

Distribute By：在有些情况下，我们需要控制某个特定行应该到哪个Reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MapReduce中partition（自定义分区），进行分区，结合sort by使用。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

--先按照部门编号分区，再按照员工编号薪资排序
set mapreduce.job.reduces=3;
insert overwrite local directory 
'/opt/module/hive/datas/distribute-result' 
select 
    * 
from emp 
distribute by deptno 
sort by sal desc;

注意：
（1）distribute by的分区规则是根据分区字段的hash码与reduce的个数进行相除后，余数相同的分到一个区
（2）Hive要求distribute by语句要写在sort by语句之前。

2.5.4 分区排序（Cluster by）

当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为asc或者desc。

select 
    * 
from emp 
cluster by deptno;

或
select 
    * 
from emp 
distribute by deptno 
sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。