자습서: 코드로 Snowflake Data Clean Rooms시작하기¶

소개¶

이 자습서는 Snowflake Data Clean Rooms를 코드로 만들거나 사용할 개발자를 대상으로 합니다. 이 자습서에서는 SQL 코드를 사용하지만, 여기에 표시된 정보를 조정하여 Snowflake에서 지원하는 모든 코딩 언어로 Clean Rooms를 만들고 사용할 수 있습니다.

공급자: 개요¶

다음은 공급자 역할로 클린룸을 만들기 위해 수행해야 하는 단계를 요약한 것입니다.

1. Clean Room에서 공유할 테스트 데이터를 생성합니다.

2. 자신의 Clean Room을 만듭니다.

3. 생성한 데이터를 Clean Room으로 가져옵니다.

4. 데이터에 조인 권한을 설정하여 컨슈머 쿼리에서 조인할 수 있는 열을 지정합니다.

5. Clean Room을 위한 템플릿을 만듭니다. Clean Room 템플릿은 JinjaSQL 로 작성되며 런타임에 SQL 쿼리로 평가됩니다. 대부분의 템플릿에는 공동 작업자가 테이블 및 열 이름, WHERE 절 조건 등을 런타임에 지정할 수 있는 변수가 포함되어 있습니다. Clean Room 공동 작업자가 Clean Room에서 템플릿을 선택하고 실행합니다.

6. Clean Room의 기본 버전을 지정합니다.

7. Clean Room에 액세스할 수 있는 컨슈머를 추가합니다. 이 자습서에서 컨슈머는 Clean Room 관리자가 승인한 계정을 가진 Snowflake 사용자여야 합니다.

8. Clean Room을 게시하여 초대된 컨슈머가 사용할 수 있도록 하십시오.

공급자: 테스트 데이터 만들기¶

SAMOOHA_APP_ROLE 역할이 부여된 사용자로 `Snowsight에 로그인<https://app.snowflake.com>`_ 합니다. 해당 역할이 없는 경우 계정 관리자에게 권한을 부여해 달라고 요청합니다.

Snowsight에서 클린룸 코드를 보관할 새 SQL 워크시트를 만듭니다 . 워크시트의 이름을 “API tutorial - Provider”로 지정합니다.

SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS 테이블에서 가져온 1,000개의 샘플 테스트 데이터 행을 기준으로 테이블을 만듭니다. 이 테이블은 클린룸에서 공급자 데이터로 사용할 것입니다.

데이터베이스를 만들 수 있는 역할을 사용합니다. 이 예에서는 ACCOUNTADMIN 을 사용하지만 테이블을 만들 수 있도록 지원하는 모든 역할을 사용할 수 있습니다.

USE WAREHOUSE app_wh;
USE ROLE ACCOUNTADMIN;
-- Using ACCOUNTADMIN role because you need a role that allows you to create a database.
-- Feel free to use any other role that can create a database.

-- Generate a provider dataset based on the first 1,000 rows of sample data.
CREATE DATABASE IF NOT EXISTS cleanroom_tut_db;
CREATE SCHEMA IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch;

-- Create a temporary table, in case you forget to delete it later.
CREATE TEMPORARY TABLE IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table AS
  SELECT TOP 1000 * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS ORDER BY HASHED_EMAIL ASC;

DESCRIBE TABLE cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table;

공급자: Clean Room 만들기¶

다음 코드 조각은 조직 내에서만 액세스할 수 있는 클린룸을 만듭니다(따라서 INTERNAL로 표시). 클린룸을 조직 외부로 공유하려면, 이 자습서에서 다루지 않는 추가 단계가 필요합니다. 클린룸을 사용자 자신에게 공유할 때는 반드시 INTERNAL이어야 합니다.

대부분의 클린룸 프로시저에는 SAMOOHA_APP_ROLE을 사용해야 합니다.

USE ROLE samooha_app_role;
SET cleanroom_name = 'Developer Tutorial';
CALL samooha_by_snowflake_local_db.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

