HiveQL 书写规范

机灵小布衣

已于 2024-01-03 11:30:13 修改

阅读量369

点赞数

文章标签： hive sql 数据仓库大数据

于 2022-09-02 16:55:19 首次发布

本文链接：https://blog.csdn.net/sinat_41207450/article/details/126666691

版权

概述

HiveQL

HiveQL 是一种用于基于 SQL 的查询语言, 用于和 Hive 进行交互. HiveQL 的语句会由 Hive 翻译成计算任务并交给计算引擎在 Hadoop 上执行.

关于本文

本文旨在提供一份 HiveQL 书写的风格指导, 用于指导 HiveQL 代码的编写和 code review, 从而提高产出的 HiveQL 代码的可读性.

本文将尝试从表, 任务, 结果集等多个话题描述 HiveQL 应当如何书写, 针对每个话题, 都将提供以下若干种建议:

必选: 不特别强调可选的内容都是必选, 请务必按照该规范进行执行. Code review 中如果出现不符合该条规范的语句, reviewer 应当要求书写者进行改进.
可选: 是否要按照该规范进行执行由书写者决定, 不做强制要求

本文并非是完备的, 只是开发人员在开发过程中总结和吸纳的经验, 文末将指出一些本文还没有涉及的问题. 欢迎对本文的内容和未涉及的问题提出修改意见和其他贡献。

通用原则

HiveQL 文件

文件名: 小写, 单词间用下划线分隔, 使用 .sql 作为扩展名, 例如:
```
ods_fact_client_role_login_di.sql
```
文件编码: 编码采用无 BOM 的 UTF-8
文件结尾应当有一个换行符
应尽量使用 HiveQL 生成工具来生成 HiveQL 代码
禁止直接修改 HiveQL 生成工具生成的 HiveQL 代码及其他文件, 如需修改应从配置文件下手

HiveQL 语法

关键字必须大写
SELECT , FROM , JOIN , WHERE 等关键字必须另起一行
同一层次的关键字列对齐, 非关键字的行需要缩进
统一用 4 个空格缩进, 并将 tab 指定为 4 个空格

WHERE 和 ON 后面的条件必须写成多行, 例如:

SELECT
account_id
FROM
dw_my.ods_fact_client_role_login_di
WHERE 
dt = '2021-03-04'
AND server_id = 1021;

所有的命名, 包括并不限于库, 表, 结果集, 字段等, 都应该小写, 并用下划线分割
条件表达式和赋值表达式两边用 1 个空格隔开, 例如: ```sql

-- 指定动态分区的数量 SET hive.exec.max.dynamic.partitions = 400; -- 指定日期的条件表达式 SELECT account_id FROM dw_my.ods_fact_client_role_login_di WHERE dt = '2021-03-04';

*   在绝大部分情况下禁止使用 ```SELECT *``` , 请明确地指出要被 SELECT 的字段，仅在一种情况下允许使用。即，通过 JOIN 或其他方式将宽表不断加宽的语句中允许使用 SELECT *，例如：
```sql
-- 根据两个字段来识别游戏的名字，分别是 product_type_identifier 和 sku
-- 优先使用根据这两个字段匹配出的游戏名字，如果匹配不到，就取两个字段中不为空的那个作为游戏代号
SELECT CASE
        WHEN rs_game_identifier.game_name IS NOT NULL THEN rs_game_identifier.game_name
        WHEN rs_game_sku.game_name IS NOT NULL THEN rs_game_sku.game_name
        WHEN rs_add_sales_metrics.product_type_identifier IS NOT NULL THEN rs_add_sales_metrics.product_type_identifier
        WHEN rs_add_sales_metrics.sku IS NOT NULL THEN rs_add_sales_metrics.sku
        ELSE NULL
    END AS game_name,
    rs_add_sales_metrics.*
FROM rs_add_sales_metrics
    LEFT JOIN (
        -- 根据 parent_identifier 匹配游戏名字
        SELECT sku,
            game_name
        FROM dw_omni_sdk.dwd_dim_app_store_game_name_di
    ) AS rs_game_identifier --
    ON rs_add_sales_metrics.parent_identifier = rs_game_identifier.sku
    LEFT JOIN (
        -- 根据 sku 匹配游戏名字
        SELECT sku,
            game_name
        FROM dw_omni_sdk.dwd_dim_app_store_game_name_di
    ) AS rs_game_sku --
    ON rs_add_sales_metrics.sku = rs_game_sku.sku

