SQL ON Hadoop-Hive（三）-分区表

分区表是将大的表文件划分成多个小文件以利于查询，但是如果数据分布不均也会影响查询效率(桶表可以对数据进行哈希取模，目的是让数据能够均匀分布在表的各个数据文件中，是对分区表的补充)

create table info_student(
student_id string,
name string,
age int,
sex string,
father_name string,
mother_name string
)
partitioned by (province string,city string);

注意定义分区的字段不能和定义表的字段重合,表的分区是以表目录的子目录存在，执行select *查询不指定任何字段，hive同样会将分区字段的数据输出

对于直接命中分区的查询，hive不会执行MapReduce作业

最常见的分区是按照创建时间或者修改时间进行分区，所以一张表中的分区数目都比较多。如果执行一条包含所有分区的查询将耗费集群巨大的时间和资源，对此可以将hive的安全措施设定为strict，设定后如果一个针对分区表的查询没有对分区进行限制的话，该作业会被禁止提交

1.可以修改hive-site.xml文件的hive.mapred.mode配置项为strict   --针对hive的所有会话
2.在hive的命令行SET hive.mapred.mode=strict;  --仅针对本次会话

show partitions student_info  --显示该表分区情况
--查看表中特定分区
show partitions student_info partition(country='beijing')

注：在hive中默认是使用静态分区，其动态分区功能是关闭的，但有时因为业务需求需要动态创建不同分区

--利用动态分区向表中插入数据(在插入分区数量非常多的情况下使用)
--先进行参数设置
set hive.exec.dynamic.partition=true   --开启动态分区功能
set hive.exec.dynamic.partition.mode=nostrict --允许所有分区都是动态的(分区字段可以全为动态指定)hive默认不允许所有分区都是动态

--创建动态分区表,建表语句与静态分区表相同
insert [overwrite|into] table table_name partition(time) select *,modify_time from source

hive会自动根据modify_time的不同值创建分区(依据select最后一个查询字段作为动态分区依据,而不是依据字段名,n个动态分区字段-最后n个select字段作为依据)

动态和静态分区结合,country为静态分区，state为动态分区(静态分区必须在动态分区之前)

insert overwrite table table_name partition(country='US',state)
select …,se.cnty,se.st
from employees se
where se.cnty='US';

--查看表基本的分区情况
show partitions table_name
--查看表结构与partitions information
desc table_name partition(day=20160305)

--查找分区对应的location
desc extended table_name partition(day=20160305)

外部分区表：与管理表一样，外部表也是可以有分区的，与普通外部表建表语句不同的是，在建表时并没有指定表的存储路径，所以在创建完外部分区表后，执行查询语句时查不到数据的，需要单独为外部表的分区键指定值和存储位置

alter table student_info add partition (province=sichuan,city=chengdu)
location 'hdfs://master:9000/student/sichuan/chengdu'

无论是管理表还是外部表，一旦该表存在分区，那么数据在加载时必须加载进指定分区中

load data inpath '/user/hadoop/data' into student_info partition (province='sichuan',city='chengdu');

insert overwrite table tb_stud partition(clus='20171218') select id,name,age from tb_stud where clus='20171211';

防止分区被查询和被删除

alter table table_name partition(year=2018,month=12) disable/enable no_drop;
alter table table_name partition(year=2018,month=12) disable/enable offline;