Hive(二)

Hive_Day02

---

hive分区的作用

• hive数据库:HDFS上的一个文件夹(bigdata25)
• hive数据库种的一张表:HDFS上的文件夹中的子文件夹(students)
• hive表中的数据:HDFS上的子文件夹中的文件(students.txt)
• 随着时间的推移，表中的数据量会越来越大，导致HDFS上表文件夹中的文件就会越来越多。最终影响到hivesql执行的效率以及时间等等假如学生信息是以班级进行分类的，那我们可以根据班级再细分文件夹，将每个班级作为子文件夹，每个学生信息放在对应子文件夹中的文件中
• hive提供的这个技术叫做分区

分区的作用: 避免了全表扫描，加快查询速度

---

静态分区和动态分区

• 静态分区（SP）static partition

  创建单分区表语法：

  ​ create table 表名（字段1，字段2，…，字段n）partition by (字段 类型) row format delimited fields terminated by ‘,’;

  导入数据时候的语法格式：

  ​ load data (local) inpath ‘数据所在文件路径’ (overwrite) into table 表名 partition(字段名=数字);

  ​ 借助于物理的文件夹分区，实现快速检索的目的。

  ​ 一般对于查询比较频繁的列设置为分区列。

  ​ 分区查询的时候直接把对应分区中所有数据放到对应的文件夹中。

要注意的是，文件所存放的分区在哪个分区，查询结果就是哪个分区的，与文件的名字无关

CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int)
row format delimited fields terminated by ',';
--  分区的字段不要和表的字段相同。相同会报错error10035


1,xiaohu01,1
2,xiaohu02,1
3,xiaohu03,1
4,xiaohu04,1
5,xiaohu05,1

6,xiaohu06,2
7,xiaohu07,2
8,xiaohu08,2

9,xiaohu09,3
10,xiaohu10,3
11,xiaohu11,3
12,xiaohu12,3
13,xiaohu13,3
14,xiaohu14,3
15,xiaohu15,3

16,xiaohu16,4
17,xiaohu17,4
18,xiaohu18,4
19,xiaohu19,4
20,xiaohu20,4
21,xiaohu21,4
-- 载入数据
-- 将相应年级一次导入
load data local inpath '/usr/local/soft/bigdata19/hivedata/student_1.txt' into table t_student partition(grade=1);

-- 演示多拷贝一行上传，分区的列的值是分区的值，不是原来的值

​ 静态多分区表语法：

使用这种静态分区一般会是文件夹之间的相互嵌套，如果指定年级和班级作为分区，就是年级中嵌套班级作为文件夹嵌套，一般不会超过三个文件夹嵌套，不会出现省市县的这种情况

同时要注意查询的时候最好按照分区进行查询，如果按照性别进行分区，查询按照年龄来查，就没有起到分区作用，依然是全表扫描

​ create table 表名（字段1，字段2，…，字段n）partition by (字段1 类型1，字段2 类型2) row format delimited fields terminated by ‘,’;

​ 导入数据时候的语法格式：

​ load data (local) inpath ‘数据所在文件路径’ (overwrite) into table 表名 partition(字段名1=数字1，字段名2=数字2，字段名n=数字n);

CREATE TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
) partitioned by(grade int,clazz int)
row format delimited fields terminated by ',';

--注意：前后两个分区的关系为父子关系，也就是grade文件夹下面有多个clazz子文件夹。
1,xiaoge01,1,1
2,xiaoge02,1,1

3,xiaoge03,1,2
4,xiaoge04,1,2

5,xiaoge05,1,3
6,xiaoge06,1,3

7,xiaoge07,2,1
8,xiaoge08,2,1

9,xiaoge09,2,2

--载入数据
load data local inpath '/usr/local/soft/bigdata25/teacher11.txt' into table t_teacher partition(grade=1,clazz=1);

