자유 형식 SQL 쿼리예요

자유 형식 SQL 쿼리예요

기능 — 일반 제공(Generally Available)

현재 이들 리전에서 사용할 수 있어요.

정부 및 VPS 배포 환경에서는 사용할 수 없어요.

데이터 제공자는 템플릿 또는 자유 형식 쿼리를 통해 자신의 데이터가 분석 실행자에게 노출되도록 허용할 수 있어요. 데이터 제공자가 데이터셋에서 자유 형식 쿼리를 활성화하면, 데이터 오퍼링에 접근 권한이 있는 모든 분석 실행자는 자신의 환경에서 해당 데이터셋에 대해 SQL 쿼리를 실행할 수 있어요.

데이터가 사용 가능해지기 전에 분석 실행자와 데이터 제공자 모두 협업에 참여하고 있어야 해요.

출처: 문서

본문

개요

Clean Room의 데이터에 대해 자유 형식 쿼리를 실행하는 단계는 다음과 같습니다.

데이터 제공자

allowed_analyses: template_and_freeform_sql이 지정된 데이터 세트를 하나 이상 포함하는 데이터 오퍼링을 등록합니다.

데이터 제공자가 데이터 세트의 열에 Snowflake 정책을 적용하려면 데이터를 등록하기 전에 해당 정책을 만들고 데이터 오퍼링 사양의 열에 정책을 연결해야 합니다.

데이터 오퍼링을 표준 방식으로 collaboration에 연결합니다.

분석 실행자

collaboration이 해당 계정에 설치된 후 분석 실행자는 VIEW_DATA_OFFERINGS를 호출합니다. freeform_sql_view_name 열에 값이 있으면 해당 열에 지정된 뷰를 대상으로 데이터 세트를 직접 쿼리할 수 있습니다.

freeform_sql_column_policies에 나열된 모든 정책은 collaboration에 의해 데이터에 적용됩니다. 데이터 제공자가 원본 데이터에 직접 적용한 정책은 적용되지만 해당 열에는 표시되지 않습니다.

데이터 제공자 및 분석 단계에 대한 자세한 내용은 다음 섹션에서 설명합니다.

자유 형식 쿼리 데이터 세트 등록 (데이터 제공자)

다음 단계에서는 데이터 오퍼링 등록 중에 자유 형식 쿼리를 활성화하는 방법을 보여줍니다.

collaboration 사양에 allowed_analyses: template_and_freeform_sql을 지정합니다. 이렇게 하면 템플릿 또는 자유 형식 쿼리를 사용하여 데이터 세트를 쿼리할 수 있습니다.

...
datasets:
- alias: customers_view
  data_object_fqn: PROVIDER_DB.DATA_SCH.CUSTOMERS
  object_class: custom
  allowed_analyses: template_and_freeform_sql
  schema_and_template_policies:
    HASHED_EMAIL:
      category: join_standard
      column_type: hashed_email_b64_encoded
...

schema_and_template_policies 아래에 나열된 열만 템플릿 또는 자유 형식 쿼리를 통해 쿼리할 수 있습니다.

원본 데이터에 적용하지 않고 자유 형식 쿼리에서 Snowflake 정책을 적용하려면 다음 단계를 수행합니다.

Snowflake 정책을 표준 방식으로 생성합니다. 테이블에는 적용하지 마세요.

CREATE OR REPLACE AGGREGATION POLICY PROVIDER_DB.DATA_SCH.MIN_GROUP_SIZE_POLICY
  AS () RETURNS AGGREGATION_CONSTRAINT ->
    AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

collaboration을 생성하는 역할에는 데이터베이스, 스키마 및 정책 개체에 대한 USAGE 권한이 있어야 합니다.

이러한 정책은 동적으로 연결됩니다. 정책을 변경하면 해당 정책을 사용하는 모든 데이터 세트에 즉시 영향을 미치며, 데이터 오퍼링이 이미 등록되고 연결된 경우에도 마찬가지입니다.

데이터 오퍼링 사양의 freeform_sql_policies 필드 아래에 정책을 할당합니다. 중요: freeform_sql_policies 아래에 사용된 모든 열 이름은 열 이름이 변경된 경우 auto-generated column name을 사용해야 합니다. 이름 변경은 조인 표준 범주 열에만 영향을 미칩니다.

이러한 정책은 원본 테이블에 직접 적용되지 않으며 collaboration에 의해 등록된 뷰에만 적용됩니다.

