hive分桶表实践

一、建表：分区分桶表，对日期分区，再对id分4个桶

二、设置强制分桶

三、执行插入语句，插入1到8这几个id

四、效果

五、表抽样

-- 语法：

例如下面从对表从桶1开始查，每次间隔1个桶，得到桶1和桶3的全部数据：

六、作用

1、抽样查询

2、map-side join，两个对相同字段做了同样分桶规则的表关联，可以实现在map端join，提高效率。

3、控制文件数量

目前，在利用hive建设数据仓库的过程中，总会遇见分区分桶的，跟传统的DBMS系统一样，通过表分区能够在特定的区域检索数据，减少扫描成本，在一定程度上提高查询效率。比如我们要收集某个大型网站的日志数据，一个网站每天的日志数据存在同一张表上，由于每天会生成大量的日志，导致数据表的内容巨大，在查询时进行全表扫描耗费的资源非常多。那其实这个情况下，我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找。在物理上分区表会将数据按照分区键的列值存储在表目录的子目录中，目录名=“分区键=键值”。其中需要注意的是分区键的值不一定要基于表的某一列（字段），它可以指定任意值，只要查询的时候指定相应的分区键来查询即可。

分桶与分区有所不同，它指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀地分发到各个桶文件中。因为分桶 *** 作需要根据某一列具体数据来进行哈希取模 *** 作，故指定的分桶列必须基于表中的某一列（字段）。因为分桶改变了数据的存储方式，它会把哈希取模相同或者在某一区间的数据行放在同一个桶文件中。如此一来便可提高查询效率，比如我们要对两张在同一列上进行了分桶 *** 作的表进行JOIN *** 作的时候，只需要对保存相同列值的桶进行JOIN *** 作即可。同时分桶也可以提高采样率。

分区是为了对表进行合理的管理以及提高查询效率，Hive可以将表组织成“分区”。一个分区实际上就是表下的一个目录，一个表可以在多个维度上进行分区，分区之间的关系就是目录树的关系。Hive(Inceptor)分区分为静态分区跟动态分区，详细介绍如下：

静态分区在插入或者导入的时候需要指定具体的分区

[if !supportLists]1、  [endif]静态分区创建

需要在PARTITIONED BY后面跟上分区键，类型。例如：

CREATE TABLE p_table1(

id int

,name

string

)

PARTITIONED BY(date_day string)

stored as orc

这是一级分区，当然也可以创建多级分区。例如：

CREATE TABLE  p_table1(

id int

,name string

)

PARTITIONED BY(date_day string, company

string,emp_no string)

stored as orc

                                                                          

下面的实例都是以一级分区为例。                     

[if !supportLists]2、  [endif]静态分区插入数据

insert overwrite table p_table1 partition(date_day='2019-07-14')

values(1,'lucy')

或者insert overwrite table p_table1 partition(date_day='2019-07-15')

select 2 as id,'lily' as name

上面两个例子都是覆盖的形式，也就是插入这个分区之前，会将该分区数据删除，再插入新的数据，也可以写成追加的形式：

insert into p_table1

partition(date_day='2019-07-14') values(1,'lucy')

或者insert into e p_table1 partition(date_day='2019-07-15') select 2 as

id,'lily' as name

[if !supportLists]3、  [endif]静态分区查看分区

查看所有分区show partitions p_table1

结果如下：

date_day=2019-07-14

date_day=2019-07-15

查看某个分区show partitions p_table1 partition(date_day='2019-07-14')

结果如下：

date_day=2019-07-14

[if !supportLists]4、  [endif]静态分区删除分区

删除某个分区alter table p_table1 drop partition(date_day='2019-07-14')

或者删除范围内的分区alter table p_table1 drop partition(date_day>='2019-07-14')

1、动态分区创建

创建方式与静态分区表完全一样，一张表可同时被静态和动态分区键分区，只是动态分区键需要放在静态分区建的后面（因为HDFS上的动态分区目录下不能包含静态分区的子目录）。

CREATE TABLE p_table2(

id int

,name string

)

PARTITIONED BY(date_day string,emp_no

string)

stored as orc

这是创建了二级分区表。

