hive静态分区与动态分区

2020-02-21 141次浏览 已收录 7个评论

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

分区字段不能在表的列中

hdfs

创建多个分区

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分区

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

删除分区

age=10
age=20

二、动态分区

数据

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、结果

分区目录

渣渣龙, 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:hive静态分区与动态分区
喜欢 (0)

您必须 登录 才能发表评论!

(7)个小伙伴在吐槽
  1. 我给你点赞了
    渣渣辉2020-03-27 12:49
  2. 看了那么多博客,就你的能看懂
    中国加油小子2020-03-27 13:40
  3. 看了那么多博客,就你的能看懂
    奋斗2020-03-27 14:06
  4. 不错
    小白2020-03-28 16:29
  5. 看了那么多博客,就你的能看懂
    渣渣辉2020-03-28 17:22
  6. 看了那么多博客,就你的能看懂
    小蚯蚓2020-05-26 09:51
  7. 笔记本2020-05-26 10:41