数据仓库Hive的分区表与分桶表

 一、分区表

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

        所谓的分区表其实和表中的字段没什么区别，包括其使用，唯一的区别就是在于向hdfs写表数据的时候，会以分区值先在表的hdfs目录下创建一个分区目录，再添加数据。

我们举一个具体的案例来讲解一下：

现在有学生不同年份的成绩汇总，由于数据量大，我们要把学生成绩按不同年份进行分区：

1、引入分区表

/user/hive/warehouse/school.db/2018/2018.txt

/user/hive/warehouse/school.db2019/2019.txt

/user/hive/warehouse/school.db/2020/2020.txt

2、创建分区表语法（DDL操作）创建一级分区

语法：create table tableName(字段列表)
        partitioned by(分区字段 字段类型)
        row format delimited fields terminated by ","

注意事项：分区字段不能是表字段，因为分区字段在一定程度上可以为表字段

hive (school)> create table sspartition

               (deptno int, dname string, loc string)

               partitioned by (year string)

               row format delimited fields terminated by ',';

3、加载数据到表中（DML操作）

        如果要向分区表中添加数据，在insert语句中必须指定partition（一个表既然指定为分区表，说明它数据量大，我们一般不使用insert语句进行添加数据）一般我们都使用load的方法区加载分区表数据

语法：insert into table partition(分区字段=值) values()

insert into sspartition partition(year="2020") values("zs",66);

接着我们就会在/user/hive/warehouse/school.db/sspartition下看到year=2020的目录

点进去就可以看到新建的分区000000_0

一旦一个表指定为分区表之后，增加数据必须去指定数据所在分区

我们来看一下load方法添加数据：

语法：load data [local] inpath  ''  into table tableName partition(分区字段=值)

hive(school)>load data local inpath '/opt/data/ss-2018.txt' into

                        table sspartition partition(year='2018');

4、查询分区表中的数据（DML操作）

查询表中所有分区的数据，分区字段会和表字段一样去显示：

select * from tableName

查询指定分区的数据：
select * from tableName where 分区字段=分区值

5、修改表的分区（DDL操作）

        表创建成功表分区的规则已经定义好，修改表分区就是给表增加、删除一个分区。

增加分区：

创建单个分区：

hive (default)> alter table sspartition add partition(year='2021') ;

同时创建多个分区

hive (default)>  alter table sspartition add

                partition(year='2022') partition(year='2023');

分区字段无法动态增加，只有创建表的时候去指定，但是我们可以根据分区字段动态增加删除分区

 删除分区：

删除单个分区

hive (default)> alter table sspartition drop partition (year='2021');

同时删除多个分区

hive (default)> alter table dept_partition drop

                partition (year='2022'), partition (year='2023');

6、查看表有多少分区

语法：show partitions tableName;

7、查看分区表结构

语法：desc formatted tableName;

8、创建二级分区（DDL操作）

create table ss1(id int,name string)

partitioned by(year string,month string)

row format delimited fields terminated by ",";

此时查看分区字段会有两个字段：

 二级分区表如何添加数据呢？

insert into ss1 partition(year="2021",month="08") values(1,"ls");

 那么在/user/hive/warehouse/school.db/ss1/下看到year=2021/month=08的二级分区。

二级分区中的数据如何查询？

select * from ss1 where year="2021" and month="08";

二、分桶表

分桶表和分区表的概念很类似。

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

        分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。

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

1、创建分桶表

create table stu(id int, name string)

clustered by(id)

into 4 buckets

row format delimited fields terminated by ',';

以上语句是按stu表中的id字段将表拆成四个桶（文件）。分桶表的字段必须是表中的字段

分桶表指定四个分桶，数据怎么知道去哪个小文件？

分桶字段的hasdcode值和分桶个数取余得到小文件的位置

2、查看表结构

hive (default)> desc formatted stu;

3、导入数据到分桶表中

hive (default)> load data local inpath '/opt/data/student.txt' into table stu;

【注意】如果使用以上命令给分桶表增加数据，需要提前设置以下两个参数：

       hive(default)> set sethive.strict.checks.bucketing=false;

       hive(default)> set  hive.mapred.mode ="nonstrict";

 

总结：

分桶表和分区表不一样的是，分区表是将数据按照分区不同存储到不同的目录下
分桶表存储的数据文件按照指定的字段，进行文件切割，形成不同的小文件进行存储

        分桶表如果想实现将存储的大文件切割成小文件，必须使用insert .....select这种方式向分桶表增加数据，这样才可以对文件进行分割小文件