Hive-DML数据操作、数据查询以及查询排序

喜欢ヅ旅行 2022-09-06 00:28 219阅读 0赞

# DML 数据操作 #

## 数据导入 ##

### 向表中装载数据(Load) ###

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:表示上传到指定分区

实操：

// 创建表
    create table student(id string, name string) row format delimited fields terminated by ',';
    
    // 数据如下：
    1,caocao
    2,liubei
    3,sunquan
    
    // 本地文件导入，底层执行的是put操作
    load data local inpath '/opt/module/hive/testData/student.txt' into table default.student;
    
    // 创建表
    create table student1(id string, name string) row format delimited fields terminated by ',';
    // HDFS导入数据，底层执行的是mv操作
    load data inpath '/sanguo/student.txt' into table defalut.student1;

通过load方法插入的数据，只会修改TABLE\_PARAMS中的numFiles的值，而通过put文件到HDFS的方式numFiles的值是不会改变的；insert方法插入的数据如果numFiles为0的情况下numFiles会+1，numRows也会加。因此要使用hive的话，尽量不要通过put去批量增加数据。

![在这里插入图片描述][82c8069b7cae4acbbd5140794e2c29dc.png]

此时numFiles不为0，numRows为0的时候count(\*)就会通过MR进行统计。

### 通过查询语句向表中插入数据(Insert) ###

// 基本插入数据
    hive (default)> insert into table student values(11,'xiangyu'),(12,'liubang');
    
    // 基本模式插入(根据单张表查询结果)
    hive (default)> insert into student
                  > select * from student1;
    
    // insert into:以追加数据的方式插入到表或分区,原有数据不会删除
    // insert overwrite:会覆盖表中已存在的数据
    // 注意:insert不支持插入部分字段

### 查询语句中创建表并加载数据(As Select) ###

根据查询结果创建表(查询的结果会添加到新创建的表中)

create table if not exists student3
    as
    select id, name from student;

### 创建表时通过 Location 指定加载数据路径 ###

// 上传数据到 hdfs 上
    hive (default)> dfs -mkdir /student;
    hive (default)> dfs -put /opt/module/datas/student.txt /student;
    
    // 创建表,并指定在 hdfs 上的位置
    hive (default)> create external table if not exists student5(id int, name string)
    				row format delimited fields terminated by '\t'
    				location '/student';

### Import 数据到指定 Hive 表中 ###

\*\*注意:先用 export 导出后,再将数据导入。\*\*即导入的数据源必须是导出的数据。

hive (default)> import table student2
    				from '/user/hive/warehouse/export/student';

如果表已经存在且已经有信息，则不能导入；如果表不存在，或者表信息为空则可以导入。

## 数据导出 ##

### Insert 导出 ###

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

### Hadoop 命令导出到本地 ###

Hadoop的get命令

### Hive Shell 命令导出 ###

基本语法:(hive -f/-e 执行语句或者脚本 > file)

## 追加
    bin/hive -e 'select * from default.student;' >> /opt/module/hive/data/export/student4.txt
    
    ## 覆盖
    bin/hive -e 'select * from default.student;' > /opt/module/hive/data/export/student4.txt

### Export 导出到 HDFS 上 ###

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

export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移。

### Sqoop 导出 ###

第三方组件，将数据导出到mysql中的

### 清除表中数据(Truncate) ###

注意:Truncate 只能删除管理表,不能删除外部表中数据，该命令只删除HDFS中的数据，不删除元数据。

hive (default) > truncate table student;

# 查询 #

参见[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select][https_cwiki.apache.org_confluence_display_Hive_LanguageManual_Select]

查询语句语法:

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]

## 基本查询(Select…From) ##

### 全表和特定列查询 ###

准备数据

dept表

10,ACCOUNTING,1700
    20,RESEARCH,1800
    30,SALES,1900
    40,OPERATIONS,1700

emp表

7369,SMITH,CLERK,7902,1980-12-17,800.00,,20
    7499,ALLEN,SALESMAN,7698,1981-2-20,1600.00,300.00,30
    7521,WARD,SALESMAN,7698,1981-2-22,1250.00,500.00,30
    7566,JONES,MANAGER,7839,1981-4-2,2975.00,,20
    7654,MARTIN,SALESMAN,7698,1981-9-28,1250.00,1400.00,30
    7698,BLAKE,MANAGER,7839,1981-5-1,2850.00,,30
    7782,CLARK,MANAGER,7839,1981-6-9,2450.00,,10
    7788,SCOTT,ANALYST,7566,1987-4-19,3000.00,,20
    7839,KING,PRESIDENT,,1981-11-17,5000.00,,10
    7844,TURNER,SALESMAN,7698,1981-9-8,1500.00,0.00,30
    7876,ADAMS,CLERK,7788,1987-5-23,1100.00,,20
    7900,JAMES,CLERK,7698,1981-12-3,950.00,,30
    7902,FORD,ANALYST,7566,1981-12-3,3000.00,,20
    7934,MILLER,CLERK,7782,1982-1-23,1300.00,,10

