hive 汇总

Hive 操作符和函数汇总

Hive 架构概述

Hive 是构建在 Hadoop 之上的数据仓库基础设施，提供类 SQL 的查询语言 HQL，将查询转换为 MapReduce、Tez 或 Spark 任务执行。

核心组件

MetaStore

• 存储表的元数据信息，包括表结构、分区、列类型、序列化/反序列化规则等
• 支持多种存储后端：Derby（默认）、MySQL、PostgreSQL
• 可配置为远程服务模式，支持多客户端并发访问

Driver

• 接收查询请求，生成执行计划
• 编译器：解析 HQL，生成语法树，进行语义分析
• 优化器：基于规则和成本的查询优化
• 执行引擎：将逻辑计划转换为物理执行计划

执行引擎

• MapReduce：早期默认引擎，适合大规模批处理
• Tez：基于 DAG 的执行引擎，减少中间结果落盘
• Spark：基于内存计算，显著提升查询性能

Hive 数据模型

Database

数据库是命名空间的逻辑容器，对应 HDFS 上的目录路径。

Table

Hive 表分为两种类型：

• 管理表（内部表）：删除表时删除元数据和数据
• 外部表：删除表时仅删除元数据，保留数据

Partition

将表数据按照分区列的值存储在不同子目录中，实现数据分区裁剪，提升查询效率。

Bucket

将表数据按照哈希算法分散到固定数量的文件中，用于采样和优化 JOIN 操作。

常用 DDL 操作

建表语句

