---

前言

在大数据时代，数据的存储与管理变得尤为重要。Apache Hive作为一种数据仓库工具，提供了一种方便的方式来处理和分析存储在Hadoop分布式文件系统（HDFS）中的大规模数据。通过Hive，用户可以使用类似SQL的查询语言（HiveQL）来执行数据操作，而无需深入了解底层的MapReduce编程模型。本篇文章将详细介绍Hive中数据表的操作，包括创建、查看、修改和删除表的基本语法和示例。我们还将探讨分区表和分桶表的概念及其在优化查询性能方面的重要性。此外，临时表的使用场景和创建方法也将被涵盖。

---

一、数据表操作

1. 创建表

1.1 语法及解释

创建表的语法如下：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])][COMMENT table_comment][PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)][CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS][SKEWED BY (col_name, col_name, ...)ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)[STORED AS DIRECTORIES][[ROW FORMAT row_format] [STORED AS file_format]| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]][LOCATION hdfs_path][TBLPROPERTIES (property_name=property_value, ...)][AS select_statement];

语法解释：

• CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]: 创建一个新表。TEMPORARY表示创建临时表，这种表只在当前会话中可见，并且在会话结束后自动删除；EXTERNAL用于创建外部表，表明该表的数据可能由其他应用程序管理，删除这样的表不会删除其数据；IF NOT EXISTS用于避免在已存在相同名称表时抛出错误。

• [db_name.]table_name: 指定要创建的表名，可以包含数据库名以指定表所属的数据库。

• (col_name data_type [column_constraint_specification] [COMMENT col_comment], ...): 定义列名、类型以及可选的列约束和注释。每个列都有一个名称和相应的数据类型，还可以添加约束（如主键、外键）和注释来描述该列。

• [COMMENT table_comment]: 为表添加注释，提供关于表用途或内容的信息。

• [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]: 指定分区列，允许按照某些列的值将数据划分成多个部分存储，以便优化查询性能。

• [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]: 这个选项用于定义如何对表进行桶化（bucketing），即根据某些列的哈希值将数据分组到不同的文件中。SORTED BY指定了在桶内按哪些列排序。

• [SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), ...) [STORED AS DIRECTORIES]]: 允许用户指定某些列值作为倾斜键，使得这些值对应的数据被单独存储，有助于优化查询效率。STORED AS DIRECTORIES表示使用目录结构来存储倾斜数据。

• [[ROW FORMAT row_format] [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] ]: 定义数据的存储格式和序列化/反序列化方式（SerDe）。ROW FORMAT指定行格式，STORED AS定义存储文件格式（如TextFile, ORC, Parquet等），或者使用STORED BY来指定自定义的存储处理器。

• [LOCATION hdfs_path]: 如果是外部表，需要指定数据在HDFS上的位置。

• [TBLPROPERTIES (property_name=property_value, ...)]: 设置表级别的属性，可用于配置与表相关的特定行为或元数据。

• [AS select_statement];: 可以选择通过执行一个查询语句来定义表结构并填充数据。

1.2 内部表

当数据由Hive自身管理时，这些数据文件通常存储在通过Hive配置参数hive.metastore.warehouse.dir指定的HDFS路径下。对于内部表（也称为管理表），其元数据和实际数据文件都由Hive统一管理。这意味着，当你删除一个内部表时，不仅该表的元数据会被移除，其在HDFS上对应的数据文件也会一并被删除。

1.2.1 创建内部表示例

创建数据库hive_db。

create database if not exists hive_db;

切换到数据库hive_db。

use hive_db;

创建内部表internal_tb。

create table if not exists internal_tb
(staff_id int comment "员工id",staff_name string comment "员工姓名",salary float comment "员工工资",hobby array<string> comment "员工的兴趣爱好",deductions map<string, float> comment "员工的扣款信息",address struct<street:string, city:string> comment "员工的地址信息"
)row format delimited
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
tblproperties("comment"="员工信息表");

解释：

• CREATE TABLE IF NOT EXISTS internal_tb: 创建一个名为internal_tb的表，只有当它不存在时才创建。