공급자: Clean Room으로 데이터 가져오기¶

다음으로, 테스트 데이터를 클린룸으로 가져옵니다. 데이터를 클린룸으로 가져오는 두 단계는 다음과 같습니다.

1. 데이터를 등록합니다.

2. 데이터를 클린룸으로 가져옵니다(연결).

USE ROLE ACCOUNTADMIN;
CALL samooha_by_snowflake_local_db.provider.register_db('cleanroom_tut_db');

-- Use samooha_app_role until you need to clean things up at the end.
USE ROLE samooha_app_role;

CALL samooha_by_snowflake_local_db.provider.link_datasets($cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table']);

CALL samooha_by_snowflake_local_db.provider.view_provider_datasets($cleanroom_name);

공급자: 데이터에 대한 조인 정책 설정¶

공급자와 컨슈머 모두 자신의 데이터에 조인 정책 을 지정할 수 있습니다. 클린룸 조인 정책은 해당 클린룸에서 공동 작업자의 쿼리로 테이블의 어떤 열을 조인할 수 있는지 지정합니다. 이를 통해 다른 사용자가 클린룸에서 데이터를 사용하는 방식을 더욱 강력하게 제어할 수 있습니다. 자신의 정책은 자신의 쿼리에 적용되지 않습니다. 즉, 쿼리를 실행할 때 자신의 데이터에 설정한 조인 정책은 무시됩니다. 정책은 다른 사용자가 실행한 쿼리에만 적용됩니다.

클린룸 조인 정책은 테이블에 설정되며 해당 테이블이 사용되는 모든 클린룸에 적용됩니다. 여기에 나열되지 않은 열은 클린룸에서 INNERJOIN 또는 OUTERJOIN 문을 사용하여 조인할 수 없습니다. 조인 정책을 지정하지 않으면 모든 열을 조인할 수 있습니다.

Clean Room 조인 정책은 Snowflake 조인 정책 과 동일하지 않습니다.Clean Room 정책은 조인할 수 있는 열을 지정하는 반면, Snowflake 조인 정책은 조인할 수 없는 열을 지정합니다.

열마다 database_name.schema_name.table_or_view_name:column_name 형식을 사용하여 조인 가능한 열을 테이블에 지정합니다. 다음 예제에서는 공급자 데이터의 세 열을 조인할 수 있도록 합니다.

-- Limit joinable columns in this table to age_band, region_code, and device_type
CALL samooha_by_snowflake_local_db.provider.set_join_policy($cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:AGE_BAND',
   'cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:REGION_CODE',
   'cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:DEVICE_TYPE']);

CALL samooha_by_snowflake_local_db.provider.view_join_policy($cleanroom_name);

공급자: 템플릿 추가하기¶

Clean Room 템플릿은 SELECT 쿼리로 평가하는 JinjaSQL 템플릿입니다. 이 쿼리는 조인 및 열 정책에 따라 Clean Room에 연결된 모든 데이터 세트에 액세스할 수 있습니다.

이 자습서에서는 JinjaSQL 템플릿 디자인에 대한 자세한 내용은 다루지 않지만 다음은 구현하려는 SQL 쿼리입니다.

SELECT
  COUNT(*),
  group_by_col
FROM Consumer_Table AS C
  INNER JOIN Provider_Table AS P
    ON C.join_col = P.join_col
GROUP BY group_col;

쿼리는 단순히 지정된 조인 열에서 하나의 공급자 테이블과 하나의 컨슈머 테이블을 조인하고, 지정된 그룹화 열을 기준으로 그룹화하고, 각 그룹의 그룹 값과 개수를 투영합니다. 사용자가 템플릿을 실행할 때 Clean Room에서 실행되는 쿼리입니다.

다음은 동일한 쿼리에 대한 JinjaSQL 템플릿으로, 컨슈머가 테이블이나 열을 지정할 수 있는 변수를 추가했습니다. 컨슈머가 변수를 지정하면 위와 유사하지만 컨슈머가 제공한 테이블 및 열 이름을 사용하여 SQL 쿼리로 평가합니다.

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

