Clean Room 테이블에서 자유 형식 SQL 쿼리 실행하기

컨슈머가 Clean Room API 또는 UI 를 사용하여 Clean Room에서 선택한 데이터 세트에 대해 자유 형식의 SQL 쿼리를 실행할 수 있습니다.

Clean Rooms API 의 자유 형식 쿼리

공동 작업자가 Clean Room 외부에서 특정 데이터 세트를 쿼리할 수 있도록 Clean Room을 구성할 수 있습니다. 공동 작업자는 Clean Room에 액세스할 수 있는 모든 환경에서 이러한 데이터 세트에 대한 자유 형식 쿼리를 실행할 수 있으며, 여기에는 Snowsight 또는 Snowflake API 가 포함됩니다. 자유 형식 데이터 세트는 표준 읽기 전용 뷰로 작동하며 SQL, Python 또는 기타 지원되는 Snowflake 언어를 사용하여 쿼리할 수 있습니다.

정책 및 차등 개인정보 보호 지원

자유 형식 쿼리를 위해 Clean Room 데이터를 노출하는 경우, 모든 Snowflake 정책이 준수됩니다. Clean Room 정책(조인 정책, 열 정책)은 자유 형식 쿼리에서 적용되지 않습니다.

차등 개인정보 보호는 자유 형식 쿼리에 노출된 데이터에 적용되지 않습니다. 여기에는 Snowflake 차등 개인정보 보호Clean Room 차등 개인정보 보호 가 모두 포함됩니다.

자유 형식 쿼리 활성화하기

중요

Clean Room이 2025년 6월 이전에 생성된 경우 공급자는 다음 패치를 설치해야 해당 Clean Room에서 API 를 사용하여 자유 형식 쿼리를 활성화할 수 있습니다.

USE ROLE SAMOOHA_APP_ROLE;
CALL samooha_by_snowflake_local_db.provider.patch_cleanroom($cleanroom_name,TRUE);
Copy

공급자

공급자는 다음 단계를 수행하여 Clean Room 공동 작업자가 자유 형식 쿼리를 사용하여 Clean Room의 데이터 세트를 사용할 수 있습니다.

  1. 표준 방식으로 Clean Room을 만듭니다.

  2. 표준 방식으로 데이터 세트를 Clean Room에 등록하고 연결하십시오. 현재 데이터는 API 를 사용하여 등록해야 하며, Clean Room UI 에서는 뷰를 등록하여 자유 형식 쿼리에 사용할 수 없습니다. Clean Room 외부에서 데이터를 공유하기 전에 Snowflake 집계, 조인 또는 기타 정책을 적용해야 합니다.

  3. provider.enable_workflows_for_consumers 를 호출하여 특정 사용자가 다음 단계에서 지정할 테이블에 자유롭게 액세스할 수 있도록 허용합니다.

  4. provider.enable_datasets_for_workflow 를 호출하여 이전 단계의 사용자가 여기에 지정된 데이터 세트에 자유롭게 액세스할 수 있도록 허용합니다.

  5. provider.add_consumers 를 호출하여 표준 방식으로 공동 작업자를 추가합니다.

  6. Clean Room을 게시합니다.

  7. 이러한 테이블에 대한 쿼리 권한을 취소하려면 사용자 수준에서는 provider.disable_consumer_run_analysis 또는 provider.remove_consumers, 뷰 수준에서는 library.unregister_objects 또는 library.unregister_db 를 호출하거나 Clean Room을 삭제하여 취소할 수 있습니다.

Clean Room이 이미 존재하고 데이터가 등록되어 있는 경우 provider.enable_workflows_for_consumersprovider.enable_datasets_for_workflow 를 호출하여 지정된 테이블을 지정된 사용자에게 노출할 수 있습니다.

다음 코드는 세 개의 샘플 테이블을 만들고, 새 Clean Room을 만들고, 테이블을 연결하고, Clean Room을 통해 Clean Room 공동 작업자에게 해당 테이블에 대한 자유 형식 쿼리 액세스 권한을 부여합니다.

----------------- Create sample data -----------------
USE ROLE MYROLE;
CREATE DATABASE freeform_db;

-- Create a table with an aggregation constraint.
CREATE OR REPLACE TABLE freeform_db.public.agg_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS;

CREATE AGGREGATION POLICY freeform_db.public.agg_policy AS ()
  RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

ALTER TABLE freeform_db.public.agg_constrained_table
  SET AGGREGATION POLICY freeform_db.public.agg_policy;

-- Create a table with a projection constraint.
CREATE OR REPLACE TABLE freeform_db.public.proj_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE_FREEFORM.DEMO.CUSTOMERS;

CREATE OR REPLACE PROJECTION POLICY freeform_db.public.proj_policy AS ()
  RETURNS PROJECTION_CONSTRAINT ->
  PROJECTION_CONSTRAINT(ALLOW => false);

ALTER TABLE freeform_db.public.proj_constrained_table MODIFY COLUMN hashed_email
  SET PROJECTION POLICY freeform_db.public.proj_policy;

