使用数据指标函数¶

在尝试本主题中的每个示例之前，请确保您使用的角色符合必要的访问控制要求。

创建自己的 DMF¶

如果没有可以执行数据质量检查的系统数据质量指标函数 (DMF)，您可以使用 CREATE DATA METRIC FUNCTION 命令创建您自己的 DMF。

示例：带单个表实参的用户定义 DMF

创建一个会调用 COUNT 函数的 DMF，以返回表前三列中具有正数的总行数：

CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.count_positive_numbers(
  arg_t TABLE(
    arg_c1 NUMBER,
    arg_c2 NUMBER,
    arg_c3 NUMBER
  )
)
RETURNS NUMBER
AS
$$
  SELECT
    COUNT(*)
  FROM arg_t
  WHERE
    arg_c1>0
    AND arg_c2>0
    AND arg_c3>0
$$;

Copy

示例：使用多个表实参执行参照检查

用户定义的 DMF 可以有多个接受表的实参。向表中添加 DMF 时，该表将用作第一个实参。如果存在接受表的其他实参，则还必须指定第二个表的完全限定名称。此功能可简化不同数据集之间的参照完整性、匹配和比较或条件检查。

假设您要验证由主键/外键关系定义的参照完整性。在这种情况下，您可以创建一个 DMF 来验证源表中的所有记录在引用表中是否具有相应的记录。以下是用户定义的 DMF，它会返回一个表中某列的值在另一个表的相应列中没有对应值的记录数：

CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.referential_check(
  arg_t1 TABLE (arg_c1 INT), arg_t2 TABLE (arg_c2 INT))
RETURNS NUMBER AS
 'SELECT COUNT(*) FROM arg_t1
  WHERE arg_c1 NOT IN (SELECT arg_c2 FROM arg_t2)';

Copy

现在，假设要检查每个 salesorders 表中的订单（由其 sp_id 标识）是否映射回 salespeople 表中的 sp_id。您可以将 DMF 添加到 salesorders 表，同时将 salespeople 表指定为另一个表的实参。

ALTER TABLE salesorders
  ADD DATA METRIC FUNCTION governance.dmfs.referential_check
    ON (sp_id, TABLE (my_db.sch1.salespeople(sp_id)));

Copy

输出将返回 salesorders 表中的行数，其中 sp_id 列有未显示在 salespeople 表的 sp_id 列中的值。值大于 0 表示 salesorders 中存在未映射到 salespeople 中记录的 sp_id 值。

手动调用 DMF¶

使用以下语法调用 DMF:

SELECT <data_metric_function>(<query>)

Copy

其中：

data_metric_function

指定系统定义或用户定义的 DMF。

query

指定对表或视图进行 SQL 查询。

查询中的列必须与 DMF 签名中的列实参匹配。

备注

以下系统 DMFs 不遵循此语法，因为它们不采用任何实参：

直接调用 DMF 并查看结果：

SELECT governance.dmfs.count_positive_numbers(
    SELECT c1,
           c2,
           c3
    FROM t
  );

Copy

当您在表或视图上设置 DMF 并计划其运行时，可以查询根据事件表生成的视图以查看结果。有关信息，请参阅查看 DMF 结果。

在表或视图上添加或删除 DMF¶

使用 ALTER TABLE 或 ALTER VIEW 命令执行以下操作：

使用 ALTER TABLE 在表中添加 DMF。

ALTER TABLE t
  ADD DATA METRIC FUNCTION governance.dmfs.count_positive_numbers
    ON (c1, c2, c3);

Copy

使用 ALTER TABLE 命令从表中删除 DMF。

ALTER TABLE t
  DROP DATA METRIC FUNCTION governance.dmfs.count_positive_numbers
    ON (c1, c2, c3);

Copy

备注

使用 ALTER VIEW 命令在物化视图列上添加 DMF，或从物化视图列中删除 DMF。

计划 DMFs 的运行¶

