HiveQL详解

本文主要是介绍HiveQL详解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

---

前言

本文将详细介绍Hive的数据定义语言（DDL）、数据操作语言（DML）和数据检索功能。通过学习本文，你将了解如何使用Hive来定义数据库、表和分区，执行数据操作，以及利用HiveQL进行数据查询和分析。

---

一、数据定义语言（DDL）

1. 数据库操作

1.1 创建数据库

语法：

CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name[COMMENT database_comment][LOCATION hdfs_path][MANAGEDLOCATION hdfs_path][WITH DBPROPERTIES (property_name=property_value, ...)];

解释：

• [REMOTE]：这个关键字表示数据库或模式将在远程服务器上创建，而不是在本地机器上。
• (DATABASE|SCHEMA)：指定要创建的对象类型。SCHEMA 和 DATABASE 的用法是可以互换的，它们的含义是一样的。
• [IF NOT EXISTS]：这是一个可选的子句，确保只有在指定的数据库或模式不存在时才进行创建。
• [COMMENT database_comment]：这是一个可选的参数，用于提供关于数据库或模式的注释或描述。
• [LOCATION hdfs_path]：这是一个可选的参数，用于指定数据库或模式在HDFS（分布式文件系统）中的位置。
• [MANAGEDLOCATION hdfs_path]：这是一个可选的参数，用于指定数据库或模式在HDFS中的托管位置。托管存储位置是指Hive自动管理的存储位置，用于存储该数据库或模式中的表和分区数据。可以提供一个HDFS路径作为新的托管存储位置。简单来说，就是更改此数据库添加新表的默认父目录。
• [WITH DBPROPERTIES (property_name=property_value, …)]：这是一个可选的参数，用于指定数据库或模式的属性。可以提供多个属性，每个属性由属性名和属性值组成。

1.2 删除数据库

语法：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

解释：

• [RESTRICT|CASCADE]：这是一个可选的子句，用于指定删除操作的级联行为。如果使用"RESTRICT"，则只有在数据库或模式为空（没有表或其他对象）时才允许删除。如果使用"CASCADE"，则会删除数据库或模式以及其中的所有对象。

1.3 更改数据库

语法：

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path;

解释：

• [USER|ROLE]：用于设置指定数据库或模式的所有者。可以指定所有者为一个用户（USER）或一个角色（ROLE）。所有者将具有对数据库或模式的管理权限。

1.4 使用数据库

语法：

USE database_name;
USE DEFAULT;

2. 连接器操作

2.1 创建连接器

从 Hive 4.0.0 开始，添加了对数据连接器的支持。

语法：

CREATE CONNECTOR [IF NOT EXISTS] connector_name[TYPE datasource_type][URL datasource_url][COMMENT connector_comment][WITH DCPROPERTIES (property_name=property_value, ...)];

解释：

• [TYPE datasource_type]: 这是一个可选的子句，用于指定连接器的数据源类型。例如MySQL、PostgreSQL等。
• [URL datasource_url]: 这是一个可选的子句，用于指定连接器的数据源URL。需要提供与数据源类型相对应的URL，以便Hive能够连接到正确的数据源。
• [WITH DCPROPERTIES (property_name=property_value, …)]: 这是一个可选的子句，用于指定连接器的数据连接属性。可以提供一个或多个属性名称和属性值对，用于配置连接器的连接属性。这些属性将根据所使用的数据源类型而有所不同。

2.2 删除连接器

语法：

DROP CONNECTOR [IF EXISTS] connector_name;

2.3 修改连接器

语法：

ALTER CONNECTOR connector_name SET DCPROPERTIES (property_name=property_value, ...);ALTER CONNECTOR connector_name SET URL new_url;ALTER CONNECTOR connector_name SET OWNER [USER|ROLE] user_or_role;

3. 表操作

表名和列名不区分大小写，但 SerDe 和属性名区分大小写。

3.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];

解释：