创建部门表

create table if not exists dept(
        deptno int,
        dname string,
        loc int
    )
    row format delimited fields terminated by ',';

创建员工表

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 ',';

加载数据

load data local inpath '/opt/module/hive/data/dept.txt' into table dept;
    
    load data local inpath '/opt/module/hive/data/emp.txt' into table emp;

查询

// 全表查询
    hive (default)> select * from emp;
    hive (default)> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ;
    
    // 选择特定列查询
    hive (default)> select empno, ename from emp;

注意:  
(1)SQL 语言**大小写不敏感。**

(2)SQL 可以写在一行或者多行

(3)关键字不能被缩写也不能分行

(4)各子句一般要分行写。

(5)使用缩进提高语句的可读性，最好用四个空格缩进，不要用tab。

### 列别名 ###

1)重命名一个列

2)便于计算

3)紧跟列名,也可以在列名和别名之间加入关键字‘AS’

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

### 算术运算符 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NsZWFybHhq_size_16_color_FFFFFF_t_70]

案例实操:查询出所有员工的薪水后加 1 显示。

hive (default)> select sal + 1 from emp;

### 常用函数 ###

// 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;

### Limit 语句 ###

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

hive (default) > select * from emp limit 5;
    hive (default)> select * from emp limit 2;

### Where 语句 ###

select，from以及where的执行顺序是form->where->select，因此where中不能用select的别名。

1)使用 WHERE 子句,将不满足条件的行过滤掉

2)WHERE 子句紧随 FROM 子句

// 查询出薪水大于 1000 的所有员工
    hive (default) > select * from emp where sal >1000;

### 比较运算符(Between/In/ Is Null) ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NsZWFybHhq_size_16_color_FFFFFF_t_70 1]

// (1)查询出薪水等于 5000 的所有员工
    hive (default)> select * from emp where sal =5000;
    
    // (2)查询工资在 500 到 1000 的员工信息，包含500和1000
    hive (default)> select * from emp where sal between 500 and 1000;
    
    // (3)查询 comm 为空的所有员工信息
    hive (default)> select * from emp where comm is null;
    
    // (4)查询工资是 1500 或 5000 的员工信息
    hive (default)> select * from emp where sal IN (1500, 5000);

### Like 和 RLike ###

1)使用 LIKE 运算选择类似的值

2)选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。  
\_ 代表一个字符。

3)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]';

### 逻辑运算符(And/Or/Not) ###

![在这里插入图片描述][b3399c29524e4976a0423d54bef558e3.png]

// (1)查询薪水大于 1000,部门是 30
    hive (default)> select * from emp where sal>1000 and deptno=30;
    
    // 查询薪水大于 1000,或者部门是 30
    hive (default)> select * from emp where sal>1000 or deptno=30;
    
    // 查询除了 20 部门和 30 部门以外的员工信息
    hive (default)> select * from emp where deptno not IN(30, 20);

## 分组 ##

### Group By 语句 ###

GROUP BY 语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。必定走MR。

// (1)计算 emp 表每个部门的平均工资
    hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t 
                    group by 
                    t.deptno;
    
    // (2)计算 emp 每个部门中每个岗位的最高薪水
    hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t
                    group by
                    t.deptno, t.job;

where在group by之前生效。

### Having 语句 ###

having 与 where 不同点

(1)where 后面不能写分组函数,而 having 后面可以使用分组函数。

(2)having 只用于 group by 分组统计语句。

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

*where、聚合函数、having* 在from后面的执行顺序：

**where > select（聚合函数(sum,min,max,avg,count)） > having**

1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。  
2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。  
3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

## Join 语句 ##

### 七种Join ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NsZWFybHhq_size_16_color_FFFFFF_t_70 2]

### 等值 Join ###

Hive 支持通常的 SQL JOIN 语句。

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

### 表的别名 ###

好处

(1)使用别名可以简化查询。

(2)使用表名前缀可以提高执行效率。

// 合并员工表和部门表
    hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

### 内连接 ###

// 内连接:只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
    // 上图中的中间图所示
    hive (default)> select 
    					e.empno, e.ename, d.deptno 
    				from 
    					emp e 
    				join 
    					dept d 
    				on e.deptno = d.deptno;

### 左外连接 ###

// 左外连接:JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。右表无记录则作为null
    // 也就是左表的数据都要，上图中左上图所示
    hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
    
    // 也就是获取左边独有的，上图左下所示，相当于左表减去右表
    // 方法一：可以在左外的基础上，通过过滤条件过滤掉有表字段不为null的，即可得到左表独有的
    hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno where d.dname is null;
    
    // 方法二：不推荐，会执行两次select操作，即两次MR任务
    hive (default)> select e.empno, e.ename from emp e where e.deptno not in (select deptno from dept)