• 动态分区 （DP）dynamic partition

  静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。

  详细来说，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能决定。

  开启动态分区首先要在hive会话中设置如下的参数

  # 表示开启动态分区
  hive> set hive.exec.dynamic.partition=true;
  
  # 表示动态分区模式：strict（需要配合静态分区一起使用）、nostrict
  # strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
  hive> set hive.exec.dynamic.partition.mode=nonstrict;
  
  ===================以下是可选参数======================
  
  # 表示支持的最大的分区数量为1000，可以根据业务自己调整
  hive> set hive.exec.max.dynamic.partitions.pernode=1000;
  

  其余参数详细配置如下

  设置为true表示开启动态分区的功能（默认为false）
  --hive.exec.dynamic.partition=true;
  
  设置为nonstrict，表示允许所有分区都是动态的（默认为strict）
  -- hive.exec.dynamic.partition.mode=nonstrict; 
  
  每个mapper或reducer可以创建的最大动态分区个数(默认为100) 
  比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错
  --hive.exec.max.dynamic.partition.pernode=100; 
  
  一个动态分区创建可以创建的最大动态分区个数（默认值1000）
  --hive.exec.max.dynamic.partitions=1000;
  
  全局可以创建的最大文件个数（默认值100000）
  --hive.exec.max.created.files=100000; 
  
  当有空分区产生时，是否抛出异常（默认false） 
  -- hive.error.on.empty.partition=false;  
  

  动态插入学生年级班级信息

  --创建分区表
  CREATE TABLE IF NOT EXISTS t_student_d (
  sno int,
  sname string
  ) partitioned by (grade int,clazz int)
  row format delimited fields terminated by ',';
  
  --创建外部表
  CREATE EXTERNAL TABLE IF NOT EXISTS t_student_e (
  sno int,
  sname string,
  grade int,
  clazz int
  ) 
  row format delimited fields terminated by ',';
  

  数据：
  
  1,xiaohu01,1,1
  2,xiaohu02,1,1
  3,xiaohu03,1,1
  4,xiaohu04,1,2
  5,xiaohu05,1,2
  6,xiaohu06,2,3
  7,xiaohu07,2,3
  8,xiaohu08,2,3
  9,xiaohu09,3,3
  10,xiaohu10,3,3
  11,xiaohu11,3,3
  12,xiaohu12,3,4
  13,xiaohu13,3,4
  14,xiaohu14,3,4
  15,xiaohu15,3,4
  16,xiaohu16,4,4
  17,xiaohu17,4,4
  18,xiaohu18,4,5
  19,xiaohu19,4,5
  20,xiaohu20,4,5
  21,xiaohu21,4,5
  

  如果静态分区的话，我们插入数据必须指定分区的值。

  如果想要插入多个班级的数据，我要写很多SQL并且执行24次很麻烦。

  而且静态分区有可能会产生数据错误问题

  -- 会报错 
  insert overwrite table t_student_d partition (grade=1) select * from t_student_e where grade=1;
  

  如果使用动态分区，动态分区会根据select的结果自动判断数据应该load到哪儿分区去。

  insert overwrite table t_student_d partition (grade,clazz) select * from t_student_e;
  

  优点：不用手动指定了，自动会对数据进行分区

  缺点：可能会出现数据倾斜

---

表的分区查询的操作

分区表查询

select * from t_student where grade = 1;

// 全表扫描，不推荐，效率低
select count(*) from students_pt1;

// 使用where条件进行分区裁剪，避免了全表扫描，效率高
select count(*) from students_pt1 where grade = 1;

// 也可以在where条件中使用非等值判断
select count(*) from students_pt1 where grade<3 1 and grade>=1;

查看分区

show partitions t_student;

添加分区

alter table t_student add partition (grade=5);

alter table t_student add partition (grade=5) location '指定数据文件的路径';
这里的文件路径指的是要上传数据的文件路径，不是已经有数据的文件路径，这个文件夹必须是事先不存在的，如果存在就报错

alter table t_student set location '/bigdata/student';
该语句将t_student表的数据文件存储路径设置为/bigdata/student目录。请注意，如果目录不存在，则需要先创建该目录。

删除分区

alter table t_student drop partition (grade=5);

---

分区和分桶的区别

• 分区的个数取决于在建表的时候指定分区的字段的种类
• 分桶是将每一种字段进行分类，是拿着某个字段的hash值对桶的个数进行取余，决定这条数据去到哪个桶
• 分桶的结果是一个个文件，分区的结果是一个个文件夹
• 分区之前不一定非要分桶

---

分桶

业务场景

分区和分桶的区别？

数据分桶的适用场景：
分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，尤其是需要确定合适大小的分区划分方式
不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况
分桶是将数据集分解为更容易管理的若干部分的另一种技术。
分桶就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去。

数据分桶原理

• Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
  • bucket num = hash_function(bucketing_column) mod num_buckets
  • 列的值做哈希取余 决定数据应该存储到哪个桶

数据分桶优势

方便抽样

​ 使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便

提高join查询效率

