Hive-分区表，二级分区，动态分区，分桶表

太过爱你忘了你带给我的痛 2023-03-04 12:29 29阅读 0赞

# 分区表，二级分区，动态分区，分桶表 #

### 文章目录 ###

*  分区表，二级分区，动态分区，分桶表
 *   *  分区表与分桶表的区别？
     *  分区表
     *   *  1 分区表基本操作
         *  2 分区表二级分区
         *  3 动态分区调整
     *  分桶表

## 分区表与分桶表的区别？ ##

1）  
分区表：使用的是表外字段（表中不存在的字段），需要指定字段类型；  
分桶表：使用的是表内字段（表中存在的字段），已经知道字段类型，不需要指定。  
2）  
分区表：通过关键字`partitioned by(partition_name string)`声明，  
分桶表：通过关键字`clustered by(column_name) into 3 buckets`声明。  
3)  
分区表：划分的是hdfs的存储路径（文件夹），是个伪列，只对应着文件存储路径上的一个层级  
分桶表：划分的是hdfs的数据文件

## 分区表 ##

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，分区目的主要是避免全表扫描，从而提升查询和计算效率。  
按分区类型划分，可以分为静态分区、动态分区。

### 1 分区表基本操作 ###

**1）引入分区表（需要根据日期对日志进行管理, 通过部门信息模拟）**

dept_20200701.log
    dept_20200702.log
    dept_20200703.log

**2）创建分区表语法**

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

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

**3）加载数据到分区表中**

1.  **数据准备**

dept_20200701.log
    10	ACCOUNTING	1700
    20	RESEARCH	1800
    
    dept_20200702.log
    30	SALES	1900
    40	OPERATIONS	1700
    
    dept_20200703.log
    50	TEST	2000
    60	DEV	1900

1.  **加载数据**

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200701.log' into table dept_partition partition(day='20200701');
    
    hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200702.log' into table dept_partition partition(day='20200702');
    
    hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200703.log' into table dept_partition partition(day='20200703');

*  注意：静态分区加载数据时，必须要指定分区

**4）查询分区表中数据**

单分区查询

hive (default)> select * from dept_partition where day='20200701';

多分区联合查询

select * from dept_partition where day='20200701'
    union
    select * from dept_partition where day='20200702'
    union
    select * from dept_partition where day='20200703';
    
    elect * from dept_partition where day='20200701' or day='20200702' or day='20200703' ;

**5）查看分区表有多少分区**

hive> show partitions dept_partition;

**6）增加分区**

创建单个分区

hive (default)> alter table dept_partition add partition(day='20200704');

同时创建多个分区（分区之间不能有逗号）

alter table dept_partition add partition(day='20200705') partition(day='20200706');

**7）删除分区**（分外部表（删除元数据，就是mysql的数据，hdfs的文件夹和数据不会删除）和内部表（删除元数据，同时也删除hdfs的文件夹和数据））

删除单个分区

hive (default)> alter table dept_partition drop partition (day='20200706');

同时删除多个分区（分区之间必须有逗号）

alter table dept_partition drop partition (day='20200704'), partition(day='20200705');

**8）查看分区表结构**

hive> desc formatted dept_partition;
    
    # Partition Information          
    # col_name              data_type               comment             
    month                   string

### 2 分区表二级分区 ###

思考: 如何一天的日志数据量也很大，如何再将数据拆分?

**1）创建二级分区表**

*  注意：如果删除所有的二级分区，那它对应的一级分区会自动删除（因为删除所有二级分区，那么一级分区里面是空的）

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

**2）正常的加载数据**

（1）加载数据到二级分区表中

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

（2）查询分区数据

hive (default)> select * from dept_partition2 where day='20200701' and hour='12';

**3）把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式**

（1）方式一：上传数据后修复

-- 上传数据
    hive (default)> dfs -mkdir -p
     /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=13;
    hive (default)> dfs -put /opt/module/datas/dept_20200701.log  /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=13;
    
    -- 查询数据（查询不到刚上传的数据）
    hive (default)> select * from dept_partition2 where day='20200701' and hour='13';
    
    -- 执行修复命令（扫描表下所有的分区文件夹）
    hive> msck repair table dept_partition2;
    
    -- 再次查询数据
    hive (default)> select * from dept_partition2 where day='20200701' and hour='13';

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

上传数据

-- 上传数据
    hive (default)> dfs -mkdir -p
     /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=14;
    hive (default)> dfs -put /opt/module/hive/datas/dept_20200701.log  /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=14;
    
    -- 执行添加分区
    hive (default)> alter table dept_partition2 add partition(day='20200701',hour='14');
    	
    -- 查询数据
    hive (default)> select * from dept_partition2 where day='20200701' and hour='14';

（3）方式三：创建文件夹后load数据到分区

-- 创建目录
    hive (default)> dfs -mkdir -p
     /user/hive/warehouse/db_hive.db/dept_partition2/day=20200701/hour=15;
    
    -- 上传数据
    hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200701.log' into table
     dept_partition2 partition(day='20200701',hour='15');
    
    -- 查询数据
    hive (default)> select * from dept_partition2 where day='20200701' and hour='15';

### 3 动态分区调整 ###

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

**1）开启动态分区参数设置**

--（1）开启动态分区功能（默认是false）
    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

**2）案例实操**

需求：将dept表中的数据按照地区（loc字段），插入到目标表dept\_partition的相应分区中。

--（1）创建目标分区表
    create table dept_partition_dy(id int, name string) 
    partitioned by (loc int) 
    row format delimited fields terminated by '\t';
    
    --（2）设置动态分区
    set hive.exec.dynamic.partition.mode = nonstrict;
    
    insert into table dept_partition_dy 
    partition(loc) 
    select deptno, dname, loc from dept;
    
    --（3）查看目标分区表的分区情况
    hive (default)> show partitions dept_partition_dy;

## 分桶表 ##

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。（分桶的字段一定要是表里存在的字段）

*  分桶是将数据集分解成更容易管理的若干部分的另一个技术。
 *  分区针对的是数据的存储路径（文件夹）；分桶针对的是数据文件

**1）先创建分桶表，通过直接导入数据文件的方式**

--（1）数据准备
    1001	qq1
    1002	qq2
    1003	qq3
    1004	qq4
    1005	qq5
    1006	qq6
    1007	qq7
    1008	qq8
    1009	qq9
    
    --（2）创建分桶表
    create table stu_buck(id int, name string)
    clustered by(id) 
    into 4 buckets
    row format delimited fields terminated by '\t';
    
    --（3）查看表结构
    desc formatted stu_buck;
    Num Buckets:   4  
    
    --（4）导入数据到分桶表中（hive新版本load数据可以直接跑mr(也会有点问题)，老版的hive需要将数据传到一张表里，再通过查询的方式导入到分桶表里面。）
    hive (default)> load data local inpath '/opt/module/hive/datas/student.txt' into table stu_buck;
     
    --（6）查询分桶的数据
    hive(default)> select * from stu_buck;

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