数仓规范：命名规范如何设计？| 规范1

会飞的一十六 2024-10-18

599

点击上方【蓝字】关注我们

数据仓库建设的目的仍是为了下游应用，因此，降低下游用户的应用成本是至关重要的。下面这些问题，你可以看看是否遇到过。很多表名非常类似，涉及各个层级的数据，也不知道粒度如何，用起来非常混乱！表内的字段名特别乱，甚至不同表中相同含义字段的命名不一致！任务名与表名差异很大，很难找到表对应的调度任务是哪一个！以上这些问题，都是由于命名不规范所导致的。那规范的命名又包括哪些方面呢？下面，我们一起来看一下。

表命名规范

表命名，核心原则是“见名知意”，通过规范的标准，降低用户的识别成本。表命名的通用方式为[数据分层]_[业务域]_[内容描述]_[刷新周期]_[存储策略]。

1.1 前缀命名

维表命名形式：dim_描述
事实表命名形式：fact_描述_[AB]
临时表命名形式：tmp_ 正式表名_ [C自定义序号]
宽表命名形式：dws_主题_描述_[AB]
备份表命名形式：正式表名_bak_yyyymmdd

表命名解释：

1）表名使用英文小写字母，单词之间用下划线分开，长度不超过40个字符，命名一般控制在小于等于6级。
2）其中ABC第一位"A"时间粒度：使用"c"代表当前数据，"h"代表小时数据，"d"代表天数据，"w"代表周数据，"m"代表月数据，"q"代表季度数据， "y"代表年数据。
3）其中ABC的第二位"B"表示对象属性，用"t"表示表，用"v"表示视图。
4）其中ABC的第三位"C"自定义序号用于标识多个临时表的跑数顺序。

1.2 注释

注释要结合表的英文名，要求注释简洁明了，体现出表的业务出处、主题和用途。

1.3 存储格式

所谓的存储格式就是在Hive建表的时候指定的将表中的数据按照什么样子的存储方式，如果指定了方式，那么在向表中插入数据的时候，将会使用该方式向HDFS中添加相应的数据类型。在数仓中建表默认用的都是PARQUET存储格式，相关语句如下所示：

STORED AS INPUTFORMAT
 
‘org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat’
 
OUTPUTFORMAT
 
‘org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat’

1.4 字符集

Hadoop和hive 都是用utf-8编码的，在建表时可能涉及到中文乱码问题，所以导入的文件的字符编码统一为utf-8格式。

1.5 约定

理论上在数仓落地的表不应该出现null未知类型，对于可能出现null的字段，如果为字符型统一为空字符串，如果是数值则给0。

1.6 刷新周期

1.7 存储策略

1.8 来源系统

如果数据来自特定的源系统，可以在名称中标注。

例如：_erp
、_crm
、_pos

-- 创建订单事实表（每日粒度）
CREATE TABLE fact_order_daily (
    order_id INT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    -- 其他字段...
);
 
-- 创建客户维度表
CREATE TABLE dim_customer (
    customer_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    -- 其他字段...
);
 
-- 创建来自ERP系统的库存汇总表（月度粒度）
CREATE TABLE dws_inventory_monthly_erp (
    product_id INT,
    warehouse_id INT,
    month_date DATE,
    avg_stock_qty INT,
    -- 其他字段...
);
 
-- 创建临时表用于数据处理
CREATE TABLE tmp_order_analysis (
    -- 临时分析用字段...
);

注意点：

全小写：使用全小写字母，避免大小写敏感性问题。
下划线连接：使用下划线 _
连接不同的单词，提高可读性。
避免特殊字符：不要使用空格、横杠或其他特殊字符。
长度限制：表名长度应控制在64个字符以内，以适应大多数数据库系统的限制。

字段命名规范

字段命名作用同表命名作用一致，同样是为了减少应用上的歧义。字段命名需要遵循以下几项原则。

字段名需含义清晰，唯一解释，不可出现同名不同义的情况。

字段名建议用英文简写，不建议用拼音，更不建议用拼音简写。

字段名一律小写，避免出现混乱的情况。·

字段名各单词间用下画线进行分割。字段名长度不宜过长，建议控制在20个字符以内。

字段命名的通用方式为[修饰词]_原子指标_[时间修饰]，以“过去30日内搜索点击次数指标”为例

1)修饰词

修饰词是对指标场景的描述，例如PV是一个原子指标，那么主页PV、列表页PV、详情页PV就是在PV基础上增加的修饰词，将原子指标包装成一个用于描述特定场景的数据。