• [TEMPORARY]: 这是一个可选的修饰符，用于创建临时表，该表在当前会话结束时会被删除。
• [EXTERNAL]: 这是一个可选的修饰符，用于创建外部表，该表的数据存储在指定的HDFS目录，而不是Hive的默认数据存储位置。
• [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]: 这是一个可选的子句，用于指定表的聚集列和排序列。可以指定一个或多个列名作为聚集列，并可选择性地指定一个或多个列名作为排序列。num_buckets是桶的数量。
• [SKEWED BY (col_name, col_name, …) ON ((col_value, col_value, …), (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 (…)] ]: 这是一个可选的子句，用于指定表的行格式和存储格式。可以选择使用ROW FORMAT指定行格式，STORED AS指定文件格式，或者使用STORED BY指定自定义存储处理程序。
• [LOCATION hdfs_path]: 这是一个可选的子句，用于指定表的存储位置。可以提供一个HDFS路径作为表的存储位置。
• [TBLPROPERTIES (property_name=property_value, …)]: 这是一个可选的子句，用于指定表的属性。可以提供一个或多个属性名称和属性值对，用于配置表的属性。
• [AS select_statement]: 这是一个可选的子句，用于指定创建表时的初始数据。可以使用AS关键字后面跟随一个SELECT语句，从现有的表中选择数据并插入到新创建的表中。

其他解释：

-- 数据类型
data_type: primitive_type| array_type| map_type| struct_type| union_type-- 原始类型
primitive_type: TINYINT| SMALLINT| INT| BIGINT| BOOLEAN| FLOAT| DOUBLE| DOUBLE PRECISION| STRING| BINARY| TIMESTAMP| DECIMAL| DECIMAL(precision, scale)| DATE| VARCHAR| CHAR-- 数组类型
array_type: ARRAY < data_type >-- 映射类型
map_type: MAP < primitive_type, data_type >-- 结构体类型
struct_type: STRUCT < col_name : data_type [COMMENT col_comment], ...>-- 联合类型
union_type: UNIONTYPE < data_type, data_type, ... >-- 行格式
row_format: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char][MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char][NULL DEFINED AS char]| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]-- 文件存储格式
file_format:: SEQUENCEFILE| TEXTFILE| RCFILE| ORC| PARQUET| AVRO| JSONFILE| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname-- 列约束规范
column_constraint_specification:: [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]-- 指定列的默认值
default_value:: [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] -- 约束规范
constraint_specification:: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ][, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ][, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ][, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

语法2：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_nameLIKE existing_table_or_view_name[LOCATION hdfs_path];

解释：

• [TEMPORARY]: 这是一个可选的修饰符，用于创建临时表，该表在当前会话结束时会被删除。
• [EXTERNAL]: 这是一个可选的修饰符，用于创建外部表，该表的数据存储在外部位置，而不是Hive的默认数据存储位置。
• [IF NOT EXISTS]: 这是一个可选的子句，用于指定如果表已经存在，则不执行任何操作。
• [db_name.]table_name: 这是要创建的新表的名称，可以选择性地指定数据库名称和表名称。
• LIKE existing_table_or_view_name: 这是用于指定现有表或视图的名称，新表将复制该现有表或视图的结构。
• [LOCATION hdfs_path]: 这是一个可选的子句，用于指定新表的存储位置。你可以提供一个HDFS路径作为表的存储位置。

3.1.1 内部表与外部表

3.1.1.1 内部表

内部表存储在 hive.metastore.warehouse.dir 路径属性下，默认情况下存储在类似于/user/hive/warehouse/databasename.db/tablename/location的文件夹路径中。在表创建过程中，该属性可以覆盖默认位置。如果删除内部表或分区，则会删除与该表或分区关联的数据和元数据。

3.1.1.2 外部表

外部表描述外部文件的元数据/架构。外部表文件可由 Hive 外部的进程访问和管理。外部表可以访问存储在 Azure 存储卷 （ASV） 或远程 HDFS 位置等源中的数据。如果外部表的结构或分区发生更改，则可以使用 MSCK REPAIR TABLE table_name 语句刷新元数据信息。删除外部表不会删除对应的文件，可以理解为外部表和文件是一种映射关系。

3.1.1.3 比较

• ARCHIVE/UNARCHIVE/TRUNCATE/MERGE/CONCATENATE 仅适用于内部表
• DROP 命令会删除内部表的数据，对于外部表仅删除外部表的元数据
• ACID/Transactional 仅适用于内部表
• 查询结果缓存仅适用于内部表
• 外部表上只允许使用 RELY 约束
• 某些实例化视图功能仅适用于内部表

3.1.1.4 识别表

可以使用此命令来识别内部表或外部表，该命令将根据表类型会显示MANAGED_TABLE或EXTERNAL_TABLE。

DESCRIBE FORMATTED table_name

3.1.1.5 外部表分区修复

如果外部表的结构或分区发生更改，则需要修复分区刷新元数据信息。例如，当HDFS文件增加分区后，需要修复外部表分区；当HDFS文件删除某些分区文件后，需要手动删除对应外部表的分区，否则执行修复分区命令会报错，这应该是版本的BUG，在有些新版本中应该已经修复。

MSCK REPAIR TABLE table_name

3.1.2 示例

3.1.2.1 创建分区表示例

create table table_name (name string,age string,address string
)
partitioned by (province string, city string)

3.1.2.2 创建外部表示例

假设HDFS路径为/opt/data/table-name/year=2023/month=01/day=01/xxx，且文件中的数据以^进行分隔，每一行的格式都相同，year、month和day都有多个目录，则可以使用以下语句创建对应的外部表。

CREATE EXTERNAL TABLE table_name (field1 string,field2 string,field3 string
)
PARTITIONED by (year string, month string, day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '^'
STORED AS TEXTFILE
LOCATION 'hdfs://hadoop101:8020/opt/data/table-name';

3.1.2.3 CTAS示例

create-table-as-select简称CTAS，查询其他表的结果来创建表。

注意：

• 目标表不能是外部表。
• 目标表不能是列表分桶表。

CREATE TABLE new_key_value_storeROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe"STORED AS RCFileAS
SELECT (key % 1024) new_key, concat(key, value) key_value_pair
FROM key_value_store
SORT BY new_key, key_value_pair;

3.1.2.4 复制表结构示例

CREATE TABLE 的 LIKE 形式允许精确地复制现有表定义（无需复制其数据）。

CREATE TABLE empty_key_value_store
LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];

3.1.2.5 复制表结构示例

CREATE TABLE 的 LIKE 形式允许精确地复制现有表定义（无需复制其数据）。

CREATE TABLE empty_key_value_store
LIKE key_value_store [TBLPROPERTIES (property_name=property_value, ...)];

3.1.2.5 桶式排序表示例

CREATE TABLE page_view(viewTime INT, userid BIGINT,page_url STRING, referrer_url STRING,ip STRING)PARTITIONED BY(dt STRING, country STRING)CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETSROW FORMAT DELIMITEDFIELDS TERMINATED BY '\001'COLLECTION ITEMS TERMINATED BY '\002'MAP KEYS TERMINATED BY '\003'STORED AS SEQUENCEFILE;

适合创建分桶表的场景：

Hive中的分桶（Bucketing）是一种将数据划分为固定数量的存储桶的技术。每个存储桶中的数据根据指定的列值进行分配，这样可以提高查询性能和执行效率。

1. 连接操作：当你需要对两个或多个表进行连接操作时，使用分桶可以提高连接操作的性能。通过将连接列的值相同的行放入同一个存储桶中，可以减少连接操作时的数据移动和比较操作。

2. 聚合操作：在执行聚合操作（如GROUP BY）时，使用分桶可以将具有相同聚合键的行放在同一个存储桶中，从而减少数据移动和聚合操作的成本。

3. 随机抽样：如果你需要从大型数据集中进行随机抽样，使用分桶可以更高效地执行抽样操作。通过在每个存储桶中随机选择一部分数据，可以获得代表性的样本，而无需扫描整个数据集。

4. 数据倾斜处理：当数据集中某些键的分布不均匀时，可能会导致数据倾斜问题。使用分桶可以将具有相同键值的行分配到不同的存储桶中，从而减轻数据倾斜的影响。

3.1.2.6 创建临时表示例

已创建为临时表的表将仅对当前会话可见。数据将存储在用户的暂存目录中，并在会话结束时删除。

如果使用数据库中已存在的永久表的数据库/表名创建临时表，则在该会话中，对该表的任何引用都将解析为临时表，而不是永久表。如果不删除临时表或将其重命名为不冲突的名称，用户将无法访问该会话中的原始表。

临时表具有以下限制：

• 不支持分区列。
• 不支持创建索引。

CREATE TEMPORARY TABLE list_bucket_multiple (col1 STRING, col2 int, col3 STRING);

3.2 删除表

语法：

DROP TABLE [IF EXISTS] table_name [PURGE];

解释：

• PURGE（可选）：使用PURGE关键字可以永久删除表及其相关的数据和元数据。如果省略了PURGE关键字，默认情况下，删除的表将被移动到回收站（Trash）中，可以通过回收站还原或永久删除。

3.3 清空表

语法：

TRUNCATE [TABLE] table_name [PARTITION (partition_column = partition_col_value, partition_column = partition_col_value, ...)];

解释：

• PARTITION（可选）：使用PARTITION子句可以指定要清空的特定分区。

• partition_column：分区列的名称。

• partition_col_value：分区列的值。

3.4 修改表

3.4.1 重命名表

Hive 0.6 之前的版本只是重命名了元存储中的表，而没有移动 HDFS 位置。
Hive 0.6 开始，内部表的重命名会移动其 HDFS 位置。
Hive 2.2.0 开始，重命名更改为仅当创建不带 LOCATION 子句的表且位于其数据库目录下时，才会移动内部表的 HDFS 位置。

语法：

ALTER TABLE table_name RENAME TO new_table_name;

3.4.2 更改表属性

可以将自己的元数据添加到表中。目前last_modified_user，last_modified_time属性由 Hive 自动添加和管理。

语法：

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

3.4.2.1 更改表注释

语法：

ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);

