📚
Snowflake Horizonで始めるデータガバナンス実践編
はじめに
Snowflake Horizonで始めるデータガバナンスの第一歩でデータ分析においてデータガバナンスの重要性をご紹介しました。
今回は実際にデータガバナンスに取り組むためのSnowflake Horizonの機能の紹介となります。
とは言え、Snowflake Horizonの機能は多岐に渡るため、今回は次の項目に絞ります。
- データ品質監視
- データ保護
- データアクセスと監査
これらの項目は初めてデータガバナンスに取り組む場合でも比較的簡単に導入が可能です。
最大の理由としては、以下QuickSartで紹介されている機能だからです。
今回は機能紹介なので、セットアップの手順は含まれていません。
機能の導入方法について知りたい方は是非QuickStartをお試しください。
データ品質監視
データの品質を監視することはデータガバナンスにおけるデータの信頼性向上に繋がります。
Garbage In, Garbage Outの言葉のとおり、ゴミのデータからはゴミのデータしか生まれません。
ゴミのデータを基にデータドリブンを実践すると失敗することは火をみるより明らかです。
データメトリック関数
Snowflakeではデータ品質を監視する機能として,データメトリック関数があります。 メリット：
-
コンプライアンスを促進する：
- データの状態を把握することで、コンプライアンスや規制基準を遵守していることを示しやすくなります。
-
サービスレベル契約（SLAs）の遵守:
- FRESHNESSなどのデータメトリックにより、データの最新性を担保します。
-
信頼性:
- 信頼できるデータ主導の意思決定を促進するデータ検証を提供します。
-
一貫性:
- システムDMFsを使用し、適切なカスタムDMFsを繰り返し使用することで、長期にわたって一貫したデータ品質評価が実現します。これにより、データの信頼性が向上します。
-
特定のユースケースのための最適化:
- カスタムDMFsを使用することにより、データエンジニアはデータを測定するための正確なメトリックを設計することができ、データのターゲットアプリケーションのためのより正確な最適化につながります。
-
自動測定:
- DMF をテーブルまたはビューに割り当て、 DMF の実行スケジュールを指定すると、 DMF の呼び出しが自動化されます。データ品質を積極的に測定するための追加作業は必要ありません。
システムデータメトリック関数（システムDMFs）
データメトリック関数の中に、システムデータメトリック関数（以降システムDMFs）があり、これは
共有のSNOWFLAKEデータベースのCOREスキーマでシステムDMFsを提供します。
Snowflakeにより提供されるシステムDMFsは次のとおりです。
| カテゴリ | システム DMFs | メモ |
|---|---|---|
| 精度 | - BLANK_COUNT - BLANK_PERCENT - BLANK_PERCENT - NULL_COUNT - NULL_PERCENT |
|
| 鮮度 | - FRESHNESS - DATA_METRIC_SCHEDULE_TIME |
- 列データの鮮度を判定します。 - カスタム鮮度メトリックを定義します。 |
| 統計 | - AVG - MAX - MIN - STDDEV |
|
| 独自性 | - DUPLICATE_COUNT - UNIQUE_COUNT |
- NULL値を含む、列内の重複値の数を決定します。 - 列内のNULL以外の値の数を決定します。 |
| ボリューム | - ROW_COUNT |
QuickStartで次のシステムDMFsの動作について紹介されていました。
- NULL_COUNT
- UNIQUE_COUNT
- DUPLICATE_COUNT
- ROW_COUNT
NULL_COUNT
NULL_COUNTは対象のテーブルカラムにあるNULLの数をカウントしてくれます。
NULL_COUNTを利用することで、NULLを許容しないカラムにNULLが含まれたことを確認できます。
$ SELECT SNOWFLAKE.CORE.NULL_COUNT(SELECT EMAIL FROM HRZN_DB.HRZN_SCH.CUSTOMER) AS NULL_COUNT;
+------------+
| NULL_COUNT |
|------------|
| 10 |
+------------+
UNIQUE_COUNT
UNIQUE_COUNTはテーブルにある指定された列の非NULLの一意な値の合計数を確認できます。
UNIQUE_COUNTを利用することで、PKやFKなどの一意制約を持つカラムに対して、制約違反をしていないか確認することができます。
$ SELECT SNOWFLAKE.CORE.UNIQUE_COUNT(SELECT EMAIL FROM HRZN_DB.HRZN_SCH.CUSTOMER) AS UNIQUE_COUNT;
+--------------+
| UNIQUE_COUNT |
|--------------|
| 988 |
+--------------+
DUPLICATE_COUNT
DUPLICATE_COUNTはNULLの値を含む、重複する列値の数を確認できます。
$ SELECT SNOWFLAKE.CORE.DUPLICATE_COUNT (SELECT EMAIL FROM HRZN_DB.HRZN_SCH.CUSTOMER) AS duplicate_count;
+-----------------+
| DUPLICATE_COUNT |
|-----------------|
| 3 |
+-----------------+
データ品質監視をスケジュール監視する方法
-- TRIGGER_ON_CHANGESは新しい行の挿入など、一般的な DML 操作でテーブルが変更されたときに実行されます
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER SET DATA_METRIC_SCHEDULE = 'TRIGGER_ON_CHANGES';
-- システムDMFsの設定
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT on (EMAIL);
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.UNIQUE_COUNT on (EMAIL);
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT on (EMAIL);;
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.ROW_COUNT on ();
次のクエリからスケジュール設定内容を確認できます。
$ SHOW PARAMETERS LIKE 'DATA_METRIC_SCHEDULE' IN TABLE HRZN_DB.HRZN_SCH.CUSTOMER;
+----------------------+--------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+
| key | value | default | level | description | type |
|----------------------+--------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------|
| DATA_METRIC_SCHEDULE | TRIGGER_ON_CHANGES | | TABLE | Specify the schedule that data metric functions associated to the table must be executed in order to be used for evaluation. | STRING |
+----------------------+--------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+
カスタムデータメトリック関数（カスタムDMFs）
システムDMFsだけでは物足りない場合は、独自のDMFsを作成することができます。
QickStartでは正規表現（RegEx）を使用して無効なメールアドレスをカウントするカスタムDMFsの紹介がありました。
$ CREATE DATA METRIC FUNCTION HRZN_DB.HRZN_SCH.INVALID_EMAIL_COUNT(IN_TABLE TABLE(IN_COL STRING))
RETURNS NUMBER
AS
'SELECT COUNT_IF(FALSE = (IN_COL regexp ''^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'')) FROM IN_TABLE';
実際にカスタムDMFsを実行し、無効なメールアドレスがいくつ存在するか確認してみます。
$ SELECT HRZN_DB.HRZN_SCH.INVALID_EMAIL_COUNT(SELECT EMAIL FROM HRZN_DB.HRZN_SCH.CUSTOMER) AS INVALID_EMAIL_COUNT;
+---------------------+
| INVALID_EMAIL_COUNT |
|---------------------|
| 37 |
+---------------------+
スケジュールの動作確認
適当なデータを追加してみます。
INSERT INTO HRZN_DB.HRZN_SCH.CUSTOMER ( ID, FIRST_NAME, LAST_NAME, STREET_ADDRESS, STATE, CITY, ZIP, PHONE_NUMBER, EMAIL, SSN, BIRTHDATE, JOB, CREDITCARD, COMPANY, OPTIN ) VALUES
(9999, 'kyami', 'sama', '373 kyamisama Grace', 'MA', 'Springfield', '00000', '000-000-0000', 'kyami@example.com', '000-00-0000', '0000-00-00', 'Data Enginner', '5555555555555555', 'HogeHoge', 'Y');
スケジュールされた結果はSNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTSに書き込まれるので確認することができます。
$ SELECT change_commit_time, measurement_time, table_schema, table_name, metric_name, value
FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS
WHERE table_database = 'HRZN_DB' and change_commit_time is not NULL
ORDER BY change_commit_time DESC LIMIT 5;
+-------------------------------+-------------------------------+--------------+------------+---------------------+-------+
| CHANGE_COMMIT_TIME | MEASUREMENT_TIME | TABLE_SCHEMA | TABLE_NAME | METRIC_NAME | VALUE |
|-------------------------------+-------------------------------+--------------+------------+---------------------+-------|
| 2025-03-02 11:02:32.110 -0700 | 2025-03-02 11:10:05.723 -0700 | HRZN_SCH | CUSTOMER | INVALID_EMAIL_COUNT | 37 |
| 2025-03-02 11:02:32.110 -0700 | 2025-03-02 11:10:34.192 -0700 | HRZN_SCH | CUSTOMER | NULL_COUNT | 10 |
| 2025-03-02 11:02:32.110 -0700 | 2025-03-02 11:10:21.145 -0700 | HRZN_SCH | CUSTOMER | UNIQUE_COUNT | 989 |
| 2025-03-02 11:02:32.110 -0700 | 2025-03-02 11:10:05.991 -0700 | HRZN_SCH | CUSTOMER | DUPLICATE_COUNT | 3 |
| 2025-03-02 11:02:32.110 -0700 | 2025-03-02 11:10:20.812 -0700 | HRZN_SCH | CUSTOMER | ROW_COUNT | 1001 |
+-------------------------------+-------------------------------+--------------+------------+---------------------+-------+
データメトリクス関数単体での動作は以上となります。
もしデータ品質監視を本番環境へ導入を考える場合は、DMFsとアラート機能を組み合わせて、データの品質に異常が発生した場合、それに気づける仕組み作りもご検討ください。
データ保護
データ保護の必要性や重要性については、今更説明するまでもないと思うので割愛します。
QuickStartではデータ保護における次の機能が紹介されていました。
- オブジェクトへのタグ付け
- 動的タグ付け
- 手動タグ付け
- ダイナミックマスキングポリシー（列レベルマスキングポリシー）
- 行レベルマスキングポリシー
- 集約ポリシー
- 投影ポリシー
マスキングポリシーの適用にはタグを利用することが一般的です。
そのため、マスキングポリシーのための事前準備として、オブジェクトへのタグ付け機能も紹介しています。
集約/投影ポリシーはデータの秘匿性を向上させる効果のあるポリシーです。
これらの機能を順にご紹介します。
オブジェクトへのタグ付け
オブジェクトへタグ付けすることでマスキングポリシーの柔軟な制御を実現するメリットがあります。
他にも、タグをつけておく事で第３者からこのスキーマ/テーブルにはどのようなデータが存在しているのかが一目でわかるというメリットがあります。
動的タグ付け
オブジェクトにタグ付けする際に、特定のタグであれば自動でタグ付けを行なってくれる便利な関数があります。
それがSYSTEM$CLASSIFYです。
特定のタグと言うのが、次のとおりです。
- システムタグと呼ばれており、次の2つのカテゴリに分類されます。
システムカテゴリ 説明 セマンティックカテゴリ セマンティックカテゴリは、個人の属性を識別します。
分類がサポートする個人属性の非網羅的なリストには、名前、年齢、性別が含まれます。
これら3つの属性は、 SEMANTIC_CATEGORY タグを列に割り当てるときに使用可能な文字列値です。
分類により、オーストラリア、カナダ、英国など、さまざまな国から情報を検出することができます。
たとえば、テーブルの列に電話番号の情報が含まれている場合、分析プロセスでは、それぞれの国の異なる電話番号の値を区別することができます。プライバシーカテゴリ 分析により、列データがセマンティックカテゴリに対応すると判断された場合、Snowflakeはさらに列をプライバシーカテゴリに分類します。
プライバシーカテゴリには、識別子、準識別子、機密の3つの値があります。
これら3つの値は、 PRIVACY_CATEGORY 分類システムタグを列に割り当てるときに指定できる文字列値です。
識別子:
これらの属性は、個人を一意に識別します。属性の例には、名前、社会保障番号、電話番号などがあります。識別子属性は 直接識別子 と同義です。
準識別子:
これらの属性は、2つ以上の属性が組み合わされた場合に、個人を一意に識別することができます。属性の例には、年齢と性別が含まれます。 準識別子は 間接識別子 と同義です。
機密:
これらの属性は、個人を識別するのに十分とは見なされませんが、個人がプライバシー上の理由で開示を望まない情報です。現在、Snowflakeが機密と評価する唯一の属性は給与です。
非機密:
これらの属性には個人情報や機密情報は含まれません。
CALL SYSTEM$CLASSIFY('HRZN_DB.HRZN_SCH.CUSTOMER', {'auto_tag': true});
SYSTEM$CLASSIFY_SCHEMAを利用することで、スキーマ単位での自動分類もサポートしています。
CALL SYSTEM$CLASSIFY_SCHEMA('HRZN_DB.HRZN_SCH', {'auto_tag': true});
SYSTEM$CLASSIFYで自動タグ付けされた結果をTAG_REFERENCES_ALL_COLUMNSで確認することができます。
$ SELECT TAG_DATABASE, TAG_SCHEMA, OBJECT_NAME, COLUMN_NAME, TAG_NAME, TAG_VALUE
FROM TABLE(
HRZN_DB.INFORMATION_SCHEMA.TAG_REFERENCES_ALL_COLUMNS(
'HRZN_DB.HRZN_SCH.CUSTOMER',
'table'
));
+--------------+------------+-------------+----------------+-------------------+-----------------------+
| TAG_DATABASE | TAG_SCHEMA | OBJECT_NAME | COLUMN_NAME | TAG_NAME | TAG_VALUE |
|--------------+------------+-------------+----------------+-------------------+-----------------------|
| SNOWFLAKE | CORE | CUSTOMER | FIRST_NAME | PRIVACY_CATEGORY | IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | LAST_NAME | PRIVACY_CATEGORY | IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | STREET_ADDRESS | PRIVACY_CATEGORY | IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | PHONE_NUMBER | PRIVACY_CATEGORY | IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | EMAIL | PRIVACY_CATEGORY | IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | SSN | PRIVACY_CATEGORY | IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | CREDITCARD | PRIVACY_CATEGORY | IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | STATE | PRIVACY_CATEGORY | QUASI_IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | CITY | PRIVACY_CATEGORY | QUASI_IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | ZIP | PRIVACY_CATEGORY | QUASI_IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | BIRTHDATE | PRIVACY_CATEGORY | QUASI_IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | JOB | PRIVACY_CATEGORY | QUASI_IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | STATE | SEMANTIC_CATEGORY | ADMINISTRATIVE_AREA_1 |
| SNOWFLAKE | CORE | CUSTOMER | CITY | SEMANTIC_CATEGORY | CITY |
| SNOWFLAKE | CORE | CUSTOMER | BIRTHDATE | SEMANTIC_CATEGORY | DATE_OF_BIRTH |
| SNOWFLAKE | CORE | CUSTOMER | EMAIL | SEMANTIC_CATEGORY | EMAIL |
| SNOWFLAKE | CORE | CUSTOMER | FIRST_NAME | SEMANTIC_CATEGORY | NAME |
| SNOWFLAKE | CORE | CUSTOMER | LAST_NAME | SEMANTIC_CATEGORY | NAME |
| SNOWFLAKE | CORE | CUSTOMER | SSN | SEMANTIC_CATEGORY | NATIONAL_IDENTIFIER |
| SNOWFLAKE | CORE | CUSTOMER | JOB | SEMANTIC_CATEGORY | OCCUPATION |
| SNOWFLAKE | CORE | CUSTOMER | CREDITCARD | SEMANTIC_CATEGORY | PAYMENT_CARD |
| SNOWFLAKE | CORE | CUSTOMER | PHONE_NUMBER | SEMANTIC_CATEGORY | PHONE_NUMBER |
| SNOWFLAKE | CORE | CUSTOMER | ZIP | SEMANTIC_CATEGORY | POSTAL_CODE |
| SNOWFLAKE | CORE | CUSTOMER | STREET_ADDRESS | SEMANTIC_CATEGORY | STREET_ADDRESS |
+--------------+------------+-------------+----------------+-------------------+-----------------------+
カスタム分類
Snowflakeが提供する分類関数以外にも自作でカスタム分類関数を作成することができます。
custom_classifierを利用することでカスタム分類を作成できます。
QuickStartではクレジットカードを部類するカスタム分類の作成例が紹介されていました。
create or replace snowflake.data_privacy.custom_classifier CREDITCARD();
CREDITCARDというカスタム分類を作成できたので、次に分類するコア部分を実装します。
QuickStartでは、MC_PAYMENT_CARD（マスターカード）とAMX_PAYMENT_CARD（アメリカン・エキスプレス）を分類する正規表現を定義したものを作成していました。
Call creditcard!add_regex('MC_PAYMENT_CARD','IDENTIFIER','^(?:5[1-5][0-9]{2}|222[1-9]|22[3-9][0-9]|2[3-6][0-9]{2}|27[01][0-9]|2720)[0-9]{12}$');
Call creditcard!add_regex('AMX_PAYMENT_CARD','IDENTIFIER','^3[4-7][0-9]{13}$');
実際にどのように動作するのか確認してみます。
AMX_PAYMENT_CARDを分類する正規表現のテストです。
$ select CREDITCARD from HRZN_DB.HRZN_SCH.CUSTOMER where CREDITCARD regexp '^3[4-7][0-9]{13}$' LIMIT 5;
+-----------------+
| CREDITCARD |
|-----------------|
| 340242507269295 |
| 370783345728011 |
| 340908984393333 |
| 355965595424305 |
| 352715993554507 |
+-----------------+
最後に作成したカスタム分類の適用と、実際にどのようなタグが付けられたのか確認してみます。
$ CALL SYSTEM$CLASSIFY('HRZN_DB.HRZN_SCH.CUSTOMER',{'auto_tag': true, 'custom_classifiers': ['HRZN_DB.CLASSIFIERS.CREDITCARD']});
$ Select SYSTEM$GET_TAG('snowflake.core.semantic_category','HRZN_DB.HRZN_SCH.CUSTOMER.CREDITCARD','column');
+----------------------------------------------------------------------------------------------------+
| SYSTEM$GET_TAG('SNOWFLAKE.CORE.SEMANTIC_CATEGORY','HRZN_DB.HRZN_SCH.CUSTOMER.CREDITCARD','COLUMN') |
|----------------------------------------------------------------------------------------------------|
| PAYMENT_CARD |
+----------------------------------------------------------------------------------------------------+
CALL SYSTEM$CLASSIFYで適用し、Select SYSTEM$GET_TAGで実際にタグ付けされたタグを確認できます。
想定通りPAYMENT_CARDと言うタグがついています。
手動タグ付け
自動タグ付けやカスタムタグ付けでカバーできないタグ付けについては、手動によるタグ付けを行います。
この後、マスキングポリシーの紹介を行いますが、簡単にポリシーがどのように動作するか説明すると、マスキングポリシーシグネチャのデータ型と列のデータ型が一致する場合、タグ付けされた列はマスキングポリシーの条件によって自動的に保護されます。
そのため、手動によるタグ付けは、タグと対となる値の作成と適用を行います。
例として機密データタグを作成し、コメントを追加してみます。
create tag HRZN_DB.TAG_SCHEMA.confidential allowed_values 'Sensitive','Restricted','Highly Confidential';
alter tag HRZN_DB.TAG_SCHEMA.confidential set comment = 'Confidential information';
create tag HRZN_DB.TAG_SCHEMA.pii_type allowed_values 'Email','Phone Number','Last Name';
alter tag HRZN_DB.TAG_SCHEMA.pii_type set comment = 'PII Columns';
上記クエリの説明を入れると、confidentialと言うタグを作成し、値として'Sensitive','Restricted','Highly Confidential'を付与しています。
ちなみに、allowed_valuesによる付与を行うことで、意図しない値の付与を防ぐことができます。
実際に検証してみたところ、このようにエラーとなります。
$ alter table customer set tag HRZN_DB.TAG_SCHEMA.confidential = 'Hoge Value';
391826 (23001): Value 'Hoge Value' is not allowed by the specified allowed_values for tag 'CONFIDENTIAL'.
次に、テーブルと列レベルでタグを適用する
テーブルにタグを設定する意図としては、テーブル全体の意味・分類を明示することができ、テーブル単位のコスト配賦や権限管理と連動することでコストやアクセス権限の設計がしやすくなります。
テーブルへのタグ適用
alter table HRZN_DB.HRZN_SCH.customer set tag HRZN_DB.TAG_SCHEMA.confidential ='Sensitive';
alter table HRZN_DB.HRZN_SCH.customer set tag HRZN_DB.TAG_SCHEMA.cost_center ='Sales';
カラムへのタグ適用
alter table HRZN_DB.HRZN_SCH.customer modify email set tag HRZN_DB.TAG_SCHEMA.pii_type ='Email';
alter table HRZN_DB.HRZN_SCH.customer modify phone_number set tag HRZN_DB.TAG_SCHEMA.pii_type ='Phone Number';
alter table HRZN_DB.HRZN_SCH.customer modify last_name set tag HRZN_DB.TAG_SCHEMA.pii_type ='Last Name';
ダイナミックマスキングポリシー（列レベルマスキングポリシー）
タグを利用したダイナミックマスキングポリシー（列レベルマスキングポリシー）の紹介です。
ダイナミックとは特定の条件によると言う意味合いがあります。
つまりタグを利用したダイナミックマスキングポリシーは、特定のタグを持つテーブルもしくはカラムに対して何かしらのマスキングを行うこととなります。
例として、ACCOUNTADMINとHRZN_DATA_GOVERNORロール以外からのアクセス時は***PII MASKED*** のマスク表示になるようなMASK_PII 、MASK_SENSITIVEというマスキングポリシーを作成します。
そして、それぞれのマスキングポリシーをSSNとCREDITCARDカラムに適用します。
CREATE OR REPLACE MASKING POLICY HRZN_DB.TAG_SCHEMA.MASK_PII AS
(VAL CHAR) RETURNS CHAR ->
CASE
WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN', 'HRZN_DATA_GOVERNOR') THEN VAL
ELSE '***PII MASKED***'
END;
CREATE OR REPLACE MASKING POLICY HRZN_DB.TAG_SCHEMA.MASK_SENSITIVE AS
(VAL CHAR) RETURNS CHAR ->
CASE
WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN', 'HRZN_DATA_GOVERNOR') THEN VAL
ELSE '***SENSITIVE***'
END;
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER MODIFY COLUMN SSN SET MASKING POLICY HRZN_DB.TAG_SCHEMA.MASK_PII;
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER MODIFY COLUMN CREDITCARD SET MASKING POLICY HRZN_DB.TAG_SCHEMA.MASK_SENSITIVE;
ポリシーの動作検証を行います。
まずはHRZN_DATA_GOVERNORロールを使ったアクセスです。
問題なくアクセスできました。
$ USE ROLE HRZN_DATA_GOVERNOR;
$ SELECT SSN,CREDITCARD FROM HRZN_DB.HRZN_SCH.CUSTOMER LIMIT 5;
+-------------+-----------------+
| SSN | CREDITCARD |
|-------------+-----------------|
| 274-01-3464 | 315427724131383 |
| 392-28-9827 | 794149108144044 |
| 173-55-6855 | 615248308602087 |
| 615-36-9200 | 384545197269129 |
| 554-83-1508 | 16855561091058 |
+-------------+-----------------+
次にマスクされるであろうHRZN_DATA_USERロールで試してみます。
ポリシー通り、マスクされています。
$ USE ROLE HRZN_DATA_USER;
$ SELECT SSN,CREDITCARD FROM HRZN_DB.HRZN_SCH.CUSTOMER LIMIT 5;
+------------------+-----------------+
| SSN | CREDITCARD |
|------------------+-----------------|
| ***PII MASKED*** | ***SENSITIVE*** |
| ***PII MASKED*** | ***SENSITIVE*** |
| ***PII MASKED*** | ***SENSITIVE*** |
| ***PII MASKED*** | ***SENSITIVE*** |
| ***PII MASKED*** | ***SENSITIVE*** |
+------------------+-----------------+
オプトインによるポリシー制御
オプトインとは同意を意味します。
つまり同意がある場合は、マスクをさせない条件を含んだマスキングポリシーということです。
実際にマスキングポリシーを作成してみます。
create or replace masking policy HRZN_DB.TAG_SCHEMA.conditionalPolicyDemo
as (phone_nbr string, optin string) returns string ->
case
when optin = 'Y' then phone_nbr
else '***OPT OUT***'
end;
alter table HRZN_DB.HRZN_SCH.CUSTOMER modify column PHONE_NUMBER set masking policy HRZN_DB.TAG_SCHEMA.conditionalPolicyDemo using (PHONE_NUMBER, OPTIN);
ポリシー条件にwhen optin = 'Y' then phone_nbrとあります。
これは、optinというカラムの値がYならphone_nbrへのアクセスを許可します。
elseでそうでないならば、phone_nbrへのアクセスはマスクしますという意味です。
なので事前に同意を意味するカラムが必要です。
実際にアクセスしてみます。
$ SELECT PHONE_NUMBER,OPTIN FROM HRZN_DB.HRZN_SCH.CUSTOMER LIMIT 5;
+-----------------------+-------+
| PHONE_NUMBER | OPTIN |
|-----------------------+-------|
| +1-316-948-9543x00253 | Y |
| ***OPT OUT*** | N |
| 001-032-631-2109 | Y |
| ***OPT OUT*** | N |
| 914.786.6421x9884 | Y |
+-----------------------+-------+
さらにオプトインのマスキングポリシーに柔軟性を持たせて、特定のロールを条件に追加してみます。
create or replace masking policy HRZN_DB.TAG_SCHEMA.conditionalPolicyDemo
as (phone_nbr string, optin string) returns string ->
case
when current_role() in ('ACCOUNTADMIN', 'HRZN_DATA_GOVERNOR') and optin = 'Y' then phone_nbr
else '***OPT OUT***'
end;
alter table HRZN_DB.HRZN_SCH.CUSTOMER modify column PHONE_NUMBER set masking policy HRZN_DB.TAG_SCHEMA.conditionalPolicyDemusing (PHONE_NUMBER, OPTIN);
上記ポリシーはcurrent_role() in ('ACCOUNTADMIN', 'HRZN_DATA_GOVERNOR')の条件を追加しており、アクセスしようとしているロールがinで定義されたロールかを条件としています。
実際どのような動作となるかは以下のとおりです。
$ USE ROLE HRZN_DATA_GOVERNOR;
$ SELECT PHONE_NUMBER,OPTIN FROM HRZN_DB.HRZN_SCH.CUSTOMER LIMIT 5;
+-----------------------+-------+
| PHONE_NUMBER | OPTIN |
|-----------------------+-------|
| +1-316-948-9543x00253 | Y |
| 2428981095 | N |
| 001-032-631-2109 | Y |
| (980)913-5080x467 | N |
| 914.786.6421x9884 | Y |
+-----------------------+-------+
$ USE ROLE HRZN_DATA_USER;
$ SELECT PHONE_NUMBER,OPTIN FROM HRZN_DB.HRZN_SCH.CUSTOMER LIMIT 5;
+-----------------------+-------+
| PHONE_NUMBER | OPTIN |
|-----------------------+-------|
| +1-316-948-9543x00253 | Y |
| ***OPT OUT*** | N |
| 001-032-631-2109 | Y |
| ***OPT OUT*** | N |
| 914.786.6421x9884 | Y |
+-----------------------+-------+
柔軟なポリシー適用方法
タグ付けによるメリットにもう1つあります。
それが、特定のタグと値を持つカラムに対して、一括でポリシー適用する方法です。
実際にどのようにして適用するか見てみます。
-- PII_COLというタグを作成し、値を限定します
CREATE OR REPLACE TAG HRZN_DB.TAG_SCHEMA.PII_COL ALLOWED_VALUES 'PII-DATA','NON-PII';
-- 一括適用させたカラムにPII_COLのタグをカラムに適用させます
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER MODIFY COLUMN LAST_NAME SET TAG HRZN_DB.TAG_SCHEMA.PII_COL = 'PII-DATA';
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER MODIFY COLUMN BIRTHDATE SET TAG HRZN_DB.TAG_SCHEMA.PII_COL = 'PII-DATA';
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER MODIFY COLUMN STREET_ADDRESS SET TAG HRZN_DB.TAG_SCHEMA.PII_COL = 'PII-DATA';
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER MODIFY COLUMN CITY SET TAG HRZN_DB.TAG_SCHEMA.PII_COL = 'PII-DATA';
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER MODIFY COLUMN STATE SET TAG HRZN_DB.TAG_SCHEMA.PII_COL = 'PII-DATA';
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER MODIFY COLUMN ZIP SET TAG HRZN_DB.TAG_SCHEMA.PII_COL = 'PII-DATA';
-- マスキングポリシーの条件にSYSTEM$GET_TAG_ON_CURRENT_COLUMNを使ったタグの条件を定義します
CREATE OR REPLACE MASKING POLICY HRZN_DB.TAG_SCHEMA.PII_DATA_MASK AS (VAL string) RETURNS string ->
CASE
WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('HRZN_DB.TAG_SCHEMA.PII_COL') = 'PII-DATA'
AND CURRENT_ROLE() NOT IN ('HRZN_DATA_GOVERNOR','ACCOUNTADMIN')
THEN '**PII TAG MASKED**'
ELSE VAL
END;
-- マスキングポリシーをタグに適用させます
ALTER TAG HRZN_DB.TAG_SCHEMA.PII_COL SET MASKING POLICY HRZN_DB.TAG_SCHEMA.PII_DATA_MASK;
ポリシー一括適用の動作を確認してみます。
PII_COLタグを適用したカラムへのアクセスでマスキングされています。
$ USE ROLE HRZN_DATA_USER;
$ SELECT FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, ZIP FROM HRZN_DB.HRZN_SCH.CUSTOMER LIMIT 5;
+------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| FIRST_NAME | LAST_NAME | STREET_ADDRESS | CITY | STATE | ZIP |
|------------+--------------------+--------------------+--------------------+--------------------+--------------------|
| Scott | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** |
| Sheri | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** |
| Tonya | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** |
| Shawn | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** |
| Andrew | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** | **PII TAG MASKED** |
+------------+--------------------+--------------------+--------------------+--------------------+--------------------+
行レベルマスキングポリシー
続いて行アクセスマスキングポリシーです。ダイナミックマスキングポリシーは列に対してマスキングしていましたが、次は行に対してのマスキングになります。
QuickStartでは、より柔軟な制御を行えるマッピングテーブルルックアップを例に紹介がありました。
マッピングテーブルルックアップは、マスキングポリシーの条件としてマッピングテーブルのデータを基にマスキングの制御を行います。
実際にクエリを見た方がわかりやすいので、早速紹介します。
まずはマッピングテーブルを用意します。
$ SELECT * FROM HRZN_DB.TAG_SCHEMA.ROW_POLICY_MAP;
+----------------+------------------+
| ROLE | STATE_VISIBILITY |
|----------------+------------------|
| HRZN_DATA_USER | MA |
+----------------+------------------+
次にマスキングポリシーを用意します。
CREATE OR REPLACE ROW ACCESS POLICY HRZN_DB.TAG_SCHEMA.CUSTOMER_STATE_RESTRICTIONS
AS (STATE STRING) RETURNS BOOLEAN ->
CURRENT_ROLE() IN ('ACCOUNTADMIN','HRZN_DATA_ENGINEER','HRZN_DATA_GOVERNOR')
OR EXISTS
(
SELECT rp.ROLE
FROM HRZN_DB.TAG_SCHEMA.ROW_POLICY_MAP rp
WHERE 1=1
AND rp.ROLE = CURRENT_ROLE()
AND rp.STATE_VISIBILITY = STATE
)
COMMENT = 'Policy to limit rows returned based on mapping table of ROLE and STATE: governance.row_policy_map';
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER ADD ROW ACCESS POLICY HRZN_DB.TAG_SCHEMA.CUSTOMER_STATE_RESTRICTIONS ON (STATE);
簡単にマスキングポリシーの定義説明をすると、CURRENT_ROLE() IN() で定義されたロールでのアクセスは全てのデータを返しますが、OR EXISTS 内部のサブクエリでマッチしたロールの場合は AND 条件にマッチしたデータのみを返します。
どのような動作となるか確認してみます。
$ USE ROLE HRZN_DATA_USER; --HRZN_DATA_USERロールなのでSTATEがMAのみ表示
$ SELECT FIRST_NAME, STREET_ADDRESS, STATE, OPTIN, PHONE_NUMBER, EMAIL, JOB, COMPANY FROM HRZN_DB.HRZN_SCH.CUSTOMER LIMIT 5;
+-------------+--------------------+-------+-------+------------------------+-------------------------------+---------------------------------+---------------------------+
| FIRST_NAME | STREET_ADDRESS | STATE | OPTIN | PHONE_NUMBER | EMAIL | JOB | COMPANY |
|-------------+--------------------+-------+-------+------------------------+-------------------------------+---------------------------------+---------------------------|
| Beverly | **PII TAG MASKED** | MA | Y | (188)087-1826 | phillipsbrittany@example.net | Nurse- mental health | Sullivan PLC |
| Christopher | **PII TAG MASKED** | MA | Y | 001-021-934-3389x4333 | matthewroberts@example.net | Product designer | Manning LLC |
| Bradley | **PII TAG MASKED** | MA | N | ***OPT OUT*** | wilsonchristopher@example.org | Farm manager | Austin LLC |
| Kathryn | **PII TAG MASKED** | MA | N | ***OPT OUT*** | hoodlisa@example.org | Analytical chemist | Walker- Webb and Potter |
| Yvonne | **PII TAG MASKED** | MA | Y | 001-689-282-3176x10201 | dorisschroeder@example.net | Investment banker- corporate | Mccoy-Parks |
+-------------+--------------------+-------+-------+------------------------+-------------------------------+---------------------------------+---------------------------+
$ USE ROLE HRZN_DATA_GOVERNOR; --HRZN_DATA_GOVERNORロールなので全てのデータを表示
$ SELECT FIRST_NAME, STREET_ADDRESS, STATE, OPTIN, PHONE_NUMBER, EMAIL, JOB, COMPANY FROM HRZN_DB.HRZN_SCH.CUSTOMER LIMIT 5;
+------------+---------------------------+-------+-------+-----------------------+---------------------------+-------------------------------+----------------------------+
| FIRST_NAME | STREET_ADDRESS | STATE | OPTIN | PHONE_NUMBER | EMAIL | JOB | COMPANY |
|------------+---------------------------+-------+-------+-----------------------+---------------------------+-------------------------------+----------------------------|
| Scott | 1106 Commanders Way South | NY | Y | +1-316-948-9543x00253 | kjones@example.org | Speech and language therapist | Peterson- Tapia and Lawson |
| Sheri | 108 East 11th Avenue | WV | N | 2428981095 | beth85@example.com | Civil Service administrator | Gibson-May |
| Tonya | 2313 South 4th Street | ND | Y | 001-032-631-2109 | danielfrancis@example.com | Retail banker | Rivera PLC |
| Shawn | 2505 Walker Lane | WA | N | (980)913-5080x467 | cookkevin@example.net | Higher education lecturer | Jackson-Smith |
| Andrew | 600 12th Avenue South | HI | Y | 914.786.6421x9884 | sara84@example.net | Contracting civil engineer | Banks and Sons |
+------------+---------------------------+-------+-------+-----------------------+---------------------------+-------------------------------+----------------------------+
集約ポリシー
集計ポリシーは、ポリシー条件に集約関数を必須とさせ、極端に小さいグループサイズに対するクエリを行わせないようにするポリシーです。
まず集約関数を必須にさせることで、SELECT *などのクエリから、データを推測し個人を特定することを防ぎます。
また極端にグループサイズが小さいクエリに対してのクエリ実行拒否も同じ意図があり、グループサイズが小さいと統計的な情報から個人を特定してしまう恐れがあり、それを防ぐ意図があります。
実際にポリシーを作成し適用します。
このポリシーはMIN_GROUP_SIZE => 100ということなので、最小グループサイズが100以上でなければクエリが実行できません。
CREATE OR REPLACE AGGREGATION POLICY HRZN_DB.TAG_SCHEMA.aggregation_policy
AS () RETURNS AGGREGATION_CONSTRAINT ->
CASE
WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','HRZN_DATA_ENGINEER','HRZN_DATA_GOVERNOR')
THEN NO_AGGREGATION_CONSTRAINT()
ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 100)
END;
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER_ORDERS SET AGGREGATION POLICY HRZN_DB.TAG_SCHEMA.aggregation_policy;
動作確認をしてみます。
まずは集計関数なしのクエリ実行検証です。
HRZN_DATA_USERロールの場合ポリシー条件により、GROUP BYがないクエリはコンパイルエラーとなります。
$ USE ROLE HRZN_DATA_USER;
$ SELECT TOP 101 * FROM HRZN_DB.HRZN_SCH.CUSTOMER_ORDERS;
506045 (23001): SQL compilation error: Aggregation policy violation: aggregation required.
次に、集計関数ありのクエリで検証してみます。
グループサイズが100以上の条件にマッチするデータなく集計できずNullとなる
$ SELECT
cl.state,
cl.city,
COUNT(oh.order_id) AS count_order,
SUM(oh.order_amount) AS order_total
FROM HRZN_DB.HRZN_SCH.CUSTOMER_ORDERS oh
JOIN HRZN_DB.HRZN_SCH.CUSTOMER cl
ON oh.customer_id = cl.id
WHERE oh.order_amount > 64
GROUP BY ALL
ORDER BY order_total DESC;
+-------+--------------------+-------------+-------------+
| STATE | CITY | COUNT_ORDER | ORDER_TOTAL |
|-------+--------------------+-------------+-------------|
| NULL | **PII TAG MASKED** | NULL | NULL |
+-------+--------------------+-------------+-------------+
order_amountの条件を緩めることでグループサイズを増やし、集計できるようになりました。
$ SELECT
cl.state,
cl.city,
COUNT(oh.order_id) AS count_order,
SUM(oh.order_amount) AS order_total
FROM HRZN_DB.HRZN_SCH.CUSTOMER_ORDERS oh
JOIN HRZN_DB.HRZN_SCH.CUSTOMER cl
ON oh.customer_id = cl.id
WHERE oh.order_amount > 3
GROUP BY ALL
ORDER BY order_total DESC;
+-------+--------------------+-------------+-------------+
| STATE | CITY | COUNT_ORDER | ORDER_TOTAL |
|-------+--------------------+-------------+-------------|
| MA | **PII TAG MASKED** | 1008 | 20658.2 |
+-------+--------------------+-------------+-------------+
投影ポリシー
投影ポリシーは、簡単に言うとSELECTステートメントを使用して列から値を投影(出力)することを防ぎます。
これは特定のカラムを参照させたくない場合に有効なアプローチです。
SELECTのカラム指定でのクエリ結果に含めることはできませんが、Where区などの条件にそのカラムを含めることは可能です。
単純に対象のカラムは見れないが、条件としては扱えると思っていただいて良いです。
投影ポリシーの作成例です。
特定のロールからのアクセスは許可するが、それ以外は拒否するポリシーです。
CREATE OR REPLACE PROJECTION POLICY HRZN_DB.TAG_SCHEMA.projection_policy
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','HRZN_DATA_ENGINEER', 'HRZN_DATA_GOVERNOR')
THEN PROJECTION_CONSTRAINT(ALLOW => true)
ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;
ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER MODIFY COLUMN ZIP SET PROJECTION POLICY HRZN_DB.TAG_SCHEMA.projection_policy;
早速違反するロールでアクセスしてみます。
HRZN_DATA_USERロールはポリシー違反となりますので、SQL compilation errorになります。
エラーにZIPカラムを取り除いてと言われます。
$ USE ROLE HRZN_DATA_USER;
$ SELECT TOP 5 * FROM HRZN_DB.HRZN_SCH.CUSTOMER;
391876 (23001): SQL compilation error: The following columns are restricted by a Projection Policy. Please remove them from the list of projected columns:
ZIP
エラーのとおり、ZIPを覗いてみます。
$ SELECT TOP 5 * EXCLUDE ZIP FROM HRZN_DB.HRZN_SCH.CUSTOMER;
+-----+-------------+--------------------+--------------------+-------+--------------------+------------------------+-------------------------------+------------------+--------------------+------------------------------+-----------------+-------------------------+-------+
| ID | FIRST_NAME | LAST_NAME | STREET_ADDRESS | STATE | CITY | PHONE_NUMBER | EMAIL | SSN | BIRTHDATE | JOB | CREDITCARD | COMPANY | OPTIN |
|-----+-------------+--------------------+--------------------+-------+--------------------+------------------------+-------------------------------+------------------+--------------------+------------------------------+-----------------+-------------------------+-------|
| 21 | Beverly | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | (188)087-1826 | phillipsbrittany@example.net | ***PII MASKED*** | **PII TAG MASKED** | Nurse- mental health | ***SENSITIVE*** | Sullivan PLC | Y |
| 391 | Christopher | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 001-021-934-3389x4333 | matthewroberts@example.net | ***PII MASKED*** | **PII TAG MASKED** | Product designer | ***SENSITIVE*** | Manning LLC | Y |
| 440 | Bradley | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | ***OPT OUT*** | wilsonchristopher@example.org | ***PII MASKED*** | **PII TAG MASKED** | Farm manager | ***SENSITIVE*** | Austin LLC | N |
| 540 | Kathryn | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | ***OPT OUT*** | hoodlisa@example.org | ***PII MASKED*** | **PII TAG MASKED** | Analytical chemist | ***SENSITIVE*** | Walker- Webb and Potter | N |
| 496 | Yvonne | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 001-689-282-3176x10201 | dorisschroeder@example.net | ***PII MASKED*** | **PII TAG MASKED** | Investment banker- corporate | ***SENSITIVE*** | Mccoy-Parks | Y |
+-----+-------------+--------------------+--------------------+-------+--------------------+------------------------+-------------------------------+------------------+--------------------+------------------------------+-----------------+-------------------------+-------+
最後に、Where句によるクエリは問題ないことを確認してみます。
$ USE ROLE HRZN_DATA_USER;
$ SELECT * EXCLUDE ZIP FROM HRZN_DB.HRZN_SCH.CUSTOMER WHERE ZIP NOT IN ('97135', '95357');
+-----+-------------+--------------------+--------------------+-------+--------------------+------------------------+-------------------------------+------------------+--------------------+---------------------------------+-----------------+---------------------------+-------+
| ID | FIRST_NAME | LAST_NAME | STREET_ADDRESS | STATE | CITY | PHONE_NUMBER | EMAIL | SSN | BIRTHDATE | JOB | CREDITCARD | COMPANY | OPTIN |
|-----+-------------+--------------------+--------------------+-------+--------------------+------------------------+-------------------------------+------------------+--------------------+---------------------------------+-----------------+---------------------------+-------|
| 21 | Beverly | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | (188)087-1826 | phillipsbrittany@example.net | ***PII MASKED*** | **PII TAG MASKED** | Nurse- mental health | ***SENSITIVE*** | Sullivan PLC | Y |
| 391 | Christopher | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 001-021-934-3389x4333 | matthewroberts@example.net | ***PII MASKED*** | **PII TAG MASKED** | Product designer | ***SENSITIVE*** | Manning LLC | Y |
| 440 | Bradley | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | ***OPT OUT*** | wilsonchristopher@example.org | ***PII MASKED*** | **PII TAG MASKED** | Farm manager | ***SENSITIVE*** | Austin LLC | N |
| 540 | Kathryn | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | ***OPT OUT*** | hoodlisa@example.org | ***PII MASKED*** | **PII TAG MASKED** | Analytical chemist | ***SENSITIVE*** | Walker- Webb and Potter | N |
| 496 | Yvonne | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 001-689-282-3176x10201 | dorisschroeder@example.net | ***PII MASKED*** | **PII TAG MASKED** | Investment banker- corporate | ***SENSITIVE*** | Mccoy-Parks | Y |
| 716 | Christopher | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 001-375-864-1330x705 | kaitlyn91@example.net | ***PII MASKED*** | **PII TAG MASKED** | Commercial/residential surveyor | ***SENSITIVE*** | Owens- Weaver and Soto | Y |
| 459 | Dennis | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | (381)928-4907 | zwilliams@example.com | ***PII MASKED*** | **PII TAG MASKED** | Claims inspector/assessor | ***SENSITIVE*** | Scott Ltd | Y |
| 912 | Trevor | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | ***OPT OUT*** | gregorywilliams@example.org | ***PII MASKED*** | **PII TAG MASKED** | Oceanographer | ***SENSITIVE*** | Obrien-Scott | N |
| 504 | Anthony | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 001-016-015-9378x186 | sheryl66@example.net | ***PII MASKED*** | **PII TAG MASKED** | Health and safety adviser | ***SENSITIVE*** | Murray Group | Y |
| 496 | Carla | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 750.308.4190x68678 | rdavidson-example.net | ***PII MASKED*** | **PII TAG MASKED** | General practice doctor | ***SENSITIVE*** | Smith and Sons | Y |
| 958 | Kristopher | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 001-086-467-5216x82690 | leverett@example.org | ***PII MASKED*** | **PII TAG MASKED** | Nature conservation officer | ***SENSITIVE*** | Gross- Fowler and Scott | Y |
| 788 | Margaret | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 8617273891 | steven75@example.net | ***PII MASKED*** | **PII TAG MASKED** | Translator | ***SENSITIVE*** | Brewer- Arnold and Morgan | Y |
| 256 | Michelle | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | 001-826-187-9790 | saralindsey@example.org | ***PII MASKED*** | **PII TAG MASKED** | Recruitment consultant | ***SENSITIVE*** | Herrera-Kennedy | Y |
| 909 | Monica | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | ***OPT OUT*** | torresmarc@example.com | ***PII MASKED*** | **PII TAG MASKED** | Technical sales engineer | ***SENSITIVE*** | Chaney-Jackson | N |
| 180 | Steven | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | (384)105-5145 | kellyrobert@example.org | ***PII MASKED*** | **PII TAG MASKED** | Multimedia specialist | ***SENSITIVE*** | Davis LLC | Y |
| 819 | Karl | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | (174)402-1365 | jennifer75@example.com | ***PII MASKED*** | **PII TAG MASKED** | Nature conservation officer | ***SENSITIVE*** | Wood Inc | Y |
| 207 | Caitlin | **PII TAG MASKED** | **PII TAG MASKED** | MA | **PII TAG MASKED** | ***OPT OUT*** | rosesue@example.com | ***PII MASKED*** | **PII TAG MASKED** | Research scientist (medical) | ***SENSITIVE*** | Lewis-Sexton | N |
+-----+-------------+--------------------+--------------------+-------+--------------------+------------------------+-------------------------------+------------------+--------------------+---------------------------------+-----------------+---------------------------+-------+
データアクセスと監査
データアクセスと監査についても必要性、重要性は不要ですね。
簡単にいうと、コンプライアンス、監査、ガバナンスに通じる部分があるので重要です。
QuickStartでは、hisutry系のテーブル/ビューを使って、どこに誰がどのようなアクセスをしたのか、どのようなクエリを実行したのかを確認するクエリの紹介がありました。
非常に使い勝手の良いクエリなので、オブジェクト名を変更するだけで簡単に使えるので是非お試しください。
access_historyを使って特定のオブジェクトへのアクセス確認クエリ
「HRZN」から始まるオブジェクトへどれぐらいアクセスされているか確認することができます。
$ SELECT
value:"objectName"::STRING AS object_name,
COUNT(DISTINCT query_id) AS number_of_queries
FROM snowflake.account_usage.access_history,
LATERAL FLATTEN (input => direct_objects_accessed)
WHERE object_name ILIKE 'HRZN%'
GROUP BY object_name
ORDER BY number_of_queries DESC LIMIT 5;
読み取りクエリと書き込みクエリの内訳確認クエリ
$ SELECT
value:"objectName"::STRING AS object_name,
CASE
WHEN object_modified_by_ddl IS NOT NULL THEN 'write'
ELSE 'read'
END AS query_type,
COUNT(DISTINCT query_id) AS number_of_queries,
MAX(query_start_time) AS last_query_start_time
FROM snowflake.account_usage.access_history,
LATERAL FLATTEN (input => direct_objects_accessed)
WHERE object_name ILIKE 'HRZN%'
GROUP BY object_name, query_type
ORDER BY object_name, number_of_queries DESC LIMIT 5;
直近90日までで実行されたクエリと対象テーブル、実行ユーザを確認するクエリ
$ SELECT
qh.user_name,
qh.query_text,
value:objectName::string as "TABLE"
FROM snowflake.account_usage.query_history AS qh
JOIN snowflake.account_usage.access_history AS ah
ON qh.query_id = ah.query_id,
LATERAL FLATTEN(input => ah.base_objects_accessed)
WHERE query_type = 'SELECT' AND
value:objectName = 'HRZN_DB.HRZN_SCH.CUSTOMER' AND
start_time > dateadd(day, -90, current_date()) LIMIT 5;
SELECT以外のクエリの実行履歴を確認するクエリ
$ SELECT
qh.user_name,
qh.query_text,
value:objectName::string as "TABLE"
FROM snowflake.account_usage.query_history AS qh
JOIN snowflake.account_usage.access_history AS ah
ON qh.query_id = ah.query_id,
LATERAL FLATTEN(input => ah.base_objects_accessed)
WHERE query_type != 'SELECT' AND
value:objectName = 'HRZN_DB.HRZN_SCH.CUSTOMER' AND
start_time > dateadd(day, -90, current_date()) LIMIT 5;
最も長く実行されているクエリを見つけるクエリ
$ SELECT
query_text,
user_name,
role_name,
database_name,
warehouse_name,
warehouse_size,
execution_status,
round(total_elapsed_time/1000,3) elapsed_sec
FROM snowflake.account_usage.query_history
ORDER BY total_elapsed_time desc LIMIT 5;
機密データを含むテーブルに対して実行されたクエリを検索するクエリ
$ SELECT
q.USER_NAME,
q.QUERY_TEXT,
q.START_TIME,
q.END_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE
q.QUERY_TEXT ILIKE '%HRZN_DB.HRZN_SCH.CUSTOMER%'
ORDER BY q.START_TIME DESC LIMIT 5;
まとめ
今回QuickStartで試した紹介した機能は、すでにSnowflakeでデータ分析を始めている方、これからSnowflakeでデータ分析を始める方の両方の方に比較的簡単に導入可能だと思います。
データ分析で新たなインサイトを得る、データドリブンな経営を行う、非常に大切なアクションです。
ですが、それと同じくらいデータガバナンスの取り組みも重要です。
データ分析のPJの成功率は2割である、みたいな話を聞いたことがあります。
PJ失敗の一要因として、データガバナンスへの取り組みが関係しているかは分かりませんが、取り組まないと失敗する確率は上がると思います。
なので、これからも私はデータガバナンスについて、情報発信を行ない、多くのデータ分析PJでデータガバナンスが取り入れられるような世界を目指します。
気になる他の機能について
最後に、今回のガバナンス機能で触れられなかった、機能を列挙し、いつの日かこの記事に追加していきたいと思います。
- Trust Center
- スキーマ変更トラッキング
- レプリケーションとフェイルオーバー/フェイルバック
- Snowflake Data Clean Rooms
- 差分プライバシー
Discussion