Criação de modelos personalizados de clean rooms¶

Sobre os modelos de clean rooms¶

Os modelos de clean room são escritos em JinjaSQL. JinjaSQL é uma extensão da linguagem de modelos Jinja que gera uma consulta SQL como saída. O JinjaSQL oferece suporte a instruções lógicas e resolução de variáveis em tempo de execução para permitir que o usuário personalize a consulta em tempo de execução. As variáveis são normalmente usadas em um modelo para permitir que um usuário especifique nomes de tabelas, colunas de tabelas e valores personalizados a serem usados em sua consulta.

O Snowflake oferece uma seleção de modelos predefinidos para casos de uso comuns. Esses modelos de estoque podem ser usados somente no aplicativo Web. No entanto, tanto os provedores quanto os consumidores podem criar modelos personalizados para uma clean room. Os modelos personalizados podem ser criados somente no código, mas podem ser executados no código ou por meio do aplicativo Web.

Há dois tipos gerais de modelos:

• Modelos de análise, que avaliam uma instrução SELECT (ou um conjunto de operações SELECT).

• Modelos de ativação, que avaliam uma instrução SELECT aninhada dentro de uma instrução CREATE TABLE e retornam o nome da tabela. Esse modelo gera dados que são exportados para a conta Snowflake do consumidor ou do provedor ou para um terceiro, dependendo de como a clean room está configurada. Um modelo de ativação é muito semelhante a um modelo de análise com alguns requisitos adicionais.

Nas UI de clean rooms, um modelo de análise pode ser associado a um modelo de ativação para permitir que o chamador execute uma análise e, em seguida, envie os dados para si mesmo ou para terceiros. O modelo de ativação não precisa ser resolvido para a mesma consulta que o modelo de análise associado.

Criação e execução de um modelo personalizado¶

Em uma clean room com configurações padrão, o provedor adiciona um modelo a uma clean room e o consumidor pode escolher, configurar e executá-la:

1. O provedor projeta um modelo personalizado e o adiciona a uma clean room chamando provider.add_custom_sql_template.

2. O consumidor chama consumer.run_analysis para executar o modelo do provedor, passando valores para quaisquer variáveis necessárias ao modelo.

Esse fluxo não exige permissões da outra parte, a não ser o fato de que o consumidor deve ser convidado pelo provedor para uma clean room. Há variações nesse processo, como modelos fornecidos pelo consumidor e modelos gerenciados pelo provedor, que são abordados em outro lugar.

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

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

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

• Variáveis de modelo

• Aliases de tabela necessários

• Filtros de modelo

• Execução de código Python personalizado

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

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

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

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

Considerações de segurança¶

Um modelo deve ser avaliado em uma única consulta SELECT, que é executada pelo aplicativo nativo da clean room. O modelo não é executado com a identidade do usuário atual.

O usuário não tem acesso direto a nenhum dado dentro da clean room; todo o acesso é feito pelo aplicativo nativo por meio dos resultados do modelo.

Aplique um filtro de política sempre que uma coluna for usada na consulta, mesmo quando o nome da coluna for definido explicitamente no modelo, ou quando a coluna ou tabela for fornecida pelo usuário. Você pode alterar as políticas de junção ou coluna posteriormente, ou alterar a coluna e esquecer de atualizar o modelo. Para todas as colunas fornecidas pelo usuário, você deve aplicar um filtro join_policy, column_policy, join_and_column_policy ou activation_policy.

Modelos de ativação¶

Um modelo também pode ser usado para salvar os resultados de consulta em uma tabela fora da clean room; isso é chamado de ativação. Atualmente, as únicas formas de ativação compatíveis com modelos personalizados são a ativação do provedor e a ativação do consumidor (armazenando resultados na conta Snowflake do provedor ou do consumidor, respectivamente). Saiba como implementar a 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.

• O nome do modelo de ativação deve começar com a cadeia de caracteres activation (exceto para modelos de ativação de provedores executados pelo consumidor). Por exemplo: activation_my_template.

