Hive学习笔记

一、Hive基本概念

1.1 hive是什么

hive是基于hadoop的一个数仓分析工具，hive可以将hdfs上存储的结构化的数据，映射成一张表，然后让用户写HQL(类SQL)来分析数据

   tel              up           down
1383838438         1345         1567
1383838439         5345         1567
1383838440         1241         16577
1383838441         3453         15757
1383838434         35355        1567567

按照手机号 分组，统计每个手机号的总流量

select  tel,up+down from test; 

hive的本质其实就是hadoop的一个客户端，hive底层不存储任何数据，hive表的数据存在hdfs上，hive表的元数据存在关系型数据库中

默认是derby，我们不一般不用默认的derby来存，一般都会修改为mysql。

元数据：描述数据的数据

Hive其实就是将用户写的HQL，给翻译成对应的mr模板，然后执行这些mr程序

hive底层执行引擎其实就是MapReduce，mr运行在yarn上

1.2 hive的优缺点

优点：操作简单，采用类sql的语法分析数据，门槛低，大大的降低了大数据分析的难度，通用性高

缺点：不够灵活，机翻粒度比较粗，调优困难。因为底层执行引擎还是mr，所以延迟较高，不能像关系型数据库那样，立马返回结果

并且底层存储是hdfs，不支持随机写，只能追加，所以hive不支持行级别的更新和删除（delete 和 update）

1.3 hive的架构原理

客户端：命令行客户端，jdbc客户端

数据存储：hdfs

底层执行引擎：mr

元数据库：hive将元数据默认存在derby中，我们一般在安装hive的时候，会修改成mysql

dirver四个器

解析器：将hql语句转换成AST抽象语法树，解析sql是否有误

编译器：将解析后的hql编译成逻辑执行计划，暂时不执行

优化器：对逻辑计划进行优化，调优

执行器：将优化后的逻辑计划执行，其实就是翻译成对应的mr程序，在yarn上运行

1.4 hive和关系型数据库对比

hive不是数据库，不是数据库，不是数据库

hive除了查询语言HQL跟SQL很像之外，别的跟数据库再也没有半点相似可言

数据更新 数据规模 执行延迟 底层引擎 数据存储

二、Hive安装

2.1 hive访问

1）通过hive自带的beeline客户端访问

beeline -u jdbc:hive2://hadoop102:10000 -n atguigu

2. hive脚本访问

hive

2.2 hive交互命令

交互命令使用场景：在shell脚本里面不能人为的进入hive客户端交互写sql，所以要通过hive -e或者-f两个交互参数进行写入

1）hive -e

hive -e "select * from student"

2）hive -f

hive -f stu.sql

2.3 hive参数设置方式

1 通过配置文件设置 （永久生效）

在hive的家目录下面的conf文件夹下的hive-site.xml hive-env.sh hive-log4j2.properties

2 通过命令行参数来设置 （临时生效，只针对当前客户端连接）

hive -hiveconf 参数名=参数值

beeline -u jdbc:hive2://hadoop102:10000 -n atguigu -hiveconf 参数名=参数值

3 通过set命令设置（临时生效，只针对当前客户端连接）

我们连接到hive的客户端以后，可以通过set语句来设置参数

查看所有参数设置

set;

查看单个参数的值

set 参数名;

设置单个参数的值

set 参数名=参数值;

参数设置优先级：

hive-default.xml < hive-site.xml < -hiveconf 参数名=参数值 < set 参数名=参数值

三、数据类型

3.1 基本数据类型

HIVE	MySQL	JAVA	长度	例子
TINYINT	TINYINT	byte	1byte有符号整数	2
SMALINT	SMALINT	short	2byte有符号整数	20
INT	INT	int	4byte有符号整数	20
BIGINT	BIGINT	long	8byte有符号整数	20
BOOLEAN	无	boolean	布尔类型，true或者false	TRUE FALSE
FLOAT	FLOAT	float	单精度浮点数	3.14159
DOUBLE	DOUBLE	double	双精度浮点数	3.14159
STRING	VARCHAR	string	字符系列。可以指定字符集。可以使用单引号或者双引号。	‘now is the time’ “for all good men”
TIMESTAMP	TIMESTAMP		时间类型
BINARY	BINARY		字节数组

利用基本数据类型建表测试

create table test(id int,weight double,name string,money bigint);

利用insert语句按照指定的数据类型插入一条数据

insert into test values(1001,75,"zhangsan",1000000000000);

