HIVE之DML语句参考笔记

一、数据导入

1.向表中装载数据（Load）

1.语法

hive> load data [local] inpath '数据的 path' [overwrite] into table 
student [partition (partcol1=val1,…)];

（1）load data:表示加载数据
（2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表 （3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区
2.实操
（1）加载本地文件到 hive

hive (default)> load data local inpath 
'/opt/module/hive/student.txt' into table default.student;

（2）加载 HDFS 文件到 hive 中

上传文件到 HDFS
hive (default)> dfs -put /opt/module/hive/data/student.txt 
/user/atguigu/hive;
加载 HDFS 上数据
hive (default)> load data inpath '/user/atguigu/hive/student.txt' into 
table default.student;

注：
1）put上传时，不会修改元数据，不走MR，count()不会变。
2）insert写入，会走MR，两个值numFiles会变，numRows也会变，会修改元数据，count()会变，因为numRows变了。
3）load上传，同过hive上传文件，会走MR，但走的也是put命令，修改了元数据，其中numFiles会变，numRows不会变。
4）总结：以后从文件加载数据，尽量用通过load。
注：从hfds加载到hive数据为何会快，因为修改的是元数据指向的地址指针，并不是真正的移动了文件。

2.通过查询语句向表中插入数据（Insert）

多表（多分区）插入模式（根据多张表查询结果），也可实现简单插入，这里不做演示

hive (default)> from student
 insert overwrite table student partition(month='201707')
 select id, name where month='201709'
 insert overwrite table student partition(month='201706')
 select id, name where month='201709';

3.查询语句中创建表并加载数据（As Select）

根据查询结果创建表（查询的结果会添加到新创建的表中）
create table if not exists student3
as select id, name from student;

4.Import 数据到指定 Hive 表中

注意：先用 export 导出后，再将数据导入。
hive (default)> import table student2
from '/user/hive/warehouse/export/student';

二、数据导出

1.insert导出

1）将查询的结果导出到本地
hive (default)> insert overwrite local directory 
'/opt/module/hive/data/export/student'
select * from student;
2）将查询的结果格式化导出到本地
hive(default)>insert overwrite local directory 
'/opt/module/hive/data/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
3）将查询的结果导出到 HDFS 上(没有 local)
hive (default)> insert overwrite directory '/user/atguigu/student2'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
 select * from student;

2.Hadoop 命令导出到本地

hive (default)> dfs -get /user/hive/warehouse/student/student.txt
/opt/module/data/export/student3.txt;

3.Hive Shell 命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）
[bym@hadoop102 hive]$ bin/hive -e 'select * from default.student;' >
/opt/module/hive/data/export/student4.txt;

4.Export 导出到 HDFS 上

hive (default)> export table default.student 
to '/user/hive/warehouse/export/student';

5.清除表中数据（Truncate）

注意：Truncate 只能删除管理表（hdfs上的，但表中内容仍能查到），不能删除外部表中数据。drop也删除不了外部表的数据。
hive (default)> truncate table student;

三、基本查询（Select…From）

基本语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

1.全表查询与特定列查询

hive (default)> select * from emp;
hive (default)> select empno,ename from emp ;

2.列别名

hive (default)> select ename AS name, deptno dn from emp;

3.常用函数

1）求总行数（count）
hive (default)> select count(*) cnt from emp;
2）求工资的最大值（max）
hive (default)> select max(sal) max_sal from emp;
3）求工资的最小值（min）
hive (default)> select min(sal) min_sal from emp;
4）求工资的总和（sum）
hive (default)> select sum(sal) sum_sal from emp; 
5）求工资的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;

4.limit子句

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。
hive (default)> select * from emp limit 5;

5. Where 语句

查询出薪水大于 1000 的所有员工
hive (default)> select * from emp where sal >1000;
查询 comm 为空的所有员工信息
hive (default)> select * from emp where comm is null;
查询工资是 1500 或 5000 的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);

6.Like和Rlike

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

（1）查找名字以 A 开头的员工信息
hive (default)> select * from emp where ename LIKE 'A%';
（2）查找名字中第二个字母为 A 的员工信息
hive (default)> select * from emp where ename LIKE '_A%';
（3）查找名字中带有 A 的员工信息
hive (default)> select * from emp where ename RLIKE '[A]';

7.GROUP BY /HAVING

8.JOIN(等值JOIN、内连接、左外连接、右外连接、满外连接。多表连接)

1.满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字
段没有符合条件的值的话，那么就使用 NULL 值替代。
hive (default)> select e.empno, e.ename, d.deptno from emp e full join
dept d on e.deptno = d.deptno;

2.多表连接：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接
条件
优化：当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的
话，那么只会产生一个 MapReduce job。否则，有几个连接则产生几个MapReduce。

9.排序

1.Order By
按照别名排序

按照员工薪水的 2 倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;

多个列排序

按照部门和工资升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal;

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

1）设置 reduce 个数
hive (default)> set mapreduce.job.reduces=3;
2）查看设置 reduce 个数
hive (default)> set mapreduce.job.reduces;
3）根据部门编号降序查看员工信息
hive (default)> select * from emp sort by deptno desc;
4）将查询结果导入到文件中（按照部门编号降序排序）
hive (default)> insert overwrite local directory 
'/opt/module/data/sortby-result'
select * from emp sort by deptno desc;

10.分区（Distribute By）

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

案例实操：
（1）先按照部门编号分区，再按照员工编号降序排序。
hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory 
'/opt/module/data/distribute-result' select * from emp distribute by 
deptno sort by empno desc;
注意：
➢ distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，
余数相同的分到一个区。 ➢ Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

11.Cluster By

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

以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一
个分区里面去