【详解】Hive操作语句整理

原创

大盘鸡拌面

发布于 2025-11-25 20:26:16

4220

Hive操作语句整理

Apache Hive 是一个基于 Hadoop 的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的 SQL 查询功能，可以将 SQL 语句转换为 MapReduce 任务进行运行。本文将对常用的 Hive 操作语句进行整理，帮助初学者快速掌握 Hive 的基本使用方法。

1. 创建数据库

在 Hive 中，可以通过以下命令创建数据库：

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

IF NOT EXISTS：如果数据库已存在，则不会执行创建操作。
COMMENT：给数据库添加注释。
LOCATION：指定数据库在 HDFS 上的存储位置。
WITH DBPROPERTIES：给数据库添加属性。

2. 删除数据库

删除数据库的命令如下：

DROP DATABASE [IF EXISTS] database_name [CASCADE | RESTRICT];

IF EXISTS：如果数据库不存在，则不会抛出错误。
CASCADE：级联删除，即删除数据库及其所有表。
RESTRICT（默认）：如果数据库中有表，则不允许删除数据库。

3. 创建表

创建表的基本语法如下：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  [(col_name data_type [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]
  [ROW FORMAT row_format]
  [STORED AS file_format]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)];

EXTERNAL：创建外部表，删除表时不会删除数据。
PARTITIONED BY：分区表，用于提高查询效率。
CLUSTERED BY：桶表，用于进一步优化查询性能。
ROW FORMAT：指定行格式。
STORED AS：指定存储格式。
LOCATION：指定表在 HDFS 上的存储位置。
TBLPROPERTIES：给表添加属性。

4. 删除表

删除表的命令如下：

DROP TABLE [IF EXISTS] table_name;

IF EXISTS：如果表不存在，则不会抛出错误。

5. 加载数据

加载数据到表中的命令如下：

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

LOCAL：从本地文件系统加载数据。
OVERWRITE：覆盖表中已有的数据。
PARTITION：指定分区信息。

6. 查询数据

查询数据的基本语法如下：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE boolean_expression]
[GROUP BY col_list]
[HAVING boolean_expression]
[ORDER BY col_list [ASC | DESC]]
[LIMIT number];

ALL（默认）：返回所有匹配的行。
DISTINCT：返回唯一不同的行。
WHERE：指定查询条件。
GROUP BY：按指定列分组。
HAVING：对分组后的结果进行过滤。
ORDER BY：按指定列排序。
LIMIT：限制返回的行数。

7. 插入数据

插入数据到表中的命令如下：

INSERT INTO TABLE table_name [PARTITION (partcol1=val1, partcol2=val2,...)]
VALUES (value1, value2, ...), (value1, value2, ...), ...;

PARTITION：指定分区信息。

8. 修改表结构

修改表结构的命令如下：

ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...);
ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE table_name ADD PARTITION (partcol1=val1, partcol2=val2,...) LOCATION 'hdfs_path';
ALTER TABLE table_name DROP PARTITION (partcol1=val1, partcol2=val2,...);

ADD COLUMNS：添加新列。
RENAME TO：重命名表。
ADD PARTITION：添加分区。
DROP PARTITION：删除分区。

9. 查看表信息

查看表信息的命令如下：

DESCRIBE [EXTENDED|FORMATTED] table_name;
SHOW CREATE TABLE table_name;

DESCRIBE：显示表的列信息。
SHOW CREATE TABLE：显示创建表的 SQL 语句。

10. 数据导出

将查询结果导出到文件的命令如下：

INSERT OVERWRITE DIRECTORY 'hdfs_path'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT * FROM table_name;

DIRECTORY：指定导出的目录。
ROW FORMAT DELIMITED：指定行格式。

Apache Hive 是一个基于 Hadoop 的数据仓库工具，用于处理大规模数据集。下面是一些常见的 Hive 操作语句及其在实际场景中的应用示例。

1. 创建数据库

在开始任何数据操作之前，通常需要创建一个数据库来组织表和数据。

CREATE DATABASE IF NOT EXISTS sales;

2. 使用数据库

选择要操作的数据库。

USE sales;

3. 创建表

创建一个表来存储销售数据。

CREATE TABLE IF NOT EXISTS sales_data (
    sale_id INT,
    product_id INT,
    customer_id INT,
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

4. 加载数据

将外部数据文件加载到表中。

LOAD DATA INPATH '/user/hadoop/sales_data.csv' INTO TABLE sales_data;

5. 查询数据

查询表中的数据。

SELECT * FROM sales_data LIMIT 10;

6. 过滤数据

根据条件过滤数据。

SELECT * FROM sales_data WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

7. 分组和聚合

对数据进行分组并计算总销售额。

SELECT product_id, SUM(quantity * price) AS total_sales
FROM sales_data
GROUP BY product_id
ORDER BY total_sales DESC;

8. 连接表

假设有一个产品表 products，包含 product_id 和 product_name，我们可以将两个表连接起来。

CREATE TABLE IF NOT EXISTS products (
    product_id INT,
    product_name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- 加载产品数据
LOAD DATA INPATH '/user/hadoop/products.csv' INTO TABLE products;

-- 连接表并查询
SELECT p.product_name, s.total_sales
FROM (
    SELECT product_id, SUM(quantity * price) AS total_sales
    FROM sales_data
    GROUP BY product_id
) s
JOIN products p ON s.product_id = p.product_id
ORDER BY s.total_sales DESC;

9. 插入数据

向表中插入新数据。

INSERT INTO sales_data (sale_id, product_id, customer_id, sale_date, quantity, price)
VALUES (1001, 2001, 3001, '2023-01-01', 5, 100.00);

10. 更新数据

更新表中的数据（Hive 不支持直接更新，但可以通过插入新数据来实现）。

INSERT OVERWRITE TABLE sales_data
SELECT sale_id, product_id, customer_id, sale_date, 
       CASE WHEN sale_id = 1001 THEN 10 ELSE quantity END AS quantity, 
       price
FROM sales_data;

11. 删除数据

删除表中的数据（Hive 不支持直接删除，但可以通过插入新数据来实现）。

INSERT OVERWRITE TABLE sales_data
SELECT sale_id, product_id, customer_id, sale_date, quantity, price
FROM sales_data
WHERE sale_id != 1001;

12. 删除表

删除不再需要的表。

DROP TABLE IF EXISTS sales_data;

Apache Hive 是一个基于 Hadoop 的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的 SQL 查询功能，可以将 SQL 语句转换为 MapReduce 任务进行运行。下面是一些常用的 Hive 操作语句的整理和解释：

1. 创建数据库

CREATE DATABASE db_name;

说明：创建一个新的数据库 db_name。

2. 使用数据库

USE db_name;

说明：选择当前操作的数据库为 db_name。

3. 创建表

3.1 创建内部表（Managed Table）

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
) STORED AS file_format;

说明：创建一个内部表 table_name，并指定列名和数据类型，以及存储格式（如 TEXTFILE, ORC, PARQUET 等）。
特点：Hive 管理数据的生命周期，删除表时会删除数据。

3.2 创建外部表（External Table）

CREATE EXTERNAL TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
) LOCATION 'hdfs_path';

说明：创建一个外部表 table_name，并指定列名和数据类型，以及数据在 HDFS 上的位置。
特点：数据不由 Hive 管理，删除表时不会删除数据。

4. 加载数据

4.1 从本地文件加载数据

LOAD DATA LOCAL INPATH 'local_path' INTO TABLE table_name;

说明：从本地文件系统中加载数据到表 table_name 中。

4.2 从 HDFS 加载数据

LOAD DATA INPATH 'hdfs_path' INTO TABLE table_name;

说明：从 HDFS 中加载数据到表 table_name 中。

5. 插入数据

5.1 插入单行数据

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

说明：向表 table_name 中插入一行数据。

5.2 插入多行数据

INSERT INTO table_name (column1, column2, ...)
VALUES (value1_1, value1_2, ...),
       (value2_1, value2_2, ...);

说明：向表 table_name 中插入多行数据。

5.3 从查询结果插入数据

INSERT INTO table_name SELECT * FROM source_table WHERE condition;

说明：将 source_table 中满足条件的数据插入到 table_name 中。

6. 查询数据

SELECT column1, column2, ... FROM table_name WHERE condition;

说明：从表 table_name 中查询满足条件的数据。

7. 更新数据

Hive 不支持直接更新数据，但可以通过以下方式实现类似的效果：

INSERT OVERWRITE TABLE table_name
SELECT column1, column2, ...
FROM table_name
WHERE condition;

说明：通过 INSERT OVERWRITE 语句覆盖表中的数据。

8. 删除数据

Hive 不支持直接删除数据，但可以通过以下方式实现类似的效果：

INSERT OVERWRITE TABLE table_name
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

说明：通过 INSERT OVERWRITE 语句过滤掉不需要的数据。

9. 删除表

DROP TABLE [IF EXISTS] table_name;

说明：删除表 table_name。如果表不存在且使用了 IF EXISTS，则不会报错。

10. 显示表信息

10.1 显示所有表

SHOW TABLES;

说明：显示当前数据库中的所有表。

10.2 显示表结构

DESCRIBE table_name;

说明：显示表 table_name 的结构信息。

11. 分区表

11.1 创建分区表

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
)
PARTITIONED BY (partition_column datatype)
STORED AS file_format;

说明：创建一个分区表 table_name，并指定分区列 partition_column 和存储格式。

11.2 添加分区

ALTER TABLE table_name ADD PARTITION (partition_column='value');

说明：为表 table_name 添加一个新的分区。

11.3 删除分区

ALTER TABLE table_name DROP PARTITION (partition_column='value');

说明：删除表 table_name 中的一个分区。

12. 桶表

12.1 创建桶表

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
)
CLUSTERED BY (bucket_column) INTO num_buckets BUCKETS
STORED AS file_format;

说明：创建一个桶表 table_name，并指定桶列 bucket_column 和桶的数量 num_buckets。

13. 视图

13.1 创建视图

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

说明：创建一个视图 view_name，视图是基于查询结果的虚拟表。

13.2 删除视图

DROP VIEW [IF EXISTS] view_name;

说明：删除视图 view_name。

这些是 Hive 中常用的一些操作语句。希望这些内容对你有帮助！如果你有任何具体的问题或需要进一步的解释，请随时告诉我。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S16