3.2 集合数据类型

数据类型	描述	语法示例
STRUCT	和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。2	struct() 例如 struct<street:string, city:string>
MAP	MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素	map() 例如map<string, int>
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。	Array() 例如array

测试数据

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing_10010
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing_10011

在hive中建表描述上述数据

create table person(
name string,
friends array<string>,
children map<string,int>,
address struct<street:string,city:string,email:int>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

加载数据

load data local inpath '/opt/module/hive/datas/person.txt' into table person;

查询数据

select * from person;

查出来songsong 这个人的姓名，第一个朋友，孩子xiaoxiao song的年龄，和他的邮编

select name, friends[0],children['xiaoxiao song'],address.email from person where name = "songsong";

select name, friends[1],children['xiao song'],address.street from person;

3.3 类型转换

1）隐式（自动）类型转换

（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。

（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。

（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。

（4）BOOLEAN类型不可以转换为任何其它的类型。

2）显示（强制）类型转换

​ CAST( vlaue AS type)

例如：select * from cast('1' as int) + 2;

四、DDL数据定义语言

4.1 库的DDL

1 创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name        --指定数据库名称
[COMMENT database_comment]                           --指定数据库描述
[LOCATION hdfs_path]                                 --指定创建的数据库在hdfs上存储的路径
[WITH DBPROPERTIES (property_name=property_value, ...)];   --指定库的一些属性

案例实操

create database if not exists db_hive
comment "this is my first db"
with dbproperties ("name"="db_hive","owner"="atguigu");

create database if not exists db_hive;

--创建数据库，并指定在hdfs上的路径
create database if not exists db_hive2
location '/db_hive2';

2 查询数据库

show databases；

3 查看数据库详情

--简单查看
desc database 数据库名;
--详细查看 （详细查看可以看到库的属性信息，简单查看看不到）
desc database extended 数据库名;

4 切换数据库

use 数据库名;

5修改数据库

只能修改数据库的属性信息，别的都无法更改。例如：库名，库的存储位置等元数据信息无法更改

alter database db_hive set dbproperties('createtime'='20200624');

6删除数据库

注意：删除掉数据库以后，hdfs上对应的目录也会删除，谨慎操作。

drop database 数据库名 cascade;

--如果数据库不为空，可以在最后加上cascade强制删除
drop database 数据库名 cascade;

--为了更严谨，我们可以在删除之前判断数据库是否存在
drop database if exists 数据库名 cascade;

4.2表的DDL

1创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name          --指定表名  【external 外部表/内部表】
[(col_name data_type [COMMENT col_comment], ...)]           --指定表的列名，列类型 【列描述】
[COMMENT table_comment]                                     --指定表的描述
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  --指定分区表的分区字段（分区字段可以是多个）
[CLUSTERED BY (col_name, col_name, ...)     --指定分桶表的分桶字段  
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]  --指定分桶表桶内排序字段   指定分桶的个数
[ROW FORMAT DELIMITED      --指定hive表在hdfs上存储的原始数据的格式
 [FIELDS TERMINATED BY char]     --每行数据中字段的分隔符  ascII码表的第一个字符  ^A 
 [COLLECTION ITEMS TERMINATED BY char]   --集合元素分隔符  ascII码表的第二个字符  ^B
 [MAP KEYS TERMINATED BY char]  --map集合中 key 和 value 的分隔符    ascII码表的第三个字符  ^C
 [LINES TERMINATED BY char]      --每行数据的分隔符     默认值：'\n'
]  
[STORED AS file_format]         --指定hive的数据在hdfs上存储的格式
[LOCATION hdfs_path]            --指定hive数据在hdfs上存储的路径  默认值 /user/hive/warehouse/数据库名
[TBLPROPERTIES (property_name=property_value, ...)]    --指定表的属性
[AS select_statement]    --按照as后面的查询语句的结果来创建表，复制表结构以及表数据
[LIKE table_name]     --按照like后面的表结构来创建表，只复制表结构，不复制表数据

2 管理表和外部表

管理表（内部表）：hive掌控者这个数据的生命周期，如果删除一个管理表，hdfs上存储的数据也跟着一起删除。所以一般我们创建管理表时，一般不会再location表的存储路径，就默认放在/user/hive/warehouse下

外部表：hive不完全掌控外部表的数据的生命周期，删除外部表，只删除hive表的元数据，不会删除掉hdfs上存储的数据

一般外部表都是先有的hdfs上的数据，然后我们创建一个外部表，手动指定这个外部表的存储路径

3 创建管理表

create table student(
id int,name string
)
row format delimited fields terminated by '\t';

create table student2(
id int,name string
);
--根据AS select语句查询结构创建表,复制表结构，复制表数据
create table student3 as select * from student;

--根据like 创建表，只复制表结构，不复制表数据
create table student4 like student;

查看表信息

--简单查看表信息
desc 表名;

--详细查看表信息
desc formatted 表名;

删除管理表

注意：删除管理表同时会删除hdfs上对应目录的数据，谨慎操作，数据无价

drop table student3;

4 创建外部表

注意：外部表创建时要加external，外部表的好处就是，删除表的时候，只删除表的元数据信息，不删hdfs上存储的数据，更安全。

建表语句

create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t'
location '/company/dept';

create external 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'
location '/company/emp';

5 外部表和内部表的转换

通过设置表属性“EXTERNAL”来控制表是外部表还是内部表

TRUE 是 外部表 FALSE 是内部表

alter table student set tblproperties('EXTERNAL'='TRUE/FALSE');

6修改表

重名表 注意：重命名表会一起修改hdfs上对应的目录名，前提这个表必须是管理表，并且创建这个管理表时没有自定义location

ALTER TABLE table_name RENAME TO new_table_name

更新列

更新列，列名可以随意修改，列的类型只能小改大，不能大改小（遵循自动转换规则）

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加列

ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...) 

