hive 分区的理解 hive分区表的意义

深入理解Hive

• 分区与分桶

• Hive分区
• Hive分桶
• 分区又分桶

• Join底层MapReduce实现

• Common Join
• Map Join

• 高阶聚合函数

• with cube
• grouping sets
• rollup

分区与分桶

Hive分区

在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。分区表指的是在创建表时指定的partition的分区空间。分区是指按照数据表的某列或某些列分为多个区，区从形式上可以理解为文件夹，比如我们要收集某个大型网站的日志数据，一个网站每天的日志数据存在同一张表上，由于每天会生成大量的日志，导致数据表的内容巨大，在查询时进行全表扫描耗费的资源非常多。那其实这个情况下，我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找。

1、创建分区表插入数据
单分区表

create table if not exists user_info_partition_single(
user_id string comment '用户id',
user_name string comment '用户姓名',
user_gender string comment '用户性别',
user_age int comment '用户年龄'
)
comment '用户信息表'
partitioned by(province string)
row format delimited fields terminated by ',' lines terminated by '\n'
;

插入数据

insert overwrite table user_info_partition_single partition(province='BeiJing')
select '11' as user_id,
'YangZi' as user_name,
'F' as user_gender,
28 as user_age
;

多分区表

create table if not exists user_info_partition_multiple(
user_id string comment '用户id',
user_name string comment '用户姓名',
user_gender string comment '用户性别',
user_age int comment '用户年龄'
)
comment '用户信息表'
partitioned by(province string,city string)
row format delimited fields terminated by ',' lines terminated by '\n'
;

插入数据

insert overwrite table user_info_partition_multiple partition(province='JangSu',city='SuZhou')
select '11' as user_id,
'YangZi' as user_name,
'F' as user_gender,
28 as user_age
;

2、查看分区目录表

[root@localhost hive-2.3.6]# hdfs dfs -ls /hive/warehouse/test.db/user_info_partition_single
Found 1 items
drwxrwxrwx   - root supergroup          0 2021-01-06 19:12 /hive/warehouse/test.db/user_info_partition_single/province=BeiJing
[root@localhost hive-2.3.6]# hdfs dfs -ls /hive/warehouse/test.db/user_info_partition_multiple
Found 1 items
drwxrwxrwx   - root supergroup          0 2021-01-13 11:37 /hive/warehouse/test.db/user_info_partition_multiple/province=JangSu
[root@localhost hive-2.3.6]# hdfs dfs -ls /hive/warehouse/test.db/user_info_partition_multiple/province=JangSu
Found 1 items
drwxrwxrwx   - root supergroup          0 2021-01-13 11:37 /hive/warehouse/test.db/user_info_partition_multiple/province=JangSu/city=SuZhou

通过查看分区表在文件系统中位置，我们会发现分区所依据的列反应在文件路径上，单分区表下面有个温文件目录province=BeiJing，多分区表下面有个province=JiangSu目录，province=JiangSu目录下面有个city=SuZhou的目录。

重点强调，所谓分区，这是将满足某些条件的记录打包，做个记号，在查询时提高效率，相当于按文件夹对文件进行分类，文件夹名可类比分区字段。这个分区字段形式上存在于数据表中，在查询时会显示到客户端上，但并不真正在存储在数据表文件中，是所谓伪列。所以，千万不要以为是对属性表中真正存在的列按照属性值的异同进行分区。比如上面的分区依据的列province并不真正的存在于数据表中，是我们为了方便管理添加的一个伪列，这个列的值也是我们人为规定的，不是从数据表中读取之后根据值的不同将其分区。我们并不能按照某个数据表中真实存在的列来分区。

Hive分桶

分桶是相对分区进行更细粒度的划分。分桶进行区分，Hive采用对列值哈希，然后将某列属性值的hash值除以桶的个数求余的方式决定该条记录存放在哪个桶当中。例如按照province属性分为3个桶，就是对province属性值的hash值对3取摸，按照取模结果对数据分桶。如取模结果为0的数据记录存放到一个文件，取模为1的数据存放到一个文件，取模为2的数据存放到一个文件。

