Hive操作语法

Hive 本质是将 SQL 翻译成 MapReduce / Tez / Spark 作业执行，底层存储在 HDFS 上。与 MySQL 最大的区别在于：写入一次、读取多次，不支持行级更新删除，延迟高但吞吐量大。

与 MySQL 的核心差异

特性 MySQL Hive
事务/ACID 支持 默认不支持（开启 ACID 后有限支持）
UPDATE / DELETE 支持 默认不支持（ACID 表支持）
自增主键 AUTO_INCREMENT 不支持
索引 B+Tree 索引 无传统索引（依赖分区 / 分桶剪枝）
数据写入 INSERT 实时 LOAD DATA 或 INSERT OVERWRITE
执行引擎 本地存储引擎 MapReduce / Tez / Spark
延迟 毫秒级 秒~分钟级
数据格式 行存 TextFile / ORC / Parquet 等
NULL 比较 IS NULL IS NULL 或使用 NVL()

数据库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建数据库，可指定 HDFS 存储路径
CREATE DATABASE [IF NOT EXISTS] db_name
[LOCATION '/user/hive/warehouse/db_name.db'];

-- 查看所有数据库
SHOW DATABASES;

-- 切换数据库
USE db_name;

-- 查看数据库详情（包含 HDFS 路径）
DESCRIBE DATABASE EXTENDED db_name;

-- 删除库（CASCADE 级联删除库内所有表）
DROP DATABASE [IF EXISTS] db_name [CASCADE];

Hive 中每个数据库在 HDFS 上对应一个以 .db 结尾的目录，默认路径为 /user/hive/warehouse/。

表操作

建表

