分区表

分区表实际上就是对应一个HDFS文件系统上的独立文件夹，该文件夹下是该分区所有的数据文件。

Hive中的分区就是分目录。把一个大的数据集根据业务需要分割成小的数据集。在查询时通过where表达式选择锁需要的指定的分区，这样的查询效率会提高很多。

创建分区表

创建分区表示例:

create table dept_partition(
    deptNo int,
    dname string,
    loc string
)
partitioned by (year string)  -- 配置分区字段
row format delimited fields terminated by ',';

注意：

分区字段不能是表中已经存在的数据，可以将分区字段看做表的伪列。
建表语句中的分区关键字是 partitioned，不是partition

向分区表内加载数据

准备数据：
dept_2020.log：

10,ACCOUNTING,1700
20,RESEARCH,1800

dept_2021.log：

30,SALES,1900
40,OPERATIONS,1700

dept_2022.log：

50,TEXT,2000
60,DEV,1900

加载数据：

-- 加载数据时，需要使用 partition 指定该数据所属的分区
load data local inpath '/home/tengyer/data/dept_2020.log' into table dept_partition partition(year='2020');
load data local inpath '/home/tengyer/data/dept_2021.log' into table dept_partition partition(year='2021');
load data local inpath '/home/tengyer/data/dept_2022.log' into table dept_partition partition(year='2022');

注意，此处的关键字是partition ，和创建表时的partitioned不一样

加载完数据后，在 hdfs 上查看该表时，会发现该表目录/user/hive/warehouse/dept_partition 下面创建了三个文件夹：year=2020、year=2021、year=2022，分别存储该分区内的数据文件。

如果查询时where条件里指定了分区条件，那么就可以避免全表扫描，只在扫描该分区内的数据文件即可，可以提高查询速度。

分区表查询

分区表的查询：

-- 创建表时，创建了三个字段：deptNo、dname、loc，还有一个分区字段 year
-- 使用select查询时，year也会被当做一个字段查询出来，
select deptno ,dname ,loc ,year from dept_partition 
-- 分区字段也可以像普通字段一样进行where
select * from dept_partition where year='2020';

增加分区

增加单个分区：

alter table dept_partition add partition(year='2019');

:::info 注意：这里使用的是partition，和创建表时的partitioned不一样 :::

增加多个分区：

alter table dept_partition add partition(year='2023') partition(year='2024');

删除分区

查看表有多少分区：

show partitions dept_partition;

删除单个分区：

alter table dept_partition drop partition(year='2019');

删除多个分区：

alter table dept_partition drop partition(year='2023'),partition(year='2024');

注意：添加多个分区时，每个分区中间是空格分隔。删除多个分区时，每个分区之间是逗号分隔。 :::info 如何分区里面有数据，删除分区后，分区内的数据也会删除。 :::

二级分区

当分区内数据也很大时，需要创建二级分区表：

create table dept_partition2(
    deptNo int,
    dname string,
    loc string
)
partitioned by (year string, month string)   -- 按年、月进行二级分区
row format delimited fields terminated by ',';

加载数据到二级分区表：

load data local inpath '/home/tengyer/data/dept_2020.log' into table dept_partition2 partition(year='2020', month='01');

此时在hdfs产生的目录为：/user/hive/warehouse/dept_partition2/year=2020/month=01

让分区表和数据产生关联

如果使用hadoop fs -mkdir手动在分区表中创建一个分区文件夹并上传了数据文件，此时使用select语句是查询不到的，因为该分区文件夹在元数据库中并不存在，无法扫描该文件夹。

hadoop fs -mkdir /user/hive/warehouse/dept_partition/year=2023
hadoop fs -put dept_2023.log /user/hive/warehouse/dept_partition/year=2023

此时使用select查询全表时，查询不到year=2023的数据。

如果要让分区表和数据产生关联，可以通过以下几种方式：

方式1：创建文件夹，并上传数据后，执行修复命令

msck repair table dept_partition;

