Hive的分区、分桶

Hive的分区表、分桶表

一、  Hive库的分区表

1.1概述

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

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

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

1.2使用场景

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

1.3创建分区

（1）create table if not exists part1(id int,name string,ordertime date) partitioned by (ordertime); --创建分区表

（2）insert into part1 partition (ordertime='20230303') (id,name)(1,'A'); --分区表中插入数据，分区字段不区分大小写，字段值区分大小写

（3）load data local inpath './data/part1.txt' overwrite into table part1; --文件加载数据

（4）show partitions tablename; --查看分区

（5）alter table part1 partition (ordertime=’20230303’) set location’hdfs://qianfeng01: 8020/user/hive/warehouse/mydb.db/part1/ordertime=2020-05-05’;--需要填写完整路径

（6）alter table part1 add partition (ordertime='20230304'); --新增分区

（7）alter table part1 drop partition (ordertime='20230304'); --删除分区

   --默认创建分区时，删除所有分区时，表目录不会被删除；

   --使用location指定分区对应位置，删除操作时，对应目录（最里层）会被删除，上级目录如果没有文件存在，也会被删除，如果有文件存在，则不会被删除

1.4分区种类

1.4.1静态分区

直接加载数据文件到指定分区，即静态分区表

1.4.2动态分区

数据未知，根据分区的值来确定需要创建的分区（分区目录不是指定的，而是根据数据的值自动分配的）

1.4.3混合分区

动态和静态都有

（注意：

（1）hive的分区使用的是表外字段，分区字段是个伪列，但是分区字段可以做查询过滤。 （2）分区字段不建议使用中文

（3）一般不建议使用动态分区，因为动态分区会使用mapreduce来进行查询数据，如果分区数据过多，导致namenode和resourcemanager的性能瓶颈。所以建议使用动态分区前尽可能知道分区数量。

（4）分区属性的修改都可以修改元数据和hdfs数据内容

）

--hive分区和Mysql分区的区别：Mysql分区使用表内字段，Hive分区使用表外字段

二、Hive中的数据分桶

2.1数据分桶适用的场景

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

2.2数据分桶的原理

跟MR中的HashPartitioner的原理一摸一样：

MR中：按照key的hash值去模除以reductTask的个数

Hive中：按照分桶字段的hash值去模除以分桶的个数

Hive也是针对某一列进行桶的组织。Hive采用队列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶中。

分桶的实质就是对分桶字段进行hash然后存放到对应文件中，所以说如果原有数据没有按key hash，需要在插入分桶的时候hash,也就是说向分桶表中插入数据的时候必然要执行一次MAPREDUCE，这也就是分桶表的数据基本只能通过结果集查询插入的方式进行导入

2.3数据分桶的作用

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

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

2.4创建数据分桶表

创建分桶和创建普通的表区别不大，只需要注意：分区中字段是原始表中不存在的字段，分桶中的字段是原始表中已有字段

Create …

Partitioned by (‘day’ string)  --分区

Clustered by (‘mediaID’,’campID’) into 100 DUCKETS  --分桶

2.5分桶表中插入数据

2.5.1导入中间表

从hdfs或本地磁盘中load数据，导入中间表

2.5.2数据导入

通过中间表查询的方式完成数据导入

需要确保reduce的数量与表中的bucket一致，有两种方式：

（1）让Hive强制分桶，自动按照分桶表的bucket进行分桶（推荐）

Set hive.enforce.bucketing=true;

（2）手动指定reduce数量

Set mapreduce.job.reduces=num;

Set mapreduce.reduce.tasks=num;

并在select后面增加cluster by语句

2.6针对分桶表的数据抽样

分桶的主要优势就是数据抽样，主要有两种方式：

2.6.1基于桶抽样

SELECT * FROM bucketed_users TABLESAMPLE(BUCKET 1 OUT OF 4 ON id);

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

（1）说法1：

注：tablesample是抽样语句，语法TABLESAMPLE(BUCKET x OUT OF y)                                                                                                                

y必须是table总bucket数的倍数或因子。Hve根据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/26=）2个bucket的数据，分别为第3个bucket和（3+16=）19个bucket的数据。

（2）说法2：

分桶语句中的分母表示的是数据将会被散列的桶的个数，分子表示将会选择的桶的个数。

2.6.2基于百分比抽样

Hive另外一种按照抽样百分比进行抽样的方式，该种方式基于行数，按照输入路径下的数据块百分比进行抽样。

这种抽样最小的单元块是一个hdfs数据块，如果表的数据大小小于普通数据块大小的128M，将返回所有行。基于百分比的抽样方式提供了一个变量，用于控制基于数据块的调优种子信息。

<porperty>

  <name>hive.sample.seednumber</name>

  <value>0</value>

</porperty>

2.7数据分桶存在的缺陷

如果通过数据文件load到分桶表中会存在额外的MR负担。

实际生产中分桶策略使用频率较低，更常见的还是数据分区。

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

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

--创建表

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';