您可以计划 DMFs 的运行，以自动衡量表的数据质量。

Snowflake 使用表所有者角色（即对表具有 OWNERSHIP 权限的角色）调用计划的 DMF。表所有者角色必须具有以下授权：

对用户定义的 DMF 具有 USAGE 权限，以及对用于存储用户定义的 DMF 的数据库和架构具有 USAGE 权限。
DATA_METRIC_USER 数据库角色（如果 DMF 是系统 DMF）。
对账户具有 EXECUTE DATA METRIC FUNCTION 权限。

通过表、视图或物化视图的 DATA_METRIC_SCHEDULE 对象参数，可以计划 DMFs 何时运行。表或视图上的所有数据指标函数都遵循相同的计划。

可以通过三种方法计划 DMF 的运行：

将 DMF 设置为在指定的分钟数后运行。
使用 cron 表达式计划 DMF，使其按特定的频率运行。
使用触发事件计划 DMF 在表发生 :doc:`DML 更改 </sql-reference/sql-dml>`（例如，在表中插入新行）时运行。但是：
- 表的重聚类不会触发 DMF 运行。
- 触发方法只适用于某些类型的表。有关更多信息，请参阅 :ref:`ALTER TABLE ...<label-alter_table_data_metric_function_action> ` 。

例如：

将数据指标函数计划设置为每 5 分钟运行一次：

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = '5 MINUTE';

Copy

将数据指标函数计划设置为每天 8:00 AM 运行：

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = 'USING CRON 0 8 * * * UTC';

Copy

将数据指标函数计划设置为仅在工作日的 8:00 AM 运行：

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = 'USING CRON 0 8 * * MON,TUE,WED,THU,FRI UTC';

Copy

将数据指标函数计划设置为每天在 0600、1200 和 1800 UTC 时间运行三次：

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = 'USING CRON 0 6,12,18 * * * UTC';

Copy

将数据指标函数设置为在常规 DML 操作（例如插入新行）修改表时运行：

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = 'TRIGGER_ON_CHANGES';

Copy

您可以使用 SHOW PARAMETERS 命令查看支持的表对象的 DMF 计划：

SHOW PARAMETERS LIKE 'DATA_METRIC_SCHEDULE' IN TABLE hr.tables.empl_info;

Copy

+----------------------+--------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+
| key                  | value                          | default | level | description                                                                                                                  | type   |
+----------------------+--------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+
| DATA_METRIC_SCHEDULE | USING CRON 0 6,12,18 * * * UTC |         | TABLE | Specify the schedule that data metric functions associated to the table must be executed in order to be used for evaluation. | STRING |
+----------------------+--------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+

对于视图和物化视图对象，将 TABLE 指定为对象域并按如下方式检查计划：

SHOW PARAMETERS LIKE 'DATA_METRIC_SCHEDULE' IN TABLE mydb.public.my_view;

Copy

备注

从修改表的 DMF 开始，会有 10 分钟的滞后，以便任何计划变更对分配给表的先前 DMFs 生效。但是，新分配到表的 DMF 不受 10 分钟延迟的限制。请仔细规划 DMF 计划和 DMF 取消设置操作，以符合您的预期 DMF 成本。

此外，当您评估 DMF 结果（如查询 DATA_QUALITY_MONITORING_RESULTS 视图）时，请在查询中指定 measurement_time 列作为评估的基础。DMF 评估由一个内部流程启动，在计划时间和测量时间之间可能会出现表更新（如 INSERT 操作）。使用 measurement_time 列时，可以更准确地评估 DMF 结果，因为测量时间可以指示 DMF 的评估时间。

查看 DMF 结果¶

要查看 DMF 结果，首先需要管理对结果的访问权限，然后选择查看结果的方式：

管理对 DMF 结果的访问权限
选项

管理对 DMF 结果的访问权限¶

您可以通过三个选项来访问计划的 DMF 的结果，详见下一节：

