カスタムテンプレートの設計¶

clean roomテンプレートについて¶

クリーンルームのテンプレートは JinjaSQL で書かれています。JinjaSQLは、Jinjaテンプレート言語の拡張機能です。JinjaSQLテンプレートは、クリーンルームで実行されたときにSQLステートメントと評価されます。JinjaSQLテンプレート化言語はロジックステートメントと実行時の変数の置換を提供し、実行時にテンプレートをカスタマイズできるようにします。例えば、ユーザーはテンプレートの実行時にテーブル名と列名を提供することができ、テンプレートは渡された値に基づいてそれ自体を調整することができます。

テンプレートには大きく分けて2つのタイプがあります：

分析テンプレート はSQL DQLステートメント（SELECTステートメント）と評価され、テンプレート実行者にクエリ結果を即座に返します。
アクティベーションテンプレート は、結果をすぐに環境に表示するのではなく、Snowflakeアカウントに結果をアクティブ化するために使用されます。アクティベーションテンプレートは、いくつかの追加要件を持つ分析テンプレートと非常によく似ており、DDLステートメント（CREATE TABLE）と評価されます。

カスタムテンプレートの構文¶

Snowflake Data Clean Roomsは、いくつか機能が拡張されたV3の JinjaSQL をサポートしています。

このセクションには以下のトピックが含まれます：

テンプレートの命名規則¶

テンプレートを作成する際、名前には文字、数字、またはアンダースコアのみを含める必要があります。テンプレート名は、テンプレートを登録するときにテンプレート仕様の name フィールドで割り当てられます。

有効な名前の例：

my_template
activation_template_1

無効な名前の例：

my template - スペースは使用できません
my_template! - 特殊文字は許可されません

テンプレート変数¶

テンプレート呼び出し元はテンプレート変数に値を渡すことができます。JinjaSQL構文は、{{double_brackets}}内の任意の変数名の変数バインドを有効にしますが、Snowflakeは、以下で説明するように、上書きすべきでないいくつかの変数名を予約しています。

注意

Snowflake定義またはカスタムにかかわらず、すべての変数はユーザーによって入力され、適切に注意して扱う必要があります。分析テンプレートは単一のSELECTステートメントに解決される必要があります（アクティベーションテンプレートはスクリプトブロックに解決されます）。すべての変数は呼び出し元によって渡されることに注意してください。

Snowflake定義の変数¶

すべてのクリーンルームテンプレートは、Snowflakeによって定義された以下のグローバル変数にアクセスできますが、分析実行者によって渡されます。

source_table:

LINK_DATA_OFFERING経由でコラボレーションにリンクされた、データ提供からのテーブルとビューのゼロベースの文字列配列で、テンプレートによって使用できます。

例：SELECT col1 FROM IDENTIFIER({{ source_table[0] }}) AS p;

my_table:

コラボレーションクリーンルームでは、 my_table はSnowflake Standard Editionユーザーのみが使用します。これらのユーザーの場合、 my_table は、分析実行者がLINK_LOCAL_DATA_OFFERINGを呼び出してリンクしたデータセットのゼロベースの文字列配列です。

例：SELECT col1 FROM IDENTIFIER({{ my_table[0] }}) AS c;

カスタム変数¶

テンプレート作成者は、分析実行者が入力できるテンプレートに任意の変数を含めることができます。これらの変数には、Snowflake定義の変数やテーブルエイリアス名以外の、Jinjaに準拠した名前を付けることができます。テンプレートのパラメーターセクションで、必須変数とオプション変数のガイダンスを提供する必要があります。

カスタム変数は、以下に示すカスタム変数 max_income のように、テンプレートからアクセスできます。

SELECT income FROM my_db.my_sch.customers WHERE income < {{ max_income }};

分析実行者は、分析実行仕様で定義されているように、RUNを呼び出す際に変数を渡します。

変数を正しく解決する¶

文字列値はテンプレートに渡されると、最終的なテンプレートで文字列リテラルに解決されます。バインドされた変数の処理が適切出ない場合、 SQL 解析エラーまたは論理エラーの原因になります：

SELECT {{ my_col }} FROM p; - これは SELECT 'my_col' from p; に解決され、文字列 "my_col" を返します。これは希望するものではないかもしれません。
SELECT age FROM {{ source_table[0] }} AS p; - これは SELECT age FROM 'somedb.somesch.source_table' AS p; に解決されますが、テーブルはリテラル文字列ではなく識別子である必要があるため、解析エラーが発生します。
SELECT age FROM IDENTIFIER({{ source_table[0] }}) AS p {{ where_clause }}; - "WHERE age < 50" を渡すと、 SELECT age FROM mytable AS p 'WHERE age < 50'; に評価されます。リテラル文字列であるWHERE句により、解析エラーが起こります。

したがって、必要に応じて変数を解決する必要があります。次では、テンプレートで変数を適切に解決する方法を説明します：

