系统规则模板和自定义规则模板 - 大数据开发治理平台 DataWorks

为了减少书写Spec时的工作量，数据质量提供了数据质量规则模板能力，系统内置了一批常用的规则模板，您也可以创建自定义的规则模板，这些模板中固化了Spec所需的大部分设置。在使用模板创建规则时，只需要在模板基础之上，设置额外的配置即可。

使用系统规则模板

以使用均值1,7,30天波动率系统规则模板创建规则为例，配置如下：

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: "dt='$[yyyymmdd]' AND hh='$[hh24-1/24]'"
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - templateId: "SYSTEM:field:avg:flux:1_7_1m_bizdate"
    fields:
      - col_income
    warn: "when > 1%"
    fail: "when > 10%"

上述代码片段中，在质量监控中引用系统规则模板均值1,7,30天波动率创建了一个规则，相关设置如下：

templateId：使用的规则模板Id，更多规则模板ID，请参见系统规则模板列表，您也可以创建自定义规则模板，并引用使用自定义规则模板创建规则。
templateParameters：规则模板所需要的参数，不同的规则模板所需的参数不一样，常见需要设置fields，表示规则所监控的字段列表。
具体每个规则模板所需的参数，请参见系统规则模板列表
warn/fail：阈值。

使用自定义规则模板

如果系统规则模板无法满足需求，或在日常使用中，有大量的自定义SQL规则包含了相同的SQL，您可以创建自定义规则模板。

定义自定义模板

自定义规则模板的Spec配置示例如下：

name: "id非空数据行数校验"
id: "1760d075-00bd-43c9-be8f-3c9a4bac35db"
assertion: "id_not_null_row_count = 0"
id_not_null_row_count:
  query: "SELECT COUNT(*) FROM ${table} WHERE dt = '$[yyyymmdd-1]';"
catalog: "ods层/实时性校验"

上述代码片段中定义了一个简单的自定义规则模板，主要包含如下字段：

name：模板名称。
id：模板的唯一标识，需要确保全局唯一。引用模板时，需引用此唯一标识。
assertion：规则内容描述，语法与数据质量Spec配置中的assertion一致。
id_not_null_row_count：此示例中的assertion引用的metric定义，具体的query中，可以使用${tableName}代替表，使用该规则模板创建规则时，将会使用规则所监控的表名替换。您也可以直接引用系统的时间参数（时间参数的详细引用方法请参见数据过滤配置）。
catalog：规则模板的类目，您可以使用类目的形式来管理规则模板，多级类目之间使用/分隔。

自定义模板中的阈值设置

固化阈值/不固化阈值

以不固化阈值为例，配置示例：

name: "id非空数据行数校验"
id: "1760d075-00bd-43c9-be8f-3c9a4bac35db"
assertion: "id_not_null_row_count"
id_not_null_row_count:
  query: "SELECT COUNT(*) FROM ${table} WHERE dt = '$[yyyymmdd-1]';"

波动类阈值

name: "id非空数据行数校验"
id: "1760d075-00bd-43c9-be8f-3c9a4bac35db"
assertion: "change avg last 7 days percent for id_not_null_row_count"
id_not_null_row_count:
  query: "SELECT COUNT(*) FROM ${table} WHERE dt = '$[yyyymmdd-1]';"

使用自定义模板创建规则

示例一

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: "dt='$[yyyymmdd]' AND hh='$[hh24-1/24]'"
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - templateId: "1760d075-00bd-43c9-be8f-3c9a4bac35db"
    # 使用fail定义不通过阈值
    fail: "when != 0"

示例二

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: "dt='$[yyyymmdd]' AND hh='$[hh24-1/24]'"
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - templateId: "1760d075-00bd-43c9-be8f-3c9a4bac35db"
    warn: "when between 1 and 10)"
    fail: "when > 10"

示例二

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: "dt='$[yyyymmdd]' AND hh='$[hh24-1/24]'"
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - templateId: "1760d075-00bd-43c9-be8f-3c9a4bac35db"
    warn: "when not between -1% and 1%"
    fail: "when not between -10% and 10%"

系统规则模板列表

