2020.9.17课堂笔记(Hive分区表和分桶表)

一、分区表（对应hdfs系统里的文件夹）

1.1 概念

Hive 中的表对应为 HDFS 上的指定目录，在查询数据时候，默认会对全表进行扫描，这样时间和性能的消耗都非常大。

分区为 HDFS 上表目录的子目录，数据按照分区存储在子目录中。如果查询的 where 字句的中包含分区条件，则直接从该分区去查找，而不是扫描整个表目录，合理的分区设计可以极大提高查询速度和性能。

这里说明一下分区表并 Hive 独有的概念，实际上这个概念非常常见。比如在我们常用的 Oracle 数据库中，当表中的数据量不断增大，查询数据的速度就会下降，这时也可以对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据存放到多个表空间（物理文件上），这样查询数据时，就不必要每次都扫描整张表，从而提升查询性能。

1.2 使用场景

通常，在管理大规模数据集的时候都需要进行分区，比如将日志文件按天进行分区，从而保证数据细粒度的划分，使得查询性能得到提升。

1.3 创建分区表

在 Hive 中可以使用 PARTITIONED BY 子句创建分区表。表可以包含一个或多个分区列，程序会为分区列中的每个不同值组合创建单独的数据目录。下面的我们创建一张雇员表作为测试：

 CREATE EXTERNAL TABLE emp_partition(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
    )
    PARTITIONED BY (deptno INT)   -- 按照部门编号进行分区
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_partition';

1.4 加载数据到分区表

加载数据到分区表时候必须要指定数据所处的分区：

# 加载部门编号为20的数据到表中
LOAD DATA LOCAL INPATH "/usr/file/emp20.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=20)
# 加载部门编号为30的数据到表中
LOAD DATA LOCAL INPATH "/usr/file/emp30.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=30)

1.5 查看分区目录

这时候我们直接查看表目录，可以看到表目录下存在两个子目录，分别是 deptno=20 和 deptno=30,这就是分区目录，分区目录下才是我们加载的数据文件。

# hadoop fs -ls  hdfs://hadoop001:8020/hive/emp_partition/

这时候当你的查询语句的 where 包含 deptno=20，则就去对应的分区目录下进行查找，而不用扫描全表。

2.1创建分区表

创建单级分区表：通过使用partitioned by定义分区

create table dept_partition(
deptno int, 
dname string,
loc string )
//通过PARTITINED BY定义分区
partitioned by (month string)
//创建多级分区：partitioned by (month string,date string)
row format delimited fields terminated by '\t';

指定了分区列：month 把分区的内容当成了一个列
可以在添加数据的时候往里面添加分区

--加载数据的同时添加分区
load data local inpath "/opt/datas/dept.txt" into table dept_partition partition(month='202012');

现在还没有创建分区，但是直接把数据往这个分区里加的话，能够查到分区和数据。

--添加分区
alter table dept_partition add partition(month="202011");

现在多了一个分区，但是里面还没有数据。
创建多级分区：

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

alter table dept_partition2 add partition(month="202011",date="01")
partition(month="202011",date="02")
partition(month="202012",date="01")
partition(month="202012",date="02");

-- 多级分区表加载数据
load data local inpath "/opt/datas/dept.txt" into table dept_partition2
partition(month='202012',date='01');

2.2静态分区操作

-- 添加分区
alter table dept_partition add partition(month='201906') ;
alter table dept_partition add partition(month='201905') partition(month='201904');
-- 删除分区
alter table dept_partition drop partition (month='201904');
alter table dept_partition drop partition (month='201905'), partition (month='201906');

查看分区表有多少分区：show partitions + 表名

--查看分区数（重要，在工作中会经常使用）
show partitions dept_partition;

2.3动态分区操作

使用动态分区需设定属性，会自动的把相应的数据生成到指定的分区里。

set hive.exec.dynamic.partition=true;              //开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict;    //非严格模式

set：只在当前会话中有效 hive-site.xml 配置文件中永久生效
分区过多会生成非常多的小文件，默认情况下是严格模式，不允许使用动态分区的。

-- 动态分区插入数据
insert into dynamic_people
partition(year,month)
select id,name,age,start_date,year(start_date),month(start_date)
from people;
-- 查询数据
select * from dynamic_people where year = 2018;	

示例：动态分区表创建的过程和静态分区表是一样的

create table if not exists employee_hr_partition(
name string,
id int,
num string,
time2 string
)
partitioned by (month string,date string)
row format delimited
fields terminated by '|';

分区列也会当成字段的，字段名不要在列名中出现过，会有冲突。
创建一个非分区表，把数据加载到非分区表里，然后再通过查询表里的数据往分区表里插入数据
动态分区的流程：

-- 创建非分区表：
create table if not exists employee_hr(
name string,
id int,
num string,
time2 string
)
row format delimited
fields terminated by '|';
-- 加载数据：
load data local inpath '/opt/datas/employee_hr.txt' into table employee_hr;
-- 设置两个参数：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict; 
-- 往分区表里插入数据：
insert into table employee_hr_partition partition(month,date)  //需要指定分区
select name,id,num,time2,
month(time2) as month,  //从表里获取到月份和日期，使用到一个函数month()  date()
date(time2) as date     //也可以使用subString的方式获取信息
from employee_hr;

