基于CLICKHOUSE的数据仓库分层规范
杨启洁 万山数据

数据仓库我们一般分为接入层、明细层、实体层、主题层、应用层。各层存储的数据粒度不同。

接入层:一般存储接收的原始数据,并给接入的数据打上接收时间戳。

明细层:一般存储合并后的全量原始数据。

实体层:按照业务实体生成的宽表。

主题层:按照业务主题汇聚的数据集。

应用层:按照具体应用汇聚的数据集。

下面我们逐层介绍各层数据表的应用标准规范。

image.png

接入层

从源系统接入的数据,数据格式与源系统保持一致,每次增量插入。

(1)建表规范:

字段基础类型以“select toTypeName({col}) from jdbc(‘{datasource}’,’select {col}  from {table}’) limit 1”查询结果为准。

添加入库时间odgTime数据类型为时间类型,用来与源库中的数据进行对账。

建表时排序键处理:以 CityHash64(*)as __allCityHash64作为排序键。

需设置采样键为,__allCityHash64。

(2)数据清理策略:

保留时长:7天(入仓时间),自动删除语句为 “TTL odgTime + toIntervalDay(7)”。

(3)数据模型设计流程:

  1. 确定数据源表

  2. 通过JDBC获取技术元数据

  3. 创建物理表

  4. 添加数据表、字段、表关系元数据

明细层

明细层中的数据是基于接入层的原始数据进行数据清洗,去重等处理过程,得到拥有高质量的数据。

(1)建表规则:

所有表必备三个字段:pkTuple(由主键字段组成的元组,Tuple类型);pkHash(pkTuple通过cityHash64计算值,Int64类型);odgTime(更新时间,DateTime类型)。

建表时排序键处理:用源表业务主键作为排序键。

(2)去重规则:

按照业务主键取最新,若无明确业务主键情况,则所有业务数据列为业务主键。

(3)数据清理策略:

永久保存。

(4)数据模型设计流程:

  1. 根据接入层对应数据表创建物理表

  2. 添加数据表、字段、表关系元数据

实体层

按照业务域,以逻辑实体为基础,增加技术属性生成的物理表。

(1)实体设计原则:

遵从业务模型,兼顾数据来源。维度原则性不退化,不丢属性。

(2)合宽表原则:

被合并的实体存在共同的业务主键。

(3)更新规范:

增量更新,相同业务主键,数据取最新。

(4)数据清理策略:

数据永久保存

(5)建表规则:

所有表必备三个字段:pkTuple(由主键字段组成的元组,Tuple类型);pkHash(pkTuple通过cityHash64计算值,Int64类型);odgTime(更新时间,DateTime类型)。

建表时排序键:实体主键。

(6)数据模型设计流程:

  1. 根据逻辑模型添加数仓必需字段信息,创建物理表

  2. 添加数据表、字段、表关系元数据


主题层

按照业务域,依据指标说明进行汇总。

(1)设计原则:

按照“实体基本信息+维度+统计指标集”原则设计主题层表。例如生产厂指标集表,由生产厂编号、名称等基本信息,月份、取值科目(业务收入类型)等维度,产值、生产成本等指标构成。

(2)更新规则:

全量更新为主,增量(数据量超千万时)更新为辅。

(3)转存规则:

落表后推送交互区。

(4)建表规则:

所有表必备三个字段:pkTuple(由主键字段组成的元组,Tuple类型);pkHash(pkTuple通过cityHash64计算值,Int64类型);odgTime(更新时间,DateTime类型)。

(5)数据模型设计流程:

  1. 梳理指标集,按照实体确定业务字段信息

  2. 按照建表规则创建物理表

  3. 添加数据表、字段、表关系元数据


应用层

应用层的数据是经过最终汇总出来的数据,用来支撑前端报表、BI系统、分析系统的展示。

(1)设计原则:

报表类数据表,以报表展示的数据列为列,行按照报表筛选条件展开。

详细数据类数据表,以前台使用为基础定义列,建立分布式表,直接从实体层或主题层取数。

(2)更新规则:

报表类数据表全量更新。

详细数据类型数据表,由实体层、主题层更新。

(3)转存规则:

报表类数据表落表后推送交互区。

原则严禁存详细数据,详细数据均由分布式表实现。

(4)建表规则:

原则上按照筛选条件、汇总条件设计预计算规则,必须存在能标识数据唯一性的主键。

(6)数据模型设计流程:

  1. 梳理应用需求,确定字段信息

  2. 按照建表规则创建物理表

  3. 添加数据表、字段、表关系元数据


知识库

知识库可以被各层使用,由代码表、映射表构成。

(1)维护策略:

原则上,每个知识库每张表来源唯一,且有责任人。

(2)设计规范:

维度代码为数字(现状多以字母加数字,不调整)。

(3)设计规范:


  1. 需建立对应的字典。

  2. 通过字典函数调用,不应通过JOIN表方式调用。

  3. 同一个代码对应多个属性时,应合并到一个字典。

  4. 字典调用方式为dictGet('dict_name', attr_names, toUInt64(expr))

  5. 若代码不为数字时,调用方式为dictGet('dict_name', attr_names, cityHash64(expr))

  6. 默认值为空字符串(非NULL)。

  7. 元数据

  8. (1)数据表元数据


image.png

(2)字段元数据

image.png

(3)数据表关系


一组关系由两个表的字段组成,为确保字段的唯一性,需要明确字段所有的数据库、数据表。系统根据表关系自动生成关系图谱。


CIO之家 www.ciozj.com 公众号:imciow
关联的文档
也许您喜欢