-- 创建内部表
CREATE TABLE IF NOT EXISTS employee (
    id INT,
    name STRING,
    salary FLOAT,
    department STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- 创建分区表
CREATE TABLE IF NOT EXISTS sales (
    order_id INT,
    amount FLOAT,
    product STRING
)
PARTITIONED BY (dt STRING, region STRING)
STORED AS ORC;

-- 创建分桶表
CREATE TABLE IF NOT EXISTS user_behavior (
    user_id INT,
    action STRING,
    timestamp BIGINT
)
CLUSTERED BY (user_id) INTO 32 BUCKETS
STORED AS ORC;

分区操作

-- 添加分区
ALTER TABLE sales ADD PARTITION (dt='2020-01-01', region='north');

-- 删除分区
ALTER TABLE sales DROP PARTITION (dt='2020-01-01', region='north');

-- 查看分区
SHOW PARTITIONS sales;

常用 DML 操作

数据加载

-- 从本地文件系统加载数据
LOAD DATA LOCAL INPATH '/tmp/employee.csv'
OVERWRITE INTO TABLE employee;

-- 从 HDFS 加载数据
LOAD DATA INPATH '/user/data/sales'
INTO TABLE sales PARTITION (dt='2020-01-01', region='north');

数据插入

-- 从查询结果插入
INSERT OVERWRITE TABLE sales PARTITION (dt='2020-01-01')
SELECT order_id, amount, product
FROM raw_sales
WHERE date = '2020-01-01';

-- 动态分区插入
SET hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE sales PARTITION (dt, region)
SELECT order_id, amount, product, date, region
FROM raw_sales;

查询操作

-- 基本查询
SELECT department, AVG(salary) as avg_salary
FROM employee
GROUP BY department;

-- 排序和限制
SELECT id, name, salary
FROM employee
ORDER BY salary DESC
LIMIT 10;

常用内置函数分类汇总

日期函数

-- 获取当前日期
SELECT current_date();

-- 日期加减
SELECT date_add('2020-01-01', 7);  -- 2020-01-08
SELECT date_sub('2020-01-08', 7);  -- 2020-01-01

-- 日期差值
SELECT datediff('2020-01-08', '2020-01-01');  -- 7

-- 日期格式转换
SELECT from_unixtime(1577836800, 'yyyy-MM-dd');  -- 2020-01-01
SELECT unix_timestamp('2020-01-01', 'yyyy-MM-dd');  -- 1577836800

字符串函数

-- 字符串拼接
SELECT concat('hello', ' ', 'world');  -- hello world

-- 字符串截取
SELECT substr('hello world', 1, 5);  -- hello

-- 大小写转换
SELECT upper('hello');  -- HELLO
SELECT lower('HELLO');  -- hello

-- 去除空格
SELECT trim('  hello  ');  -- hello

-- 字符串分割
SELECT split('a,b,c', ',');  -- ["a","b","c"]

数学函数

-- 向上取整
SELECT ceil(3.14);  -- 4

-- 向下取整
SELECT floor(3.99);  -- 3

-- 四舍五入
SELECT round(3.5);  -- 4

-- 绝对值
SELECT abs(-10);  -- 10

-- 幂运算
SELECT pow(2, 3);  -- 8

聚合函数

-- 计数
SELECT COUNT(*) FROM employee;
SELECT COUNT(DISTINCT department) FROM employee;

-- 求和
SELECT SUM(amount) FROM sales;

-- 平均值
SELECT AVG(salary) FROM employee;

-- 最大值和最小值
SELECT MAX(salary), MIN(salary) FROM employee;

窗口函数

-- 行号
SELECT id, name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employee;

-- 排名
SELECT id, name, salary,
    RANK() OVER (ORDER BY salary DESC) as rank
FROM employee;

-- 累积求和
SELECT dt, amount,
    SUM(amount) OVER (ORDER BY dt) as cumulative_amount
FROM sales;

-- 移动平均
SELECT dt, amount,
    AVG(amount) OVER (
        ORDER BY dt 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg
FROM sales;

Hive 性能优化要点

分区裁剪

通过 WHERE 条件过滤分区列，避免全表扫描。

-- 优化前：全表扫描
SELECT * FROM sales;

-- 优化后：只扫描指定分区
SELECT * FROM sales WHERE dt='2020-01-01';

MapJoin

将小表加载到内存，在 Map 阶段完成 JOIN，避免 Shuffle。

-- 启用 MapJoin
SET hive.auto.convert.join=true;
SET hive.mapjoin.smalltable.filesize=25000000;

-- 查询自动使用 MapJoin
SELECT a.*, b.*
FROM large_table a
JOIN small_table b ON a.id = b.id;

数据倾斜处理

识别和解决数据分布不均导致的性能问题。

-- 开启数据倾斜优化
SET hive.optimize.skewjoin=true;
SET hive.skewjoin.key=100000;

-- 使用 SKEWED BY 声明倾斜键
CREATE TABLE skewed_table (
    id INT,
    value STRING
)
SKEWED BY (id) ON (1, 2, 3);

文件格式选择

使用列式存储格式提升压缩率和查询效率。

-- ORC 格式
CREATE TABLE orc_table (
    id INT,
    name STRING
)
STORED AS ORC
TBLPROPERTIES ("orc.compress"="SNAPPY");

-- Parquet 格式
CREATE TABLE parquet_table (
    id INT,
    name STRING
)
STORED AS PARQUET;

Hive 与传统 RDBMS 的区别

特性	Hive	传统 RDBMS
数据规模	PB 级别，适合海量数据	TB 级别，适合结构化数据
延迟	高延迟，分钟到小时级别	低延迟，毫秒到秒级别
事务支持	有限支持，主要支持 ACID	完整的 ACID 事务支持
索引	支持有限，主要依赖分区和分桶	完善的索引机制
更新删除	支持有限，批量操作为主	支持实时增删改查
数据类型	弱类型，支持复杂类型	强类型，支持标准 SQL 类型
查询语言	HQL，类 SQL 但有差异	标准 SQL

常见问题与排查思路

查询性能慢

排查步骤

1. 检查是否使用了分区裁剪
2. 查看 EXPLAIN 输出，分析执行计划
3. 检查是否存在数据倾斜
4. 评估文件格式和压缩方式
5. 考虑使用适当的执行引擎（Tez/Spark）

-- 查看执行计划
EXPLAIN SELECT * FROM sales WHERE dt='2020-01-01';

内存溢出

排查步骤

1. 检查 MapReduce 内存配置
2. 分析是否因数据倾斜导致某些 Task 负载过高
3. 优化 JOIN 顺序，小表在前
4. 增加 Map/Reduce Task 内存

-- 调整内存配置
SET mapreduce.map.memory.mb=4096;
SET mapreduce.reduce.memory.mb=8192;

元数据连接失败

排查步骤

1. 检查 MetaStore 服务状态
2. 验证数据库连接配置
3. 检查网络连通性
4. 查看日志文件定位具体错误

文件格式不兼容

排查步骤

1. 确认文件格式与表定义一致
2. 检查分隔符配置
3. 验证序列化/反序列化类配置
4. 使用 DESCRIBE FORMATTED 查看表详细信息

参考文档

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions