Hive的内部表、外部表、分区表与分桶表

Hive

Hive的内部表、外部表、分区表与分桶表

[TOC]

内部表&外部表

定义：未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；

二者区别

区别 内部表managed table 外部表external table
数据管理 Hive自身管理 HDFS管理
存储位置 hive.metastore.warehouse.dir（默认：/user/hive/warehouse） 自己指定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）
删除表 直接删除元数据（metadata）及存储数据 仅会删除元数据，HDFS上的文件并不会被删除
修改表 对内部表的修改会将修改直接同步给元数据 对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;)
创建 无须指定特定关键字 External OR Location 这两个关键字出现一个就是外部表
应用场景 对于保存的一些业务维度表或者是统计好的报表使用管理表(内部表) 公共的数据源使用外部表
加载数据 load数据时，若为宿主机文件，语法为load file local inputpath PATH insert into table TABLENAME,当文件为HDFS文件时，去掉local即可【load结束之后，原HDFS上的数据则会转移到/user/hive/warehouse/TABLENAME/ 路径下，原文件删除】。 load数据时，若为宿主机文件，语法为load file local inputpath PATH insert into table TABLENAME,当文件为HDFS文件时，去掉local即可【原文件不删除】。

分区表&分桶表

​ 数据分区的概念以及存在很久了，通常使用分区来水平分散压力，将数据从物理上移到和使用最频繁的用户更近的地方，以及实现其目的。

​ hive中处理的数据在HDFS中 , select * from tb_name where dt=2020-06-18 ，查询表中的数据是加载HDFS中对应表文件夹下的数据 ,文件夹下的数据很多,将数据全部加载以后再筛选过滤出数据, 显然效率低 ,Hive中的分区表起始就是根据表中某列维度将数据分文件夹管理 ,当按照这列维度查询的时候,直接从对应的文件夹下加载数据，效率更高!

​ hive中有分区表的概念，我们可以看到分区具重要性能优势，而且分区表还可以将数据以一种符合逻辑的方式进行组织，比如分层存储

​ 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑—-数据倾斜。

1、无分区

如果没有分区

1
2
3
4
5
6
7
8
9
10
11
12
--建表
create table  tb_order2(
oid int ,
dt string ,
cost double 
)
row format delimited fields terminated by "," ;
--加载每天的数据到表中
load data local inpath "/hive/data/06-18.txt" into table  tb_order2 ;
load data local inpath "/hive/data/06-19.txt" into table  tb_order2 ;
对应的数据会被加载到表对应的文件夹中 ,当我们执行如下查询的时候 ,先加载两个文件数据,再过滤
select * from tb_order where dt='2020-06-18' ; --两个文件

无分区查询

2、分区表—静态分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- 创建静态分区表
create table  tb_p_order(
oid int ,
dt string ,
cost double 
)
partitioned  by (dy string)
row format delimited fields terminated by "," ;


load data local inpath "/hive/data/06-18.txt" into table  tb_p_order  partition(dy="06-18");
load data local inpath "/hive/data/06-19.txt" into table  tb_p_order  partition(dy="06-19");


0: jdbc:hive2://singletony:10000> select * from tb_p_order where  dy="06-18";
+-----------------+----------------+------------------+----------------+
| tb_p_order.oid  | tb_p_order.dt  | tb_p_order.cost  | tb_p_order.dy  |
+-----------------+----------------+------------------+----------------+
| 1               | 2020-06-18     | 200.0            | 06-18          |
| 2               | 2020-06-18     | 200.0            | 06-18          |
| 3               | 2020-06-18     | 100.0            | 06-18          |
| 3               | 2020-06-18     | 200.0            | 06-18          |
| 4               | 2020-06-18     | 200.0            | 06-18          |
| 5               | 2020-06-18     | 20.0             | 06-18          |
| 6               | 2020-06-18     | 100.0            | 06-18          |
| 7               | 2020-06-18     | 200.0            | 06-18          |
| 8               | 2020-06-18     | 200.0            | 06-18          |
| 9               | 2020-06-18     | 100.0            | 06-18          |
| 10              | 2020-06-18     | 200.0            | 06-18          |
+-----------------+----------------+------------------+----------------+






