Hive表---分区表、分桶表以及两者区别

本文介绍了Hive中的分区表和分桶表的概念、创建方法、效率优化以及注意事项。分区表通过目录结构加速查询,而分桶表则通过文件组织提高特定操作效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一. 分区表:每个分区本质是一个目录

分区表特点/好处: 需要产生分区目录, 查询的时候使用分区字段筛选数据,避免全表扫描从而提升查询效率

效率上注意: 如果分区表,在查询数据的时候没有使用分区字段去筛选数据,效率不变

分区字段名注意: 分区字段名不能和原有字段名重复,因为分区字段名要作为字段拼接到表后

在这里插入图片描述

1. 一级分区

创建分区表:create [external] table [if not exists] 表名(字段名 字段类型 , 字段名 字段类型 , … )partitioned by (分区字段名 分区字段类型)… ;

自动生成分区目录并插入数据: load data [local] inpath ‘文件路径’ into table 分区表名 partition (分区字段名=‘值’);

注意: 如果加local后面文件路径应该是linux本地路径,如果没有加那么就是hdfs文件路径
示例创建一级分区表并加载数据

-- 创建库使用库
create database hive3;
use hive3;
-- 演示分区表
-- 1.一级分区表
-- 建表
create table one_part_order(
    oid string,
    name string,
    price double,
    num int
)partitioned by (year string)
    row format delimited
fields terminated by ' ';
-- 加载数据
-- 先在hdfs的source目录下准备好订单相关数据文件
-- 使用load加载数据到分区表中
load data inpath '/source/order202251.txt' into table one_part_order partition (year=2022);
load data inpath '/source/order2023415.txt' into table one_part_order partition (year='2023');
load data inpath '/source/order202351.txt' into table one_part_order partition (year='2023');
load data inpath '/source/order202352.txt' into table one_part_order partition (year='2023');
-- 验证数据
select * from one_part_order limit 20;

/*分区表特点
去hdfs验证分区表的本质就是分目录存储各个小文件
通过查询发现分区字段最终效果作为一个字段拼接到表最后
*/
-- 分区表的好处:避免全表扫描,提升查询效率
select * from one_part_order where year='2022';
-- 注意: 如果查询的时候条件不是分区字段,效率不会改变
select * from one_part_order where price=20;

2.多级分区

创建分区表: create [external] table [if not exists] 表名(字段名 字段类型 , 字段名 字段类型 , … )partitioned by (一级分区字段名 分区字段类型, 二级分区字段名 分区字段类型 , …) ;

自动生成分区目录并插入数据: load data [local] inpath ‘文件路径’ into table 分区表名 partition (一级分区字段名=‘值’,二级分区字段名=‘值’ , …);

注意: 如果加local后面文件路径应该是linux本地路径,如果没有加那么就是hdfs文件路径

示例创建多级分区表并加载数据

-- 2.多级分区表
-- 创建表
create table multi_part_order(
    oid string,
    name string,
    price float,
    num int
)partitioned by (year string,month string,day string)
    row format delimited
fields terminated by ' ';
-- 加载数据
-- 思考数据文件在哪里?如果想从hdfs加载,怎么操作?上传到hdfs指定位置
load data inpath '/source/order202251.txt' into table multi_part_order partition (year=2022,month=05,day=01);
load data inpath '/source/order202351.txt' into table multi_part_order partition (year=2023,month=05,day=01);
load data inpath '/source/order202352.txt' into table multi_part_order partition (year=2023,month=05,day=02);
load data inpath '/source/order2023415.txt' into table multi_part_order partition (year=2023,month=04,day=15);
-- 验证数据
select * from multi_part_order;

-- 分区表的好处:避免全表扫描,提升查询效率
-- 需求: 统计2023年商品总销售额
select sum(price*num) from multi_part_order where year='2023'; -- 提升效率
-- 需求: 统计2023年5月份商品总销售额
select sum(price*num) from multi_part_order where year='2023'and month='5'; -- 提升效率
-- 需求: 统计2023年5月1日的商品总销售额
select sum(price*num) from multi_part_order where year='2023'and month='5' and day='1'; -- 提升效率

3.分区操作

添加分区: alter table 分区表名 add partition (分区字段名=‘值’ , …);

删除分区: alter table 分区表名 drop partition (分区字段名=‘值’ , …);

修改分区名: alter table 分区表名 partition (分区字段名=‘旧值’ , …) rename to partition (分区字段名=‘新值’ , …);

查看所有分区: show partitons 分区表名;

同步/修复分区: msck repair table 分区表名;

-- 分区操作
-- 注意: 先确定有一级分区和多级分区表,如果没有先创建再做分区操作
select * from one_part_order limit 20;
select * from multi_part_order limit 20;