schema_and_template_policies:
  HASHED_EMAIL:                                  # Source column name.
    category: join_standard
    column_type: hashed_email_b64_encoded        # Column is renamed to the column_type value.
  STATUS:
    category: passthrough
  AGE_BAND:
    category: passthrough
  DAYS_ACTIVE:
    category: passthrough
  INCOME_BRACKET:
    category: passthrough
freeform_sql_policies:          # Apply agg, join, and masking policies created by the data owner to these columns.
  aggregation_policy:
    name: PROVIDER_DB.DATA_SCH.MIN_GROUP_SIZE_POLICY
    entity_keys:
      - HASHED_EMAIL_B64_ENCODED
  join_policy:
    name: PROVIDER_DB.DATA_SCH.EMAIL_JOIN_POLICY
    columns:
      - HASHED_EMAIL_B64_ENCODED    # This is the renamed column.
  masking_policies:
    - name: PROVIDER_DB.DATA_SCH.MASK_INCOME_POLICY
      columns:
        - INCOME_BRACKET

REGISTER_DATA_OFFERING을 호출하여 데이터 오퍼링을 표준 방식으로 등록합니다.

자유 형식 쿼리 실행 (분석 실행자)

분석 실행자가 VIEW_DATA_OFFERINGS를 호출할 때 freeform_sql_view_name 열에 값이 나타나면 템플릿을 사용하지 않고 자유 형식 SQL 뷰를 직접 쿼리할 수 있습니다. 원본 테이블에 적용된 모든 Snowflake 정책 또는 data offering’s freeform_sql_policies 섹션에 정의된 모든 Snowflake 정책이 쿼리에서 적용됩니다.

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS($collaboration_name);

``````****``

{
  "aggregation_policy": {"entity_keys": ["HASHED_EMAIL_B64_ENCODED"]},
  "masking_policy": {"columns": ["INCOME_BRACKET"]},
  "join_policy": {"columns": ["HASHED_EMAIL_B64_ENCODED"]},
  "no_policy": {"columns": ["DAYS_ACTIVE", "AGE_BAND", "STATUS"]}
}

| 열 | 값 |
|---|---|
| TEMPLATE_VIEW_NAME | data_provider.provider_customers_V1.customers |
| TEMPLATE_JOIN_COLUMNS | hashed_email_b64_encoded |
| ANALYSIS_ALLOWED_COLUMNS | STATUS, AGE_BAND, DAYS_ACTIVE, INCOME_BRACKET |
| ACTIVATION_ALLOWED_COLUMNS |  |
| FREEFORM_SQL_VIEW_NAME | SFDCR_FREEFORM_SQL_DEMO.FREEFORM_SQL.DATA_PROVIDER_PROVIDER_CUSTOMERS_V1_CUSTOMERS |
| FREEFORM_SQL_COLUMN_POLICIES |  |
| SHARED_BY | data_provider |
| SHARED_WITH | ["data_consumer"] |
| DATA_OFFERING_ID | provider_customers_V1 |

`freeform_sql_view_name`의 값을 사용해야 하며, `template_view_name`의 값을 사용하면 안 됩니다.

```
SELECT status, COUNT(*) AS customer_count
  FROM SFDCR_FREEFORM_SQL_DEMO.FREEFORM_SQL.DATA_PROVIDER_PROVIDER_CUSTOMERS_V1_CUSTOMERS AS t
  GROUP BY status
  ORDER BY customer_count DESC;
```

## 예: 양자 간 collaboration

다음 예에서는 양자 간 collaboration을 보여줍니다. 한 당사자(“제공자”)는 collaboration 소유자이자 소비자를 위한 데이터 제공자입니다. 다른 당사자(“소비자”)는 분석 실행자로서 템플릿을 실행하고 제공자가 제공한 데이터를 사용할 수 있으며, 데이터 제공자 사양에 정의된 정책에 따라 데이터에 대해 자유 형식 SQL 쿼리도 실행할 수 있습니다.

이 예를 실행하려면 Snowflake Data Clean Rooms가 설치된 두 개의 별도 계정이 있어야 합니다.

파일을 다운로드하여 Snowflake 계정에 업로드하거나, Snowsight를 사용하여 예제 코드를 복사하여 두 개의 별도 계정에 있는 워크시트에 붙여넣을 수 있습니다.

소스 SQL 파일을 다운로드한 다음 Snowflake Data Clean Rooms가 설치된 두 개의 별도 계정에 업로드합니다:

