Hive中的表类型

标签：tb 分区 partition Hive 类型 table 数据 id

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

下面来一个一个学习一下这些类型的表

内部表

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

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

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

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

外部表

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

使用

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

create external table tb_external (
key string
) location '/user/hive/external';

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

此时到hdfs的 /user/hive/warehouse/ 目录下查看，是看不到这个表的目录的，因为这个表的目录是我们刚才通过location指定的目录，我们再来看一下metastore中的tbls表，这里看到external_table的类型是外部表。

内部表和外部表的相互转化

注意：实际上内外部表是可以互相转化的，需要我们做一下简单的设置即可。

内部表转外部表

alter table tblName set tblproperties ('EXTERNAL'='true'); # EXTERNAL必须是大写，小写不生效

外部表转内部表

alter table tb_external set tblproperties ('EXTERNAL'='false'); # EXTERNAL必须是大写，小写不生效

在实际工作中，我们在hive中创建的表95%以上的都是外部表

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

分区表

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

如果你已经采集了一年的数据，这样每次计算都需要把一年的数据取出来，再过滤出来某一天的数据，效率就太低了，会非常浪费资源，所以我们可以让hive在查询的时候，根据你要查询的日期，直接定位到对应的日期目录。这样就可以直接查询满足条件的数据了，效率提升可不止一点点啊，是质的提升。

想要实现这个功能，就需要使用分区表了

分区可以理解为分类，通过分区把不同类型的数据放到不同目录中

分区的标准就是指定分区字段，分区字段可以有一个或多个，根据咱们刚才举的例子，分区字段就是日期

单字段分区

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

create table tb_partition1 (
id int,
name string
) partitioned by (dt string)
row format delimited
fields terminated by ' ';

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

数据格式是这样的

1 zhangsan
2 lisi

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

load data local inpath '/root/test_hive/hivedata/partition_1.data' into table tb_partition1 partition(dt=20200101);

来查看一下hdfs中的信息，刚才创建的分区信息在hdfs中的体现是一个目录。

当然我们也可以手动在表中只创建分区：

alter table tb_partition1 add partition(dt='20200102');

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

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

show partitions tb_partition1; # 查询表的分区
alter table tb_partition1 drop partition(dt='20200102'); # 删除表的分区

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

多字段分区

刚才呢，我们创建了一个分区，但是有的业务需求，需要创建多个分区，可以吗？当然是可以的！

create table tb_partition2 (
id int,
name string
) partitioned by (year int, school string)
row format delimited
fields terminated by ' ';

文件内容

1 zhangsan
2 lisi
3 wangwu

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

load data local inpath '/root/test_hive/hivedata/partition_2.data' into table tb_partition2 partition(year=2020, school='tsinghua');
load data local inpath '/root/test_hive/hivedata/partition_2.data' into table tb_partition2 partition(year=2021, school='beijing');

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

select * from tb_partition2; # 全表扫描，没有用到分区的特性
select * from tb_partition2 where year = 2020; # 用到了一个分区字段进行过滤
select * from tb_partition2 where year = 2020 and school = 'tsinghua'; # 用到了两个分区字段进行过滤

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

外部分区表

外部分区表是工作中最常用的表

create external table tb_external_partition(
id int,
name string
) partitioned by(dt string)
 row format delimited
 fields terminated by '\t'
 location '/user/hive/external_partition';

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

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

这个一定要注意，在实际工作中新手最容易遇到的一个问题就是，针对分区表，通过hdfs的put命令把数据上传上去了，但是却查不到数据，就是因为没有在表中添加分区信息。

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

alter table tb_external_partition add partition(dt='20200101') location '/user/hive/external_partition'

总结

load data local inpath '/root/test_hive/hivedata/partition_3.data' into table tb_external_partition partition(dt='20200101');

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

hdfs dfs -mkdir /user/hive/external_partition/dt=20200101
hdfs dfs -put /root/test_hive/hivedata/partition_3.data /user/hive/external_partition/dt=20200101
alter table tb_external_partition add partition(dt='20200101') location '/user/hive/external_partition/dt=20200101'

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

桶表

