Hive07：Hive的进阶操作03之Hive中的表类型：内部表、外部表、分区表、桶表以及视图_hive 查看表类型

一、Hive中的表类型

在Mysql中没有表类型这个概念，因为它就只有一种表。
但是Hive中是有多种表类型的，我们可以分为四种，内部表、外部表、分区表、桶表
下面来一个一个学习一下这些类型的表

二、内部表

首先看内部表

内部表也可以称为受控表
它是Hive中的默认表类型，表数据默认存储在 warehouse 目录中

在加载数据的过程中，实际数据会被移动到warehouse目录中，就是咱们前面在使用load加载数据的时候，数据就会被加载到warehouse中表对应的目录中

当我们删除表时，表中的数据和元数据将会被同时删除

实际上，我们前面创建的表都属于受控表，前面我们已经演示了，创建一张表，其对应就，在metastore中存储表的元数据信息，当我们一旦从hive中删除一张表之后，表中的数据会被删除，在metastore中存储的元数据信息也会被删除。
这就是内部表的特性。

三、外部表

建表语句中包含 External 的表叫外部表

外部表在加载数据的时候，实际数据并不会移动到warehouse目录中，只是与外部数据建立一个链接(映射关系)

表的定义和数据的生命周期互相不约束，数据只是表对hdfs上的某一个目录的引用而已，当删除表定义的时候，数据依然是存在的。仅删除表和数据之间引用关系，所以这种表是比较安全的，就算是我们误删表了，数据还是没丢的

我们来创建一张外部表，看一下外部表的建表语句该如何来写
看一下官方文档

官网中的案例如下：
主要就是在建表语句中增加了EXTERNAL 以及在最后通过locatin指定了这个表数据的存储位置，注意这个路径是hdfs的路径

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
 COMMENT 'This is the staging page view table'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
 STORED AS TEXTFILE
 LOCATION '<hdfs_location>';
1
2
3
4
5
6
7
8

那根据这个格式我们自己来创建一个外部表

create external table external_table (
key string
) location '/data/external';
1
2
3

表创建完以后到hdfs上查询，如果指定的目录不存在会自动创建

此时到hdfs的/user/hive/warehouse/目录下查看，是看不到这个表的目录的，因为这个表的目录是我们刚才通过location指定的目录

而是在这个目录下：

我们再来看一下metastore中的tbls表，这里看到external_table的类型是外部表。

下面我们往这个外部表中加载数据，原始数据文件为external_table.data

a
b
c
d
e
1
2
3
4
5

加载数据

load data local inpath '/data/soft/hivedata/external_table.data' into table external_table;
1

此时加载的数据会存储到hdfs的/data/external目录下

然后查询数据

select * from external_table;
1

接下来尝试删除这个表，看看会发生什么现象

drop table external_table;
1

到hdfs上查看数据，发现之前上传上去的数据还在

在hive中查询目前所有的表信息，发现external_table表确实被删除了。

然后到metastore中查看，发现metastore中的相应的记录也一并被删除掉了。

这个其实就是前面我们所的外部表的特性，外部表被删除时，只会删除表的元数据，表中的数据不会被删除。

注意：实际上内外部表是可以互相转化的，需要我们做一下简单的设置即可，里面的EXTERNAL参数必须是大写才能生效。
1

内部表转外部表

alter table tblName set tblproperties ('EXTERNAL'='true');
1

外部表转内部表

alter table tblName set tblproperties ('EXTERNAL'='false');
1

在实际工作中，我们在hive中创建的表95%以上的都是外部表
因为大致流程是这样的

我们先通过flume采集数据，把数据上传到hdfs中，然后在hive中创建外部表和hdfs上的数据绑定关系，就可以使用sql查询数据了，所以连load数据那一步都可以省略了，因为是先有数据，才创建的表

四、分区表

接下来看一个特殊的表，分区表，这种表的使用场景也是很多的

