Referência de modelo de sala limpa personalizado¶

Um exemplo rápido¶

Aqui está um exemplo simples de SQL que une uma tabela de provedor e uma tabela de consumidor por e-mail e mostra a contagem de sobreposições por cidade:

SELECT COUNT(*), city FROM consumer_table
  INNER JOIN provider_table
  ON consumer_table.hashed_email = provider_table.hashed_email
  GROUP BY city;

Esta é a aparência dessa consulta como um modelo que permite que o chamador escolha as colunas select/group e join, bem como as tabelas:

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

Observações sobre o modelo:

• Os valores em {{ double bracket pairs }} são variáveis personalizadas. group_by_col, my_table, source_table, consumer_join_col, provider_join_col e group_by_col são variáveis personalizadas preenchidas pelo chamador.

• source_table e my_table são variáveis de matriz de cadeia de caracteres definidas pelo Snowflake e preenchidas pelo chamador. Os membros da matriz são nomes totalmente qualificados de tabelas de provedores e consumidores vinculadas à clean room. O chamador especifica quais tabelas devem ser incluídas em cada matriz.

• As tabelas de provedores devem ter o alias P e as tabelas de consumidores C em um modelo. Se você tiver várias tabelas, poderá indexá-las como P1, P2, C1, C2 e assim por diante.

• IDENTIFIER é necessário para todos os nomes de colunas e tabelas, pois as variáveis em {{ double brackets }} são avaliadas como literais de cadeia de caracteres, que não são identificadores válidos.

• Filtros JinjaSQL podem ser aplicados às variáveis. O Snowflake implementa os filtros personalizados join_policy e column_policy, que verificam se uma coluna está em conformidade com as políticas de junção ou de coluna na clean room, respectivamente, e reprova a consulta se não estiver. Um filtro é aplicado a um nome de coluna como {{ column_name | filter_name }}.

Todos esses pontos serão discutidos em detalhes posteriormente.

Veja como um consumidor pode executar esse modelo no código. Observe como os nomes de colunas são qualificados pelos aliases de tabela declarados no modelo.

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.RUN_ANALYSIS(
  $cleanroom_name,
  $template_name,
  ['my_db.my_sch.consumer_table],       -- Populates the my_table variable
  ['my_db.my_sch.provider_table'],      -- Populates the source_table variable
  OBJECT_CONSTRUCT(                     -- Populates custom named variables
    'consumer_join_col','c.age_band',
    'provider_join_col','p.age_band',
    'group_by_col','p.device_type'
  )
);

Para usar esse modelo na UI de salas limpas, o provedor deve criar um formulário de UI personalizado para o modelo. O formulário de UI nomeou os elementos do formulário de forma que correspondam aos nomes das variáveis do modelo, e os valores inseridos no formulário são passados para o modelo.

Desenvolvimento de um modelo personalizado¶

Os modelos de sala limpa são modelos JinjaSQL. Para criar um modelo, você deve estar familiarizado com os seguintes tópicos:

• Noções básicas sobre criação de modelos Jinja

• A extensão JinjaSQL para Jinja

Siga o procedimento get_sql_jinja para testar a validade do modelo e renderizar um modelo. Execute o modelo renderizado para ver se ele produz os resultados desejados. Observe que esse procedimento não oferece suporte para extensões de filtro de sala limpa, como join_policy, portanto, você deve testar seu modelo sem esses filtros e adicioná-los mais tarde.

Exemplo:

-- Template to test
SELECT {{ col1 | sqlsafe }}, {{ col2 | sqlsafe }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c
  ON {{ provider_join_col | sqlsafe }} = {{ consumer_join_col | sqlsafe}}
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe}}{% endif %};

-- Render the template.
USE WAREHOUSE app_wh;
USE ROLE samooha_app_role;

CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.GET_SQL_JINJA(
$$
SELECT {{ col1 | sqlsafe }}, {{ col2 | sqlsafe }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c
  ON {{ provider_join_col | sqlsafe }} = {{ consumer_join_col | sqlsafe}}
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};
  $$,
  object_construct(
'col1', 'c.status',
'col2', 'c.age_band',
'where_phrase', 'p.household_size > 2',
'consumer_join_col', 'c.age_band',
'provider_join_col', 'p.age_band',
'source_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
'my_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']
));

-- The rendered template looks like this:
SELECT c.status, c.age_band
  FROM IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p
  JOIN IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS c
  ON p.age_band = c.age_band
  WHERE p.household_size > 2;

-- Run it.

- Test without a WHERE clause
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.CONSUMER.GET_SQL_JINJA(
$$
SELECT {{ col1 | sqlsafe }}, {{ col2 | sqlsafe }}
  FROM IDENTIFIER({{ source_table[0] }}) AS p
  JOIN IDENTIFIER({{ my_table[0] }}) AS c
  ON {{ provider_join_col | sqlsafe }} = {{ consumer_join_col | sqlsafe}}
  {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};
  $$,
  object_construct(
'col1', 'c.status',
'col2', 'c.age_band',
'consumer_join_col', 'c.age_band',
'provider_join_col', 'p.age_band',
'source_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS'],
'my_table', ['SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS']
));

-- Output
SELECT c.status, c.age_band
  FROM IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS p
  JOIN IDENTIFIER('SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS') AS c
  ON p.age_band = c.age_band
  ;

