[大数据]Hive(3)

最新推荐文章于 2021-11-27 23:38:15 发布

RegulusF

最新推荐文章于 2021-11-27 23:38:15 发布

阅读量159

点赞数

分类专栏：大数据

本文链接：https://blog.csdn.net/weixin_44699689/article/details/116209236

版权

大数据专栏收录该内容

31 篇文章 1 订阅

订阅专栏

6.查询

6.1 基本查询

--建立员工表
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';

--建立部门表
create table if not exists dept(
deptno int,
dname string,
loc 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;

在这里插入图片描述

--起别名
select ename as name from emp;
--as可以省略
select ename name from emp;

算数运算符：

运算符	描述
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;

在这里插入图片描述

--UDF函数
select substring(ename, 1, 1) from emp;

在这里插入图片描述

--UDAF函数（多个输入得到一个输出）
select count(*) from emp; #得到表有几行

在这里插入图片描述

--limit，取前几行
select * from emp limit 5;

在这里插入图片描述

6.2 条件过滤

比较运算符：

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，如果一边为NULL，返回False
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相匹配，而不是只需与其字符串匹配。

正则表达式

一般字符匹配自己
^ 匹配一行开头 ^R 以R开头
$ 匹配一行结束 R$ 以R结尾
. 匹配任意字符 ^.$ 一行只有一个字符
* 前一个子式匹配零次或多次
[] 匹配一个范围内的任意字符
\ 转义

示例：

--查询工资大于1000的人
select * from emp where sal > 1000;

在这里插入图片描述

--查询工资在500到1000的员工信息
select * from emp where sal between 500 and 1000;

在这里插入图片描述

--通配符字符串匹配 % _
--以A开头的员工
select * from emp where ename like "A%";

在这里插入图片描述

--正则匹配
--以A开头的员工
select * from emp where ename rlike "^A";

在这里插入图片描述

逻辑运算符：

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

示例：

--查询薪水大于1000，部门是30
select * from emp where sal>1000 and deptno=30;

在这里插入图片描述

--查询薪水大于1000，或者部门是30

在这里插入图片描述

--查询除了20部门和30部门以外的员工信息
select * from emp where deptno not IN(30, 20);

在这里插入图片描述

6.3 分组

6.3.1 GroupBy语句

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

--计算emp表每个部门的平均工资
select deptno, avg(sal) avg_sal from emp group by deptno;

在这里插入图片描述

--计算emp每个部门中每个岗位的最高薪水
select deptno, job, max(sal) max_sal from emp group by deptno, job;

在这里插入图片描述

--分组过滤
--计算部门平均工资大于2000的部门

6.3.2 Having语句

having与where不同点
（1）where后面不能写分组函数，而having后面可以使用分组函数。
（2）having只用于group by分组统计语句。

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

在这里插入图片描述

6.4 连接

6.4.1 等值Join

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

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

在这里插入图片描述

6.4.2 内连接

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

6.4.3 左外连接

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

6.4.5 右外连接

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

6.4.6 满外连接

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

6.4.7 多表连接

创建location.txt

1700	Beijing
1800	London
1900	Tokyo

创建表，导入数据

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;进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。
优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

6.3.8 笛卡尔积（避免出现）

笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接

select empno, dname from emp, dept;

在这里插入图片描述

6.5 排序

6.5.1 全局排序（Order By）

Order By：全局排序，只有一个Reducer
ASC（ascend）: 升序（默认）
DESC（descend）: 降序

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

在这里插入图片描述

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

在这里插入图片描述

6.5.2 按别名排序

--按照员工薪水的2倍排序
 select ename, sal*2 twosal from emp order by twosal;

在这里插入图片描述

6.5.3 多列排序

--按照部门和工资升序排序
select ename, deptno, sal from emp order by deptno, sal ;

在这里插入图片描述

6.5.4 局部排序（Sort By）

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

设置Reduce个数

hive (default)> set mapreduce.job.reduces=3;

（在全局排序中Reduce的数量自动降为1）

排序：

select *
from emp
sort by empno desc;

在这里插入图片描述

6.5.5 分区排序

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

--按照部门分区，然后按照员工工资排序
select * from emp
distribute by empno
sort by sal desc;

在这里插入图片描述

6.5.6 Cluster By

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

--如果分区和排序的字段一样，我们可以用cluster by代替
select * from emp distribute by empno sort by empno;
select * from emp cluster by empno;

在这里插入图片描述

6.6 分桶及抽样查询

6.6.1 分桶表数据存储

分区针对的是数据的存储路径；分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。

-- 设置属性
hive (default)> set hive.enforce.bucketing=true;
hive (default)> set mapreduce.job.reduces=-1;

-- 分桶表
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;

-- 先创建一个普通的stu表
create table stu(id int, name string)
row format delimited fields terminated by '\t';
-- 向普通的stu表中导入数据
load data local inpath '/opt/module/datas/student.txt' into table stu;
-- 清空stu_buck表中数据
truncate table stu_buck;
select * from stu_buck;
-- 导入数据到分桶表，通过子查询的方式
insert into table stu_buck
select id, name from stu;

6.6.2 分桶抽样查询

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

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

注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
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