1、分桶的场景
分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，尤其是需要确定合适大小的分区划分方式，（不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况）
分桶是将数据集分解为更容易管理的若干部分的另一种技术。

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

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

3、、如何分桶
1）分桶之前要执行命令hive.enforce.bucketiong=true;
2）使用关键字clustered by 指定分区依据的列名，指定分为多少桶
3）与分区不同的是，分区依据的不是真实数据表文件中的列，而是我们指定的伪列，但是分桶是依据数据表中真实的列而不是伪列。所以在指定分区依据的列的时候要指定列的类型，因为在数据表文件中不存在这个列，相当于新建一个列。而分桶依据的是表中已经存在的列，这个列的数据类型显然是已知的，所以不需要指定列的类型。

创建表格

create table if not exists user_info_bucket (
user_id string comment '用户id',
user_name string comment '用户姓名',
user_gender string comment '用户性别',
user_age int comment '用户年龄'
)
clustered by (user_id) into 3 buckets
row format delimited fields terminated by ',' lines terminated by '\n'
;

插入数据

load data local inpath '/home/huangwei/input/user_info.txt' into table user_info_bucket;
Error: Error while compiling statement: FAILED: SemanticException Please load into an intermediate table and use 'insert... select' to allow Hive to enforce bucketing. Load into bucketed tables are disabled for safety reasons. If you know what you are doing, please sethive.strict.checks.bucketing to false and that hive.mapred.mode is not set to 'strict' to proceed. Note that if you may get errors or incorrect results if you make a mistake while using some of the unsafe features. (state=42000,code=40000)

分桶表不能采用load数据方式插入数据，需要将数据先导入一个intermediate table中，采用"insert…select"方式插入数据

insert overwrite table user_info_bucket select user_id,user_name,user_gender,user_age from user_info_external;

4、查看分桶信息

hdfs dfs -ls /hive/warehouse/test.db/user_info_bucket
Found 3 items
-rwxrwxrwx   1 root supergroup         44 2021-01-13 14:34 /hive/warehouse/test.db/user_info_bucket/000000_0
-rwxrwxrwx   1 root supergroup         61 2021-01-13 14:34 /hive/warehouse/test.db/user_info_bucket/000001_0
-rwxrwxrwx   1 root supergroup         47 2021-01-13 14:34 /hive/warehouse/test.db/user_info_bucket/000002_0

可以看到分3个桶就是将数据表由一个文件存储分为3个文件存储

5、对桶数据进采样
桶的个数从1开始计数。因此，前面的查询从4个桶的第一个中获取所有的用户。 对于一个大规模的、均匀分布的数据集，这会返回表中约四分之一的数据行。我们 也可以用其他比例对若干个桶进行取样(因为取样并不是一个精确的操作，因此这个 比例不一定要是桶数的整数倍)。

select * from user_info_bucket tablesample(bucket 1 out of 3 on user_id);
+---------------------------+-----------------------------+-------------------------------+----------------------------+
| user_info_bucket.user_id  | user_info_bucket.user_name  | user_info_bucket.user_gender  | user_info_bucket.user_age  |
+---------------------------+-----------------------------+-------------------------------+----------------------------+
| 9                         | LuHan                       | M                             | 30                         |
| 6                         | JinChen                     | F                             | 30                         |
| 3                         | ZhengKai                    | M                             | 34                         |
+---------------------------+-----------------------------+-------------------------------+----------------------------+

tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUTOF y)
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。

分区又分桶

可以在分区的基础上再分桶

create table if not exists user_info_partition_bucket (
user_id string comment '用户id',
user_name string comment '用户姓名',
user_gender string comment '用户性别',
user_age int comment '用户年龄'
)
partitioned by(province string,city string)
clustered by (user_id) into 3 buckets
row format delimited fields terminated by ',' lines terminated by '\n'
;

insert overwrite table user_info_partition_bucket partition(province='JangSu',city='SuZhou') select user_id,user_name,user_gender,user_age from person;

查看分区分桶信息