3.4.2.2 添加 SerDe 属性

语法：

ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, property_name = property_value, ... )];ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES (property_name = property_value, property_name = property_value, ... );

3.4.2.3 删除 SerDe 属性

Hive 4.0.0 开始支持删除 SerDe 属性。

语法：

ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );

3.4.2.4 更改表存储属性

更改表的物理存储属性，只会修改 Hive 的元数据，不会重新组织或重新格式化现有数据。

语法：

ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS;

3.4.2.5 调整表分布

语法：

ALTER TABLE table_name SKEWED BY (col_name1, col_name2, ...)ON ([(col_name1_value, col_name2_value, ...) [, (col_name1_value, col_name2_value), ...][STORED AS DIRECTORIES];

3.4.2.6 取消表分布

NOT SKEWED 选项使表不倾斜并关闭列表分桶功能（因为列表分桶表始终是倾斜的）。这会影响在 ALTER 语句之后创建的分区，但对在 ALTER 语句之前创建的分区没有影响。

语法：

ALTER TABLE table_name NOT SKEWED;

3.4.2.7 取消表的目录存储方式

用于取消表的存储方式设置为目录形式的命令。当您不再需要以目录形式存储表时，可以使用该命令将其还原为默认的存储方式。

语法：

ALTER TABLE table_name NOT STORED AS DIRECTORIES;

3.4.2.8 设置表的倾斜位置

语法：

ALTER TABLE table_name SET SKEWED LOCATION (col_name1="location1" [, col_name2="location2", ...] );

3.4.2.9 更改表约束

添加表约束。

语法：

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column, ...) DISABLE NOVALIDATE;
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column, ...) REFERENCES table_name(column, ...) DISABLE NOVALIDATE RELY;
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column, ...) DISABLE NOVALIDATE;

修改表中列的约束。

语法：

ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type CONSTRAINT constraint_name NOT NULL ENABLE;
ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type CONSTRAINT constraint_name DEFAULT default_value ENABLE;
ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type CONSTRAINT constraint_name CHECK check_expression ENABLE;

删除表约束。

语法：

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

3.5 修改分区

3.5.1 添加分区

分区值为字符串时，需要用单引号将其引起来。

语法：

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

示例：

ALTER TABLE table_name ADD IF NOT EXISTS
PARTITION (year='2024', month='01')
PARTITION (year='2024', month='02');

3.5.2 交换分区

交换分区可以将分区中的数据从一个表移动到另一个具有相同架构但尚不具有该分区的表。

移动一个分区从 table_name_1 到 table_name_2。

ALTER TABLE table_name_2 EXCHANGE
PARTITION (partition_column = partition_col_value, partition_column = partition_col_value, ...)
WITH TABLE table_name_1;

移动多个分区从 table_name_1 到 table_name_2。

ALTER TABLE table_name_2 EXCHANGE
PARTITION (
(partition_column1 = partition_col_value1, partition_column1 = partition_col_value1, ...), 
(partition_column2 = partition_col_value2, partition_column2 = partition_col_value2, ...),
...)
WITH TABLE table_name_1;

示例：

ALTER TABLE table_name_2 EXCHANGE
PARTITION ((year='2024', month='01'), (year='2024', month='02'))
WITH TABLE table_name_1;

3.5.3 修复分区

Hive在其元数据存储中保存了每个表的分区列表。然而，如果直接通过使用命令将新的分区添加到HDFS（比如使用hadoop fs -put命令），或者从HDFS中删除分区，那么元数据存储（以及Hive）将不会意识到这些对于分区信息的更改，除非用户对于每个新增或删除的分区运行相应的命令。

MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

MSC 命令的默认选项是 ADD PARTITIONS。使用此选项，它会将 HDFS 上存在但不在元存储中的任何分区添加到元存储中。DROP PARTITIONS 选项将从元存储中删除已从 HDFS 中删除的分区信息。SYNC PARTITIONS 选项等效于同时调用 ADD 和 DROP PARTITIONS。

示例：

MSCK REPAIR TABLE table_name SYNC PARTITIONS;

3.5.4 删除分区

删除此分区的数据和元数据，数据实际上被移动到 .Trash/Current 目录（如果配置了 Trash）。使用PURGE关键字可以永久删除分区及其相关的数据和元数据。

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...][IGNORE PROTECTION] [PURGE];

partition_spec:: (partition_column = partition_col_value, partition_column = partition_col_value, ...)

解释：

• [IGNORE PROTECTION]: 如果启用了保护模式（protection mode），默认情况下无法直接删除受保护（protected）状态下的分区 。添加此选项将忽略保护模式并强制删除受保护状态下的分区 。

• [PURGE]: 默认情况下，在从表中移除数据时，Hive 将数据移动到回收站目录 .Trash/Current 中以进行恢复和防止意外数据丢失。使用 PURGE 参数可以永久性地删除分区的数据，而不将其移动到回收站。

3.5.5 存档分区

存档是将分区的文件移动到 Hadoop 存档 （HAR） 中的一项功能，存档不会压缩文件。
目的：减少文件计数。
弊端：

• 由于从 HAR 读取的额外开销，查询速度可能会变慢。
• 存档分区不能用 INSERT OVERWRITE 覆盖。如果使用必须先取消存档分区。
• 如果两个进程尝试同时存档同一分区，则可能会发生异常。

启用存档：

set hive.archive.enabled=true;
set hive.archive.har.parentdir.settable=true;
set har.partfile.size=1099511627776;

• hive.archive.enabled：控制是否启用存档操作。

