Hive分区（partition）详解

一、背景

1、在Hive查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。

2、分区表指的是在创建表时指定的partition的分区空间。

3、如果需要创建有分区的表，需要在create表的时候调用可选参数partitioned by

二、基础语法

1.创建分区表

分区建表分为2种

（1）单分区，也就是说在表文件夹目录下只有一级文件夹目录。
（2）多分区，表文件夹下出现多文件夹嵌套模式。

a、单分区建表语句：

-- 单分区表，按天分区，在表结构中存在id，content，dt三列。
create table day_table (id int, content string)
partitioned by (dt string);

b、双分区建表语句：

--双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。
create table day_hour_table (id int, content string)
partitioned by (dt string, hour string);

【注意】：
需要注意的是分区字段不能和表中的字段重复，否则就会报错：

FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns

2.查看分区信息

格式：show partitions table_name;

3.加载数据（load、insert）

（1）load 方式

-- 加载本地文件
LOAD DATA local INPATH '/user/hua/*' INTO TABLE day_table
partition(dt='2010-07- 07');
--加载HDFS文件
LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table 
PARTITION(dt='2008-08- 08', hour='08');

当数据被加载至表中时，不会对数据进行任何转换。

（2）insert 方式：动态分区

-- 分区插入
insert overwrite table day_table partition(dt='2010-07- 07')
select * from tb_select1 ;

-- 动态分区插入(先设置非严格模式)
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table tb_dy_part partition(sex，name) 
select * from tb_select1;

【注意】
动态分区与静态分区区别就是不指定分区目录，由系统自己选择。
但是动态分区往往需要先开启，并指定非严格模式

-- 开启动态分区（默认开启的话可以不执行此命令）
set hive.exec.dynamic.partition=true;
--使用非严格模式
set hive.exec.dynamic.partition.mode=nostrick;

4.添加分区（表已创建，在此基础上添加分区）

用户可以用 ALTER TABLE ADD PARTITION 来向一个表中增加分区。当分区名是字符串时加引号
格式：

ALTER TABLE table_name ADD partition_spec
[ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ... 
partition_spec: : 
PARTITION (partition_col = partition_col_value,
 partition_col = partiton_col_value, ...)

举例：

ALTER TABLE day_table 
ADD PARTITION (dt='2008-08-08', hour='08') location '/path/pv1.txt' 
PARTITION (dt='2008-08-08', hour='09') location '/path/pv2.txt';

5.删除分区

用户可以用 ALTER TABLE table_name DROP PARTITION 来删除分区。
分区的元数据和数据将被一并删除

ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

【注意】：
对于外表进行drop partition并不会删除hdfs上的文件，并且通过msck repair table table_name同步回hdfs上的分区。

6.查询

SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';

7.修复分区

修复分区就是重新同步hdfs上的分区信息。

msck repair table table_name;

二、筛选分区表

我们知道show partitions table_name;语句可以查询分区表中的所有分区。
现在换种思路，我想要筛选出hive中是分区表的表名，如何操作？
这里分两步：

1.shell脚本

# hive 数据库名字
DB=dim

mkdir dim.db
#获取hive建表语句
tables=$(hive -e "use $DB; show tables;")

#生成建表文件（每个表单独一个文件）
for table in $tables;
do
echo "$(hive -e "use $DB;show create table $table;");" >> ./dim.db/$table.hql
done

2.awk命令

// 筛选出带partition的文件名，然后用 .隔开
grep -i partition * | awk -F '.' '{print $1}'

注意：
如果不进入目录而采用./* 需要执行如下指令

grep -i partition ./* 
grep -i partition ./* | awk -F '.' '{print $2}' | awk -F '/' '{print $2}'

三、分区原理

1、一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。

可以这样理解，当插入数据的时候指定分区，其实就是新建一个目录或者子目录，或者在原有的目录上添加数据文件。

2、Hive分区又分为单值分区、范围分区。单值分区又分为静态分区和动态分区

详情可以参考博文《静态分区和动态分区》

3、 msck repair修复分区常用于distcp数据之后修复表分区

详情参考《修复分区》

4、建表的完整语法（建分区可参见PARTITIONED BY参数）：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...)
 [SORTED BY (col_name [ASC|DESC], ...)] 
INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] [STORED AS file_format]
[LOCATION hdfs_path]

四、总结

1、在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在最字集的目录中。

2、总的说来partition就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。

hive中关于partition的操作：
hive> create table mp (a string) partitioned by (b string, c string);
OK
Time taken: 0.044 seconds
hive> alter table mp add partition (b='1', c='1');
OK
Time taken: 0.079 seconds
hive> alter table mp add partition (b='1', c='2');
OK
Time taken: 0.052 seconds
hive> alter table mp add partition (b='2', c='2');
OK
Time taken: 0.056 seconds
hive> show partitions mp ;
OK
b=1/c=1
b=1/c=2
b=2/c=2
Time taken: 0.046 seconds
hive> explain extended alter table mp drop partition (b='1');
OK
ABSTRACT SYNTAX TREE:
  (TOK_ALTERTABLE_DROPPARTS mp (TOK_PARTSPEC (TOK_PARTVAL b '1')))

STAGE DEPENDENCIES:
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-0
      Drop Table Operator:
        Drop Table
          table: mp


Time taken: 0.048 seconds
hive> show partitions mp ;
OK
b=1/c=1
b=1/c=2
b=2/c=2
Time taken: 0.044 seconds
hive> alter table mp add   partition ( b='1', c = '3') partition ( b='1' , c='4');
OK
Time taken: 0.168 seconds
hive> show partitions mp ;
OK
b=1/c=1
b=1/c=2
b=1/c=3
b=1/c=4
b=2/c=2
b=2/c=3
Time taken: 0.066 seconds
hive>insert overwrite table mp partition (b='1', c='1') select cnt from tmp_et3 ;

hive>alter table mp add columns (newcol string);

 

location指定目录结构
hive> alter table alter2 
add partition (insertdate='2008-01-01') location '2008/01/01';

hive> alter table alter2 
add partition (insertdate='2008-01-02') location '2008/01/02';