• 列定义部分：

  • staff_id int comment "员工id": 定义了一个整数类型的列staff_id，并附有注释“员工id”。
  • staff_name string comment "员工姓名": 定义了一个字符串类型的列staff_name，并附有注释“员工姓名”。
  • salary float comment "员工工资": 定义了一个浮点数类型的列salary，并附有注释“员工工资”。
  • hobby array<string> comment "员工的兴趣爱好": 定义了一个字符串数组类型的列hobby，用来存储员工的兴趣爱好，并附有相应的注释。
  • deductions map<string, float> comment "员工的扣款信息": 定义了一个键为字符串、值为浮点数的映射类型列deductions，用于记录员工的扣款信息，并附有注释。
  • address struct<street:string, city:string> comment "员工的地址信息": 定义了一个结构体类型的列address，包含两个字段：street（街道）和city（城市），都是字符串类型，用于记录员工的地址信息，并附有注释。

• 数据格式描述部分：

  • ROW FORMAT DELIMITED: 指定行格式是分隔符分隔的文本格式。
  • FIELDS TERMINATED BY ',': 字段之间使用逗号分隔。
  • COLLECTION ITEMS TERMINATED BY '_': 集合项（如数组或映射中的元素）使用下划线_分隔。
  • MAP KEYS TERMINATED BY ':': 映射的键和值之间使用冒号:分隔。
  • LINES TERMINATED BY '\n': 行与行之间使用换行符\n分隔。

• STORED AS TEXTFILE: 指定数据以文本文件的形式存储。

• TBLPROPERTIES("comment"="员工信息表"): 设置表属性，这里添加了一个注释，说明这是一个“员工信息表”。

1.3 外部表

当使用外部表时，数据实际上是由HDFS管理的，且数据文件可以存储在创建表时通过LOCATION子句指定的HDFS路径下。如果没有指定LOCATION，则默认会使用Hive配置参数hive.metastore.warehouse.dir所指定的HDFS路径来存储数据文件。重要的是，对于外部表，删除操作仅移除表的元数据，并不会删除实际的数据文件。这意味着即使删除了外部表定义，原始数据仍然保留在HDFS上，从而允许数据被重新导入或用于其他目的。

1.3.1 创建外部表示例

创建外部表external_tb。

create external table if not exists external_tb
(staff_id int comment "员工id",staff_name string comment "员工姓名",salary float comment "员工工资",hobby array<string> comment "员工的兴趣爱好",deductions map<string, float> comment "员工的扣款信息",address struct<street:string, city:string> comment "员工的地址信息"
)row format delimited
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location 'hdfs://hadoop:9000/tmp/external_table/'
tblproperties("comment"="员工信息表");

解释：

• CREATE EXTERNAL TABLE IF NOT EXISTS external_tb: 创建一个名为external_tb的外部表，只有当它不存在时才创建。“EXTERNAL”关键字意味着这个表的数据是由用户或者其他应用程序管理的，删除该表时只会移除元数据而不是实际的数据文件。

• 列定义部分：

  • 定义了多个列，包括staff_id（员工ID）、staff_name（员工姓名）、salary（员工工资）、hobby（员工的兴趣爱好，为字符串数组类型）、deductions（员工的扣款信息，为键值对形式的映射类型，其中键是字符串，值是浮点数）、address（员工地址信息，为结构体类型，包含街道和城市信息）。每个字段都有对应的中文注释描述其用途。

• 数据格式描述部分：

  • ROW FORMAT DELIMITED: 指定行格式是分隔符分隔的文本格式。
  • FIELDS TERMINATED BY ',': 字段之间使用逗号分隔。
  • COLLECTION ITEMS TERMINATED BY '_': 集合项（如数组或映射中的元素）使用下划线“_”分隔。
  • MAP KEYS TERMINATED BY ':': 映射的键和值之间使用冒号“:”分隔。
  • LINES TERMINATED BY '\n': 行与行之间使用换行符“\n”分隔。

• STORED AS TEXTFILE: 指定数据以文本文件的形式存储。

• LOCATION 'hdfs://hadoop:9000/tmp/external_table/': 指定外部表数据在HDFS上的位置。这里指定的是位于hdfs://hadoop:9000/tmp/external_table/路径下的数据文件。这意味着Hive将不会把数据存储在其默认仓库目录中，而是使用这个特定的位置来读取和写入数据。

• TBLPROPERTIES("comment"="员工信息表"): 设置表属性，这里添加了一个注释，说明这是一个“员工信息表”。

2. 查看表

2.1 查看当前数据库中所有表

show tables;

2.2 查看表信息

2.2.1 语法及解释

查看表信息语法如下。

DESCRIBE|DESC [formatted] table_name;

语法解释：

• formatted：显示更详细的表信息。

2.2.2 查看表信息示例

查看表internal_tb和external_tb的信息。

describe internal_tb;
describe external_tb;