• hive.archive.har.parentdir.settable：通知 Hive 是否可以在创建存档时设置父目录。

• har.partfile.size：控制构成存档的文件的大小。值越大意味着文件越少，但由于映射器数量减少，将导致存档时间更长。

语法：

ALTER TABLE table_name ARCHIVE PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

示例：

ALTER TABLE table_name ARCHIVE PARTITION(year='2024', month='01')

取消存档：

ALTER TABLE srcpart UNARCHIVE PARTITION(year='2024', month='01')

3.6 修改表或分区

3.6.1 修改文件格式

修改表（或分区）的文件格式。该操作仅更改表元数据。现有数据的任何转换都必须在 Hive 外部完成。

ALTER TABLE table_name [PARTITION (partition_column = partition_col_value, partition_column = partition_col_value, ...)] SET FILEFORMAT file_format;

file_format选项：

file_format:: SEQUENCEFILE| TEXTFILE| RCFILE| ORC | PARQUET| AVRO| JSONFILE| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

3.6.2 修改位置

修改表（或分区）的位置。

ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";

3.6.3 更新最后访问时间

外部脚本可以调用 TOUCH 来触发钩子并将所述表或分区标记为已修改。更新表或分区的最后修改时间。

ALTER TABLE table_name TOUCH [PARTITION partition_spec];

3.6.4 压缩

通常，在使用 Hive 事务时，不需要请求压缩，因为系统会检测到对它们的需求并启动压缩。但是，如果关闭了表的压缩，或者希望在系统不选择的时间压缩表，则 ALTER TABLE 可以启动压缩。默认情况下，该语句将把压缩和返回的请求排入队列。从 Hive 2.2.0 开始，可以指定“AND WAIT”来执行操作块，直到压缩完成。

压缩表或分区：

ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])]COMPACT 'compaction_type'[AND WAIT][CLUSTERED INTO n BUCKETS][ORDER BY col_list][POOL 'pool_name'][WITH OVERWRITE TBLPROPERTIES ("property"="value" [, ...])];

查看压缩进度：

SHOW COMPACTIONS

3.6.5 合并

合并表或分区，用于通过合并小文件来提高查询性能。当数据插入到表或分区时，它可能会以多个小文件的形式存储。这会增加文件操作的开销，并对查询性能产生负面影响。

CONCATENATE 操作将这些小文件合并为较大的文件，从而提高读取效率并减少元数据开销。它将多个输入文件中的内容有效地组合成较少数量的输出文件。

语法：

ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] CONCATENATE;

3.7 修改列

3.7.1 修改列

语法：

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

解释：

• CHANGE COLUMN: 关键字用于指示将要更改某一特定列。
• col_old_name: 要被替换或重命名的旧列名。
• col_new_name: 新名称，即将替代旧名称作为新标识符使用（可以与旧名称相同）。
• column_type: 列数据类型。它描述了该字段存储哪种类型和格式化数据（例如字符串、整数等）。
• [COMMENT col_comment]：可选项，在更改后设置新列注释信息。
• [FIRST | AFTER column name ]：确定新添加或重命名后移动到何处位置，默认情况下会添加到最后一个位置；使用 FIRST 将其放置在第一个位置；使用 AFTER 指定某个现有字段之前插入此字段。
• [CASCADE | RESTRICT ]：如果表中存在依赖于被修改列的视图或函数，CASCADE 将更新这些依赖项以反映新的列定义；RESTRICT 将拒绝更改并保留原始定义。

3.7.2 添加/替换列

ALTER TABLE table_name [PARTITION partition_spec] ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) [CASCADE|RESTRICT] 

3.7.3 部分分区规范

可以使用具有部分分区规范的单个 ALTER 语句一次更改多个现有分区。

需要启动动态分区。

SET hive.exec.dynamic.partition = true;
ALTER TABLE table_name PARTITION (year='2024', month='01') CHANGE COLUMN dec_column_name dec_column_name DECIMAL(5,2);
ALTER TABLE table_name PARTITION (year, month) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(5,2);

解释：

• CHANGE COLUMN dec_column_name dec_column_name DECIMAL(5,2): 关键字用于指示要更改某一列的定义，并提供新的列名称和数据类型信息。
• dec_column_name：原始列名称（需要被替换/重写）；
• 第二个 dec_column_ name ：新加入或被替换/重写字段（可以与旧名称相同）；
• DECIMAL(5,2)：描述了该字段存储为十进制数值类型，并设置精度和小数位数。

3.8 动态分区

3.8.1 介绍

在动态分区插入中，用户可以给出部分分区规范，这意味着只需在 PARTITION 子句中指定分区列名称列表即可。列值是可选的。如果给出了分区列值，我们称之为静态分区，否则就是动态分区。每个动态分区列都有一个来自 select 语句的相应输入列。这意味着动态分区的创建由输入列的值决定。动态分区列必须在 SELECT 语句中的列中最后指定，并且按照它们在 PARTITION 子句中的显示顺序指定。从 Hive 3.0.0 开始，无需指定动态分区列。如果未指定，Hive 将自动生成分区规范。

动态分区插入在 Hive 0.9.0 之前默认禁用，在 Hive 0.9.0 及更高版本中默认启用。

3.8.2 属性

配置属性	默认值	描述
hive.exec.dynamic.partition	true	需要设置为启用动态分区插入true
hive.exec.dynamic.partition.mode	strict	在strict模式下，用户必须指定至少一个静态分区，以防用户意外覆盖所有分区，在nonstrict模式下，所有分区都允许是动态的
hive.exec.max.dynamic.partitions.pernode	100	允许在每个映射器/缩减器节点中创建的最大动态分区数
hive.exec.max.dynamic.partitions	1000	总共允许创建的最大动态分区数
hive.exec.max.created.files	100000	MapReduce作业中所有map/reducer创建的最大HDFS文件数
hive.error.on.empty.partition	false	如果动态分区插入生成空结果，是否引发异常

3.8.3 动态分区插入

启用动态分区：

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.error.on.empty.partition=false;

动态分区插入示例：

INSERT OVERWRITE TABLE table_name PARTITION(year, month)
SELECT *, YEAR(time_field) as year, MONTH(time_field) as month
WHERE time_field >= '2023-01-01' time_field < '2024-03-01';

4. 视图操作

4.1 创建视图

语法：

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)]AS SELECT ...;

4.2 删除视图

语法：

DROP VIEW [IF EXISTS] [db_name.]view_name;

4.3 修改视图

4.3.1 修改视图属性

语法：

ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties;

表属性：

