Hive企业级优化之分区表

 

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

一、分区表的基本操作

1.创建分区表

2.插入数据进入分区表

3.查看分区表的数据

4.增加分区

5.删除分区

6.查询分区

7.查看分区表结构

二、分区表注意事项

1.建立二级分区表

2.插入数据

3.查询分区数据

三、修复分区

1、修复一 手动添加

2、修复二 修复命令

四、动态分区调整

1、概述

2、开启动态分区参数设置

3、实例操作

一、分区表的基本操作

必须新建分区表，已有表不能转为分区表

1.创建分区表

create table dept_partition(

deptno int, dname string, loc string

)

partitioned by (month string)

row format delimited fields terminated by '\t';

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

 

2.插入数据进入分区表

1.

load data local inpath '/opt/module/datas/dept.txt' into table dept_partition partition(month="202110");

2.

insert into dept_partition partion(month="202109") select * from dept where dname!='XXXX';

select * from dept_partition;

 

3.查看分区表的数据

select * from dept_partition where deptno=10;

查询的是全表

 

select * from dept_partition where month="202110";

查询的是分区表

 

4.增加分区

创建单个分区

alter table dept_partition add partition(month='201706') ;

同时创建多个分区

alter table dept_partition add partition(month='201705') partition(month='201704');

空格

5.删除分区

删除单个分区

alter table dept_partition drop partition (month='201704');

同时删除多个分区

alter table dept_partition drop partition (month='201705'), partition (month='201706');

逗号

6.查询分区

show partitions dept_partition;

 

7.查看分区表结构

desc formatted dept_partition;

二、分区表注意事项

1.建立二级分区表

create table dept_partition2(

deptno int, dname string, loc string

 )

 partitioned by (month string, day string)

 row format delimited fields terminated by '\t';

 

2.插入数据

load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month="20219090",day='23');

 

3.查询分区数据

select * from dept_partition2 where month='20179090' and day='23';

 

三、修复分区

在hdfs上直接建立一张分区表

 

查询结果是没有08分区的数据的

 

因为在hive的元数据里并没有分区

查询分区还是只有之前那两个，手动添加的文件夹在元数据里并没有体现

 

1、修复一 手动添加

alter table dept_partition add partition(month="202108");

 

2、修复二 修复命令

同上上传一个07

 

修复命令

 msck repair table dept_partition;

 

直接修复，自动添加进去

四、动态分区调整

1、概述

关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。

2、开启动态分区参数设置

（1）开启动态分区功能（默认true，开启）

hive.exec.dynamic.partition=true

（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）

hive.exec.dynamic.partition.mode=nonstrict;

（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000

hive.exec.max.dynamic.partitions=1000

（4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

hive.exec.max.dynamic.partitions.pernode=100

（5）整个MR Job中，最大可以创建多少个HDFS文件。默认100000

hive.exec.max.created.files=100000

（6）当有空分区生成时，是否抛出异常。一般不需要设置。默认false

hive.error.on.empty.partition=false

3、实例操作

开启动态分区调整

可以把非分区表的数据插到分区表里

原本的不分区表

 

建立表格

create table dept_partition3

(dpetno int, dname string)

partitioned by (loc int)

row format delimited fields terminated by '\t';

设置动态分区

非严格模式

 

自动按照loc匹配插到表中

insert into table dept_partition3 partition(loc)

select deptno, dname, loc from dept;

要分区的一定要写在select语句的最后一个字段