替换列

ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

7清空表

注意：只能truncate 管理表，外部表不能truncate。truncate的本质其实就是删除hdfs上对应路径的数据。

truncate table 表名;

五 DML数据操作

5.1 导入

5.1.1 load

1 通过load命令加载数据

load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];

2 通过本地方式导入

注意：通过本地路径load，本质上其实就是将本地的文件put到hdfs对应的表目录

load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;

3通过hdfs路径导入

注意：通过hdfs路径导入，本质是将hdfs上路径的数据剪切到对应的表的hdfs路径

load data inpath '/student.txt' into table student;

4 覆盖导入

注意:加上关键字overwrite 就是覆盖导入，不加的话，默认是追加导入

load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;

5.1.2 insert

1) 基本模式插入

into是追加插入，overwrite是覆盖插入，此方式一般没人用

insert into/overwrite table student values(1018,'ss18'),(1019,'ss19');

2）通过查询结果插入

此方式用的比较多，一般都是查询原始表的数据到临时表，注意select之前不能加as，跟创建表时as select区分开

insert into table student2  select * from student where id < 1006;
insert overwrite table student2  select * from student where id < 1006;

注意：通过inset插入数据，数据格式和列的数量要一致才可以。

5.1.3 建表语句后+ as select

注意：建表语句后跟select语句时，as不能省略，跟上面的insert into刚好相反

create table student3 as  select * from student;

5.1.4 建表指定location位置

提前把数据传到hdfs上，然后创建表的时候，指定表的位置为数据的路径

注意 location后面一定要给一个目录，不能直接给文件路径。

create table student5(
id int,name string
)
row format delimited fields terminated by '\t'
location '/student';

5.1.5 import导入

注意：使用import导入之前，得使用export导出数据，并且因为export会把数据和元数据一起导出，所以我们使用import导入的时候，表不能存在，否则会元数据冲突，报错。

import table student6  from '/stu';

5.2 数据导出

5.2.1 insert 导出

无格式导出

insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;

格式化导出

insert overwrite local directory '/opt/module/hive/datas/export/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;

注意：因为insert导出，后面只能跟overwrite，所以我们给路径的时候，一定要具体，防止hive误删重要文件、

这个导出路径可以不存在，hive会帮我们创建路径

5.2.2hadoop命令导出

hadoop dfs -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student3.txt;

5.2.3hive shell 命令导出

hive -e 'select * from db_hive.student' > /opt/module/hive/datas/stu.txt

5.2.4 export 导出

注意;export导出只能导出到hdfs上，并且会将元数据和数据一起导出。

export table db_hive.student to '/stu';

六 查询DQL

SELECT [ALL | DISTINCT] select_expr, select_expr, ...     --指定查询字段
  FROM table_reference                                    --从哪个表查询
  [WHERE where_condition]                                --指定where过滤条件
  [GROUP BY col_list]                                   --指定分组条件
  [ORDER BY col_list]                                 --指定排序条件
  [CLUSTER BY col_list    
    | [DISTRIBUTE BY col_list] [SORT BY col_list]        --hive排序四个by
  ]
 [LIMIT number]                      --限制输出结果条数