table_properties:: (property_name = property_value, property_name = property_value, ...)

4.3.2 修改视图作为查询

语法：

ALTER VIEW [db_name.]view_name AS select_statement;

示例：

ALTER VIEW db_name.view_name AS select * from table_name;

4.4 物化视图

在Hive中，物化视图（Materialized View）是一种预先计算和存储的查询结果集。

Hive中的物化视图提供了以下优势：

• 提高查询性能：物化视图将查询的结果预先计算并存储在磁盘上，这样可以避免每次查询时重新计算复杂的逻辑。当查询需要使用物化视图时，Hive可以直接从物化视图中获取结果，从而提高查询性能。

• 减少计算开销：物化视图的结果存储在磁盘上，可以避免重复计算和数据传输的开销。这对于大规模数据和复杂查询非常有益，可以显著减少查询的执行时间。

• 支持复杂查询优化：物化视图可以存储复杂查询的结果，包括聚合、连接和过滤操作。这样，当查询需要使用这些操作时，Hive可以直接从物化视图中获取结果，而无需重新执行复杂的计算。

4.4.1 创建物化视图

语法：

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name[DISABLE REWRITE][COMMENT materialized_view_comment][PARTITIONED ON (col_name, ...)][CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)][[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
<query>;

解释：

• CREATE MATERIALIZED VIEW: 创建物化视图的关键字。
• [db_name.]materialized_view_name: 物化视图的名称，可以选择性地指定所属的数据库。
• [DISABLE REWRITE]: 可选项，禁用查询重写优化。
• [COMMENT materialized_view_comment]: 可选项，为物化视图添加注释。
• [PARTITIONED ON (col_name, …)]: 可选项，指定物化视图的分区列。
• [CLUSTERED ON (col_name, …) | DISTRIBUTED ON (col_name, …) SORTED ON (col_name, …)]: 可选项，指定物化视图的聚集、分布和排序方式。
• ROW FORMAT row_format: 可选项，指定物化视图的行格式。
• STORED AS file_format: 可选项，指定物化视图的存储格式。
• STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)]: 可选项，指定自定义的存储处理程序和序列化属性。
• LOCATION hdfs_path: 可选项，指定物化视图的存储路径。
• TBLPROPERTIES (property_name=property_value, …): 可选项，指定物化视图的表属性。
• <query>: 物化视图的查询语句，用于计算和存储物化视图的结果。

4.4.2 删除物化视图

语法：

DROP MATERIALIZED VIEW [db_name.]materialized_view_name;

4.4.3 显示物化视图

语法：

SHOW MATERIALIZED VIEWS [IN database_name] ['identifier_with_wildcards'];

解释：

• [‘identifier_with_wildcards’]: 可选项，用于过滤要显示的物化视图的名称。可以使用通配符（如*）来匹配多个物化视图名称。

4.4.4 描述物化视图

语法：

DESCRIBE [EXTENDED | FORMATTED] [db_name.]materialized_view_name;

解释：

• DESCRIBE: 描述物化视图的关键字。
• [EXTENDED | FORMATTED]: 可选项，用于指定描述的详细程度。EXTENDED提供更详细的描述信息，而FORMATTED提供格式化的描述信息。如果不指定，则默认为基本描述信息。

4.4.5 启用/禁用物化视图重写

默认情况下，物化视图在创建时启用重写。
语法：

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

解释：

• ENABLE|DISABLE REWRITE: 用于启用或禁用物化视图重写的选项。ENABLE REWRITE表示启用物化视图重写，DISABLE REWRITE表示禁用物化视图重写。

5. 索引操作

Hive 索引的目标是提高对表的某些列进行查询查找的速度。
索引从 Hive 3.0 开始删除。

5.1 创建索引

语法：

CREATE INDEX index_nameON TABLE base_table_name (col_name, ...)AS index_type[WITH DEFERRED REBUILD][IDXPROPERTIES (property_name=property_value, ...)][IN TABLE index_table_name][[ ROW FORMAT ...] STORED AS ...| STORED BY ...][LOCATION hdfs_path][TBLPROPERTIES (...)][COMMENT "index comment"];

解释：

• ON TABLE base_table_name: 指定要在哪个表上创建索引，base_table_name是表的名称。
• (col_name, …): 指定要在哪些列上创建索引，col_name是列的名称。可以指定一个或多个列。
• AS index_type: 指定索引的类型，例如B-tree索引、Bitmap索引等。
• [WITH DEFERRED REBUILD]: 可选项，用于指定是否延迟重建索引。如果指定了该选项，索引将在创建后立即可用，但索引的构建将在后台进行。
• [IDXPROPERTIES (property_name=property_value, …)]: 可选项，用于指定索引的属性。可以指定多个属性，每个属性由property_name=property_value的形式表示。
• [IN TABLE index_table_name]: 可选项，用于指定索引存储的表名称。如果不指定，则索引将存储在默认的索引表中。
• ROW FORMAT … STORED AS … 或 STORED BY …: 可选项，用于指定索引的存储格式。可以使用ROW FORMAT子句指定行格式和存储格式，或使用STORED BY子句指定自定义的存储处理器。
• [LOCATION hdfs_path]: 可选项，用于指定索引的存储位置。hdfs_path是HDFS上的路径。
• [TBLPROPERTIES (…)]: 可选项，用于指定表的属性。可以指定多个属性，每个属性由property_name=property_value的形式表示。
• [COMMENT “index comment”]: 可选项，用于指定索引的注释。

5.2 删除索引

语法：

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

解释：

• REBUILD: 重建索引的选项，表示要对索引进行重建操作。

6. 宏

在Hive中，宏是一种用于在查询中定义可重用代码片段的机制。通过创建宏，可以将常用的查询逻辑封装起来，并在查询中进行调用，以简化复杂的查询和提高查询的可读性。

宏在Hive中具有以下几个作用：

• 代码重用：宏可以将一段常用的代码逻辑封装起来，以便在多个查询中重复使用。通过使用宏，可以避免在每个查询中编写相同的代码，提高代码的可维护性和可读性。

• 逻辑简化：宏可以用于简化复杂的查询逻辑。通过将复杂的查询逻辑封装在宏中，可以将复杂的查询转化为简单的宏调用，使查询语句更加清晰和易于理解。

• 参数化：宏可以接受参数，根据不同的参数值执行相应的逻辑。通过参数化，可以使宏更加灵活和通用，适应不同的查询需求。

• 减少错误：通过使用宏，可以减少手动编写代码的错误。宏的定义只需编写一次，并在多个查询中重复使用，减少了手动编写代码的机会，从而减少了潜在的错误。