Id	模板	必填字段	示例
`SYSTEM:table:table_count:fixed`	表行数，固定值	`pass`	`templateId: "SYSTEM:table:table_count:fixed" pass: "when > 0"`
`SYSTEM:table:table_count:fixed:0`	表行数大于0	-	`templateId: "SYSTEM:table:table_count:fixed:0"`
`SYSTEM:table:table_count_delta:fixed:1_bizdate`	表行数，1天差值	`pass`	`templateId: "SYSTEM:table:table_count_delta:fixed:1_bizdate" pass: "when < 10"`
`SYSTEM:table:table_count_delta:fixed:latest_bizdate`	表行数，上周期差值	`pass`	`templateId: "SYSTEM:table:table_count_delta:fixed:latest_bizdate" pass: "when < 10"`
`SYSTEM:table:table_count:flux:1_7_1m_bizdate`	表行数,1,7,30天波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_count:flux:1_7_1m_bizdate" warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:table:table_count:flux:1_7_1m_1st_bizdate`	表行数,1,7,30天,本月1号,波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_count:flux:1_7_1m_1st_bizdate" warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:table:table_count:flux:1_bizdate`	表行数，1天波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_count:flux:1_bizdate" warn: "when < -1%" fail: "when < -5%"`
`SYSTEM:table:table_count:flux:1m_bizdate`	表行数，30天波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_count:flux:1m_bizdate" warn: "when < -1%" fail: "when < -5%"`
`SYSTEM:table:table_count:flux:7_bizdate`	表行数，7天波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_count:flux:7_bizdate" warn: "when < -1%" fail: "when < -5%"`
`SYSTEM:table:table_count:dynamic_threshold`	表行数，动态阈值	-	`templateId: "SYSTEM:table:table_count:dynamic_threshold"`
`SYSTEM:table:table_count:avg:7_bizdate`	表行数，7天平均值波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_count:avg:7_bizdate" warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:table:table_count:avg:1m_bizdate`	表行数，30天平均值波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_count:avg:1m_bizdate" warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:table:table_count:cycle:latest_bizdate`	表行数，上周期波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_count:cycle:latest_bizdate" warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:table:table_size:fixed`	表大小，固定值	`pass`	`templateId: "SYSTEM:table:table_size:fixed" pass: "when > 0"`
`SYSTEM:table:table_size_delta:fixed:1_bizdate`	表大小，相比1天前差值(字节)	`pass`	`templateId: "SYSTEM:table:table_size_delta:fixed:1_bizdate" pass: "when > 0"`
`SYSTEM:table:table_size_delta:fixed:latest_bizdate`	表大小，上周期差值	`pass`	`templateId: "SYSTEM:table:table_size_delta:fixed:latest_bizdate" pass: "when > 0"`
`SYSTEM:table:table_size:flux:1_bizdate`	表大小，1天波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_size:flux:1_bizdate" warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:table:table_size:flux:1m_bizdate`	表大小，30天波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_size:flux:1m_bizdate" warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:table:table_size:flux:7_bizdate`	表大小，7天波动率	`warn` `fail`	`templateId: "SYSTEM:table:table_size:flux:7_bizdate" warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:table:table_size:dynamic_threshold`	表大小，动态阈值	-	`templateId: "SYSTEM:table:table_size:dynamic_threshold"`
`SYSTEM:field:null_value:fixed`	空值个数，固定值	`fields` `pass`	`templateId: "SYSTEM:field:null_value:fixed" fields: - id pass: "when > 0"`
`SYSTEM:field:null_value:fixed:0`	空值记录数为0	`fields`	`templateId: "SYSTEM:field:null_value:fixed:0" fields: - id`
`SYSTEM:field:null_value_percent:fixed`	空值个数/总行数，固定值	`fields` `pass`	`templateId: "SYSTEM:field:null_value_percent:fixed" fields: - id pass: "when > 0"`
`SYSTEM:field:pattern_match:fixed`	正则表达式校验	`fields` `valid` `regex` `templateParameters` `metric` `pass`	`templateId: "SYSTEM:field:pattern_match:fixed" templateParameters: metric: invalid_distinct_count fields: - id valid: regex: "[0-9]+" pass: "when > 0"`
`SYSTEM:field:pattern_match_date:fixed`	日期格式校验	`fields` `valid` `format` `templateParameters` `metric` `pass`	`templateId: "SYSTEM:field:pattern_match_date:fixed" templateParameters: metric: invalid_count fields: - id valid: format: date_yyyymmdd pass: "when > 0"`
`SYSTEM:field:pattern_match_email:fixed`	电子邮箱格式校验	`fields` `templateParameters` `metric` `pass`	`templateId: "SYSTEM:field:pattern_match_email:fixed" templateParameters: metric: invalid_count fields: - id pass: "when > 0"`
`SYSTEM:field:pattern_match_idcard:fixed`	身份证格式校验	`fields` `templateParameters` `metric` `pass`	`templateId: "SYSTEM:field:pattern_match_idcard:fixed" templateParameters: metric: invalid_count fields: - id pass: "when > 0"`
`SYSTEM:field:pattern_match_mobile_number:fixed`	手机号码格式校验	`fields` `templateParameters` `metric` `pass`	`templateId: "SYSTEM:field:pattern_match_mobile_number:fixed" templateParameters: metric: invalid_count fields: - id pass: "when > 0"`
`SYSTEM:field:pattern_match_money:fixed`	货币格式校验	`fields` `valid` `format` `templateParameters` `metric` `pass`	`templateId: "SYSTEM:field:pattern_match_money:fixed" templateParameters: metric: invalid_count fields: - id valid: format: CNY pass: "when > 0"`
`SYSTEM:field:pattern_match_number:fixed`	数值格式校验	`fields` `templateParameters` `metric` `pass`	`templateId: "SYSTEM:field:pattern_match_number:fixed" templateParameters: metric: invalid_count fields: - id pass: "when > 0"`
`SYSTEM:field:pattern_match_phone_number:fixed`	电话号码格式校验	`fields` `templateParameters` `metric` `pass`	`templateId: "SYSTEM:field:pattern_match_phone_number:fixed" templateParameters: metric: invalid_count fields: - id pass: "when > 0"`
`SYSTEM:field:duplicated_count:fixed`	重复值个数，固定值	`fields` `pass`	`templateId: "SYSTEM:field:duplicated_count:fixed" fields: - id pass: "when > 0"`
`SYSTEM:field:duplicated_count:fixed:0`	字段重复值为0	`fields`	`templateId: "SYSTEM:field:duplicated_count:fixed:0" fields: - id`
`SYSTEM:fields:duplicated_count:fixed:0`	多字段重复值为0	`fields`	`templateId: "SYSTEM:fields:duplicated_count:fixed:0" fields: - id - name`
`SYSTEM:field:duplicated_percent:fixed`	重复值个数/总行数，固定值	`fields` `pass`	`templateId: "SYSTEM:field:duplicated_percent:fixed" fields: - id pass: "when > 0"`
`SYSTEM:field:count_distinct:fixed`	唯一值个数，固定值	`fields` `pass`	`templateId: "SYSTEM:field:count_distinct:fixed" fields: - id pass: "when > 0"`
`SYSTEM:field:count_distinct:flux:1_7_1m_bizdate`	唯一值个数，1、7、30天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:count_distinct:flux:1_7_1m_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:table:distinct_count:dynamic_threshold`	唯一值个数，动态阈值	`fields` `pass`	`templateId: "SYSTEM:table:distinct_count:dynamic_threshold" fields: - id`
`SYSTEM:field:count_distinct_percent:fixed`	唯一值个数/总行数，固定值	`fields` `pass`	`templateId: "SYSTEM:field:count_distinct_percent:fixed" fields: - id pass: "when > 0"`
`SYSTEM:field:min:flux:1_7_1m_bizdate`	最小值，1、7、30天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:min:flux:1_7_1m_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:min:dynamic_threshold`	最小值，动态阈值	`fields`	`templateId: "SYSTEM:field:min:dynamic_threshold" fields: - id`
`SYSTEM:field:min:cycle:1_bizdate`	最小值，1天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:min:cycle:1_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:min:cycle:latest_bizdate`	最小值，上周期波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:min:cycle:latest_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:min:flux:1_7_1m_bizdate`	最大值，1、7、30天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:min:flux:1_7_1m_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:max:dynamic_threshold`	最大值，动态阈值	`fields`	`templateId: "SYSTEM:field:max:dynamic_threshold" fields: - id`
`SYSTEM:field:max:cycle:1_bizdate`	最大值，1天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:max:cycle:1_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:max:cycle:latest_bizdate`	最大值，上周期波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:max:cycle:latest_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:avg:flux:1_7_1m_bizdate`	平均值，1、7、30天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:avg:flux:1_7_1m_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:avg:dynamic_threshold`	平均值，动态阈值	`fields`	`templateId: "SYSTEM:field:avg:dynamic_threshold" fields: - id`
`SYSTEM:field:avg:cycle:1_bizdate`	平均值，1天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:avg:cycle:1_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:sum:flux:1_7_1m_bizdate`	汇总值，1、7、30天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:sum:flux:1_7_1m_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:sum:dynamic_threshold`	汇总值，动态阈值	`fields`	`templateId: "SYSTEM:field:avg:dynamic_threshold" fields: - id`
`SYSTEM:field:sum:cycle:1_bizdate`	汇总值，1天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:sum:cycle:1_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:sum:cycle:latest_bizdate`	汇总值，上周期波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:sum:cycle:latest_bizdate" fields: - id warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:count_distinct_not_in:fixed`	枚举值不匹配唯一值个数，固定值	`fields` `valid` `values` `pass`	`templateId: "SYSTEM:field:count_distinct_not_in:fixed" fields: - gender valid: values: - male - female pass: "when = 0"`
`SYSTEM:field:count_not_in:fixed`	枚举值不匹配行数，固定值	`fields` `valid` `values` `pass`	`templateId: "SYSTEM:field:count_not_in:fixed" fields: - gender valid: values: - male - female pass: "when = 0"`
`SYSTEM:field:count_not_in:fixed:0`	枚举值不匹配行数为0	`fields` `valid` `values`	`templateId: "SYSTEM:field:count_not_in:fixed" fields: - gender valid: values: - male - female`
`SYSTEM:field:field_enum:fixed`	枚举值，自定义，固定值	`fields` `valid` `values` `templateParameters` `metirc` `pass`	`templateId: "SYSTEM:field:count_not_in:fixed" templateParameters: metric: invalid_count fields: - gender valid: values: - male - female pass: "when > 0"`
`SYSTEM:field:all_discrete_count:fixed`	离散值（状态值），固定值	`fields` `pass`	`templateId: "SYSTEM:field:all_discrete_count:fixed" fields: - gender pass: "when > 0"`
`SYSTEM:field:discrete_group_count:fixed`	离散值（分组个数），固定值	`fields` `pass`	`templateId: "SYSTEM:field:discrete_group_count:fixed" fields: - gender pass: "when > 0"`
`SYSTEM:field:discrete_group_count:dynamic_threshold`	离散值（分组个数），动态阈值	`fields`	`templateId: "SYSTEM:field:discrete_group_count:dynamic_threshold" fields: - gender`
`SYSTEM:field:discrete_value_count:dynamic_threshold`	离散值（状态值），动态阈值	`fields`	`templateId: "SYSTEM:field:discrete_value_count:dynamic_threshold" fields: - gender`
`SYSTEM:field:discrete_group_count:cycle:latest_bizdate`	离散值（分组个数），1天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:discrete_group_count:cycle:latest_bizdate" fields: - gender warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`
`SYSTEM:field:all_discrete_metrics:fluxdiscrete:1_7_1m_bizdate`	离散值（分组个数及状态值），1、7、30天波动率	`fields` `warn` `fail`	`templateId: "SYSTEM:field:all_discrete_metrics:fluxdiscrete:1_7_1m_bizdate" fields: - gender warn: "when not between -5% and 5%" fail: "when not between -10% and 10%"`

