hive 静态分区表

use test03

drop table my_partitioner1
drop table my_partitioner3

//一级分区的静态分区表
create external table my_partitioner1(
id bigint,
name string,
time date,
love string)
partitioned by(sex string)
row format delimited fields terminated by '\t'

//二级分区的静态分区表
create external table my_partitioner3(
id bigint,
name string,
time date,
interest array<string>)
partitioned by(sex string,country string)
row format delimited fields terminated by '\t'
collection items terminated by ','

//根据表结构创建表
create external table my_partitioner2 like my_partitioner3

//由本地文件导入数据到表
load data local inpath '/data/log/fenqu3.txt' into table my_partitioner1 partition(sex='woman')
load data local inpath '/data/log/fenqu4.txt' into table my_partitioner3 partition(sex='woman',country='usa')

//由子查询导入,需要指定分区 ，追加模式
insert into my_partitioner2 partition(sex='man',country='usa') 
select id,name,time,interest from my_partitioner3 where sex='woman' and country='usa'

//由子查询导入,需要指定分区 ，覆盖模式
insert overwrite table my_partitioner2 partition(sex='man',country='usa') 
select id,name,time,interest from my_partitioner3 where sex='woman' and country='usa'

//查询分区表的数据，注意：只显示有分区的数据，只有分区，但分区里没数据，则不显示
select * from my_partitioner2

//查询某个分区的数据
select * from my_partitioner3 where sex='woman'
select * from my_partitioner3 where sex='woman' and country='china'

//添加分区，只能在当前级数下进行分区，数目必须和其一致，HDFS目录会增加
alter table my_partitioner2 add partition (sex='woman',country='china')
alter table my_partitioner1 add partition (sex='man')

//删除分区，
注意：只是改变 show partitions 的结果，并不会真实删除数据，hdfs的目录也不会有改变，当然也查询不出这个分区的数据了，如果又再添加已经删除的分区，select也会再查询出这个分区的数据了
alter table my_partitioner2 drop partition(sex='woman',country='usa')

//显示表有哪些分区
show partitions my_partitioner1

分区表在hdfs中的表现