hive union all hive union all 分区

分区&分桶

分区

为什么有分区？

随着系统运行时间增长，表的数据量越来越大，而hive查询时通常是是全表扫描，这样将导致大量的不必要的数据扫描，从而大大减低查询效率。

从而引进分区技术，使用分区技术，避免hive全表扫描，提升查询效率。

可以将用户的整个表的数据在存储时划分到多个子目录，从而在查询时可以指定查询条件（子目录以分区变量的值来命名）eg:year=‘2018’。

怎么分区？

根据业务，通常按照年、月、日、地区等

分区的技术？

PARTIONED BY(col_name data_type)
hive的分区字段使用的是表外字段。而mysql使用的是表内字段。
1、hive的分区名区分大小写
2、hive的分区本质是在表目录下面创建目录，但是该分区字段是一个伪列，不真实存在于数据中
3、一张表可以有一个或者多个分区，分区下面也可以有一个或者多个分区

分区的意义？

可以让用户在做数据分析统计时缩小数据扫描的范围，因为可以在select时指定要统计的分区，提高查询效率。

创建分区表

一级：

create table if not exists part1(
uid int,
uname string,
uage int
)
PARTITIONED BY (country string)
row format delimited 
fields terminated by ','
;

分区表导入数据的方式：

load data local inpath '/usr/local/xxx' into table part1
partition(country='China'); #要指定分区

select * from part1 where country='China';

二级：

create table if not exists part2(
uid int,
uname string,
uage int
)
PARTITIONED BY (year string,month string)
row format delimited 
fields terminated by ','
;


load data local inpath '/usr/local/xxx' into table part1
partition(year='2018',month='09'); 

load data local inpath '/usr/local/xxx' into table part1
partition(year='2018',month=09); 

select * from part2 where year='2018',month='09';

三级：

create table if not exists part3(
uid int,
uname string,
uage int
)
PARTITIONED BY (year string,month string,day string)
row format delimited 
fields terminated by ','
;


load data local inpath '/usr/local/xxx' into table part3
partition(year='2018',month='09',day='29'); 

load data local inpath '/usr/local/xxx' into table part3
partition(year='2018',month=09,day='29'); 

select * from part3 where year='2018',month='09',day='29';

显示分区：

show partitions part3;

修改分区：
分区名不能使用命令修改（手动修改），文件名+元数据
增加分区：

alter table part1 add partition(country='india');

增加多个分区：

alter table part1 add partition(country='india') partition(country='korea') partition(country='America');

增加分区并设置数据：

alter table part1 add partition(country='Vietnam')
location '/user/hive/warehouse/xxx'
;

增加分区并设置数据：
location 跟在 partition之后，空格隔开

修改分区的存储路径：(hdfs的路径必须是全路径)

alter table part1 partition(country='Vietnam') set location ‘hdfs://hadoop01:9000/user/hive/warehouse/brz.db/part1/country=Vietnam’

删除分区：

alter table part1 drop partition(country='india');

alter table part1 drop partition(country='india'),partition(country='korea'),partition(country='America'); ##逗号隔开

分区类型：
1、静态分区:加载数据到指定的分区值
2、动态分区：数据未知，根据分区的值确定创建分区
3、混合分区：静态和动态都有

动态分区的属性：
set hive.exec.dynamic.partition=true;//(true/false)
set hive.exec.dynamic.partition.mode=strict;//(strict/nonstrict) 至少有一个静态的值
set hive.exec.dynamic.partitions=1000;//(分区最大数)
set hive.exec.max.dynamic.partitions.pernode=100

创建动态分区：

create table if not exists dt_part1(
uid int,
uname string,
uage int
)
PARTITIONED BY (year string,month string)
row format delimited 
fields terminated by ','
;

加载数据：（不能使用load加载数据）

load data local inpath '/usr/local/xxx' into table part1
partition(year,month);

加载数据：（使用 insert into方式加载数据）

1、创建临时表

create table if not exists part_tmp(
uid int,
uname string,
uage int,
year string,
month string
)
row format delimited 
fields terminated by ','
;

2、insert into方式加载数据

insert into dy_part1 partition(year,month)
select * from part_tmp
;

混合：(注意列的个数的匹配)

create table if not exists dy_part2(
uid int,
uname string,
uage int
)
PARTITIONED BY (year string,month string)
row format delimited 
fields terminated by ','
;

insert into dy_part2 partition(year='2018',month)
select uid,uname,uage,month from part_tmp
;

###设置hive的执行模式：
1、严格模式：

set hive.mapred.mode=nonstrict/strict

In strict mode, some risky queries are not allowed to run. They include:
Cartesian Product.
No partition being picked up for a query.
Comparing bigints and strings.
Comparing bigints and doubles.
Orderby without limit.

注意事项：
1、hive的分区使用的表外字段，分区字段是一个伪列但是可以查询过滤。
2、分区字段不建议使用中文
3、不太建议使用动态分区。因为动态分区将会使用mapreduce来查询数据，如果分区数量过多将导致namenode和yarn的资源瓶颈。所以建议动态分区前也尽可能之前预知分区数量。
4、分区属性的修改均可以使用手动元数据和hdfs的数据内容

分桶

为什么要分桶？

单个分区或者表中的数据量越来越大，当分区不能更细粒的划分数据时，所以会采用分桶技术将数据更细粒度的划分和管理。

分桶技术