假设我们的web服务器每天都产生一个日志数据文件，Flume把数据采集到HDFS中，每一天的数据存储到一个日期目录中。我们如果想查询某一天的数据的话，hive执行的时候默认会对所有文件都扫描一遍，然后再过滤出来我们想要查询的那一天的数据

如果你已经采集了一年的数据，这样每次计算都需要把一年的数据取出来，再过滤出来某一天的数据，效率就太低了，会非常浪费资源。

所以我们可以让hive在查询的时候，根据你要查询的日期，直接定位到对应的日期目录。这样就可以直接查询满足条件的数据了，效率提升可不止一点点啊，是质的提升。

想要实现这个功能，就需要使用分区表了
分区可以理解为分类，通过分区把不同类型的数据放到不同目录中
分区的标准就是指定分区字段，分区字段可以有一个或多个，根据咱们刚才举的例子，分区字段就是日期

分区表的意义在于优化查询，查询时尽量利用分区字段，如果不使用分区字段，就会全表扫描，最典型的一个场景就是把天作为分区字段，查询的时候指定天

按照上面的分析，我们来创建一个分区表，使用partitioned by指定区分字段，分区字段的名称为dt，类型为string

create table partition_1 (
id int,
name string 
) partitioned by (dt string)
row format delimited
fields terminated by '\t';
1
2
3
4
5
6

查看表的信息，可以看到分区信息

desc partition_1;
1

数据格式是这样的

1       zhangsan
2       lisi
1
2

向分区表中加载数据【注意，在这里添加数据的同时需要指定分区信息】

load data local inpath '/data/soft/hivedata/partition_1.data' into table partition_1 partition (dt='2020-01-01');
1

来查看一下hdfs中的信息，刚才创建的分区信息在hdfs中的体现是一个目录。
由于这个分区表属于内部表， 所以目录还在warehouse这个目录中

也可以手动在表中只创建分区：

alter table partition_1 add partition (dt='2020-01-02');
1

此时会发现hdfs中又多了一个目录，只不过这个分区目录中是没有数据的

向这个分区中添加数据，可以使用刚才的load命令或者hdfs的put命令都可以

load data local inpath '/data/soft/hivedata/partition_1.data' into table partition_1 partition (dt='2020-01-02');
1

如何查看我的表中目前有哪些分区呢，语法为：show partitions tblName

show partitions partition_1;
1

那问题来了，刚才增加了一个分区，那我能删除一个分区吗？
必须是可以的！

alter table partition_1 drop partition(dt='2020-01-02');
1

到hdfs上确认一下

注意了，此时分区删除之后，分区中对应的数据也就没有了，因为是内部表，所以分区的数据是会被删掉的
1

刚才呢，我们创建了一个分区，但是有的业务需求，需要创建多个分区，可以吗？
当然是可以的！
这里再举一个例子。某学校，有若干二级学院，每年都招很多学生，学校的统计需求大部分会根据年份和学院名称作为条件
所以为了提高后期的统计效率，我们最好是使用年份和学院名称作为分区字段

建表

create table partition_2 (
id int,
name string
) partitioned by (year int, school string)
row format delimited
fields terminated by '\t';
1
2
3
4
5
6

查看表信息

desc partition_2;
1

数据文件内容

1       zhangsan
2       lisi
3       wangwu
1
2
3

注意：数据文件中只需要有id和name这两个字段的值就可以了，具体year和school这两个分区字段是在加载分区的时候指定的。
1

加载数据：

load data local inpath '/data/soft/hivedata/partition_2.data' into table partition_2 partition (year=2020,school='xk');
load data local inpath '/data/soft/hivedata/partition_2.data' into table partition_2 partition (year=2020,school='english'); 
load data local inpath '/data/soft/hivedata/partition_2.data' into table partition_2 partition (year=2019,school='xk');
load data local inpath '/data/soft/hivedata/partition_2.data' into table partition_2 partition (year=2019,school='english'); 
1
2
3
4