桶表是对数据进行哈希取值，然后放到不同文件中存储

物理上，每个桶就是表(或分区）里的一个文件

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

create table tb_bucket(
id int
) clustered by (id) into 4 buckets;

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

注意，在插入数据之前需要先设置开启桶操作，不然数据无法分到不同的桶里面
其实这里的分桶就是设置reduce任务的数量，因为你分了多少个桶，最终结果就会产生多少个文件，最终结果中文件的数量就和reduce任务的数量是挂钩的

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

原始数据文件是这样的 bucket_source.data

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

create table tb_bucket_source(id int);
load data local inpath '/root/test_hive/hivedata/bucket_source.data' into table tb_bucket_source;
insert into table tb_bucket select id from tb_bucket_source where id is not null;

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

到hdfs上查看桶表中的文件内容，可以看出是通过对buckets取模确定的，这样就实现了数据分桶存储。

数据抽样

假如我们使用的是一个大规模的数据集,我们只想去抽取部分数据进行查看.使用bucket表可以变得更加的
高效

select * from tb_bucket 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桶的数据

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

select * from tb_bucket tablesample(bucket 1 out of 4 on id);
select * from tb_bucket tablesample(bucket 2 out of 4 on id);
select * from tb_bucket tablesample(bucket 3 out of 4 on id);
select * from tb_bucket tablesample(bucket 4 out of 4 on id);

提高某些查询效率

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

视图

create table t3(id int,name string);
create view v1 as select t3.id,t3.name from t3;

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

综合案例

需求分析

需求：Flume按天把日志数据采集到HDFS中的对应目录中，使用SQL按天统计每天数据的相关指标
分析一下：
Flume按天把日志数据保存到HDFS中的对应目录中
针对Flume的source可以使用execsource、channel可以使用基于文件的或者内存的，sink使用hdfssink，在hdfssink的path路径中需要使用%Y%m%d获取日期，将每天的日志数据采集到指定的hdfs目录中
这个是我们在前面学习Flume的时候已经讲过的了，这个倒不难

后面就是需要对按天采集的日志数据建表，由于这份数据可能会被多种计算引擎使用，所以建议使用外部表，这样就算我们不小心把表删了，数据也还是在的，不影响其他人使用，还有就是这份数据是按天分目录存储的，在实际工作中，离线计算的需求大部分都是按天计算的，所以在这里最好在表中增加日期这个分区字段，所以最终决定使用外部分区表。

实现分析

前面FLume采集数据的流程我们就不再演示了，在这我就直接使用之前我们使用hdfs落盘的数据了。我们之前有一个案例是分类型，分目录，把多种类型的数据存储到不同的目录下

目录结构是这样的，首先是按天，然后是按照类型

[root@bigdata04 hivedata]# hdfs dfs -ls /moreType/20200504
Found 3 items
drwxr-xr-x - root supergroup 0 2020-05-04 21:23 /moreType/20200504/videoInfo
drwxr-xr-x - root supergroup 0 2020-05-04 21:23 /moreType/20200504/userInfo
drwxr-xr-x - root supergroup 0 2020-05-04 21:23 /moreType/20200504/giftRecord

这里面的数据是json格式的，也是有规律的，如果我们在建表的时候该怎么创建？

针对json格式的数据建表的时候没办法直接把每个字段都定义出来
通常的解决方案是先写一个mapreduce数据清洗任务，只需要map阶段就行了，对json格式的数据进行解析，把里面每个字段的值全部解析出来，拼成一行，字段值中间可以使用逗号分割，然后再基于解析之后的数据在hive中建表就可以了。
这个解决方案没有任何问题，如果硬要挑问题，那就只能鸡蛋里面挑骨头，说这个解决方案比较麻烦，还需要写MapReduce，不够优雅了。

在这里我们需要提前涉及一个函数get_json_object，这个函数可以从json格式的数据中解析出指定字段

所以我的思路是这样的，

先基于原始的json数据创建一个外部分区表，表中只有一个字段，保存原始的json字符串即可，分区字段是日期和数据类型
然后再创建一个视图，视图中实现的功能就是查询前面创建的外部分区表，在查询的时候会解析json数据中的字段，这样就方便了，我们以后查询直接查视图就可以查询出我们需要的字段信息了，并且一行代码都不需要写。

具体实现

create external table ex_par_more_type(
log string
) partitioned by(dt string,d_type string)
 row format delimited
 fields terminated by ' '
 location '/moreType';

加载数据 【注意，此时的数据已经通过flume采集到hdfs中了，所以不需要使用load命令了，只需要使用一个alter命令添加分区信息就可以了,但是记得要把那三个子目录都添加进去】

添加分区 【注意，这个步骤每天都要做一次】

alter table ex_par_more_type add partition(dt='20200504',d_type='videoInfo') location '/moreType/20200504/videoInfo';
alter table ex_par_more_type add partition(dt='20200504',d_type='userInfo') location '/moreType/20200504/userInfo';
alter table ex_par_more_type add partition(dt='20200504',d_type='giftRecord') location '/moreType/20200504/giftRecord';

可以先查询一下数据，可以查询出来，说明前面的配置没有问题。

接下来就是重点了，需要创建视图，在创建视图的时候从数据中查询需要的字段信息
注意了，由于这三种类型的数据字段是不一样的，所以创建一个视图还搞不定，只能针对每一种类型创建
一个视图。
如果这三种类型的数据字段都是一样的，就可以只创建一个视图了。

create view gift_record_view as select 
get_json_object(log,'$.send_id') as send_id,
get_json_object(log,'$.video_id') as video_id,
get_json_object(log,'$.gold') as gold,
dt
from ex_par_more_type
where d_type = 'giftRecord';

另外两个类似

通过这三个视图查询数据

select * from gift_record_view where dt = '20200504';

后面想要查询数据就直接通过视图，指定日期查询就可以了，不指定日期的话会查询这个类型下面所有的数据。

其实到这里还没完，因为后期flume每天都会采集新的数据上传到hdfs上面，所以我们需要每天都做一次添加分区的操作。
这个操作肯定是要写到脚本中定时调度的，否则每天手工执行还不疯了

开发脚本，脚本名称为 addPartition.sh

#!/bin/bash
# 每天凌晨1点定时添加当天日期的分区
if [ "a$1" = "a" ]
then
    dt=`date +%Y%m%d`
else
    dt=$1
fi
# 指定添加分区操作
hive -e "
alter table ex_par_more_type add partition(dt='${dt}',d_type='videoInfo') location '/moreType/20200504/videoInfo';
alter table ex_par_more_type add partition(dt='${dt}',d_type='userInfo') location '/moreType/20200504/userInfo';
alter table ex_par_more_type add partition(dt='${dt}',d_type='giftRecord') location '/moreType/20200504/giftRecord';
"

注意：此脚本不能重复执行，如果指定的分区已存在，重复添加分区会报错，为了避免报错，可
以这样修改一下，添加if not exists

alter table ex_par_more_type add if not exists partition(dt='${dt}',d_type='videoInfo') location '/moreType/20200504/videoInfo';

总结

这就是一个完整的开发流程，针对需要多次执行的sql一般都是需要配置到脚本中使用hive -e去执行的。

最后再来分析一下，针对这个需求如果数据量不算太打，并且大家对计算效率要求也不算太高的话是没有问题的。
因为现在这种逻辑是每次在查询视图的时候会对原始数据进行解析再计算，如果每天的数据量达到几百G，甚至上T的时候，查询效率就有点低了，大致需要10~20分钟左右，每次查询都是这样，不管你查询多少次，，每次都要根据原始数据进行解析。
如果感觉这个时间不能接受，想要优化，那只能从源头进行优化了，就是先对原始的json数据进行清洗，把需要的字段解析出来，存储到hdfs中，再建表，这个时候就可以提高效率了，因为对原始数据解析的过程只需要一次，后期计算的时候都是对解析过的数据直接计算了，省略了解析步骤，效率可以提升一倍以上。

所以这个就要综合实际情况去考虑了，选便捷性还是选效率

标签：tb,分区,partition,Hive,类型,table,数据,id
From： https://www.cnblogs.com/strongmore/p/17346637.html