ウェブアプリで SQL クエリテンプレートを使う

SQL クエリテンプレートをクリーンルームに含めることで、コンシューマーは、クリーンルームのデータをクエリするための無料フォーム SQL を書くことができます。SQL クエリテンプレートを使用する場合、コンシューマークエリが正常に結果を返すためには、一定の要件を満たす必要があります。これらの要件は、データプロバイダーがデータプライバシーポリシーでどのようにテーブルを保護するかによって決定されます。

テンプレートポリシー

SQL クエリテンプレートは、コンシューマーがどのようにデータをクエリできるかを制御するために、以下のポリシーを適用します。

ポリシー

説明

投影ポリシー

クエリが列を投影できるかどうか、つまり、 SELECT ステートメントに列を含めるかどうかを制御します。クリーンルームでは、コンシューマーはプロジェクションポリシーで列を投影することはできません。

集計ポリシー

クエリが結果を返すためにデータを集計する必要があります。SQL クエリテンプレートでアクセスされるすべてのテーブルは、集計ポリシーを持っています。

集計ポリシーは、各集計グループが最小数のエンティティを含むことを要求することによって、エンティティのプライバシーを保護します。エンティティは、列の明確な値によって識別されます。例えば、 email 列に値 joe@company.com を持つすべての行は、同じエンティティに属するかもしれません。

クリーンルームでは、集計ポリシーはテーブル内の複数のエンティティを保護することができます。例えば、ポリシーは、集計グループが少なくとも5世帯と10ユーザーを含むことを要求するかもしれません。

重要

作成プロセスのステップ2でクリーンルームに追加される結合ポリシーは、 SQL クエリテンプレートを使用する場合、結合を制御しません。むしろ、クリーンルームは、結合列として選択された各列に投影ポリシーと集計ポリシーを適用します。SQL クエリは、 Join Columns ドロップダウンリストで選択した列だけでなく、どの列でも結合できます。

SQL クエリテンプレートをクリーンルームに追加します。

コンシューマーが SQL クエリテンプレートを使用して分析を実行できるように、クリーンルームを構成する一般的なプロセスは以下のとおりです。

  1. クリーンルーム作成プロセス を開始します。

  2. ステップ2では、 Join Columns ドロップダウンリストを使用して、集計ポリシーおよび投影ポリシーで保護する列を選択します。選択した列は、結合キーとして使用できる列には影響しません。

  3. ステップ3で、テンプレートのリストから Horizontal » SQL Query、次に 、 SQL クエリテンプレート を構成します。

  4. クリーンルームをコラボレーターと共有します。

SQL クエリテンプレートの構成

SQL クエリテンプレートをクリーンルームに追加した後、以下のアクションを実行することで構成できます。

コンシューマーが SELECT ステートメントで結合列を使用できるようにする。

Aggregation Policy Columns ドロップダウンリストを使用して、 Join Policy と表示されている列の名前を選択する。

コンシューマーが SELECT、非結合列でフィルタリングできるようにする。

Fully Permitted Columns ドロップダウンリストを使用して、列の名前を選択する。

追加エンティティの定義

結合ポリシーを持つすべての列は、テーブルの集計ポリシーによって保護されたエンティティを識別します。つまり、すべての集計グループには、すべての結合列から最小数の明確な値が含まれていなければなりません。

追加の列を識別エンティティとして指定できるので、各集計グループには、それらの列から最小数の異なる値が含まれる必要があります。例えば、 HEM が結合ポリシーだとします。集計ポリシーのデフォルト構成では、クエリは、 HEM 列から一定量の明確な値を含むグループにデータを集計するように強制されます。ここで、 FIRST_NAME 列を集計ポリシー列として追加したとします。さて、各集計グループには、 HEM 列から一定数の明確な値 と、 FIRST_NAME 列から一定数の明確な値が含まれていなければなりません。

エンティティを識別する列を追加するには、 Aggregation Policy Columns ドロップダウンリストを使用して列名を選択します。

注釈

列がエンティティを識別することを指定すると、コンシューマーはその列をフィルタリングしたり、投影したりすることもできることに注意してください。

最小エンティティ数の変更

列がエンティティを識別する場合、各集計グループには、列から一定数の異なる値が含まれていなければなりません。エンティティの異なる値の数を調整するには:

  1. Privacy Settings » Aggregation Policies セクションを探します。

  2. 列を見つけ、 Threshold を増減します。閾値が高ければ高いほど、各集計グループでより明確な値を返さなければならず、エンティティのプライバシー保護が強化されます。

SQL クエリテンプレートを使用した分析の実行

このセクションの情報を使用して、 SQL クエリテンプレートで成功するクエリを記述します。

どの列に参加できますか？

どの列でも結合できます。

集計の要件

