Tutorial: como começar a usar o Snowflake Data Clean Rooms no código¶

Introdução¶

Este tutorial é destinado a desenvolvedores que criarão ou usarão o Snowflake Data Clean Rooms no código. Este tutorial usa código SQL, mas você pode adaptar as informações mostradas aqui para criar e usar clean rooms em qualquer linguagem de codificação compatível com o Snowflake.

Provedor: visão geral¶

Aqui está um resumo das etapas que você deve seguir para criar uma clean room:

1. Crie dados de teste para compartilhar em sua clean room.

2. Crie sua clean room.

3. Leve os dados que você criou para a clean room.

4. Defina permissões de junção em seus dados para especificar quais colunas podem ser unidas quando consultadas pelos consumidores.

5. Crie um modelo para sua clean room. Um modelo de clean room é gravado em JinjaSQL e é avaliado como uma consulta SQL em tempo de execução. A maioria dos modelos inclui variáveis que permitem que os colaboradores especifiquem nomes de tabela e coluna, condições da cláusula WHERE e muito mais, em tempo de execução. Um colaborador de clean room escolhe e executa um modelo em uma clean room.

6. Especifique a versão padrão da clean room.

7. Adicione os consumidores que podem acessar sua clean room. Neste tutorial, os consumidores devem ser usuários Snowflake com contas aprovadas pelo administrador da clean room.

8. Publique a clean room para disponibilizá-la aos seus consumidores convidados.

Provedor: crie dados de teste¶

Comece a trabalhar em seu notebook de conta de provedor.

Primeiro, você criará uma tabela com base em mil linhas de dados de teste de amostra da tabela SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS. Você usará essa tabela como os dados do provedor na clean room.

Use uma função que permita que você crie um banco de dados. O exemplo aqui usa ACCOUNTADMIN, mas você pode usar qualquer função que permita a criação de uma tabela.

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;

Provedor: criação da clean room¶

Uma clean room começa sem dados, sem usuários e apenas com as propriedades padrão.

O trecho de código a seguir cria uma clean room que pode ser acessada somente dentro da organização (portanto, está marcada como INTERNAL). Para compartilhar uma clean room fora de uma organização, são necessárias etapas adicionais que não serão abordadas neste tutorial.

Você deve usar SAMOOHA_APP_ROLE para quase todas as ações de clean room.

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

Provedor: como trazer os dados para a clean room¶

Vamos levar seus dados de teste para a clean room.

Há duas etapas para levar os dados para a clean room:

1. Registro dos dados

2. Importação dos dados para a 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);

Provedor: definição das políticas de junção dos dados¶

Tanto os provedores quanto consumidores podem especificar políticas de junção em seus próprios dados. Uma política de junção de clean room especifica quais colunas em uma tabela podem ser unidas por consultas nessa clean room. Isso proporciona um nível extra de controle sobre como outras pessoas podem usar seus dados na clean room.

Observe que as políticas de junção de clean room não são iguais às políticas de junção Snowflake; as políticas de clean room especificam quais colunas podem ser unidas; as políticas de junção Snowflake especificam quais colunas não podem ser unidas.

Forneça uma lista de colunas juntáveis à clean room. As colunas não listadas aqui não podem ser unidas usando as instruções INNER JOIN ou OUTER JOIN na clean room. Especifique colunas associáveis para uma tabela usando o formato database_name.schema_name.table_or_view_name:column_name para cada coluna.

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

Provedor: como adicionar seu modelo¶

Um modelo de clean room é um modelo JinjaSQL avaliado em uma consulta SELECT. Essa consulta tem acesso a todos os conjuntos de dados vinculados à clean room, sujeitos às políticas de junção e coluna.

Este tutorial não abordará os detalhes do design de um modelo JinjaSQL, mas aqui está a consulta SQL que você está tentando implementar:

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;

