チュートリアル：データメトリック関数入門¶

概要¶

このチュートリアルは、 Snowsight のワークシートを使用するか、 SnowSQL などの CLI クライアントを使用して完了できます。コード例を貼り付けて実行します。

このチュートリアルが修了するまでには、次の方法を学習します。

データ品質を測定するためのカスタムデータメトリック関数（DMF）を作成します。
DMFを管理して、サーバーレスクレジット使用状況を最適化します。
スケジュールされたDMFの呼び出しに関連するサーバーレスのクレジット使用状況を監視します。

アクセス制御の設定¶

このチュートリアルを完了するには、以下のような必要なアクセス権をすべて持つ単一のカスタムロールを使用してください：

データベースを作成することで、スキーマの作成、スキーマ内の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

データを含むテーブルをクエリするウェアハウスを作成し、 dq_tutorial_role ロールにロールの USAGE 権限を付与します。

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の設定を解除します。

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分間にセットされているため、SnowflakeがDMFを呼び出して結果を処理するまで5分間待つ必要があります。とりあえず、 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 ステートメントに対応する、不適切な形式のメールアドレス1件に対応します。

テーブルからDMFsの設定を解除します。¶

DMFの定義、スケジュール、および予想される結果に基づいて、DMFが予想通りに機能していることを確認しました。

不必要なサーバーレスのクレジット使用状況を避けるために、テーブルから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 列に空白値を含むテーブル行を返すには、 SYSTEM$DATA_METRIC_SCAN 関数を name 列に対して実行します。

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

このセクションでは、品質チェックに失敗したデータを含む記録を抽出しました。次のセクションでは、サーバーレスのクレジット消費量を表示する方法を学びます。

サーバーレスクレジット消費量を表示する¶

スケジュールされたデータメトリック関数（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を管理してサーバーレスクレジット使用状況を最適化します。
スケジュールされたDMFの呼び出しに関連するサーバーレスのクレジット使用状況を監視します。

チュートリアルのオブジェクトをドロップする¶

チュートリアルを繰り返す予定がある場合は、作成したオブジェクトを保持することができます。

そうでない場合は、以下のようにチュートリアルのオブジェクトをドロップします。

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

Copy

次の内容¶

次のリソースを使用して、Snowflakeについて引き続き学習しましょう。

データ品質とデータメトリック関数の紹介から始めて、DMFs の詳細を学習します。
Snowflakeチュートリアルトピックで、Snowflakeが提供する他のチュートリアルを完了してください。