分区分桶的应用场景_分区和分桶的作用

实际应用中，hive里面的分区表和分桶表一般用于什么场景呢？
　　相同点：分区表和分桶都能将参与计算的数据限定在一个范围内，hive 上分区是以目录的形式存在，分桶则是以不同文件的形式存在。从存储形式所在的层级上来看，这两者并不冲突。

　　在实际场景中，数仓事实表中的数据通常有事件发生的时间，比如可以将数据以天作为分区存储，如果只关心前一天/ 最近一段时间的数据，数据查询时就可以只扫描对应的分区。

　　分桶则是用在一个没有自然分区属性的字段上，比如说用户的id，有可能是一个随机值或者是个字符串。假设两张表以用户 id 为 join key 做一次 join 就需要在全量用户信息上做 join，join build 就需要比较大的内存。如果首先将 left 和 right 的数据都按照同样的值进行 hash 后取模，也就是说同一个 user 的数据在left 和 right 表都存在相同 bucket id 的分桶中。在具体引擎做计算的时候，就可以一个分桶一个分桶的做，需要的内存就会少很多。

　　分区表

　　分区表的基本操作

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

　　1）创建分区表语法：

　　注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

　　2）加载数据到分区表中

　　1.数据准备

　　2.加载数据

　　注意：分区表加载数据时，必须指定分区

　　4）查询分区表中数据

　　单分区查询

　　多分区联合查询

　　5）增加分区

　　创建单个分区

　　同时创建多个分区

　　6）删除分区

　　删除单个分区

　　同时删除多个分区

　　7）查看分区表有多少分区

　　8）查看分区表结构

　　二级分区

　　1）创建二级分区表

　　2）正常的加载数据

　　（1）加载数据到二级分区表中

　　（2）查询分区数据

　　3）把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

　　（1）方式一：上传数据后修复

　　上传数据

　　查询数据（查询不到刚上传的数据）

　　执行修复命令

　　（2）方式二：上传数据后添加分区

　　上传数据

　　执行添加分区

　　（3）方式三：创建文件夹后 load 数据到分区

　　创建目录

　　上传数据

　　动态分区参数设置

　　关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据 插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过， 使用 Hive 的动态分区，需要进行相应的配置。

　　（1）开启动态分区功能（默认 true，开启）

　　（2）设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为 静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）

　　（3）在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000

　　（4）在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际 的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就 需要设置成大于 365，如果使用默认值 100，则会报错。

　　（5）整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000

　　（6）当有空分区生成时，是否抛出异常。一般不需要设置。默认 false

　　分桶表

　　分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理 的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围 划分。

　　1）先创建分桶表

　　（1）数据准备

　　（2）创建分桶表

　　（3）查看表结构

　　（4）导入数据到分桶表中，load 的方式

　　（5）分桶规则：

　　Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方 式决定该条记录存放在哪个桶当中

　　2）分桶表操作需要注意的事项

　　（1）reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个 数设置为大于等于分桶表的桶数

　　（2）从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题

　　（3）不要使用本地模式

　　3）insert 方式将数据导入分桶表

　　先创立好一个stu_insert表；

　　导入数据

　　插入

　　抽样查询

　　对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结 果。Hive 可以通过对表进行抽样来满足这个需求。

　　语法: TABLESAMPLE(BUCKET x OUT OF y) x小于等于y

　　原文链接：Hive之分区和分桶

　　分区

　　分区可以提高查询效率，实际上 hive 的一个分区就是 HDFS 上的一个目录，目录里放着属于该分区的数据文件。

　　分区的基本操作

　　创建分区表

　　*分区字段不能是表中字段

　　创建完分区表如果需要将数据导入表中，需要用 load 命令导入；

　　如果是在 HDFS 中创建目录并将数据文件传到目录中，是没办法查到的，因为查询分区表是需要查询元数据的;

　　如果非要用这种方法或者已经做了，可以执行修复命令:

　　查看分区

　　查询

　　添加分区

　　删除分区

　　二级分区

　　二级分区相当于在一级分区对应的目录上新增一个目录，一般用于单个分区数据量很大，需要做拆解的情况。

　　创建二级分区表

　　二级分区表的其它操作与一级的区别不大，因此不做过多的描写。

　　动态分区

　　关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中

　　Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，不过使用 Hive 的动态分区需要进行相应的配置。

　　开启动态分区功能（默认 true，开启）

　　设置为非严格模式