テーブル名と列名の解決

テーブル名や列名を指定する変数は、以下の2つの方法のどちらかを使用し、テンプレート内で識別子に変換する必要があります：

IDENTIFIER:For example: SELECT IDENTIFIER({{ my_column }}) FROM p;
sqlsafe ：このJinjaSQLフィルターは識別子文字列をSQLテキストに解決します。前の箇条書きと同等のステートメントは SELECT {{ my_column | sqlsafe }} FROM p; です

特定の使用状況によって、IDENTIFIERまたは sqlsafe を使用する場合が定まります。例えば、 p.{{ my_column | sqlsafe }} はIDENTIFIERを使用して簡単に書き換えることができません。

動的 SQL の解決

WHERE 句のように、リテラル SQL として使用する文字列変数がある場合は、 sqlsafe フィルターをテンプレートで使用してください。例:

SELECT age FROM IDENTIFIER({{ source_table[0] }}) AS p WHERE {{ where_clause }};

ユーザーが "age < 50" を where_clause に渡した場合、クエリは SELECT age FROM sometable AS p WHERE 'age < 50'; に解決します。これは、リテラル文字列WHERE条件のため無効なSQLです。この場合は、 sqlsafe フィルターを使用する必要があります。

SELECT age FROM IDENTIFIER( {{ source_table[0] }} ) as p {{ where_clause | sqlsafe }};

必須のテーブルエイリアス¶

クエリの最上位では、すべての source_table データセットには p のエイリアスが必要で、すべての my_table データセットには c のエイリアスが必要です。Snowflakeがクエリで結合と列のポリシーを正しく検証するためです。結合ポリシーまたは列のポリシーに対して検証する必要がある列は、小文字の p または c テーブルエイリアスで修飾されている必要があります。

クエリで複数の source_table または my_table データセットを使用する場合は、最初のテーブルエイリアスの後は、数値の連番で1から始まるサフィックスを追加します。そのため、1番、2番、3番の source_table データセットには p または p1 、 p2 、 p3 など、1番、2番、3番の my_table データセットには c または c1 、 c2 、 c3 などが使用されます。p または c インデックスは、空きのない連続である必要があります（つまり、エイリアスは p1 、 p2 、 p3 と作成し、p1 、 p2 、 p4 とはしない）

例

SELECT p1.col1 FROM IDENTIFIER({{ source_table[0] }}) AS p1
UNION
SELECT p2.col1 FROM IDENTIFIER({{ source_table[1] }}) AS p2;

カスタムのクリーンルームテンプレートフィルター¶

Snowflake は、すべての標準 Jinja フィルターと、ほとんどの標準 JinjaSQL フィルター、およびいくつかの拡張機能をサポートしています：

join_policy:: 列がデータ所有者の結合ポリシーにある場合は成功し、それ以外の場合は失敗します。データ提供へのデータ保護ポリシーの適用をご参照ください。
column_policy:: 列がデータ所有者の列ポリシーにある場合は成功し、それ以外の場合は失敗します。データ提供へのデータ保護ポリシーの適用をご参照ください。
activation_policy:: 列がデータ所有者のアクティベーションポリシーにある場合は成功し、それ以外の場合は失敗します。データ提供へのデータ保護ポリシーの適用をご参照ください。
join_and_column_policy:: 列がデータ所有者の結合または列ポリシーにある場合は成功し、それ以外の場合は失敗します。データ提供へのデータ保護ポリシーの適用をご参照ください。
identifier:: このJinjaSQLフィルターはSnowflakeテンプレートでは サポートされていません 。

Tip

JinjaSQL ステートメントは左から右に評価されます。

{{ my_col | column_policy }} 正しい
{{ my_col | sqlsafe | column_policy }} 正しい
{{ column_policy | my_col }} 正しくない
{{ my_col | column_policy | sqlsafe }} 正しくない： column_policy は、文字列として my_col 値をチェックされ、エラーが起こります。

clean roomへのポリシーの適用¶

クリーンルームは、テンプレートで使用された列に対してクリーンルームポリシーを自動的にチェックしません。列に対してポリシーを強制する場合は、次のとおりです。

テンプレートのその列に適切なポリシーフィルターを適用する必要があります。例:

FROM IDENTIFIER({{ source_table[0] }}) AS p1
JOIN IDENTIFIER({{ source_table[1] }}) AS p2
  ON IDENTIFIER({{ p1_join_col | join_policy }}) = IDENTIFIER({{ p2_join_col | join_policy }})

テーブルを小文字の p または c としてエイリアス化する必要があります。必須のテーブルエイリアスをご参照ください。

ポリシーは、 source_table 変数で参照されるテーブルの列に対してのみチェックされます。これは、クリーンルーム内で共有されるビューを参照します。ポリシーは、クリーンルーム内で共有されないローカルテーブルである my_table 変数で参照されるテーブルの列に対してはチェックされません。