附录：自定义范围类型的系统模板配置说明

如下几个自定义范围类型的系统模板配置方法与标准系统模板配置存在差异，需要单独配置。

条件匹配占比，自定义范围

rules:
  - assertion: "matched_row_percent = 0"
    filter: "id IS NULL"

字段最小值，自定义范围

rules:
  - assertion: "anomal detection for min(income)"

字段最大值，自定义范围

rules:
  - assertion: "change avg last 7 days percent for max(income)"
    warn: "when > 0.1%"
    fail: "when > 0.5%"

字段均值，自定义范围

rules:
  - assertion: "change var last 30 days percent for avg(income)"
    warn: "when < -0.1%"
    fail: "when < -0.5%"

字段汇总值，自定义范围

rules:
  - assertion: "change 1 days ago percent for sum(income)"
    warn: "when not between -0.1% and 0.1%"
    fail: "when not between -0.5% and 0.5%"

自定义SQL

assertion的配置方法与定义自定义指标规则一致。

rules:
  - assertion: "change percent for id_not_null_count"
    id_not_null_count:
      query: "SELECT COUNT(*) AS cnt FROM tb_spec_demo WHERE dt = '$[yyyymmdd]'"
    warn: "when not between -0.1% and 0.1%"
    fail: "when not between -0.5% and 0.5%"