基于CLICKHOUSE的数据仓库分层规范

数据仓库分层架构

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

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

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

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

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

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

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

接入层

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

（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）。

元数据

（1）数据表元数据

（2）字段元数据

（3）数据表关系

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