drop table if exists user7; drop table if exists user7_0; drop table if exists user7_1; create table user7 (name string ,age int)partitioned by (sex string) stored as orc; set hive.exec.dynamic.partition=true; insert into table user7 partition(sex) select 'zs',1,'girl' from system.dual; create table user7_0 (name string ,age int ,sex string)stored as orc; load data inpath '/inceptorsql1/user/hive/warehouse/default.db/hive/user7/sex=girl' into table user7_0; insert into table user7 partition(sex) select 'zs',1,'girl' from system.dual; create table user7_1 (name string ,age int) stored as orc;
load data inpath '/inceptorsql1/user/hive/warehouse/default.db/hive/user7/sex=girl' into table user7_1;
select * from user7_0; select * from user7_1;
这个实验,演示了分区表和非分区表的关系。值分区表的orc文件不保存分区列的值。
下面试着演示范围分区:
create table user9(name string) partitioned by range (age int) ( PARTITION p5_105_205 VALUES LESS THAN (5), PARTITION p5_105_215 VALUES LESS THAN (10), PARTITION p5_115_205 VALUES LESS THAN (20), PARTITION p5_115_max VALUES LESS THAN (MAXVALUE) )stored as orc;
insert into user9 select name,age from user4; dfs -ls /inceptorsql1/user/hive/warehouse/default.db/hive/user9/p5_105_215; create table user9_0(name string,age int) stored as orc; load data inpath '/inceptorsql1/user/hive/warehouse/default.db/hive/user9/p5_105_215/000000_0' into table user9_0;
用dfs -ls 找出有数据的目录,然后将数据load到对应非分区表user9_0中。
结果如下,说明范围分区表中的orc文件有和对应非分区表相同数量的列。