查看表internal_tb更详细的信息。

describe formatted internal_tb;

3. 修改表

3.1 重命名表

3.1.1 语法

重命名表语法如下。

ALTER TABLE table_name RENAME TO new_table_name;

3.1.2 示例

将表internal_tb重命名为internal_tb_new。

ALTER TABLE internal_tb RENAME TO internal_tb_new;

3.2 修改表属性

3.2.1 语法

修改表属性语法如下。

ALTER TABLE table_name SET TBLPROPERTIES(property_name = property_value, property_name = property_value, ... );

3.2.2 示例

修改表internal_tb_new的属性。

ALTER TABLE internal_tb_new SET TBLPROPERTIES("date"="2025-02-07");

3.3 修改表列

3.3.1 语法

修改表列语法如下。

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name];

• ALTER TABLE table_name: 指定要修改的表名。

• CHANGE [COLUMN]: 这个关键词标志着即将对表中的一列进行更改。COLUMN关键字是可选的，可以省略。

• col_old_name: 原有列的名字，即你想要修改的那一列的当前名称。

• col_new_name: 列的新名称。如果你不想改变列名，这里可以填写与原名相同的名字。

• column_type: 新的数据类型。你可以选择保持原有数据类型不变，或者根据需要更改为其他类型。请注意，并非所有情况下都可以自由更改数据类型，这取决于具体的数据库系统及其配置。

• [COMMENT col_comment]: 可选项，为新列添加或修改注释。这有助于记录关于该列用途或内容的信息。

• [FIRST|AFTER column_name]: 也是可选项，用于指定列在表中的新位置。

  • 使用 FIRST 将该列移动到表的最前面。
  • 使用 AFTER column_name 可以将该列放置在指定列之后，column_name是你希望紧跟其后的另一列的名称。

3.3.2 示例

把表internal_tb_new中的列staff_name改为staff_name_new。

ALTER TABLE internal_tb_new CHANGE COLUMN staff_name staff_name_new STRING COMMENT '员工姓名';

4. 删除表

4.1 语法及解释

删除表语法如下。

DROP TABLE [IF EXISTS] table_name [PURGE];

语法解释：

• [PURGE]: 这也是一个可选子句，在某些数据库系统（比如Apache Hive）中使用。默认情况下，删除表时，数据会被移到回收站或类似的安全位置以便可以恢复。如果使用了PURGE关键字，则数据会立即被永久删除，无法通过通常的恢复过程找回。

4.2 示例

删除表internal_tb_new。

DROP TABLE IF EXISTS internal_tb_new;

---

二、分区表操作

随着系统运行时间的增长，表中的数据量也会逐渐增加。在Hive中，查询操作通常依赖于全表扫描，这种方式在处理大规模数据集时会导致效率低下，因为大量的不必要数据也被扫描了，从而拖慢了查询速度。为了解决这个问题，Hive引入了分区技术。

分区技术允许根据业务需求将表的整体数据划分为多个子目录进行存储，每个子目录代表一个特定的分区。通过这种方式，当执行查询时，只需要针对相关的特定分区进行扫描，而不是对整个表的数据进行全表扫描。这样不仅减少了不必要的数据扫描，还显著提高了查询效率。

例如，如果一个表包含了多年的数据记录，可以通过年份来对数据进行分区。当用户需要查询特定年份的数据时，Hive可以直接定位到对应的年份分区，仅扫描该分区的数据，从而加快了查询速度并提升了系统的整体性能。这种策略特别适合于数据量庞大且存在明显分类特征的应用场景。

1. 创建分区表

1.1 创建分区表示例

创建分区表partitioned_tb。

create table if not exists partitioned_tb
(id int,name string,age int
)
partitioned by (province string, city string)
row format delimited
fields terminated by ','
lines terminated by '\n';

解释：

• CREATE TABLE IF NOT EXISTS partitioned_tb: 如果不存在同名表，则创建一个名为partitioned_tb的新表。IF NOT EXISTS子句用于避免当存在同名表时抛出错误。

• 列定义部分：

  • 定义了三个字段：id（整型）、name（字符串类型）和age（整型）。这些列构成了表的基本结构，存储有关记录的主要信息。

• PARTITIONED BY (province string, city string): 指定该表按照province（省份）和city（城市）两个字段进行分区。这意味着数据将根据这两个字段的值被划分到不同的子目录中存储。每个province下的不同city会进一步细分存储位置。这种做法有助于提高查询效率，特别是当查询条件可以限定到特定的省份或城市时，因为只需要扫描相关分区的数据而不是整个表。

