数仓建设：如何设计数据治理考评规则？

会飞的一十六 2024-11-11

134

0 为什么要数据治理？

随着大数据技术的逐渐普及，越来越多的企业搭建了数据仓库、实时数仓、数据湖等等。但是由于企业各个系统平台数据源复杂，企业的组织结构的复杂，前后时期口径不同一等等原因，最终造成：

数据质量低：数据错误、不准确或不一致，导致决策依据不可靠，影响企业的运营效率和盈利能力。
数据孤岛：各部门和系统之间的数据无法有效整合，形成数据孤岛，降低数据的利用价值和沟通效率。
数据安全风险：缺乏有效的数据保护措施，可能导致敏感数据泄露，增加企业的法律和财务风险。
规范性问题：在数据使用、存储和传输过程中，可能无法满足规范性要求，增加数据的理解难度，降低使用率。
难以支持创新：由于数据质量、一致性和安全性问题，企业难以利用数据驱动创新和提升竞争力。
难以实现数据驱动：在没有良好数据治理的情况下，企业难以实现数据驱动的决策、市场营销和产品开发。
用户体验受损：数据问题可能导致客户和员工的不良体验，影响客户满意度和员工士气

2 什么是数据治理？

数据治理是一种系统化的方法，旨在管理企业中数据的质量、一致性、安全性和完整性。它涉及一系列策略、流程、技术和工具，帮助组织有效地收集、存储、访问和利用数据。数据治理在以下几个方面具有重要意义：

数据质量：确保数据的准确性、一致性和可用性，降低错误和冗余，为决策提供可靠基础。

数据规范：通过标准化和数据整合，使不同部门和系统间的数据具有统一的标准规范，提高沟通效率和数据利用价值。

数据安全：保护敏感数据，防止未经授权的访问和泄露，确保合规性和隐私保护。

数据完整性：维护数据的完整性，确保不受损坏或误操作影响，提高数据恢复能力。

数据策略和流程：制定和执行数据治理政策，明确数据所有权和责任，促进组织内数据的有效管理。

3 如何数据治理如何落地？

数据治理本身是一个理念，必然需要具体的实施策略。

就像许多年前企业从纸质办公过渡到信息化建设，势必就搭建各种OA、CRM、ERP 等各种企业管理系统，来实现企业的业务流程和管理制度。

数据治理本身也是一种管理制度，对应的落地解决方案中最常见的就是数据中台。

一个完善的数据中台项目，可谓是“一站式”的数据处理与治理平台，包含但不限于如下功能：

数据接入与集成：支持多种数据源的接入和集成，包括结构化、非结构化和半结构化数据，实现数据的统一收集和管理。
数据清洗与转换：提供数据清洗、转换和标准化功能，以提高数据质量和一致性，满足不同业务需求。
数据存储与管理：具备高效、可扩展的数据存储和管理能力，确保数据的完整性和安全性。
数据质量管理：监控和评估数据质量，包括准确性、完整性、一致性、及时性等方面，确保数据可靠性。
元数据管理与血缘管理：元数据管理以及数据血缘功能，方便用户发现、理解和使用数据。
数据安全与合规：提供数据加密、访问控制、审计等功能，确保数据安全，同时满足相关法规和行业标准的合规要求。
数据分析与可视化：支持多维度的数据分析和可视化功能，帮助用户快速洞察数据背后的价值和趋势。
数据共享与服务：提供数据共享和API服务功能，便于跨部门和系统间的数据协同和价值创造。
数据治理策略与流程：制定和实施数据治理策略、流程和规范，明确数据所有权和责任，促进组织内的数据治理。
数据监控与运维：实时监控数据中台的性能和运行状况，确保数据的高可用性和稳定性，及时处理潜在问题。

总之，一个完善的数据中台应具备数据接入与集成、清洗与转换、存储与管理、质量管理、元数据管理、血缘管理、安全与合规、分析与可视化、共享与服务、治理策略与流程、监控与运维等多种功能，以支持企业实现高效的数据治理和价值创造。

4 数据考评的指标

平台把考评的指标分为5个大类：

规范：主要是考评数据是否合规、信息是否完整、责任划分是否明确。
存储：主要考评数据存储规划是否合理。：
计算：主要考评计算逻辑规划是否合理
质量：主要考评数据产出是否符合预期
安全：主要考评数据是否安全

5 考核指标列表