一行代码禁止超过 120 个字符
多写注释, 在 -- 符号和注释之间留出一个空格
每一条 HiveQL 语句都应该以分号 ; 结尾, 即使文件中只有一条 HiveQL 语句
严禁使用 a, b, t1, t2 作为字段的别名或者结果集的名字
Hive 表绝大部分为分区表, 代码中必须指定分区, 且确保 WHERE 语句中, 分区指定字段在第一位
禁止全表查询
禁止笛卡尔积关联
禁止 IN 嵌套子查询, 请使用 JOIN 替代
禁止三层以上的 SELECT 嵌套, 应使用 WITH AS 提取成单独的结果集
禁止四个以上的 JOIN 操作, 应使用 WITH AS 提取成单独的结果集
必须使用谓词下移的写法,即两表关联时,先过滤再关联,例如:

-- 错误
SELECT rs_today_acct.first_login_device_id,
    COUNT(rs_history_acct.account_id) AS device_count
FROM dw_my.dwd_dim_account_di AS rs_today_acct
    LEFT JOIN dw_my.dwd_dim_account_di AS rs_history_acct
ON rs_today_acct.first_login_device_id = rs_history_acct.first_login_device_id
WHERE rs_today_acct.first_login_date = :dt
    AND rs_history_acct.first_login_date BETWEEN '2017-10-18' AND :dt
GROUP BY rs_today_acct.first_login_device_id;

--正确
SELECT rs_today_acct.first_login_device_id,
    device_count
FROM(
        SELECT first_login_device_id
        FROM dw_my.dwd_dim_account_di
        WHERE first_login_date = :dt
    ) AS rs_today_acct
    LEFT JOIN (
        SELECT first_login_device_id,
            COUNT(account_id) AS device_count
        FROM dw_my.dwd_dim_account_di
        WHERE first_login_date BETWEEN '2017-10-18' AND :dt
        GROUP BY first_login_device_id
    ) AS rs_history_acct 
    ON rs_today_acct.first_login_device_id = rs_history_acct.first_login_device_id;

可选

一个 SQL 任务尽量只生成成一个目标表 ( 可以有多个分区 )，特殊情况除外
对数据量大的表禁止使用 COUNT(DISTINCT)，可以组合使用 COUNT 和 GROUP BY
使用 row_number() 开窗函数之前, 尽量缩小扫描数据范围
禁止对日志量巨大的非整数类型字段排序 ( 日期或时间字段请转换成时间戳 )

命名

当命名中出现以下的概念, 请使用统一的英文命名

含义	缩写	完整写法	备注
数量	cnt	count
用户	usr	user
账号	acct	account
角色	role	role
日期	dt	date
周	ww	week
月	mm	month
季度	qq	quarter
年	yy	year
来源	src	source
渠道	chan	channel
消费	expd	expend
充值	pay	pay
留存	retention	retention
留存率	retention_rate	retention_rate
当日	d0	day0
次日	d1	day1
第三日	d2	day2
第七日	d6	day6
平台	plt	platform
设备	dev	device
服务器	srv	server
国家	country	country
访问用户数	uv	unique visitor	虽然最初是指访问, 但现在更多的直接用来表示用户数
访问次数	pv	page view	虽然最初是指访问, 但现在更多的直接用来表示次数
新增设备数	new_dev_cnt	new_device_count
新增用户数	new_usr_cnt/new_uv	new_user_count
老用户数	old_usr_cnt/old_uv	old_user_count
持续用户数	retention_usr_cnt	retention_user_count
回流用户数	bk_usr_cnt/bk_uv	back_user_count
转化率	conv_rate	conversion_rate
占比	prop	proportion	rate一般用于某种漏斗, 例如转化率, 变化率, 利率 ratio 一般用于比例, 例如新老 1:3, proportion一般用于占比, 份额
点击	clk	click
均值	avg	average
报表	rpt	report
地址	addr	address
分组	grp	group
字符	char	character
对象	obj	object
参数	param	parameter
资源	res	resource

编辑器

文本编辑器统一使用 VSCode

统一使用 HiveQL 格式化插件:

Name: SQLTools
Id: mtxr.sqltools
Description: Database management done right. Connection explorer, query runner, intellisense, bookmarks, query history. Feel like a database hero!
Version: 0.23.0
Publisher: Matheus Teixeira
VS Marketplace Link: https://marketplace.visualstudio.com/items?itemName=mtxr.sqltools

HiveQL 关键字大写插件:

Name: Upper Case SQL
Id: eplak.vscode-uppercasesql
Description: makes all SQL keywords upper case with an easy hotkey and includes som short and easy snippets
Version: 2.1.4
Publisher: Christoffer Bjelke
VS Marketplace Link: https://marketplace.visualstudio.com/items?itemName=eplak.vscode-uppercasesql

表

表名

表名格式为 [库名/层名]_[主题/事实]_[具体表名]_[模型更新周期][全量/增量标识]
- 库名/层名:
  - stg: 贴源数据, STG 层是根据 CDC 策略把各个源系统的数据抽取到数据仓库中
  - ods: 仓库基础层, 为线上库快照数据或行为日志等原始数据
  - dwd: 对ODS层数据进行清洗, 维度退化, 脱敏等
  - dws: 以DWD为基础, 按固定时间间隔进行轻度汇总
  - dwt: 以DWS为基础, 按主题进行汇总
  - ads: 为各种统计报表提供数据
  - tmp: 临时表
- 主题/事实:
  - dim: 维度
  - fact: 事实
- 模型更新周期
  - y: 年
  - m: 月
  - d: 日
  - h: 小时
  - r: 实时或准实时
- 全量/增量标识
  - a: 全量
  - i: 增量 ```sql

-- 库名/层名: ods -- 主题/事实: fact -- 具体表名: client_role_login -- 模型更新周期: d -- 全量/增量标识: i CREATE TABLE IF NOT EXISTS ods_fact_client_role_login_di;


## 与表相关的 DDL

*   对表的任何操作都必须指定表所处的数据库, 例如:

dw_my.ods_fact_client_role_login_di;

*   表的存储需要使用 ```ORC``` 格式
*   建立表必须带有 ```IF NOT EXISTS``` 和 ```COMMENT```, 例如:
```sql
CREATE TABLE IF NOT EXISTS dw_my.ods_fact_client_role_login_di(
  dt string COMMENT '日期',
  day_of_week bigint COMMENT '周几',
  date_type_no bigint COMMENT '日期类型'
) COMMENT '客户端日志中的登录事实表'
STORED AS ORC;

删除表必须带有 IF EXISTS, 例如:

DROP TABLE IF EXISTS dw_my.ods_fact_client_role_login_di;

临时表使用 tmp 前缀, 并在最后带上 data_version 和执行的日期或月份, 例如: ```sql

-- 在 20190102 这天跑 20180630 版本的数据 tmp_client_role_login_20180630_20190102

*   临时表在使用之后需要删除
*   禁止在提及表的时候不指明所属的数据库, 例如:
```sql
-- 错误
DROP TABLE IF EXISTS ods_fact_client_role_login_di;
-- 正确
DROP TABLE IF EXISTS dw_my.ods_fact_client_role_login_di;

禁止使用 CREATE TABLE tmp_client_role_login_20180630_20190102 AS SELECT 的方式创建临时表, 必须手动创建

任务

任务名

任务名应当尽量和要输出的表保持一致. 与此同时, 增量更新的任务使用后缀 .inc.sql , 全量更新的任务使用后缀 .all.sql , 例如: ```sql

-- 修改 dw_my.ods_fact_client_role_login_di 的任务 -- 增量更新的任务名: ods_fact_client_role_login_di.inc.sql -- 全量更新的任务名: ods_fact_client_role_login_di.all.sql


## 头部注释

*   任务头部的注释中, 必须包含任务名, 作者, 创建时间和简短的描述, 例如:
```sql
-- 任务名: ods_fact_client_role_login_di
-- 作者: 翟旭亮
-- 创建日期: 2021-03-04
-- 构建 ODS 层客户端日志登录事实表

可选

任务头部的注释中, 可以包含更新时间, 输入, 输出, 例如: ```sql

-- 输入: dw.dw_app -- 输出: dw_my.ods_fact_client_role_login_di


## 字段

*   为字段取别名的时候, 必须使用 ```AS``` 关键字, 例如:
```sql
-- 正确
SELECT 
    dt AS login_date
FROM
    dw_my.ods_fact_client_role_login_di;
-- 错误
SELECT
    dt login_date
FROM
    dw_my.ods_fact_client_role_login_di;

结果集

所有的结果集都应该以 rs 开头, 取 result set 的意思, 例如: ```sql

-- 设备第一次登录的结果集 rs_device_first_login

*   从结果集中提取字段, 必须来自哪个结果集, 例如:
```sql
-- 通过账号 ID 和支付的时间, 确定一天内的每一条支付记录
-- 根据账号 ID 找到该账号当天和前一天的全部登录记录
SELECT rs_device_account_login.device_id,
    rs_account_pay.account_id,
    rs_account_pay.pay_time,
    rs_account_pay.dt,
    rs_device_account_login.login_time