A consulta simplesmente une uma tabela de provedor e uma de consumidor em uma coluna de junção especificada, agrupa por uma coluna de agrupamento especificada e projeta o valor do grupo e a contagem de cada grupo. Essa é a consulta que será executada na clean room quando o usuário executar o modelo.

Aqui está o modelo JinjaSQL para a mesma consulta, com variáveis adicionadas onde o consumidor pode especificar tabelas ou colunas. Depois que o consumidor especificar as variáveis, ele avaliará uma consulta SQL semelhante à anterior, mas com os nomes de tabela e coluna fornecidos pelo consumidor.

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

Algumas observações sobre o modelo:

• O conteúdo entre {{brackets}} são variáveis nomeadas passadas pelo consumidor quando ele executa o modelo. As seguintes variáveis são passadas pelo consumidor: group_by_col, consumer_join_col e provider_join_col

• As matrizes my_table e source_table são variáveis globais criadas pelo sistema, preenchidas com nomes de tabela de consumidor e provedor passados pelo chamador. Essas tabelas devem ser conectadas à clean room pelo consumidor e provedor.

• Todas as tabelas do provedor devem ter o alias p na consulta. Todas as tabelas de consumidor devem ter o alias c. Se você usar várias tabelas, crie um alias com um sufixo baseado em 1, assim: p, p1, p2, p3 e assim por diante para tabelas de provedor, e c, c1, c2, c3 e assim por diante para aliases de tabela de consumidor. (p e p0 são equivalentes)

• O Snowflake Data Clean Rooms oferece suporte à alguns filtros JinjaSQL personalizados que atuam sobre variáveis. Os filtros column_policy e row_policy verificam se as colunas às quais são aplicados estão em conformidade com as políticas de coluna e linha dessa clean room, caso contrário, a solicitação para executar o modelo falhará. Portanto, {{ consumer_join_col | join_policy }} verifica se o valor passado para consumer_join_col está em conformidade com as políticas de associação definidas pelo provedor e consumidor nessa clean room.

• As variáveis usadas como identificadores devem ser processadas pela função IDENTIFIER antes de poderem ser usadas em SQL.

Adicione o modelo à 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);

Provedor: definição de políticas de coluna¶

Cada parte da clean room pode limitar as colunas que as outras partes podem exibir nos resultados, definindo uma column_policy. Uma política de colunas em uma clean room lista todas as colunas que podem ser projetadas; nenhuma outra coluna pode ser projetada. O provedor define as políticas de coluna para suas tabelas; o consumidor define as políticas de coluna para suas tabelas.

Uma política de coluna está vinculada a uma tabela e a um modelo específicos em uma clean room. Você pode permitir que colunas diferentes sejam exibidas em modelos diferentes. A mesma coluna não pode estar em uma junção e em uma política de coluna.

Observe que as políticas de coluna e junção são aplicadas somente se o modelo usar os filtros column_policy e row_policy no modelo. Além disso, se você não especificar uma política de coluna, isso significa que todas as suas colunas podem ser projetadas; da mesma forma, não especificar uma política de junção significa que todas as suas colunas podem ser unidas.

Veja como permitir a projeção de três colunas de seus dados no modelo que acabamos de criar. A sintaxe da coluna é 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);

Provedor: como adicionar uma diretiva de lançamento¶

Cada clean room tem um número de versão, que consiste em valores principais, secundários e de correção. Você precisa especificar qual versão da clean room é fornecida a um consumidor: isso é chamado de diretiva de lançamento padrão.

Esta é a primeira versão, portanto, o número da versão é 1.0.0.

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

O Snowflake cria uma nova versão da clean room toda vez que o usuário faz upload de código para a clean room e, se quiser que os usuários obtenham a versão mais recente, precisará chamar esse procedimento novamente com a nova versão, se quiser que os usuários obtenham a versão mais recente. Você não fará upload de código, portanto, não precisará chamar isso novamente.

Provedor: como adicionar consumidores¶

