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

소개¶

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

공급자: 개요¶

다음은 Clean Room을 만드는 데 필요한 단계를 요약한 것입니다.

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_SAMPLE_DATABASE.DEMO.CUSTOMERS 테이블에서 1,000행의 샘플 테스트 데이터를 기반으로 테이블을 만듭니다. 이 테이블을 Clean Room의 공급자 데이터로 사용하게 됩니다.

데이터베이스를 만들 수 있는 역할을 사용합니다. 이 예에서는 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 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 만들기¶

Clean Room은 데이터, 사용자, 기본 속성만 있는 상태로 시작됩니다.

다음 스니펫은 조직 내에서만 액세스할 수 있는 Clean Room을 만듭니다(따라서 INTERNAL 로 표시됨). 조직 외부에서 Clean Room을 공유하려면 이 자습서에서 다루지 않는 추가 단계가 필요합니다.

거의 모든 Clean Room 작업에는 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으로 데이터 가져오기¶

테스트 데이터를 Clean Room으로 가져와 보겠습니다.

데이터를 Clean Room으로 가져오는 데는 다음 두 가지 단계가 있습니다.

1. 데이터 등록하기

2. Clean Room으로 데이터 가져오기

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

-- Back to 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);

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

공급자와 컨슈머 모두 자신의 데이터에 조인 정책 을 지정할 수 있습니다. Clean Room 조인 정책은 해당 Clean Room에서 쿼리가 조인할 수 있는 테이블의 열을 지정합니다. 이를 통해 다른 사람이 Clean Room에서 데이터를 사용하는 방식을 더욱 세밀하게 제어할 수 있습니다.

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

Clean Room에 조인 가능한 열 목록을 제공합니다. 여기에 목록에 없는 열은 Clean Room에서 INNER JOIN 또는 OUTER JOIN 문을 사용하여 조인할 수 없습니다. 각 열에 대해 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);

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

Clean Room의 각 당사자는 column_policy 를 설정하여 다른 당사자가 결과에 표시할 수 있는 열을 제한할 수 있습니다. Clean Room의 열 정책에는 투영할 수 있는 모든 열이 나열되며, 다른 열은 투영할 수 없습니다. 공급자는 공급자의 테이블에 대한 열 정책을 설정하고, 컨슈머는 컨슈머의 테이블에 대한 열 정책을 설정합니다.

열 정책은 Clean Room의 특정 테이블 및 템플릿에 연계됩니다. 서로 다른 템플릿에 서로 다른 열을 표시하도록 허용할 수 있습니다. 동일한 열은 조인 및 열 정책에 모두 포함될 수 없습니다.

열 및 조인 정책은 템플릿에서 column_policy 및 row_policy 필터를 사용하는 경우에만 적용된다는 점에 유의하십시오. 또한 열 정책을 지정하지 않으면 모든 열이 투영될 수 있으며, 마찬가지로 조인 정책을 지정하지 않으면 모든 열이 조인될 수 있음을 의미합니다.

방금 만든 템플릿에서 데이터의 세 열을 투영할 수 있도록 허용하는 방법은 다음과 같습니다. 열 구문은 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:AGE_BAND',
   $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);

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

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

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

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

Snowflake는 Clean Room에 코드를 업로드할 때마다 Clean Room의 새 버전을 생성하며, 사용자가 최신 버전을 받으려면 새 버전으로 이 프로시저를 다시 호출해야 합니다. 코드를 업로드하지 않으므로 다시 호출할 필요가 없습니다.

공급자: 컨슈머 추가하기¶

이제 Clean Room을 컨슈머로 사용할 수 있는 계정을 지정하십시오. Clean Room을 공유할 때 몇 가지 요구 사항이 있습니다.

• 사용자는 Clean Room 환경이 설치된 Snowflake 계정이어야 합니다. Snowflake 계정이 아닌 다른 계정과 Clean Room을 공유할 수는 있지만 여기서는 다루지 않습니다.

• 컨슈머 계정은 공급자 계정과 동일한 클라우드 리전이어야 합니다. 리전 간에 공유할 수 있지만 여기서는 다루지 않는 추가 구성이 필요합니다.

• 공급자 계정의 Collaborators 페이지에 컨슈머 계정을 추가해야 합니다. 계정의 공동 작업자 목록에 추가된 계정만 해당 계정에서 만든 Clean Room에 참여하도록 초대를 받을 수 있습니다.