FROM(
        SELECT account_id,
            stime AS pay_time,
            dt
        FROM dw_my.ods_fact_client_role_payment_di
        WHERE dt = :dt
    ) rs_account_pay -- 一天内的支付记录
    LEFT JOIN (
        SELECT device_id,
            account_id,
            stime AS login_time
        FROM dw_my.ods_fact_client_role_login_di
        WHERE dt BETWEEN DATE_ADD(:dt, -1) AND :dt
    ) rs_device_account_login -- 当天和前一天的登录记录
    ON rs_account_pay.account_id = rs_device_account_login.account_id

使用 WITH AS 语法对 HiveQL 的结构进行优化, 将复杂的计算拆分成简单的结果集, 例如:

WITH rs_device_first_login AS(
-- 取每一个设备在当天第一次登录的信息
SELECT rs_all_device_login.device_id,
    rs_all_device_login.channel_id,
    rs_all_device_login.server_id,
    rs_all_device_login.first_login_time,
    rs_all_device_login.first_login_date,
    rs_all_device_login.first_login_ip,
    rs_all_device_login.first_login_ip_city,
    rs_all_device_login.first_login_ip_province,
    rs_all_device_login.first_login_ip_country,
    rs_all_device_login.device_brand,
    rs_all_device_login.device_model,
    rs_all_device_login.ad_channel_id
FROM(
        -- 取 dw_my.ods_client_role_login 中的登录记录, 按登录时间排序
        SELECT device_id,
            channel_id,
            server_id,
            stime AS first_login_time,
            date_format(stime, 'yyyy-MM-dd') AS first_login_date,
            ip AS first_login_ip,
            ip_city AS first_login_ip_city,
            ip_province AS first_login_ip_province,
            ip_country AS first_login_ip_country,
            device_brand,
            device_model,
            ad_channel_id,
            row_number() OVER(
                PARTITION BY device_id
                ORDER BY stime
            ) AS rk
        FROM dw_my.ods_fact_client_role_login_di
        WHERE dt >= :dt
    ) rs_all_device_login
WHERE rk = 1
);

规范代码示例

-- 任务名: dwd_fact_client_payment_di.inc
-- 作者: 黄迪
-- 创建日期: 2021-03-04
-- 构建由客户端数据计算出的支付事实表
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
SET hive.exec.max.dynamic.partitions = 400;
SET hive.exec.max.dynamic.partitions.pernode = 400;
WITH -- 针对每一次支付记录, 取出支付账号在当天和前一天的全部登录记录
rs_account_pay_login AS(
    -- 通过账号 ID 和支付的时间, 确定一天内的每一条支付记录
    -- 根据账号 ID 找到该账号当天和前一天的全部登录记录
    SELECT rs_device_account_login.device_id,
        rs_account_pay.account_id,
        rs_account_pay.pay_time,
        rs_account_pay.dt,
        rs_device_account_login.login_time
    FROM(
            SELECT account_id,
                stime AS pay_time,
                dt
            FROM dw_my.ods_fact_client_role_payment_di
            WHERE dt = :dt
        ) rs_account_pay -- 一天内的支付记录
        LEFT JOIN (
            SELECT device_id,
                account_id,
                stime AS login_time
            FROM dw_my.ods_fact_client_role_login_di
            WHERE dt BETWEEN DATE_ADD(:dt, -1) AND :dt
        ) rs_device_account_login -- 当天和前一天的登录记录
        ON rs_account_pay.account_id = rs_device_account_login.account_id
),
-- 找到支付记录对应的设备 ID
rs_device_payment AS(
    -- 找到登录之前最后一次登录的设备 ID
    -- 需要注意的是, 该结果集并不是完整的全部支付记录
    -- 会过滤掉一些因为系统问题导致登录时间晚于支付时间的记录
    SELECT rs_login_before_payment.device_id,
        rs_login_before_payment.account_id,
        rs_login_before_payment.pay_time,
        rs_login_before_payment.dt
    FROM(
            -- 将所有结果按照登录的时间逆序排列
            SELECT rs_account_pay_login.device_id,
                rs_account_pay_login.account_id AS account_id,
                rs_account_pay_login.pay_time,
                rs_account_pay_login.dt,
                rs_account_pay_login.login_time,
                row_number() over(
                    partition BY account_id,
                    pay_time,
                    dt
                    ORDER BY login_time DESC
                ) rk
            FROM rs_account_pay_login --
                -- 从结果中去除登录时间晚于支付时间的记录
                -- 目的是找到支付前最近的一次登录行为
            WHERE pay_time > login_time
        ) rs_login_before_payment
    WHERE rk = 1
),
-- 再次取出支付记录, 将设备 ID 与其关联
rs_payment_fact AS(
    -- 再次关联可以保证结果集中包含全部的支付记录
    -- 会有千分之一左右的记录找不到设备 ID, 但不会影响其他分析
    SELECT rs_device_payment.device_id,
        rs_payment_info.account_id,
        rs_payment_info.channel_id,
        rs_payment_info.server_id,
        rs_payment_info.role_id,
        rs_payment_info.pay_time,
        rs_payment_info.pay_amount,
        rs_payment_info.currency_id,
        rs_payment_info.dt
    FROM (
            SELECT account_id,
                channel_id,
                server_id,
                role_id,
                stime AS pay_time,
                recharge_money AS pay_amount,
                currency_id,
                dt
            FROM dw_my.ods_fact_client_role_payment_di
            WHERE dt = :dt
        ) rs_payment_info
        LEFT JOIN rs_device_payment --
        ON rs_payment_info.account_id = rs_device_payment.account_id
        AND rs_payment_info.pay_time = rs_device_payment.pay_time
) --
-- 替换 role_id 并写入 dw_my.dwd_fact_client_payment_di
-- INSERT overwrite TABLE dw_my.dwd_fact_client_payment PARTITION (dt)
SELECT rs_payment_fact.device_id,
    rs_payment_fact.account_id AS account_id,
    rs_payment_fact.channel_id,
    rs_payment_fact.server_id,
    rs_role_archive.new_role_id AS role_id,
    rs_payment_fact.pay_time,
    rs_payment_fact.pay_amount,
    rs_payment_fact.currency_id,
    rs_payment_fact.dt AS dt