create  table  tb(
mid int ,
money int ,
name string 
)
row format delimited fields terminated by ","
location "/data/hive_test/store";




3、分区表—动态分区

​ 如果想要根据查询的数据的某个属性进行分区 ,就是动态分区!

创建数据

user.txt数据如下 ,想要根据性别, 或者是地理位置来进行分区,那么就是根据每个属性的值来进行分区的!!

1
2
3
4
5
6
7
8
9
10
11
12
13
u001 ZSS 23 M beijing
u002 YMM 33 F nanjing
u003 LSS 43 M beijing
u004 ZY 23 F beijing
u005 ZM 23 M beijing
u006 CL 23 M dongjing
u007 LX 23 F beijing
u008 YZ 23 M beijing
u009 YM 23 F nanjing
u010 XM 23 M beijing
u011 XD 23 F beijing
u012 LH 23 M dongjing
u013 FTM 23 F dongjing

创建一个临时表 导入数据

1
2
3
4
5
6
7
8
9
create  table  if not exists  tb_user(
uid string ,
name  string ,
age int ,
gender string ,
address string 
)
row format delimited fields  terminated by  " " ;
load  data local  inpath "/hive/data/user.txt"  into table  tb_user ;

创建分区表

1
2
3
4
5
6
7
8
9
create  table  if not exists  tb_p_user(
uid string ,
name  string ,
age int ,
gender string ,
address string 
)
partitioned  by (addr string)
row format delimited fields  terminated by  " " ;

开启动态分区功能

1
2
3
4
5
set hive.exec.dynamic.partition=true;				表示开启动态分区功能（默认为false）
set hive.exec.dynamic.partition.mode=nonstrict; 	表示允许所有分区都是动态的（默认为strict：少需要有一个是静态分区），可以从普通表中导入数据
set hive.exec.max.dynamic.partitions=1000 			表示允许动态分区的最大数量（默认为1000）
set hive.exec.max.dynamic.partitions.pernode =100	表示单个节点上的mapper/reducer允许创建的最大分区
-- insert overwrite 是覆盖   insert into 是追加

动态导入数据

普通表5个字段
分区表 5个主字段 1 个分区字段
插入数据的时候字段个数类型一致 最后一个字段就是分区字段 

1
2
insert into tb_p_user partition(addr)
select uid , name , age , gender , address , address from  tb_user ;

4、分桶表

​ 分桶是相对于分区进行更细粒度的划分，将表中记录按分桶键(字段)的哈希值分散进多个文件中，这些小文件称为桶

​ 分区针对的是数据的存储路径；分桶针对的是数据文件。

​ 分桶是将数据集分解成更容易管理的若干部分的另一个技术。

另外一个要注意的问题是使用桶表的时候我们要开启桶表：

set hive.enforce.bucketing = true;

把表或分区划分成bucket有两个理由

  1. 更快，桶为表加上额外结构，连接相同列划分了桶的表，可以使用map-side join更加高效。
  2. 取样sampling更高效。没有分区的话需要扫描整个数据集。

静态分区&动态分区

二者之间的区别如下：

区别点 静态分区 动态分区
数据插入方式 load和insert insert
分区判断 编译期间指定分区名，分区值确定 根据分区列/字段的实际值（分区值不确定），进行动态分区
分区限制 无限制 需要开启分区功能set hive.exec.dynamic.partition.mode=nonstrict
分区数量 适用于分区数少 分区数不确定，具体看列值
分区数据 创建分区后，分区内可以没数据 分区存在，则区内必有数据

分区表&分桶表

区别点 分区表 分桶表
分区/桶数 不指定 指定桶数
如何分区/桶 指定分区名或者列值 给定列的属性hash%桶数
Hive

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！