-- Create a table with a masking policy.
CREATE OR REPLACE TABLE freeform_db.public.masked_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE_FREEFORM.DEMO.CUSTOMERS;

CREATE OR REPLACE MASKING POLICY freeform_db.public.masking_policy
  AS (val string) RETURNS STRING ->
  CASE
    WHEN current_account() IN ('DCR_PROVIDER_PP6') THEN VAL
    ELSE '*********'
  END;

ALTER TABLE freeform_db.public.masked_table MODIFY COLUMN hashed_email
  SET MASKING POLICY freeform_db.public.masking_policy;

----------------- Create and publish a clean room that supports -----------------
----------------- free-form queries against this data.          -----------------

-- Create the clean room. Nothing new here.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

-- Link in the policy-protected tables from above. Nothing new here.
USE ROLE MYROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.register_db('freeform_db');
USE ROLE SAMOOHA_APP_ROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.link_datasets($cleanroom_name,
  ['freeform_db.public.agg_constrained_table',
  'freeform_db.public.proj_constrained_table',
  'freeform_db.public.masked_table']);

-- Grant the following consumer access to the tables specified next.
SET flow_name = freeform_sql;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_workflows_for_consumers($cleanroom_name,
  [$flow_name],
  ['<CONSUMER_LOCATOR>']);

-- Grant the consumer specified above access to the specified tables.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_datasets_for_workflow($cleanroom_name,
  $flow_name,
  ['freeform_db.public.agg_constrained_table',
   'freeform_db.public.proj_constrained_table',
    'freeform_db.public.masked_table']);

-- Add collaborators and publish, in the standard way.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.add_consumers(
  $cleanroom_name, '<CONSUMER_LOCATOR>', '<ORG_NAME>.<CONSUMER_LOCATOR>');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.set_default_release_directive(
  $cleanroom_name, 'V1_0', '0');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.create_or_update_cleanroom_listing(
  $cleanroom_name);
Copy

컨슈머

공급자가 자유 형식의 SQL 워크플로가 있는 Clean Room을 게시하면 해당 Clean Room에 액세스할 수 있는 컨슈머는 노출된 뷰에 대해 쿼리를 실행할 수 있습니다.

  1. 표준 방식으로 Clean Room을 설치합니다. 컨슈머가 직접 테이블에 액세스할 수 있으므로 데이터를 연결할 필요가 없습니다.

  2. consumer.get_provider_freeform_sql_views 를 호출하여 현재 계정 및 역할에 사용할 수 있는 자유 형식의 SQL 뷰를 목록으로 확인합니다.

  3. 데이터에 대해 표준 SQL 쿼리를 실행합니다. 생산자의 정책은 데이터에 대해 적용됩니다.

-- Install the clean room.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';

CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_LOCATOR>');

-- List free form views available in the clean room.
CALL samooha_by_snowflake_local_db.consumer.GET_PROVIDER_FREEFORM_SQL_VIEWS($cleanroom_name);

-- Run queries on the views
SELECT * FROM <PROJECTION_POLICY_VIEW_NAME>;
SELECT * FROM <MASKING_POLICY_VIEW_NAME>;
SELECT COUNT(hashed_email), age_band
  FROM <AGGREGATION_POLICY_VIEW_NAME> group by age_band;
Copy

Clean Rooms UI 의 자유 형식 쿼리

Clean Room에 SQL Query 템플릿을 사용하면 컨슈머가 자유 형식의 SQL 을 작성하여 Clean Room의 데이터를 쿼리할 수 있습니다. SQL Query 템플릿을 사용할 때 소비자 쿼리가 특정 요건을 충족해야 결과를 성공적으로 반환할 수 있습니다. 이러한 요구 사항은 데이터 공급자가 데이터 개인정보 보호 정책을 통해 테이블을 보호하는 방법에 따라 결정됩니다.

UI 에서 Clean Room을 만들거나 업데이트할 때 SQL Query 템플릿을 Clean Room에 추가하고 아래에 설명된 대로 구성합니다.