hdfs dfs -ls /hive/warehouse/test.db/user_info_partition_bucket/province=JangSu/city=SuZhou/
Found 3 items
-rwxrwxrwx   1 root supergroup         40 2021-01-13 15:32 /hive/warehouse/test.db/user_info_partition_bucket/province=JangSu/city=SuZhou/000000_0
-rwxrwxrwx   1 root supergroup         31 2021-01-13 15:32 /hive/warehouse/test.db/user_info_partition_bucket/province=JangSu/city=SuZhou/000001_0
-rwxrwxrwx   1 root supergroup         25 2021-01-13 15:32 /hive/warehouse/test.db/user_info_partition_bucket/province=JangSu/city=SuZhou/000002_0

查看分桶数据

select * from user_info_partition_bucket tablesample(bucket 1 out of 3 on user_id);
+-------------------------------------+---------------------------------------+-----------------------------------------+--------------------------------------+--------------------------------------+----------------------------------+
| user_info_partition_bucket.user_id  | user_info_partition_bucket.user_name  | user_info_partition_bucket.user_gender  | user_info_partition_bucket.user_age  | user_info_partition_bucket.province  | user_info_partition_bucket.city  |
+-------------------------------------+---------------------------------------+-----------------------------------------+--------------------------------------+--------------------------------------+----------------------------------+
|                                     | NULL                                  | NULL                                    | NULL                                 | JangSu                               | SuZhou                           |
| 6                                   | AiLi                                  | F                                       | 18                                   | JangSu                               | SuZhou                           |
| 3                                   | WangCuiHua                            | F                                       | 21                                   | JangSu                               | SuZhou                           |
+-------------------------------------+---------------------------------------+-----------------------------------------+--------------------------------------+--------------------------------------+----------------------------------+

Join底层MapReduce实现

Hive中的Join可分为两种情况
Common Join (Reduce阶段完成Join)
Map Join (Map阶段完成Join)

Common Join

如果没有开启hive.auto.convert.join=true或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，在Reduce阶段完成join，并且整个过程包含Map、Shuffle、Reduce阶段。
1、Map阶段
读取表的数据，Map输出以Join on条件中的列为key，如果Join有多个关联键，则以这些关联键的组合为key。
Map输出的value为Join之后需要输出或者作为条件的列；同时在value中还会包含表的Tag信息，用于标明此value对应的表，按照key进行排序。
2、Shuffle阶段
根据key取哈希值，并将key/value按照哈希值分发到不同的reduce中
3、Reduce阶段
根据key完成Join操作，并且通过Tag来识别不同表中的数据。在合并过程中，把表编号扔掉。
举例

drop table if exists wedw_dwd.user_info_df;
 CREATE TABLE wedw_dwd.user_info_df(
  user_id    string  COMMENT '用户id',
  user_name  string  COMMENT '用户姓名'
 )
row format delimited fields terminated by '\t'
 STORED AS textfile
 ;
 
 +----------+-------------+
| user_id  | user_name    |
+----------+-------------+
| 1        | 小红         |
| 2        | 小明         |
| 3        | 小花         |
+----------+-------------+

drop table if exists wedw_dwd.order_info_df;
 CREATE TABLE wedw_dwd.order_info_df(
  user_id      string  COMMENT '用户id',
  course_name  string  COMMENT '课程名称'
 )
row format delimited fields terminated by '\t'
 STORED AS textfile
 ;
 
 +----------+--------------+
| user_id  | course_name  |
+----------+---------------+
| 1        | spark        |
| 2        | flink        |
| 3        | java         |
+----------+--------------+

select
 t1.user_id
,t1.user_name
,t2.course_name
from
wedw_dwd.user_info_df t1
join wedw_dwd.order_info_df t2
on t1.user_id = t2.user_id
;
+----------+------------+---------------+
| user_id  | user_name  | course_name  |
+----------+------------+---------------+
| 1        | 小红         | spark        |
| 2        | 小明         | flink        |
| 3        | 小花         | java         |
+----------+------------+---------------+

图解：(在合并过程中，把表编号扔掉)

Map Join

1、什么是Map Join
MapJoin顾名思义，就是在Map阶段进行表之间的连接。而不需要进入到Reduce阶段才进行连接。这样就节省了在Shuffle阶段时要进行的大量数据传输。从而起到了优化作业的作用。

