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

簡単な例¶

以下は、プロバイダーテーブルとコンシューマーテーブルをメールで結合し、重複回数を都市ごとに表示するシンプルな SQL の例です:

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

呼び出し元が使用するテーブルのほか、JOIN および GROUP BY 列を選択できるようにした JinjaSQL テンプレートとして表示されるクエリは次のようになります。

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

このテンプレートの注意事項：

• {{ double bracket pairs }} 内の値はカスタム変数です。group_by_col、 my_table、 source_table、 consumer_join_col、 provider_join_col、 group_by_col はすべて、呼び出し元が設定したカスタム変数です。

• source_table および my_table は、呼び出し元が設定するSnowflake定義の文字列配列変数です。配列のメンバーは、clean roomにリンクされたプロバイダーとコンシューマーテーブルの完全修飾名です。呼び出し元は、各配列にどのテーブルを含めるかを指定します。

• テンプレートでは、プロバイダーテーブルは 小文字の p として、コンシューマーテーブルは 小文字の c としてエイリアス化する必要があります。複数のテーブルがある場合は、 p1 、 p2 、 c1 、 c2 のようにインデックスを付けることができます。

• IDENTIFIER がすべての列名とテーブル名に必要です。なぜなら、 {{ double brackets }} の変数は文字列リテラルに評価されますが、これは有効な識別子ではないからです。

• JinjaSQL フィルター を変数に適用して、いずれかの側で設定された任意の 結合ポリシーまたは列ポリシー を強制できます。Snowflakeはカスタムフィルターの join_policy および column_policy を実装します。これは、列がクリーンルームの結合ポリシーまたは列ポリシーのそれぞれに準拠しているかどうかを確認し、そうでない場合はクエリを失敗します。フィルターは {{ column_name | filter_name }} のように列名に適用されます。

これらの点については、後で詳しく説明します。

コンシューマーがこのテンプレートを実行するコードを以下に示します。列名がテンプレートで宣言されたテーブルエイリアスによって修飾される点に注目してください。

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

クリーンルーム UI でこのテンプレートを使用できるようにするため、プロバイダーは :ref:`テンプレート <dcr_provider_add_ui_form_customizations> 用のカスタム UI フォームを作成する ` 必要があります。その UI フォームには、テンプレート変数名に対応する名前付きのフォーム要素が含まれ、フォームで提供された値はテンプレートに渡されます。

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

クリーンルームのテンプレートは JinjaSQL テンプレートです。テンプレートを作成するには、次のトピックに精通している必要があります。

• Jinjaテンプレートの基本

• Jinjaの JinjaSQL 拡張機能。

consumer.get_jinja_sql プロシージャを使用して、テンプレートの有効性をテストしてから、レンダリングされたテンプレートを実行して、期待される結果が得られることを確認します。このプロシージャは、 join_policy などのクリーンルームフィルター拡張機能をサポートしていないため、これらのフィルターなしでテンプレートをテストし、後で追加する必要があります。

例：

-- Template to test
SELECT {{ col1 | sqlsafe }}, {{ col2 | sqlsafe }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c
  ON {{ provider_join_col | sqlsafe }} = {{ consumer_join_col | sqlsafe}}
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe}}{% endif %};

-- Render the template.
USE WAREHOUSE app_wh;
USE ROLE SAMOOHA_APP_ROLE;

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.GET_SQL_JINJA(
$$
SELECT {{ col1 | sqlsafe }}, {{ col2 | sqlsafe }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c
  ON IDENTIFIER({{ provider_join_col }}) = IDENTIFIER({{ consumer_join_col }})
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};
  $$,
  object_construct(
'col1', 'c.status',
'col2', 'c.age_band',
'where_phrase', 'p.household_size > 2',
'consumer_join_col', 'c.age_band',
'provider_join_col', 'p.age_band',
'source_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
'my_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']
));

レンダリングされたテンプレートは次のようになります。

SELECT c.status, c.age_band
  FROM IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p
  JOIN IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS c
  ON p.age_band = c.age_band
  WHERE p.household_size > 2;

環境内で上記の SQL ステートメントの実行を試み、ステートメントが機能し、期待される結果が得られるかどうかを確認します。

次に、WHERE 句なしでテンプレートをテストします。

-- Render the template without a WHERE clause
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.GET_SQL_JINJA(
$$
SELECT {{ col1 | sqlsafe }}, {{ col2 | sqlsafe }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c
  ON {{ provider_join_col | sqlsafe }} = {{ consumer_join_col | sqlsafe}}
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};
  $$,
  object_construct(
'col1', 'c.status',
'col2', 'c.age_band',
'consumer_join_col', 'c.age_band',
'provider_join_col', 'p.age_band',
'source_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
'my_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']
));