考评板块	考评指标	考评标准	需要信息
规范	有技术owner	有则10分，无则0分	元数据
	有业务 owner	有则10分，无则0分	元数据
	表名合规	参考建数仓表规范 ODS层：开头:ods 结尾 :inc/full 结构ods_xx_( inc\|full) DIM层 : dim开头 full/zip 结尾结构: ods_xx_( inc\|full) DWD层: dwd 开头 inc/full 结尾结构：dwd_xx_xx_(inc\|full) DWS层：dws开头结构dws_xx_xx_xx_ (1d/nd/td) ADS层：ads 开头结构 ads_xxx DM层：dm开头结构: dm_xx 符合则 10分，否则0分 OTHER：未纳入分层，给5分	元数据
	表有备注	有则10分，无则0分	元数据
	字段有备注信息	有备注字段/所有字段 *10分	元数据
存储	生命周期合理	未设定周期类型的给 0分周期类型为永久则给10分周期类型为普通 : 无分区信息的给0分没设生命周期给0分周期长度超过建议周期天数{days}给50分	元数据
	是否空表	空表则0分，有数据则10分	元数据
	存在相似表	同层次两个表字段重复超过{percent}%，则给0分，其余给10分	元数据
计算	长期无产出	一张表{days}天内没有产出数据则给0分，其余给10	检查hdfs
	长期无访问	一张表{days}天内没有访问则给0分，其余给10	检查hdfs
	计算中有报错	检查DS 有报错则给0分，其余给10分	任务信息
	简单加工	sql语句没有任何join\groupby\ 非分区字段的where过滤，以上情况给0分，其余给10分	任务信息
质量	表产出时效监控	前一天产出时效，超过前x天产出时效平均值n% 则给0分，其余10分	任务信息
	表产出数据量监控	必须日分区表前一天产出的数据量，超过前x天平均产出量{upper_limit}% ，或低于{lower_limit}% ，则给0分，其余10分	检查hdfs
安全	未明确安全等级	未设置 0分其余10分	元数据
	目录文件数据访问权限超过建议值	检查该表最高权限的目录或者文件，如果超过{auth}则给0分其余给10分	检查hdfs

6 数仓团队应如何建设？

6.1 考评指标分析

考评板块	考评指标	考评标准	需要信息
规范	有技术owner	有则10分，无则0分	元数据
	有业务 owner	有则10分，无则0分	元数据
	表名合规	参考建数仓表规范 ODS层：开头:ods 结尾 :inc/full 结构ods_xx_( inc\|full) DIM层 : dim开头 full/zip 结尾结构: dim_xx_( full/zip) DWD层: dwd 开头 inc/full 结尾结构：dwd_xx_xx_(inc\|full) DWS层：dws开头结构dws_xx_xx_xx_ (1d/nd/td) ADS层：ads 开头结构 ads_xxx DM层：dm开头结构: dm_xx 符合则 10分，否则0分 OTHER：未纳入分层，给5分	元数据
	表有备注	有则10分，无则0分	元数据
	字段有备注信息	有备注字段/所有字段 *10分	元数据
存储	生命周期合理	未设定周期类型的给 0分周期类型为永久、拉链表则给10分周期类型为日分区 : 无分区信息的给0分没设生命周期给0分周期长度超过建议周期天数{days}给5分	元数据
	是否空表	空表则0分，有数据则10分	元数据
	存在相似表	同层次两个表字段重复超过{percent}%，则给0分，其余给10分	元数据
计算	长期无产出	一张表{days}天内没有产出数据则给0分，其余给10	检查hdfs
	长期无访问	一张表{days}天内没有访问则给0分，其余给10	检查hdfs
	计算中有报错	检查DS 有报错则给0分，其余给10分	任务信息
	简单加工	sql语句没有任何join\groupby\ 非分区字段的where过滤，以上情况给0分，其余给10分	任务信息
质量	表产出时效监控	前一天产出时效，超过前{days}天产出时效平均值n% 则给0分，其余10分	任务信息
	表产出数据量监控	必须日分区表前一天产出的数据量，超过前x天平均产出量{upper_limit}% ，或低于{lower_limit}% ，则给0分，其余10分	检查hdfs
安全	未明确安全等级	未设置 0分其余10分	元数据
	目录文件数据访问权限超过建议值	检查该表最高权限的目录或者文件，如果超过文件超过{file_permission}或者目录超过{dir_permission}则给0分其余给10分	检查hdfs