2、MapJoin的原理：
通常情况下，要连接的各个表里面的数据会分布在不同的Map中进行处理。即同一个Key对应的Value可能存在不同的Map中。这样就必须等到Reduce中去连接。

要使MapJoin能够顺利进行，那就必须满足这样的条件：除了一份表的数据分布在不同的Map中外，其他连接的表的数据必须在每个Map中有完整的拷贝。

3、MapJoin适用的场景：
通过上面分析你会发现，并不是所有的场景都适合用MapJoin. 它通常会用在如下的一些情景：在二个要连接的表中，有一个很大，有一个很小，这个小表可以存放在内存中而不影响性能。
这样我们就把小表文件复制到每一个Map任务的本地，再让Map把文件读到内存中待用。
不等值的链接操作

4、MapJoin的实现方法：

1）在Map-Reduce的驱动程序中使用静态方法DistributedCache.addCacheFile()增加要拷贝的小表文件，。JobTracker在作业启动之前会获取这个URI列表，并将相应的文件拷贝到各个TaskTracker的本地磁盘上。

 2）在Map类的setup方法中使用DistributedCache.getLocalCacheFiles()方法获取文件目录，并使用标准的文件读写API读取相应的文件。

5、Hive内置提供的优化机制之一就包括MapJoin
在Hive v0.7之前，需要使用hint提示 /*+ mapjoin(table) */才会执行MapJoin 。Hive v0.7之后的版本已经不需要给出MapJoin的指示就进行优化。它是通过如下配置参数来控制的：

hive> set hive.auto.convert.join=true;

Hive还提供另外一个参数–表文件的大小作为开启和关闭MapJoin的阈值。

hive.mapjoin.smalltable.filesize=25000000 即25M

示例：

select f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802)

该语句中B表有30亿行记录，A表只有100行记录，而且B表中数据倾斜特别严重，有一个key上有15亿行记录，在运行过程中特别的慢，而且在reduece的过程中遇有内存不够而报错。
解决方法：MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map是进行了join操作，省去了reduce运行的效率也会高很多

select /*+ mapjoin(A)*/ f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802)

mapjoin还有一个很大的好处是能够进行不等连接的join操作，如果将不等条件写在where中，那么mapreduce过程中会进行笛卡尔积，运行效率特别低，

如果使用mapjoin操作，在map的过程中就完成了不等值的join操作，效率会高很多。

select A.a ,A.b from A join B where A.a>B.a

高阶聚合函数

数据准备

广东,广州,白云区,100,30
广东,广州,番禺区,120,45
广东,深圳,福田区,200,67
广东,深圳,南山区,290,10
浙江,杭州,萧山区,80,20
浙江,杭州,滨江区,120,50
浙江,宁波,江东区,80,23
浙江,宁波,江北区,45,5

创建表加载数据

create table if not exists area_gdp_df
(
province_name string comment '省份名称',
city_name string comment '城市名称',
area_name string comment '地区名称',
people_cnt int comment '人口数量',
amt decimal(16,2) comment 'GDP'
)
row format delimited fields terminated by ','
;

with cube

分组组合最全，包含所有可能的组合