-- 添加分区(本质在hdfs上创建分区目录)
alter table one_part_order add partition (year=2024);
alter table multi_part_order add partition (year=2024,month=5,day=1);

-- 修改分区(本质在hdfs上修改分区目录名)
alter table one_part_order partition (year=2024) rename to partition (year=2030);
alter table multi_part_order  partition (year=2024,month=5,day=1) rename to partition (year=2030,month=6,day=10);

-- 查看所有分区
show partitions one_part_order;
show partitions multi_part_order;

-- 删除分区
alter table multi_part_order drop partition (year=2030,month=6,day=10);
alter table multi_part_order drop partition (year=2023,month=5,day=2);
alter table multi_part_order drop partition (year=2023,month=5);
alter table multi_part_order drop partition (year=2023,month=4);
alter table multi_part_order drop partition (year=2022);

-- 如果在hdfs上创建符合分区目录格式的文件夹,可以使用msck repair修复
-- 举例:手动创建一个year=2033目录
msck repair table one_part_order;
msck repair table multi_part_order;
-- 修复后再次查看所有分区
show partitions one_part_order;
show partitions multi_part_order;

hadoop_hive文档

hive文档: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
hdfs文档: https://hadoop.apache.org/docs/stable/hadoop-project-dist/hadoop-hdfs/hdfs-default.xml
yarn文档: https://hadoop.apache.org/docs/stable/hadoop-yarn/hadoop-yarn-common/yarn-default.xml
mr文档: https://hadoop.apache.org/docs/stable/hadoop-mapreduce-client/hadoop-mapreduce-client-core/mapred-default.xml

二. 分桶表:每个分桶本质是一个文件

分桶表特点/好处: 需要产生分桶文件, 查询的时候特定操作上提升效率(过滤,join,分组 以及 抽样)

效率上注意: 如果分桶表,在查询数据的时候没有使用分桶字段去筛选数据,效率不变

分桶字段名注意: 分桶字段名必须是原有字段名, 因为分桶需要根据对应字段值取余数把余数相同的数据放到同一个分桶

在这里插入图片描述

1. 重要参数

-- 默认开启,hive2.x版本已经被移除
set hive.enforce.bucketing; -- 查看未定义因为已经被移除
set hive.enforce.bucketing=true; -- 修改

-- 查看reduce数量
-- 参数优先级: set方式 > hive文档 > hadoop文档
set mapreduce.job.reduces; -- 查看默认-1,代表自动根据桶数量匹配reduce数量
set mapreduce.job.reduces=3; -- 设置参数

2. 基础分桶表

创建基础分桶表:
create [external] table [if not exists] 表名(
字段名 字段类型
)
clustered by (分桶字段名)
into 桶数量 buckets ;

示例创建基础分桶表并加载数据

-- 1.创建基础分桶表,要求分3个桶
create table course_base (
    cid int,
    cname string,
    sname string
)
clustered by(cid) into 3 buckets
row format delimited fields terminated by '\t';

-- 2.load方式加载数据
-- 前提: 已经上传course.txt文件到hdfs的/source目录下
load data inpath '/source/course.txt' into table course_base;

-- 3.查询数据,观察结果
select * from course_base;

3. 分桶表排序

创建基础分桶表,然后桶内排序:
create [external] table [if not exists] 表名(
字段名 字段类型
)
clustered by (分桶字段名)
sorted by(排序字段名 asc|desc) # 注意:asc升序(默认) desc降序
into 桶数量 buckets ;

示例创建分桶排序表并加载数据

-- 1.创建基础分桶表,要求分3个桶,桶内根据cid降序
create table course_sort (
    cid int,
    cname string,
    sname string
)
clustered by(cid) sorted by (cid desc) into 3 buckets
row format delimited fields terminated by '\t';

-- 2.加载数据
-- 还是使用/source/course.txt数据文件
load data inpath '/source/course.txt' into table course_sort;

-- 3.查询数据,观察结果
select * from course_sort;

分桶原理

如果是数值类型分桶字段: 直接使用数值对桶数量取模
如果是字符串类型分桶字段: 底层会使用hash算法计算出一个数字然后再对桶数量取模

Hash: Hash是一种数据加密算法,其原理我们不去详细讨论,我们只需要知道其主要特征:同样的值被Hash加密后的结果是一致的
举例: 字符串“binzi”被Hash后的结果是93742710(仅作为示意),那么无论计算多少次,字符串“binzi”的结果都会是93742710。
计算余数: hash(‘binzi’)%3==0
注意: 同样的数据得到的结果一致,如’binzi’ hash取模结果是0,无论计算多少次,它的取模结果都是0

