「这是我参与11月更文挑战的第22天，活动详情查看：2021最后一次更文挑战」。
一、DML 数据操作
1、数据导入
1.1、向表中装载数据（Load）
- 语法
1 | hive复制代码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:表示上传到指定分区
- 实操案例
+ 创建一张表
1
2
hive复制代码hive (default)> create table student(id string, name string)
> row format delimited fields terminated by '\t';
+ 加载本地文件到 hive
1
2
hive复制代码hive (default)> load data local inpath '/opt/module/hive-3.1.2/datas/student' into table student;
Loading data to table default.student
+ 加载 HDFS 文件到 hive 中
上传文件到 HDFS
1
hive复制代码hive (default)> dfs -put /opt/module/hive-3.1.2/datas/student.txt /user/moe/hive/;
加载 HDFS 上数据
1
2
hive复制代码hive (default)> load data inpath '/user/moe/hive/student.txt' into table student;
Loading data to table default.student
+ 加载数据覆盖表中已有的数据
1
2
hive复制代码hive (default)> load data inpath '/user/moe/hive/student.txt' overwrite into table student;
Loading data to table default.student
1.2、通过查询语句向表中插入数据（Insert）
- 创建一张表
1 | hive复制代码hive (default)> create table student_par(id string, name string) row format delimited fields terminated by '\t'; |
- 基本插入数据
1 | hive复制代码hive (default)> insert into table student_par values('1','wangwu'),('2','zhaoliu'); |
- 基本模式插入（根据单张表查询结果）
1 | hive复制代码hive (default)> insert overwrite table student_par select id,name from student; |
insert into：以追加数据的方式插入到表或分区，原有数据不会删除
insert overwrite：会覆盖表中已存在的数据
注意：insert 不支持插入部分字段
- 多表（多分区）插入模式（根据多张表查询结果）
1.3、查询语句中创建表并加载数据（As Select）
根据查询结果创建表（查询的结果会添加到新创建的表中）
1 | hive复制代码create table if not exists student3 as select id, name from student; |
1.4、创建表时通过 Location 指定加载数据路径
- 上传数据到 hdfs 上
1 | hive复制代码hive (default)> dfs -mkdir /student; |
- 创建表，并指定在 hdfs 上的位置
1 | hive复制代码hive (default)> create external table if not exists student3(id int, name string) |
- 查询数据
1 | hive复制代码hive (default)> select * from student3; |
1.5、Import 数据到指定 Hive 表中
注意：先用 export 导出后，再将数据导入。
1 | hive复制代码hive (default)> import table student2 from '/user/hive/warehouse/export/student'; |
2、数据导出
2.1、Insert 导出
- 将查询的结果导出到本地
1 | hive复制代码hive (default)> insert overwrite local directory '/opt/module/hive-3.1.2/datas/student01' select * from student; |
- 将查询的结果格式化导出到本地
1 | hive复制代码hive (default)> insert overwrite local directory '/opt/module/hive-3.1.2/datas/student02' row format delimited fields terminated by '\t' select * from student; |
- 将查询的结果导出到 HDFS 上(没有 local)
1 | hive复制代码hive (default)> insert overwrite directory '/student03' row format delimited fields terminated by '\t' select * from student; |
2.2、Hadoop 命令导出到本地
1 | hive复制代码hive (default)> dfs -get /user/hive/warehouse/student/student.txt /opt/module/data/export/student3.txt; |
2.3、Hive Shell 命令导出
基本语法：（hive -f/-e 执行语句或者脚本 > file）
1 | shell复制代码[moe@hadoop102 datas]$ hive -e 'select * from student;' > ./student4.txt |
2.4、Export 导出到 HDFS 上
1 | hive复制代码hive (default)> export table student to '/student5'; |
export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移。
2.5、Sqoop 导出
2.6、清除表中数据（Truncate）
注意：Truncate 只能删除管理表，不能删除外部表中数据
1 | hive复制代码hive (default)> truncate table student; |
二、查询
1、基本查询（Select…From）
查询语句语法：
1 | txt复制代码SELECT [ALL | DISTINCT] select_expr, select_expr, ... |
1.1、全表和特定列查询
- 数据准备
原始数据
dept:
1 | txt复制代码10 ACCOUNTING 1700 |
emp：
1 | txt复制代码7369 SMITH CLERK 7902 1980-12-17 800.00 20 |
+ 创建部门表
1
2
3
4
5
6
hive复制代码create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
+ 创建员工表
1
2
3
4
5
6
7
8
9
10
hive复制代码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';
+ 导入数据
1
2
3
4
5
6
hive复制代码hive (default)> load data local inpath '/opt/module/hive-3.1.2/datas/dept.txt' into table dept;
Loading data to table default.dept
OK
Time taken: 0.266 seconds
hive (default)> load data local inpath '/opt/module/hive-3.1.2/datas/emp.txt' into table emp;
Loading data to table default.emp
- 全表查询
1 | hive复制代码hive (default)> select * from emp; |
- 选择特定列查询
1 | hive复制代码hive (default)> select empno, ename from emp; |
注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。
1.2、列别名
- 重命名一个列
- 便于计算
- 紧跟列名，也可以在列名和别名之间加入关键字‘AS’
- 案例实操
查询名称和部门
1 | hive复制代码hive (default)> select ename AS name, deptno dn from emp; |
1.3、算术运算符
案例实操：查询出所有员工的薪水后加 1 显示。
1 | hive复制代码hive (default)> select sal + 1 from emp; |
1.4、常用函数
- 求总行数（count）
1 | hive复制代码hive (default)> select count(*) cnt from emp; |
- 求工资的最大值（max）
1 | hive复制代码hive (default)> select max(sal) max_sal from emp; |
- 求工资的最小值（min）
1 | hive复制代码hive (default)> select min(sal) min_sal from emp; |
- 求工资的总和（sum）
1 | hive复制代码hive (default)> select sum(sal) sum_sal from emp; |
- 求工资的平均值（avg）
1 | hive复制代码hive (default)> select avg(sal) avg_sal from emp; |
1.5、Limit 语句
1 | hive复制代码hive (default)> select * from emp limit 5; |
1.6、Where 语句
- 使用 WHERE 子句，将不满足条件的行过滤掉
- WHERE 子句紧随 FROM 子句
- 案例实操
查询出薪水大于 1000 的所有员工
1 | hive复制代码hive (default)> select * from emp where sal >1000; |
注意：where 子句中不能使用字段别名。
1.7、比较运算符（Between/In/ Is Null）
- 查询出薪水等于 5000 的所有员工
1 | hive复制代码hive (default)> select * from emp where sal = 5000; |
- 查询工资在 500 到 1000 的员工信息
1 | hive复制代码hive (default)> select * from emp where sal between 500 and 1000; |
- 查询 comm 为空的所有员工信息
1 | hive复制代码hive (default)> select * from emp where comm is null; |
- 查询工资是 1500 或 5000 的员工信息
1 | hive复制代码hive (default)> select * from emp where sal IN (1500, 5000); |
1.8、Like 和 RLike
- 使用 LIKE 运算选择类似的值
- 选择条件可以包含字符或数字
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
- RLIKE 子句
RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件。
- 案例实操
+ 查找名字以 A 开头的员工信息
1
hive复制代码hive (default)> select * from emp where ename LIKE 'A%';
+ 查找名字中第二个字母为 A 的员工信息
1
hive复制代码hive (default)> select * from emp where ename LIKE '_A%';
+ 查找名字中带有 A 的员工信息
1
hive复制代码hive (default)> select * from emp where ename RLIKE '[A]';
1.9、逻辑运算符（And/Or/Not）
- 查询薪水大于 1000，部门是 30
1 | hive复制代码hive (default)> select * from emp where sal>1000 and deptno=30; |
- 查询薪水大于 1000，或者部门是 30
1 | hive复制代码hive (default)> select * from emp where sal>1000 or deptno=30; |
- 查询除了 20 部门和 30 部门以外的员工信息
1 | hive复制代码hive (default)> select * from emp where deptno not IN(30, 20); |
2、分组
2.1、Group By 语句
GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
- 计算 emp 表每个部门的平均工资
1 | hive复制代码hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno; |
- 计算 emp 每个部门中每个岗位的最高薪水
1 | hive复制代码hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job; |
2.2、Having 语句
having 与 where 不同点
- where 后面不能写分组函数，而 having 后面可以使用分组函数。
- having 只用于 group by 分组统计语句。
求每个部门的平均工资
1 | hive复制代码hive (default)> select deptno, avg(sal) from emp group by deptno; |
求每个部门的平均薪水大于 2000 的部门
1 | hive复制代码hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000; |
3、Join 语句
3.1、等值 Join
Hive 支持通常的 SQL JOIN 语句。
根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；
1 | hive复制代码hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e inner join dept d on e.deptno = d.deptno; |
3.2、表的别名
- 好处
+ 使用别名可以简化查询。
+ 使用表名前缀可以提高执行效率。- 案例实操
合并员工表和部门表
1 | hive复制代码hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno; |
3.3、内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
1 | hive复制代码hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno; |
3.4、左外连接
左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。
1 | hive复制代码hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno; |
3.5、右外连接
右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
1 | hive复制代码hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno; |
3.6、满外连接
满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。
1 | hive复制代码hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno; |
3.7、多表连接
注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。
- 数据准备
1 | txt复制代码1700 Beijing |
- 创建位置表
1 | hive复制代码create table if not exists location( |
- 导入数据
1 | hive复制代码hive (default)> load data local inpath '/opt/module/hive-3.1.2/datas/location.txt' into table location; |
- 多表连接查询
1 | hive复制代码hive (default)>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; 进行连接操作。
注意：为什么不是表 d 和表 l 先进行连接操作呢？这是因为 Hive 总是按照从左到右的顺序执行的。
优化：当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的话，那么只会产生一个 MapReduce job。
3.8、笛卡尔积
- 笛卡尔集会在下面条件下产生
+ 省略连接条件
+ 连接条件无效
+ 所有表中的所有行互相连接- 案例实操
1 | hive复制代码hive (default)> select empno, dname from emp, dept; |
4、排序
4.1、全局排序（Order By）
Order By：全局排序，只有一个 Reducer
- 使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
- ORDER BY 子句在 SELECT 语句的结尾
- 案例实操
+ 查询员工信息按工资升序排列
1
hive复制代码hive (default)> select * from emp order by sal;
+ 查询员工信息按工资降序排列
1
hive复制代码hive (default)> select * from emp order by sal desc;
4.2、按照别名排序
按照员工薪水的 2 倍排序
1 | hive复制代码hive (default)> select ename, sal*2 twosal from emp order by twosal; |
4.3、多个列排序
按照部门和工资升序排序
1 | hive复制代码hive (default)> select ename, deptno, sal from emp order by deptno, sal; |
4.4、每个 Reduce 内部排序（Sort By）
Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。
- 设置 reduce 个数
1 | hive复制代码hive (default)> set mapreduce.job.reduces=3; |
- 查看设置 reduce 个数
1 | hive复制代码hive (default)> set mapreduce.job.reduces; |
- 根据部门编号降序查看员工信息
1 | hive复制代码hive (default)> select * from emp sort by deptno desc; |
- 将查询结果导入到文件中（按照部门编号降序排序）
1 | hive复制代码hive (default)> insert overwrite local directory '/opt/module/data/sortby-result' select * from emp sort by deptno desc; |
4.5、分区（Distribute By）
Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。
先按照部门编号分区，再按照员工编号降序排序。
1 | hive复制代码hive (default)> set mapreduce.job.reduces=3; |
distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区。
Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
4.6、Cluster By
当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。
以下两种写法等价
1 | hive复制代码hive (default)> select * from emp cluster by deptno; |
注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一个分区里面去。
三、友情链接
本文转载自: 掘金