hive操作数据表之DML操作

hive数据表 DML 操作全指南：从数据库到表的核心操作

Hive 作为数据仓库工具，其 DML（数据操纵语言）操作与传统 SQL 高度兼容，但也存在适应分布式存储的特殊语法（如分区、外部表）。本文详细讲解 Hive 中数据库和数据表的核心操作，包括创建、查询、修改和删除，帮助开发者快速上手 Hive 数据管理。

数据库操作

数据库在 Hive 中主要用于隔离不同业务的数据表，类似传统数据库的 “命名空间”。所有数据库的元数据存储在 Metastore 中，实际数据存储在 HDFS 上。

查看数据库列表

使用 show databases 查看所有数据库，默认数据库为 default：

hive (default)> show databases;  
OK  
default  
study_hive  -- 已创建的自定义数据库  
Time taken: 0.123 seconds  

创建数据库

通过 CREATE DATABASE 语句创建数据库，支持指定存储路径、注释和属性。

基本语法

CREATE DATABASE [IF NOT EXISTS] database_name  
[COMMENT '数据库注释']  
[LOCATION 'hdfs_path']  -- 指定数据库在 HDFS 上的存储路径  
[WITH DBPROPERTIES ('key'='value', ...)];  -- 自定义数据库属性  

实操示例

-- 创建数据库（若不存在）  
CREATE DATABASE IF NOT EXISTS study_hive  
COMMENT '用于学习 Hive 的数据库'  
LOCATION '/user/hive/warehouse/study_hive.db';  -- 显式指定 HDFS 路径  

-- 若不指定 LOCATION，默认路径为：hdfs://<namenode>/user/hive/warehouse/<database_name>.db  

查看数据库详情

使用 desc database 或 desc database extended 查看数据库的元数据信息：

-- 基本信息（名称、路径、所有者）  
hive (default)> desc database study_hive;  
OK  
db_name       comment                location                                            owner_name  owner_type  parameters  
study_hive    用于学习 Hive 的数据库  hdfs://localhost:9000/user/hive/warehouse/study_hive.db  zhanghe     USER        {}  

-- 扩展信息（包含更多属性）  
hive (default)> desc database extended study_hive;  
-- 输出结果包含数据库创建时间、参数等详细信息  

切换数据库

使用 use database_name 切换当前操作的数据库：

hive (default)> use study_hive;  
OK  
hive (study_hive)>  -- 提示符显示当前数据库为 study_hive  

删除数据库

通过 DROP DATABASE 语句删除数据库，需注意数据库是否为空。

基本语法

DROP DATABASE [IF EXISTS] database_name  
[CASCADE];  -- 可选，强制删除非空数据库（删除库中所有表）  

实操示例

-- 删除空数据库（数据库中无表）  
hive (study_hive)> drop database if exists study_hive_empty;  

-- 删除非空数据库（需加 CASCADE，否则报错）  
hive (study_hive)> drop database study_hive cascade;  
-- 警告：CASCADE 会删除数据库中所有表，操作不可逆！  

注意事项

若数据库非空且未加CASCADE，删除会报错：

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database study_hive is not empty.)  

数据表操作

数据表是 Hive 存储和管理数据的核心单元，支持内部表、外部表、分区表、分桶表等多种类型。以下讲解通用表操作。

创建表

Hive 表创建语法灵活，需指定列定义、存储格式、存储路径等关键信息。

基本语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name  
(  
  col_name data_type [COMMENT '列注释'],  
  ...  -- 更多列定义  
)  
[COMMENT '表注释']  
[PARTITIONED BY (col_name data_type [COMMENT '分区列注释'], ...)]  -- 分区表配置  
[CLUSTERED BY (col_name, ...) INTO num_buckets BUCKETS]  -- 分桶表配置  
[ROW FORMAT DELIMITED  
  FIELDS TERMINATED BY ','  -- 列分隔符  
  LINES TERMINATED BY '\n'  -- 行分隔符  
]  
[STORED AS file_format]  -- 存储格式（如 TEXTFILE、PARQUET）  
[LOCATION 'hdfs_path']  -- 表在 HDFS 上的存储路径  
[TBLPROPERTIES ('key'='value', ...)];  -- 表属性（如是否压缩）  

关键参数说明

EXTERNAL：创建外部表（数据不受 Hive 管理，删除表不删除数据）；不指定则为内部表（删除表时数据一同删除）。
PARTITIONED BY：创建分区表，按指定列（如 dt 日期）拆分数据，提升查询效率。
ROW FORMAT：定义文本文件中列和行的分隔符（默认不指定时需通过 SerDe 解析）。
STORED AS：指定存储格式，推荐列式存储（如 PARQUET、ORC）提升查询性能。

实操示例：创建内部表

-- 在 study_hive 数据库中创建用户表  
use study_hive;  

CREATE TABLE IF NOT EXISTS user_info (  
  id INT COMMENT '用户ID',  
  name STRING COMMENT '用户名',  
  age INT COMMENT '年龄',  
  register_date STRING COMMENT '注册日期'  
)  
COMMENT '用户基本信息表'  
ROW FORMAT DELIMITED  
  FIELDS TERMINATED BY ','  -- 列用逗号分隔  
  LINES TERMINATED BY '\n'  -- 行用换行分隔  
STORED AS TEXTFILE  -- 存储为文本文件  
LOCATION '/user/hive/warehouse/study_hive.db/user_info';  -- 显式指定路径  

查看表信息

通过 show tables 和 desc 语句查看表列表和结构。

查看当前数据库的表列表

hive (study_hive)> show tables;  
OK  
user_info  -- 已创建的表  

查看表结构

-- 基本表结构（列名、类型、注释）  
hive (study_hive)> desc user_info;  
OK  
id                int                  用户ID                
name              string               用户名                
age               int                  年龄                  
register_date     string               注册日期              

-- 扩展信息（包含存储格式、路径等）  
hive (study_hive)> desc formatted user_info;  
-- 输出包含表类型（内部表/外部表）、存储格式、HDFS路径等详细信息  

修改表

Hive 支持修改表名、列定义、添加列等操作，通过 ALTER TABLE 语句实现。

（1）修改表名

-- 将表 user_info 重命名为 user_detail  
ALTER TABLE user_info RENAME TO user_detail;  

（2）修改列定义（名称、类型、位置）

-- 语法：ALTER TABLE table_name CHANGE [COLUMN] 旧列名 新列名 新类型 [COMMENT '新注释']  
ALTER TABLE user_detail CHANGE COLUMN id user_id INT COMMENT '用户ID（修改后）';  

（3）添加列

-- 语法：ALTER TABLE table_name ADD COLUMNS (新列名 类型 [COMMENT '注释'], ...)  
ALTER TABLE user_detail ADD COLUMNS (  
  gender STRING COMMENT '性别（男/女）',  
  city STRING COMMENT '所在城市'  
);  

（4）删除列（谨慎使用）

Hive 不直接支持 DROP COLUMN，需通过替换表结构实现（会覆盖原有数据，谨慎操作）：

-- 替换表结构（仅保留指定列，原有数据需兼容新结构）  
ALTER TABLE user_detail REPLACE COLUMNS (  
  user_id INT COMMENT '用户ID',  
  name STRING COMMENT '用户名'  
);  

删除表

使用 DROP TABLE 语句删除表，需注意内部表和外部表的区别。

基本语法

DROP TABLE [IF EXISTS] table_name;  

实操示例

-- 删除表（若存在）  
DROP TABLE IF EXISTS user_detail;  

注意事项

内部表：删除表时会同时删除 HDFS 上的数据和元数据；
外部表：删除表仅删除元数据，HDFS 上的数据不会被删除（外部表数据由用户自行管理）。

核心概念与注意事项

内部表 vs 外部表：
- 内部表（默认）：Hive 完全管理数据生命周期，删除表即删除数据，适合临时数据或中间结果；
- 外部表（CREATE EXTERNAL TABLE）：数据存储在用户指定的 HDFS 路径，删除表不影响数据，适合共享数据或原始日志。
HDFS 存储路径：
- 数据库默认路径：/user/hive/warehouse/<db_name>.db；
- 表默认路径：/user/hive/warehouse/<db_name>.db/<table_name>；
- 可通过 LOCATION 显式指定路径，灵活管理数据存储。
分区与分桶：
- 分区表（PARTITIONED BY）：按列拆分数据（如按日期 dt=2023-10-01），减少查询扫描范围；
- 分桶表（CLUSTERED BY）：按列哈希分桶，适合抽样查询和高效 Join，需配合 INSERT 语句加载数据。