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¶

Este é um resumo das etapas que você seguirá para criar uma sala limpa como provedor:

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 as permissões de junção em seus dados para especificar quais colunas podem ser unidas nas consultas do consumidor.

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¶

Faça login no Snowsight como usuário com a função SAMOOHA_APP_ROLE. Se não tiver essa função, peça que o administrador da sua conta a conceda para você.

Crie uma nova planilha SQL no Snowsight para armazenar o código da sala limpa. Nomeie a planilha como «Tutorial da API - Provedor».

Crie uma tabela com base em 1.000 linhas de dados de teste de amostra da tabela SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS. Você usará essa tabela como os dados do provedor na sala limpa.

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

Provedor: criação da clean room¶

O trecho a seguir cria uma sala limpa acessível somente dentro da organização (portanto, marcada como INTERNAL). Para compartilhar uma sala limpa fora de uma organização, são necessárias etapas adicionais que não serão abordadas neste tutorial. Ao compartilhar uma sala limpa com você mesmo, certamente ela deve ser INTERNAL.

Você deve usar a SAMOOHA_APP_ROLE para a maioria dos procedimentos de sala limpa.

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¶

A seguir, transfira os dados de teste para a sala limpa. Há duas etapas para transferir os dados para uma sala limpa:

1. Registre os dados.

2. Importe (vincule) os dados para a sala limpa.

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

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

Tanto provedores quanto consumidores podem especificar políticas de junção em seus próprios dados. Uma política de junção de sala limpa especifica quais colunas em uma tabela podem ser unidas nas consultas feitas por seus colaboradores nessa sala limpa. Isso aumenta o nível de controle sobre como as outras pessoas podem usar seus dados na sala limpa. Suas próprias políticas não são aplicadas a suas próprias consultas, ou seja, as políticas de junção que você define nos próprios dados são ignoradas quando você executa uma consulta. Suas políticas são aplicadas somente a consultas executadas por outros usuários.

As políticas de junção de sala limpa são definidas na tabela e se aplicam a todas as salas limpas em que a tabela é usada. As colunas não listadas aqui não podem ser unidas usando as instruções INNER JOIN ou OUTER JOIN na sala limpa. Se você não especificar uma política de junção, todas as colunas poderão ser unidas.

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.

Especifique colunas que possam ser unidas em uma tabela usando o formato database_name.schema_name.table_or_view_name:column_name para cada coluna. O seguinte exemplo permite unir três colunas de dados do provedor:

-- 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 na sala limpa pode limitar as colunas que as outras partes podem projetar configurando uma column_policy. Uma política de coluna em uma sala limpa lista todas as colunas dos dados que podem ser projetadas. Nenhuma outra coluna pode ser projetada. Se você não especificar uma política de coluna para seus dados, todos os dados poderão ser projetados.

Uma política de coluna é vinculada a uma tabela e um modelo específicos em uma sala limpa. Você pode permitir que diferentes colunas sejam projetadas em diferentes modelos. A mesma coluna não pode estar em uma política de junção e também de coluna.

Observe que as políticas de coluna e de junção serão aplicadas somente se o modelo usar os filtros column_policy e row_policy no modelo.

Veja como permitir a projeção de três colunas de seus dados no modelo que você acabou 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:DAYS_ACTIVE']);

CALL samooha_by_snowflake_local_db.provider.view_column_policy($cleanroom_name);

Provedor: como adicionar uma diretiva de lançamento¶

Cada sala limpa tem um número de versão, que consiste nos valores principal, secundário e de patch. Você deve especificar qual versão da sala limpa é fornecida aos consumidores, o que é 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 sala limpa cada vez que você carrega o código na sala limpa. Se quiser que os usuários obtenham a versão mais recente, você precisará definir uma nova diretiva de lançamento padrão com o número da versão mais recente. Você não carregará o código, logo, não precisará chamá-lo novamente neste tutorial.

Provedor: designar consumidores¶

Agora você especificará quem tem acesso à sua sala limpa como consumidor. Para este tutorial, você se adicionará como consumidor. Isso marca a sala limpa como de teste interno, usada apenas para testes, o que limita algumas de suas funcionalidades, mas oferece suporte a todos os recursos necessários para este tutorial.

O procedimento precisa de dois argumentos para identificar cada consumidor:

• O localizador da conta do consumidor. Obtenha o localizador da conta desta maneira:

  SELECT CURRENT_ACCOUNT();
  

  Copy

• O ID da conta de compartilhamento de dados do consumidor, no formato org_name.account_name. Obtenha o ID da sua conta de compartilhamento de dados de consumidor no formato adequado desta maneira:

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

  Copy

Agora compartilhe a sala limpa com você mesmo como consumidor, adicionando o localizador da sua conta e o ID da conta de compartilhamento de dados do consumidor nos locais indicados:

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

Provedor: publicação da clean room¶

Por fim, publique a sala limpa. Isso a torna disponível para o consumidor adicionado 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 terminar, você deverá ver a sala limpa listada na UI de salas limpas, na guia Created em sua conta de provedor e na guia Invited na conta do consumidor, com o rótulo «Powered by Dev Edition». A conta do consumidor receberá um e-mail de convite. (Não instale a sala limpa da guia Invited. Você a instalará no código em uma etapa posterior.)

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

Agora remova o limite do provedor e insira o limite do consumidor.

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

Você usará a mesma conta para as funções de provedor e de consumidor neste tutorial, portanto, adicione uma nova planilha SQL chamada «Tutorial da API - Consumidor» no Snowsight na mesma conta.

Configure o ambiente da sessão, parecido com o que você fez para o provedor:

USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

Em seguida, instale a sala limpa que você publicou e compartilhou como provedor. Para instalar uma sala limpa, você deve especificar o nome da sala limpa e o localizador da conta do provedor que compartilhou a sala limpa com você. Especificar o nome da sala limpa e o localizador da conta ajuda a evitar ambiguidade de salas limpas com nomes idênticos. Execute SELECT CURRENT_ACCOUNT(); para obter seu localizador de provedor.

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

A instalação pode levar alguns minutos.

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

Agora crie e adicione um conjunto de dados a essa sala limpa. Crie uma tabela semelhante ao conjunto de dados do provedor, mas use as 1.000 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 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;

Depois de criar os dados de origem, você deverá registrá-los e vinculá-los à sala limpa, 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: não é necessário definir políticas¶

Você pode definir políticas em seus dados, da mesma forma que o provedor fez, mas este modelo foi aprovado apenas para o consumidor executar, portanto, não há necessidade de restringir suas próprias consultas. De qualquer forma, suas próprias políticas de linha e de coluna são ignoradas quando você executa uma consulta.

No entanto, se você fosse aprovar uma solicitação do provedor para executar esse modelo, primeiro definiria políticas de junção e de coluna em seus dados para controlar o que o provedor poderia fazer com eles.

Consumidor: executar a análise¶

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

• O nome do modelo que deseja executar.

• Os nomes das tabelas a serem usadas no modelo.

• Os nomes das tabelas do provedor a serem usadas no modelo.

• Qualquer outra variável de nome/valor a ser passada.

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

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;