템플릿에 대한 몇 가지 참고 사항:

• {{brackets}} 로 둘러싸인 콘텐츠는 템플릿을 실행할 때 컨슈머가 전달한 변수 이름입니다. 컨슈머가 전달한 변수는 group_by_col, consumer_join_col, provider_join_col 입니다.

• my_table 및 source_table 배열은 시스템에 의해 생성된 전역 변수로, 호출자가 전달한 컨슈머 및 공급자 테이블 이름으로 채워집니다. 이러한 테이블은 컨슈머와 공급자가 Clean Room에 연결해야 합니다.

• 모든 공급자 테이블은 쿼리에서 p 로 별칭을 지정해야 합니다. 모든 컨슈머 테이블의 별칭은 c 로 지정해야 합니다. 여러 개의 테이블을 사용하는 경우 1을 기본 접미사로 사용하여 별칭을 지정합니다. p, p1, p2, p3 등 공급자 테이블의 경우, c, c1, c2, c3 등과 같이 컨슈머 테이블 별칭을 사용합니다. (p 와 p0 은 동일합니다.)

• Snowflake Data Clean Rooms는 변수에 따라 작동하는 일부 사용자 지정 JinjaSQL 필터 를 지원합니다. column_policy 및 row_policy 필터는 적용되는 열이 해당 Clean Room의 열 및 행 정책을 준수하는지 확인하며, 그렇지 않으면 템플릿 실행 요청이 실패합니다. 따라서 {{ consumer_join_col | join_policy }} 는 consumer_join_col 로 전달된 값이 이 Clean Room에서 공급자와 컨슈머가 설정한 조인 정책을 준수하는지 확인합니다.

• 식별자로 사용되는 변수는 SQL 에서 사용하려면 IDENTIFIER 함수로 처리해야 합니다.

Clean Room에 템플릿을 추가합니다.

-- Add the template
SET template_name = 'overlap_template';
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    $template_name,
    $$
    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.provider.view_added_templates($cleanroom_name);

공급자: 열 정책 설정하기¶

클린룸의 각 당사자는 *column_policy*를 설정하여 다른 당사자가 프로젝션할 수 있는 열을 제한할 수 있습니다. 클린룸의 열 정책에는 프로젝션할 수 있는 데이터의 모든 열이 나열됩니다. 다른 열은 프로젝션할 수 없습니다. 데이터에 대한 열 정책을 지정하지 않으면 모든 데이터를 프로젝션할 수 있습니다.

열 정책은 클린룸의 특정 테이블 및 템플릿에 연결됩니다. 다른 템플릿에서 다른 열을 프로젝션하도록 허용할 수 있습니다. 동일한 열이 조인 정책과 열 정책 모두에 있을 수는 없습니다.

열 정책 및 조인 정책은 템플릿이 column_policy 및 row_policy 필터를 사용하는 경우에만 적용됩니다.

다음은 방금 만든 템플릿에서 3개의 데이터 열을 프로젝션하도록 하는 방법입니다. 열 구문은 template_name:table_name:column_name 입니다.

-- Set column policies. Column policies are tied to a specific template and table, so we
-- needed to add the template first.
CALL samooha_by_snowflake_local_db.provider.set_column_policy($cleanroom_name,
  [$template_name || ':cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:STATUS',
   $template_name || ':cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table:DAYS_ACTIVE']);

CALL samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);

공급자: 릴리스 지시문 추가하기¶

모든 클린룸에는 주, 부 및 패치 값으로 구성된 버전 번호 가 있습니다. 컨슈머에게 제공되는 클린룸 버전을 지정해야 하며, 이를 *기본 릴리스 지시문*이라고 합니다.

첫 번째 버전이므로 버전 번호는 1.0.0입니다.

CALL samooha_by_snowflake_local_db.provider.set_default_release_directive(
  $cleanroom_name,
  'V1_0',
  '0');

