HiveSql中的分区分桶详解

分区(partiton)

静态分区

动态分区

静态分区

     如果有一张表,需求是要其中一类的数据例如(星座),但是需要全盘扫描,如何精准的获取到我们要的数据?

    其实只需要采用分区表的思路来管理就可以解决,只需把各个星座放入到不同的文件夹当中即可.

-- 1. 创建分区表, 指定分区字段.
create table consteall(
    id            int comment 'ID',
    name          string comment '名字',
    constellation string comment '星座'
    
) comment '星座表'
partitioned by (role string comment '充当分区字段')  -- 核心细节: 分区字段必须是表中没有的字段.
row format delimited fields terminated by '\t';

上述创建表之后会有4个字段:3个原本字段+1个分区字段

当然也可以往分区表当中添加数据l:

添加方法如下: 注意:role = '值' 这个就是静态分区

load data local inpath '/export/hivedata/archer.txt' into table consteall partition(role='sheshou');   

这样再次查询星座信息就可以节约内存查询到信息提升效率

select * from t_all_hero_part where role='sheshou';         -- 精准扫描某个分区(目录), 避免全表扫描.

动态分区:

    键表方式跟静态分区一样,很多人在进行load加载数据的时候会报错,这个是由于动态分区只支持 insert into | overwrite语法 并且不能单独添加动态分区需要手动关闭严格模式

-- 关闭严格模式.
set hive.exec.dynamic.partition.mode=nonstrict;     -- nonstrict 非严格模式, strict: 严格模式(默认)

-- 动态分区的方式, 添加数据.
insert into table consteall partition(role)
select *, role_main from t_all_hero;

总结:

    之前我们已经实现了静态分区, 即: 手动指定分区字段 和 分区字段值, 如果分区过多, 每次写分区字段值比较繁琐, 且有可能写错.

如何解决这个问题呢?
非常简单, 可以通过 动态分区的方式实现, 即: 手动指定分区字段即可, 该字段值一样的数据, 会被自动放到一起.

细节:
1. 在进行动态分区的时候, 建议: 手动关闭严格模式.
2. 分区的严格模式要求: 在进行动态分区的时候, 至少要有1个静态分区. 如果都是动态分区, 则: 报错.
静态分区: partition(role='sheshou') partition(分区字段='值')
动态分区: partition(role) partition(分区字段)
3. 动态分区不支持load方式加载数据, 采用 insert into | overwrite方式导入数据.
4. set 参数名=值; 是在 设置参数值.
set 参数名; 是在 是在获取(查看)该参数的值.

多级分区

    实际开发中, 如果数据量比较大的情况下,可以考虑采用 多级分区的思路来解决, 多级分区一般用 时间来分区, 可以是: 年, 月, 日...

show partitions 表名; 查看分区信息

添加1个分区

alter table 表名 add partition(year=2023, month=1);

添加多个分区

alter table 表名 add partition(year=2023, month=4) partition(year=2023, month=5) partition(year=2023, month=11);

修改分区

alter table 表名 partition(year=2024, month=10) rename to partition(year=2024, month=08);

删除分区

alter table products drop partition(year=2023, month=4); -- 删除2023年4月 这个分区

分桶(cluster)

    分桶表的作用:

            1. 方便进行数据采样.
             2. 减少join的次数, 提高查询效率.

    1. 分桶字段必须是表中已有的字段.
     2. 分桶数量 = HDFS文件系统中, 最终的文件数量.
     3. 分桶规则用的是: 哈希取模分桶法, 简单来说, 就是根据分桶字段计算它的哈希值, 然后和桶的个数取余, 余数为几, 就进哪个桶.
         哈希值: 程序根据值的内容, 内存地址值等信息, 计算出来的1个数字.
             例如:
                 select hash('乔峰');          哈希值为: -870432061
                 select hash('乔峰') % 3;      取余结果为: -1           3是假设一共有3个桶.
                 select abs(-10);             计算绝对值的: 10
                 select abs(hash('乔峰')) % 3; 取余结果为: 1
     4. set mapreduce.job.reduces=n;     可以设置ReduceTask任务的数量, 这个设置只在 分桶查询中会用到.
        分桶建表的时候, 不用该参数.
     5. 分桶表的数据不建议load data方式 或者 手动上传, 而是: insert into | overwrite的方式添加.

基本用法

-- 1. 创建分桶表-学生表, 按照学生id进行分桶, 分成 3 个桶, 桶内部按照 age 降序排列.

create table student_buckets_sort(
    sid int,
    name string,
    gender string,
    age int,
    major string
) comment '学生信息表'
-- clustered by (sid) sorted by (sid) into 3 buckets      -- 按照学生id进行分桶, 分成 3 个桶, 桶内部按照 sid 升序排列.
clustered by (sid) sorted by (age desc) into 3 buckets    -- 按照学生id进行分桶, 分成 3 个桶, 桶内部按照 age 降序排列.
row format delimited fields terminated by ',';

-- 2.往上述的分桶排序表中, 添加数据.
insert into student_buckets_sort select * from student;

-- 3. 查询结果.
select * from student_buckets_sort;