• 数据格式描述部分：

  • ROW FORMAT DELIMITED: 表明行格式是分隔符分隔的文本格式。
  • FIELDS TERMINATED BY ',': 字段之间使用逗号,作为分隔符。
  • LINES TERMINATED BY '\n': 行与行之间使用换行符\n作为分隔符。

2. 查看表分区

2.1 语法及解释

查看表分区语法如下。

SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_column = partition_col_value, partition_column = partition_col_value, ...)];

语法解释：

• SHOW PARTITIONS: 这是命令的核心，用来展示表中的分区信息。

• [db_name.]table_name: 指定要查看分区信息的表名。可以包含数据库名称（db_name.），如果不指定数据库名称，则默认使用当前上下文中的数据库。

• [PARTITION(partition_column = partition_col_value, ...)]: 这是一个可选的部分，允许你指定具体的分区列和对应的值来过滤显示结果。通过这种方式，你可以只查看满足特定条件的分区，而不是列出所有的分区。例如，如果你有一个按年份和月份分区的表，你可以仅查询某一年某一月的数据分区。

2.2 查看分区示例

查看表partitioned_tb的所有分区。

SHOW PARTITIONS partitioned_tb;

3. 添加表分区

3.1 语法

添加表分区语法如下。

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION 
(partition_column = partition_col_value, 
partition_column = partition_col_value, ...) 
[LOCATION 'location']…;

解释：

• ALTER TABLE table_name: 指定要修改的表名。

• ADD [IF NOT EXISTS]: 添加新的分区到指定的表中。如果使用了IF NOT EXISTS选项，当尝试添加的分区已经存在时，Hive不会抛出错误，而是跳过该操作。这有助于避免重复添加相同的分区。

• PARTITION (partition_column = partition_col_value, partition_column = partition_col_value, ...): 定义要添加的新分区。这里需要指定每个分区列及其对应的值。例如，如果你的表是按年和月分区的，你可能需要指定类似year='2025', month='02'这样的分区信息。

• [LOCATION 'location']: 可选参数，用于指定新分区数据在HDFS中的存储位置。如果不指定，默认会使用表的默认位置加上分区值作为子目录来存放数据。通过指定LOCATION，可以将数据存储在自定义的位置，这对于管理数据分布或整合来自不同源的数据非常有用。

3.2 示例

给表partitioned_tb添加分区。

ALTER TABLE partitioned_tb
ADD PARTITION (province='yunnan', city='kunming') 
PARTITION (province='guangdong', city='huizhou');

4. 重命名表分区

4.1 语法

重命名表分区语法如下。

ALTER TABLE table_name PARTITION 
(partition_column = partition_col_value, 
partition_column = partition_col_value, ...)  
RENAME TO PARTITION (partition_column = partition_col_value, 
partition_column = partition_col_value, ...);

解释：

• ALTER TABLE table_name: 指定要修改的表名。

• PARTITION (partition_column = partition_col_value, partition_column = partition_col_value, ...): 指定当前需要重命名的分区。你需要提供现有的分区列及其对应的值，这些是你要修改的分区标识。

• RENAME TO PARTITION (partition_column = partition_col_value, partition_column = partition_col_value, ...): 指定新的分区值。这部分定义了你希望将现有分区更改为的新分区标识。你需要为每个分区列提供新的值。

4.2 示例

重命名表partitioned_tb分区。

ALTER TABLE partitioned_tb
PARTITION (province='guangdong', city='huizhou') 
RENAME TO
PARTITION (province='guangdong', city='shenzheng');

5. 删除表分区

5.1 语法及解释

删除表分区语法如下。

ALTER TABLE table_name DROP [IF EXISTS] 
PARTITION (partition_column = partition_col_value, 
partition_column = partition_col_value, ...) [PURGE];

语法解释：

• ALTER TABLE table_name: 指定要操作的目标表。

• DROP [IF EXISTS]: DROP关键字表示将执行删除操作。IF EXISTS是一个可选子句，如果指定，当试图删除的分区不存在时，Hive不会抛出错误。这可以避免在不确定分区是否存在的时候出现异常情况。

• PARTITION (partition_column = partition_col_value, partition_column = partition_col_value, ...): 这部分定义了要删除的具体分区。你需要提供分区列名及其对应的值来精确指定要删除的分区。例如，如果你的表是按照年份和月份进行分区的，你可以通过指定year='2025', month='02'来删除代表2025年2月的那个分区。