• 提高性能：宏可以用于优化查询性能。通过将一些常用的计算或过滤逻辑封装在宏中，可以减少查询中的重复计算，提高查询的执行效率。

6.1 创建临时宏

语法：

CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;

解释：

• CREATE TEMPORARY MACRO: 创建临时宏的关键字。
• macro_name: 宏的名称，用于唯一标识宏。
• ([col_name col_type, …]): 可选项，用于指定宏的参数列表。col_name是参数的名称，col_type是参数的数据类型。可以指定一个或多个参数。
• expression: 宏的表达式，表示宏的实现逻辑。

示例：

CREATE TEMPORARY MACRO simple_add (x int, y int) x + y;

6.2 删除临时宏

语法：

DROP TEMPORARY MACRO [IF EXISTS] macro_name;

7. 函数

7.1 临时函数

7.1.1 创建临时函数

语法：

CREATE TEMPORARY FUNCTION function_name AS class_name;

示例：

CREATE TEMPORARY FUNCTION my_concat AS 'org.apache.hadoop.hive.contrib.udf.example.GenericUDFExample';

7.1.2 删除临时函数：

语法：

DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

7.2 永久函数

7.2.1 创建永久函数

语法：

CREATE FUNCTION [db_name.]function_name AS class_name[USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];

解释：

• CREATE FUNCTION：创建函数的关键字。
• [db_name.]function_name：函数的名称，可以选择性地指定数据库名称（db_name）作为前缀。
• AS class_name：指定实现函数的 Java 类的完全限定名（fully qualified name）。
• USING JAR|FILE|ARCHIVE ‘file_uri’：可选项，指定函数依赖的 JAR 文件、文件或归档文件的路径。可以使用 USING JAR、USING FILE 或 USING ARCHIVE 来指定不同类型的依赖文件。可以使用逗号分隔多个文件。

7.2.2 删除永久函数

语法：

DROP FUNCTION [IF EXISTS] function_name;

7.2.3 重新加载永久函数

语法：

RELOAD (FUNCTIONS|FUNCTION);

8. 显示

8.1 显示数据库

列出元存储中定义的所有数据库。SCHEMAS 和 DATABASES 的用法是可以互换的，它们的含义是一样的。

语法：

SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];

解释：

• [LIKE ‘identifier_with_wildcards’]：可选的 LIKE 子句允许使用正则表达式筛选数据库列表。正则表达式中的通配符对于任何字符只能是“”，对于选项只能是“|”。例如，‘employees’、'emp’、‘emp*|*ees’，所有这些都将与名为“employees”的数据库匹配。从 Hive 4.0.0 开始，可以包含任何字符的“%”和单个字符的“_”的表达式。

8.2 显示连接器显示表

从 Hive 4.0.0 开始。
语法：

SHOW CONNECTORS;

8.3 显示表

语法：

SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

8.4 显示视图

正则表达式中的通配符对于任何字符只能是“*”，对于选项只能是“|”。

语法：

SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'];

示例：

SHOW VIEWS LIKE 'test_*|*_view2';

8.5 显示物化视图

列出当前数据库（或使用 OR 子句显式命名的视图）中名称与可选正则表达式匹配的所有视图。它还显示有关物化视图的其他信息，例如，是否启用重写，以及实例化视图的刷新模式。

正则表达式中的通配符对于任何字符只能是“*”，对于选项只能是“|”。

语法：

SHOW MATERIALIZED VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards’];

8.6 显示分区

列出指定表的所有现有分区。分区按字母顺序列出。

语法：

SHOW PARTITIONS table_name;

示例：

SHOW PARTITIONS [db_name.]table_name;
SHOW PARTITIONS [db_name.]table_name PARTITION(year='2024');

8.7 显示扩展的表/分区

语法：

SHOW TABLE EXTENDED [IN|FROM database_name] LIKE 'identifier_with_wildcards' [PARTITION(partition_spec)];

8.8 显示表属性

语法：

SHOW TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname("property_name");

第一种表单列出了相关表的所有表属性，每行一个属性，由制表符分隔。该命令的第二种形式仅打印所请求属性的值。

8.9 显示创建表

显示创建给定表的 CREATE TABLE 语句，或创建给定视图的 CREATE VIEW 语句。

语法：

SHOW CREATE TABLE ([db_name.]table_name|view_name);

8.10 显示索引

从Hive 3.0 起删除索引

语法：

SHOW [FORMATTED] (INDEX|INDEXES) ON table_with_index [(FROM|IN) db_name];

8.11 显示列

显示表中的所有列，包括分区列。

语法：

SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];

从Hive 3.0 起，语法如下：

SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name] [ LIKE 'pattern_with_wildcards'];

正则表达式中的通配符对于任何字符只能是“*”，对于选项只能是“|”。
示例：

SHOW COLUMNS FROM table_name LIKE "col1|cola"; 
SHOW COLUMNS IN table_name IN database_name LIKE 'col*';

8.12 显示函数

列出了所有用户定义和内置的函数，如果使用 LIKE 指定，则按正则表达式进行筛选。

语法：

SHOW FUNCTIONS [LIKE "<pattern>"];

8.13 显示锁

显示表或分区上的锁。

语法：

SHOW LOCKS <table_name>;
SHOW LOCKS <table_name> EXTENDED;
SHOW LOCKS <table_name> PARTITION (<partition_spec>);
SHOW LOCKS <table_name> PARTITION (<partition_spec>) EXTENDED;
SHOW LOCKS (DATABASE|SCHEMA) database_name;

8.14 显示配置

语法：

SHOW CONF <configuration_name>;

8.15 显示事务

语法：

SHOW TRANSACTIONS;

8.16 显示压缩

返回当前正在处理或计划的所有压缩请求的列表。

语法：

SHOW COMPACTIONS [DATABASE.][TABLE] [PARTITION (<partition_spec>)] [POOL_NAME] [TYPE] [STATE] [ORDER BY `start` DESC] [LIMIT 10];

解释：

• POOL_NAME: 可选项，指定要显示与特定资源池相关联的压缩任务信息 。
• TYPE: 可选择性地限制结果为某种类型（例如：major、minor） 的压缩操作 ，以过滤结果集 。
• STATE: 根据状态（例如：queued、running）对结果进行过滤显示。
• 使用 “ORDER BY start DESC” 对开始时间降序排序输出结果集；
• 使用 “LIMIT 10” 来限制最多返回前10个匹配记录。

9. 描述

9.1 描述数据库

