数仓设计规范

1、命名规范

1.1 User权限说明

序号 数据库 User 用途 备注
1 Mysql admin 管理员 具有DBA权限
2 Mysql ods ods库用户 镜像层数据访问及操作
3 Mysql dw dw库用户 dw层数据访问及操作
4 Mysql dm dm库用户 数据集市层访问及操作
5 Mysql rpt rpt库用户 应用层数据相关操作

1.2 Table命名规范

# 分类依据 Name Description
1 类型 fct_ 基础事实表
2 类型 dim_ 维度表
3 类型 rpt_ 生成报表所使用的表
4 类型 map_ 桥表
5 类型 inf_ 接口表
6 类型 imp_ 数据导入接口表
7 类型 tmp_ 临时表
8 类型 bic_ 宽表(统一视图)
9 类型 dim_his_ 历史拉链表
10 类型 fct_snap_ 快照事实表
11 类型 fct_accu_ 累积快照事实表
12 时间 **_daily 某个表的天统计粒度
13 时间 **_wkly 某个表的周统计粒度
14 时间 **_mthly 某个表的月统计粒度
15 内容 *user* 客户信息
16 内容 *goods* 产品信息
17 内容 *mrchnt 商户信息
18 内容 *order* 订单信息
19 数据源 *superdz* 来源为超级店长<会废弃>
20 数据源 *cvs* 来源为infortech
21 数据源 *pos* 来源为BI架构的sc同步数据
22 时间 **_real 数据为每小时同步或者实时同步

1.3 Column命名规范

# Rule Description
1 date_id 通用列,时间戳
2 local_modify_date 通用列,表示记录在源系统的更新时间
3 local_create_date 通用列,表示本次etl的时间
4 后缀_xxx_id 表示某个实体的ID列,如:ORDER_ID,PROD_ID,USER_ID
5 后缀_xxx_code 某个实体的Code
6 后缀_xxx_name 某个实体的Name
7 start_date 记录生效时间
8 end_date 记录失效时间
9 cur_flag 当前记录是否最新版本
10 后缀_ytd 年累计值(Year to Date)
11 后缀_mtd 月累计值(Month to Date)
12 后缀_qtd 季度累计值(Quarter to Date)
13 后缀_ld 度量值的LD值(Last Day)
14 后缀_ly 度量值的LY值(Last Year)
15 后缀_lm 度量值的LM值(Last Month)
16 后缀_lq 度量值的LQ值(Last Quarter)
17 xxx_lvl[1~9]_后缀 级别类型字段

1.4字段后缀使用规范

# 各种类型的字段 description
1 后缀_date 类型date,  格式yyyy-mm-dd,例如:2012-3-14
2 后缀_time 类型datetime,格式yyyy-mm-dd hh-mi-ss,例如:2012-3-14 15:02:22
3 后缀_amt 存储金额类型的字段,amt为amount缩写,字段类型int
4 后缀_num 存储数量类型的字段,例如订单数,int
5 后缀_price 存储价格类型字段,int 
6 后缀_cnt 存储次数类型字段,例如点击次数,int
7 后缀_flag 存储特殊字段对应值,例如,是否新用户,可以表示为int 
8 后缀_rate 存储比例字段对应值,例如,佣金比例,可以表示为int
9 后缀_status 存储状态类型状态,例如生命周期状态 lifecycle_status

1.5 FUNCTION/PROCEDURE 命名规范

序号 类型 命名规范 备注
1 PROCEDURE P_ 前缀为P_,后缀可参照table命名规范
2 FUNCTION F_ 前缀为F_,后缀可参照table命名规范
3 CONSTANT C_ 常量前缀为C_,后缀为要使用带有的业务含义描述,多个单词使用"_"隔开
4 VARIABLE V_ 变量前缀为V_,后缀为要使用带有的业务含义描述,多个单词使用"_"隔开
5 GLOBAL VARIABLE GV_ 全局变量前缀为GV_,后缀为要使用带有的业务含义描述,多个单词使用"_"隔开
6 PARAMETER P_ 传入参数前缀为GV_,后缀为要使用带有的业务含义描述,多个单词使用"_"隔开
7 TEMP TABLE TMP_ 程序中使用的临时表需要以"TMP_"开头+表名

