Criar modelos personalizados¶

Sobre os modelos de clean rooms¶

Os modelos de sala limpa são escritos em JinjaSQL. JinjaSQL é uma extensão da linguagem de criação de modelos Jinja. Um modelo JinjaSQL é avaliado como uma instrução SQL quando executado em uma sala limpa. A linguagem de criação de modelos JinjaSQL fornece instruções lógicas e substituição de variáveis em tempo de execução, o que permite que o modelo seja personalizado em tempo de execução. Por exemplo, um usuário pode fornecer nomes de tabelas e colunas ao executar o modelo, e o modelo pode se ajustar com base nos valores passados.

Há dois tipos gerais de modelos:

Modelos de análise, que são avaliados como uma instrução SQL DQL (uma instrução SELECT) que retorna os resultados da consulta imediatamente para o executor do modelo.
Modelos de ativação, que são utilizados para ativar os resultados em uma conta Snowflake, em vez de exibi-los no ambiente imediato. Um modelo de ativação é muito semelhante a um modelo de análise, com alguns requisitos adicionais, e resulta em uma instrução DDL (CREATE TABLE).

Sintaxe de modelo personalizado¶

O Snowflake Data Clean Rooms é compatível com JinjaSQL V3, com algumas extensões, conforme indicado.

Esta seção inclui os seguintes tópicos:

Regras de nomeação de modelos¶

Quando você criar um modelo, os nomes devem conter apenas letras, números ou sublinhados. Os nomes dos modelos são atribuídos no campo name da especificação do modelo quando você registra o modelo.

Exemplo de nomes válidos:

my_template
activation_template_1

Exemplo de nomes inválidos:

my template – Espaços não permitidos
my_template! – caracteres especiais não permitidos

Variáveis de modelo¶

Os autores da chamada de modelo podem passar valores para variáveis de modelo. A sintaxe JinjaSQL permite a vinculação de variáveis para qualquer nome de variável dentro de {{ double_brackets }}, mas o Snowflake reserva alguns nomes de variáveis que você não deve sobrescrever, conforme descrito abaixo.

Cuidado

Todas as variáveis, sejam definidas pelo Snowflake, sejam personalizadas, são preenchidas pelo usuário e devem ser tratadas com a devida cautela. Os modelos de análise devem ser resolvidos para uma única instrução SELECT (os modelos de ativação são resolvidos para um bloco de script). Lembre-se de que todas as variáveis são passadas pelo autor da chamada.

Variáveis definidas pelo Snowflake¶

Todos os modelos de sala limpa têm acesso às seguintes variáveis globais definidas pelo Snowflake, mas passadas pelo executor de análise:

source_table:

Uma matriz de cadeia de caracteres baseada em zero de tabelas e exibições de ofertas de dados vinculadas à colaboração por meio de LINK_DATA_OFFERING que o modelo pode utilizar.

Exemplo: SELECT col1 FROM IDENTIFIER({{ source_table[0] }}) AS p;

my_table:

Em uma sala limpa de colaboração, my_table é utilizado apenas por usuários do Snowflake Standard Edition. Para esses usuários, my_table é uma matriz de cadeias de caracteres baseada em zero de conjuntos de dados que o executor de análise vinculou chamando LINK_LOCAL_DATA_OFFERING.

Exemplo: SELECT col1 FROM IDENTIFIER({{ my_table[0] }}) AS c;

Variáveis personalizadas¶

Os criadores de modelos podem incluir variáveis arbitrárias em um modelo que podem ser preenchidas pelo executor de análise. Essas variáveis podem ter qualquer nome compatível com Jinja, exceto as variáveis definidas pelo Snowflake ou nomes de alias de tabela. Você deve fornecer orientações na seção de parâmetros do modelo para variáveis obrigatórias e opcionais.

As variáveis personalizadas podem ser acessadas por seu modelo, conforme mostrado aqui para a variável personalizada max_income:

SELECT income FROM my_db.my_sch.customers WHERE income < {{ max_income }};

Os executores de análise passam variáveis ao chamar RUN, conforme definido na especificação de execução de análise.

Resolução correta de variáveis¶

Os valores de cadeia de caracteres passados para o modelo são resolvidos como literais de cadeia de caracteres no modelo final. Isso pode causar erros SQL de análise ou lógicos se você não tratar adequadamente as variáveis vinculadas:

SELECT {{ my_col }} FROM p;: é resolvida como SELECT 'my_col' from p;, que apenas retorna a cadeia de caracteres «my_col», e provavelmente não é o que você deseja.
SELECT age FROM {{ source_table[0] }} AS p;: é resolvida como SELECT age FROM 'somedb.somesch.source_table' AS p;, que causa um erro de análise porque a tabela deve ser um identificador, não uma cadeia de caracteres literal.
SELECT age FROM IDENTIFIER({{ source_table[0] }}) AS p {{ where_clause }};: passada em «WHERE age < 50» é avaliada como SELECT age FROM mytable AS p 'WHERE age < 50';, que é um erro de análise devido à cláusula WHERE de cadeia de caracteres literal.

