本文将为您介绍,Hologres在开发过程中的相关规范,帮助您快速了解Hologres的开发要求,避免进行错误的操作。
数据域规范
- 数仓分层
数据仓库以分层建设为主包含如下几类分层,其中CDM包含DWD、DWS、DIM。在Hologres中通过Schema隔离不同的分层。
- ODS:操作数据层
- CDM:公共维度模型层
- DWD:明细数据层
- DWS:汇总数据层
- DIM:维度数据层
- ADS:应用数据层
不同公司可根据业务的复杂程度选择适当的颗粒度,如一个公司存在多个BU,则以BU缩写作为Schema的前缀。
create schema ${bu}_ads; create schema ${bu}_ads_dev; create schema ${bu}_dwd; create schema ${bu}_dwd_dev; create schema ${bu}_dws; create schema ${bu}_dws_dev; create schema ${bu}_dim; create schema ${bu}_dim_dev; create schema ${bu}_ods; create schema ${bu}_ods_dev;
- 数据域缩写
不同的数据域需要定义共享的数据域代码,形成公司共识规范。示例如下。
数据域的名称 数据域对应的缩写示例 交易域 trd 商品域 itm 日志域 log 会员和店铺域 mbr 供销存管理域 dst 销售和客服域 crm 信用和风控域 rsk 工具和服务域 tls 物流和快递域 lgt
命名规范
- 任务命名规范
任务命令时要区分内部任务还是同步任务,命名规则如下。
- 内部SQL任务(非同步任务):
holo_{target_table_name}
(与外部表任务区分)。 - 数据导入Hologres:
{source}2holo_{target_table_name}
。 - Hologres数据导出:
holo2{target}_{target_table_name}
。
- 内部SQL任务(非同步任务):
- 表命名规范
分层名称 当前分层中表的命名规则 示例 DWD ${bu}_dwd.数据域_业务过程_[自定义词根]_后缀
taobao_dwd._trd_ord_flow
DWS ${bu}_dws.数据域_数据粒度缩写_业务过程_[{自定义词根}]_统计时间周期
taobao_dws.trd_all_dtr,taobao_dws.log_slr_pv_dtr
DIM ${bu}_dim.{维度定义}[_{自定义词根}]
taobao_cdm.dim_itm
ADS ${bu}_ads.业务域_维度_[{自定义词根}]_{刷新周期标识}
说明 刷新周期标识如下。- d:按天刷新。
- r:实时刷新。
- h:准实时刷新。
taobao_ads.trd_cate_d
- Table Group命名规范
若是业务需要创建多个Table Group,命名可以采取
${bu}_{数仓分层名}_{业务定义}_tg
的规范。 - 视图命名规范
持久化视图命名规则与示例如下。
- 规则
- DWS:
${bu}_dws.数据域_数据粒度缩写_业务过程_[{自定义词根}]_统计时间周期_v
。 - ADS:
${bu}_ads.业务域_维度_[{自定义词根}]_{刷新周期标识}_v
。
- DWS:
- 示例
taobao_dws.trd_byr_itm_ord_cm_v
- 规则
- 外表命名规范
在原有MaxCompute表名基础下加
ext
后缀,具体示例如下。taobao_dim.camp_ext
- 临时表命名规范
在原有表名基础下加
tmp
前缀及数字序号后缀,具体示例如下。taobao_dim.tmp_camp_01
- 常用缩写词
统计周期 缩写 最近一天 1d 最近多天 nd 累计 td 自然周 cw 自然月 cm 截止当前累计 dtr 截止当前小时累计 dhr
表开发规范
- 内表规范
创建表之前必须按照数据模型规范确定表和字段的命名,并根据需求确认表的生命周期,为表和字段添加完整注释,相关规范如下。
- 强控规范(如不符合则不允许发布):
- 输出的表与字段需包含Comment,适用于全平台数据研发操作场景,表的Comment应确保描述信息简练、清晰。
- 建表语句中需包含有表的生命周期(time_to_live_in_seconds)。
- 建表语句需带有分布键索引(distribute_key),分部键选择原则如下。
足够分散、最常JOIN或者GROUP BY的字段。例如买家商品表,可以设置user_id和item_id,但如果常关联的KEY为user_id,则分布键设置user_id而非user_id和item_id。
- 进行关联查询的表需要创建在同一个Table Group中。
- 同一个实体ID,在所有事实表和维表中保持名称和数据类型一致,比如交易表中用户ID为user_id,在维表中也为user_id,而不能是uid,同时数据类型保持一致,减少数据类型的转换。
- 所有物理表的分区字段默认使用
ds
表示。
- 建议规范:
- 建表语句应当带有bitmap_columns、segment_key、cluster_key任意之一。
- 在不明确字段基数情况下,不建议设置建表属性
dictionary_encoding_columns
(字典索引),您可调用如下内容将属性置空。call set_table_property('table_name', 'dictionary_encoding_columns','')
- 建表属性orientation(数据存储格式)建议使用column,可以设置为row。
说明 除非明确该表的查询能够始终指定所有的primary key(等于或者in),否则尽量不要使用row,不设置时默认是用column存储。
- 建表属性bitmap_columns(比特编码),bitmap可以对存储文件内部的数据进行快速过滤。
- 建议把filter条件的数据建成bitmap_columns,默认情况下会将所有的TEXT字段设置。
- 不建议枚举值过多的字段,比如user_id,建议活动ID这类指标设置为bitmap_columns。
- 建表属性event_time_column需用在与实时写入有关的字段上,例如事件时间戳。
- 建表属性clustering_key聚簇索引,Hologres会在聚簇索引上对数据进行排序,建立聚簇索引能够加速在索引列上的range和filter查询,仅能设置一组。针对对于范围过滤适用,比如gmv分档时。
- 强控规范(如不符合则不允许发布):
- MaxCompute外表规范
Hologres支持通过外表对MaxCompute进行加速查询,可简化数据同步的流程。为了提升计算性能,非必要场景不建议您使用内表与外表关联。为更好的管理和维护外表,请遵循如下规范。
- 强控规范:您需严格按照外表命名规范,在原有的MaxCompute表名基础下增加
ext
后缀。 - 建议规范:
- 保留外表的DDL,做好版本之间的管理。
- 不建议内表与外表关联使用,建议采用外表同步至内表的方式。
- 强控规范:您需严格按照外表命名规范,在原有的MaxCompute表名基础下增加
- 视图规范
- 强控规范:您需严格按照视图命名规范。
- 建议规范:
- 建议您开启任务调度,保障后续开发作业依赖链路完整。
- 建议不同粒度的视图单独创建,避免综合请求计算量过大。
例如,cw、cm、nd、1d等,可分别建立4个视图。如存在分端,则建立pc、wap、app。如分采集方式,可以分ut和非ut。
- 生命周期(仅限内表)规范
数仓分层 对应的生命周期规则描述 DWD 天级增量明细,建议不超过2年。 DWS 天级增量明细,建议不超过2年。 DIM 大维表建议进行极限存储建模后永久保存,小维表与MaxCompute表保持一致。 大、小维表的界定标准:单分区不可超过1 TB。
若是有分区表,建议按照实时任务写入当天分区,并且按照数仓分层设置合适的TTL,且更新的历史数据不应该写入已经超过TTL设置的分区。
- Table Group规范(可选)每个数据库都会有默认的Table Group和Shard数,您可以根据业务需要新建Table Group或者修改Shard数,以此达到更好的性能,建议规范如下。
- 如无必要不要新建Table Group。
- 数据量较大的表,可独立新建Shard数较大的Table Group。
- 有大量数据量较小的表,可适当创建一个Shard数较小的Table Group。
- 需要Join关联查询的表,必须放在同一个Table Group。
字段开发规范
- 字段类型规范
字段类型需严格按照如下要求进行创建。
字段/字段后缀 字段注释 示例 缩写 user_id 自增会员ID user_id=232442843
int8
item_id 商品ID item_id=63283278784383
int8
member_id 注册会员ID member_id=b2b-dsajk2343821b
TEXT *amt* 金额类 pay_ord_amt_1d_001=923.23
NUMERIC *fee* 费用类 post_fee=923.23
NUMERIC *cnt* 数量类 pay_ord_byr_cnt_1d_001=923
int4/int8
is_* 是否类 is_pm=Y/is_pm=true
TEXT/BOOL ds 分区 ds=20210120
YYYYMMDD - 基本数据类型参考
目前,Hologres数据类型与PostgreSQL数据类型兼容,但支持的数据类型是PostgreSQL的一个子集。详细字段类型及MaxCompute映射,请参见数据类型汇总。
- 货币单位及精度
货币单位统一为美元。除非模型有特殊说明,否则金额相关的数据不做任何四舍五入操作,以免后续汇总计算中造成不同口径的汇总结果不一致。
SQL规范
- 强控规范:
- SQL最外层及子查询内层不需要计算的字段禁止使用
select *
操作,所有操作必须明确指定列名。 - Where条件中空字段和空字符串要进行必要的Coalesce处理。
- SQL最外层及子查询内层不需要计算的字段禁止使用
- 建议规范:
- 常count distinct字段采用该字段作为distribution keys,对于多个count distinct的组合需要手动的改写。
select count(distinct userid) , count(distinct case when stat_date = '20201111' then userid end) from t group by cate_id; 改写成 select count(1), sum(c) from ( select userid , cate_id , cast(count(case when stat_date = '20201111' then 1 end) > 0) as c from t group by cate_id, userid ) t1 group by cate_id;
- 离线调度任务增加analyze table操作分区表。
- 针对长周期使用场景,批操作时采用ATTACH/DETACH操作历史分区,避免数据指标大起大落。
- 常count distinct字段采用该字段作为distribution keys,对于多个count distinct的组合需要手动的改写。