欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

7. Hive技术之DML数据查询

程序员文章站 2022-05-01 10:13:19
...

文章目录

Hive技术之DML数据查询

查询语句语法：

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]

dept和emp表结构：

7. Hive技术之DML数据查询

1. 基本查询（Select…From）

全表和特定列查询

  1. SQL 语言大小写不敏感。
  2. SQL 可以写在一行或者多行
  3. 关键字不能被缩写也不能分行
  4. 各子句一般要分行写。
  5. 使用缩进提高语句的可读性。
#全表查询
select * from emp;

#选择特定列查询
select empno,ename 
from emp;

列别名：

  1. 重命名一个列
  2. 便于计算
  3. 紧跟列名，也可以在列名和别名之间加入关键字 as(可省略)
#查询名称和部门
select ename as name,deptno dn 
from emp;
1.1 算术运算符
运算符 描述
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 按位取反 
#查询出所有员工的薪水后加 1 显示。
select sal+1 from emp;
1.2 常用函数
#求总行数（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;

Limit 语句:

#典型的查询会返回多行数据。limit子句用于限制返回的行数。
select * from emp limit 5;

Where 语句

  1. 使用where子句，将不满足条件的行过滤掉

  2. where子句紧随 from子句

  3. #查询出薪水大于 1000 的所有员工
select * from emp where sal > 1000;
1.3 比较运算符（Between/In/ Is Null）

下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON 和HAVING 语句中。

