第9周 · 分区表和分桶表

分区表和分桶表

1.1分区

1.1.1 创建分区表

Hive创建partition_temperature表时，可以使用PARTITIONED BY子句来定义分区，并使用year字段作为分区列，具体操作如图1-1所示。

create table if not exists partition_temperature

(id string,temperature int)

PARTITIONED BY (year string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

STORED AS TEXTFILE ;

📷 图1-1创建分区表

1.1.2 加载数据

使用insert子句将temperature表中的数据加载到partition_temperature分区表中，并以year字段作为分区列动态创建分区，具体操作如图1-2所示。

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

set hive.exec.max.dynamic.partitions.pernode=10000;

set hive.exec.max.dynamic.partitions=10000;

set hive.exec.max.created.files=10000;

INSERT OVERWRITE TABLE partition_temperature PARTITION(year) SELECT id,temperature,year FROM temperature ;

📷 图1-2动态加载数据至分区表

1.1.3 查看分区

可以使用show partitions命令查看Hive表中有哪些分区，partition_temperature表的分区结果如图1-3所示。

show partitions partition_temperature;

📷 图1-3查看表分区

1.1.4 查询分区数据

在select语句中使用where限定分区列year的值，这样Hive会对查询结果进行修剪，从而只扫描相关的分区，具体操作如图1-4所示。

select id,temperature from partition_temperature where year='2011'

📷 图1-4查询限定分区数据

对于partition_temperature表限定到特定日期的查询，Hive的数据处理会变得非常高效，因为它们只需要扫描查询year=’2011’分区中的文件。

1.1.分桶

1.1..1 创建分桶表

Hive创建bucket_temperature表时，可以使用clustered by子句来指定划分桶所用的列和要划分的桶的个数，具体操作如图1-5所示。

create table bucket_temperature(id string,year string,temperature int)

clustered by(id)

into 3 buckets;

📷 图1-5创建分桶表

在这里，我们使用气象站id来确定如何划分桶（Hive对id值进行哈希并将结果除以桶的个数取余数），这样任何一个桶里都会有一个随机的天气集合。

1.1..2 加载数据

使用insert子句将temperature表中的数据加载到bucket_temperature分桶表中，具体操作如图1-6所示。

insert overwrite table bucket_temperature select * from temperature;

📷 图1-6加载数据至分桶表

1.1..3 查看分桶文件

物理上，每个桶就是表目录里面的一个文件。事实上，桶对应于MapReduce的输出文件分区，一个作业产生的桶和reduce任务个数相同。通过dfs命令查看bucket_temperature表目录下面的桶文件，具体操作如图1-7所示。

dfs -ls /user/hive/warehouse/mydb/bucket_temperature;

📷 图1-7查看桶文件

1.1..4查看分桶数据

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。使用TABLESAMPLE子句对bucket_temperature表进行抽样来满足这个需求，具体操作如图1-8所示。

select * from bucket_temperature TABLESAMPLE(BUCKET 1 OUT OF 3 on id);

📷 图1-8桶抽样

图中语句表示从3个桶中的第一个桶中获取所有的天气记录，因为查询只需要读取和TABLESAMPLE子句匹配的桶，所以取样分桶表是非常高效的操作。

II