Portanto, quando apropriado, você deve resolver as variáveis. Veja como resolver as variáveis corretamente em seu modelo:

Resolução de nomes de tabela e coluna

As variáveis que especificam nomes de tabelas ou colunas devem ser convertidas em identificadores em seu modelo de duas maneiras:

IDENTIFIER: For example: SELECT IDENTIFIER({{ my_column }}) FROM p;
sqlsafe: Este filtro JinjaSQL resolve cadeias de caracteres de identificadores para texto SQL. Uma instrução equivalente ao item anterior é SELECT {{ my_column | sqlsafe }} FROM p;

Seu uso específico determina quando usar IDENTIFIER ou sqlsafe. Por exemplo, não é possível reescrever p.{{ my_column | sqlsafe }} facilmente usando IDENTIFIER.

Resolução de SQL dinâmico

Quando você tiver uma variável de cadeia de caracteres que deva ser usada como SQL literal, como uma cláusula WHERE, use o filtro sqlsafe em seu modelo. Por exemplo:

SELECT age FROM IDENTIFIER({{ source_table[0] }}) AS p WHERE {{ where_clause }};

Se um usuário passar «age < 50» para where_clause, a consulta seria resolvida para SELECT age FROM sometable AS p WHERE 'age < 50'; o que é um SQL inválido devido à condição WHERE de cadeia de caracteres literal. Nesse caso, você deve usar o filtro sqlsafe:

SELECT age FROM IDENTIFIER( {{ source_table[0] }} ) as p {{ where_clause | sqlsafe }};

Aliases de tabela necessários¶

No nível superior da sua consulta, todos os conjuntos de dados source_table devem ter alias definidos como p e todos os conjuntos de dados my_table devem ter alias definidos como c, para que o Snowflake valide corretamente as políticas de junção e coluna na consulta. Uma coluna que deve ser verificada em relação às políticas de junção ou de coluna precisa ser qualificada com os alias de tabela p ou c em letras minúsculas.

Se você usar vários conjuntos de dados source_table ou my_table em sua consulta, adicione um sufixo numérico sequencial baseado em 1 a cada alias de tabela após o primeiro. Portanto: p ou p1, p2, p3 e assim por diante para o primeiro, segundo e terceiro conjuntos de dados source_table, e c ou c1, c2, c3 e assim por diante para o primeiro, segundo e terceiro conjuntos de dados my_table. O índice p ou c deve ser sequencial, sem intervalos (ou seja, crie os alias p1, p2 e p3, e não p1, p2 e p4).

Exemplo

SELECT p1.col1 FROM IDENTIFIER({{ source_table[0] }}) AS p1
UNION
SELECT p2.col1 FROM IDENTIFIER({{ source_table[1] }}) AS p2;

Filtros de modelo de sala limpa personalizados¶

O Snowflake é compatível com todos os filtros Jinja padrão e com a maioria dos filtros padrão JinjaSQL, além de algumas extensões:

join_policy:: Com sucesso se a coluna estiver na política de junção do proprietário dos dados; caso contrário, com falha. Consulte Aplicando políticas de proteção de dados às ofertas de dados.
column_policy:: Com sucesso se a coluna estiver na política de coluna do proprietário dos dados; caso contrário, com falha. Consulte Aplicando políticas de proteção de dados às ofertas de dados.
activation_policy:: Com sucesso se a coluna estiver na política de ativação do proprietário dos dados; caso contrário, com falha. Consulte Aplicando políticas de proteção de dados às ofertas de dados.
join_and_column_policy:: Com sucesso se a coluna estiver na política de junção ou de coluna do proprietário dos dados; caso contrário, com falha. Consulte Aplicando políticas de proteção de dados às ofertas de dados.
identifier:: Este filtro JinjaSQL não é compatível com os modelos do Snowflake.

Dica

As instruções JinjaSQL são avaliadas da esquerda para a direita:

{{ my_col | column_policy }} Correto
{{ my_col | sqlsafe | column_policy }} Correto
{{ column_policy | my_col }} Incorreto
{{ my_col | column_policy | sqlsafe }} Incorreto: column_policy será verificada em relação ao valor my_col como uma cadeia de caracteres, o que é um erro.

Aplicação de políticas de clean room¶

As salas limpas não verificam automaticamente as políticas da sala limpa em relação às colunas utilizadas em um modelo. Se você quiser aplicar uma política a uma coluna:

Aplique o filtro de política apropriado a essa coluna no modelo. Por exemplo:

FROM IDENTIFIER({{ source_table[0] }}) AS p1
JOIN IDENTIFIER({{ source_table[1] }}) AS p2
  ON IDENTIFIER({{ p1_join_col | join_policy }}) = IDENTIFIER({{ p2_join_col | join_policy }})

Você deve atribuir um alias à tabela como minúsculas p ou c. Consulte Aliases de tabela necessários.