选项 1：查询专用事件表。
选项 2：查询 DATA_QUALITY_MONITORING_RESULTS 视图，它是事件表的扁平化版本。
选项 3：调用 DATA_QUALITY_MONITORING_RESULTS 表函数。

根据您想要管理 DMF 结果访问权限的方式，可以将以下系统应用程序角色授予其他账户级角色：

应用程序角色	备注
DATA_QUALITY_MONITORING_ADMIN	可使用选项 1、2 和 3。被授予此应用程序角色的角色可将 DATA_QUALITY_MONITORING_VIEWER 和 DATA_QUALITY_MONITORING_LOOKUP 应用程序角色授予其他角色。被授予 ACCOUNTADMIN 角色的用户可将 DATA_QUALITY_MONITORING_ADMIN 应用程序角色授予其他角色。
DATA_QUALITY_MONITORING_VIEWER	选项 2 和 3。
DATA_QUALITY_MONITORING_LOOKUP	仅选项 3。

应用程序角色

备注

DATA_QUALITY_MONITORING_ADMIN

可使用选项 1、2 和 3。

被授予此应用程序角色的角色可将 DATA_QUALITY_MONITORING_VIEWER 和 DATA_QUALITY_MONITORING_LOOKUP 应用程序角色授予其他角色。

被授予 ACCOUNTADMIN 角色的用户可将 DATA_QUALITY_MONITORING_ADMIN 应用程序角色授予其他角色。

DATA_QUALITY_MONITORING_VIEWER

选项 2 和 3。

DATA_QUALITY_MONITORING_LOOKUP

仅选项 3。

例如，使用 GRANT APPLICATION ROLE 命令将 DATA_QUALITY_MONITORING_VIEWER 系统应用程序角色授予 analyst 自定义角色：

USE ROLE ACCOUNTADMIN;
GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE analyst;

Copy

使用 REVOKE APPLICATION ROLE 命令撤销账户角色中的系统应用程序角色。

小技巧

此外，如果要管理对系统 DMFs 结果的访问权限，请将 SNOWFLAKE.DATA_METRIC_USER 数据库角色授予可以访问该结果的账户角色。有关更多信息，请参阅 GRANT DATABASE ROLE。

选项¶

要查看计划的 DMF 结果，请选择以下选项之一：

选项 1：查询专用事件表
选项 2：查询 DATA_QUALITY_MONITORING_RESULTS 视图
选项 3：调用 DATA_QUALITY_MONITORING_RESULTS 表函数

选项 1：查询专用事件表

通过此选项，可以访问原始数据，并且可以更自由地使用派生对象对数据进行后处理，例如根据分析结果所需的方式创建视图、表函数或存储过程。此外，如果创建这些派生对象，可以有选择地向不同的角色授予对这些对象的访问权限。例如，数据工程师可以访问存储过程以维护获取结果的方法，而数据分析师可以访问视图来分析结果。

事件表命名为 SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS_RAW。

有关事件表列的信息，请参阅事件表列。

有关查询事件表的代表性示例，请参阅日志记录和跟踪教程。

选项 2：查询 DATA_QUALITY_MONITORING_RESULTS 视图

通过此选项，可以查询 DATA_QUALITY_MONITORING_RESULTS 视图，该视图会扁平化事件表中的原始数据，以便更轻松地访问 DMF 结果。此外，不需要数据后处理以及不需要授予对原始数据的访问权限时，最好选择此选项。

该视图存在于以下共享 SNOWFLAKE 数据库的 LOCAL 架构中：SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS。

有关信息，请参阅 DATA_QUALITY_MONITORING_RESULTS 视图。

备注

SNOWFLAKE.GOVERNANCE_VIEWER 数据库角色无权查询 DATA_QUALITY_MONITORING_RESULTS 视图。

选项 3：调用 DATA_QUALITY_MONITORING_RESULTS 表函数