查看分区信息

show partitions partition_2;
1

查看hdfs中的目录

前面我们讲了如何创建、增加和删除分区

还有一个比较重要的是我们该如何查询分区中的数据呢？其实非常简单，分区相当于我们的一个查询条件，直接跟在where后面就可以了。

select * from partition_2; 【全表扫描，没有用到分区的特性】
select * from partition_2 where year = 2019;【用到了一个分区字段进行过滤】
select * from partition_2 where year = 2019 and school = 'xk';【用到了两个分区字段进行过滤】
1
2
3

这就是分区表的主要操作。
其实我们在这使用的分区表可以认为是内部分区表，内部分区表的应用场景也不多，外部分区表的应用场景才多，外部分区表就是在外部表的基础上又增加了分区。

五、外部分区表

外部分区表示工作中最常用的表
我们先来创建一个外部分区表

create external table ex_par(
id int,
name string
)partitioned by(dt string) 
 row format delimited  
 fields terminated by '\t'
 location '/data/ex_par';
1
2
3
4
5
6
7

其它的操作和前面操作普通分区表是一样的，我们主要演示一下添加分区数据和删除分区的操作

数据内容如下：

1       zhangsan
2       lisi
1
2

添加分区数据

load data local inpath '/data/soft/hivedata/ex_par.data' into table ex_par partition (dt='2020-01-01');

show partitions ex_par;
1
2
3

删除分区

alter table ex_par drop partition(dt='2020-01-01');
1

分区删除之后，在这里就看不到分区信息了，那hdfs上的分区目录被删除了吗？

注意：此时分区目录的数据还是在的，因为这个是外部表，所以删除分区也只是删除分区的定义，分区中的数据还是在的，这个和内部分区表就不一样了。
1

虽然这个分区目录还在，但是刚才我们通过，show partitions 已经查不到分区信息了，所以查询表数据是查不出来的，虽然这个目录确实在这个表对应的hdfs目录中，但是由于这个是一个分区表，这份数据没有和任何分区绑定，所以就查询不出来。

这个一定要注意，在实际工作中新手最容易遇到的一个问题就是，针对分区表，通过hdfs的put命令把数据上传上去了，但是却查不到数据，就是因为没有在表中添加分区信息，也就是说你们现在虽然在一起了，但是还没有领结婚证，国家还不承认。

select * from ex_par;
1

如果数据已经上传上去了，如何给他们绑定关系呢？就是使用前面咱们讲的alter add partition命令，注意在这里需要通过location指定分区目录。

alter table ex_par add partition(dt='2020-01-01') location '/data/ex_par/dt=2020-01-01';
1

此时再查询分区数据和表数据，就正常了。

总结一下：

load data local inpath '/data/soft/hivedata/ex_par.data' into table ex_par partition (dt='2020-01-01');
1

load data…partition这条命令做了两件事情，上传数据，添加分区(绑定数据和分区之间的关系)

hdfs dfs -mkdir /data/ex_par/dt=2020-01-01
hdfs dfs -put /data/soft/hivedata/ex_par.data /data/ex_par/dt=2020-01-01

alter table ex_par add partition(dt='2020-01-01') location '/data/ex_par/dt=2020-01-01';
1
2
3
4

上面这三条命令做了两个事情，上传数据，添加分区(绑定数据和分区之间的关系)

六、桶表