显示数据库的名称、注释（如果已设置注释）及其在文件系统上的根位置。

语法：

DESCRIBE (DATABASE|SCHEMA) [EXTENDED] db_name;

9.2 描述连接器

从 Hive 4.0.0 开始。
显示连接器的名称、其注释（如果已设置）以及其数据源 URL 和数据源类型。

语法：

DESCRIBE CONNECTOR [EXTENDED] connector_name;

9.3 描述表/视图/物化视图/列

描述表/视图/具体化视图/列语法有两种格式，具体取决于是否指定了数据库。

未指定数据库，语法：

DESCRIBE [EXTENDED|FORMATTED] table_name[.col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

指定数据库，语法：

DESCRIBE [EXTENDED|FORMATTED] [db_name.]table_name[ col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

解释：

• EXTENDED 或 FORMATTED: 这是可选参数，用于指定输出结果的格式。如果使用 EXTENDED 参数，则会显示更多关于表和列属性的详细信息；而使用 FORMATTED 参数则会以易读格式显示结果。

• [db_name.]table_name: 这是要描述结构的目标表名，可以选择性地包含数据库名称前缀（例如：database.table）。

• [col_name ( [.field_ name ] | [’ $elem ] | [’ $key ] ｜[’ $value ]] *): 这部分表示你可以选择一个或多个列来获取更具体字段级别或嵌套数据类型（如数组、映射等） 的相关信息。

• field_name 表示结构元素

• ‘$elem$’ 表示数组元素

• ‘$key$’ 表示映射键

• ‘$value$’ 表示映射值

9.4 显示列统计信息

计算指定表中所有列的列统计信息（如果表已分区，则计算所有分区的列统计信息）

语法：

ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS;

查看收集的列统计信息。

语法：

DESCRIBE FORMATTED [db_name.]table_name column_name;
DESCRIBE FORMATTED [db_name.]table_name column_name PARTITION (partition_spec);

9.5 描述分区

Hive 2.0 之前。
语法：

DESCRIBE [EXTENDED|FORMATTED] table_name[.column_name] PARTITION partition_spec;
DESCRIBE [EXTENDED|FORMATTED] [db_name.]table_name [column_name] PARTITION partition_spec;

Hive 2.0 开始。
语法：

DESCRIBE [EXTENDED | FORMATTED][db_name.]table_name [PARTITION partition_spec] [col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

9.6 中止事务

Hive 2.1.0 开始。
此命令可与 SHOW TRANSACTIONS 一起使用。后者可以帮助找出要清理的候选事务 ID。

ABORT TRANSACTIONS transactionID [ transactionID ...];

---

二、数据操作语言（DML）

1. 从文件加载数据到Hive表

Hive 在将数据加载到表中时不执行任何转换。加载操作目前是纯复制/移动操作，将数据文件移动到与 Hive 表对应的位置。

Hive 3.0 之前的加载操作是纯复制/移动操作，将数据文件移动到与 Hive 表对应的位置。

Hive 3.0 之前。语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

示例：

LOAD DATA LOCAL INPATH '/path/to/local/file_name' OVERWRITE INTO TABLE my_table;LOAD DATA INPATH 'hdfs://localhost:8020/path/to/local/file_name' OVERWRITE INTO TABLE my_table;

Hive 3.0 及更高版本支持其他加载操作，因为 Hive 在内部将加载重写为 INSERT AS SELECT。

Hive 3.0 开始，语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde']

解释：

• LOAD DATA：这是Hive中用于加载数据的关键字。
• [LOCAL]：可选项，指示要从本地文件系统加载数据。如果省略该关键字，则假定文件路径位于HDFS上。
• INPATH ‘filepath’：要加载的文件路径。可以是本地文件系统或HDFS上的完整路径。
• [OVERWRITE]：可选项，表示如果目标表已经存在，则覆盖（删除）现有数据，并将新数据导入表中。如果省略该关键字，默认行为为追加到现有表中。
• INTO TABLE tablename：指定目标表名，即将数据导入其中。
• [PARTITION (partcol1=val1, partcol2=val2 …)] ：用于指定要将数据导入到哪些特定分区（若适用）。通过提供列名和相应值来定义每个分区条件。
• [INPUTFORMAT ‘inputformat’]: 可以选择性地指定输入格式类别，在默认情况下会根据存储位置自动推断出合适的输入格式类别。例如，“org.apache.hadoop.mapred.TextInputFormat”表示文本类型；
• [SERDE ‘serde’]: 可以选择性地指定序列化器类别，用于将输入数据解析为表中的列。默认情况下，Hive使用适当的序列化器根据表定义自动推断。

2. 将查询结果插入到Hive表

可以使用 insert 子句将查询结果插入到Hive表中。

2.1 标准插入

标准语法：

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

2.2 多重插入

多重插入语法：

FROM from_statement
INSERT OVERWRITE TABLE table_name1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE table_name2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE table_name2 [PARTITION ...] select_statement2] ...;

FROM from_statement
INSERT INTO TABLE table_name1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT INTO TABLE table_name2 [PARTITION ...] select_statement2]
[INSERT OVERWRITE TABLE table_name2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;

2.3 动态分区插入

动态分区插入语法：

INSERT OVERWRITE TABLE table_name PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

INSERT INTO TABLE table_name PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

启用动态分区：

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.error.on.empty.partition=false;

动态分区插入示例：

INSERT OVERWRITE TABLE table_name PARTITION(year, month)
SELECT *, YEAR(time_field) as year, MONTH(time_field) as month
WHERE time_field >= '2023-01-01' time_field < '2024-03-01';

3. 将查询结果写入目录

3.1 标准语法

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format]
SELECT ... FROM ...

行格式：

row_format: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char][MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char][NULL DEFINED AS char]

解释：

• DELIMITED: 表示行数据是以分隔符进行分隔的。这是最常见的行格式之一。
• FIELDS TERMINATED BY char: 指定字段之间的分隔符。char是一个单字符或字符串，用于将字段分隔开。例如，如果使用逗号作为字段分隔符，可以使用FIELDS TERMINATED BY ‘,’。
• ESCAPED BY char: 指定转义字符，用于在行数据中转义分隔符或转义字符本身。如果行数据中包含分隔符或转义字符，可以使用转义字符进行转义。char是一个单字符或字符串，用于表示转义字符。例如，如果使用反斜杠（\）作为转义字符，可以使用ESCAPED BY ‘\’。
• COLLECTION ITEMS TERMINATED BY char: 指定集合类型字段（如数组或列表）中的元素之间的分隔符。char是一个单字符或字符串，用于将集合中的元素分隔开。
• MAP KEYS TERMINATED BY char: 指定映射类型字段中键值对之间的分隔符。char是一个单字符或字符串，用于将键和值分隔开。
• LINES TERMINATED BY char: 指定行之间的分隔符。char是一个单字符或字符串，用于将行分隔开。
• NULL DEFINED AS char: 指定表示空值的字符串。char是一个单字符或字符串，用于表示空值。当遇到该字符串时，Hive将其解释为NULL值。

3.2 多重写入

FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

4. 插入数据

语法：

INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

5. 更新数据

语法：

UPDATE table_name SET column = value [, column = value ...] [WHERE expression]

6. 删除数据

语法：

DELETE FROM table_name [WHERE expression]

7. 合并数据

MERGE 从 Hive 2.2 开始可用。
语法：

MERGE INTO <target table> AS T USING <source expression/table> AS S
ON <boolean expression1>
WHEN MATCHED [AND <boolean expression2>] THEN UPDATE SET <set clause list>
WHEN MATCHED [AND <boolean expression3>] THEN DELETE
WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>

解释：

• <target table>: 目标表，即要进行合并操作的表。
• AS T: 为目标表指定别名，以便在后续的语句中引用它。
• <source expression/table>: 源表达式或源表，即要与目标表进行比较的数据源。
• AS S: 为源表达式或源表指定别名，以便在后续的语句中引用它。
• ON : 指定用于匹配目标表和源表的条件。是一个布尔表达式，用于指定匹配条件。
• WHEN MATCHED [AND ] THEN UPDATE SET : 当目标表和源表匹配时，根据指定的条件执行更新操作。是一个可选的布尔表达式，用于进一步筛选匹配的行。是一个用于设置目标表列值的列表。
• WHEN MATCHED [AND ] THEN DELETE: 当目标表和源表匹配时，根据指定的条件执行删除操作。是一个可选的布尔表达式，用于进一步筛选匹配的行。
• WHEN NOT MATCHED [AND ] THEN INSERT VALUES : 当目标表和源表不匹配时，根据指定的条件执行插入操作。是一个可选的布尔表达式，用于进一步筛选不匹配的行。是要插入到目标表的值列表。

---

三、数据检索

1. SELECT

语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...FROM table_reference[WHERE where_condition][GROUP BY col_list][ORDER BY col_list][CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY col_list]][LIMIT [offset,] rows]

解释：

• [ALL | DISTINCT]: 可选项，用于指定返回的结果集中是否包含重复的行。ALL表示返回所有行（包括重复行），DISTINCT表示返回唯一的行（去除重复行）。
• select_expr, select_expr, …: 要选择的列或表达式列表。可以选择表中的所有列，也可以选择特定的列或使用表达式计算新的列。
• FROM table_reference: 指定要查询的表或表的引用。可以是单个表、多个表的联接，或者子查询。
• [WHERE where_condition]: 可选项，用于指定过滤条件。只有满足条件的行才会包含在结果集中。
• [GROUP BY col_list]: 可选项，用于指定分组的列。结果集将按照指定的列进行分组，并对每个组进行聚合操作。
• [ORDER BY col_list]: 可选项，用于指定排序的列。结果集将按照指定的列进行排序，默认是升序排序。
• [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]: 可选项，用于指定数据的分区和排序。CLUSTER BY用于指定数据的物理分区，DISTRIBUTE BY用于指定数据的逻辑分区，SORT BY用于指定数据的排序方式。
• [LIMIT [offset,] rows]: 可选项，用于限制结果集的行数。LIMIT后面可以指定要返回的行数，也可以指定偏移量和行数，表示从偏移量开始返回指定数量的行。

2. HAVING

示例：

SELECT col1 FROM table_name GROUP BY col1 HAVING SUM(col2) > 10

3. 正则表达式查询

启用正则表达式查询：

SET hive.support.quoted.identifiers=none;

示例：

查询除 ds 和 hr 之外的所有列。

SELECT `(ds|hr)?+.+` FROM sales

4. 连接查询

4.1 内联接

SELECT * FROM table1
JOIN table2 ON table1.column = table2.column;

4.2 左外连接

SELECT * FROM table1
LEFT OUTER JOIN table2 ON join_condition;

4.3 右外连接

SELECT * FROM table1 
RIGHT OUTER JOIN table2 ON join_condition;

4.4 全外连接

SELECT * FROM tabl

5. 联合查询

语法：

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

解释：

• select_statement: 代表一个完整的SELECT查询，包括选择要返回哪些列、从哪些表中检索数据以及可选地应用过滤条件等。
• UNION: 关键字表示进行联合操作。
• [ALL | DISTINCT]: 可选项，指定是否保留重复行。如果使用了DISTINCT关键字，则会删除重复行；如果使用了ALL关键字，则会保留所有行（包括重复）。默认使用DISTINCT关键字。

示例：

INSERT OVERWRITE TABLE target_table
SELECT name, id, category FROM source_table_1
UNION ALL
SELECT name, id, "Category159" FROM source_table_2

6. EXPLAIN

用于显示查询的执行计划。

语法：

EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query

解释：

• EXTENDED: 提供更详细的执行计划信息，包括任务分配、数据倾斜等。
• CBO: 使用Cost-Based Optimizer（成本优化器）生成执行计划。
• AST: 显示抽象语法树（Abstract Syntax Tree）表示形式。
• DEPENDENCY: 显示与其他对象之间存在依赖关系的对象列表。
• AUTHORIZATION: 显示涉及授权检查和权限验证过程中使用到的角色和资源列表。
• LOCKS: 显示在查询期间获取或释放锁定时涉及到哪些表或分区以及锁类型等相关信息。

示例：

EXPLAIN EXTENDED SELECT column1, column2 FROM table1 
UNION ALL
SELECT column1, column2 FROM table2;

---

总结

本文全面介绍了Hive的DDL、DML和数据检索功能，希望能够增加你对Hive的理解和应用能力。通过学习本文，你已经了解了如何使用Hive来定义数据结构、执行数据操作，并利用HiveQL进行数据查询和分析。无论是构建数据仓库还是进行数据分析，Hive都是一个强大而灵活的工具，帮助你处理大数据并获得有价值的信息。继续深入学习和实践，你将能够更好地利用Hive来满足你的数据处理和分析需求。

希望本教程对您有所帮助！如有任何疑问或问题，请随时在评论区留言。感谢阅读！

这篇关于HiveQL详解的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---