​ 获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

分桶实战

​ 首先，分区和分桶是两个不同的概念，很多资料上说需要先分区在分桶，其实不然，分区是对数据进行划分，而分桶是对文件进行划分。

​ 当我们的分区之后，最后的文件还是很大怎么办，就引入了分桶的概念。

将这个比较大的文件再分成若干个小文件进行存储，我们再去查询的时候，在这个小范围的文件中查询就会快很多。

​ 对于hive中的每一张表、分区都可以进一步的进行分桶。

​ 当然，分桶不是说将文件随机进行切分存储，而是有规律的进行存储。在看完下面的例子后进行解释，现在干巴巴的解释也不太好理解。它是由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。

创建顺序和分区一样，创建的方式不一样。

首先我们需要开启分桶的支持

（依然十分重要，不然无法进行分桶操作！！！！）
set hive.enforce.bucketing=true; 

数据准备（id,name,age）

1,tom,11
2,cat,22
3,dog,33
4,hive,44
5,hbase,55
6,mr,66
7,alice,77
8,scala,88

创建一个普通的表

create table psn31
(
id int,
name string,
age int
)
row format delimited
fields terminated by ',';

将数据load到这张表中

load data local inpath '文件在Linux上的绝对路径' into table psn31;

创建分桶表

create table psn_bucket
(
id int,
name string,
age int
)
clustered by(age) into 4 buckets
row format delimited
fields terminated by ',';

将数据insert到表psn_bucket中

(注意：这里和分区表插入数据有所区别，分区表需要select 和指定分区，而分桶则不需要)

insert into psn_bucket select * from psn31;

查询数据

我们在linux中使用Hadoop的命令查看一下（与我们猜想的顺序一致）

hadoop fs -cat /user/hive/warehouse/bigdata17.db/psn_bucket/*

这里设置的桶的个数是4 数据按照 年龄%4 进行放桶(文件)
11%4 == 3 -----> 000003_0
22%4 == 2 -----> 000002_0
33%4 == 1 -----> 000001_0
44%4 == 0 -----> 000000_0
…以此类推

在Hive进行查询

-- tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
-- 分桶语句中的分母表示的是数据将会被散列的桶的个数，分子表示将会选择的桶的个数。

-- x表示从哪个bucket开始抽取。
-- 例如，table总bucket数为32，tablesample(bucket 2 out of 2)
-- 表示总共抽取（2/2=）1个bucket的数据，分别为第2个bucket和第（2+2=）4个bucket的数据
-- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。
-- 例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据

select * from psn_bucket tablesample(bucket 2 out of 2);

select * from psn_bucket tablesample(bucket 3 out of 2);
随机取值（设置因子，桶的个数/因子）
这里就是取2号桶和4号桶，取2个

select * from psn_bucket tablesample(bucket 2 out of 4);
随机取值（设置因子，桶的个数/因子）
这里就是取2号桶，取一个

select * from psn_bucket tablesample(bucket 2 out of 8);
随机取值（设置倍数，倍数/桶的个数）
这里就是取2号桶 1/2个数据
取出来是一条数据

---

四个by的区别

全局排序

• order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间
• 使用 order by子句排序 :ASC（ascend）升序（默认）| DESC（descend）降序
• order by放在select语句的结尾

select * from 表名 order by 字段名1[，别名2...];

局部排序(对reduce内部做排序)

• sort by 不是全局排序,其在数据进入reducer前完成排序。
• 如果用sort by进行排序，并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局有序。asc,desc
• 设置reduce个数

set mapreduce.job.reduces=3;
set mapred.reduce.tasks=3;

• 查看reduce个数

set mapreduce.job.reduce;

• 排序

select * from 表名 sort by 字段名[,字段名...];

分区排序

distribute by（字段）根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

类似MR中partition,进行分区，结合sort by使用。（注意：distribute by 要在sort by之前）

对于distrbute by 进行测试，一定要多分配reduce进行处理，否则无法看到distribute by的效果。

设置reduce个数

set mapreduce.job.reduce=7;

• 排序

select * from 表名 distribute by 字段名[,字段名...];

分区并排序

• cluster by（字段）除了具有Distribute by的功能外，还会对该字段进行排序
• cluster by = distribute by + sort by 只能默认升序，不能使用倒序

select * from 表名 sort cluster by 字段名[,字段名...];
select * from 表名 distribute by 字段名[,字段名...] sort by 字段名[,字段名...];