- [Collaboration owner and data provider worksheet](https://docs.snowflake.com/static/samples/clean-rooms/collab-hub-freeform-sql-provider.sql)

- [Collaboration query runner worksheet](https://docs.snowflake.com/static/samples/clean-rooms/collab-hub-freeform-sql-consumer.sql)

```
-- ============================================================================
-- Free-form SQL Collaboration Demo: Data Provider
-- ============================================================================
-- This example demonstrates a Snowflake Data Clean Rooms collaboration using
-- freeform SQL policies. The data provider creates a sample dataset with
-- Snowflake aggregation, join, and masking policies, registers a data offering
-- that permits freeform SQL queries, creates a template, and initializes a
-- collaboration with one other collaborator (data_consumer).
--
-- For more information, see:
--   docs.snowflake.com/user-guide/cleanrooms/free-form-sql.rst
--   docs.snowflake.com/user-guide/cleanrooms/spec-reference
-- ============================================================================

-- ============================================================================
-- SETUP: Create sample database, schema, table, and policies.
-- ============================================================================

USE ROLE SAMOOHA_APP_ROLE;
USE WAREHOUSE APP_WH;

-- You can't use secondary roles with most collaboration procedures.
USE SECONDARY ROLES NONE;

CREATE DATABASE IF NOT EXISTS PROVIDER_DB;
CREATE SCHEMA IF NOT EXISTS PROVIDER_DB.DATA_SCH;

-- Create a table with 300 rows from the sample CUSTOMERS table.
CREATE OR REPLACE TABLE PROVIDER_DB.DATA_SCH.CUSTOMERS AS
  SELECT HASHED_EMAIL, STATUS, AGE_BAND, REGION_CODE, DAYS_ACTIVE, INCOME_BRACKET
  FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS
  LIMIT 300;

-- Create an aggregation policy that requires a minimum group size of 5.
CREATE OR REPLACE AGGREGATION POLICY PROVIDER_DB.DATA_SCH.MIN_GROUP_SIZE_POLICY
  AS () RETURNS AGGREGATION_CONSTRAINT ->
    AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

-- Create an inactive join policy. You will modify this later.
CREATE OR REPLACE JOIN POLICY PROVIDER_DB.DATA_SCH.EMAIL_JOIN_POLICY
  AS () RETURNS JOIN_CONSTRAINT ->
    JOIN_CONSTRAINT(JOIN_REQUIRED => FALSE);

-- Create a masking policy that replaces the original value with a fixed string.
CREATE OR REPLACE MASKING POLICY PROVIDER_DB.DATA_SCH.MASK_INCOME_POLICY
  AS (val STRING) RETURNS STRING ->
    '***MASKED***';

-- ============================================================================
-- Register a data offering with freeform SQL policies.
-- ============================================================================

-- The data offering enables freeform SQL queries (template_and_freeform_sql)
-- and attaches three Snowflake policies to protect data in freeform queries:
--   * Aggregation policy on hashed_email: enforces a minimum group size of 5.
--   * Join policy on hashed_email: requires joins to include this column.
--   * Masking policy on income_bracket: masks the column value in query results.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_DATA_OFFERING(
  $$
  api_version: 2.0.0
  spec_type: data_offering
  version: V1
  name: provider_customers
  description: Customer dataset with freeform SQL policies.
  datasets:
    - alias: customers
      data_object_fqn: PROVIDER_DB.DATA_SCH.CUSTOMERS
      object_class: custom
      allowed_analyses: template_and_freeform_sql
      schema_and_template_policies:
        HASHED_EMAIL:
          category: join_standard
          column_type: hashed_email_b64_encoded
        STATUS:
          category: passthrough
        AGE_BAND:
          category: passthrough
        DAYS_ACTIVE:
          category: passthrough
        INCOME_BRACKET:
          category: passthrough
      freeform_sql_policies:
        aggregation_policy:
          name: PROVIDER_DB.DATA_SCH.MIN_GROUP_SIZE_POLICY
          entity_keys:
            - HASHED_EMAIL_B64_ENCODED
        join_policy:
          name: PROVIDER_DB.DATA_SCH.EMAIL_JOIN_POLICY
          columns:
            - HASHED_EMAIL_B64_ENCODED
        masking_policies:
          - name: PROVIDER_DB.DATA_SCH.MASK_INCOME_POLICY
            columns:
              - INCOME_BRACKET
  $$
);

-- Save the data offering ID returned by the registration call.
SET data_offering_id = '<data_offering_id>';

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_DATA_OFFERINGS();

-- ============================================================================
-- Register a template with a simple one-table query.
-- ============================================================================

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.REGISTER_TEMPLATE(
  $$
  api_version: 2.0.0
  spec_type: template
  name: status_summary
  version: V1
  type: sql_analysis
  description: Returns a count of customers grouped by status.
  template:
    SELECT status, COUNT(*) AS customer_count
      FROM IDENTIFIER({{ source_table[0] }})
      GROUP BY status
      ORDER BY customer_count DESC;
  $$
);

-- Save the template ID returned by the registration call.
SET template_id = '<template_id>';

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.REGISTRY.VIEW_REGISTERED_TEMPLATES();

-- ============================================================================
-- Create the collaboration.
-- ============================================================================

-- Replace the <...> placeholders with the appropriate values.
-- Get your account data sharing ID with:
--   SELECT CURRENT_ORGANIZATION_NAME() || '.' || CURRENT_ACCOUNT_NAME();
-- In this collaboration, the consumer can run templated and free-form queries
-- against the provider's data. The provider/owner isn't an analysis runner.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.INITIALIZE(
  $$
  api_version: 2.0.0
  spec_type: collaboration
  name: freeform_sql_demo
  owner: data_provider
  collaborator_identifier_aliases:
    data_provider: <provider_account_data_sharing_id>
    data_consumer: <consumer_account_data_sharing_id>
  analysis_runners:
    data_consumer:
      data_providers:
        data_provider:
          data_offerings:
            - id: <data_offering_id>
      templates:
        - id: <template_id>
  $$,
  'APP_WH'
);

SET collaboration_name = 'freeform_sql_demo';

-- INITIALIZE automatically joins the owner. Repeat until status is JOINED.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

-- Verify that the collaboration is visible.
-- Collaboration spec is in COLLABORATION_SPEC column.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_COLLABORATIONS() ->>
  SELECT * FROM $1 WHERE "SOURCE_NAME" = $collaboration_name;

-- SWITCH TO data_consumer account to join and run analyses.

-- Update the join policy associated with HASHED_EMAIL_B64_ENCODED.
-- All queries on that data offering now require joins on HASHED_EMAIL_B64_ENCODED.
-- Re-run any of the previously successful free-form queries and they will fail.
ALTER JOIN POLICY PROVIDER_DB.DATA_SCH.EMAIL_JOIN_POLICY SET BODY ->
  JOIN_CONSTRAINT(JOIN_REQUIRED => TRUE);

-- ============================================================================
-- CLEANUP: Delete the collaboration, registered resources, and sample data.
-- ============================================================================

-- Teardown is a multi-step process. Call TEARDOWN, then wait for GET_STATUS
-- to report LOCAL_DROP_PENDING, then call TEARDOWN again.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.TEARDOWN($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

-- When GET_STATUS reports LOCAL_DROP_PENDING, call TEARDOWN again to complete.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.TEARDOWN($collaboration_name);
```

```
-- ============================================================================
-- Free-form SQL Collaboration Demo: Data Consumer
-- ============================================================================
-- This example demonstrates joining a Snowflake Data Clean Rooms collaboration
-- as an analysis runner. The data consumer joins a collaboration created by
-- the data provider, views available templates and data offerings, runs an
-- analysis using the provider's template, and then runs several free-form SQL
-- queries directly against the data-offering views.
--
-- The data offering in this collaboration has three free-form SQL policies:
--   * Aggregation policy (hashed_email): minimum group size of 5.
--   * Join policy (hashed_email): joins must include this column. Currently inactive.
--   * Masking policy (income_bracket): values are replaced with '***MASKED***'.
--
-- For more information, see:
--   docs.snowflake.com/user-guide/cleanrooms/free-form-sql.rst
--   docs.snowflake.com/user-guide/cleanrooms/spec-reference
-- ============================================================================

-- ============================================================================
-- Join the collaboration
-- ============================================================================

USE ROLE SAMOOHA_APP_ROLE;
USE WAREHOUSE APP_WH;

-- You can't use secondary roles with most collaboration procedures.
USE SECONDARY ROLES NONE;

-- View available collaborations. Look for the collaboration created by the data provider.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_COLLABORATIONS();

-- Use the SOURCE_NAME column value from the response to VIEW_COLLABORATIONS().
SET collaboration_name = 'freeform_sql_demo';

-- Use the OWNER_ACCOUNT column value from the response to VIEW_COLLABORATIONS().
SET collaborator_data_sharing_id = '<provider_data_sharing_id>';

-- Review the collaboration spec before joining.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.REVIEW($collaboration_name, $collaborator_data_sharing_id);

-- Join the collaboration. Joining is asynchronous; call GET_STATUS until JOINED.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.JOIN($collaboration_name);
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.GET_STATUS($collaboration_name);

-- ============================================================================
-- View available templates and data offerings
-- ============================================================================

-- View data offerings shared with you in this collaboration.
-- Set a variable to use in future queries.
-- Note that the view name used by templates != the view name used for free-form SQL queries.
-- Templates use the TEMPLATE_VIEW_NAME value.
-- Free-form queries use the FREEFORM_SQL_VIEW_NAME value.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_DATA_OFFERINGS($collaboration_name);
SET template_view_name = '<template_view_name>';
SET freeform_view_name = '<freeform_view_name>';

-- View templates available to you in this collaboration.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.VIEW_TEMPLATES($collaboration_name);

-- ============================================================================
-- Run an analysis using the provider's template
-- ============================================================================

-- Replace the placeholders with the template name/version from VIEW_TEMPLATES
-- and the view name from VIEW_DATA_OFFERINGS.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.COLLABORATION.RUN(
  $collaboration_name,
  $$
  api_version: 2.0.0
  spec_type: analysis
  description: Count customers grouped by status.
  template: '<status_summary_template_name_and_version>'
  template_configuration:
    view_mappings:
      source_tables:
        - '<template_view_name>'
  $$
);

-- ============================================================================
-- Free-form SQL queries: Queries that SUCCEED
-- ============================================================================

-- The following queries run directly against the data-offering view.

-- Query 1: Count customers grouped by status.
-- Succeeds because the aggregation produces groups larger than 5.
SELECT status, COUNT(*) AS customer_count
  FROM IDENTIFIER( $freeform_view_name ) AS t
  GROUP BY status
  ORDER BY customer_count DESC;

-- Query 2: Count customers grouped by age_band.
-- Succeeds because the aggregation produces groups larger than 5.
SELECT age_band, COUNT(*) AS customer_count
  FROM IDENTIFIER( $freeform_view_name ) AS t
  GROUP BY age_band
  ORDER BY age_band;

-- Query 3: Select income_bracket to demonstrate the masking policy.
-- The query succeeds, but income_bracket values are replaced with '***MASKED***'
-- because the masking policy is applied to this column.
SELECT income_bracket, COUNT(*) AS customer_count
  FROM IDENTIFIER( $freeform_view_name ) AS t
  GROUP BY income_bracket;

-- Query 4: Combine masked and unmasked columns.
-- income_bracket is masked; status and age_band are not.
SELECT status, age_band, income_bracket, COUNT(*) AS customer_count
  FROM IDENTIFIER( $freeform_view_name ) AS t
  GROUP BY status, age_band, income_bracket
  ORDER BY customer_count DESC;

-- Query 5: Group by a high-cardinality column.
-- Succeeds, but shows no values for hashed_email_b64_encoded because
-- grouping by hashed_email_b64_encoded produces groups of 1.
SELECT hashed_email_b64_encoded, COUNT(*) AS row_count
  FROM IDENTIFIER( $freeform_view_name ) AS t
  GROUP BY hashed_email_b64_encoded;

-- ============================================================================
-- Free-form SQL queries: Queries that FAIL
-- ============================================================================

-- Query 6: Select individual rows without aggregation.
-- FAILS because the aggregation policy requires a minimum group size of 5.
SELECT hashed_email_b64_encoded, status, age_band
  FROM IDENTIFIER( $freeform_view_name ) AS t
  LIMIT 10;

-- Query 8: Select a column not listed in the data offering.
-- FAILS because region_code is not included in schema_and_template_policies,
-- so it is not exposed in the data-offering view, although it is present in the source data.
SELECT region_code, COUNT(*) AS customer_count
  FROM IDENTIFIER( $freeform_view_name ) AS t
  GROUP BY region_code;

-- SWITCH TO provider account, update the JOIN policy, and re-run the successful
-- queries, which will now fail.
```
---

## 더 알아보기 (Learn more)

- [these regions](https://docs.snowflake.com/user-guide/cleanrooms/installing-dcr#label-dcr-supported-regions)
- [auto-generated column name](https://docs.snowflake.com/user-guide/cleanrooms/resources-data-offerings#label-dcr-source-column-renaming)
- [data offering’s](https://docs.snowflake.com/user-guide/cleanrooms/spec-data-offering#label-dcr-collaboration-data-yaml)
- [Collaboration owner and data provider worksheet](https://docs.snowflake.com/static/samples/clean-rooms/collab-hub-freeform-sql-provider.sql)
- [Collaboration query runner worksheet](https://docs.snowflake.com/static/samples/clean-rooms/collab-hub-freeform-sql-consumer.sql)