系統規則模板和自訂規則模板 - 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`	Regex校正	`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%"