桶表是对数据进行哈希取值，然后放到不同文件中存储
物理上，每个桶就是表(或分区）里的一个文件

什么时候会用到桶表呢？
举个例子，针对中国的人口，主要集中河南、江苏、山东、广东、四川，其他省份就少的多了，你像西藏就三四百万，海南也挺少的，如果使用分区表，我们把省份作为分区字段，数据会集中在某几个分区，其他分区数据就不会很多，那这样对数据存储以及查询不太友好，在计算的时候会出现数据倾斜的问题，计算效率也不高，我们应该相对均匀的存放数据，从源头上解决，这个时候我们就可以采用分桶的概念，也就是使用桶表

下面来建立一个桶表：
这个表的意思是按照id进行分桶，分成4个桶。

create table bucket_tb(
id int
) clustered by (id) into 4 buckets;
1
2
3

这个时候往桶中加载数据的时候，就不能使用load data的方式了，而是需要使用其它表中的数据，那么给桶表加载数据的写法就有新的变化了。

类似这样的写法
insert into table … select … from …;

注意，在插入数据之前需要先设置开启桶操作，不然数据无法分到不同的桶里面。

其实这里的分桶就是设置reduce任务的数量，因为你分了多少个桶，最终结果就会产生多少个文件，最终结果中文件的数量就和reduce任务的数量是挂钩的。

设置完set hive.enforce.bucketing = true可以自动控制reduce的数量从而适配bucket的个数。

初始化一个表，用于向桶表中加载数据
原始数据文件是这样的

1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12

创建源表、加载数据到表中。

create table b_source(id int);
load data local inpath '/data/soft/hivedata/b_source.data' into table b_source;
select * from b_source;
1
2
3

向桶表中加载数据，注意，这里会产生mapreduce任务！！

insert into table bucket_tb select id from b_source where id is not null;
1

查看桶表中的数据

select * from bucket_tb;
1

按照我们设置的桶的数量为4，这样在hdfs中会存在4个对应的文件，每个文件的大小是相似的。

到hdfs上查看桶表中的文件内容，可以看出是通过对buckets取模确定的。

这样就实现了数据分桶存储。

桶表的主要作用：

1、数据抽样
假如我们使用的是一个大规模的数据集,我们只想去抽取部分数据进行查看.使用bucket表可以变得更加的高效
select * from bucket_tb tablesample(bucket 1 out of 4 on id);
tablesample是抽样语句
语法解析：TABLESAMPLE(BUCKET x OUT OF y ON column)
y尽可能是桶表的bucket数的倍数或者因子，而且y必须要大于等于x
y表示是把桶表中的数据随机分为多少桶
x表示取出第几桶的数据

例如：

bucket 1 out of 4 on id：根据id对桶表中的数据重新分桶，分成4桶，取出第1桶的数据
bucket 2 out of 4 on id：根据id对桶表中的数据重新分桶，分成4桶，取出第2桶的数据
bucket 3 out of 4 on id：根据id对桶表中的数据重新分桶，分成4桶，取出第3桶的数据
bucket 4 out of 4 on id：根据id对桶表中的数据重新分桶，分成4桶，取出第4桶的数据
1
2
3
4

验证一下效果，这里面四个SQL语句，每个SQL语句取出一个桶的数据，最终的总和就是表中的所有数据

2、提高某些查询效率

例如：join查询,可以避免产生笛卡尔积的操作
select a.id,a.name,b.addr from a join b on a.id = b.id;
如果a表和b表已经是分桶表，而且分桶的字段是id字段，那么做这个操作的时候就不需要再进行全表笛卡尔积了，因为分桶之后相同规则的id已经在相同的文件里面了，这样a表的每个桶就可以和b表的每个桶直接join，而不用全表join了。

7、视图

Hive中，也有视图的概念，那我们都知道视图实际上是一张虚拟的表，是对数据的逻辑表示，
它的主要作用是为了降低查询的复杂度。
那我们在Hive中如何来创建一个视图呢？
需要使用create view命令，
下面我们来创建一个视图

create view v1 as select  t3_new.id,t3_new.stu_name from t3_new;
1

此时通过show tables也可以查看到这个视图

show tables;
1

查看视图的结构，显示的内容和表显示的内容是没有区别的

通过视图查询数据

select * from v1;
1

注意：视图在/user/hive/warehouse中是不存在的。因为它只是一个虚拟的表。

在元数据metastore中的体现