Agora, especifique quais contas podem usar sua clean room como consumidores. Alguns requisitos para compartilhar uma clean room:

• Os usuários devem estar em uma conta Snowflake com o ambiente de clean room instalado. É possível compartilhar uma clean room com uma conta que não seja do Snowflake, mas isso não será abordado aqui.

• A conta de consumidor deve estar na mesma região de nuvem que a conta de provedor. Você pode compartilhar entre regiões, mas isso requer uma configuração extra que não será abordada aqui.

• A conta de consumidor deve ser adicionada à página Collaborators na conta de provedor. Somente as contas adicionadas à lista de colaboradores de uma conta podem receber um convite para participar de uma clean room criada nessa conta.

• O localizador de contas pode ser obtido executando o seguinte procedimento em sua conta de consumidor: SELECT CURRENT_ACCOUNT();

• O nome da conta está no formato org_name.account_name. Você pode obter esses valores executando os seguintes procedimentos em sua conta de consumidor:

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

Provedor: publicação da clean room¶

Por fim, você pode publicar a clean room. Isso torna a clean room disponível aos consumidores que você adicionou acima. O procedimento leva um minuto ou mais para ser concluído.

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

Quando o procedimento for concluído, você deverá ver a clean room listada no aplicativo da Web de clean rooms da sua conta de provedor na guia Created e na guia Invited do aplicativo da Web da sua conta de consumidor, com o rótulo “Powered by Dev Edition”. A conta de consumidor receberá um e-mail de convite e a clean room deverá aparecer na guia Invited no aplicativo da Web da conta de consumidor.

Parabéns! Você publicou sua primeira clean room!

Agora, vá para a conta de consumidor para usar a clean room.

Consumidor: instalação (ingresso) da clean room¶

Passe para a conta de consumidor no Snowsight. Você pode abrir uma segunda guia em seu navegador ou usar o alternador de contas.

Após fazer login com sua conta de consumidor, configure seu ambiente no notebook de consumidor:

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

Instale a clean room que você acabou de publicar e compartilhar.

Para instalar uma clean room, você deve especificar o nome da clean room e o localizador de conta de provedor que compartilhou a clean room com você. A especificação do nome da clean room e do localizador da conta ajuda a eliminar a ambiguidade de vários convites para clean rooms. Você pode executar SELECT CURRENT_ACCOUNT(); na conta de provedor para obter o localizador de provedor.

A instalação pode levar alguns minutos.

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

Consumidor: criação e vinculação de seus dados¶

Agora, crie e adicione um conjunto de dados nessa clean room, semelhante ao conjunto de dados do provedor, mas usando as mil linhas inferiores dos dados de amostra.

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;

Após criar seus dados de origem, você deve se registrar e vinculá-los à clean room, da mesma forma que fez como provedor.

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

Consumidor: definição das políticas de adesão para seus dados¶

Agora, você pode definir políticas de associação para seus dados, assim como fez na conta de provedor. A configuração de políticas de associação de consumidores é redundante neste exemplo porque somente os consumidores podem executar um modelo nessa clean room. E como você mesmo está executando o modelo, sabe quais das suas colunas devem ser uníveis. No entanto, no uso real, faz sentido definir políticas de junção em seus dados, caso a clean room permita que o provedor execute um modelo.

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

Consumidor: como preparar sua consulta¶

Para executar uma consulta, você precisa das seguintes informações:

• O nome do modelo que deseja executar.

• Os nomes de suas tabelas a serem disponibilizadas ao modelo.

• Os nomes das tabelas do provedor a serem disponibilizadas ao modelo.

• Quaisquer outras variáveis de nome/valor a serem passadas. Em nosso modelo, precisamos passar os nomes das colunas de junção das tabelas de provedor e consumidor.

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

Ambas as contas: limpeza¶

Agora vamos limpar todos os recursos que você criou.

Observe que, para excluir as tabelas de origem, você precisará usar a mesma função que usou para criá-las.

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;