6.1 基本查询

6.2 分组查询

6.3 join关联查询

6.4 排序

1 全局排序 order by

Order By：全局排序，只有一个Reducer，把所有数据都放在一个分区里面进行排序，这样才能保证输出的结果集是全局有序的。

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

按照多个列，双重排序

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

2 单个reducer排序 sort by

注意：因为单独使用sort by的话，只能给每个分区内的数据排序，但是不能指定分区内的数据都有哪些，属于随机给数据分配分区

因此没人会单独使用sort by来排序，因为出来的结果集没有意义

3 分区 distribute by

指定分区字段，一般在sort by之前都会加上 distribute by

4 分区排序 Cluster by

cluster by属于 distribute by + sort by的结合，但是前提是 分区字段和排序字段是同一个的时候，才可以替换。并且排序只能升序排，不能再指定desc和asc

select * from emp distribute by deptno sort by deptno;
--上面的sql可以简写成下面的
select * from emp cluster by deptno;

七 分区表 分桶表

7.1分区表

hive存在问题：hive里面没有索引机制，每次查询的时候，hive会暴力扫描整张表。

分区表的本质就是分目录，按照业务需求，把数据分成多个目录存储，然后查询的时候就可以通过where条件指定对应的分区

创建分区表语法

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

分区字段属于分区表的一个伪列，数据里面并没有记录这列的值，分区字段的值体现在分区目录名上面。

往分区表里正常load数据，一定要指定分区

load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');

分区表查询数据

select * from dept_partition where day = '20200401';


select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402'
union
select * from dept_partition where day='20200403';

select * from dept_partition where day = '20200401' or day = '20200402';

查看分区表有多少分区

show partitions dept_partition;

删除分区

注意：删除分区会一起删除掉分区内的数据

alter table dept_partition drop partition (day='__HIVE_DEFAULT_PARTITION__');
--删除多个分区  注意：多个分区间必须有逗号，没有会报错
alter table dept_partition drop partition(day='20200405'),partition(day='20200406');

增加分区

 alter table dept_partition add partition(day='20200404') ;
 --增加多个分区  注意：多个分区间不能逗号，有会报错
 alter table dept_partition add partition(day='20200405') partition(day='20200406');

查看分区表信息

desc formatted dept_partition;

7.2 二级分区表

创建二级分区表

create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';

给二级分区正常加载数据

load data local inpath '/opt/module/hive/datas/dept_20200401.log' 
into table dept_partition2 
partition(day='20200401',hour='12');

给二级分区增加分区

alter table dept_partition2 add partition(day='20200403',hour='01') partition(day='20200403',hour = '02');

给二级分区删除分区

alter table dept_partition2 drop partition(day='20200403',hour='01'),partition(day='20200403',hour = '02');

分区表和元数据对应三种方式

1 先上传 再修复表

msck repair table dept_partition2;

2 先上传数据，然后手动添加分区

3 直接load load数据的时候直接指定分区字段的值，这个时候不仅会上传数据，还会创建对应的分区

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

八 函数

查看系统自带函数

show functions;

查看函数具体用法

desc function extended upper;

1 空字段赋值 NVL

将null值转换成我们想要的值

select sal,comm,sal+ NVL(comm,0) money from emp;

select ename,job,sal,mgr,comm,NVL(comm,mgr) from emp;

2 CASE WHEN

name	dept_id	sex
悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女

期望结果 求出不同部门男女各多少人

dept_Id     男       女
A     		2       1
B     		1       2

分析过程

第一步：先求出每个部门有多少人

select
	dept_id,
	sum(1)
from  emp_sex
group by   dept_id ;

第二步：求出每个部门男女各多少人

select
	dept_id,
	sum(case sex when '男' then 1 else 0 end) man,
	sum(case sex when '女' then 1 else 0 end) woman
from  emp_sex
group by   dept_id ;

3 行转列 多行转一列

相关函数

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

注意：CONCAT_WS must be “string or array”

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