[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
关键字：BUCKET

分桶的意义 ###：

1、为了保存分桶查询结果的分桶结构（数据已经按照分桶字段进行了hash散列）
2、分桶表数据进行抽样和JOIN时可以提高MR程序效率

分桶表的使用 ###：

1、创建一个带分桶定义的表（分桶表）

create table if not exists buc1(
uid int,
uname string,
uage int
)
clustered by (uid) into 4 buckets
row format delimited
fields terminated by ','
;

加载数据：

load data local inpath '/usr/local/hive/test/3.txt' into table buc1
;

设置强制分桶属性：

set hive.enforce.bucketing=false/true

如果reduce的个数和分桶的个数不一致时，请手动设置reduce的个数：
设置reduce task的个数：

set mapreduce.job.reduces=4;

分桶查询测试：

select * from buc1
cluster by (uid)
;

创建临时表：

create table if not exists buc_temp(
uid int,
uname string,
uage int
)
row format delimited
fields terminated by ','
;

加载数据：

load data local inpath '/usr/local/hivedata/3.txt' into table buc_temp
;

创建分桶表：

create table if not exists buc2(
uid int,
uname string,
uage int
)
clustered by (uid) into 4 buckets
row format delimited
fields terminated by ','
;

分桶表数据需要使用insert into 方式来加载：

insert overwrite table buc2
select uid,uname,uage from buc_temp
cluster by (uid)
;

创建分桶表，并且指定排序字段及排序规则

create table if not exists buc3(
uid int,
uname string,
uage int
)
clustered by (uid) 
sorted by (uid desc) into 4 buckets
row format delimited
fields terminated by ','
;

导入数据：

insert overwrite table buc3
select uid,uname,uage from buc_temp
cluster by (uid)
;

insert overwrite table buc3
select uid,uname,uage from buc_temp
distribute by (uid) sort by (uid asc)
;

insert overwrite table buc3
select uid,uname,uage from buc_temp
distribute by (uid) sort by (uid desc)
;

insert overwrite table buc3
select uid,uname,uage from buc_temp
distribute by (uid) sort by (uage desc)
;

对分桶表的查询 ：

1、查询全部：

select * from buc3;
select * from buc3 tablesample(bucket 1 out of 1)

查询第几桶：

select * from buc3 tablesample(bucket 1 out of 4 on uid);                            //除4余0
select * from buc3 tablesample(bucket 1 out of 2 on uid);

解释一下：

tablesample(bucket x out of y on uid)  
x：代表从第几桶开始查询
y：查询的总桶数，y可以是总的桶数的倍数或者因子；x不能大于y

不压缩不拉伸：1 out of 4
for 1 to 4
1 2 3 4 1 2 3 4
1       1 + 4

压缩： 1 out of 2
1 2 3 4
1 2 1 2 1 2
1   1 + 4/2 1+4/2+4/2

拉伸：1 out of 8
1 2 3 4 5 6 7 8
1 2 3 4

等于是重新对所有数据进行分桶：

例子：
查询uid为奇数：（tablesample一定要紧跟在表名之后）

select * from buc3 tablesample(bucket 2 out of 2 on uid)

查询：

select * from part_tmp limit 3;
select * from part_tmp tablesample(3 rows);
select * from part_tmp tablesample(13 percent);
select * from part_tmp tablesample(56B); ###k B M G T P

随机查询3条数据：

select * from part_tmp order by rand() limit 3;
select rand() as rand1,* from part_tmp;

分桶总结：

1、定义：
clustered by (uid) – 指定分桶的字段
sorted by (uid desc) – 指定数据的排序规则，表示预期的数据就是以这里设置的字段以及排序规则来进行存储

2、导数据
cluster by (uid) – 指定getPartition以哪个字段来进行hash散列，并且排序字段也是指定的字段，默认以正序进行排序

distribute by(uid) – 指定getPartition以哪个字段来进行hash散列

sort by(uid asc) – 指定排序字段，以及排序规则
–更灵活的方式，这种数据获取方式可以分别指定getPartition的字段和sort的字段

cluster by (uid)与distribute by(uid) sort by (uid asc)结果是一样的

分区下的分桶：

举例说明一下：按照性别进行分区（1男2女），在分区中按照uid的奇偶进行分桶：

1 gyy1 1
2 gyy2 2
3 gyy3 2
4 gyy4 1
5 gyy5 2
6 gyy6 1
7 gyy7 1
8 gyy8 2
9 gyy9 1
10 gyy10 1
11 gyy11 2
12 gyy12 1

1、创建临时表：

create table if not exists stu_temp(
uid int,
uname string,
usex int
)
row format delimited 
fields terminated by ' '
;

加载数据：

load data local inpath '/usr/local/hivedata/stu.dat' into table stu_temp;

创建分区分桶表：

create table if not exists stus(
uid int,
uname string
)
partitioned by (sex int)
clustered by (uid) into 2 buckets
row format delimited 
fields terminated by ' '
;

//insert into方式：

insert into table stus partition(sex)
select uid,uname,usex from stu_temp
cluster by (uid)
;

需求：
查询性别为女性的、并且学号为奇数的学生：

select *
from stus tablesample(bucket 2 out of 2 on uid)
where sex=2;

注意：
1、分区使用的是表外字段，分桶使用的是表内字段
2、分桶是更细粒度的划分、管理数据，更多用来做数据抽样、JOIN操作