As políticas são verificadas apenas em relação a colunas de tabelas referenciadas em uma variável source_table, que se referem a exibições compartilhadas dentro da sala limpa. As políticas não são verificadas em relação às colunas das tabelas referenciadas em uma variável my_table, que são tabelas locais não compartilhadas dentro da sala limpa.

Observe que os nomes das colunas não podem ser ambíguos ao testar políticas. Portanto, se você tiver colunas com o mesmo nome em duas tabelas, deverá qualificar o nome da coluna para testar a política nessa coluna.

Considerações de acesso e práticas recomendadas¶

Um modelo é sempre executado no contexto da função de aplicativo de sala limpa. Um colaborador não tem acesso direto a nenhum dado dentro da sala limpa que seja restrito ao acesso apenas ao modelo; todo o acesso é feito por meio das funções de aplicativo nativas e das saídas do modelo.

Como prática recomendada, você deve seguir os itens abaixo para modelos que cria ou usa em uma sala limpa:

Certifique-se de que um filtro de política seja aplicado sempre que uma variável de coluna for utilizada em um modelo, para que as políticas do colaborador sejam respeitadas.
Coloque as variáveis fornecidas pelo usuário entre IDENTIFIER() quando possível para proteger os modelos contra ataques de injeção de SQL.

Modelos de ativação¶

Um modelo também pode ser utilizado para salvar os resultados da consulta em uma tabela fora da sala limpa; isso é chamado de ativação. Um modelo de ativação é um modelo de análise com os seguintes requisitos adicionais:

Os modelos de ativação são instruções JinjaSQL que são avaliadas em um bloco de script SQL, diferentemente dos modelos de análise, que podem ser simples instruções SELECT.
Os modelos de ativação devem criar uma tabela interna na sala limpa para armazenar os resultados. A tabela gerada pelo modelo deve ter o prefixo cleanroom.activation_data_, por exemplo: cleanroom.activation_data_my_results
Todas as colunas na tabela de resultados interna devem ter o valor activation_allowed: TRUE na especificação da oferta de dados.
O bloco de script deve terminar com uma instrução RETURN que retorna o nome da tabela gerada sem o prefixo cleanroom.activation_data_, por exemplo: RETURN 'my_results'.
O modelo em si não tem requisitos de nomenclatura.

Veja aqui um exemplo de especificação de modelo de ativação:

api_version: 2.0.0
spec_type: template
name: my_activation_template
version: v0
type: sql_activation
description: Activation template that creates segment data
template: |
  BEGIN
      CREATE OR REPLACE TABLE cleanroom.activation_data_analysis_results AS
      SELECT
          {{ group_by_column | sqlsafe }} AS bucket_label,
          {{ activation_column | sqlsafe | activation_policy }} AS activation_label,
          COUNT(DISTINCT {{ join_column | sqlsafe }}) AS overlap_count
      FROM IDENTIFIER({{ source_table[0] }}) AS p
      GROUP BY {{ group_by_column | sqlsafe }},
               {{ activation_column | sqlsafe }};
      RETURN 'analysis_results';
  END;
parameters:
  - name: join_column
    description: Join column name
    required: true
    default: "p.IP_ADDRESS"
  - name: group_by_column
    description: Group by column name
    required: true
    default: "p.CAMPAIGN_NAME"
  - name: activation_column
    description: Activation column name
    required: true
    default: "p.DEVICE_TYPE"

Saiba como implementar a ativação em uma colaboração: Ativação de resultados de consulta.

Próximos passos¶

Depois que você dominar o sistema de modelos, leia os detalhes específicos para implementar uma clean room com seu tipo de modelo:

Os modelos de ativação criam uma tabela de resultados após uma execução bem-sucedida e são compartilhados fora da sala limpa. Dependendo da especificação de colaboração, é possível compartilhar a tabela de resultados com o executor da análise ou outros colaboradores.
Pacotes de código são utilizados para carregar UDFs e UDTFs Python personalizadas em uma colaboração. Os modelos na colaboração podem executar essas funções para realizar ações de dados complexas.
Tabelas internas são utilizadas para armazenar resultados intermediários ou persistentes, que podem ser utilizados downstream para oferecer suporte a fluxos de trabalho de várias etapas. Essas tabelas são acessíveis a modelos ou código carregado personalizado dentro da sala limpa.

Criar modelos personalizados¶

Sobre os modelos de clean rooms¶

Criando, compartilhando e executando um modelo personalizado¶

1. O modelo JinjaSQL¶

2. O modelo de colaboração¶

3. Executando o modelo¶

Desenvolvimento de um modelo personalizado¶

Proteção de dados¶

Sintaxe de modelo personalizado¶

Regras de nomeação de modelos¶

Variáveis de modelo¶

Variáveis definidas pelo Snowflake¶

Variáveis personalizadas¶

Resolução correta de variáveis¶

Aliases de tabela necessários¶

Filtros de modelo de sala limpa personalizados¶

Aplicação de políticas de clean room¶

Considerações de acesso e práticas recomendadas¶

Modelos de ativação¶

Próximos passos¶

Mais informações¶