实时数仓Hologres：Hologres开发规范

数仓分层

数据仓库以分层建设为主包含如下几类分层，其中CDM包含DWD、DWS、DIM。在Hologres中通过Schema隔离不同的分层。

• ODS（Operational Data Store）：操作数据层

• CDM（Common Data Model）：公共维度模型层

  • DWD（Data Warehouse Detail）：明细数据层

  • DWS（Data Warehouse Summary）：汇总数据层

  • DIM（Dimension）：维度数据层

• ADS（Application Data Service）：应用数据层

不同公司可根据业务的复杂程度选择适当的颗粒度，如一个公司存在多个BU（Business Unit），则以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;

数据域缩写

不同的数据域需要定义共享的数据域代码，形成公司共识规范。示例如下。

任务命名规范

任务命令时要区分内部任务还是同步任务，命名规则如下。

• 内部SQL任务（非同步任务）：holo_{target_table_name} （与外部表任务区分）。

• 数据导入Hologres：{source}2holo_{target_table_name}。

• Hologres数据导出：holo2{target}_{target_table_name}。

表命名规范

Table Group命名规范

若是业务需要创建多个Table Group，命名可以采取${bu}_{数仓分层名}_{业务定义}_tg的规范。

视图命名规范

持久化视图命名规则与示例如下。

• 规则

  • DWS：${bu}_dws.数据域_数据粒度缩写_业务过程_[{自定义词根}]_统计时间周期_v。

  • ADS：${bu}_ads.业务域_维度_[{自定义词根}]_{刷新周期标识}_v。

• 示例

  taobao_dws.trd_byr_itm_ord_cm_v

外表命名规范

在原有MaxCompute表名基础下加ext后缀，具体示例如下。

taobao_dim.camp_ext

临时表命名规范

在原有表名基础下加tmp前缀及数字序号后缀，具体示例如下。

taobao_dim.tmp_camp_01

常用缩写词

内表规范

创建表之前必须按照数据模型规范确定表和字段的命名，并根据需求确认表的生命周期，为表和字段添加完整注释，相关规范如下。

• 强控规范（如不符合则不允许发布）：

  • 输出的表与字段需包含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，做好版本之间的管理。

  • 不建议内表与外表关联使用，建议采用外表同步至内表的方式。

视图规范

• 强控规范：您需严格按照视图命名规范。

• 建议规范：

  • 建议您开启任务调度，保障后续开发作业依赖链路完整。

  • 建议不同粒度的视图单独创建，避免综合请求计算量过大。

    例如，cw、cm、nd、1d等，可分别建立4个视图。如存在分端，则建立pc、wap、app。如分采集方式，可以分ut和非ut。

生命周期（仅限内表）规范

建议规范：

若是有分区表，建议按照实时任务写入当天分区，并且按照数仓分层设置合适的TTL，且更新的历史数据不应该写入已经超过TTL设置的分区。

Table Group规范（可选）

字段类型规范

字段类型需严格按照如下要求进行创建。

基本数据类型参考

目前，Hologres数据类型与PostgreSQL数据类型兼容，但支持的数据类型是PostgreSQL的一个子集。详细字段类型及MaxCompute映射，请参见数据类型汇总。

货币单位及精度

货币单位统一为美元。除非模型有特殊说明，否则金额相关的数据不做任何四舍五入操作，以免后续汇总计算中造成不同口径的汇总结果不一致。

强控规范：

• SQL最外层及子查询内层不需要计算的字段禁止使用select *操作，所有操作必须明确指定列名。

• Where条件中空字段和空字符串要进行必要的Coalesce处理。

建议规范：

• 常采用count distinct字段作为distribution_key，对于多个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操作历史分区，避免数据指标大起大落。