hive数据表创建

目录

分隔符

分区表 

 二级分区

分桶表

外部表

---

分隔符

CREATE TABLE emp(
userid bigint,
emp_name array<string>,
emp_date map<string,date>,
other_info struct<deptname:string, gender:string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

FIELDS TERMINATED BY '\t'                             --字段之间的分隔符为制表符（'\t'）

COLLECTION ITEMS TERMINATED BY ','         --集合项之间的分隔符为逗号（','）

MAP KEYS TERMINATED BY ':'                         --MAP中每个键值对由冒号分隔

如：birth date:1953-11-07,from date:1990-01-22

---

分区表 

create table dept_partition(  
deptno int, dname string, loc string) 
partitioned by (day string) 
row format delimited fields terminated by '\t'; 

增加分区

alter table dept_partition add partition(day='20200404');alter table dept_partition add partition(day='20200405') partition(day='20200406'); 

删除分区

alter table dept_partition drop partition (day='20200406'); alter table dept_partition drop partition (day='20200404'), partition(day='20200405');  

删除partition内的部分信息(INSERT OVERWRITE TABLE）

分区字段不能出现在查询字段中，即不能用select *

INSERT OVERWRITE TABLE emp_partition partition(dept_name='Finance')
SELECT 
emp_no,
first_name,
last_name,
gender,
birth_date,
from_date
FROM emp_partition
WHERE dept_name='Finance' and gender = "F";

 二级分区

create table dept_partition2(
deptno int, dname string, loc string ) 
partitioned by (day string, hour string)
row format delimited fields terminated by '\t'; 

分桶表

create table emp_bucket (
emp_no bigint,
first_name string,
last_name string,
gender string,
birth_date date,
from_date date,
dept_name string
)
clustered by(gender) into 2 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

clustered by(gender) 按照gender来分桶

外部表

--有自动创建文件夹功能
create external table emp_external(
emp_no bigint,
first_name string,
last_name string,
gender string,
birth_date date,
from_date date,
dept_name string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
location '/user/admin/external_table/emp_external';

外部表和内部表的区别