方式2：创建文件夹，并上传数据后，手工创建分区

alter table dept_partition add partition(year='2024');

方式3：创建文件夹后，将数据文件load到分区

-- 使用load命令，即使不创建文件夹，load命令也会自动创建
load data local inpath '/home/tengyer/data/dept_2025.log' into table dept_partition partition(year='2025');

默认分区

当load数据到分区表时，没有指定分区，则会导入到一个Hive生成的默认分区中。

load data local inpath '/home/tengyer/data/dept_2026.log' into table dept_partition;

:::info 此时数据会被导入到一个Hive创建的默认分区中，路径为：/user/hive/warehouse/dept_partition/year=__HIVE_DEFAULT_PARTITION__ :::

动态分区

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

开启动态分区参数设置（默认值就是true）：

set hive.exec.dynamic.partition=true;

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

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

示例：

创建表：

create table dept_dynamic_partition (
    dname string,
    loc string
)
partitioned by (deptNo int)   -- 分区字段为deptNo
row format delimited fields terminated by ',';

根据插入数据的deptNo自动找到对应分区：

insert into dept_dynamic_partition partition(deptNo)   -- 指定分区字段为deptNo，但是静态指定所属的具体分区
select dname,loc,deptNo from dept -- 将分区字段deptNo放到select的最后面

如果不设置非严格模式，那么这句sql会报错。设置了非严格模式后，就可以正常执行了。

在Hive3中，动态分区还可以进行简写：

insert into dept_dynamic_partition -- 不需要再指定分区字段。这种写法在严格模式下也可以正常执行
select dname,loc,deptNo from dept -- 将分区字段deptNo放到select的最后面

其他参数：

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

set hive.exec.max.dynamic.partitions=1000;

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

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

整个MR Job中，最大可以创建多少个hdfs文件（默认值100000）：

set hive.exec.max.created.files=100000;

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

set hive.error.on.empty.partition=false;

分桶表

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

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分区针对的是数据的存储路径，分桶针对的是数据文件。

分桶表在数据集极大的情况下才会用到，对极大的数据集进行抽样，不常用。

示例：

创建分桶表：

create table stu_buck(
    id int,
    name string
)
clustered by (id)  -- 分桶表的分桶字段必须是表中的已有字段
into 4 buckets    -- 指定桶的数量
row format delimited fields terminated by ',';

准备数据：

1001,ss1
1002,ss2
1003,ss3
1004,ss4
1005,ss5
1006,ss6
1007,ss7
1008,ss8
1009,ss9
1010,ss10
1011,ss11
1012,ss12
1013,ss13
1014,ss14
1015,ss15
1016,ss16

将数据导入分桶表：

load data local inpath '/home/tengyer/data/stu.txt' into table stu_buck;

因为我们设置了4个桶，所以导入数据后，会在/user/hive/warehouse/stu_buck下生成4个数据文件来存储这些数据。

分桶的规则：将分桶字段进行hash，然后对桶的个数取余，决定这条数据存放在哪个桶文件中。

因为int的hash值就是本身，所以000000_0文件中存储的是：1001、1005、1009、1013的数据。其他几个文件类推。

分桶表操作需要注意的事项：

reducer的个数设置为-1，让Job自行决定需要用多少个reduce。或者将reducer的个数设置为大于等于分桶表的桶数
导入数据时最好从hdfs中load数据到分桶表中，避免本地数据文件只存在在一个Datanode而其他DataNode读取不到的问题
不要使用本地模式

insert方式将数据导入到分桶表：

insert into table stu_buck select * from student;

抽样查询

对于非常大的数据集，有时候用户需要使用的是一个具有代表性的查询结果，而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

语法：TABLESAMPLE(BUCKET x OUT OF y)

示例：

select * from stu_buck tablesample(bucket 1 out of 4 on id);

注意：x的值必须小于等于y的值，否则会报错。

其中：

y 表示样本总共要分的份数
x表示从哪一份开始向后找数据

大数据Hadoop

07-分区和分桶

分区表