-- Put in the policy filters and declare the template
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'simple_template',
    $$
    SELECT {{ col1 | sqlsafe | column_policy }}, {{ col2 | sqlsafe | column_policy }}
      FROM IDENTIFIER({{ source_table[0] }}) AS p
      JOIN IDENTIFIER({{ my_table[0] }}) AS c
      ON {{ provider_join_col | sqlsafe | join_policy }} = {{ consumer_join_col | sqlsafe | join_policy }}
      {% if where_phrase %} WHERE {{ where_phrase | sqlsafe }}{% endif %};
    $$,
);

Proteção de dados¶

Os modelos podem acessar apenas conjuntos de dados vinculados à clean room pelo provedor e pelo consumidor.

Tanto o provedor quanto o consumidor podem definir políticas de junção, coluna e ativação em seus dados para proteger as colunas que podem ser unidas, projetadas ou ativadas. Entretanto, o modelo deve incluir o filtro JinjaSQL apropriado em uma coluna para a política que será aplicada.

Regras de nomeação de modelos¶

Ao criar um modelo, os nomes devem ser todos em letras minúsculas, números, espaços ou sublinhados. Os modelos de ativação (exceto para a ativação do provedor administrado pelo consumidor) devem ter um nome que comece com “activation”. Os nomes de modelos são atribuídos quando você chama provider.add_custom_sql_template ou consumer.create_template_request.

Exemplo de nomes válidos:

• my_template

• activation_template_1

Exemplo de nomes inválidos:

• my template – Espaços não permitidos

• My_Template – Somente modelos em letras minúsculas são permitidos

Variáveis de modelo¶

Os chamadores 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 em {{ double_brackets }}, mas o Snowflake reserva alguns nomes de variável que você não deve substituir, conforme descrito abaixo.

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

Aliases de tabela necessários¶

No nível superior de sua consulta, todas as tabelas ou subconsultas devem ter o alias P (para tabelas de provedores) ou C (para tabelas de consumidores) para que o Snowflake valide corretamente as políticas de junção e coluna na consulta. Qualquer coluna que precise ser verificada em relação às políticas de junção ou de coluna pertence a uma tabela cujo alias é P ou C. (Especificar P ou C informa ao back-end se a coluna deve ser validada com base na política do provedor ou consumidor, respectivamente)

Se você usar várias tabelas de provedor ou consumidor em sua consulta, adicione um sufixo numérico sequencial baseado em 1 a cada alias de tabela após o primeiro. Portanto: P, P1, P2 e assim por diante para a primeira, segunda e terceira tabelas de provedores, e C, C1, C2 e assim por diante para a primeira, segunda e terceira tabelas de consumidores. O índice P ou C deve ser sequencial, sem intervalos (ou seja, crie os aliases P, P1 e P2, e não P, P2 e P4).

Exemplo

SELECT col1 FROM IDENTIFIER({{ source_table[0] }}) AS P;

Filtros de modelo¶

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: verifica se a coluna é permitida pela política de junção da tabela e falha se não for.

• column_policy: verifica se a coluna é permitida pela política de colunas do modelo (pode ser projetada).

• activation_policy: verifica se a coluna filtrada é permitida pelas políticas de ativação da clean room (provider.set_activation_policy ou consumer.set_activation_policy).

• join_and_column_policy: verifica se a coluna é permitida pelas políticas de junção, ativação ou coluna. Usado para oferecer mais flexibilidade na clean room, para permitir que os colaboradores atualizem as políticas de junção e coluna sem alterar o modelo.

• O filtro JinjaSQL identifier não é compatível com os modelos Snowflake.

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 com base no valor my_col como cadeia de caracteres, o que é um erro.

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

As clean rooms não verificam automaticamente as políticas de clean room em relação às colunas usadas em um modelo. Se quiser impor uma política a uma coluna, você deve aplicar o filtro de política apropriado a essa coluna no modelo. Por exemplo:

JOIN IDENTIFIER({{ source_table[0] }}) AS p
  ON {{ c_join_col | sqlsafe | join_policy }} = {{ p_join_col | sqlsafe }}

Isso testará a política de junção em relação à coluna passada para c_join_col, mas não em relação a p_join_col.

Observe que os nomes das colunas não podem ser ambíguos ao testar políticas, da mesma forma que qualquer outro uso de SQL. Portanto, se você tiver colunas com o mesmo nome em duas tabelas, deverá qualificar o nome da coluna para testar a política em relação a essa coluna.

Execução de código Python personalizado¶

Os modelos podem executar o código Python carregado na clean room. O modelo pode chamar uma função Python que aceita valores de uma linha de dados e retorna valores para serem usados ou projetados na consulta.

• Quando o provedor carrega um código Python personalizado em uma sala limpa, o modelo chama funções Python com a sintaxe cleanroom.function_name. Mais detalhes aqui.

• Quando o consumidor carrega um código Python personalizado em uma sala limpa, o modelo chama a função com o valor básico function_name passado para consumer.generate_python_request_template (sem escopo definido para cleanroom como acontece no código do provedor). Mais detalhes aqui.

Exemplo de código de provedor:

-- Provider uploads a Python function that takes two numbers and returns the sum.
call samooha_by_snowflake_local_db.provider.load_python_into_cleanroom(
  $cleanroom_name,
  'simple_addition',                        -- Function name to use in the template
  ['someval integer', 'added_val integer'], -- Arguments
  [],                                       -- No packages needed
  'integer',                                -- Return type
  'main',                                   -- Handler for function name
  $$

def main(input, added_val):
  return input + int(added_val)
    $$
);

-- Template passes value from each row to the function, along with a
-- caller-supplied argument named 'increment'
call samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    'simple_python_example',
$$
    SELECT val, cleanroom.simple_addition(val, {{ increment | sqlsafe }})
    FROM VALUES (5),(8),(12),(39) AS P(val);
$$
);