3小时吃掉HiveSQL：从基础语法到性能优化

前方干货预警： 这可能是你能够找到的最清晰最系统的SQL入门和进阶教程。

程序员的日常工作避免不了处理大量数据。

处理数据的标准语言是SQL，常见的包括MySQL/HiveSQL/SparkSQL/Trino，它们的语法基本相差不大。（本教程以HiveSQL语法为主）

扎实的SQL功底可以让你在数据的海洋中畅游无阻。

下面这10个问题，可以小小地检查一下你的SQL基础是否扎实。

• • 1，insert into 和 insert overwrite有什么区别？

• • 2，数据表分区和分桶的区别是什么？分桶有什么用？

• • 3，常用select查询语句的执行顺序是什么？给出join、groupby、where 和窗口函数 的执行顺序排序。

• • 4，with as语句有什么作用？

• • 5，左表 left join 右表，返回的行数有可能大于左表的行数吗？左表 semi join 右表，返回的行数有可能大于左表的行数吗？

• • 6，COALESCE函数 和NVL 函数的作用是什么？

• • 7, 窗口函数使用ROWS BETWEEN 和 RANGE BETWEEN确定窗口范围有什么区别？

• • 8, group by 分组时遇到某个分组的key数据特别多，发生数据倾斜怎么办？

• • 9，MapJoin和SknewJoin性能优化的原理是什么？

• • 10，排序操作 order by 和 sort by有什么区别？如何对大数据topk 排序进行性能优化？

以上的这些问题，都可以在下面的这个HiveSQL教程中找到。enjoy!

公众号算法美食屋后台回复关键词：SQL，获取本文markdown源代码！

一， SQL 概述

根据用途，SQL 语法可以分为一下几个部分。

• • DDL: 数据定义语言(CREATE，ALTER，DROP，DECLARE)

• • DML: 数据操纵语言(SELECT，DELETE，UPDATE，INSERT)

• • DCL: 数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

注意：SQL 对大小写不敏感！

1，数据定义语言

DDL(Data Definition Language)

关键词：

• • create(建表和数据库)

• • drop(删除表)

• • alter(更改表)

• • truncate(清空表)

• • use(切换数据库)

• • show(显示数据库,表信息)

• • desc(查看表结构)

常用语句:

--创建分区表
DROP TABLE IF EXISTS basedb.sales;
CREATE TABLE IF NOT EXISTS basedb.sales
(sale_id INT,
 product string COMMENT '产品名称',
 amount double COMMENT '销量')
 PARTITIONED BY (year INT, month INT)

2，数据操纵语言

DML(Data Manipulatin Language)

关键词:

• • select(数据查询)

• • insert(插入记录)

• • update(更新记录)

• • delete(删除记录)

注意：insert插入数据到分区有两种方式， Insert overwrite 和 Insert into, 前者是覆盖分区，后者是追加数据。

常用语句：

-- 插入数据到指定分区(覆盖分区)
INSERT OVERWRITE  TABLE basedb.sales PARTITION (year='2025', month='01')
SELECT id, amount, date
FROM tmpdb.raw_sales;


-- 动态插入数据到分区(追加数据)
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
INSERT INTO TABLE basedb.sales PARTITION (year, month)
SELECT id, amount, date, year, month
FROM tmpdb.raw_sales;

--数据查询select语句常用模版
SELECT ... 
    FROM 表1 JOIN 表2
ON 等值连接 
WHERE 分组前过滤条件 
GROUP BY 分组字段 
HAVING 分组后过滤条件 
ORDER BY 排序字段

3，数据控制语言

DCL(Data Control Language)

关键词：

• • grant(授权)

• • revoke(撤销权限)

• • commit(提交事务)

• • rollback(回滚事务)

常用语句：

-- 授权用户访问表权限
GRANTSELECT, INSERT, UPDATE, DELETEONTABLE basedb.sales TOUSER user1;
-- 撤销用户访问表权限
REVOKESELECT, INSERT, UPDATE, DELETEONTABLE basedb.sales FROMUSER user1;

-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

二，数据存储结构

