hive 建表分区语句（仅供参考）

Myth丶恋晨 2022-05-16 02:19 570阅读 0赞

create table test\_01(  
name string,  
friends array<string>,  
children map<string,int>,  
address struct<street:string,city:string>)  
row format delimited fields terminated by ','  
collection items terminated by '\_'  
map keys terminated by ':'  
lines terminated by '\\n';  
  
  
create table student01(id int ,name string)  
> row format delimited fields terminated by ','  
> stored as textfile location '/user/hive/warehouse/';  
  
  
create table if not exists student2 (id int ,name string)  
row format delimited fields terminated by ',' stored as textfile  
location '/user/hive/warehouse/student2';  
创建外部表：  
create external table dept (deptno int , dname string,loc string) row format delimited fields terminated by '\\t';

load data local inpath '/home/hadoop/data/dept.txt' into table dept;

create external table if not exists default.emp(  
empno int ,  
ename string,  
job string ,  
mgr int,  
hiredate string,  
sal double,  
comm double,  
deptno int)  
row format delimited fields terminated by '\\t';

load data local inpath '/home/hadoop/data/emp.txt' into table emp;

创建分区：  
create table dept\_partition(deptno int , dname string,loc string)  
partitioned by (month string)  
row format delimited fields terminated by '\\t';

load data local inpath '/home/hadoop/data/dept.txt' into table default.dept\_partition partition(month='201709');  
load data local inpath '/home/hadoop/data/dept.txt' into table default.dept\_partition partition(month='201708');  
load data local inpath '/home/hadoop/data/dept.txt' into table default.dept\_partition partition(month='201710');

单分区查询  
select \* from dept\_partition where month='201709';  
多分区的联合查询  
select \* from dept\_partition where month='201709'  
union  
select \* from dept\_partition where month='201708'  
union  
select \* from dept\_partition where month='201707';

添加单个分区：  
alter table dept\_partition add partition(month='201706');

添加多个分区：  
alter table dept\_partition add partition(month='201705') partition(month='201704');

删除单个分区：  
alter table dept\_partition drop partition(month='201704');

删除多个分区：  
alter table dept\_partition drop partition(month='201705'), partition(month='201706');

查看有多少分区：  
show partitions dept\_partition;

创建二级分区表：  
create table dept\_partition2(deptno int , dname string,loc string)  
partitioned by (month string,day string)  
row format delimited fields terminated by '\\t';

load data local inpath '/home/hadoop/data/dept.txt' into table default.dept\_partition2 partition(month='201710',day='10');

select \* from dept\_partition2 where month='201709' and day='13';