教程：数据指标函数入门¶

简介¶

您可以使用 Snowsight 中的工作表或使用 CLI 客户端（如 SnowSQL）完成本教程。只需粘贴代码示例并运行它们即可。

完成本教程后，您将学会如何：

创建自定义数据指标函数 (DMF) 以衡量数据质量。
管理 DMF 以优化无服务器 Credit 使用量。
监控与调用计划 DMF 关联的无服务器 Credit 使用量。

访问控制设置¶

要完成本教程，请使用具有所有必需访问权限的单个自定义角色，其中包括以下权限：

创建数据库，随后允许创建架构、在架构中创建 DMF 以及在架构中创建表
创建仓库以执行查询操作
查询包含调用计划 DMF 结果的视图
查询包含无服务器计算机使用情况的视图

创建在整个教程中使用的 dq_tutorial_role 角色：

USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS dq_tutorial_role;

Copy

授予权限，并将应用程序角色和数据库角色授予 dq_tutorial_role：

GRANT CREATE DATABASE ON ACCOUNT TO ROLE dq_tutorial_role;
GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE dq_tutorial_role;
GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE dq_tutorial_role;
GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER TO ROLE dq_tutorial_role;
GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE dq_tutorial_role;

Copy

创建仓库以查询包含数据的表，并将角色的 USAGE 权限授予 dq_tutorial_role 角色：

CREATE WAREHOUSE IF NOT EXISTS dq_tutorial_wh;
GRANT USAGE ON WAREHOUSE dq_tutorial_wh TO ROLE dq_tutorial_role;

Copy

确认授予 dq_tutorial_role 角色的权限：

SHOW GRANTS TO ROLE dq_tutorial_role;

Copy

建立角色层次结构，并将角色授予可以完成本教程的用户（替换 jsmith 值）：

GRANT ROLE dq_tutorial_role TO ROLE SYSADMIN;
GRANT ROLE dq_tutorial_role TO USER jsmith;

Copy

数据设置¶

为了便于管理本教程的数据和 DMF，请创建一个包含这些对象的专用数据库：

创建表¶

USE ROLE dq_tutorial_role;
CREATE DATABASE IF NOT EXISTS dq_tutorial_db;
CREATE SCHEMA IF NOT EXISTS sch;

CREATE TABLE customers (
  account_number NUMBER(38,0),
  first_name VARCHAR(16777216),
  last_name VARCHAR(16777216),
  email VARCHAR(16777216),
  phone VARCHAR(16777216),
  created_at TIMESTAMP_NTZ(9),
  street VARCHAR(16777216),
  city VARCHAR(16777216),
  state VARCHAR(16777216),
  country VARCHAR(16777216),
  zip_code NUMBER(38,0)
);

Copy

在表中插入值¶

将数据添加到表中：

USE WAREHOUSE dq_tutorial_wh;

INSERT INTO customers (account_number, city, country, email, first_name, last_name, phone, state, street, zip_code)
  VALUES (1589420, 'san francisco', 'usa', 'john.doe@', 'john', 'doe', 1234567890, null, null, null);

INSERT INTO customers (account_number, city, country, email, first_name, last_name, phone, state, street, zip_code)
  VALUES (8028387, 'san francisco', 'usa', 'bart.simpson@example.com', 'bart', 'simpson', 1012023030, null, 'market st', 94102);

INSERT INTO customers (account_number, city, country, email, first_name, last_name, phone, state, street, zip_code)
  VALUES
    (1589420, 'san francisco', 'usa', 'john.doe@example.com', 'john', 'doe', 1234567890, 'ca', 'concar dr', 94402),
    (2834123, 'san mateo', 'usa', 'jane.doe@example.com', 'jane', 'doe', 3641252911, 'ca', 'concar dr', 94402),
    (4829381, 'san mateo', 'usa', 'jim.doe@example.com', 'jim', 'doe', 3641252912, 'ca', 'concar dr', 94402),
    (9821802, 'san francisco', 'usa', 'susan.smith@example.com', 'susan', 'smith', 1234567891, 'ca', 'geary st', 94121),
    (8028387, 'san francisco', 'usa', 'bart.simpson@example.com', 'bart', 'simpson', 1012023030, 'ca', 'market st', 94102);

Copy

创建和使用 DMFs¶

在以下各节中，我们将创建一个用户定义的 DMF 来衡量无效电子邮件地址的计数，随后执行以下操作：