1
2
3
4
5
6
7
8
9
10
11
-- 基础建表：必须指定分隔符和存储格式
CREATE TABLE IF NOT EXISTS table_name (
    id      INT,
    name    STRING,
    salary  DOUBLE,
    dt      STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'          -- 字段分隔符
LINES TERMINATED BY '\n'           -- 行分隔符
STORED AS TEXTFILE;                -- 存储格式

与 MySQL 的差异：

  • 没有 AUTO_INCREMENT、PRIMARY KEY、FOREIGN KEY
  • 必须声明 STORED AS 存储格式
  • 字符串类型统一用 STRING，不需要指定长度

复制表结构

1
2
3
4
5
6
-- 只复制结构，不复制数据
CREATE TABLE new_table LIKE old_table;

-- 复制结构 + 数据（CTAS）
CREATE TABLE new_table AS
SELECT * FROM old_table;

修改表

1
2
3
4
5
6
7
8
9
10
11
-- 重命名表
ALTER TABLE old_name RENAME TO new_name;

-- 新增列（只能加在末尾）
ALTER TABLE table_name ADD COLUMNS (new_col STRING);

-- 修改列（重命名 + 类型，不能缩短宽度）
ALTER TABLE table_name CHANGE old_col new_col STRING;

-- 替换所有列定义（危险，会重置列）
ALTER TABLE table_name REPLACE COLUMNS (id INT, name STRING);

内部表 vs 外部表

这是 Hive 最重要的概念之一，MySQL 中没有对应概念。

1
2
3
4
5
6
7
8
9
10
11
12
-- 内部表（Managed Table）：删表时 HDFS 数据一并删除
CREATE TABLE managed_table (
    id   INT,
    name STRING
) STORED AS ORC;

-- 外部表（External Table）：删表时只删元数据，HDFS 数据保留
CREATE EXTERNAL TABLE external_table (
    id   INT,
    name STRING
)
LOCATION '/data/external_table/';

选择原则：

  • 数据由 Hive 独占管理 → 内部表
  • 数据与其他系统（Spark、Flink、Presto）共享 → 外部表
  • 生产环境建议优先用外部表，防止误删数据

分区表

分区将数据按某个字段值拆分到不同 HDFS 子目录，查询时可跳过无关分区，大幅提升性能。分区字段不在表的实际数据列中，是一个虚拟列。

1
2
3
4
5
6
7
8
-- 创建分区表
CREATE TABLE orders (
    order_id  INT,
    user_id   INT,
    amount    DOUBLE
)
PARTITIONED BY (dt STRING, region STRING)   -- 分区字段
STORED AS ORC;
1
2
3
4
5
6
7
8
-- 手动添加分区
ALTER TABLE orders ADD PARTITION (dt='2024-01-01', region='north');

-- 查看所有分区
SHOW PARTITIONS orders;

-- 删除分区（同时删除 HDFS 数据，外部表只删元数据）
ALTER TABLE orders DROP PARTITION (dt='2024-01-01');
1
2
3
4
5
6
7
8
9
10
11
-- 静态分区写入（必须明确指定分区值）
INSERT INTO orders PARTITION (dt='2024-01-01', region='north')
SELECT order_id, user_id, amount FROM stg_orders
WHERE dt='2024-01-01' AND region='north';

-- 动态分区写入（自动根据数据值分区，需开启配置）
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;  -- 允许全动态分区

INSERT INTO orders PARTITION (dt, region)
SELECT order_id, user_id, amount, dt, region FROM stg_orders;

查询必须带分区过滤，否则触发全表扫描（严格模式下会报错）：

1
2
3
4
5
-- 好的写法
SELECT * FROM orders WHERE dt = '2024-01-01';

-- 危险写法（全分区扫描）
SELECT * FROM orders WHERE amount > 100;

分桶表

分桶是对某个字段做 Hash 后分散到固定数量的文件（桶）中，适用于 Join 优化 和 数据抽样。

1
2
3
4
5
6
7
8
-- 创建分桶表
CREATE TABLE users (
    user_id INT,
    name    STRING,
    age     INT
)
CLUSTERED BY (user_id) INTO 32 BUCKETS    -- 按 user_id Hash 分成 32 桶
STORED AS ORC;
1
2
3
4
-- 写入分桶表（需强制开启）
SET hive.enforce.bucketing = true;

INSERT INTO users SELECT * FROM stg_users;
1
2
3
4
5
-- 数据抽样（从第 1 桶开始，共取 4 桶，总共 32 桶）
SELECT * FROM users TABLESAMPLE(BUCKET 1 OUT OF 4 ON user_id);

-- 按比例随机抽样
SELECT * FROM users TABLESAMPLE(10 PERCENT);

分区 vs 分桶对比：

分区 分桶
目的 减少扫描数据量 优化 Join / 抽样
实现 HDFS 子目录 HDFS 文件内 Hash 分片
数量 动态（数据驱动） 固定（建表时指定）
适合场景 按时间/地区过滤 user_id 等高基数 Join 字段

数据加载与导出

1
2
3
4
5
6
7
8
9
-- 从本地文件系统加载（复制到 HDFS）
LOAD DATA LOCAL INPATH '/local/path/data.csv' INTO TABLE table_name;

-- 从 HDFS 加载（移动，不是复制）
LOAD DATA INPATH '/hdfs/path/data.csv' INTO TABLE table_name;

-- 覆盖写入（OVERWRITE 清空原有数据再写入）
LOAD DATA LOCAL INPATH '/local/path/data.csv'
OVERWRITE INTO TABLE table_name PARTITION (dt='2024-01-01');
1
2
3
4
5
6
7
8
9
-- 查询结果导出到本地
INSERT OVERWRITE LOCAL DIRECTORY '/local/output/'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT * FROM table_name;

-- 查询结果导出到 HDFS
INSERT OVERWRITE DIRECTORY '/hdfs/output/'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT * FROM table_name;

Hive 特有数据类型

复杂类型

1
2
3
4
5
6
CREATE TABLE complex_demo (
    id      INT,
    tags    ARRAY<STRING>,                   -- 数组
    scores  MAP<STRING, INT>,               -- 键值对
    address STRUCT<city:STRING, zip:STRING>  -- 结构体
);
1
2
3
4
5
6
7
8
-- 访问 ARRAY
SELECT tags[0] FROM complex_demo;              -- 取第一个元素

-- 访问 MAP
SELECT scores['math'] FROM complex_demo;       -- 取 key='math' 的值

-- 访问 STRUCT
SELECT address.city FROM complex_demo;         -- 取 city 字段

LATERAL VIEW + EXPLODE（炸裂函数）

将数组或 Map 展开成多行，是 Hive 中非常常用的操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 将 tags 数组展开为多行
SELECT id, tag
FROM complex_demo
LATERAL VIEW EXPLODE(tags) tmp AS tag;

-- 将 MAP 展开为 key-value 多行
SELECT id, k, v
FROM complex_demo
LATERAL VIEW EXPLODE(scores) tmp AS k, v;

-- 多个 LATERAL VIEW 叠加
SELECT id, tag, k, v
FROM complex_demo
LATERAL VIEW EXPLODE(tags)   t1 AS tag
LATERAL VIEW EXPLODE(scores) t2 AS k, v;

窗口函数

Hive 对窗口函数支持完整，语法与标准 SQL 相同，但比 MySQL 5.x 早支持。

1
2
3
4
5
6
7
8
9
-- 分组排名
SELECT
    user_id,
    amount,
    dt,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
    RANK()       OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS dense_rank
FROM orders;
1
2
3
4
5
6
7
8
9
-- 滑动窗口聚合（最近 3 行求和）
SELECT
    dt,
    amount,
    SUM(amount) OVER (
        ORDER BY dt
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3day_sum
FROM daily_sales;
1
2
3
4
5
6
7
-- LAG / LEAD（访问前后行数据）
SELECT
    dt,
    amount,
    LAG(amount, 1, 0)  OVER (ORDER BY dt) AS prev_day,
    LEAD(amount, 1, 0) OVER (ORDER BY dt) AS next_day
FROM daily_sales;

常用内置函数

字符串函数

1
2
3
4
5
6
7
8
9
10
SELECT
    CONCAT('hello', ' ', 'world'),         -- hello world
    CONCAT_WS('-', '2024', '01', '01'),    -- 2024-01-01（指定分隔符）
    SUBSTR('hello', 2, 3),                 -- ell
    TRIM('  hello  '),                     -- hello
    LOWER('HELLO'),                        -- hello
    UPPER('hello'),                        -- HELLO
    LENGTH('hello'),                       -- 5
    REGEXP_REPLACE('abc123', '[0-9]', ''), -- abc
    SPLIT('a,b,c', ',')[1];                -- b（返回 ARRAY，下标从0）

日期函数

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    CURRENT_DATE,                                    -- 当前日期
    CURRENT_TIMESTAMP,                               -- 当前时间戳
    TO_DATE('2024-01-01 12:00:00'),                  -- 2024-01-01
    DATE_ADD('2024-01-01', 7),                       -- 2024-01-08
    DATE_SUB('2024-01-08', 7),                       -- 2024-01-01
    DATEDIFF('2024-01-08', '2024-01-01'),            -- 7
    DATE_FORMAT(CURRENT_DATE, 'yyyy-MM'),            -- 2024-01
    YEAR('2024-01-01'),                              -- 2024
    MONTH('2024-01-01'),                             -- 1
    FROM_UNIXTIME(1704067200, 'yyyy-MM-dd'),         -- Unix 时间戳转日期
    UNIX_TIMESTAMP('2024-01-01', 'yyyy-MM-dd');      -- 日期转 Unix 时间戳

条件函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
    -- IF（Hive 独有，MySQL 也有）
    IF(score >= 60, '及格', '不及格'),

    -- CASE WHEN（标准 SQL）
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END,

    -- COALESCE：返回第一个非 NULL 值
    COALESCE(col1, col2, '默认值'),

    -- NVL：Hive 版的 IFNULL（两参数）
    NVL(col, '默认值'),

    -- NVL2：非 NULL 返回 val1，NULL 返回 val2
    NVL2(col, 'not null', 'is null')
FROM t;

聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    COUNT(*),
    COUNT(DISTINCT user_id),
    SUM(amount),
    AVG(amount),
    MAX(amount),
    MIN(amount),
    -- 收集为数组（Hive 特有）
    COLLECT_LIST(name),      -- 保留重复值
    COLLECT_SET(name),       -- 去重
    -- 拼接字符串聚合
    CONCAT_WS(',', COLLECT_SET(name))
FROM orders
GROUP BY dt;

存储格式对比

格式 特点 适用场景
TEXTFILE 纯文本，可读，无压缩 外部数据导入，调试
ORC 列式存储，高压缩，支持 ACID Hive 生产环境首选
Parquet 列式存储，兼容性好 跨引擎（Spark/Presto）共享
SEQUENCEFILE 二进制 KV 存储 较少使用
AVRO 行式，Schema 演进友好 数据流场景 
1
2
3
4
5
6
-- 建表时指定压缩格式
CREATE TABLE t STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

CREATE TABLE t STORED AS PARQUET
TBLPROPERTIES ('parquet.compression'='SNAPPY');

常用配置项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 开启本地模式（小数据量调试时加速）
SET hive.exec.mode.local.auto = true;

-- 开启动态分区
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

-- 设置 Reduce 任务数
SET mapreduce.job.reduces = 10;

-- 开启 Map-side Join（小表 Join 大表时广播小表）
SET hive.auto.convert.join = true;
SET hive.mapjoin.smalltable.filesize = 25000000;  -- 小表阈值 25MB

-- 开启压缩输出
SET hive.exec.compress.output = true;
SET mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

-- 并行执行独立 Stage
SET hive.exec.parallel = true;

-- 跳过全表扫描保护（谨慎使用）
SET hive.mapred.mode = nonstrict;

Join 类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 普通 JOIN（INNER JOIN）
SELECT a.*, b.name
FROM orders a
JOIN users b ON a.user_id = b.user_id;

-- LEFT JOIN（Hive 行为与 MySQL 一致）
SELECT a.*, b.name
FROM orders a
LEFT JOIN users b ON a.user_id = b.user_id;

-- Map Join（小表 Join，在 Map 端完成，无需 Reduce）
SELECT /*+ MAPJOIN(b) */ a.*, b.name
FROM orders a
JOIN dim_users b ON a.user_id = b.user_id;

-- Hive 不支持 IN 子查询中使用关联列，改用 LEFT SEMI JOIN
-- MySQL 写法：SELECT * FROM a WHERE id IN (SELECT id FROM b)
-- Hive 写法：
SELECT a.*
FROM orders a
LEFT SEMI JOIN whitelist b ON a.user_id = b.user_id;

查看元数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查看表结构
DESCRIBE table_name;
DESC FORMATTED table_name;   -- 详细信息（存储格式、HDFS路径、分区信息等）
DESC EXTENDED table_name;    -- 原始元数据字符串

-- 查看所有表
SHOW TABLES;
SHOW TABLES LIKE 'order*';

-- 查看表的分区
SHOW PARTITIONS table_name;

-- 查看建表语句
SHOW CREATE TABLE table_name;

-- 查看函数列表
SHOW FUNCTIONS;
DESCRIBE FUNCTION EXTENDED function_name;