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 consumer_table
  ON consumer_table.hashed_email = provider_table.hashed_email
  GROUP BY city;

Veja abaixo a aparência da consulta como um modelo JinjaSQL que permite ao autor da chamada escolher as colunas JOIN e GROUP BY e as tabelas utilizadas:

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 do provedor devem usar os alias em um modelo como p em letras minúsculas, e as tabelas do consumidor como c em letras minúsculas. Se você tem várias tabelas, pode 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.

• Os filtros do JinjaSQL podem ser aplicados a variáveis para impor políticas de junção ou de coluna definidas por qualquer um dos lados. O Snowflake implementa os filtros personalizados join_policy e column_policy, que verificam se uma coluna cumpre respectivamente as políticas de junção ou de coluna na sala limpa. Se não cumprir, haverá falha na consulta. 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.

Use o procedimento consumer.get_jinja_sql para testar a validade do seu modelo e, em seguida, execute o modelo renderizado para verificar se ele produz os resultados esperados. 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 IDENTIFIER({{ provider_join_col }}) = IDENTIFIER({{ consumer_join_col }})
  {% 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']
));

O modelo renderizado tem esta aparência:

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;

Tente executar a instrução SQL acima em seu ambiente para ver se ela funciona e retorna os resultados esperados.

Em seguida, teste seu modelo sem uma cláusula WHERE:

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

Modelo renderizado:

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
  ;

Adicione os filtros de política ao modelo e o modelo à sua sala limpa:

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 de política JinjaSQL apropriado em uma coluna para a política que será aplicada.

Regras de nomeação de modelos¶

Ao criar um modelo, todos os nomes devem ser letras minúsculas, números, espaços ou sublinhados. Os modelos de ativação (exceto para ativação de provedor executada pelo consumidor) devem ter um nome que comece com activation_. Os nomes dos 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 da sua consulta, todas as tabelas ou subconsultas devem ter alias definidos como p (para tabelas de provedor) ou c (para tabelas de consumidor), de modo que o Snowflake valide as políticas de junção e de coluna corretamente 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. Especificar p ou c instrui o back-end se é para validar uma coluna em relação à política de provedor ou de consumidor, respectivamente.

Se você usa várias tabelas de provedor ou de consumidor em sua consulta, adicione um sufixo numérico e 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 provedor, e c, c1, c2, e assim por diante, para a primeira, segunda e terceira tabelas de consumidor. O índice p ou c deve ser sequencial, sem intervalos (ou seja, crie os alias p, p1 e p2, e não p, p2 e p4).

Exemplo

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

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.

• column_policy: com sucesso se a coluna estiver na política de coluna do proprietário dos dados; caso contrário, com falha.

• activation_policy: com sucesso se a coluna estiver na política de ativação do proprietário dos dados; caso contrário, com falha.

• 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.

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

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

As salas limpas não verificam automaticamente as políticas de sala limpa em relação às colunas usadas 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:

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

• Você deve definir o alias da tabela como p ou c em letras minúsculas. Consulte Aliases de tabela necessários.

As políticas são verificadas somente em relação às colunas de propriedade de outros colaboradores. Elas não são verificadas em seus próprios dados.

Observe que os nomes das colunas não podem ser ambíguos ao testar as 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.

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