操作符 ``````````````````````` 支持的数据类型 描述
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 是一个正则表达式，如果 A 与其匹配，则返回 TRUE；反之返回FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹 配，而不是只需与其字符串匹配。 
#查询出薪水等于 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);
1.4 Like 和 和 RLike
  1. 使用 like 运算选择类似的值
  2. 选择条件可以包含字符或数字
    • % 代表零个或多个字符(任意个字符)。
    • _ 代表一个字符。
  3. rlike子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件。
#查找以 2 开头薪水的员工信息
select * from emp 
where sal like '2%';

#查找第二个数值为 2 的薪水的员工信息
select * from emp 
where sal like '_2%';

#查找薪水中含有 2 的员工信息
select * from emp 
where sal rlike '[2]';
1.5 逻辑运算符（And/Or/Not )
操作符 含义
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);

2. 分组查询

2.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.2 Having 语句

having 与 where 不同点：

  1. where 针对表中的列发挥作用，查询数据；having 针对查询结果中的列发挥作用，
    筛选数据。
  2. where 后面不能写聚合函数，而 having 后面可以使用聚合函数。
  3. having 只用于 group by 分组统计语句。
#求每个部门的平均薪水大于 2000 的部门
#求每个部门的平均工资
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;

3. Join 语句

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

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

好处：

  1. 使用别名可以简化查询。
  2. 使用表名前缀可以提高执行效率。
#合并员工表和部门表
select e.empno,e.ename,d.deptno 
from emp e 
join dept d 
on e.deptno=d.deptno;
3.2 内连接

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

select e.empno,e.ename,d.deptno 
from emp e 
join dept d 
on e.deptno = d.deptno;
3.3 左外连接

左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

select e.empno,e.ename,d.deptno 
from emp e 
left join dept d 
on e.deptno = d.deptno;
3.4 右外连接

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

select e.empno,e.ename,d.deptno 
from emp e 
right join dept d 
on e.deptno = d.deptno;
3.5 满外连接

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

select e.empno,e.ename,d.deptno 
from emp e 
full join dept d 
on e.deptno = d.deptno;
3.6 多表连接

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

#创建位置表
create table if not exists location(loc int,loc_name string)
row format delimited fields terminated by '\t';

#导入数据
load data local inpath '/opt/module/data/location.txt' into table location;

#多表连接查询
select e.ename,d.deptno,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.7 笛卡尔积

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

  1. 省略连接条件
  2. 连接条件无效
  3. 所有表中的所有行互相连接
select empno,dname from emp, dept;

连接谓词持 中不支持 or

select e.empno,e.ename,d.deptno
from emp e
join dept d
on e.deptno=d.deptno 
or e.ename=d.dname;
FAILED: SemanticException [Error 10019]: Line 10:3 OR not supported in JOIN currently 'dname'

4. 排序

4.1 全局排序（Order By ）

Order By：全局排序，一个 Reducer

  • ASC（ascend）: 升序（默认）
  • DESC（descend）: 降序

order by 子句在 select 语句的结尾

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

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

#按照别名排序
#按照员工薪水的 2 倍排序
select ename,sal*2 twosal 
from emp 
order by twosal;
4.2 按多个列排序
#按照部门和工资升序排序
select ename,deptno,sal 
from emp 
order by deptno,sal;
4.3 每个 MapReduce 内部排序（Sort By )

Sort By：每个 Reducer 内部进行排序，对全局结果集来说不是排序。

#设置 reduce 个数
set mapreduce.job.reduces=3;
#查看设置 reduce 个数
set mapreduce.job.reduces;

#根据部门编号降序查看员工信息
select * from emp 
sort by empno desc;

#将查询结果导入到文件中（按照部门编号降序排序）
insert overwrite local directory '/opt/module/data/sortby-result' 
select * from emp 
sort by deptno desc;
4.4 分区排序（Distribute By ）

Distribute By：类似 MR 中 partition，进行分区，结合 sort by 使用。
注意，Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by的效果。

#先按照部门编号分区，再按照员工编号降序排序。
set mapreduce.job.reduces=3;

insert overwrite local directory '/opt/module/data/distribute-result'  
select * from emp
distribute by deptno 
sort by empno desc;
4.5 Cluster By

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

#以下两种写法等价
select * from emp 
cluster by deptno;

select deptno,empno from emp 
distribute by deptno 
sort by deptno;
#注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一个分区里面去。

5. 分桶及抽样查询

5.1 分桶表数据存储
  1. 分区针对的是数据的存储路径；分桶针对的是数据文件。
  2. 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。
  3. 分桶是将数据集分解成更容易管理的若*分的另一个技术。
#先创建分桶表，通过直接导入数据文件的方式
create table stu_buck(id int, name string) 
clustered by (id) into 4 buckets
row format delimited fields terminated by '\t';

#查看表结构
desc formatted stu_buck;

#导入数据到分桶表中
load data local inpath '/opt/module/data/student.txt' into table stu_buck;
#查看创建的分桶表中是否分成 4 个桶，发现并没有分成 4 个桶。是什么原因呢？

#创建分桶表时，数据通过子查询的方式导入
#先建一个普通的 stu 表
create table stu(id int, name string)
row format delimited fields terminated by '\t';

#向普通的 stu 表中导入数据
load data local inpath '/opt/module/data/student.txt' into table stu;

#清空 stu_buck 表中数据
truncate table stu_buck;
#导入数据到分桶表，通过子查询的方式
insert into table stu_buck 
select id, name 
from stu;

#发现还是只有一个分桶
#需要设置一个属性
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;
insert into table stu_buck
select id, name from stu;

#查询分桶的数据
select * from stu_buck;

7. Hive技术之DML数据查询

5.2 分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求。

#查询表 stu_buck 中的数据。
select * from stu_buck 
tablesample(bucket 1 out of 4 on id)

注：tablesample 是抽样语句，语法：tablesample (bucket x out ofy) 。

  1. y 必须是 table 总 bucket 数的倍数或者因子。hive 根据 y 的大小，决定抽样的比例。例如，table 总共分了 4 份，当 y=2 时，抽取(4/2=)2 个bucket 的数据，当 y=8 时，抽取(4/8=)1/2 个 bucket 的数据。
  2. x 表示从哪个 bucket 开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table 总 bucket 数为 4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2 个bucket 的数据，抽取第 1(x)个和第 3(x+y)个 bucket 的数据。

注意：x 的值必须小于等于 y 的值，否则:

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

☆

相关标签： Hive

上一篇： kotlin入门 —— 一篇文章就够了

下一篇： Flink读取HDFS上的Parquet文件生成DataSet

推荐阅读