カスタムclean roomテンプレートの作成¶

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

Clean roomテンプレートは JinjaSQL で記述されています。JinjaSQL はJinjaテンプレート言語の拡張機能で、 SQL クエリを出力として生成します。JinjaSQL はロジックステートメントと実行時の変数解決をサポートするため、ユーザーは実行時にクエリをカスタマイズできます。変数は通常、テンプレートで使用され、クエリで使用するテーブル名、テーブル列、カスタム値を変数で指定できます。

Snowflakeには、一般的なユースケースに対応した事前設計のテンプレートが用意されています。これらのストックテンプレートは、ウェブアプリケーション以外では使用できませんが、プロバイダーもコンシューマーも、clean roomのカスタムテンプレートを作成できます。カスタムテンプレートはコーディングでのみ作成可能ですが、実行はコードからでも、ウェブアプリからでも可能です。

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

• 分析テンプレート は、 SELECT ステートメント (または、 SELECT 演算セット) を評価します。

• アクティベーションテンプレート は、 SELECT ステートメントを CREATE TABLE ステートメントの入れ子にして評価し、テーブル名を返します。このテンプレートは、clean roomの構成に応じて、コンシューマーまたはプロバイダーのSnowflakeアカウントまたはサードパーティにエクスポートされるデータを生成します。アクティビティテンプレートは、分析テンプレートとよく似ていますが、 いくつかの追加要件があります。

clean room UI で、分析テンプレートをアクティベーションテンプレートと関連付けることで、呼び出し元は分析を実行し、その後に自分自身またはサードパーティにデータを送信できます。アクティベーションテンプレートを、関連する分析テンプレートと同じクエリに解決する必要はありません。

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

デフォルト設定のclean roomで、プロバイダーはclean roomにテンプレートを追加し、コンシューマーはそれを選択、構成、実行することができます：

1. プロバイダー はカスタムテンプレートを設計し、 provider.add_custom_sql_template を呼び出すことでclean roomに追加します。

2. コンシューマー は consumer.run_analysis を呼び出てプロバイダーのテンプレートを実行し、テンプレートが必要とする変数の値を渡します。

このフローでは、コンシューマーはプロバイダーからclean roomに招待される必要がありますが、それ以外にサードパーティからの許可は必要ありません。このプロセスには、コンシューマーが提供するテンプレートやプロバイダーが実行するテンプレートなどのバリエーションがありますが、 ここでは説明しません。

SELECT COUNT(*), city FROM consumer_table
  INNER JOIN provider_table
  ON consumer_table.hashed_email = provider_table.hashed_email
  GROUP BY city;

SELECT COUNT(*), IDENTIFIER({{ group_by_col | column_policy }})
  FROM IDENTIFIER({{ my_table[0] }}) AS C
  INNER JOIN IDENTIFIER({{ source_table[0] }}) AS P
  ON IDENTIFIER({{ consumer_join_col | join_policy }}) = IDENTIFIER({{ provider_join_col | join_policy }})
  GROUP BY IDENTIFIER({{ group_by_col | column_policy }});

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.RUN_ANALYSIS(
  $cleanroom_name,
  $template_name,
  ['my_db.my_sch.consumer_table],       -- Populates the my_table variable
  ['my_db.my_sch.provider_table'],      -- Populates the source_table variable
  OBJECT_CONSTRUCT(                     -- Populates custom named variables
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.device_type'
  )
);

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

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

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

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

• テンプレート変数

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

• テンプレートフィルター

• カスタムなPythonコードの実行

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

SELECT col1 FROM IDENTIFIER({{ source_table[0] }}) AS P;

JOIN IDENTIFIER({{ source_table[0] }}) AS p
  ON {{ c_join_col | sqlsafe | join_policy }} = {{ p_join_col | sqlsafe }}

-- Provider uploads a Python function that takes two numbers and returns the sum.
call samooha_by_snowflake_local_db.provider.load_python_into_cleanroom(
  $cleanroom_name,
  'simple_addition',                        -- Function name to use in the template
  ['someval integer', 'added_val integer'], -- Arguments
  [],                                       -- No packages needed
  'integer',                                -- Return type
  'main',                                   -- Handler for function name
  $$

def main(input, added_val):
  return input + int(added_val)
    $$
);

-- Template passes value from each row to the function, along with a
-- caller-supplied argument named 'increment'
call samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'simple_python_example',
$$
    SELECT val, cleanroom.simple_addition(val, {{ increment | sqlsafe }})
    FROM VALUES (5),(8),(12),(39) AS P(val);
$$
);

セキュリティに関する考慮事項¶

テンプレートはclean roomのネイティブアプリケーションが実行する単一の SELECT クエリによって評価される必要があります。現在のユーザーのIDでは、テンプレートは実行されません。

ユーザーはclean room内のデータに直接アクセスするのではなく、すべてのアクセスがテンプレート結果を介して、ネイティブアプリケーションで行われます。

テンプレートの中で明示的に列名を定義した場合でも、列やテーブルがプロバイダーによって提供される場合でも、 クエリで列が使用されると必ず、ポリシーフィルターが適用されます。 結合や列のポリシーを後から変更したり、列を変更したりした際に、テンプレートの更新を忘れないでください。ユーザーが入力した列に対し、 join_policy、 column_policy、 join_and_column_policy、 activation_policy のフィルターを適用してください。

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