COLLECT_LIST(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行不去重汇总，产生array类型字段。

原始数据

name	constellation	blood_type
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A
苍老师	白羊座	B

需求 把星座和血型一样的人归类到一起

射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋|苍老师

实现：

SELECT 
	t1.c_b,
	concat_ws('|',COLLECT_LIST(t1.name))
FROM 
(
	select 
		name,
		concat_ws(',',constellation,blood_type) c_b 
	from person_info
) t1
group by t1.c_b;

4 列转行 一列转多行

相关函数

Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

原始数据

movie	category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

需求

《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难

实现 第一步，炸开类型

select
	explode(split(category,','))
from movie_info;

实现第二步：尝试使用join进行连接，但是不行，没有办法写join条件，造成了笛卡尔积

SELECT 
	t1.movie,
	t2.category_name
from
(
	select movie from  movie_info
) t1
left join
(
	select
		explode(split(category,',')) category_name
	from movie_info
) t2;

实现第三步：

我们的需求是·想让炸开后的临时表和炸开之前表所在行的其他字段进行join

hive帮我们实现了这个需求 lateral view （侧写视图）

select
	movie,
	category_name
from movie_info
LATERAL VIEW explode(split(category,',')) tmp as category_name;

5 窗口函数

1）定义

窗口函数属于sql中比较高级的函数

mysql从8.0版本才支持窗口函数，我们学的5.6,5.7都有窗口函数

oracle 里面一直支持窗口函数

hive也支持窗口函数

以下函数才是窗口函数

窗口函数：

LEAD LEAD(col,n, default_val)：往后第n行数据 col 列名 n 往后第几行 默认为1 默认值 默认null

LAG LAG(col,n,default_val)：往前第n行数据 col 列名 n 往前第几行 默认为1 默认值 默认null

FIRST_VALUE 在当前窗口下的第一个值 FIRST_VALUE （col,true/false） 如果设置为true，则跳过空值。

LAST_VALUE 在当前窗口下的最后一个值 LAST_VALUE （col,true/false）如果设置为true，则跳过空值。

标准聚合函数：

• COUNT
• SUM
• MIN
• MAX
• AVG

分析排名函数

• RANK
• ROW_NUMBER
• DENSE_RANK
• NTILE

窗口函数=函数+窗口

窗口：函数在运算时，我们可以指定函数运算的数据范围

2）语法

窗口函数 over([partition by 字段] [order by 字段] [ 窗口语句])

partition by 给查出来的结果集按照某个字段分区，分区以后，开窗的大小最大不会超过分区数据的大小

一旦分区之后，我们必须在单个分区内指定窗口。

order by 给分区内的数据按照某个字段排序

3) 窗口语句

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

两种特殊情况

当指定ORDER BY缺少WINDOW子句时，WINDOW规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

如果同时缺少ORDER BY和WINDOW子句，则WINDOW规范默认为ROW BETWEENUND UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING。

以下函数在over（）里面只能分区和排序，不能自定义窗口大小了，也就是不能再写window字句

排序分析函数 都不能写 例如： Rank, NTile, DenseRank, CumeDist, PercentRank.

Lead 和 Lag不能写

窗口需求

需求1 查询在2017年4月份购买过的顾客及总人数

select  
	name,
	count(1) over(rows between UNBOUNDED  PRECEDING and UNBOUNDED FOLLOWING)
from business
where month(orderdate) =4
group by name;

由于窗口语句有两种特殊情况，我们这种刚好符合第二种，因此可以省略掉窗口语句

select  
	name,
	count(1) over()
from business
where month(orderdate) =4
group by name;

需求2 查询顾客的购买明细及月购买总额

SELECT 
	name,
	orderdate,
	cost,
	sum(cost ) over(partition by name,month(orderdate) )
from business;

需求3 上述的场景, 将每个顾客的cost按照日期进行累加

SELECT 
	name,
	orderdate,
	cost,
	sum(cost) over(partition by name order by orderdate rows between UNBOUNDED  PRECEDING and CURRENT ROW) cost1,
	sum(cost) over(partition by name order by orderdate) cost2
from business;

需求4 查询顾客购买明细以及上次的购买时间和下次购买时间

select
	name,
	orderdate,
	cost,
	LAG(orderdate,1,'无') over(partition by name order by orderdate) prev_time,
	LEAD(orderdate,1,'无') over(partition by name order by orderdate) next_time
from business;

需求5 查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间

注意：LAST_VALUE和FIRST_VALUE 需要自定义windows字句，否则出现错误

select
	name,
	orderdate,
	cost,
	FIRST_VALUE(orderdate) 
	over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED  PRECEDING and UNBOUNDED FOLLOWING) first_time,
	LAST_VALUE(orderdate) 
	over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED  PRECEDING and UNBOUNDED FOLLOWING) last_time
