1,为什么要有分区表?
大家写程序都有一个习惯,输出日志内容,输出日志内容最喜欢的方式是以天为单位进行存储,这样能够方便的快速定位哪一天.
分区表也是如此,按某种条件进行分区,查询时候加上分区条件,避免了全表的查询,极大的提高了效率,所以数据量很大情况下一定建立分区表
2,Hive如何创建分区表?
2,1创建单级分区
参考官网创建语法:
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
创建示例:
create table teacher(
tid int,
tname string
)
PARTITIONED BY (day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
desc formatted teacher;
image.png
往teacher表里面添加数据
load data local inpath '/home/hadoopadmin/teacher.txt' into table teacher partition (day=20201212) ;
load data local inpath '/home/hadoopadmin/teacher.txt' into table teacher partition (day=20201213) ;
load data local inpath '/home/hadoopadmin/teacher.txt' into table teacher partition (day=20201214) ;
查看Hdfs上面对应的数据
image.png
这里注意了,如果创建的是分区表,那么在像表里面插入数据时候,必须加上分区字段,否则报错
Hive元数据查看
select * fromPARTITIONS
image.png
2.2创建多级分区
创建分区表时候既可以指定一个分区字段,还可以指定多个分区字段,即多级分区
create table teacher_birthday(
id int,
name string
)
PARTITIONED BY (day string,hour string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
desc formatted teacher_birthday
image.png
往teacher_birthday里面添加数据
hive (two)> load data local inpath '/home/hadoopadmin/teacher.txt' into table teacher_birthday partition (day='20201213',hour='13');
hive (two)> load data local inpath '/home/hadoopadmin/teacher.txt' into table teacher_birthday partition (day='20201213',hour='14');
hive (two)> load data local inpath '/home/hadoopadmin/teacher.txt' into table teacher_birthday partition (day='20201214',hour='15');
hive (two)> load data local inpath '/home/hadoopadmin/teacher.txt' into table teacher_birthday partition (day='20201214',hour='16');
查看Hdfs上面对应的数据
image.png
image.png
image.png
同样,如果在加载数据到分区时候不指定分区字段或者分区字段缺失部分,便会报错
Hive元数据查看
select * fromPARTITIONS
image.png
分区表创建之后尽量使用分区条件查询,避免全表查询,否则表分区并没有意义
查询时候就可以把分区名称作为一个条件查询
image.png
另外创建静态分区时候,分区的名称不能和表里面字段名称有重复的,否则报错
image.png
2.4分区表数据的MSCK和ALTER
如果创建的不是一个分区表,如果是一个普通表的话,可以使用hdfs dfs -put命令,将linux上面的相同数据类型的文件直接放到该表对应的Hdfs的路径下面,然后执行select * from 表,便可以看到这个数据能够查询出来
但是分区表比较特殊,如果是将文件移动到现有的分区下面,比如20201214分区已经存在,那么将同类型文件放到该分区下,然后执行select * from teacher where day=20201214,数据生效
如果在该表的Hdfs路径下创建一个分区文件夹20201215,里面放一个数据文件
image.png
image.png
发现查不到数据,有两种方法:
alter方法:
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
alter table teacher add partition (day=20201215);
image.png
MSCK方法:
msck table tablename:扫描以下有哪些需要修复的
image.png
msck repair table tablename:直接修复
image.png
!!!但是注意的是,msck是全表扫描的,数据量大情况下很定会速度受到影响,但是就是省事,建议能用alter就用alter,因为那样资源占用小,毕竟是找到特定的分区添加上.效率和资源占用肯定是更小.
如果只是删除静态分区对应的hdfs上面的路径,我们通过show partitions tablename还是能够看到名字,执行alter table teacher drop partition (day=20201216)才算删除
3,动态分区(dynamic partitions)
为什么会需要动态分区?
create table emp_partition (
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double
)
partitioned by (deptno int)
ROW FORMAT DELIMITED FIELDS TERMONATED BY '\t';
把三个部门数据按照部门存储到三个分区
insert into table emp_partition partition(deptno=10)
select empno,ename,job,mgr,hiredate,sal,comm from emp where deptno=10;
insert into table emp_partition partition(deptno=20)
select empno,ename,job,mgr,hiredate,sal,comm from emp where deptno=20;
insert into table emp_partition partition(deptno=30)
select empno,ename,job,mgr,hiredate,sal,comm from emp where deptno=30;
采用静态分区方式存储数据需要有多少的数据就要分成多少分别存入
所以就有了动态分区,创建分区表方式不变,变化的是插入数据阶段,之前是要具体指定deptno是多少,但是现在不需要,只需要在查询字段上面加上deptno即可
insert into table emp_dynamic_partition partition(deptno)
select empno,ename,job,mgr,hiredate,sal,comm, deptno from emp;
这是标准的写法,但是其实可以deptno,下面是lala也可以,只要是保障除了lala字段,前面几个字段和插入的表一致即可
insert into table emp_dynamic_partition partition(deptno)
select empno,ename,job,mgr,hiredate,sal,comm, lala from emp;
这样就实现了动态创建分区,但是需要开启两个参数,
SET hive.exec.dynamic.partition=true; #开启动态分区，默认是true
SET hive.exec.dynamic.partition.mode=nonstrict; #开启允许所有分区都是动态的，否则必须要有静态分区才能使用,因为我们创建了一个动态分区,只有一个分区,是动态的,这就是全是动态分区
上面就实现了动态分区,这个分区数是group by deptno,能够group by出来多少结果,就意味着Hive里面有多少分区.每个分区的deptno都不一样,10个不同的deptno,在Hive就是10个文件夹
如果要是多个动态分区的话,因为静态分区就可以多级创建,动态分区同样可以,如果想多级创建动态分区,比如年(year),月(month),日(day)分三个动态分区
insert into table emp_dynamic_partition partition(year,month,day)
select empno,ename,job,mgr,hiredate,sal,comm, deptno,month,day from emp;
动态分区常用配置参数:官网截图:
如果是分区字段在下面的查询语句是空,即select 出来的deptno=null,然后Hive根据null这种分区,Hive会创建一个单独的分区,
image.png
4,外部分区表
Hive也可以直接创建外部分区表,之前创建的外部表是直接套了一个壳子在文件上
image.png
现在是文件夹下还有文件夹,以数据分一层为例,多层原理一样.他是文件夹下,文件夹,再到文件,直接把外部表套在最外面文件夹下,是读取不到文件的,因为中间隔着一层分区文件夹
这个是不是似曾相识,最上面讲的内部表分区,直接在表的Hdfs文件夹上面加上一个分区文件夹,然后里面有文件,他也不认,对吧.其实Hive就这样,你在他的表级文件夹下直接放文件可以认识,但是放文件夹肯定就不认识了.
2种方法:alter,msck方法
我的另一篇文章讲述了这个alter和msck对于内部表和外部表修复分区遇到的坑
其实内部表和外部表对于分区修复原理差不多,如果你分区文件夹名称创建不规范,肯定会出问题,详情在我的下面这个博客里面
https://www.jianshu.com/p/4d59d9bbbc02
5.静态分区和动态分区的区别
静态分区
①静态分区是在编译期间指定的指定分区名
②支持load和insert两种插入方式
load方式
会将分区字段的值全部修改为指定的内容
一般是确定该分区内容是一致的时候才会使用
insert方式
必须先将数据放在一个没有设置分区的普通表中
该方式可以在一个分区内存储一个范围的内容
从普通表中选出的字段不能包含分区字段
③适用于分区数少，分区名可以明确的数据
动态分区
①根据分区字段的实际值，动态进行分区
②是在sql执行的时候进行分区
③需要先将动态分区设置打开
④用insert或者CTAS方式
CREATE TABLE T (key int, value string) PARTITIONED BY (ds string, hr int) AS
SELECT key, value, ds, hr+1 hr1 FROM srcpart WHERE ds is not null and hr>10;
⑤通过普通表选出的字段包含分区字段，分区字段放置在最后，多个分区字段按照分区顺序放置
2.静态分区和动态分区的应用场景
静态分区：适合已经确认分区的文件，分区相对较少的，适合增量导入的应用场景
动态分区：适合根据时间线做分区，分区比较多的，适合全量导入的场景
Hive官网参考https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions
