Hive SQL 完全指南：DDL与DML深度解析

Hive SQL 完全指南：DDL与DML深度解析

• • 引言
  • 一、Hive SQL语言分类概览
  • • 1.1 SQL语言分类
    • 1.2 主要区别
  • 二、DDL（数据定义语言）详解
  • • 2.1 数据库操作
    • • 2.1.1 创建数据库
      • 2.1.2 查看和切换数据库
      • 2.1.3 修改数据库
      • 2.1.4 删除数据库
    • 2.2 表操作
    • • 2.2.1 创建内部表（托管表）
      • 2.2.2 创建外部表
      • 2.2.3 分区表
      • 2.2.4 分桶表
      • 2.2.5 查看表信息
      • 2.2.6 修改表
      • 2.2.7 删除表
    • 2.3 视图操作
    • 2.4 索引操作
  • 三、DML（数据操纵语言）详解
  • • 3.1 加载数据
    • • 3.1.1 LOAD DATA
      • 3.1.2 从查询插入
    • 3.2 动态分区插入
    • 3.3 更新数据（ACID表）
    • 3.4 删除数据
    • 3.5 MERGE（合并）
    • 3.6 导出数据
  • 四、高级操作与优化
  • • 4.1 CTE（公用表表达式）
    • 4.2 复杂数据类型
    • 4.3 自定义函数
  • 五、DDL与DML对比总结
  • 六、最佳实践
  • • 6.1 命名规范
    • 6.2 存储格式选择
    • 6.3 分区策略
    • 6.4 性能优化参数
  • 七、总结

引言

Hive是基于Hadoop的数据仓库工具，它将结构化的数据文件映射为数据库表，并提供类SQL查询功能。理解Hive SQL中的DDL（数据定义语言）和DML（数据操纵语言）是掌握Hive的核心基础。本文将系统性地介绍Hive SQL的两大语言类别，并通过丰富的示例帮助读者深入理解。

一、Hive SQL语言分类概览

1.1 SQL语言分类

#mermaid-svg-1H2i4C3v5kIoZpCf{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-1H2i4C3v5kIoZpCf .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-1H2i4C3v5kIoZpCf .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-1H2i4C3v5kIoZpCf .error-icon{fill:#552222;}#mermaid-svg-1H2i4C3v5kIoZpCf .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-1H2i4C3v5kIoZpCf .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-1H2i4C3v5kIoZpCf .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-1H2i4C3v5kIoZpCf .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-1H2i4C3v5kIoZpCf .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-1H2i4C3v5kIoZpCf .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-1H2i4C3v5kIoZpCf .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-1H2i4C3v5kIoZpCf .marker{fill:#333333;stroke:#333333;}#mermaid-svg-1H2i4C3v5kIoZpCf .marker.cross{stroke:#333333;}#mermaid-svg-1H2i4C3v5kIoZpCf svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-1H2i4C3v5kIoZpCf p{margin:0;}#mermaid-svg-1H2i4C3v5kIoZpCf .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-1H2i4C3v5kIoZpCf .cluster-label text{fill:#333;}#mermaid-svg-1H2i4C3v5kIoZpCf .cluster-label span{color:#333;}#mermaid-svg-1H2i4C3v5kIoZpCf .cluster-label span p{background-color:transparent;}#mermaid-svg-1H2i4C3v5kIoZpCf .label text,#mermaid-svg-1H2i4C3v5kIoZpCf span{fill:#333;color:#333;}#mermaid-svg-1H2i4C3v5kIoZpCf .node rect,#mermaid-svg-1H2i4C3v5kIoZpCf .node circle,#mermaid-svg-1H2i4C3v5kIoZpCf .node ellipse,#mermaid-svg-1H2i4C3v5kIoZpCf .node polygon,#mermaid-svg-1H2i4C3v5kIoZpCf .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-1H2i4C3v5kIoZpCf .rough-node .label text,#mermaid-svg-1H2i4C3v5kIoZpCf .node .label text,#mermaid-svg-1H2i4C3v5kIoZpCf .image-shape .label,#mermaid-svg-1H2i4C3v5kIoZpCf .icon-shape .label{text-anchor:middle;}#mermaid-svg-1H2i4C3v5kIoZpCf .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-1H2i4C3v5kIoZpCf .rough-node .label,#mermaid-svg-1H2i4C3v5kIoZpCf .node .label,#mermaid-svg-1H2i4C3v5kIoZpCf .image-shape .label,#mermaid-svg-1H2i4C3v5kIoZpCf .icon-shape .label{text-align:center;}#mermaid-svg-1H2i4C3v5kIoZpCf .node.clickable{cursor:pointer;}#mermaid-svg-1H2i4C3v5kIoZpCf .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-1H2i4C3v5kIoZpCf .arrowheadPath{fill:#333333;}#mermaid-svg-1H2i4C3v5kIoZpCf .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-1H2i4C3v5kIoZpCf .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-1H2i4C3v5kIoZpCf .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-1H2i4C3v5kIoZpCf .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-1H2i4C3v5kIoZpCf .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-1H2i4C3v5kIoZpCf .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-1H2i4C3v5kIoZpCf .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-1H2i4C3v5kIoZpCf .cluster text{fill:#333;}#mermaid-svg-1H2i4C3v5kIoZpCf .cluster span{color:#333;}#mermaid-svg-1H2i4C3v5kIoZpCf div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-1H2i4C3v5kIoZpCf .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-1H2i4C3v5kIoZpCf rect.text{fill:none;stroke-width:0;}#mermaid-svg-1H2i4C3v5kIoZpCf .icon-shape,#mermaid-svg-1H2i4C3v5kIoZpCf .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-1H2i4C3v5kIoZpCf .icon-shape p,#mermaid-svg-1H2i4C3v5kIoZpCf .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-1H2i4C3v5kIoZpCf .icon-shape rect,#mermaid-svg-1H2i4C3v5kIoZpCf .image-shape rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-1H2i4C3v5kIoZpCf .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-1H2i4C3v5kIoZpCf .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-1H2i4C3v5kIoZpCf :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

Hive SQL

DDL数据定义语言

DML数据操纵语言

DQL数据查询语言

CREATE创建

ALTER修改

DROP删除

TRUNCATE截断

LOAD加载数据

INSERT插入数据

UPDATE更新数据

DELETE删除数据

MERGE合并

SELECT查询

1.2 主要区别

二、DDL（数据定义语言）详解

DDL用于创建、修改和删除数据库中的对象，如数据库、表、视图、索引等。

2.1 数据库操作

2.1.1 创建数据库

— 基础创建
CREATE DATABASE IF NOT EXISTS mydb;

— 指定位置和注释
CREATE DATABASE IF NOT EXISTS mydb
COMMENT 'This is my database'
LOCATION '/user/hive/warehouse/mydb.db'
WITH DBPROPERTIES ('creator'='zhangsan', 'date'='2024-01-01');

— 创建时指定字符集
CREATE DATABASE mydb
CHARACTER SET 'latin1'
COLLATE 'latin1_bin';

2.1.2 查看和切换数据库

— 查看所有数据库
SHOW DATABASES;
SHOW DATABASES LIKE 'my*';

— 查看数据库详细信息
DESCRIBE DATABASE mydb;
DESCRIBE DATABASE EXTENDED mydb; — 显示更多属性

— 切换当前数据库
USE mydb;

— 查看当前数据库
SELECT current_database();

2.1.3 修改数据库

— 修改数据库属性
ALTER DATABASE mydb
SET DBPROPERTIES ('edited-by'='lisi');

— 修改所有者
ALTER DATABASE mydb
SET OWNER USER 'lisi';

— 修改位置（仅限托管数据库）
ALTER DATABASE mydb
SET LOCATION '/new/path/to/database';

2.1.4 删除数据库

— 删除空数据库
DROP DATABASE IF EXISTS mydb;

— 级联删除（删除数据库及其所有表）
DROP DATABASE IF EXISTS mydb CASCADE;

— 限制删除（默认，数据库非空时拒绝删除）
DROP DATABASE IF EXISTS mydb RESTRICT;

2.2 表操作

2.2.1 创建内部表（托管表）

— 创建内部表
CREATE TABLE IF NOT EXISTS employees (
emp_id INT COMMENT 'Employee ID',
emp_name STRING COMMENT 'Employee Name',
emp_salary DECIMAL(10,2) COMMENT 'Monthly Salary',
hire_date DATE COMMENT 'Hire Date',
department STRING COMMENT 'Department',
is_active BOOLEAN COMMENT 'Active Status'
)
COMMENT 'Employee Information Table'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\\n'
STORED AS TEXTFILE
TBLPROPERTIES ('comment'='Main employee table', 'creator'='admin');

— 使用LIKE创建相同结构的表
CREATE TABLE employees_copy LIKE employees;

2.2.2 创建外部表

— 创建外部表（数据存储在指定位置）
CREATE EXTERNAL TABLE IF NOT EXISTS external_employees (
emp_id INT,
emp_name STRING,
emp_salary DECIMAL(10,2)
)
COMMENT 'External Employee Table'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION '/data/external/employees'
TBLPROPERTIES ('external.table.purge'='true'); — 删除表时删除数据

内部表 vs 外部表

2.2.3 分区表

— 创建分区表
CREATE TABLE IF NOT EXISTS partitioned_employees (
emp_id INT,
emp_name STRING,
emp_salary DECIMAL(10,2),
hire_date DATE
)
PARTITIONED BY (department STRING, hire_year INT) — 分区字段
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS PARQUET; — 推荐使用列式存储

— 添加分区
ALTER TABLE partitioned_employees
ADD PARTITION (department='IT', hire_year=2024)
LOCATION '/user/hive/warehouse/mydb.db/partitioned_employees/dept=IT/year=2024';

— 动态分区插入
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

INSERT INTO TABLE partitioned_employees
PARTITION (department, hire_year)
SELECT emp_id, emp_name, emp_salary, hire_date, department, YEAR(hire_date)
FROM staging_employees;

2.2.4 分桶表

— 创建分桶表
CREATE TABLE IF NOT EXISTS bucketed_employees (
emp_id INT,
emp_name STRING,
emp_salary DECIMAL(10,2),
department STRING
)
CLUSTERED BY (emp_id) INTO 16 BUCKETS — 按emp_id分16个桶
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS ORC
TBLPROPERTIES ('transactional'='true'); — 支持ACID

— 设置分桶相关参数
SET hive.enforce.bucketing = true;
SET hive.enforce.sorting = true;

2.2.5 查看表信息

— 查看所有表
SHOW TABLES;
SHOW TABLES IN mydb LIKE 'emp*';

— 查看表结构
DESCRIBE employees;
DESC FORMATTED employees; — 详细表信息
DESC EXTENDED employees; — 扩展信息

— 查看分区
SHOW PARTITIONS employees;
SHOW PARTITIONS employees PARTITION(department='IT');

— 查看表属性
SHOW TBLPROPERTIES employees;
SHOW TBLPROPERTIES employees ('comment');

2.2.6 修改表

— 重命名表
ALTER TABLE employees RENAME TO staff;

— 添加列
ALTER TABLE employees ADD COLUMNS (
email STRING COMMENT 'Email address',
phone STRING COMMENT 'Phone number'
);

— 修改列
ALTER TABLE employees CHANGE COLUMN emp_name employee_name STRING
COMMENT 'Full Name' AFTER emp_id; — 移动位置

— 替换所有列
ALTER TABLE employees REPLACE COLUMNS (
emp_id INT,
full_name STRING,
salary DECIMAL(10,2)
);

— 修改表属性
ALTER TABLE employees SET TBLPROPERTIES (
'comment' = 'Updated employee table',
'auto.purge' = 'true'
);

2.2.7 删除表

— 删除内部表
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS employees PURGE; — 直接删除，不进回收站

— 删除外部表（只删除元数据）
DROP TABLE external_employees;

— 清空表数据
TRUNCATE TABLE employees; — 只适用于内部表

2.3 视图操作

— 创建视图
CREATE VIEW IF NOT EXISTS it_employees
COMMENT 'IT Department Employees'
AS
SELECT emp_id, emp_name, emp_salary
FROM employees
WHERE department = 'IT';

— 创建物化视图（Hive 3.0+）
CREATE MATERIALIZED VIEW mv_dept_stats
AS
SELECT department, COUNT(*) as emp_count, AVG(emp_salary) as avg_salary
FROM employees
GROUP BY department;

— 查看视图
SHOW TABLES; — 视图也显示在列表中
DESC FORMATTED it_employees;

— 修改视图
ALTER VIEW it_employees AS
SELECT emp_id, emp_name, emp_salary, hire_date
FROM employees
WHERE department = 'IT';

— 删除视图
DROP VIEW IF EXISTS it_employees;

2.4 索引操作

— 创建索引
CREATE INDEX emp_index
ON TABLE employees (emp_id)
AS 'COMPACT' — 索引类型：COMPACT或BITMAP
WITH DEFERRED REBUILD; — 延迟重建

— 重建索引
ALTER INDEX emp_index ON employees REBUILD;

— 显示索引
SHOW INDEX ON employees;
SHOW FORMATTED INDEX ON employees;

— 删除索引
DROP INDEX emp_index ON employees;

三、DML（数据操纵语言）详解

DML用于操作表中的数据，包括加载、插入、更新、删除等。

3.1 加载数据

3.1.1 LOAD DATA

— 从本地文件系统加载（复制）
LOAD DATA LOCAL INPATH '/home/user/employees.csv'
OVERWRITE INTO TABLE employees; — OVERWRITE覆盖，不加则追加

— 从HDFS加载（移动文件）
LOAD DATA INPATH '/data/employees.csv'
INTO TABLE employees;

— 加载到分区
LOAD DATA LOCAL INPATH '/home/user/it_employees.csv'
INTO TABLE partitioned_employees
PARTITION (department='IT', hire_year=2024);

3.1.2 从查询插入

— 标准插入
INSERT INTO TABLE employees
VALUES (1, '张三', 15000.00, '2024-01-01', 'IT', true);

— 从查询插入
INSERT INTO TABLE employees
SELECT * FROM staging_employees WHERE is_active = true;

— 覆盖插入
INSERT OVERWRITE TABLE employees
SELECT * FROM staging_employees WHERE department = 'IT';

— 多表插入
FROM staging_employees se
INSERT OVERWRITE TABLE employees
SELECT se.* WHERE se.is_active = true
INSERT INTO TABLE inactive_employees
SELECT se.* WHERE se.is_active = false;

3.2 动态分区插入

— 启用动态分区
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict; — 允许所有分区动态
SET hive.exec.max.dynamic.partitions = 1000; — 最大动态分区数

— 动态分区插入
INSERT OVERWRITE TABLE partitioned_employees
PARTITION (department, hire_year)
SELECT
emp_id, emp_name, emp_salary, hire_date,
department,
YEAR(hire_date) as hire_year
FROM staging_employees;

— 混合分区（静态+动态）
INSERT INTO TABLE partitioned_employees
PARTITION (department='IT', hire_year) — 静态指定IT，动态指定年份
SELECT emp_id, emp_name, emp_salary, hire_date, YEAR(hire_date)
FROM staging_employees
WHERE department = 'IT';

3.3 更新数据（ACID表）

— 需要启用事务
SET hive.support.concurrency = true;
SET hive.enforce.bucketing = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

— 创建ACID表
CREATE TABLE acid_employees (
emp_id INT,
emp_name STRING,
emp_salary DECIMAL(10,2)
)
CLUSTERED BY (emp_id) INTO 4 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

— 更新数据
UPDATE acid_employees
SET emp_salary = emp_salary * 1.1
WHERE emp_id = 1001;

3.4 删除数据

— 删除数据（ACID表）
DELETE FROM acid_employees WHERE emp_id = 1001;

— 删除整个表数据（非ACID表）
TRUNCATE TABLE employees; — 快速清空

— 基于条件的删除（非ACID表，需要OVERWRITE）
INSERT OVERWRITE TABLE employees
SELECT * FROM employees WHERE is_active = true;

3.5 MERGE（合并）

— MERGE INTO (Hive 2.2+)
MERGE INTO employees AS target
USING staging_employees AS source
ON target.emp_id = source.emp_id
WHEN MATCHED THEN
UPDATE SET target.emp_name = source.emp_name,
target.emp_salary = source.emp_salary
WHEN NOT MATCHED THEN
INSERT VALUES (source.emp_id, source.emp_name,
source.emp_salary, source.hire_date,
source.department, source.is_active);

3.6 导出数据

— 导出到本地文件系统
INSERT OVERWRITE LOCAL DIRECTORY '/home/user/export'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
SELECT * FROM employees;

— 导出到HDFS
INSERT OVERWRITE DIRECTORY '/data/export/employees'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\\t'
SELECT emp_id, emp_name, emp_salary FROM employees;

四、高级操作与优化

4.1 CTE（公用表表达式）

— 使用WITH子句
WITH dept_stats AS (
SELECT department, COUNT(*) as emp_count, AVG(emp_salary) as avg_salary
FROM employees
GROUP BY department
),
high_salary_depts AS (
SELECT department FROM dept_stats WHERE avg_salary > 10000
)
SELECT e.*
FROM employees e
JOIN high_salary_depts h ON e.department = h.department;

4.2 复杂数据类型

— 创建包含复杂类型的表
CREATE TABLE complex_table (
emp_id INT,
emp_info STRUCT<name:STRING, age:INT>, — 结构体
phone_numbers ARRAY<STRING>, — 数组
properties MAP<STRING, STRING> — 映射
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;

— 插入复杂数据
INSERT INTO complex_table
SELECT
1,
named_struct('name', '张三', 'age', 30),
array('13800000000', '13900000000'),
map('height', '175cm', 'weight', '70kg')
FROM dummy LIMIT 1;

— 查询复杂数据
SELECT
emp_info.name,
phone_numbers[0],
properties['height']
FROM complex_table;

4.3 自定义函数

— 创建临时函数
ADD JAR /path/to/my–udf.jar;
CREATE TEMPORARY FUNCTION my_udf AS 'com.example.MyUDF';

— 使用自定义函数
SELECT my_udf(emp_name) FROM employees;

— 创建永久函数
CREATE FUNCTION my_permanent_udf
AS 'com.example.MyUDF'
USING JAR 'hdfs:///udfs/my-udf.jar';

五、DDL与DML对比总结

六、最佳实践

6.1 命名规范

— 数据库名：业务名_环境
CREATE DATABASE sales_prod;
CREATE DATABASE sales_dev;

— 表名：主题_维度/事实_层次
CREATE TABLE dim_customer;
CREATE TABLE fact_sales_daily;

— 列名：小写字母+下划线
CREATE TABLE user_info (
user_id BIGINT,
user_name STRING,
created_time TIMESTAMP
);

6.2 存储格式选择

6.3 分区策略

— 按日期分区（推荐）
CREATE TABLE fact_sales (
product_id INT,
amount DECIMAL(10,2)
)
PARTITIONED BY (sale_date STRING);

— 按多个维度分区
CREATE TABLE fact_logs (
log_id INT,
content STRING
)
PARTITIONED BY (log_date STRING, log_type STRING);

— 分区数建议：每个分区文件大小约1-2GB

6.4 性能优化参数

— 常用优化设置
SET hive.exec.parallel = true; — 并行执行
SET hive.exec.parallel.thread.number = 8;
SET hive.auto.convert.join = true; — 自动MapJoin
SET hive.mapjoin.smalltable.filesize = 25000000; — 小表阈值
SET hive.optimize.skewjoin = true; — 倾斜优化
SET hive.merge.mapfiles = true; — 合并小文件
SET hive.merge.mapredfiles = true;
SET hive.merge.size.per.task = 256000000; — 256MB

七、总结

学习路径建议：

掌握DDL和DML是使用Hive的基础，结合查询优化和性能调优，可以构建高效的数据仓库解决方案。