from business;

需求6 查询前20%时间的订单信息

select
	t1.*
FROM 
(
	select
		name,
		orderdate,
		cost,
		ntile(5) over(order by orderdate ) nsort
	from business
) t1
where t1.nsort = 1;

6 排名行数

RANK() 排序相同时会重复，会跳号

DENSE_RANK() 排序相同时会重复，不会跳号

ROW_NUMBER() 会根据顺序计算

SELECT 
	name,
	subject,
	score,
	rank() over(PARTITION by subject order by score desc) rp,
	DENSE_RANK() over(PARTITION by subject order by score desc) drp,
	ROW_NUMBER() over(PARTITION by subject order by score desc) rowp
from score;

几个关键字总结

1 建表 ：PARTITIONED BY(分区表) CLUSTERED BY（分桶表）

2 查询：ORDER BY(全局排序) SORT BY(区内排序)

DITRIBUTE BY（分区） CLUSTER BY（分区排序）

3 窗口函数：PARTITION BY(对数据分区) ORDER BY（排序）

7自定义udf

1 编写代码继承genericUDF类。实现里面的三个方法

2 创建临时函数

添加jar包的类路径给hive，注意是临时生效

add jar /opt/module/hive/datas/myudf.jar;

创建临时函数

create  temporary function my_len as "com.atguigu.hive.udf.MyStringLength";

删除临时函数

drop  temporary function my_len;

注意：临时函数只跟会话有关系，只要会话不断，在当前会话下，任意一个库都可以使用。其他会话全都不能使用。

3 创建永久函数

注意：因为永久函数是永久生效的，我们推出当前会话以后，其他会话也要使用永久函数，因此我们就不能简单的使用add jar来添加hive的类路径了

创建永久函数

注意：此时要使用USING JAR的方式来添加函数的jar包类路径，并且这个路径必须是hdfs路径

create function my_len2 as "com.atguigu.hive.udf.MyStringLength" USING JAR 'hdfs://hadoop102:9820/hivejar/myudf.jar';

删除永久函数

drop function my_len2;

注意：永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。
然后使用永久函数的时候，需要在指定的库里面操作,或者在其他库里面使用的话得加上 库名.函数名

九 压缩与存储

1. Hadoop压缩配置

1. MR支持的压缩编码

压缩格式	算法	文件扩展名	是否可切分
DEFLATE	DEFLATE	.deflate	否
Gzip	DEFLATE	.gz	否
bzip2	bzip2	.bz2	是
LZO	LZO	.lzo	是
Snappy	Snappy	.snappy	否

为了支持多种压缩/解压缩算法，Hadoop引入了编码/解码器，如下表所示：

压缩格式	对应的编码/解码器
DEFLATE	org.apache.hadoop.io.compress.DefaultCodec
gzip	org.apache.hadoop.io.compress.GzipCodec
bzip2	org.apache.hadoop.io.compress.BZip2Codec
LZO	com.hadoop.compression.lzo.LzopCodec
Snappy	org.apache.hadoop.io.compress.SnappyCodec

压缩性能的比较：

压缩算法	原始文件大小	压缩文件大小	压缩速度	解压速度
gzip	8.3GB	1.8GB	17.5MB/s	58MB/s
bzip2	8.3GB	1.1GB	2.4MB/s	9.5MB/s
LZO	8.3GB	2.9GB	49.3MB/s	74.6MB/s

2.压缩参数配置

要在Hadoop中启用压缩，可以配置如下参数（mapred-site.xml文件中）：

参数	默认值	阶段	建议
io.compression.codecs （在core-site.xml中配置）	org.apache.hadoop.io.compress.DefaultCodec, org.apache.hadoop.io.compress.GzipCodec, org.apache.hadoop.io.compress.BZip2Codec,org.apache.hadoop.io.compress.Lz4Codec	输入压缩	Hadoop使用文件扩展名判断是否支持某种编解码器
mapreduce.map.output.compress	false	mapper输出	这个参数设为true启用压缩
mapreduce.map.output.compress.codec	org.apache.hadoop.io.compress.DefaultCodec	mapper输出	使用LZO、LZ4或snappy编解码器在此阶段压缩数据
mapreduce.output.fileoutputformat.compress	false	reducer输出	这个参数设为true启用压缩
mapreduce.output.fileoutputformat.compress.codec	org.apache.hadoop.io.compress. DefaultCodec	reducer输出	使用标准工具或者编解码器，如gzip和bzip2
mapreduce.output.fileoutputformat.compress.type	RECORD	reducer输出	SequenceFile输出使用的压缩类型：NONE和BLOCK