　　默认 strict，表示至少指定一个分区为静态分区，nonstrict 表示允许所有的分区字段都能使用动态分区。

　　在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000

　　在每个执行 MR 的节点上，最大可以创建多少个动态分区。比如源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就 需要设置成大于 365，如果使用默认值 100，则会报错。

　　动态插入数据到分区

　　分桶

　　分区提高了数据的查询效率，同时还能将数据隔离开，但是并非所有数据能形成合理的分区。

　　hive可以将数据进行分桶，不同于分区是针对存储路径进行分类，分桶是在数据文件中对数据进行划分的一种技术。

　　分桶是指定某一列，让该列数据按照哈希取模的方式随机、均匀的分发到各个桶文件中。

　　创建分桶表

　　加载数据

　　加载数据到分桶表中可以使用 load 或者 insert 的方式。

　　需要注意的是，reduce 的个数设置应该为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个 数设置为大于等于分桶表的桶数。

　　抽样

　　对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结 果。Hive 可以通过对表进行抽样来满足这个需求。

　　语法：tablesample（bucket x out of y）

　　y必须是table总共bucket数的倍数或者因子。

　　上面的语句表示：对于分桶数为 6 的表，总共抽取 6/y = 6/3 = 2 个bucket的数据，

　　分别为第 x=1 个 bucket 和第 x+3=4 个 bucket 的数据。

　　小结

　　本文简单介绍了 hive 的分区，包括如何创建分区表、新建分区和删除分区，还有二级分区和动态分区；以及分桶表，包括分桶表的概念和抽样函数。

　　“大数据的奇妙冒险”提示您：持续不迷路，转载请注明出处

　　01 分区表的概念

　　当Hive表对应的数据量大、文件多时，为了避免查询时全表扫描数据，Hive支持根据用户指定的字段进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。比如把一整年的数据根据月份划分12个月（12个分区），后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。

　　分区表建表语法：

　　需要注意：分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上。

　　分区表的本质：

　　外表上看起来分区表好像没多大变化，只不过多了一个分区字段。实际上在底层管理数据的方式发生了改变。这里直接去HDFS查看区别。

　　非分区表：t_all_hero

　　分区表：t_all_hero_part

　　分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。只需要根据分区值找到对应的文件夹，扫描本分区下的文件即可，避免全表数据扫描。

　　02 分桶表的概念

　　分桶表也叫做桶表，源自建表语法中bucket单词。是一种用于优化查询而设计的表类型。该功能可以让数据分解为若干个部分易于管理。

　　在分桶时，我们要指定根据哪个字段将数据分为几桶（几个部分）。默认规则是：Bucket number = hash_function(bucketing_column) mod num_buckets。

　　可以发现桶编号相同的数据会被分到同一个桶当中。hash_function取决于分桶字段bucketing_column的类型：

　　如果是int类型，hash_function(int) == int;

　　如果是其他类型，比如bigint,string或者复杂数据类型，hash_function比较棘手，将是从该类型派生的某个数字，比如hashcode值。

　　分桶表的语法

　　其中CLUSTERED BY (col_name)表示根据哪个字段进行分；

　　INTO N BUCKETS表示分为几桶（也就是几个部分）。

　　需要注意的是，分桶的字段必须是表中已经存在的字段。

　　——> hive分区表和分桶表

　　03 分区表的使用

　　分区表的使用重点在于：

　　一、建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等；

　　二、查询的时候尽量先使用where进行分区过滤，查询指定分区的数据，避免全表扫描。

　　比如：查询英雄主要定位是射手并且最大生命大于6000的个数。使用分区表查询和使用非分区表进行查询，SQL如下：

　　分区表的注意事项

　　一、分区表不是建表的必要语法规则，是一种优化手段表，可选；

　　二、分区字段不能是表中已有的字段，不能重复；

　　三、分区字段是虚拟字段，其数据并不存储在底层的文件中；

　　四、分区字段值的确定来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）

　　五、Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度

　　04 分桶表的使用好处

　　和非分桶表相比，分桶表的使用好处有以下几点：

　　1、基于分桶字段查询时，减少全表扫描

　　2、JOIN时可以提高MR程序效率，减少笛卡尔积数量

　　对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了分桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

　　3、分桶表数据进行抽样

　　当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。

　　1、未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；

　　2、内部表数据由Hive自身管理，外部表数据由HDFS管理；

　　3、内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）；

　　4、删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；

　　5.对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;）；

　　6.修改外部表想要生效，需要先把外部表转内部表，然后修改，再转外部表。