공급자: Clean Room 생성 및 정책 설정

  1. Clean Room을 만들거나 기존 Clean Room을 편집하고 테이블 또는 뷰를 지정할 수 있습니다.

  2. SQL Query 템플릿을 사용할 때는 Clean Room 생성 프로세스 중에 지정된 조인 정책이 무시되지만 다른 템플릿에서는 적용됩니다.

  3. Configure Analysis & Query 에서 Horizontal » SQL Query 를 선택합니다.

  4. SQL Query 설정 섹션에서 다음 속성을 설정합니다.

    1. Tables 에서 자유 형식 쿼리에서 Clean Room 공동 작업자가 사용할 수 있어야 하는 테이블을 선택합니다. 기본적으로 선택한 테이블의 모든 열을 투영할 수 있으며 집계 정책을 적용할 필요가 없습니다. 투영할 수 있는 열과 집계해야 하는 열을 제어하려면 다음 섹션에서 열 정책을 설정해야 합니다.

    2. Column Policies 섹션에서 다음 값을 설정하여 쿼리에서 열을 사용할 수 있는지 여부와 방법을 제어합니다.

      1. Aggregation policy columns: 쿼리 결과에 표시하기 위해 집계해야 하는 열을 지정합니다. 열에 집계 정책을 적용하고 쿼리에서 하나의 열이 사용되는 경우 결과를 집계해야 합니다. 여기에 나열된 모든 열은 Privacy settings 섹션에 추가됩니다.

      2. Projection policy columns: 프로젝션 정책이 있는 열은 프로젝션할 수 없습니다 (즉, SELECT 문에 포함됨). 그러나 컨슈머는 프로젝션 정책을 통해 열에 필터링하거나 조인할 수 있습니다.

      3. Fully permitted columns: 컨슈머는 SELECT, 필터링하거나 제한 없이 이러한 열에 조인할 수 있습니다(집계 또는 기타).

    3. Privacy settings 섹션에는 집계 정책이 적용된 모든 열이 나열됩니다. Threshold 값은 해당 값이 결과에 표시되려면 얼마나 많은 엔터티가 있어야 하는지를 나타냅니다. 예를 들어 FIRST_NAME 열에 임계값을 5로 설정하고 “Erasmus”라는 이름이 테이블에 4번만 나타나는 경우 “Erasmus”가 포함된 모든 행은 처리되기 전에 필터링됩니다(예: 해당 테이블의 COUNT(*)는 임계값 이하의 그룹 크기를 가진 4행이 생략됨).

컨슈머: 자유 형식 쿼리 실행하기

  1. Clean Rooms UI 에서 Clean Room에 참여하거나 편집합니다.

  2. Configure Analysis & Query 섹션에서 자유 형식 쿼리에 사용할 테이블을 선택합니다.

  3. Finish 를 선택하여 변경 사항을 저장합니다.

  4. 쿼리를 실행하려면 SQL Query 템플릿이 있는 Clean Room에서 Run 을 선택하고 SQL Query 템플릿을 선택합니다.

조인 및 필터링 열 선택하기

정책이 있거나 완전히 허용된 모든 열에 조인하고 필터링할 수 있습니다. 열을 필터에 조인하거나 사용할 수 있는지 여부를 결정합니다.

  1. Query Configurations 섹션에서 Tables 타일을 찾습니다.

  2. 드롭다운 목록을 사용하여 테이블을 선택합니다. 목록에 있는 모든 열에 조인하고 필터링할 수 있습니다.

투영 열 선택하기

SQL Query 템플릿을 사용하여 실행되는 쿼리에는 프로젝션할 수 있는 열에 제한이 있습니다(SELECT 문에 사용됨).

쿼리가 열을 프로젝션할 수 있는지 확인하려면:

  1. Query Configurations 섹션에서 Tables 타일을 찾습니다.

  2. 드롭다운 목록을 사용하여 테이블을 선택합니다.

  3. 프로젝션 정책 레이블이 있는 열을 찾으면 프로젝션할 수 없음을 의미합니다. 프로젝션 정책 레이블이 있는 열을 제외한 모든 열을 프로젝션할 수 있습니다.

집계 요구 사항

공급자가 열에 집계 정책을 지정한 경우 SQL 쿼리 템플릿을 사용하여 실행되는 모든 쿼리는 집계된 결과를 반환해야 합니다.

쿼리 결과를 집계해야 하는지 여부를 결정합니다.

  1. Query Configurations 섹션에서 Tables 타일을 찾습니다.

  2. 드롭다운 목록을 사용하여 테이블을 선택합니다.

  3. 집계 정책 레이블이 있는 열을 찾습니다. 집계 정책 레이블이 하나 이상 있는 경우 쿼리에서 집계를 사용해야 합니다.

집계 정책으로 보호되는 데이터에 대한 성공적인 쿼리를 작성하는 방법에 대한 지침은 다음을 참조하십시오.

그래프 작성 요구 사항

Snowflake가 그래프를 생성하려면 다음이 필요합니다.

  • 결과 테이블에는 최소한 1개의 측정값(숫자) 열과 1개의 차원(카테고리) 열이 포함되어야 합니다.

  • 측정값 열 이름에는 다음 접두사 또는 접미사가 있어야 합니다(대/소문자 구분 안 함).

    • 열 이름 접두사:

      • COUNT

      • SUM

      • AVG

      • MIN

      • MAX

      • OUTPUT

      • OVERLAP

    • 열 이름 접미사:

      • _OVERLAP

Snowflake는 결과 테이블에서 첫 번째 적격 측정값 열과 첫 번째 차원 열을 사용하여 차트를 생성합니다.

제한 사항

  • ORDER BY 절은 분석 결과가 표시되는 방식에 영향을 미치지 않습니다.

샘플 쿼리

이 섹션을 사용하여 SQL Query 템플릿으로 분석을 실행할 때 쿼리에 포함할 수 있는 항목과 포함할 수 없는 항목을 더 잘 이해할 수 있습니다.

집계 함수가 없는 쿼리

어떤 경우에는 집계 함수를 사용하지 않고도 값을 반환할 수 있습니다.

허용됨

허용되지 않음

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