### 右外连接 ###

// 右外连接:JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。左表无记录则记录为null
    // 也就是右表的数据都要，上图中右上图所示
    hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
    
    // 上图右下所示，相当于右表减去左表

### 满外连接 ###

// 满外连接:将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话,那么就使用 NULL 值替代。也叫全外连接
    // 两张表中的所有数据都会出现，上图表中的下左
    hive (default)> select e.empno, e.ename, nvl(e.deptno,d.deptno) from emp e full join dept d on e.deptno = d.deptno;
    
    // 取左表和左表独有的，去掉公共部分，即上图表下右
    // 方法一：通过满外连接，去掉公共部分，即保留左表为空或者右表为空的数据
    hive (default)> select e.empno, e.ename, e.deptno, d.deptno, d.dname from emp e full join dept d on e.deptno = d.deptno where e.deptno is null or d.deptno is null;
    
    // 方法二：通过union将左表独有的和右表独有的合起来
    // 要保证两次查询的字段要是一样的
    hive (default)> select * from (
    select e.empno, e.ename, e.deptno, d.deptno, d.dname from emp e left join dept d on e.deptno = d.deptno where d.dname is null
    union
    select e.empno, e.ename, e.deptno, d.deptno, d.dname from emp e right join dept d on e.deptno = d.deptno where e.ename is null
    )tmp;

nvl函数，nvl(e.deptno, d.deptno)，用于如果前者为null则用后面的值，前者不为null则用前者的值。

union和union all的区别在于是否去重，union会去重。如果需求本身不存在重复数据的话，用union all，因为效率高。

### 多表连接 ###

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

新增数据如下：

location表

1700,Beijing
    1800,London
    1900,Tokyo

创建location表

create table if not exists location(
        loc int,
        loc_name string
    )
    row format delimited fields terminated by ',';

导入数据

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

查询数据

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。

### 笛卡尔积 ###

笛卡尔集会在下面条件下产生

(1)省略连接条件

(2)连接条件无效

(3)所有表中的所有行互相连接

笛卡尔集就是左表的每条和右边的每一条进行连接

hive (default)> select empno, dname from emp, dept;

**生产环境中要去避免的。**

# 排序 #

## 全局排序(Order By) ##

Order By:全局排序,只有一个 Reducer

使用 ORDER BY 子句排序：ASC(ascend): 升序(默认)、DESC(descend): 降序

ORDER BY 子句在 SELECT 语句的结尾

// (1)查询员工信息按工资升序排列
    hive (default)> select * from emp order by sal;
    
    // (2)查询员工信息按工资降序排列
    hive (default)> select * from emp order by sal desc;

### 按照别名排序 ###

// 按照员工薪水的 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;

### 每个 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/hive/data/sortby-result' select * from emp sort by deptno desc;

默认的分区随机分区的，并且是伪随机（相同的数据集分区结果是一样的）。

### 分区(Distribute By) ###

Distribute By: 在有些情况下,我们需要控制某个特定行应该到哪个 reducer,通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。**distribute by** 类似 MR 中 partition(自定义分区),进行分区,结合 sort by 使用

对于 distribute by 进行测试,一定要分配多 reduce 进行处理,否则无法看到 distributeby 的效果。

// 先按照部门编号分区,再按照员工编号降序排序。
    hive (default)> set mapreduce.job.reduces=3;
    hive (default)> insert overwrite local directory '/opt/module/hive/data/distribute-result'
    select * from emp 
    distribute by deptno 
    sort by empno desc;

注意:

distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后,余数相同的分到一个区。

Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

一般来说sorted by 和distribute by一起使用。

### 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;

不常用。

Order By 做全局排序的，只有一个Reducer任务，所有数据放到一起排序。

Sort By 区内排序，不一起使用Distribute By的话是随机排序

Distribute By 指定字段分区

Cluster By Sort By 和 Distribute By是相同字段时，可以省略为Cluster By

[82c8069b7cae4acbbd5140794e2c29dc.png]: /images/20220829/c394b989985d47d9946da63017571f10.png
[https_cwiki.apache.org_confluence_display_Hive_LanguageManual_Select]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NsZWFybHhq_size_16_color_FFFFFF_t_70]: /images/20220829/377eddc39b8a4db2a261a475cfc67da3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NsZWFybHhq_size_16_color_FFFFFF_t_70 1]: /images/20220829/dc64685aa3aa49adaa5d86613687e0bd.png
[b3399c29524e4976a0423d54bef558e3.png]: /images/20220829/342c4f4bef2a490bbcfd6eb1f9a44fee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NsZWFybHhq_size_16_color_FFFFFF_t_70 2]: /images/20220829/1c436649e728448183c41e28ea1a26fe.png