• 계정 로케이터는 컨슈머 계정에서 SELECT CURRENT_ACCOUNT(); 프로시저를 실행하여 얻을 수 있습니다.

• 계정 이름은 org_name.account_name 형식으로 되어 있습니다. 컨슈머 계정에서 다음 프로시저를 실행하여 이러한 값을 얻을 수 있습니다.

  • SELECT CURRENT_ORGANIZATION_NAME();

  • SELECT CURRENT_ACCOUNT_NAME();

CALL samooha_by_snowflake_local_db.provider.add_consumers(
  $cleanroom_name,
  <CONSUMER_LOCATOR>,
  <ORG_NAME>.<ACCOUNT_NAME>);

CALL samooha_by_snowflake_local_db.provider.view_consumers($cleanroom_name);

공급자: Clean Room 게시하기¶

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

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

프로시저가 완료되면 공급자 계정에서 Clean Rooms 웹 앱의 Created 탭과 컨슈머 계정에서 웹 앱의 Invited 탭에 Clean Rooms가 ‘Powered by Dev Edition’이라는 레이블과 함께 목록에 표시됩니다. 컨슈머 계정에 초대 이메일이 전송되며 컨슈머 계정의 웹 앱에 있는 Invited 탭에 Clean Room이 표시됩니다.

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

이제 Clean Room을 사용하려면 컨슈머 계정으로 이동하십시오.

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

Snowsight에서 컨슈머 계정으로 전환합니다. 브라우저에서 두 번째 탭을 열거나 계정 전환기 를 사용할 수 있습니다.

컨슈머 계정으로 로그인한 후 컨슈머 노트북에서 환경을 설정합니다.

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

방금 게시하고 공유한 Clean Room을 설치합니다.

Clean Room을 설치하려면 Clean Room 이름과 Clean Room을 공유한 공급자의 계정 로케이터를 모두 지정해야 합니다. Clean Room 이름과 계정 로케이터를 지정하면 여러 개의 Clean Room 초대를 명확하게 구분하는 데 도움이 됩니다. 공급자 계정에서 SELECT CURRENT_ACCOUNT(); 를 실행하여 공급자 로케이터를 확인할 수 있습니다.

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

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

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

이제 이 Clean Room에 공급자의 데이터 세트와 유사하지만 샘플 데이터의 맨 아래 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 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;

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

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

컨슈머: 데이터에 대한 조인 정책 설정하기¶

이제 공급자 계정에서와 마찬가지로 데이터에 대한 조인 정책을 설정할 수 있습니다. 이 Clean Room에서는 컨슈머만 템플릿을 실행할 수 있으므로 이 예제에서는 컨슈머 조인 정책을 설정하는 것이 중복됩니다. 템플릿을 직접 실행하고 있으므로 어떤 열을 조인할 수 있는지 알 수 있습니다. 그러나 실제 사용 시에는 Clean Room에서 데이터 공급자가 템플릿을 실행할 수 있도록 지원하는 경우 데이터에 대한 조인 정책을 설정하는 것이 좋습니다.

-- Allow same three columns in your data to be joined.
CALL samooha_by_snowflake_local_db.consumer.set_join_policy($cleanroom_name,
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table:age_band',
  'cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table:region_code',
  'cleanroom_tut_db.cleanroom_tut_sch.demo_consumer_table:device_type']);

컨슈머: 쿼리 준비하기¶

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

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

• 템플릿에 가용성을 부여할 테이블의 이름입니다.

• 템플릿에 사용할 수 있도록 하기 위한 공급자의 테이블 이름입니다.

• 전달할 다른 이름/값 변수입니다. 템플릿에서는 공급자 및 컨슈머 테이블의 조인 열 이름을 전달해야 합니다.

-- List templates in the clean room, then examine the template details
CALL samooha_by_snowflake_local_db.consumer.view_added_templates($cleanroom_name);

SET template_name = 'overlap_template';
CALL samooha_by_snowflake_local_db.consumer.view_template_definition($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'],
  ['cleanroom_tut_db.cleanroom_tut_sch.demo_provider_table'],
  OBJECT_CONSTRUCT(
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.status'
  ),
  FALSE
);

두 계정 모두 해당: 정리¶

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

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

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;