SQL クエリテンプレートを使用するクリーンルーム内のすべてのテーブルは、集計ポリシーを持っています。その結果、 SQL クエリテンプレートを使って実行されるクエリはすべて、集計された結果を返さなければなりません。

集計ポリシーによって保護されたデータに対するクエリを成功させる方法については、ガイドラインをご参照ください。

どの列を投影できますか？

SQL クエリテンプレートを使用して実行されるクエリには、投影できる（SELECT ステートメントで使用できる）列の制限があります。

クエリが列を投影できるかどうかを判断するには:

  1. Query Configurations セクションで、 Tables タイルを見つけます。

  2. ドロップダウンリストを使用してテーブルを選択します。

  3. 投影ポリシーのラベルがある列を探します。投影ポリシーが設定されている列を除き、すべての列を投影することができます。

どの列でフィルタリングできますか？

クエリがどの列でフィルタリングできるかを決定するには:

  1. Query Configurations セクションで、 Tables タイルを見つけます。

  2. ドロップダウンリストを使ってテーブルを選択し、列を確認します。リストされたすべての列でフィルターをかけることができます。

結果の表示

ウェブアプリでクエリを実行するには、 Run を選択し、オプションでクエリ実行の名前とスケジュールを指定します。 Analyses and Queries リストでクエリのステータスを確認し、クエリがいつ終了したかを確認します。完了したクエリを選択すると、結果が表示されます。クエリに成功すると、結果のテーブルが表示されます。結果テーブルが次に詳述する特定の要件を満たす場合、結果をグラフで表示することもできます。Snowflakeには、棒グラフ、折れ線グラフ、円グラフなど、いくつかのグラフタイプがあります。結果セクションで適切なボタンを選択して、出力形式を選択します。

グラフ作成の要件

Snowflakeがグラフを生成できるようにするには:

  • 結果テーブルには、少なくとも1つの測定（数値）列と1つのディメンジョン（カテゴリ）列を含める必要があります。

  • 測定列名には、以下の接頭語または接尾語（大文字と小文字は区別されません）が必要です。

    • 列名の接頭辞:

      • COUNT

      • SUM

      • AVG

      • MIN

      • MAX

      • OUTPUT

      • OVERLAP

    • 列名の接尾辞:

      • _OVERLAP

Snowflakeは、結果テーブルの最初の適格測定列と最初のディメンジョン列を使用してチャートを生成します。

制限事項

  • ORDER BY 句は、分析結果の表示方法には影響しません。

サンプルクエリ

このセクションでは、 SQL クエリテンプレートを使用して分析を実行する際に、クエリに含めることができる内容と含めることができない内容を理解します。

集計関数のないクエリ

状況によっては、集計関数を使わずに値を返すこともできます。

許可済み

不可

 
SELECT gender, regions
  FROM TABLE sample_db.demo.customer
  GROUP BY gender, region;
Copy
 
SELECT gender, regions
  FROM TABLE sample_db.demo.customer;
Copy
共通テーブル式（CTEs）

許可済み

不可

 
WITH audience AS
  (SELECT COUNT(DISTINCT t1.hashed_email),
    t1.status
    FROM provider_db.overlap.customers t1
    JOIN consumer_db.overlap.customers t2
      ON t1.hashed_email = t2.hashed_email
    GROUP BY t1.status);

SELECT * FROM audience;
Copy
 
WITH audience AS
  (SELECT t1.hashed_email,
    t1.status
    FROM provider_db.overlap.customers quoted t1
    JOIN consumer_db.overlap.customers t2
      ON t1.hashed_email = t2.hashed_email
    GROUP BY t1.status)

SELECT * FROM audience
Copy
CREATE, ALTER, TRUNCATE

クエリは、 CREATE、 ALTER、 TRUNCATE を使用することはできません。

結合を含むクエリ

許可済み

 
SELECT p.education_level,
  c.status,
  AVG(p.days_active),
  COUNT(DISTINCT p.age_band)
  FROM  sample_database_preprod.demo.customers c
  INNER JOIN
  sample_database_preprod.demo.customers p
    ON  c.hashed_email = p.hashed_email
  GROUP BY ALL;
Copy
DATE_TRUNC

許可済み

 
SELECT COUNT(*),
  DATE_TRUNC('week', date_joined) AS week
  FROM consumer_sample_database.audience_overlap.customers
  GROUP BY week;
Copy
引用符で囲まれている識別子

許可済み

 
SELECT COUNT(DISTINCT t1.”hashed_email”)
  FROM provider_sample_database.audience_overlap."customers quoted" t1
  INNER JOIN
  consumer_sample_database.audience_overlap.customers t2
    ON t1."hashed_email" = t2.hashed_email;
Copy
言語: 日本語