Hive 分桶表

Hive 分桶表

分桶表的概念

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

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

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

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

分桶表的语法

--分桶表建表语句
CREATE [EXTERNAL] TABLE [db_name.]table_name
[(col_name data_type, ...)]
CLUSTERED BY (col_name)
INTO N BUCKETS;

• CLUSTERED BY (col_name) 表示根据哪个字段进行分；
• INTO N BUCKETS 表示分为几桶（也就是几个部分）。

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

分桶表的创建

现有美国 2021-1-28 号，各个县 county 的新冠疫情累计案例信息，包括确诊病例和死亡病例，数据格式如下所示：

2021-01-28,Juneau City and Borough,Alaska,02110,1108,3
2021-01-28,Kenai Peninsula Borough,Alaska,02122,3866,18
2021-01-28,Ketchikan Gateway Borough,Alaska,02130,272,1
2021-01-28,Kodiak Island Borough,Alaska,02150,1021,5
2021-01-28,Kusilvak Census Area,Alaska,02158,1099,3
2021-01-28,Lake and Peninsula Borough,Alaska,02164,5,0
2021-01-28,Matanuska-Susitna Borough,Alaska,02170,7406,27
2021-01-28,Nome Census Area,Alaska,02180,307,0
2021-01-28,North Slope Borough,Alaska,02185,973,3
2021-01-28,Northwest Arctic Borough,Alaska,02188,567,1
2021-01-28,Petersburg Borough,Alaska,02195,43,0

字段含义如下：count_date（统计日期）,county（县）,state（州）,fips（县编码code）,cases（累计确诊病例）,deaths（累计死亡病例）。

根据 state 州把数据分为 5 桶，建表语句如下：

CREATE TABLE itcast.t_usa_covid19(
    count_date string,
    county string,
    state string,
    fips int,
    cases int,
    deaths int)
CLUSTERED BY(state) INTO 5 BUCKETS;

在创建分桶表时，还可以指定分桶内的数据排序规则

-- 根据state州分为5桶 每个桶内根据cases确诊病例数倒序排序
CREATE TABLE itcast.t_usa_covid19_bucket_sort(
      count_date string,
      county string,
      state string,
      fips int,
      cases int,
      deaths int)
CLUSTERED BY(state) sorted by (cases desc) INTO 5 BUCKETS;

分桶表的数据加载

--step1:开启分桶的功能 从Hive2.0开始不再需要设置
set hive.enforce.bucketing=true;

--step2:把源数据加载到普通 Hive 表中
CREATE TABLE itcast.t_usa_covid19(
       count_date string,
       county string,
       state string,
       fips int,
       cases int,
       deaths int)
row format delimited fields terminated by ",";

--将源数据上传到HDFS，t_usa_covid19表对应的路径下
hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/itcast.db/t_usa_covid19

--step3:使用insert+select语法将数据加载到分桶表中
insert into t_usa_covid19_bucket select * from t_usa_covid19;

并且从结果可以发现，只要 hash_function(bucketing_column) 一样的，就一定被分到同一个桶中。

分桶表的使用好处

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

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

   --基于分桶字段state查询来自于New York州的数据
   --不再需要进行全表扫描过滤
   --根据分桶的规则hash_function(New York) mod 5计算出分桶编号
   --查询指定分桶里面的数据 就可以找出结果  此时是分桶扫描而不是全表扫描
   select * from t_usa_covid19_bucket where state="New York";

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

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

3. 分桶表数据进行抽样

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