FROM rs_payment_fact
    LEFT JOIN (
        -- 替换 role_id
        SELECT role_id,
            new_role_id,
            server_id,
            dt
        FROM dw_my.ods_server_role_archiving
        WHERE dt = :dt
    ) rs_role_archive --
    ON rs_payment_fact.role_id = rs_role_archive.role_id
    AND rs_payment_fact.server_id = rs_role_archive.server_id
    AND rs_payment_fact.dt = rs_role_archive.dt;

等待完善的内容库

建立库必须带有 IF NOT EXISTS , COMMENT 和 LOCATION, 例如:

CREATE DATABASE IF NOT EXISTS dw_my COMMENT '魔域数据仓库' LOCATION 'hdfs_path';

删除库必须带有 IF EXISTS, 例如:

DROP DATABASE IF EXISTS dw_my;

指定每个作业的名称

在执行任务sql前加(不包括create,drop 临时表)
```
SET mapred.job.name = "job_name";
```
作业名称规范
- 创建临时表名称 create_table_${table_name} , 其中临时表名称规范见下文
- 执行插入(insert overwrite table)语句时 ,名称为 insert_overwrite
- ${table_name}
- ${partition} 如 insert overwrite table dws_xxx_m partition 取值 month = 201906 ,data_version = 20180630 ，则改任务名称是 insert_overwrite_dws_xxx_m_201906_20180630

指定压缩

使用 snappy 压缩

SET hive.exec.compress.intermediate=true;
SET hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
SET hive.intermediate.compression.type=BLOCK;
SET hive.exec.compress.output=true;
SET mapreduce.output.compression.type=BLOCK;
SET mapreduce.output.fileoutputformat.compress=true;
SET mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

新函数注释

函数功能说明
函数名称
输入说明与示例
输出说明与示例
返回结果说明与示例

-- 功能: xxxxxxx
-- 名称: xxxxxxx
-- 输入 xxxxxxx
-- 输入示例:
--     xxxxxxx
-- 输出示例:
--     xxxxxxx

效率与排查

小维表使用 MAPJOIN
分母为 0 的时候 Infinity
有2个数相除的时候，一定要处理分母为 0 的情况

机灵小布衣

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
HiveQL 书写规范

HiveQL 是一种用于基于 SQL 的查询语言, 用于和 Hive 进行交互。本文旨在提供一份 HiveQL 书写的风格指导, 用于指导 HiveQL 代码的编写和 code review, 从而提高产出的 HiveQL 代码的可读性.
复制链接

扫一扫