• [PURGE]: 这是一个可选关键字，在某些数据库系统（如Apache Hive）中使用。默认情况下，删除分区的操作会将数据移动到Hive的回收站或类似的安全位置，以便可以在必要时恢复。然而，如果指定了PURGE，则数据将被立即且永久地删除，无法通过常规手段恢复。这个选项对于确保敏感数据彻底删除或者释放存储空间特别有用。

5.2 示例

删除表partitioned_tb指定分区。

ALTER TABLE partitioned_tb
DROP IF EXISTS 
PARTITION (province='guangdong', city='shenzheng');

---

三、分桶表操作

尽管Hive的分区技术能够将整体数据划分为多个分区，从而优化查询性能，但并不是所有类型的数据都适合进行分区处理。即使在使用分区的情况下，也可能遇到数据分布不均的问题，即所谓的数据倾斜。具体表现为某些分区包含大量数据，而其他分区的数据量则相对较少。这种不平衡会影响查询效率，使得一些查询操作变得低效甚至不可行。

为了解决分区可能导致的数据倾斜问题，Hive引入了分桶（bucketing）技术。通过分桶，用户可以指定表的一个或多个列，并基于这些列的值对数据进行哈希取模运算，然后根据计算结果将数据均匀地分配到预定义数量的桶文件中。这样做的好处是不仅能够保证数据在各个桶之间的均匀分布，减少数据倾斜带来的负面影响，而且还能提高特定类型的查询性能，例如当查询条件涉及分桶字段时，Hive可以直接定位到对应的桶文件，而不是扫描整个表或分区，从而加快查询速度。

简而言之，Hive中的分桶机制允许以一种更加细粒度和均衡的方式管理数据分布，这对于提升大规模数据集上的查询性能至关重要。特别是在面对数据倾斜挑战时，合理使用分桶技术可以显著改善数据处理的效率和效果。

1. 创建分桶表

1.1 创建分桶表示例

创建分桶表clustered_tb。

create table if not exists clustered_tb
(id string,name string,gender string,age int,dept string
)
clustered by (dept) sorted by (age desc) into 3 buckets
row format delimited
fields terminated by ','
lines terminated by '\n';

解释：

• CREATE TABLE IF NOT EXISTS clustered_tb: 如果不存在同名表，则创建一个名为clustered_tb的新表。IF NOT EXISTS子句确保只有在该表不存在时才会创建，避免覆盖已有表。

• 列定义部分：

  • 定义了五个字段：id（字符串类型）、name（字符串类型）、gender（字符串类型）、age（整型）和dept（字符串类型）。这些列构成了表的基本结构，用于存储关于记录的信息。

• CLUSTERED BY (dept): 指定根据dept（部门）列进行聚簇。这意味着数据将根据dept列的值通过哈希算法分配到不同的桶中，旨在实现数据的均匀分布。

• SORTED BY (age DESC): 在每个桶内，数据将按照age列降序排列。这有助于提高某些类型的查询性能，特别是当查询涉及排序操作时，可以减少运行时的排序开销。

• INTO 3 BUCKETS: 指定表的数据被分成3个桶。桶的数量可以根据实际情况调整，目的是为了优化特定查询的性能和数据分布。

• 数据格式描述部分：

  • ROW FORMAT DELIMITED: 表明行格式是分隔符分隔的文本格式。
  • FIELDS TERMINATED BY ',': 字段之间使用逗号,作为分隔符。
  • LINES TERMINATED BY '\n': 行与行之间使用换行符\n作为分隔符。

2. 查看分桶表信息

describe formatted clustered_tb;

---

四、临时表操作

临时表是Hive数据表的一种特殊形式，其主要特点是对当前会话可见，这意味着仅在创建它的会话期间可以访问该临时表。数据被存储在用户特定的临时目录下，确保了数据的隔离性和安全性。重要的是，一旦会话结束，临时表及其相关数据将自动被删除。这种机制使得临时表非常适合用于在单个会话内执行临时性的数据分析任务或存储中间结果，而无需担心后续的数据清理工作。由于其临时性，临时表不会对其他会话或用户造成影响，也简化了数据管理流程。

1. 创建临时表

1.1 创建临时表示例

创建临时表temporary_tb。

create temporary table temporary_tb
(name string,age int,genter string
)
row format delimited
fields terminated by ','
lines terminated by '\n';

2. 查看临时表信息

describe formatted temporary_tb;