6.2 健康分计算规则分析

计算表的健康分值

 数据表健康分公式=   （  规范得分/规范总分 * 规范权重
+ 存储得分/存储总分*存储权重   
+ 计算得分/计算总分*计算权重  
                         + 质量得分/质量总分*质量权重  
+ 安全得分/存储总分*安全权重  
）*  数据表权重

各个板块权重

板块	权重
规范	15
存储	30
计算	30
质量	15
安全	10

要把每张表的每个指标项进行计算，再按照公式计算数据表的健康分，同时核算到每个技术owner的负责表的总健康分，最终核算为全数仓的总健康分。

6.3 执行步骤分析

编辑

6.4 表结构设计

考评指标参数表

create table dga.governance_metric
(
    id                 bigint auto_increment comment 'id'
        primary key,
    metric_name        varchar(200)  null comment '指标名称',
    metric_code        varchar(200)  null comment '指标编码',
    metric_desc        varchar(2000) null comment '指标描述',
    governance_type    varchar(20)   null comment '治理类型',
    metric_params_json varchar(2000) null comment '指标参数',
    governance_url     varchar(500)  null comment '治理连接',
    is_disabled        varchar(1)    null comment '是否启用'
)
    comment '考评指标参数表';

考评指标类别权重表

  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `type_code` varchar(200) DEFAULT NULL COMMENT '治理项类型编码',
  `type_desc` varchar(2000) DEFAULT NULL COMMENT '治理项类型描述',
  `type_weight` decimal(10,2) DEFAULT NULL COMMENT '治理类型权重',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 
COMMENT='理考评类别权重表';

治理考评结果明细

CREATE TABLE `governance_assess_detail` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `assess_date` varchar(20) DEFAULT NULL COMMENT '考评日期',
  `table_name` varchar(200) DEFAULT NULL COMMENT '表名',
  `schema_name` varchar(200) DEFAULT NULL COMMENT '库名',
  `metric_id` varchar(200) DEFAULT NULL COMMENT '指标项id',
  `metric_name` varchar(200) DEFAULT NULL COMMENT '指标项名称',
  `governance_type` varchar(200) DEFAULT NULL COMMENT '治理类型',
  `tec_owner` varchar(200) DEFAULT NULL COMMENT '技术负责人',
  `assess_score` decimal(10,2) DEFAULT NULL COMMENT '考评得分',
  `assess_problem` varchar(2000) DEFAULT NULL COMMENT '考评问题项',
  `assess_comment` varchar(2000) DEFAULT NULL COMMENT '考评备注',
  `is_assess_exception` varchar(1) DEFAULT '0' COMMENT '考评是否异常',
  `assess_exception_msg` varchar(2000) DEFAULT NULL COMMENT '异常信息',
  `governance_url` varchar(2000) DEFAULT NULL COMMENT '治理处理路径',
  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB   DEFAULT CHARSET=utf8mb3 COMMENT='治理考评结果明细';

6.5 一般简单指标规则分析

（1）是否有业务Owner

考评板块	考评指标	考评标准	需要信息
规范	有业务owner	有则10分，无则0分	元数据

（2）是否有表备注

考评板块	考评指标	考评标准	需要信息
规范	表有备注	有则10分，无则0分	元数据

（3）是否缺失字段备注

考评板块	考评指标	考评标准	需要信息
规范	字段有备注信息	有备注字段/所有字段 *10分	元数据

（4）是否空表

考评板块	考评指标	考评标准	需要信息
存储	是否空表	空表则0分，有数据则10分	元数据

（5）是否设置了安全级别

考评板块	考评指标	考评标准	需要信息
安全	未明确安全等级	未设置 0分其余10分	元数据

（6）长期未被访问表

考评板块	考评指标	考评标准	需要信息
计算	长期无访问	一张表{days}天内没有访问则给0分，其余给10	长期无访问

（7）长期未产出表

考评板块	考评指标	考评标准	需要信息
计算	长期无产出	一张表{days}天内没有产出数据则给0分，其余给10	检查hdfs

6.6 一般难度指标规则分析

（1）表名是否合规

考评板块

考评指标

考评标准

需要信息

规范

表名合规

参考建数仓表规范

ODS层：开头:ods 结尾 :inc/full

结构ods_xx_( inc|full)

DIM层 : dim开头 full/zip 结尾

结构: dim_xx_( zip|full)