2. 开启Map输出阶段压缩（MR引擎）

开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。具体配置如下：

（1）开启hive中间传输数据压缩功能

hive (default)>set hive.exec.compress.intermediate=true;

（2）开启mapreduce中map输出压缩功能

hive (default)>set mapreduce.map.output.compress=true;

（3）设置mapreduce中map输出数据的压缩方式

hive (default)>set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

（4）执行查询语句

hive (default)> select count(ename) name from emp;

3. 开启Reduce输出阶段压缩

​ 当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。

（1）开启hive最终输出数据压缩功能

hive (default)>set hive.exec.compress.output=true;

（2）开启mapreduce最终输出数据压缩

hive (default)>set mapreduce.output.fileoutputformat.compress=true;

（3）设置mapreduce最终数据输出压缩方式

hive (default)> set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;

（4）设置mapreduce最终数据输出压缩为块压缩

hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK;

（5）测试一下输出结果是否是压缩文件

hive (default)> insert overwrite local directory '/opt/module/hive/datas/compress/' select * from emp distribute by deptno sort by empno desc;

4. 文件存储格式

Hive支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。

1.列式存储和行式存储

行式存储：一行一行的截取，存储成一行
列式存储：一列一列的截取，存储成一列
大数据存储一般采用列式存储

1. 行存储的特点

​ 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。

2. 列存储的特点

​ 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；

ORC和PARQUET是基于列式存储的。

2. TextFile格式

​ 默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

3. Orc格式

​ Orc (Optimized Row Columnar)是Hive 0.11版里引入的新的存储格式。

	每个Orc文件由1个或多个stripe组成，每个stripe一般为HDFS的块大小，每一个stripe包含多条记录，这些记录按照列进行独立存储，对应到Parquet中的row group的概念。每个Stripe里有三部分组成，分别是Index Data，Row Data，Stripe Footer：
	1）Index Data：一个轻量级的index，默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在Row Data中的offset。
	2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。
	3）Stripe Footer：存的是各个Stream的类型，长度等信息。
每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。

4. Parquet格式

	Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。
	（1）行组(Row Group)：每一个行组包含一定的行数，在一个HDFS文件中至少存储一个行组，类似于orc的stripe的概念。
	（2）列块(Column Chunk)：在一个行组中每一列保存在一个列块中，行组中的所有列连续的存储在这个行组文件中。一个列块中的值都是相同类型的，不同的列块可能使用不同的算法进行压缩。
	（3）页(Page)：每一个列块划分为多个页，一个页是最小的编码的单位，在同一个列块的不同页可能使用不同的编码方式。
	通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。Parquet文件的格式。
	一个文件中可以存储多个行组，文件的首位都是该文件的Magic Code，用于校验它是否是一个Parquet文件，Footer length记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每一个行组的元数据，每一页的开始都会存储该页的元数据，在Parquet中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前Parquet中还不支持索引页。

十 企业级调优

1. Fetch抓取

​ Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM emp;在这种情况下，Hive可以简单地读取emp对应的存储目录下的文件，然后输出查询结果到控制台。

​ 在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。

<property>
    <name>hive.fetch.task.conversion</name>
    <value>more</value>
    <description>
      Expects one of [none, minimal, more].
      Some select queries can be converted to single FETCH task minimizing latency.
      Currently the query should be single sourced not having any subquery and should not have any aggregations or distincts (which incurs RS), lateral views and joins.
      0. none : disable hive.fetch.task.conversion
      1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
      2. more  : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)
    </description>
</property>

案例实操

​ （1）把hive.fetch.task.conversion设置成none，然后执行查询语句，都会执行mapreduce程序。

hive (default)> set hive.fetch.task.conversion=none;
hive (default)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;

2. 本地模式

​ 大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。

​ 用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化。

set hive.exec.mode.local.auto=true;  //开启本地mr
//设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

（1）开启本地模式，并执行查询语句

hive (default)> set hive.exec.mode.local.auto=true; 
hive (default)> select * from emp cluster by deptno;
Time taken: 1.328 seconds, Fetched: 14 row(s)

（2）关闭本地模式，并执行查询语句