• O modelo de ativação deve criar uma tabela com um nome que depende do tipo de ativação que ele permite:

  • Ativação de provedor executada pelo provedor: o nome da tabela gerada deve ser cleanroom.temp_result_data.

  • Todos os outros tipos de ativação: o nome da tabela gerada deve ser prefixado por cleanroom.activation_data_, por exemplo: cleanroom.activation_data_cross_activation_results. O nome da tabela deve ser exclusivo em sua clean room.

  Essa tabela gerada é uma tabela intermediária; você não deve tentar acessá-la diretamente.

• O bloco de script deve terminar com uma instrução RETURN que retorna o nome da tabela gerada, menos qualquer prefixo cleanroom. ou cleanroom.activation_data_.

• Todas as colunas que estão sendo ativadas devem estar listadas na política de ativação do provedor ou consumidor que vinculou os dados e devem ter o filtro activation_policy aplicado a elas. Observe que uma coluna pode ser tanto uma coluna de ativação quanto uma coluna de junção.

• Se o modelo tiver de ser executado a partir da UI de clean rooms, você deverá fornecer um formulário Web que inclua os campos activation_template_name e enabled_activations. Os modelos para uso na UI devem ter um modelo de análise e um modelo de ativação associado.

• Todas as colunas calculadas devem ter aliases explícitos, em vez de nomes inferidos, porque uma tabela está sendo gerada. Ou seja:

  SELECT COUNT(*), P.status from T AS P; FAILS porque o nome da coluna COUNT é inferido.

  SELECT COUNT(*) AS COUNT_OF_ITEMS, P.status from T AS P; SUCCEEDS porque ele cria explicitamente um alias para a coluna COUNT.

Aqui estão duas amostras de modelos básicos de ativação. Um é para ativação de servidor executado pelo provedor e o outro é para outros tipos de ativação. Eles diferem nas duas linhas destacadas, que contêm o nome da tabela de resultados.

-- These are the required table name strings.
BEGIN
  CREATE OR REPLACE TABLE cleanroom.temp_result_data AS
    SELECT COUNT(c.status) AS ITEM_COUNT, c.status, c.age_band
      FROM IDENTIFIER({{ my_table[0] }}) AS c
    JOIN IDENTIFIER({{ source_table[0] }}) AS p
      ON {{ c_join_col | sqlsafe | activation_policy }} = {{ p_join_col | sqlsafe | activation_policy }}
    GROUP BY c.status, c.age_band
    ORDER BY c.age_band;
  RETURN 'temp_result_data';
END;

-- analysis_results can be whatever name you want.
BEGIN
  CREATE OR REPLACE TABLE cleanroom.activation_data_analysis_results AS
    SELECT COUNT(c.status) AS ITEM_COUNT, c.status, c.age_band
      FROM IDENTIFIER({{ my_table[0] }}) AS c
    JOIN IDENTIFIER({{ source_table[0] }}) AS p
      ON {{ c_join_col | sqlsafe | activation_policy }} = {{ p_join_col | sqlsafe | activation_policy }}
    GROUP BY c.status, c.age_band
    ORDER BY c.age_band;
  RETURN 'analysis_results';
END;

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:

• Modelos de provedor são modelos gravados pelo provedor. Esse é o caso de uso padrão.

• Modelos de consumidor são modelos gravados pelo consumidor. Em alguns casos, o criador de uma clean room quer permitir que o consumidor crie, carregue e execute seus próprios modelos na clean room.

• Modelos de ativação criam uma tabela de resultados após uma execução bem-sucedida. Dependendo do modelo de ativação, a tabela de resultados pode ser salva na conta de provedor ou consumidor fora da clean room ou enviada a um provedor de ativação terceirizado listado no hub de ativação.

• Modelos encadeados permitem que você encadeie vários modelos em que a saída de cada modelo é usada pelo próximo modelo da cadeia.

Mais informações¶

• Documentação do Jinja

• Documentação do JinjaSQL