分区表
分区表的定义
- 分区表实际上就是对应一个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;