Snowflake는 클린룸에 코드를 업로드할 때마다 클린룸의 새 버전을 생성합니다. 사용자에게 최신 버전을 제공하려면 최신 버전 번호를 사용하여 새 기본 릴리스 지시문을 설정해야 합니다. 여기서는 코드를 업로드하지 않을 것이므로, 이 자습서에서는 다시 호출할 필요가 없습니다.

공급자: 컨슈머 지정¶

이제 컨슈머 역할로 클린룸에 액세스할 수 있는 사용자를 지정합니다. 이 자습서에서는 자신을 컨슈머로 추가합니다. 이렇게 하면 클린룸이 내부 테스트용 클린룸으로 표시되며 테스트용으로만 사용됩니다. 이 클린룸은 일부 기능이 제한 되지만 이 자습서에 필요한 모든 기능을 지원합니다.

이 프로시저에는 각 컨슈머를 식별하기 위한 다음과 같은 두 인자가 필요합니다.

• 컨슈머의 계정 로케이터입니다. 다음과 같이 계정 로케이터를 가져옵니다.

  SELECT CURRENT_ACCOUNT();
  

  Copy

• 컨슈머의 컨슈머 데이터 공유 계정ID 입니다(org_name.account_name 형식). 다음과 같은 적절한 형식으로 컨슈머 데이터 공유 계정 ID를 가져옵니다.

  SELECT CURRENT_ORGANIZATION_NAME() || '.' || CURRENT_ACCOUNT_NAME();
  

  Copy

이제 컨슈머 역할로 자신에게 클린룸을 공유하고 표시된 대로 계정 로케이터 및 컨슈머 데이터 공유 계정 ID를 추가합니다.

CALL samooha_by_snowflake_local_db.provider.add_consumers(
  $cleanroom_name,
  '<CONSUMER_LOCATOR>',
  '<CONSUMER_DATA_SHARING_ACCOUNT_ID>');

CALL samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);

공급자: 클린룸 게시¶

마지막으로, 클린룸을 게시합니다. 이렇게 하면 위에서 추가한 컨슈머가 클린룸을 사용할 수 있습니다. 이 프로시저를 완료하는 데 1분 이상 걸립니다.

-- Publish the clean room.
CALL samooha_by_snowflake_local_db.provider.create_or_update_cleanroom_listing(
  $cleanroom_name);

프로시저가 완료되면 “Powered by Dev Edition” 레이블이 포함된 클린룸이 클린룸 UI, 공급자 계정의 Created 탭 및 컨슈머 계정의 Invited 탭에 나열됩니다. 컨슈머 계정은 초대 이메일을 받습니다. (이후 단계에서 코드로 클린룸을 설치할 예정이므로 Invited 탭에서 클린룸을 설치하지 마세요.)

축하합니다. 첫 번째 Clean Room을 게시하셨습니다!

이제 공급자 역할에서 컨슈머 역할로 전환합니다.

컨슈머: Clean Room 설치하기(참여하기)¶

이 자습서에서는 공급자 및 컨슈머 역할에 동일한 계정을 사용하므로, 같은 계정의 Snowsight에서 “API Tutorial - Consumer”라는 이름의 새 SQL 워크시트를 추가합니다.

공급자 역할에서 수행한 방법과 유사한 방식으로 세션 환경을 설정합니다.

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

다음으로, 공급자 역할로 게시하고 공유했던 클린룸을 설치합니다. 클린룸을 설치하려면 클린룸 이름과 클린룸을 공유한 공급자의 계정 로케이터를 모두 지정해야 합니다. 클린룸 이름과 계정 로케이터를 지정하면 이름이 같은 클린룸을 명확하게 구분하는 데 도움이 됩니다. SELECT CURRENT_ACCOUNT(); 를 실행하여 공급자 로케이터를 가져옵니다.

SET cleanroom_name = 'Developer Tutorial';
CALL samooha_by_snowflake_local_db.consumer.install_cleanroom(
  $cleanroom_name,
  <PROVIDER_LOCATOR>);

설치하는 데 몇 분 정도 걸릴 수 있습니다.

컨슈머: 데이터 생성 및 연결하기¶

