深入Hive中的表类型

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

部表

内部表是Hive中的默认表类型，它的数据和元数据都存储在Hive的warehouse目录中。当我们使用load命令加载数据时，实际数据会被移动到warehouse目录中。当删除内部表时，表中的数据和元数据将会被同时删除。

内部表的特性可以概括为：数据和元数据存储在Hive的warehouse目录中，删除表将同时删除数据和元数据。

外部表

建表语句中包含 External 的表叫外部表，外部表在加载数据的时候，实际数据并不会移动到warehouse目录中，只是与外部数据建立一个链接(映射关系)。当删除一个外部表时，只删除元数据，不删除表中的数据，仅删除表和数据之间的链接。建表语句如下：

create external table external_table (
    name string
) location '/data/external';

建表后插入数据，然后到HDFS查看，发现刚才插入的数据都还在，而且他们可以互相转化；实际工作经常做的操作是，现有数据再创建表，绑定后使用Hive进行SQL查询。

• 内转外：alter table 表名 set tblproperties (‘external’=‘true’);
• 外转内：alter table 表名 set tblproperties (‘external’=‘false’);

分区表

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

如果此时已经存储5年的数据，如果把这些数据都扫描一遍，效率未免太过低下。因此可以让hive在查询的时候，根据要查询的日期，直接定位到对应的日期目录。分区表的意义在于优化查询，查询时尽量利用分区字段，如果不使用分区字段会全表扫描，最典型的一个场景就是把天作为分区字段。

创建一个分区表，使用partitioned by指定区分字段，分区字段的名称为dt，类型为string

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

数据不需要带分区字段，还是只有id和name

[root@bigdata06 hivedata]# more partition_1.data 
1 zhangsan
2 lisi

向分区表中加载数据【注意，在这里添加数据的同时需要指定分区信息partition】，创建的分区信息在hdfs中的体现是一个目录。

load data local inpath '/data/soft/hivedata/partition_1.data' into table partition_1 partition(dt='2023-10-15');

分区常用命令

• 查看表中有哪些分区：show partitions 表名
• 只创建分区：alter table 表名 add partition (dt='分区名称');
• 删除分区：alter table 表名 drop partition(dt='2020-01-02');

创建多分区案例

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

某学校有若干二级学院，每年都招很多学生，学校的统计需求大部分会根据年份和学院名称作为条件，使用时同时指定两个即可。

load data local inpath '/data/soft/hivedata/partition_2.data' into table partition_2 partition(year=2020,school='english');

使用分区字段查询

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

外部分区表

外部分区表示工作中最常用的表，同时使用external代表外部表，partitioned指定分区，创建SQL如下：

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

添加与删除分区操作

load data local inpath '/data/soft/hivedata/ex_par.data' into table ex_par partition(dt='20231015'');
alter table ex_par drop partition(dt='2020-01-01');  -- 删除分区

删除分区后，使用SQL无法查询，但是分区上的数据还在。如果数据已经上传上去了，如何给他们绑定关系呢？使用alter add partition

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

桶表

桶表适用于数据分布不均匀的情况，例如中国人口集中在几个省份。如果使用分区表，数据会集中在少数分区，导致其他分区数据较少，影响存储和查询效率。为解决数据倾斜问题，可以采用分桶的概念，即使用桶表来相对均匀地存放数据。

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

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

在加载数据到桶表时，不能使用load data的方式，而需要使用其他表中的数据，类似这种写法：

insert into table … select … from … where ……;

在插入数据之前，需要先设置开启桶操作，以便将数据分配到不同的桶中。分桶的数量决定了最终结果产生的文件数量，与reduce任务的数量相关。通过设置 set hive.enforce.bucketing = true，可以自动控制reduce的数量以适配桶的个数。

桶表有什么用？

1. 数据抽样：假如使用大规模的数据集，去抽取部分数据进行查看，使用bucket表可以变得更加的高效。
   • select * from bucket_tb tablesample(bucket 1 out of 4 on id);
   • tablesample是抽样语句，tablesample(bucket x out of y on 字段)
   • 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桶的数据
2. 提高某些查询效率

视图

使用时和普通表一样，视图名称就是表名，但是视图在/user/hive/warehouse中是不存在的，因为它只是一个虚拟的表。

create view 视图名称 as select …… from …… where ……