2)原子指标

原子指标可以理解为指标的内核、业务指标的最小颗粒度，例如PV、UV、人均PV、人均时长等。根据指标的类型，原子指标的命名规范如图所示。

3）时间修饰

大多数情况下，指标描述的是当日的数据表现，但仍有些应用场景，需要记录过去或者未来一段时间的表现情况。例如，过去7日搜索PV、过去第7日搜索PV、未来7日搜索PV、未来第7日搜索PV等。对于这种需要跨度的时间修饰，命名需要遵循一定原则，用以区分过去／未来、N日内／第N日，如图所示。

3）派生指标

4）衍生指标

注意点：

（1）前缀使用：对于特定类型的字段，可以使用前缀。

is_ 表示布尔值（例如：is_active）
has_ 表示布尔值（例如：has_children）
num_ 表示计数（例如：num_orders）
pct_ 表示百分比（例如：pct_discount）
amt_ 表示金额（例如：amt_total）

时间字段：对于时间相关的字段，使用统一的命名方式。

date 后缀表示日期（例如：order_date）
time 后缀表示时间（例如：login_time）
timestamp 后缀表示时间戳（例如：create_timestamp）

主键：主键字段通常使用 id 作为后缀。

例如：customer_id、order_id
外键：外键字段应与引用表的主键名称保持一致。


例如：如果 customer 表的主键是 customer_id，那么在 order 表中引用它的外键也应该叫 customer_id

全小写：使用全小写字母。

下划线连接：使用下划线 _ 连接不同的单词。

避免保留字：不要使用数据库的保留字作为字段名。

避免缩写：除非是广为人知的缩写（如 ID），否则应使用完整单词。

一致性：相同含义的字段在不同表中应保持一致的命名。

示例：

CREATE TABLE fact_order (
    order_id INT PRIMARY KEY,
    customer_id INT,  -- 外键，引用 dim_customer 表
    order_date DATE,
    order_timestamp TIMESTAMP,
    total_amount DECIMAL(10,2),
    num_items INT,
    is_paid BOOLEAN,
    payment_method VARCHAR(50),
    discount_pct DECIMAL(5,2),
    shipping_amt DECIMAL(8,2),
    has_gift_wrap BOOLEAN,
    -- 其他字段...
);
 
CREATE TABLE dim_customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    registration_date DATE,
    is_active BOOLEAN,
    last_login_timestamp TIMESTAMP,
    lifetime_value DECIMAL(12,2),
    -- 其他字段...
);

在这个例子中，我们可以看到：

字段名清晰描述了其内容（例如 total_amount、num_items）
使用了适当的前缀（例如 is_paid、discount_pct）
时间相关字段使用了统一的后缀（_date、_timestamp）
主键和外键的命名保持一致（customer_id）
所有字段名都是小写并使用下划线连接
通过遵循这些规则，我们可以创建出清晰、一致且易于理解的数据模型，大大提高了数据仓库的可用性和可维护性。

任务命名规范

表的生成代码往往需要配置一个调度任务加以完成，这里需要注意，调度任务命名需与表命名一致，建议采用相同的名称，如果已被占用，建议使用表命名作为基准，增加后缀作为任务命名，方便后续查找应用。

命名规范

ETL 脚本名称尽可能和所产出的表同名；
数据采集、数据推送脚本尽可能标识数据去向；
ETL 脚本若产生多个表，采用对应的数据域和语义描述命名；
Jar 包命名以实际的业务处理逻辑语义描述为主，调度任务命名同样尽量以产出表名命名根据脚本命名规范。

具体实例

我们对如下的脚本进行命名：

数据导出脚本命名：

dws_jttl_pull_month_tmp_exp.sh

，其中exp表示向外导出用于标识方向

ETL过程脚本命名：

     dws_jttl_pull_month.sh

一个ETL脚本产生多个表：

dws_phm_switchshock_min_hh_mm.sh

该脚本文件描述了按分钟、小时、月份转辙机振动的监控数据。说明了该脚本有三张表，一张按分钟统计的表，一张按小时统计的表、一张按月份统计的表。

层级命名规范

自定义函数命名规范

自定义函数命名规范在表的生成过程中，一般会用到SQL的内置函数，然而在面对一些复杂逻辑，内置函数无法满足需求时，往往需要自己创建函数，以满足对应的应用场景。为了便于应用及管理，自定义函数命名需要遵循一定规范。自定义函数命名的通用方式为[业务域]_udf/udaf/udtf_[内容描述]，以“城市等级划分函数”为例，如图所示。