42 分区表、分桶表、函数

2023-10-08 21:03:28

分区表

分区表的定义

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹

分区表的基本操作

引入分区表（需要根据日期对日志进行管理, 通过部门信息模拟）
```
dept_20200401.log
dept_20200402.log
dept_20200403.log
……
```

创建分区表语法

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

加载数据到分区表中

# 数据准备
# dept_20200401.log
10	ACCOUNTING	1700
20	RESEARCH	1800

# dept_20200402.log
30	SALES	1900
40	OPERATIONS	1700

# dept_20200403.log
50	TEST	2000
60	DEV	1900

# 加载数据
# 分区表加载数据时，必须指定分区
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');
load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition partition(day='20200402');
load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');

查询分区表中的数据

# 单分区查询
select * from dept_partition where day='20200401';

# 多分区联合查询
select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402';

增加分区

# 创建单个分区
alter table dept_partition add partition(day='20200404');

# 同时创建多个分区
alter table dept_partition add partition(day='20200405') partition(day='20200406');

删除分区

# 删除单个分区
alter table dept_partition drop partition (day='20200406');

# 同时删除多个分区
alter table dept_partition drop partition (day='20200404'), partition(day='20200405');

查看分区表有多少个分区
```
show partitions dept_partition
```
查看分区表结构
```
desc formatted dept_partition;
```

二级分区

创建二级分区表

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

# 查询分区数据
select * from dept_partition2 where day='20200401' and hour='12';

把数据直接上传到分区目录上

方式一：上传数据后修复

# 上传数据
dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
dfs -put /opt/module/datas/dept_20200401.log  /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

# 查询数据（查询不到刚上传的数据）
select * from dept_partition2 where day='20200401' and hour='13';

# 执行修复命令
msck repair table dept_partition2;

# 再次查询数据

方式二：上传数据后添加分区

# 添加分区
alter table dept_partition2 add partition(day='201709',hour='14');

方式三：创建文件加后load数据到分区

# 创建目录
dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=15;

# 上传数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
 dept_partition2 partition(day='20200401',hour='15');

# 查询数据
select * from dept_partition2 where day='20200401' and hour='15';

动态分区

参数设置

# 开启动态分区功能（默认ture， 开启）
hive.exec.dynamic.partition=true

# 设置为非严格模式
# 动态分区模式，默认为strict，表示必须指定至少一个分区为静态分区
# nonstrict模式表示允许所有的分区字段都可以使用动态分区
hive.exec.dynamic.partition.mode=nonstrict

# 在所有执行MR的节点上，最大一共可以创建多少个动态分区，默认为1000
hive.exec.max.dynamic.partitions=1000

# 在每个执行MR的节点上，最大可以创建多少个动态分区
# day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错
hive.exec.max.dynamic.partitions.pernode=100

# 整个MR Job中，最大可以创建多少个HDFS文件。默认100000
hive.exec.max.created.files=100000

# 当有空分区生成时，是否抛出异常。一般不需要设置。默认false
hive.error.on.empty.partition=false

案例实操

# 创建目标分区表
create table dept_partition_dy(id int, name string) partitioned by (loc int) row format delimited fields terminated by '\t';

# 设置动态分区
set hive.exec.dynamic.partition.mode = nonstrict;

insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;

# 查看目标分区表的分区情况
show partitions dept_partition;

分桶表

并非所有的数据集都可形成合理的分区
对于一张表或者分区，Hive可以进一步组织成桶
分区针对的是数据的存储路径，分桶针对的是数据文件

数据准备

1001	ss1
1002	ss2
1003	ss3
1004	ss4
1005	ss5
1006	ss6
1007	ss7
1008	ss8
1009	ss9
1010	ss10
1011	ss11
1012	ss12
1013	ss13
1014	ss14
1015	ss15
1016	ss16

创建分桶表