hive (default)> set hive.exec.mode.local.auto=false; 
hive (default)> select * from emp cluster by deptno;
Time taken: 20.09 seconds, Fetched: 14 row(s)

3. 表的优化

1. 小表、大表Join

​ 将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率；再进一步，可以使用map join让小的维度表（1000条以下的记录条数）先进内存。在map端完成reduce。

​ 实际测试发现：新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。

2. 大表Join小表

1.空key过滤

​ 有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。

2.空key转换

​ 有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。

3. MapJoin

​ 如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。

mapjoin： 大表join小表， 小表会加载到缓存里， 然后在map端获取缓存文件进行join
reducejoin：正常跑， 然后在reduce端进行join.

（1）设置自动选择Mapjoin

set hive.auto.convert.join = true; 默认为true

（2）大表小表的阈值设置（默认25M以下认为是小表）：

set hive.mapjoin.smalltable.filesize=25000000;

4. Group By

​ 默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。

​ 并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

（1）是否在Map端进行聚合，默认为True

set hive.map.aggr = true

（2）在Map端进行聚合操作的条目数目

set hive.groupby.mapaggr.checkinterval = 100000

（3）有数据倾斜的时候进行负载均衡（默认是false）

set hive.groupby.skewindata = true

​ 当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作。

5. Count(Distinct) 去重统计

​ 数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换,但是需要注意group by造成的数据倾斜问题.

6.笛卡尔积

​ 尽量避免笛卡尔积，join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积。

7. 行列过滤

​ 列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。

​ 行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤.

4. 合理设置Map及Reduce数

1）通常情况下，作业会通过input的目录产生一个或者多个map任务。

主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件块大小。

2）是不是map数越多越好？

答案是否定的。如果一个任务有很多小文件（远远小于块大小128m），则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。

3）是不是保证每个map处理接近128m的文件块，就高枕无忧了？

答案也是不一定。比如有一个127m的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。

针对上面的问题2和3，我们需要采取两种方式来解决：即减少map数和增加map数；

1. 复杂文件增加Map数

​ 当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。

增加map的方法为：根据computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。

2. 小文件合并

​ 1）在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。

set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

2）在Map-Reduce的任务结束时合并小文件的设置：在map-only任务结束时合并小文件，默认true

SET hive.merge.mapfiles = true;

在map-reduce任务结束时合并小文件，默认false

SET hive.merge.mapredfiles = true;

合并文件的大小，默认256M

SET hive.merge.size.per.task = 268435456;

当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge

SET hive.merge.smallfiles.avgsize = 16777216;

3. 合理设置Reduce数

1）调整reduce个数方法一

​ （1）每个Reduce处理的数据量默认是256MB

hive.exec.reducers.bytes.per.reducer=256000000

​ （2）每个任务最大的reduce数，默认为1009

hive.exec.reducers.max=1009

​ （3）计算reducer数的公式

N=min(参数2，总输入数据量/参数1)

2）调整reduce个数方法二

在hadoop的mapred-default.xml文件中修改

设置每个job的Reduce个数

set mapreduce.job.reduces = 15;

3）reduce个数并不是越多越好

​ （1）过多的启动和初始化reduce也会消耗时间和资源；

​ （2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；

​ 在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适；

5. 并行执行

​ Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成。

​ 通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。

set hive.exec.parallel=true;              //打开任务并行执行，默认为false
set hive.exec.parallel.thread.number=16;  //同一个sql允许最大并行度，默认为8。

​ 当然，得是在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来。

6. 严格模式

​ Hive可以通过设置防止一些危险操作：

1）分区表不使用分区过滤

将hive.strict.checks.no.partition.filter设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
2）使用order by没有limit过滤

将hive.strict.checks.orderby.no.limit设置为true时，对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。

3）笛卡尔积

将hive.strict.checks.cartesian.product设置为true时，会限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在 执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。

7. JVM重用

问题：hive中查询缓慢

原因：数据源hdfs中查询表中 小文件过多，导致产生过万的maptask ,JVM启动创建实例消耗过多资源，导致查询缓慢

解决：在mapred-site.xml配置文件中 设置mapred.job.reuse.jvm.num.tasks=10参数，该参数为JVM实例重用数

或者运行Hive 直接输入命令set mapred.job.reuse.jvm.num.tasks=10;配置生效

 

弊端：开启JVM重用时，task用过的插槽会一直被占用，直到整个查询任务结束，插槽才会被释放