create table t1(
id int
,name string
,hobby array<string>
,add map<String,string>
)
partitioned by (pt_d string)
row format delimited
fields terminated by ','
collection items terminated by '-'map keys terminated by ':'
;
注:这里分区字段不能和表中的字段重复。
如果分区字段和表中字段相同的话,会报错,如下:
create table t1(
id int
,name string
,hobby array<string>
,add map<String,string>
)
partitioned by (id int)
row format delimited
fields terminated by ','
collection items terminated by '-'map keys terminated by ':'
;
报错信息:FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns
添加分区,增加一个分区文件
alter table t1 add partition (pt_d = ‘333333’);
删除分区(删除相应分区文件) 注意,对于外表进行drop partition并不会删除hdfs上的文件,并且通过msck repair table table_name同步回hdfs上的分区。
alter table test1 drop partition (pt_d = ‘20170101’);
二、多个分区操作
创建分区表
create table t10(
id int
,name string
,hobby array<string>
,add map<String,string>
)
partitioned by (pt_d string,sex string)
row format delimited
fields terminated by ','
collection items terminated by '-'map keys terminated by ':'
;
装载数据(分区字段必须都要加)
load data local inpath ‘/home/hadoop/Desktop/data’ overwrite into table t10 partition ( pt_d = ‘0’);
如果只是添加一个,会报错:FAILED: SemanticException [Error 10006]: Line 1:88 Partition not found ”0”
load data local inpath '/home/hadoop/Desktop/data' overwrite intotable t10 partition ( pt_d = '0',sex='male');load data local inpath '/home/hadoop/Desktop/data' overwrite intotable t10 partition ( pt_d = '0',sex='female');
,观察HDFS上的文件,可发现多个分区具有顺序性,可以理解为windows的树状文件夹结构。
表分区的增删修查
增加分区
这里我们创建一个分区外部表
createexternaltable testljb(id int) partitioned by (age int);