Hive的内外部表和分区分桶

Hive的内外部表

内部表

当创建好表的时候，HDFS会在当前表所属的库中创建一个文件夹

当设置表路径的时候，如果直接指向一个已有的路径,可以直接去使用文件夹中的数据

当load数据的时候，就会将数据文件存放到表对应的文件夹中

而且数据一旦被load，就不能被修改

我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS

当我们删除表的时候，表对应的文件夹会被删除，同时数据也会被删除

默认建表的类型就是内部表

--内部表
create table students_internal
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input2';

--hive> dfs -put /usr/local/soft/data/students.txt /input2/;

外部表

外部表因为是指定其他的hdfs路径的数据加载到表中来，所以hive会认为自己不完全独占这份数据

删除hive表的时候，数据仍然保存在hdfs中，不会删除。

--外部表
create external table students_external
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input3';

--hive> dfs -put /usr/local/soft/data/students.txt /input3/;


--删除表
--hive> drop table students_internal;
--Moved: 'hdfs://master:9000/input2' to trash at: hdfs://master:9000/user/root/.Trash/Current
--OK
--Time taken: 0.474 seconds
--hive> drop table students_external;
--OK
--Time taken: 0.09 seconds

Hive分区

在大数据中，最常见的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天或者每小时切分成一个个小的文件，这样去操作小的文件就会容易很多了。

假如现在我们公司一天产生3亿的数据量，那么为了方便管理和查询，就做以下的事情。

1）建立分区（可按照日期，部门等等具体业务分区）

2）分门别类的管理

Hive分区分为静态分区SP和动态分区DP

静态分区SP

static partition–partition by (字段类型)

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

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

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

--创建单分区表语法：
CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int)
row format delimited fields terminated by ',';
-- 分区的字段不要和表的字段相同。相同会报错error10035
-- 载入数据
-- 将相应年级一次导入
load data local inpath '/usr/local/soft/bigdata33/data/stu1.txt' into table t_student partition(grade=1);
load data local inpath '/usr/local/soft/bigdata33/data/stu2.txt' into table t_student partition(grade=2);
load data local inpath '/usr/local/soft/bigdata33/data/stu3.txt' into table t_student partition(grade=3);
load data local inpath '/usr/local/soft/bigdata33/data/stu4.txt' into table t_student partition(grade=5);
-- 演示多拷贝一行上传，分区的列的值是分区的值，不是原来的值


--静态多分区表语法：
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子文件夹。
--载入数据
load data local inpath '/usr/local/soft/bigdata33/t11.txt' into table t_teacher partition(grade=1,clazz=1);
load data local inpath '/usr/local/soft/bigdata33/t12.txt' into table t_teacher partition(grade=1,clazz=2);
load data local inpath '/usr/local/soft/bigdata33/t13.txt' into table t_teacher partition(grade=1,clazz=3);
load data local inpath '/usr/local/soft/bigdata33/t21.txt' into table t_teacher partition(grade=2,clazz=1);
load data local inpath '/usr/local/soft/bigdata32/data/t5.txt' into table t_teacher partition(grade=2,clazz=2);
--可以理解为windows系统中的文件夹父子级


--查看分区
show partitions t_teacher;
--添加分区
alter table t_student add partition (grade=6);
alter table t_teacher add partition (grade=3,clazz=1) location '/user/hive/warehouse/bigdata33.db/t_teacher/grade=3/clazz=1';
--删除分区
alter table t_student drop partition (grade=5);

动态分区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; 
-- hive.exec.dynamic.partition.mode=strict; 

每个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 ','
location "/bigdata33/teachers";

--数据
1,zharongx01,1,1
2,zharongx02,1,1
3,zharongx03,1,1
4,zharongx04,1,2
5,zharongx05,1,2
6,zharongx06,2,3
7,zharongx07,2,3
8,zharongx08,2,3
9,zharongx09,3,3
10,zharongx10,3,3
11,zharongx11,3,3
12,zharongx12,3,4
13,zharongx13,3,4
14,zharongx14,3,4
15,zharongx15,3,4
16,zharongx16,4,4
17,zharongx17,4,4
18,zharongx18,4,5
19,zharongx19,4,5
20,zharongx20,4,5
21,zharongx21,4,5


--如果静态分区的话，我们插入数据必须指定分区的值。
--如果想要插入多个班级的数据，我要写很多SQL并且执行24次很麻烦。
--而且静态分区有可能会产生数据错误问题

-- 会报错 
insert overwrite table t_student_d partition (grade=1,clazz=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;

insert overwrite table B partition (分区字段1,[分区字段2,..]) 查询sql语句;

--优点：不用手动指定了，自动会对数据进行分区
--缺点：可能会出现数据倾斜

Hive分桶

分桶场景

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

分桶原理

Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

bucket num = hash_function(bucketing_column) mod num_buckets
列的值做哈希取余决定数据应该存储到哪个桶

分桶优势

方便抽样

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

提高join查询效率

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

分桶示例

首先，分区和分桶是两个不同的概念，很多资料上说需要先分区在分桶，其实不然，分区是对数据进行划分，而分桶是对文件进行划分。当我们的分区之后，最后的文件还是很大怎么办，就引入了分桶的概念。

将这个比较大的文件再分成若干个小文件进行存储，我们再去查询的时候，在这个小范围的文件中查询就会快很多。对于hive中的每一张表、分区都可以进一步的进行分桶。

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

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

# 分区和分桶的区别
1、在HDFS上的效果区别，分区产生的是一个一个子文件夹，分桶产生的是一个一个文件

2、无论是分区还是分桶，在建表的时候都要指定字段，分区使用partitioned by指定分区字段，分桶使用clustered by指定分桶字段

3、partitioned by指定分区字段的时候，字段后面需要加上类型，而且不能在建表小括号中出现。clustered by指定分桶字段的时候，字段已经出现定义过了，只需要指定字段的名字即可

4、分区字段最好选择固定类别的，分桶字段最好选择值各不相同的。

5、分桶不是必须要建立在分区之上，可以不进行分区直接分桶

--开启分桶支持：
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 person
(
id int,
name string,
age int
)
row format delimited fields terminated by ',';

--上传数据
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 person;

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

posted @ 2025-03-07 20:19 Roxan 阅读(299) 评论(0) 收藏举报

刷新页面返回顶部

Hive的内外部表和分区分桶

Hive的内外部表和分区分桶