tdh inceptor表的分区分桶和hdfs目录的关系，partition,bucket

create table user_ext(name string ,age int,sex string) clustered by (sex) into 2 buckets  row format delimited fields terminated by ',' location '/tmp/tableuser';

create table user(name string ,age int,sex string)  comment 'input text table ' row format delimited fields terminated by ',';

create table user2(name string ,age int  )  comment '2 buck by age' partitioned by (sex string) clustered by (age) into 2 buckets stored as orc;

create table user3( name string,age int) comment '3 buck age' partitioned by (sex string) clustered by(age) into 3 buckets stored as orc ; 

set hive.exec.dynamic.partition=true;

insert into user2 partition(sex) select 'zs',17,'girl' from system.dual;

dfs -ls /inceptorsql1/user/hive/warehouse/default.db/hive/user2;

insert into user2 partition(sex) select 'zs',17,'boy' from system.dual;

 此时可见user2目录下有partition=girl和partition=boy两个子目录。

dfs -ls /inceptorsql1/user/hive/warehouse/default.db/hive/user2/sex=girl;

此时可见sex=girl目录下有两个文件000000_0，000001_0 因为分为两个桶。根据hash规则，age=17，17%2=1,则数据在000001_0的文件中。删掉此文件。

dfs -rm /inceptorsql1/user/hive/warehouse/default.db/hive/user2/sex=girl/000001_0;
select * from user2;

可见user2变空了，没有了数据。再插入数据到000000_0文件中。

insert into user2 partition(sex) select 'a',18,'girl' from system.dual;
select * from user2;

可见sex=girl目录下有了000000_0，000000_0_copy_1,000001_0三个文件。删除000000_0后仍然可以查看到a,18,girl的数据。删除000000_0_copy_1后表user2变为空表。

 

通过以上实验可以看出，分区且分桶的表，其表目录下包括下级目录，文件数量大于桶数*分区数。即使桶中数据为空，桶文件仍然存在。