按颗粒度从大到小的顺序，Hive数据存储单元被组织为：

• • 数据库 Databases

• • 数据表 Tables

• • 分区 Partitions

• • 桶或簇 Buckets

1，数据库

数据库是一个名称空间，作用是避免表、视图、分区、列等的命名冲突。

数据库还可用于为用户或用户组实施安全性

2，数据表

具有相同模式的同质数据单元。

一个数据库里可以有多张表。

3，分区

每个表可以有一个或多个分区键，用于确定数据的存储方式。

分区除了作为存储单元外，还允许用户有效地识别满足指定条件的行。

分区列（Partition columns）是虚拟列，它们不是数据本身的一部分，而是在加载时派生的。

4，分桶

每个分区中的数据又可以基于表的某一列的散列函数的值被划分为桶。

分桶表用于需要高效连接操作、数据抽样和均匀负载的场景，特别是在大数据集和复杂查询中。

分桶和分区的区别：

• • 每个分区对应一个子目录，每个分桶对应一个文件。

• • 分区字段不是实际的列，分桶字段必须是实际的列。

• • 分区表的分区数量可以一直增长，而分桶表创建好之后桶的数量就固定不变了。

--创建同时有分区和分桶的表
CREATE TABLE orders (
    order_id INT,
    user_id INT,
    amount DOUBLE,
    order_date STRING
)
PARTITIONED BY (order_month STRING)  -- 按月份分区
CLUSTERED BY (user_id) INTO 10 BUCKETS  -- 按用户ID分桶，共10个桶
STORED AS ORC;  -- 使用ORC存储格式，提升查询性能

-- BucketMapJoin 支持大表对大表的连接，且不用shuffle.

--1，创建桶表
CREATE TABLE large_table_A (
    id INT,
    value STRING
)
CLUSTERED BY (id) INTO8 BUCKETS
STORED AS ORC;

CREATE TABLE large_table_B (
    id INT,
    description STRING
)
CLUSTERED BY (id) INTO8 BUCKETS
STORED AS ORC;

INSERTover large_table_A 


--2，启用 BucketMapJoin 优化
SET hive.optimize.bucketmapjoin=true;
SET hive.optimize.bucketmapjoin.sortedmerge=true;

-- 基于桶的连接查询
SELECT a.id, a.value, b.description
FROM large_table_A a
JOIN large_table_B b
ON a.id = b.id;

三， 常用数据类型

hive 支持常用的基础数据类型和一些复杂类型。

1，基础数据类型

• • 整数类型

• • • TINYINT: 1字节的有符号整数 (-128~127)

  • • SMALLINT: 2字节的有符号整数 (-32768~32767)

  • • INT: 4字节的有符号整数 (-2^31~2^31-1)

  • • BIGINT: 8字节的有符号整数 (-2^63~2^63-1)

• • 浮点数类型

• • • FLOAT: 单精度浮点数

  • • DOUBLE: 双精度浮点数

  • • DECIMAL: 精确的小数值，常用于金融计算

• • 字符串类型

• • • STRING: 字符串，无长度限制

  • • VARCHAR: 可变长度字符串

  • • CHAR: 固定长度字符串

• • 其他基础类型

• • • BOOLEAN: 布尔类型 (TRUE/FALSE)

  • • TIMESTAMP: 时间戳

  • • DATE: 日期类型

  • • BINARY: 二进制数据

2，复杂数据类型

• • ARRAY: 有序的相同类型的集合，如 ARRAY

• • MAP: 键值对的集合，如 MAP<STRING, INT>

• • STRUCT: 不同数据类型的命名字段的集合，如 STRUCT<name:STRING, age:INT>

• • UNION: 不同数据类型的联合体，如 UNIONTYPE<STRING, INT>

3，数据类型转换

Hive 支持隐式转换 和 显式转换。

(1) 隐式转换

• • 自动进行，无需显式转换语句

• • 数据类型按精度从低到高转换

• • 转换规则:

• • • TINYINT -> SMALLINT -> INT -> BIGINT

  • • INT -> DOUBLE

  • • FLOAT -> DOUBLE