本文已参与「新人创作礼」活动，一起开启掘金创作之路。
文章目录
一 DML
1 数据导入
（1）从本地磁盘或者HDFS导入数据
load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];
有local为本地文件，否则文件在集群上
写了overwrite是覆盖导入，否则为追加导入
-
例子
--从本地磁盘追加导入 load data local inpath '/opt/module/datas/student.txt' into table student; --从本地磁盘覆盖导入 load data local inpath '/opt/module/datas/student.txt' overwrite into table student; --先在HDFS服务器上test文件夹下上传一份数据student.txt --HDFS的导入是移动，本地的导入是复制 load data inpath '/test/student.txt' overwrite into table student;
（2）insert导入
--对数据进行初步过滤
insert into table student select id,name from stu_par where class='01';
（3）查询语句中创建表并加载数据
create table if not exists student3as select id, name from student;
（4)创建表时通过Location指定加载数据路径
假设一份数据本来存在于HDFS上，可以在这个文件所在的文件夹建一个表，这个表会将这份数据包括进去
--先在HDFS服务器上test1文件夹下上传一份数据student.txt
create external table student1(id int , name string)
row format delimited fields terminated by '\t'
location '/test1';
2 数据导出
很少有需求需要将数据导出
（1）inser导出
--没有表格式的导出
insert overwrite local directory '/opt/module/datas/export/student'
select * from student;
--带表格式的导出
insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
（2）hive shell命令行导出
hive -e 'select * from default.student;' > /opt/module/datas/export/test.txt;
（3）Export导出到HDFS上
--整张表export到HDFS
export table default.student to '/export/student';
--将导出结果导入到Hive
import table student3 from '/export/student';
将表的元数据和数据一次性全部导出出去
export和import主要用于两个Hadoop平台集群之间Hive表迁移。
3 数据删除
（1）只删除表数据，不删除表本身
truncate table student;
二 查询
基本语法
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
1 基本查询
（1）全表和特定列查询
--创建部门表
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
--创建员工表
create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\t';
--导入数据
load data local inpath '/opt/module/datas/dept.txt' into table dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;
--全表查询
select * from emp;
--查询某一列
select empno,ename from emp;
（2）列别名
--给查询结果起别名，as可以省略
select ename as name from emp;
（3）算数运算符
| 运算符 | 描述 |
|---|---|
| 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按位取反 |
--运算符
select ename,sal + 10 from emp;
（4）常用函数
--UDF函数
select substring(ename,2,2) from emp;
ename的每一列的从第二个字符开始的两个字符
UDF函数特点：一进一出，给一个输入给一个输出
--UDAF函数，也就是常用的统计函数
select count(*) from emp;
--求总行数（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;
A：Aggregation聚合，很多个输入，一个输出
--UDTF函数
T：table generating，表生成函数，一个输入，很多个输出
（5）Limit语句
--limit取前几行，不会遍历整张表，只遍历前5行
select * from emp limit 5;
2 条件过滤
| 操作符 | 支持的数据类型 | 描述 |
|---|---|---|
| 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是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 |
（1）比较运算符
--查询出薪水等于5000的所有员工
select * from emp where sal =5000;
--查询工资在500到1000的员工信息
select * from emp where sal between 500 and 1000;
--查询comm为空的所有员工信息
select * from emp where comm is null;
--查询工资是1500或5000的员工信息
select * from emp where sal IN (1500, 5000);
（2）通配符字符串匹配 % _
--查询以A开头的员工
select * from emp where ename like "A%";
（3）正则匹配
--查询以A开头的员工
select * from emp where ename rlike "^A";
正则表达式：描述的是一串字符串的特征
/ 转义字符，将特殊字符的含义消去
^A 表示以A开头的行
A& 表示以R结尾的行
^& 这一行刚开始就结束了，表示匹配所有空行
^A& 匹配一行中只有一个A的所有行
. 表示匹配任意字符
^.& 匹配所有只有一个字符的行
“*” 表示上一个子式匹配0此或多次
^a*& 匹配0个或多个a开头和结尾的行，也就是空行或者无限循环a的行
Zo* 表示Z后面有0个或n个o的行
.* 匹配任意字符串
[] 匹配某个范围内的一个字符
[6,8] 匹配6或者8
[a-z] 匹配a-z之间的任意一个字符
[a-z]* 匹配任意字母字符串
[a-f,o-z] 匹配a-f或者o-z的任意一个字符
（4）逻辑运算符
| 操作符 | 含义 |
|---|---|
| AND | 逻辑并 |
| OR | 逻辑或 |
| NOT | 逻辑否 |
--查询薪水大于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);
3 分组
（1）Group By语句
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列对结果进行分组，然后对每个组执行聚合操作。
--计算emp表每个部门的平均工资
select deptno, avg(sal) from emp group by deptno;
（2）having语句
--计算emp表每个部门的平均工资大于2000的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
mysql语句执行过程：
from where group by select having order by limit
4 连接
（1）等值连接
--查询员工编号，姓名以及所在部门名称
select
e.empno,
e.ename,
d.dname
from
emp e
join
dept d
on
e.deptno=d.deptno;
（2）内连接
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select
e.empno,
e.ename,
d.deptno
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
（3）左外连接
JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
（4）右外连接
JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
（5）满外连接
将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno
= d.deptno;
（6）多表连接
连接 n个表，至少需要n-1个连接条件。
-
创建location表
create table if not exists location( loc int, loc_name string ) row format delimited fields terminated by '\t'; -
导入数据
load data local inpath '/opt/module/datas/location.txt' into table location; -
多表连接查询
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的输出和表l;进行连接操作。
因为Hive总是按照从左到右的顺序执行，所以不是表d和表l先进行连接操作。
优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。
（7）笛卡尔积
select * from emp,dept;
当省略连接条件、连接条件无效、所有表中的所有行互相连接时会出发笛卡尔积
在hive中要严禁笛卡尔积式查询
（8）hive1和hive2连接谓词中不支持or
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
= d.deptno or e.ename=d.deptno;
5 排序
（1）全局排序
Order By：全局排序，只有一个Reducer
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
问题：所有的数据都会进入到一个reduce中，reduce处理的数据量很大
--按照工资降序
select * from emp order by sal desc;
- 一般不会进行全局排序，只会求前几名
--查询工资前十的人，map求局部前十
select * from emp order by sal desc limit 10;
（2）局部排序
Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。
Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。
对应MapReduce分区概念，分成多个区，交给多个Reduce处理，每个Reduce内部进行排序。
默认的分区规则：按照一行的哈希值进行分区，指定希望的局部分区字段见（4）
建立分桶表时需要用到局部排序
--有时可能只需要看大概的数据趋势，不需要进行全部排序
select * from emp sort by empno desc;
（3）多条件排序
--先按照部门升序，部门内再按照工资降序
select * from emp order by deptno asc,sal desc;
（4）分区排序
Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
--指定局部排序的分区字段
select * from emp distribute by deptno sort by sal desc;
（5）Cluster by
当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
select * from emp distribute by empno sort by empno;
--等价于上一句
select * from emp cluster by empno;
（6）分桶及抽样查询
分桶表：hive没有索引，但是分区可以代替索引，比如说按照部门分区，相同部门的人会被放到同一个文件夹中，在查询的时候加上分区号，可以减少数据扫描量，在某种程度上可以起到索引的作用。但是有些字段（主键，因为主键唯一）不方便进行分区，但是又希望减少数据扫描量，针对难以分区的字段，进一步将数据组织成桶，也就是分桶表。分桶是针对某一个分区的数据，将这些数据进一步组织成多个文件。
- 创建分桶表
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
- 上传数据
load data local inpath '/opt/module/datas/student.txt' into table stu_buck;
分桶规则：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
分桶的好处：在进行主键过滤（hive不能按照主键进行分区）的时候减少数据的扫描量。在连接的时候，使用分桶表可以提高连接的速度，连接其实在做的就是主键过滤，因为是主键与主键进行连接。
（7）分桶抽样查询
对于非常大的数据集，有时需要了解数据的整体特征，不可能将数据全部读取一遍，这时就会使用到抽样。注意，只有对于很大的数据集才有进行抽样的必要。
分桶表可以加快抽样的速度。
--将student这张表按照id分成4份取出第1份，每份大小随机分配
select * from student tablesample(bucket 1 out of 4 on id);