レンダリングされたテンプレート:

SELECT c.status, c.age_band
  FROM IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p
  JOIN IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS c
  ON p.age_band = c.age_band
  ;

ポリシーフィルターをテンプレートに追加し、テンプレートをクリーンルームに追加します。

CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'simple_template',
    $$
    SELECT {{ col1 | sqlsafe | column_policy }}, {{ col2 | sqlsafe | column_policy }}
      FROM IDENTIFIER({{ source_table[0] }}) AS p
      JOIN IDENTIFIER({{ my_table[0] }}) AS c
      ON {{ provider_join_col | sqlsafe | join_policy }} = {{ consumer_join_col | sqlsafe | join_policy }}
      {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};
    $$,
);

データ保護¶

テンプレートはプロバイダーとコンシューマーがclean roomにリンクしたデータセットにのみアクセスできます。

プロバイダーもコンシューマーも、データに結合ポリシー、列ポリシー、アクティベーションポリシーを設定して、結合、投影、またはアクティベーションできる列を保護できます。ただし テンプレートには、ポリシーを適用する列に対して 適切な JinjaSQL ポリシーフィルター が含まれている 必要があります 。

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

テンプレートを作成する際、名前はすべて小文字、数字、スペース、またはアンダースコアでなければなりません。アクティベーションテンプレート（コンシューマーが実行するプロバイダーのアクティベーションを除く）は、activation_ で始まる名前でなければなりません。テンプレート名は、 provider.add_custom_sql_template または consumer.create_template_request を呼び出すときに割り当てられます。

有効な名前の例：

• my_template

• activation_template_1

無効な名前の例：

• my template - スペースは使用できません

• My_Template - 小文字のみ使用可

テンプレート変数¶

テンプレートの呼び出し元は、テンプレート変数に値を渡すことができます。JinjaSQL 構文を使用すると、 {{ double_brackets }} 内の任意の変数名に対する変数バインディングが可能になりますが、Snowflakeは以下に説明するように、変数名をいくつか予約しており、それらをオーバーライドすることはできません。

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

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

クエリの最上位では、Snowflakeがクエリで結合と列のポリシーを正しく検証できるよう、すべてのテーブルまたはサブクエリは p （プロバイダーテーブルの場合）または c （コンシューマーテーブルの場合）のいずれかとしてエイリアス化されている必要があります。結合ポリシーまたは列のポリシーに対して検証する必要がある列は、小文字の p または c テーブルエイリアスで修飾されている必要があります。（p または c を指定することで、それぞれプロバイダーポリシーまたはコンシューマーポリシーに対して列を検証するかどうかをバックエンドに指示します。）

クエリで複数のプロバイダーテーブルまたはコンシューマーテーブルを使用する場合は、最初のテーブルエイリアスの後は、数値の連番で1から始まるサフィックスを追加します。したがって、最初、2番目、3番目のプロバイダーテーブルは p 、 p1 、 p2 のように、最初、2番目、3番目のコンシューマーテーブルは c 、 c1 、 c2 のようになります。p または c インデックスは、空きのない連続である必要があります（つまり、エイリアスは p 、 p1 、 p2 と作成し、p 、 p2 、 p4 とはしない）

例

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

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

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

• join_policy: 列がデータ所有者の結合ポリシーにある場合は成功し、それ以外の場合は失敗します。

• column_policy:列がデータ所有者の列ポリシーにある場合は成功し、それ以外の場合は失敗します。

• activation_policy:列がデータ所有者のアクティベーションポリシーにある場合は成功し、それ以外の場合は失敗します。

• join_and_column_policy:列がデータ所有者の結合または列ポリシーにある場合は成功し、それ以外の場合は失敗します。

• identifier JinjaSQL フィルターは、Snowflakeテンプレートでは サポートされていません 。

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

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

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

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

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

ポリシーは、他のコラボレーターが所有する列に対してのみチェックされます。ポリシーが、自分の所有するデータに対してチェックされることはありません。

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

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

clean roomにアップロードされたPythonコードを、テンプレートから実行することができます。テンプレートからPython関数を呼び出し、データ行から値を受け取ったり、クエリで使用する値や投影する値を返したりできます。

• プロバイダー がカスタムPythonコードをクリーンルームにアップロードすると、テンプレートは構文 cleanroom.function_name でPython関数を呼び出します。詳細はこちらをご参照ください。

• コンシューマー がカスタムPythonコードをクリーンルームにアップロードすると、テンプレートはそのままの function_name 値を consumer.generate_python_request_template （プロバイダーコードのように`クリーンルーム`にスコープされていない）に渡して関数を呼び出します。詳細はこちらをご参照ください。

プロバイダーコードの例：

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