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

テーブルを作成する¶

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) ); 

テーブルに値を挿入する¶

テーブルにデータを追加します。

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

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

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

テーブルを作成する¶

以下のステートメントを貼り付けて実行し、テーブルを作成します。

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) 

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' ))); 

値を更新した後、以下を実行すると0が返されます。

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

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

概要と重要なポイント¶

まとめると、以下のことを学びました。

• カスタムDMFを作成してデータ品質を測定し、DMFを管理してサーバーレスクレジット使用状況を最適化します。

• スケジュールされたDMFの呼び出しに関連するサーバーレスのクレジット使用状況を監視します。

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

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

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

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

次の内容¶

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

• データ品質とデータメトリック関数の紹介 から始めて、DMFs の詳細を学習します。

• Snowflakeチュートリアル トピックで、Snowflakeが提供する他のチュートリアルを完了してください。