2、动态分区插入数据

插入数据时需要开启动态数据支持：

set hive.exec.dynamic.partition=true

set hive.exec.dynamic.partition.mode=nostrict

插入数据（覆盖）insert overwrite table p_table2 partition(date_day，emp_no)

select 2 as id,'lily' as name,'2019-07-14' as date_day, ‘a’ as emp_no

分区并没有写死，而是根据查询到的值动态创建的两级分区。

3、动态分区查看分区、删除分区与静态分区 *** 作完全一致不再重述。

分桶字段是表内字段，默认是对分桶的字段进行hash值，然后模总的桶数，得到的值则是分区桶数，主要有以下两点好处：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN *** 作。对于JOIN *** 作两个表有一个相同的列，如果对这两个表都进行了桶 *** 作。那么将保存相同列值的桶进行JOIN *** 作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

创建分桶表

先看一下创建分桶表的创建，分桶表的建表有三种方式：直接建表，CREATE TABLE LIKE 和 CREATE TABLE AS SELECT ，单值分区表不能用 CREATETABLE

AS SELECT 建表。这里以直接建表为例：

create table b_table1(id int,name string)

clustered by (id) sorted by(id) into 4 buckets stored as textfile

使用CLUSTERED BY 子句来指定划分桶所用的列和要划分的桶的个数，当表分区时，每个分区下都会有4个桶。对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的 mapper知道右边表内相匹配的行在对应的桶内。因此，mapper只需要获取那个桶 (这只是右边表内存储数据的一小部分)即可进行连接。这一优化方法并不一定要求两个表必须桶的个数相同，两个表的桶个数是倍数关系也可以。用HiveQL对两个划分了桶的表进行连接。

桶中的数据可以根据一个或多个列另外进行排序。由于这样对每个桶的连接变成了高效的归并排序(merge-sort), 因此可以进一步提升map端连接的效率。

向分桶表写入数据

如何保证表中的数据都划分成桶了呢？把在Hive外生成的数据加载到划分成桶的表中，当然是可以的。其实让Hive来划分桶更容易。这一 *** 作通常针对已有的表。

Hive并不检查数据文件中的桶是否和表定义中的桶一致(无论是对于桶的数量或用于划分桶的列）。如果两者不匹配，在査询时可能会碰到错误或未定义的结果。因此，建议让Hive来进行划分桶的 *** 作。

要向分桶表中填充成员，需要将 hive.enforce.bucketing 属性设置为 true。这样Hive 就知道用表定义中声明的数量来创建桶。

下面有个未分桶的用户表b_user_test，数据如下：

1      a

2      b

3      c

4      d

5      e

6      f

7      g

插入语句

INSERT OVERWRITE TABLE b_table1 SELECT *

FROM b_user_test

查看文件结构

dfs -ls/user/hive/warehouse/bucketed_users

文件结构如下所示：

 /user/hive/warehouse/b_table1/000000_0

 /user/hive/warehouse/b_table1/000001_0

 /user/hive/warehouse/b_table1/000002_0

 /user/hive/warehouse/b_table1/000003_0

查看文件000000_0

dfs -cat /user/hive/warehouse/bucketed_users/000000_0

值为4 d说明文件000000_0存的是对分桶数求余等于0的那部分数据。

对桶中的数据进行采样

对分桶进行查询 tablesample(bucket x out of y on id)：

x:表示查询那个桶

y:表示建表指定的桶的总数,如果不是建表时指定的桶的总数，则会重新分桶。

x不能大于y。

取第一个桶的数据：

Sql：SELECT * FROM

b_table1 TABLESAMPLE(BUCKET 2 OUT OF 4 ON id)

结果：

5      e

[if !supportLists]1                       [endif]a

当桶数不等于建表指定的桶的总数时

Sql：SELECT * FROM

b_table1 TABLESAMPLE(BUCKET 2 OUT OF 3 ON id)

结果：

4      d

1      a

7      g

由结果可知，进行了重新分桶，分成了三个桶，取出第二个桶的数据，也就是hash值对3求余等于1的那部分数据。

分桶比分区粒度更细，在每个分区了可以将数据进行分桶 *** 作。

---