管理表 & 外部表

管理表（内部表）

管理表很多时候被称为内部表，Hive 默认创建的就是管理表（Managed Table）。管理表和传统意义上的数据库中的表的概念比较相似，因为它是由 Hive 完全管理其生命周期的，每个表都有自己的存储目录，存放在配置在 hive-site.xml 文件的${hive.metastore.warehouse.dir}/table_name 目录下。当删除管理表的时候，Hive 会删除其目录下的数据。

创建内部表：

create table if not exists student(
    id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile;

外部表

被 external 关键字修饰的表称为外部表（External Table）。外部表和管理表最大的区别是：外部表在删除表的时候只会删除该表的元数据，并不会删除表中的实际数据，表中的数据依然存储在 HDFS 上。

创建外部表：

create external table if not exists student(
    id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile;

通过 desc formatted table_name 命令输出的Table Type可以查看该表是管理表还是外部表。如果输出MANAGED_TABLE则为管理表，如果输出EXTERNAL_TABLE则为外部表。

如：

desc formatted student;

管理表与外部表转换

-- 管理表转外部表
alter table student2 set TBLPROPERTIES('EXTERNAL'='TRUE');

-- 外部表转管理表
alter table student2 set TBLPROPERTIES('EXTERNAL'='FALSE');

提示

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

分区表

当 Hive 表对应的数据量大、文件多时，为了避免查询时全表扫描数据，Hive 支持根据用户指定的字段进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。比如把一整年的数据根据月份划分 12 个月（12 个分区），后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。每一个分区实际上对应的就是 HDFS 上的一个独立的文件夹，该文件夹下面是该分区的所有数据文件。这样如果在查询的时候指定查询条件为month=12，则只会扫描month=12目录下的文件，加快查询速度。

创建分区表语法：

create table order_detail(
    goods_id bigint,
    godds_name string,
    dt date
)
partition by (month int)
row format delimited fields terminated by '\t';

上面语句中的partition by就是指定month字段作为分区字段。如果要数据量较大，可以指定多个字段进行分区，字段时间用逗号分隔，如partition by (month int, day int)，2022-01-01 的数据就会存储在month=1/day=1目录下。

提示

虽然分区字段没有和其它字段一样出现在括号里面，但是其依然是表的字段，如上面语句创建的order_detail表会有四个字段，分别是 goods_id、goods_name、dt、month。

查看表有多少分区

show partitions order_detail;

查看表分区结构

desc formatted order_detail;

把数据上传到分区目录的三种方式

1. 将数据直接上传至该表的 HDFS 目录下（如：/user/hive/warehouse/order_detail/month=1/day=1），然后执行 msck repair table order_detail。
2. 将数据直接上传至该表的 HDFS 目录下（如：/user/hive/warehouse/order_detail/month=1/day=1），然后执行alter table order_detail add partition(month=1, day=1)。
3. 通过命令load data local inpath '/home/bigdata/order_detail.txt' into table order_detail partition(month=1,day=1);

其实上面前两种方式的原理都是现将数据放在指定分区对应的 HDFS 目录下，然后通过不同的命令让其和 Hive 产生关联关系（创建元数据）。

静态分区 & 动态分区

所谓静态分区指的是分区的字段值是由用户在加载数据的时候手动指定的。比如上面三种方式往 Hive 表中加载数据就是采用的静态分区，因为明确指定了此数据集是数据哪个分区的。但是如果一个数据集中包含了多个分区的数据，既有 1 月份的，也有 2 月份 3 月份的。那么就要通过动态分区的方式去插入。

所谓动态分区指的是分区的字段值是基于查询结果自动推断出来的，核心语法就是 insert 和 select。启用 Hive 动态分区，需要在 Hive 会话中设置两个参数：

-- 表示开启动态分区功能。
set hive.exec.dynamic.partition=true;

-- 指定动态分区的模式。分为nonstick非严格模式和strict严格模式。strict严格模式要求至少有一个分区为静态分区。
set hive.exec.dynamic.partition.mode=nonstrict;

动态插入语句：

insert into table order_detail partition(month) select * from other_table;

分区表的使用重点在于：

1. 建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等；
2. 查询的时候尽量先使用 where 进行分区过滤，查询指定分区的数据，避免全表扫描。

分桶表

分桶表概念

当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，或者说通过分区数据分布不均匀的时候，就需要使用分桶技术。将数据按照指定的字段进行分成多个桶中去，即将数据按照字段哈希值进行划分，将具有不同哈希值的数据写到每个桶对应的文件中。分区针对的是数据的存储路径；分桶针对的是数据文件。和分区不一样的是，分桶的字段必须是表中已经存在的字段。

分桶规则

默认的分桶规则：Bucket number = hash_function(bucketing_column) mod num_buckets。

创建分桶表

create table order_detail(
    goods_id bigint,
    godds_name string,
    dt date
)
clustered by(goods_id) into 4 buckets
row format delimited fields terminated by '\t';

上面语句中的clustered by指定了goods_id作为分桶字段，将数据分到 4 个桶中，但如果是 2.0 以下版本，使用分桶表还需要指定一个参数：

set hive.enforce.bucketing=true;

此时如果向表中插入数据，只要 hash_function(bucketing_column)一样的，就一定被分到同一个桶中。

分桶表的优点

和非分桶表相比，分桶表的使用好处有以下几点：

1. 基于分桶字段查询时，会减少全表扫描。
2. join 时可以提高 MR 程序效率，减少笛卡尔积数量。对于 join 操作两个表有一个相同的列，如果对这两个表都进行了分桶操作。那么将保存相同列值的桶进行 join 操作就可以，可以大大较少 join 的数据量。
3. 分桶表数据进行抽样。当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。

分桶抽样查询

抽样查询通过 tablesample 关键字来实现，语法为：TABLESAMPLE(BUCKET x OUT OF y)。

例：

select * from order_detail tablesample(bucket 1 out of 4 on goods_id);

TABLESAMPLE(BUCKET x OUT OF y) 中 y 必须是 table 总 bucket 数的倍数或者因子。hive 根据 y 的大小，决定抽样的比例。例 如，table 总共分了 4 份，当 y=2 时，抽取(4/2=)2 个 bucket 的数据，当 y=8 时，抽取(4/8=)1/2 个 bucket 的数据。 x 表示从哪个 bucket 开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上 y。例如，table 总 bucket 数为 4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2 个 bucket 的数据，抽取第 1(x)个和第 3(x+y)个 bucket 的数据。

注意

x 的值必须小于等于 y 的值。