select province_name,city_name,area_name,sum(people_cnt) all_people_cnt from area_gdp_df group by province_name,city_name,area_name with cube;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+----------------+------------+------------+-----------------+
| province_name  | city_name  | area_name  | all_people_cnt  |
+----------------+------------+------------+-----------------+
| NULL           | NULL       | NULL       | 1035            |
| NULL           | NULL       | 南山区        | 290             |
| NULL           | NULL       | 江东区        | 80              |
| NULL           | NULL       | 江北区        | 45              |
| NULL           | NULL       | 滨江区        | 120             |
| NULL           | NULL       | 番禺区        | 120             |
| NULL           | NULL       | 白云区        | 100             |
| NULL           | NULL       | 福田区        | 200             |
| NULL           | NULL       | 萧山区        | 80              |
| NULL           | 宁波         | NULL       | 125             |
| NULL           | 宁波         | 江东区        | 80              |
| NULL           | 宁波         | 江北区        | 45              |
| NULL           | 广州         | NULL       | 220             |
| NULL           | 广州         | 番禺区        | 120             |
| NULL           | 广州         | 白云区        | 100             |
| NULL           | 杭州         | NULL       | 200             |
| NULL           | 杭州         | 滨江区        | 120             |
| NULL           | 杭州         | 萧山区        | 80              |
| NULL           | 深圳         | NULL       | 490             |
| NULL           | 深圳         | 南山区        | 290             |
| NULL           | 深圳         | 福田区        | 200             |
| 广东             | NULL       | NULL       | 710             |
| 广东             | NULL       | 南山区        | 290             |
| 广东             | NULL       | 番禺区        | 120             |
| 广东             | NULL       | 白云区        | 100             |
| 广东             | NULL       | 福田区        | 200             |
| 广东             | 广州         | NULL       | 220             |
| 广东             | 广州         | 番禺区        | 120             |
| 广东             | 广州         | 白云区        | 100             |
| 广东             | 深圳         | NULL       | 490             |
| 广东             | 深圳         | 南山区        | 290             |
| 广东             | 深圳         | 福田区        | 200             |
| 浙江             | NULL       | NULL       | 325             |
| 浙江             | NULL       | 江东区        | 80              |
| 浙江             | NULL       | 江北区        | 45              |
| 浙江             | NULL       | 滨江区        | 120             |
| 浙江             | NULL       | 萧山区        | 80              |
| 浙江             | 宁波         | NULL       | 125             |
| 浙江             | 宁波         | 江东区        | 80              |
| 浙江             | 宁波         | 江北区        | 45              |
| 浙江             | 杭州         | NULL       | 200             |
| 浙江             | 杭州         | 滨江区        | 120             |
| 浙江             | 杭州         | 萧山区        | 80              |
+----------------+------------+------------+-----------------+

grouping sets

自定义维度，根据需要分组即可。

select province_name,city_name,sum(people_cnt) all_people_cnt from area_gdp_df group by province_name,city_name grouping sets((province_name,city_name));
+----------------+------------+-----------------+
| province_name  | city_name  | all_people_cnt  |
+----------------+------------+-----------------+
| 广东             | 广州         | 220             |
| 广东             | 深圳         | 490             |
| 浙江             | 宁波         | 125             |
| 浙江             | 杭州         | 200             |
+----------------+------------+-----------------+

select province_name,city_name,sum(people_cnt) all_people_cnt from area_gdp_df group by province_name,city_name grouping sets((province_name,city_name),(province_name));
+----------------+------------+-----------------+
| province_name  | city_name  | all_people_cnt  |
+----------------+------------+-----------------+
| 广东             | NULL       | 710             |
| 广东             | 广州         | 220             |
| 广东             | 深圳         | 490             |
| 浙江             | NULL       | 325             |
| 浙江             | 宁波         | 125             |
| 浙江             | 杭州         | 200             |
+----------------+------------+-----------------+

rollup

rollup的各维度组合应满足，前一维度为null后一位维度必须为null，前一维度取非null时，下一维度随意，即层次维度

select province_name,city_name,area_name,sum(people_cnt) all_people_cnt from area_gdp_df group by province_name,city_name,area_name with rollup;
+----------------+------------+------------+-----------------+
| province_name  | city_name  | area_name  | all_people_cnt  |
+----------------+------------+------------+-----------------+
| NULL           | NULL       | NULL       | 1035            |
| 广东             | NULL       | NULL       | 710             |
| 广东             | 广州         | NULL       | 220             |
| 广东             | 广州         | 番禺区        | 120             |
| 广东             | 广州         | 白云区        | 100             |
| 广东             | 深圳         | NULL       | 490             |
| 广东             | 深圳         | 南山区        | 290             |
| 广东             | 深圳         | 福田区        | 200             |
| 浙江             | NULL       | NULL       | 325             |
| 浙江             | 宁波         | NULL       | 125             |
| 浙江             | 宁波         | 江东区        | 80              |
| 浙江             | 宁波         | 江北区        | 45              |
| 浙江             | 杭州         | NULL       | 200             |
| 浙江             | 杭州         | 滨江区        | 120             |
| 浙江             | 杭州         | 萧山区        | 80              |
+----------------+------------+------------+-----------------+