1.6建分区表语句

create table fct_bootstrap
(
   date_id              date comment '日期',
   site_id              int(2) comment '站点ID',
   terminal_id          int(2) comment '平台ID',
   utm_id               int(6) comment '渠道ID',
   gu_id                varchar(100) comment 'gu_id',
   version              varchar(20) comment 'version',
   boot_num             bigint comment '启动次数',
   user_id              bigint comment 'user_id',
   update_time          TIMESTAMP comment '更新时间' default now()
)partition by range(TO_DAYS(date_id))
(partition P20140101 values less than (TO_DAYS('2014-01-01')));

1.7维度视图命名规范

  1. 针对有缓慢变化维的维度表(后面备有list)建立视图分别会有V_XXX_CUR(XXX代表维度表,比如DIM_YHD_PM)和V_XXX两个视图,V_XXX_CUR视图代表只取当前有效标记的数据,V_XXX视图代表取所有的数据。对于所需列可根据实际情况而定。见下面例子

2    数据类型定义


1           数仓中整型最小用int<不会使用smallint和tinyint>。
2           金额最小单位存储为分<int或者bigint>。
3           日期时间使用string类型,除特殊情况外,业务系统中的timestamp全部转换为年月日时分秒格式。
4           将过大字段拆分到其他表中。
5           禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。

3    性能规范


1           按需查询,SELECT语句必须显式的指明字段名称,避免 SELECT * FROM …
2           ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面。
3           避免使用%前缀模糊查询,例如LIKE "%juanpi"。
4           WHERE条件中必须使用合适的类型,避免MySQL进行隐式类型转化。
5           WHERE条件中避免非等值条件(IN、BETWEEN、<、<=、>、>=)会导致后面的条件使用不了索引。
6           避免使用between and,增强代码可读性
7           统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1)。

4    存储过程开发规范

4.1 代码注释

请参看存储过程模板
调用存储过程的通用shell模板参考

4.2 索引的建立

索引要根据表的使用情况,建立常用的索引。对于查询中,执行计划经常不会使用到的索引要进行删除。索引的字段数不要过多,通常2个为佳。一个表的索引个数也不要太多。

4.3 分区和复合分区的建立

对于数据量在十万级以上的事实表,需要考虑建立分区或者复合分区。建立分区的字段,也通常是查询中的常用字段。

4.4 表和字段的注释

建立的表的过程中,需要加上对表的注释,解释该表的用途。同时,对表中所有字段也加上注释,解释字段含义,字段值的含义,及字段相关的逻辑。
下面给出,注释的几个例子:
对于字段,首先要给出每个字段的含义。如果字段值是数字,请给出数字的含义。如果字段值是金额,请给出金额的单位。另外,有些字段是通过运算得到的,请将字段的运算逻辑在注释中写明。

字段名 字段注释
CAN_SALE 可采:1.可采 0.不可采
COST_AMT 成本金额:以成本价格计算的金额,单位元
SALE_TIME 销售日期,自营-超市部分按出库时间, 自营-代销部分按确认付款时间
TOT_UV_NUM 总的独立访问数=记录的独立访问数+流失的独立访问数


对于表,首先给出每张表的用途,已经数据的粒度,有效时间,主要存储的数据是哪些。

表名 表注释
RPT_CMS_KPI CMS分省份统计,从2012-03-17开始,包括UV,PV等

4.5 INSERT语句

在insert语句中,需要指定插入的字段。例如:
Insert into table_name (column1,column2)Values(x,y);
Insert into table_name (column1,column2) select column1, column2 ...;

4.6 空值

维表必须增加缺省值
维度表 空值主键 默认设置为-999
事实表 维度字段为空 默认设置为-999
       指标字段依具体需求而定

4.7聚合值

      事实表中被汇总的字段,数值型设置为-99,字符型设置为'TOTAL'

赞(0)
微信
支付宝
微信二维码图片

微信扫描二维码打赏

支付宝二维码图片

支付宝扫描二维码打赏

版权属于: sbboke版权所有。

转载时必须以链接形式注明作者和原始出处及本声明。

张贴在数据库相关标签:

相关文章

0 0 投票数
文章评分
订阅评论
提醒
0 评论
内联反馈
查看所有评论