이제 데이터 세트를 만들어 이 클린룸에 추가합니다. 공급자의 데이터 세트와 유사한 테이블을 생성하지만, 샘플 데이터의 하위 1,000개 행을 사용합니다.

USE ROLE ACCOUNTADMIN;
CREATE DATABASE IF NOT EXISTS cleanroom_tut_db;
CREATE SCHEMA IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch;

CREATE TEMPORARY TABLE IF NOT EXISTS cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table AS
  SELECT
    TOP 1000 *
    FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS
  ORDER BY HASHED_EMAIL DESC;

DESCRIBE TABLE cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table;

원본 데이터를 생성한 후에는 공급자 역할로 했던 것처럼 등록하고 클린룸에 연결해야 합니다.

-- You need to use a role that has ownership of the object to be registered, probably not samooha_app_role.
USE ROLE ACCOUNTADMIN;
CALL samooha_by_snowflake_local_db.library.register_objects(
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table']);

-- Drop back down to samooha_app_role for the other actions.
USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.consumer.link_datasets(
  $cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table']);

CALL samooha_by_snowflake_local_db.consumer.view_consumer_datasets($cleanroom_name);

컨슈머: 정책을 설정할 필요 없음¶

공급자와 동일한 방식으로 데이터에 대한 정책을 설정할 수 있지만, 이 템플릿은 컨슈머만 실행할 수 있도록 승인되었으므로 자신의 쿼리를 제한할 필요가 없습니다. 어떤 경우든 쿼리를 실행할 때 자신의 행 정책 및 열 정책은 무시됩니다.

그러나 이 템플릿을 실행하려는 공급자 요청을 승인할 경우, 먼저 데이터에 조인 및 열 정책을 설정하여 공급자가 이 템플릿으로 수행할 수 있는 작업을 제어해야 합니다.

컨슈머: 분석 실행¶

쿼리를 실행하려면 다음 정보가 필요합니다.

• 실행하려는 템플릿의 이름입니다.

• 템플릿에서 사용할 테이블의 이름입니다.

• 템플릿에서 사용할 공급자 테이블의 이름입니다.

• 전달할 다른 모든 이름/값 변수입니다.

-- List templates in the clean room.
CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

-- See the template code.
SET template_name = 'overlap_template';
CALL samooha_by_snowflake_local_db.consumer.view_template_definition(
  $cleanroom_name,
  $template_name);

-- See what arguments can be passed in to the template:
CALL samooha_by_snowflake_local_db.consumer.get_arguments_from_template(
  $cleanroom_name,
  $template_name
);

-- Table name to use is in the LINKED_TABLE column in the results.
CALL samooha_by_snowflake_local_db.consumer.view_provider_datasets($cleanroom_name);

-- See which provider columns can be joined on.
CALL samooha_by_snowflake_local_db.consumer.view_provider_join_policy($cleanroom_name);

-- See which provider columns can be projected.
CALL samooha_by_snowflake_local_db.consumer.view_provider_column_policy($cleanroom_name);

CALL samooha_by_snowflake_local_db.consumer.run_analysis(
  $cleanroom_name,
  $template_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table'], -- Consumer table list.
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table'], -- Provider table list.
  OBJECT_CONSTRUCT(                    -- Additional template arguments as name-value pairs.
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.status'
  )
);

두 계정 모두 해당: 정리¶

이제 생성한 모든 리소스를 정리해 보겠습니다.

원본 테이블을 삭제하려면 원본 테이블을 만들 때 사용한 것과 동일한 역할을 사용해야 합니다.

USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.provider.drop_cleanroom($cleanroom_name);

USE role ACCOUNTADMIN;
DROP TABLE cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table;
DROP DATABASE cleanroom_tut_db;

USE ROLE samooha_app_role;
CALL samooha_by_snowflake_local_db.consumer.uninstall_cleanroom($cleanroom_name);

USE ROLE ACCOUNTADMIN;
DROP VIEW cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table;
DROP DATABASE cleanroom_tut_db;