分区可以很好的解决全盘扫描,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');
删除分区


二、动态分区
数据
1,小明1,10,boy,play-book-movie,henan:zhengzhou-henan:inzheng
2,小明11,10,man,play-book-movie,henan:zhengzhou-henan:inzheng
3,小明21,10,man,play-movie,henan:zhengzhou-henan:inzheng
4,小明31,20,man,play-book-movie,henan:zhengzhou-henan:inzheng
5,小明14,20,boy,play-book,henan:zhengzhou-henan:inzheng
6,小明51,20,boy,play-book-movie,henan:zhengzhou-henan:inzheng
7,小明3,20,boy,play-book-movie,henan:zhengzhou-henan:inzheng
8,小明4,10,boy,play-book,henan:zhengzhou-henan:inzheng
9,小明5,10,boy,play-book-movie,henan:zhengzhou-henan:inzheng
按年龄和性别进行分区
1、创建一个普通表
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';
2、创建一个分区表
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';
3、开启分区
set hive.exec.dynamic,partition=true;
4、设置成非严格模式,默认是严格模式
set hive.exec.dynamic,partition.mode=nostrict;
5、加载数据
from yuan insert overwrite table yuan2 partition(age,sex) select id,name,likes,address,age,sex distribute by age,sex;
6、结果



我给你点赞了
看了那么多博客,就你的能看懂
看了那么多博客,就你的能看懂
不错
看了那么多博客,就你的能看懂
看了那么多博客,就你的能看懂
好