使用该选项可以调用 DATA_QUALITY_MONITORING_RESULTS 表函数以查看 DMF 结果。该函数可返回与 DATA_QUALITY_MONITORING_RESULTS 视图相同的列。但是，调用该函数时只能指定一个表。当您想将数据指标函数的结果限制在单个表中，而不提供对其他表或事件表的测量结果的访问权限时，此选项是最佳选择。

除了 SNOWFLAKE.DATA_QUALITY_MONITORING_LOOKUP 应用程序角色外，用于调用 DATA_QUALITY_MONITORING_RESULTS 表函数的角色还需要以下权限：

对表的 OWNERSHIP 或 SELECT 权限。
对 DMF 的 OWNERSHIP 或 USAGE 权限。

查看 DMF 的属性¶

描述 DMF 以查看其属性：

DESC FUNCTION governance.dmfs.count_positive_numbers(
  TABLE(
    NUMBER, NUMBER, NUMBER
  )
);

Copy

+-----------+---------------------------------------------------------------------+
| property  | value                                                               |
+-----------+---------------------------------------------------------------------+
| signature | (ARG_T TABLE(ARG_C1 NUMBER, ARG_C2 NUMBER, ARG_C3 NUMBER))          |
| returns   | NUMBER(38,0)                                                        |
| language  | SQL                                                                 |
| body      | SELECT COUNT(*) FROM arg_t WHERE arg_c1>0 AND arg_c2>0 AND arg_c3>0 |
+-----------+---------------------------------------------------------------------+

确保 DMF 的安全¶

您可以使用 ALTER FUNCTION 命令确保 DMF 安全。有关其对函数安全的意义的更多信息，请参阅使用安全 UDFs 和存储过程保护敏感信息。

ALTER FUNCTION governance.dmfs.count_positive_numbers(
 TABLE(
   NUMBER,
   NUMBER,
   NUMBER
))
SET SECURE;

Copy

列出 DMFs¶

使用 SHOW DATA METRIC FUNCTIONS 或 SHOW FUNCTIONS 命令列出账户、数据库或架构中的 DMFs：

SHOW DATA METRIC FUNCTIONS IN ACCOUNT;

Copy

或者，还可以查询 Information Schema FUNCTIONS 视图或 Account Usage FUNCTIONS 视图，以列出指定数据库或您账户中的 DMFs。

is_data_metric 列指定函数是否为 DMF。

在 DMF 上设置标签¶

使用 ALTER FUNCTION 命令在 DMF 上设置标签：

ALTER FUNCTION governance.dmfs.count_positive_numbers(
  TABLE(NUMBER, NUMBER, NUMBER))
  SET TAG governance.tags.quality = 'counts';

Copy

跟踪 DMF 引用¶

您可以查询 DATA_METRIC_FUNCTION_REFERENCES Account Usage 视图，以查看账户中的 DMF 引用。也可以调用 DATA_METRIC_FUNCTION_REFERENCES Information Schema 表函数，以识别与给定表或视图关联的 DMFs 或者与给定 DMF 关联的表或视图。

例如：

为每个在该表或视图上设置了名为 count_positive_numbers 的 DMF 的对象（表或视图）返回一行：

USE DATABASE governance;
USE SCHEMA INFORMATION_SCHEMA;
SELECT *
  FROM TABLE(
    INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
      METRIC_NAME => 'governance.dmfs.count_positive_numbers'
    )
  );

Copy

为分配给名为 hr.tables.empl_info 的表的每个 DMF 返回一行：

USE DATABASE governance;
USE SCHEMA INFORMATION_SCHEMA;
SELECT *
  FROM TABLE(
    INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
      REF_ENTITY_NAME => 'hr.tables.empl_info',
      REF_ENTITY_DOMAIN => 'table'
    )
  );

Copy

从系统中删除 DMF¶

从系统中删除 DMF：

DROP FUNCTION governance.dmfs.count_positive_numbers(
  TABLE(
    NUMBER, NUMBER, NUMBER
  )
);

Copy

限制¶

有关信息，请参阅数据质量限制部分。