分区可以很好的解决全盘扫描，mysql里的where子句也可以得到同样的结果，但是一个是部分扫描，一个是全盘扫描。

一、静态分区

创建表时指定分区

create table pname3(
    id int,
    name string,
    likes array<string>,
    address map<string,string>
)
partitioned by (age int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';

往分区表中加载数据

load data local inpath '/root/input/f' into table pname3 partition (age=10);

分区字段不能在表的列中

创建多个分区

create table pname4(
    id int,
    name string,
    likes array<string>,
    address map<string,string>
)
partitioned by (age int,sex string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';

load data local inpath '/root/input/f' into table pname4 partition (age=10,sex='boy');

• 添加分区的时候，必须在现有分区的基础上
• 删除分区的时候，会将所有存在的分区全不删除

alter table pname5 add partition (age=10,sex='man');

在pname5上添加一个man分区

alter table pname5 drop partition (sex='man');

删除分区

二、动态分区

数据

create table yuan(
    id int,
    name string,
    age int,
    sex string,
    likes array<string>,
    address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';

create table yuan2(
    id int,
    name string,
    likes array<string>,
    address map<string,string>
)
partitioned by(age int,sex string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';

from yuan
insert overwrite table yuan2 partition(age,sex)
select id,name,likes,address,age,sex distribute by age,sex;

6、结果

分享到：

赞(0) 打赏