😊 工作中数据仓库开发规范

文章摘要

PostChat

此内容根据文章生成，并经过人工审核，仅用于文章内容的解释与总结

HIVE SQL 作为数仓操作语言，良好的代码习惯能我们高效地完成数据分析工作。除了根据 SQL 执行顺序上的优化外，按以下规范编写 SQL 代码既能使代码更加清晰，方便自己后期维护，也方便其他同事接手。

一、注释规范

注释内容要清晰明了，含义准确，避免歧义
字段注释紧跟在字段后面
应对不易理解的分支条件表达式加注释
对重要的计算应说明其功能
过长的函数实现，应将其语句按实现的功能分段加以概括性说明
原则上所有表、字段、任务都需要添加注释，任务有特定的注释规范，见下文任务注释说明

任务注释说明

-- [hive][ads][营销类应收未付明细报表]ads_fin_receivable_unpaid_marketing_detail_df

-- **************************************************************************
-- ** 创建者   : baihe
-- ** 创建日期 : 20210326
-- ** 功能描述 ：营销类应收未付明细报表
-- **************************************************************************
-- **************************** 修改日志 *************************************
-- 2021.3.10 修正应收未付金额算法逻辑为直接取值
--
-- **************************************************************************
-- ** 依赖表：
--              dws_fin_receivable_unpaid_marketing_detail_df               营销类应收未付汇总表
--
-- **************************************************************************
-- ** 输出表：
--          ads_fin_receivable_unpaid_marketing_detail_df      营销类应收未付明细报表
-- **************************************************************************
--
-- *******************  CURRENT_VERSION ： V.1.1  ***************************
-- **************************************************************************

任务注释注意事项补充

提供任务名，方便任务创建时任务的获取以及平台中任务查询
提供创建者、创建日期、功能描述等信息，方便后期维护跟踪
提供代码变更历史，便于了解代码演进历史及依据
提供脚本依赖表清单，方便后续任务依赖配置
提供输出表清单，方便确认是否单个目标表

二、存储格式规范

所谓的存储格式就是在Hive建表的时候指定的将表中的数据按照什么样子的存储方式，如果指定了方式，那么在向表中插入数据的时候，将会使用该方式向HDFS中添加相应的数据类型。建表如果无特殊要求，一律使用下面的row format格式及存储格式。

1 2	row format serde 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe' stored as RCFile;

三、建表语句规范

--参考实例create table if not exists ads_sg.ads_fin_receivable_unpaid_marketing_detail_df
(
    `business_id`        STRING comment '关联id',
    `contract_id`        STRING comment '合同id',
    `region_id`          STRING COMMENT '所属大区ID',
    `region_name`        STRING COMMENT '所属大区',
    `city_company_id`    STRING COMMENT '所属城市公司ID',
    `city_company_name`  STRING COMMENT '所属城市公司名称',
    `plaza_id`           STRING COMMENT '广场主数据id',
    `plaza_name`         STRING COMMENT '广场简称',
    `category`           STRING COMMENT '类别',
    `budget_amt`         DECIMAL(18, 2) COMMENT '预算金额',
    `contract_type`      STRING COMMENT '合同类型',
    `supplier_name`      STRING COMMENT '乙方单位',
    `contract_amt`       DECIMAL(18, 2) COMMENT '合同金额',
    `change_amt`         DECIMAL(18, 2) COMMENT '预计变更金额',
    `sign_off_amt`       DECIMAL(18, 2) COMMENT '预计结算金额',
    `payable_amt`        DECIMAL(18, 2) COMMENT '累计应付金额',
    `payable_rat`        DECIMAL(18, 4) COMMENT '累计应付比例',
    `payment_amt`        DECIMAL(18, 2) COMMENT '累计已付金额',
    `unpaid_amt`         DECIMAL(18, 2) COMMENT '累计应付未付金额（计算）',
    `is_acceptance_form` INT COMMENT '是否有验收单',
    `acceptance_time`    STRING COMMENT '验收时间',
    `risk_prompt`        STRING COMMENT '风险提示',
    `last_update_time`   timestamp() COMMENT 'etl时间')
    comment '营销类应收未付明细报表'    partitioned by ( stat_date bigint comment '日期分区')
    row format serde 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'    stored as RCFile;

四、数据类型规范

一般情况下大部分字段都采用 string 类型
金额类及其它小数点数据等非整型数据统一使用decimal
分区字段比较特殊，在数仓中统一使用bigint（平台特性原因），非平台项目可以使用date或string
金额类数据如果单位是元，则采用decimal(16,4) 长度类型，如果单位是万元或亿元，则采用 decimal(10,6) 长度类型，其它数据字段类型视具体情况而定
整形数值统一使用BIGINT或INT，最大值10位及以上的整数使用BIGINT，最大值在3-9位的整数使用INT
时间类型数据不做具体要求，可以使用string、也可以根据日期格式使用date或timestamp，对于只包含年月日的数据字段可采用 date 类型；对于既包含年月日也包含时分秒的数据字段可采用 timestamp 类型

五、SQL编码原则及规范

SELECT 查询语句中禁止使用 select *，所有操作必须明确指定列名
SQL代码中应用到的所有关键字、保留字都使用大写，如select、from、where、and、or、union、insert、delete、group、having、count等。
SQL语句多表连表时，应使用表的别名，同时表别名形式来引用列
SELECT语句排列样式上，对于字段应每行一个进行编排，两个字段之间的逗号分割符紧跟在第一个字段后面或第二个字段的前面，as语句应与相应的字段在同一行,多个字段的as建议尽量对齐在同一列上