在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 tableselectfromwhere ……;

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

image-20231015225652581

桶表有什么用?

  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 ……