DWD层: dwd 开头 inc/full 结尾

结构：dwd_xx_xx_(inc|full)

DWS层：dws开头

结构dws_xx_xx_xx_ (1d/nd/td)

ADS层：ads 开头

结构 ads_xxx

DM层：dm开头

结构: dm_xx

符合则 10分，否则0分

OTHER：

未纳入分层，给5分

元数据

本身来说这指标其实就是字符串检查并不复杂，不过这个指标可以通过正则表达式使代码变得更本身来说这指标其实就是字符串检查并不复杂，不过这个指标可以通过正则表达式使代码变得更加清晰简洁，而减少字符串切割或字符串匹配的繁琐。

正则表达式的主要目的：判断一个字符串是否符合某种范式。

正则表达式学习：https://www.runoob.com/regexp/regexp-tutorial.htm

（2）生命周期是否合理

考评板块

考评指标

考评标准

需要信息

存储

生命周期合理

未设定周期类型的给 0分

周期类型为永久、拉链表则给10分

周期类型为日分区 :

无分区信息的给0分

没设生命周期给0分

周期长度超过建议周期天数{days}，则给（建议周期天数 / 设置周期天数）*10

元数据

（3）是否为相似表

考评板块

考评指标

考评标准

需要信息

存储

生命周期合理

未设定周期类型的给 0分

周期类型为永久则给10分

周期类型为普通 :

无分区信息的给0分

没设生命周期给0分

周期长度超过建议周期天数{days}，则给（建议周期天数 / 设置周期天数）*10

元数据

（4）表产出数据量监控

考评板块

考评指标

考评标准

需要信息

质量

表产出数据量监控

必须日分区表

前一天产出的数据量，超过前x天平均产出量{upper_limit}% ，或低于{lower_limit}% ，则给0分，其余10分

检查hdfs

（5）目录文件数据访问权限超过建议值

考评板块

考评指标

考评标准

需要信息

安全

目录文件数据访问权限超过建议值

检查该表最高权限的目录或者文件，如果超过文件超过{file_permission}或者目录超过{dir_permission}则给0分其余给10分

检查hdfs

6.7 对接dolphinscheduler的指标

在DS搭建数仓的注意事项

流程中的每个节点不能以层为单位进行配置，要以SQL为单位进行配置，即每个SQL一个节点。
全部SQL配置好后，会是一个DAG结构。
为了方便其他应用通过表定位到任务，所以每个任务定义的名称要以 “库名.表名”的结构命名。
定义节点时不要用SQL节点，要用SHELL节点用hive -e 执行sql语句。因为SQL节点是通过Jdbc提交到HiveServer2的方式，这个执行过程无法获得Yarn的application_id。而Shell节点通过Hive -e 是可以获得Yarn的application_id的。这个便于我们事后结合日志进行深度的任务分析。

dolphinscheduler 的任务的数据表

编辑

其中对于任务分析比较重要的两张表t_ds_task_definition和t_ds_task_instance表。

t_ds_task_definition保存了任务的定义比如每个节点表名和执行sql或脚本，以及设定超时的时间，重试次数等等。

t_ds_task_instance 保存了具体某次该任务被运行的情况，包括本次运行时长、运行状态。

（1）指标：当日有任务报错

考评板块	考评指标	考评标准	需要信息
计算	计算中有报错	检查DS 有报错则给0分，其余给10分	任务信息

（2）指标：表产出时效监控

考评板块

考评指标

考评标准

需要信息

质量

表产出时效监控

前一天产出时效，超过前{days}天产出时效平均值n%

则给0分，其余10分

任务信息

（3）指标：是否简单加工

考评板块	考评指标	考评标准	需要信息
计算	简单加工	sql语句没有任何join\groupby\union 非分区字段的where过滤，以上情况给0分，其余给10分	任务信息

6.8 数据治理考评结果的核算

需要根据考评结果明细，核算为三种分数：

每张表的分数
全局分数
每个技术负责人的分数

主要的计算方式就是利用sql的 group by 进行计算。

计算核算到表时，要考虑考评指标的治理类型，不同治理类型对应不同的权重。要把分数乘以权重计算该治理类型的分数。

（1）计算每张表的考评分

各表的考评结果表