create table stu_bucket(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

导入数据到分桶表中，load方式

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

insert方式将数据导入分桶表

insert into table stu_buck select * from student_insert;

查询分桶的数据

select * from stu_buck;

分桶规则

Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶中

分桶表操作注意事项

reduce的个数设置为-1，让Job自行决定需要用多少个reduce
或者将reduce的个数设置为大于分桶表的桶数
从hdfs中load数据到分桶表中，避免本地文件找不到的问题
不要用本地模式

抽样查询

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

select * from stu_buck tablesample(bucket 1 out of 4 on id);

常用内置函数

空字段赋值

NVL：为NULL的数据赋值

# 如果员工的comm为NULL，则用-1代替
select comm,nvl(comm, -1) from emp;

# 如果员工的comm为NULL，则用领导id代替
select comm, nvl(comm,mgr) from emp;

case when then else end

需求：求不同部门男女各多少人

创建本地emp_sex.txt，导入数据

vim emp_sex.txt
悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女

create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by "\t";

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

按需求查询数据

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

行转列

collect_set(col)：只接收基本数据类型，主要作用是将某字段的值进行去重汇总，产生array字段
concat_ws 必须是string 或"array"

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

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

创建本地constellation.txt，导入数据

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

create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/hive/datas/person_info.txt" into table person_info;

按需求查询数据

SELECT t1.c_b , CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT NAME ,CONCAT_WS(',',constellation,blood_type) c_b
FROM person_info
)t1 
GROUP BY t1.c_b

列转行

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

需求：将电影分类中的数组数据展开

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

创建本地movie.txt，导入数据

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

创建hive表并导入数据

create table movie_info(
    movie string, 
    category string) 
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/hive/datas/movie_info.txt" into table movie_info;

按需求查询数据

SELECT movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name;

窗口函数（开窗函数）

over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的改变而变化
n preceding：往前n行数据
n following：往后n行数据
unbounded：起点
lag(col,n,deafult_val)：往前第n行数据
lead(col,n,deafult_val)：往后第n行数据
ntile(n)：把有序窗口大的行分发搭配指定数据的组中，各个数组有编号，编号从1开始，对于每一行，ntile返回此行所属组的编号，n必须为int类型

数据准备

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

需求

# 查询在2017年4月份购买过的顾客及总人数
# 查询顾客的购买明细及月购买总额
# 上述的场景, 将每个顾客的cost按照日期进行累加
# 查询每个顾客上次的购买时间
# 查询前20%时间的订单信息

创建hive表并导入数据

create table business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/hive/datas/business.txt" into table business;

按需求查询数据

# 查询在2017年4月份购买过的顾客及总人数
select name,count(*) over ()
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

# 查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) 
over(partition by month(orderdate)) from
business;

# 将每个顾客的cost按照日期进行累加
select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 
from business;

# 查看顾客上次的购买时间
select name,orderdate,cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 
from business;

# 查询前20%时间的订单信息
select * from (
    select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
    from business
) t
where sorted = 1;

Rank

rank()排序相同时会重复，总数不会变
dense_rank()排序相同时会重复，总数会减少
row_number()会根据顺序计算

数据准备

name	subject	score
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78

需求

计算每门学科成绩排名

创建Hive表并导入数据

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive/datas/score.txt' into table score;

按需求查询数据

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) rmp
from score;

码农公寓

分区表

分区表的定义

分区表的基本操作

二级分区

创建二级分区表

正常的加载数据

把数据直接上传到分区目录上

动态分区

参数设置

案例实操

分桶表

数据准备

创建分桶表

导入数据到分桶表中，load方式

insert方式将数据导入分桶表

查询分桶的数据

分桶规则

分桶表操作注意事项

抽样查询

常用内置函数

空字段赋值

case when then else end

行转列

列转行

窗口函数（开窗函数）

数据准备

需求

创建hive表并导入数据

按需求查询数据

Rank

数据准备

需求

创建Hive表并导入数据

按需求查询数据

相关文章