计划 DMF 每 5 分钟运行一次。
检查 DMF 表引用（查找设置 DMF 的表）。
查询包含调用计划的 DMF 结果的内置视图。
从表中取消设置 DMF 以避免不必要的无服务器 Credit 使用量。

创建 DMF¶

创建数据指标函数 (DMF)，以返回与指定的正则表达式不匹配的列中的电子邮件地址数：

CREATE DATA METRIC FUNCTION IF NOT EXISTS
  invalid_email_count (ARG_T table(ARG_C1 STRING))
  RETURNS NUMBER AS
  'SELECT COUNT_IF(FALSE = (
    ARG_C1 REGEXP ''^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$''))
    FROM ARG_T';

Copy

为表设置计划¶

DMF 计划定义了表上所有 DMFs 的运行时间。目前，5 分钟是最短的时间间隔：

ALTER TABLE customers SET DATA_METRIC_SCHEDULE = '5 MINUTE';

Copy

备注

为了本教程目的，计划设置为 5 分钟。但是，在优化 DMF 用例后，可以尝试其他计划设置，例如与影响表的 DML 操作相关的 cron 表达式或触发事件。

为表设置 DMFs 并检查引用¶

将 DMF 与表关联：

ALTER TABLE customers ADD DATA METRIC FUNCTION
  invalid_email_count ON (email);

Copy

因为计划设置为 5 分钟，我们需要等待 5 分钟，Snowflake 才能调用 DMF 并处理结果。目前，我们可以通过调用 DATA_METRIC_FUNCTION_REFERENCES Information Schema 表函数来检查 DMF 是否与表关联：

SELECT * FROM TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
  REF_ENTITY_NAME => 'dq_tutorial_db.sch.customers',
  REF_ENTITY_DOMAIN => 'TABLE'));

Copy

查看 DMF 结果¶

调用计划 DMF 的结果存储在 DATA_QUALITY_MONITORING_RESULTS 视图中。要确定无效电子邮件地址的数量，请查询 DATA_QUALITY_MONITORING_RESULTS 视图以查看调用计划 DMF 的结果：

SELECT scheduled_time, measurement_time, table_name, metric_name, value
FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS
WHERE TRUE
AND METRIC_NAME = 'INVALID_EMAIL_COUNT'
AND METRIC_DATABASE = 'DQ_TUTORIAL_DB'
LIMIT 100;

Copy

结果表明，value 列包含 1。此数字对应于一个格式不正确的电子邮件地址，它对应于在表中插入值部分中的第一个 INSERT 语句。

从表中取消设置 DMFs¶

您已根据 DMF 定义、计划和预期结果确定 DMF 按预期工作。

为避免不必要的无服务器 Credit 使用量，请从表中取消设置 DMF：

ALTER TABLE customers DROP DATA METRIC FUNCTION
  invalid_email_count ON (email);

Copy

使用 DMF 返回失败的记录¶

在本节中，您将返回因包含空值而未能通过数据质量检查的记录。

数据质量度量函数会识别所含数据未通过质量检查的行。您可以运行数据指标扫描，以提取并返回这些记录。

要返回 DMF 识别出的行，请按照下列步骤操作：

创建表。
将存在问题的记录添加到表中。
运行数据指标扫描，以返回包含空白值的记录。
查看扫描结果。
使用新值更新记录。

创建表¶

粘贴并运行以下语句，以创建表。

CREATE or REPLACE table dq_tutorial_db.sch.employeesTable (
  id NUMBER,
  name VARCHAR,
  last_name VARCHAR,
  email VARCHAR,
  zip_code NUMBER
 );

Copy

在表中插入值¶

将包含一些存在问题的记录（例如空白值）的数据添加到表：

INSERT INTO dq_tutorial_db.sch.employeesTable (id, name, last_name, email, zip_code)
VALUES
  (8, 'John', 'Doe', 'johndoe@example.com', 12345),
  (23, '', 'Smith', 'smithj@example.com', 23456),
  (1, NULL, 'Taylor', 'taylorj@example.com', 34567),
  (99, 'Jane', 'Adams', 'jadams@example.com', 45678),
  (50, 'Alice', 'Brown', '', 56789),
  (51, NULL, 'Lee', 'lee@example.com', 67890),
  (234, 'Michael', '', 'michael@example.com', 78901),
  (56, 'Sara', 'Jones', 'sjones@example.com', 89012),
  (11, '', NULL, 'blanklast@example.com', 90123),
  (12, 'Tom', 'Harris', NULL, 10234);