[重点]分区表和分桶表的区别

分区表
创建表的时候使用关键字: partition by (分区字段名 分区字段类型)

分区字段名注意事项: 是一个新的字段,需要指定类型,且不能和其他字段重名

分区表好处: 使用分区字段作为条件的时候,底层直接找到对应的分区目录,能够避免全表扫描,提升查询效率

分区表最直接的效果: 在hfds表目录下,分成多个分区目录(year=xxxx,month=xx,day=xx)

不建议直接上传文件在hdfs表根路径下: 分区不能识别对应文件中数据,因为分区表会找分区目录下的数据文件

使用load方式加载hdfs中文件: 本质是移动文件到对应分区目录下

分桶表
创建表的时候使用关键字: clustered by (分桶字段名) into 桶数量 buckets

分桶字段名注意事项: 是指定一个已存在的字段,不需要指定类型

分桶表好处: 使用分桶字段做抽样等特定操作的时候,也能提升性能效率

分桶表最直接的效果: 在hdfs表目录或者分区目录下,分成多个分桶文件(000000_0,000001_0,000002_0…)

不建议直接上传文件在hdfs表根路径下: 分桶表可以识别对应文件中数据,但是并没有分桶效果,也是不建议的

使用load方式加载hdfs中文件: 本质是复制数据到各个分桶文件中

### Hive 安装配置 Hive 的安装配置通常涉及以下几个方面:环境准备、下载与解压、配置文件修改以及启动服务。以下是简要说明: - **环境准备**:确保已安装 Java Hadoop,并设置好 JAVA_HOME HADOOP_HOME 环境变量[^1]。 - **下载与解压**:从 Apache 官方网站下载最新版本的 Hive 并解压缩至目标路径。 - **配置文件修改**: - 修改 `hive-site.xml` 文件,配置元数据存储位置(通常是 MySQL 或 Derby),并指定 HDFS 路径作为默认仓库目录[^3]。 - 如果使用 MySQL,则需额外下载 JDBC 驱动程序并将驱动包放置到 `$HIVE_HOME/lib` 下。 - **启动服务**:通过命令 `schematool -initSchema -dbType mysql` 初始化元数据库;之后可以通过 `hive` 命令进入 CLI。 --- ### 内外部的操作方法 #### 创建内部 ```sql CREATE TABLE internal_table ( id INT, name STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; ``` 内部特点是其数据由 Hive 自己管理,删除时会自动清理对应的数据文件[^4]。 #### 创建外部 ```sql CREATE EXTERNAL TABLE external_table ( id INT, name STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/path/to/hdfs/data'; ``` 外部不会在删除的同时移除底层数据文件,仅解除关联关系。 --- ### 分区表的操作方法 分区表允许按某些字段划数据子集,从而优化查询效率。创建方式如下所示: ```sql CREATE TABLE partitioned_table ( id INT, value STRING ) PARTITIONED BY (year INT, month INT); ``` 向分区表加载数据时需要显式指定区键值: ```sql LOAD DATA INPATH '/path/to/file' INTO TABLE partitioned_table PARTITION(year=2023, month=9); ``` 对于已有数据但未区的情况,可执行修复命令同步元数据与实际存储结构: ```sql MSCK REPAIR TABLE partitioned_table; ``` --- ### 的操作方法 通过对某一列进行哈希运算实现均匀布的小规模数据切片,适合用于采样析或连接操作加速。示例如下: ```sql CREATE TABLE bucketed_table ( id INT, name STRING ) CLUSTERED BY(id) INTO 4 BUCKETS; -- 将数据划为 4 个 ``` 为了使生效,在插入前应启用相应参数: ```sql SET hive.enforce.bucketing = true; INSERT OVERWRITE TABLE bucketed_table SELECT * FROM source_table DISTRIBUTE BY id; ``` 如果希望进一步提升性能,还可以结合排序功能一起定义规则: ```sql CREATE TABLE sorted_bucketed_table ( id INT, score DOUBLE ) CLUSTERED BY(id) SORTED BY(score DESC) INTO 5 BUCKETS; ``` --- ### 分区表联合使用 两者可以共同作用以达到最佳效果——先按业务逻辑割成不同区域再细化颗粒度。下面是一个综合案例: ```sql CREATE TABLE combined_partition_bucket_table ( user_id BIGINT, event_time TIMESTAMP, action STRING ) PARTITIONED BY(date STRING) CLUSTERED BY(user_id) INTO 8 BUCKETS STORED AS ORC TBLPROPERTIES('transactional'='true'); ``` 上述语句明该支持事务特性,采用 ORC 存储格式,并针对日期维度进行了物理隔离同时依据用户 ID 实现负载均衡[^5]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值