查看某一个分区的数据：

select * from employee_hr_partition where month='9' and date='2017-09-30';

会把分区表的字段也当成列。区别，使用where条件指定分区列的时候，会只去指定的分区文件夹里查询，提高查询效率。

二、分桶表（对应hdfs系统里的文件）

底层原理相当于之前学MapReduce里面的分区，默认的分桶是hash分桶，底层实现就是使用的hashpartitioner，分桶的作用：可以提供更高的查询效率，按照hash值做分桶，把数据分成一个一个的文件。进行表的join的时候，正常情况是先有一个笛卡尔积，再根据条件过滤，两个大表想乘。分桶会只去对应的表里面，相乘的数据会表少。提高查询效率，企业里也会使用但不是特别多，分区使用比较多。
数据量特别多防止数据倾斜的时候，做抽样的工作，会使用到分桶。

1.1 简介

分区提供了一个隔离数据和优化查询的可行方案，但是并非所有的数据集都可以形成合理的分区，分区的数量也不是越多越好，过多的分区条件可能会导致很多分区上没有数据。同时 Hive 会限制动态分区可以创建的最大分区数，用来避免过多分区文件对文件系统产生负担。鉴于以上原因，Hive 还提供了一种更加细粒度的数据拆分方案：分桶表 (bucket Table)。

分桶表会将指定列的值进行哈希散列，并对 bucket（桶数量）取余，然后存储到对应的 bucket（桶）中。

1.2 理解分桶表

单从概念上理解分桶表可能会比较晦涩，其实和分区一样，分桶这个概念同样不是 Hive 独有的，对于 Java 开发人员而言，这可能是一个每天都会用到的概念，因为 Hive 中的分桶概念和 Java 数据结构中的 HashMap 的分桶概念是一致的。

当调用 HashMap 的 put() 方法存储数据时，程序会先对 key 值调用 hashCode() 方法计算出 hashcode，然后对数组长度取模计算出 index，最后将数据存储在数组 index 位置的链表上，链表达到一定阈值后会转换为红黑树 (JDK1.8+)。下图为 HashMap 的数据结构图：

图片引用自：HashMap vs. Hashtable

1.3 创建分桶表

在 Hive 中，我们可以通过 CLUSTERED BY 指定分桶列，并通过 SORTED BY 指定桶中数据的排序参考列。下面为分桶表建表语句示例：

  CREATE EXTERNAL TABLE emp_bucket(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT)
    CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS  --按照员工编号散列到四个 bucket 中
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_bucket';

1.4 加载数据到分桶表

这里直接使用 Load 语句向分桶表加载数据，数据时可以加载成功的，但是数据并不会分桶。

这是由于分桶的实质是对指定字段做了 hash 散列然后存放到对应文件中，这意味着向分桶表中插入数据是必然要通过 MapReduce，且 Reducer 的数量必须等于分桶的数量。由于以上原因，分桶表的数据通常只能使用 CTAS(CREATE TABLE AS SELECT) 方式插入，因为 CTAS 操作会触发 MapReduce。加载数据步骤如下：

1. 设置强制分桶(分桶只有动态分桶)

set hive.enforce.bucketing = true; --Hive 2.x 不需要这一步

在 Hive 0.x and 1.x 版本，必须使用设置 hive.enforce.bucketing = true，表示强制分桶，允许程序根据表结构自动选择正确数量的 Reducer 和 cluster by column 来进行分桶。

2. CTAS导入数据

INSERT INTO TABLE emp_bucket SELECT *  FROM emp;  --这里的 emp 表就是一张普通的雇员表

可以从执行日志看到 CTAS 触发 MapReduce 操作，且 Reducer 数量和建表时候指定 bucket 数量一致：

1.5 查看分桶文件

bucket(桶) 本质上就是表目录下的具体文件：

2.1 设置属性：

SET hive.enforce.bucketing = true;

2.2 定义分桶

分桶的列是表中已有的列，分桶数最好是2的n次方

CLUSTERED BY (employee_id) INTO 2 BUCKETS

2.3 分桶抽样

随机抽样基于整行数据

SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;

随机抽样基于指定列（使用分桶列更高效）

SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON id) s;

2.4 扩展：

TABLESAMPLE(BUCKET x OUT OF y) 。

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。
注意：x的值必须小于等于y的值，否则会报错

三、分区表和分桶表结合使用

分区表和分桶表的本质都是将数据按照不同粒度进行拆分，从而使得在查询时候不必扫描全表，只需要扫描对应的分区或分桶，从而提升查询效率。两者可以结合起来使用，从而保证表数据在不同粒度上都能得到合理的拆分。下面是 Hive 官方给出的示例：

CREATE TABLE page_view_bucketed(
	viewTime INT, 
    userid BIGINT,
    page_url STRING, 
    referrer_url STRING,
    ip STRING )
 PARTITIONED BY(dt STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

此时导入数据时需要指定分区：

INSERT OVERWRITE page_view_bucketed
PARTITION (dt='2009-02-25')
SELECT * FROM page_view WHERE dt='2009-02-25';

参考文档：https://github.com/heibaiying/BigData-Notes

MapReduce底层可以换成tez提高执行速度。