一、普通分区表基本操作
1、建立分区表:
create table part_tbl(
id string)
partitioned by (dt string)
row format delimited fields terminated by "\t";
2、给分区表添加分区:
alter table part_tbl add partition (dt = 20201010);
3、从本地加载数据的时候添加分区:
load data local inpath "/data/data.txt" into table part_tbl partition (dt = 20200303);
4、删除分区表:
- 分区删除之后,分区数据也会被删除
alter table scoretest drop partition (statdate="202106");
二、动态分区表使用
需求:
vim scoretest.txt
# name subject score statdate(统计时间)
Lily math 90 20210615
Lily chinese 89 20210612
Lily english 75 20210616
Micky math 78 20210615
Mary math 81 20210615
创建一个自己使用的外部表,将scoretest.txt数据导入到新建的表中,并且根据统计日期按月(如202106)分区动态插入。
解决:
- 创建分区表
create external table scoretest(
name string comment "姓名",
subject string comment "科目",
score bigint comment "分数",
statdate string
)partitioned by (dt string) row format delimited fields terminated by "\t";
- 创建临时表
create table tmp(
name string,
subject string,
score bigint,
statdate string
)row format delimited fields terminated by "\t";
- 临时表加载数据
load data local inpath "/data/test/scoretest.txt" overwrite into table tmp;
- 开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict; #开启允许所有分区都是动态的,否则必须要有静态分区才能使用。
- 向动态分区中插入数据
insert overwrite table scoretest partition (dt) select name,subject,score,statdate,substr(statdate,0,6) from tmp;