create table if not exists dga.governance_assess_table
(
    id                   bigint auto_increment comment 'id'
        primary key,
    assess_date          varchar(10)    null comment '考评日期',
    table_name           varchar(200)   null comment '表名',
    schema_name          varchar(200)   null comment '库名',
    tec_owner            varchar(200)   null comment '技术负责人',
    score_spec_avg       decimal(10, 2) null comment '规范分数',
    score_storage_avg    decimal(10, 2) null comment '存储分数',
    score_calc_avg       decimal(10, 2) null comment '计算分数',
    score_quality_avg    decimal(10, 2) null comment '质量分数',
    score_security_avg   decimal(10, 2) null comment '安全分数',
    score_on_type_weight decimal(10, 2) null comment '五维权重后分数',
    problem_num          bigint         null comment '问题项个数',
    create_time          datetime       null comment '创建日期'
)
    comment '表治理考评情况';

（2）计算每个技术负责人的考评分

各个技术负责人的考评结果表

create table if not exists dga.governance_assess_tec_owner
(
    id             bigint auto_increment comment 'id'
        primary key,
    assess_date    varchar(10)    null comment '考评日期',
    tec_owner      varchar(200)   null comment '技术负责人',
    score_spec     decimal(10, 2) null comment '规范分数',
    score_storage  decimal(10, 2) null comment '存储分数',
    score_calc     decimal(10, 2) null comment '计算分数',
    score_quality  decimal(10, 2) null comment '质量分数',
    score_security decimal(10, 2) null comment '安全分数',
    score          decimal(10, 2) null comment '分数',
    table_num      bigint         null comment '涉及表',
    problem_num    bigint         null comment '问题项个数',
    create_time    datetime       null comment '创建时间'
)
    comment '技术负责人治理考评表';

（3）计算全局的考评分

各个全局的考评结构表

create table if not exists dga.governance_assess_global
(
    id             bigint auto_increment comment 'id'
        primary key,
    assess_date    varchar(10)    null comment '考评日期',
    score_spec     decimal(10, 2) null comment '规范分数',
    score_storage  decimal(10, 2) null comment '存储分数',
    score_calc     decimal(10, 2) null comment '计算分数',
    score_quality  decimal(10, 2) null comment '质量分数',
    score_security decimal(10, 2) null comment '安全分数',
    score          decimal(10, 2) null comment '分数',
    table_num      bigint         null comment '涉及表',
    problem_num    bigint         null comment '问题项个数',
    create_time    datetime       null comment '创建时间'
)
    comment '治理总考评表';

7 小结

如果觉得本文对你有帮助，想进一步学习SQL语言这门艺术的，那么不妨也可以选择去看看我的博客专栏，部分内容如下：

数字化建设通关指南

专栏原价99，现在活动价59.9，按照阶梯式增长，直到恢复原价。

专栏主要内容：
（1）SQL进阶实战技巧
可以参考如下教程，具体链接如下

SQL很简单，可你却写不好？也许这才是SQL最好的教程

上面链接中的文章及技巧会不定期更新。

（2）数仓建模实战技巧和个人心得
1）新人入职新公司后应如何快速了解业务？

2）以业务视角看宽表化建设？

3) 维度建模 or 关系型建模？

4）业务模型与数据模型有什么区别？业务阶段的模型该如何建设？

5）业务指标体系该如何建设？指标体系该如何维护？指标平台应如何建设？指标体系该由谁来搭建？

6）如何优雅设计DWS层？DWS层模型好坏该如何评价？

7）指标发生异常，该如何排查？应从哪些方面入手寻找问题点？

8）数据架构的选择，mpp or hadoop?

9）数仓团队应如何体现自己的业务价值，讲好数据故事？

10）BI与大数据有什么关系？BI与信息化、数字化之间有什么关系？BI与报表之间的关系？

11）数据部门如何与业务部门沟通，并规划指引业务需求？

文章不限于以上内容，有新的想法也会及时更新到该专栏。

大数据数据治理

文章转载自会飞的一十六，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

数仓建设：如何设计数据治理考评规则？

0 为什么要数据治理？

2 什么是数据治理？

3 如何数据治理如何落地？

4 数据考评的指标

5 考核指标列表

6 数仓团队应如何建设？

6.1 考评指标分析

6.2 健康分计算规则分析

6.3 执行步骤分析

6.4 表结构设计

6.5 一般简单指标规则分析

6.6 一般难度指标规则分析

6.7 对接dolphinscheduler的指标

6.8 数据治理考评结果的核算

7 小结

评论