テンプレートを使用すると、クエリ結果をclean room外のテーブルに保存することもできます。これを アクティベーション と呼びます。現在、カスタムテンプレートでサポートされているアクティベーションは、プロバイダーのアクティベーションと、コンシューマーのアクティベーション (それぞれプロバイダーまたはコンシューマーのSnowflakeアカウントに結果を保存します) のみです。 アクティベーションの実装方法についてはこちらをご参照ください。

アクティベーションテンプレートは、以下の要件が追加された分析テンプレートです：

• アクティベーションテンプレートは、 SQL スクリプトブロックに評価される JinjaSQL ステートメントで、単純な SELECT ステートメントになる分析テンプレートとは異なります。

• アクティベーション テンプレート の名前は、文字列 activation で始まらなければなりません (コンシューマーが実行するプロバイダーアクティベーションテンプレートを除く)。例: activation_my_template。

• アクティベーションテンプレートには テーブル を作成する必要があります。テーブル名はアクティベーションの種類によって異なります：

  • プロバイダーが実行するプロバイダーアクティベーション： 生成されるテーブル名を cleanroom.temp_result_data にする必要があります。

  • その他すべてのアクティベーションタイプ： 生成されるテーブル名にプレフィックス cleanroom.activation_data_ を付ける必要があります (例： cleanroom.activation_data_cross_activation_results)。テーブル名は、clean room内で一意でなければなりません。

  生成されるテーブルは中間テーブルです。直接アクセスすることは避けてください。

• スクリプトブロックの最後には RETURN ステートメントを記述します。これは、生成されたテーブルの名前をから cleanroom. や cleanroom.activation_data_ のプレフィックスを除いた名前を返します。

• アクティベートされる列は、データをリンクしたプロバイダーまたはコンシューマーの アクティベーションポリシー にリストされ、かつ activation_policy フィルターが適用されている必要があります。列はアクティベーション列の場合もあれば、結合列の場合もあります。

• テンプレートをclean room UI から実行する場合は、 ウェブフォームを用意 し、そこに activation_template_name と enabled_activations のフィールドを含める必要があります。UI で使用するテンプレートには、分析テンプレートと、関連するアクティビティテンプレートが必要です。

• テーブルが生成されるため、すべての計算列は推測される名前ではなく、明示的にエイリアスされなければなりません。つまり：

  SELECT COUNT(*), P.status from T AS P; FAILS、なぜなら、 COUNT 列名が推測されるからです。

  SELECT COUNT(*) AS COUNT_OF_ITEMS, P.status from T AS P; SUCCEEDS、なぜなら、 COUNT 列を明示的にエイリアスしているからです。

以下に、基本的なアクティベーションテンプレートの例を2つ示します。1つはプロバイダーが実行するサーバーのアクティベーションで使用し、もう1つはその他のアクティベーションタイプで使用します。両者の違いは、結果テーブルの名前を含む強調表示された2行です。

-- These are the required table name strings.
BEGIN
  CREATE OR REPLACE TABLE cleanroom.temp_result_data AS
    SELECT COUNT(c.status) AS ITEM_COUNT, c.status, c.age_band
      FROM IDENTIFIER({{ my_table[0] }}) AS c
    JOIN IDENTIFIER({{ source_table[0] }}) AS p
      ON {{ c_join_col | sqlsafe | activation_policy }} = {{ p_join_col | sqlsafe | activation_policy }}
    GROUP BY c.status, c.age_band
    ORDER BY c.age_band;
  RETURN 'temp_result_data';
END;

-- analysis_results can be whatever name you want.
BEGIN
  CREATE OR REPLACE TABLE cleanroom.activation_data_analysis_results AS
    SELECT COUNT(c.status) AS ITEM_COUNT, c.status, c.age_band
      FROM IDENTIFIER({{ my_table[0] }}) AS c
    JOIN IDENTIFIER({{ source_table[0] }}) AS p
      ON {{ c_join_col | sqlsafe | activation_policy }} = {{ p_join_col | sqlsafe | activation_policy }}
    GROUP BY c.status, c.age_band
    ORDER BY c.age_band;
  RETURN 'analysis_results';
END;

次のステップ¶

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

• プロバイダーテンプレート は、プロバイダーが作成するテンプレートです。これがデフォルトのユースケースです。

• コンシューマーテンプレート コンシューマーが作成するテンプレートです。場合によっては、clean roomの作成者が、自身のテンプレートをclean roomに作成、アップロード、実行することをコンシューマーに許可したい場合があります。

• アクティベーションテンプレート 実行に成功すると結果テーブルが作成されます。アクティベーションテンプレートに応じて、結果テーブルはclean room外のプロバイダーまたはコンシューマーのアカウントに保存されることも、アクティベーションハブにリストされたサードパーティのアクティベーションプロバイダーに送信されることもあります。

• 連鎖テンプレート 複数のテンプレートを連鎖させて、各テンプレートの出力を次のテンプレートで使用することができます。

詳細情報¶

• Jinjaドキュメント

• JinjaSQL ドキュメント