ポリシーをテストするときに、列名をあいまいにしないよう注意してください。2つのテーブルに同じ名前の列がある場合、その列に対してポリシーをテストするには、列名を修飾する必要があります。

アクセスの考慮事項とベストプラクティス¶

テンプレートは、常にクリーンルームアプリケーションロールのコンテキストで実行されます。コラボレーターは、テンプレートアクセスのみに制限されているクリーンルーム内のデータに直接アクセスすることはできません。すべてのアクセスは、ネイティブアプリケーションロールとテンプレート出力を介して行われます。

ベストプラクティスとして、クリーンルームで作成または使用するテンプレートは、以下に従ってください。

コラボレーターのポリシーが尊重されるように、テンプレートで列変数が使用されるたびにポリシーフィルターが適用されるようにします。
可能な場合はユーザー提供の変数をIDENTIFIER()でラップし、SQLインジェクション攻撃に対してテンプレートを強化します。

アクティベーションテンプレート¶

テンプレートは、クエリ結果をクリーンルームの外のテーブルに保存するためにも使用できます。これは アクティベーション と呼ばれます。アクティベーションテンプレートは、以下の追加要件を持つ分析テンプレートです。

アクティベーションテンプレートは、 SQL スクリプトブロックに評価される JinjaSQL ステートメントで、単純な SELECT ステートメントになる分析テンプレートとは異なります。
アクティベーションテンプレートは、結果を保存するためにクリーンルームに内部テーブルを作成する必要があります。テンプレートによって生成されたテーブルには、接頭辞 cleanroom.activation_data_ が必要です（例： cleanroom.activation_data_my_results ）。
内部結果テーブルのすべての列には、データ提供仕様に activation_allowed: TRUE 値が必要です。
スクリプトブロックは、生成されたテーブルの名前から任意の cleanroom.activation_data_ プレフィックスを抜いた（例： RETURN 'my_results' ）名前を返すRETURNステートメントで終わる必要があります。
テンプレート自体には、命名要件はありません。

以下はアクティベーションテンプレート仕様の例です。

api_version: 2.0.0
spec_type: template
name: my_activation_template
version: v0
type: sql_activation
description: Activation template that creates segment data
template: |
  BEGIN
      CREATE OR REPLACE TABLE cleanroom.activation_data_analysis_results AS
      SELECT
          {{ group_by_column | sqlsafe }} AS bucket_label,
          {{ activation_column | sqlsafe | activation_policy }} AS activation_label,
          COUNT(DISTINCT {{ join_column | sqlsafe }}) AS overlap_count
      FROM IDENTIFIER({{ source_table[0] }}) AS p
      GROUP BY {{ group_by_column | sqlsafe }},
               {{ activation_column | sqlsafe }};
      RETURN 'analysis_results';
  END;
parameters:
  - name: join_column
    description: Join column name
    required: true
    default: "p.IP_ADDRESS"
  - name: group_by_column
    description: Group by column name
    required: true
    default: "p.CAMPAIGN_NAME"
  - name: activation_column
    description: Activation column name
    required: true
    default: "p.DEVICE_TYPE"

コラボレーションでアクティベーションを実装する方法を学びます：クエリ結果のアクティベーション。

次のステップ¶

テンプレートシステムの説明は以上です。以下では、clean roomの実装についてテンプレートタイプ別に説明します:

アクティベーションテンプレートは、実行が成功した後に結果テーブルを作成し、これはクリーンルームの外で共有されます。コラボレーション仕様に応じて、結果テーブルを分析実行者または他のコラボレーターと共有することができます。
コードバンドルは、カスタムPython UDFsおよびUDTFsをコラボレーションにアップロードするために使用されます。コラボレーションのテンプレートは、これらの関数を実行して複雑なデータアクションを実行できます。
内部テーブルは、中間または永続的な結果を保存するために使用されます。これは、マルチステップワークフローをサポートするために下流で使用できます。これらのテーブルは、クリーンルーム内のテンプレートやカスタムアップロードコードからアクセスできます。

カスタムテンプレートの設計¶

clean roomテンプレートについて¶

カスタムテンプレートの作成、共有、実行¶

1.JinjaSQLテンプレート¶

2. コラボレーションテンプレート¶

3. テンプレートの実行¶

カスタムテンプレートの開発¶

データ保護¶

カスタムテンプレートの構文¶

テンプレートの命名規則¶

テンプレート変数¶

Snowflake定義の変数¶

カスタム変数¶

変数を正しく解決する¶

必須のテーブルエイリアス¶

カスタムのクリーンルームテンプレートフィルター¶

clean roomへのポリシーの適用¶

アクセスの考慮事項とベストプラクティス¶

アクティベーションテンプレート¶

次のステップ¶

詳細情報¶