Hive---分区表和分桶表

分区表和分桶表

---

分区表

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

语法

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

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

加载数据

load data local inpath '/opt/text.txt' into table dept_partition partition(field=xxxx);

注意：分区表加载数据时，必须指定分区

增加分区

增加单个

alter table dept_partition add partition(field=xxxx);

增加多个(无逗号)

alter table dept_partition add partition(field=xxxx) partition(field=yyyy);

删除分区

删除单个

alter table dept_partition drop partition (field=xxxx);

删除多个（有逗号）

alter table dept_partition drop partition (field=xxxx), partition(field=yyyy);

查看分区表有多少分区

show partitions dept_partition;

查看分区表结构

desc formatted dept_partition;

动态分区

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

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

set hive.exec.dynamic.partition=true;

设置为非严格模式

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

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

在所有执行 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 '\t';

查看表结构

desc formatted stu_buck;

导入数据

load data inpath '/student.txt' into table stu_buck;

分桶规则

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

（1）reduce 的个数设置为-1，让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个数设置为大于等于分桶表的桶数
（2）从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
（3）不要使用本地模式

区别

分区表是指按照数据表的某列或某些列分为多个区，区从形式上可以理解为文件夹。
分桶是相对分区进行更细粒度的划分。分桶将整个数据内容按照某列属性值的hash值进行区分，如果按照name 属性分为3个桶，就是对name属性值的hash值对3取模，按照取模结果对数据分桶。如取模结果为0的数据记录存放到一个文件，取模为1的数据存放到一个文件，取模为2的数据存放到一个文件。

从表现形式形式上

分区表是一个目录，分桶表是文件。

从创建语句上

分区表使用partitioned by 子句指定，以指定字段为伪列，需要指定字段类型。

分桶表由clustered by 子句指定，指定字段为真实字段，需要指定桶的个数。

从数量上

分区表的分区个数可以增长，分桶表一旦指定，不能增长。

作用上

分区避免全表扫描，根据分区列查询指定目录提高查询速度

分桶保存分桶查询结果的分桶结构（数据已经按照分桶字段进行了hash散列)

分桶表数据进行抽样和join时可以提高MR程序效率