Copy

运行 BLANK_COUNT 数据度量函数，返回空白值的数量¶

执行 BLANK_COUNT 数据度量函数，返回空白值的数量：

SELECT snowflake.core.blank_count (SELECT name FROM dq_tutorial_db.sch.employeesTable)

Copy

运行 SYSTEM$DATA_METRIC_SCAN 函数返回行¶

要返回 name 列中包含空白值的表格行，请在 name 列上执行 SYSTEM$DATA_METRIC_SCAN 函数。

SELECT *
  FROM TABLE(SYSTEM$DATA_METRIC_SCAN(
    REF_ENTITY_NAME  => 'dq_tutorial_db.sch.employeesTable',
    METRIC_NAME  => 'snowflake.core.blank_count',
    ARGUMENT_NAME => 'name'
   ));

Copy

查看系统指标扫描结果¶

结果显示 employeeTable 表中包含空白值的行。

+-----+-------+--------------+-----------------------+-----------+------- --+
| ID  | NAME  | LAST_NAME    | EMAIL                 | CREATEDAT | ZIP_CODE |
|-----+-------+--------------+-----------------------+----------------------|
| 23  |       |   Smith      | smith@example.com     | null      | 23456    |
| 11  |       |   null       | blanklast@example.com | null      | 90123    |
+-----+-------+--------------+-----------------------+-----------+----------+

使用新值更新记录¶

要替换 name 列，请在包含 SYSTEM$DATA_METRIC_SCAN 函数的目标表上运行查询。这会对系统函数返回的每一行运行 UPDATE 命令，从而将 name 列中的空白值设置为 NULL：

UPDATE dq_tutorial_db.sch.employeesTable
  SET name = null
  WHERE dq_tutorial_db.sch.employeesTable.ID IN (
    select ID from table(system$data_metric_scan(
  REF_ENTITY_NAME => 'dq_tutorial_db.sch.employeesTable',
  METRIC_NAME => 'snowflake.core.blank_count',
  ARGUMENT_NAME => 'name'
  )));

Copy

更新值之后，运行以下命令将返回 0：

SELECT snowflake.core.blank_count (SELECT name FROM dq_tutorial_db.sch.employeesTable)

Copy

在本节中，您提取了包含未通过质量检查的数据的记录。在下一节中，您将了解如何查看您的无服务器 Credit 使用量。

查看无服务器 Credit 使用量¶

调用计划数据指标函数 (DMFs) 需要无服务器计算资源。您可以查询 Account Usage 视图 DATA_QUALITY_MONITORING_USAGE_HISTORY，以查看 DMF 无服务器计算成本。

由于视图具有 1-2 小时的延迟，请等待该时间过去后再查询视图。您可以稍后返回此步骤。

查询视图并筛选结果以包括计划 DMF 的时间间隔：

USE ROLE dq_tutorial_role;
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.DATA_QUALITY_MONITORING_USAGE_HISTORY
WHERE TRUE
AND START_TIME >= CURRENT_TIMESTAMP - INTERVAL '3 days'
LIMIT 100;

Copy

清理、总结和其他资源¶

恭喜！您已完成本教程。

请花几分钟时间查看本教程中涵盖的总结和要点。

考虑通过删除在本教程中创建的对象进行清理。通过查看 Snowflake 文档中的其他主题了解更多信息。

总结和要点¶

概括来说，您学会了如何执行以下操作：

创建自定义 DMF 来衡量数据质量并管理 DMF，以优化无服务器 Credit 使用量。
监控与调用计划 DMF 关联的无服务器 Credit 使用量。

删除教程对象¶

如果您打算重复学习教程，可以保留创建的对象。

否则，请删除教程对象，如下所示：

USE ROLE ACCOUNTADMIN;
DROP DATABASE dq_tutorial_db;
DROP WAREHOUSE dq_tutorial_wh;
DROP ROLE dq_tutorial_role;

Copy

下一步是什么？¶

请使用以下资源继续了解 Snowflake：

从 Introduction to data quality checks 开始详细了解 DMFs。
完成 Snowflake 在 Snowflake 入门教程主题中提供的其他教程。