Execução de consultas SQL de forma livre nas tabelas de clean room¶

Políticas e suporte à privacidade diferencial¶

Quando você expõe dados de clean room para consultas de forma livre, todas as políticas do Snowflake são respeitadas. Políticas de clean room (políticas de junção, políticas de coluna) não são aplicadas em consultas de forma livre.

A privacidade diferencial não é aplicada em dados expostos a consultas de forma livre. Isso inclui tanto a privacidade diferencial Snowflake quanto a privacidade diferencial de clean room.

Ativação de consultas de forma livre¶

USE ROLE SAMOOHA_APP_ROLE;
CALL samooha_by_snowflake_local_db.provider.patch_cleanroom($cleanroom_name,TRUE);

Provedor

O provedor executa os seguintes passos para disponibilizar os conjuntos de dados em uma clean room aos colaboradores da clean room usando consultas de forma livre:

1. Crie a clean room da maneira padrão.

2. Registre e vincule os conjuntos de dados à clean room da maneira padrão. Observe que, atualmente, os dados devem ser registrados usando a API; não é possível registrar exibições na UI de clean room e usá-las para consultas de forma livre. Você deve aplicar qualquer política de agregação, junção ou outras políticas do Snowflake antes de compartilhar seus dados fora da clean room.

3. Chame provider.enable_workflows_for_consumers para permitir que usuários específicos tenham acesso de forma livre às tabelas que você especificará na próxima etapa.

4. Chame provider.enable_datasets_for_workflow para permitir que os usuários da etapa anterior tenham acesso de forma livre aos conjuntos de dados especificados aqui.

5. Adicione seus colaboradores da maneira padrão, chamando provider.add_consumers.

6. Publique sua clean room.

7. Se quiser revogar a permissão para consultar essas tabelas, você pode fazer isso no nível do usuário, chamando provider.disable_consumer_run_analysis ou provider.remove_consumers, no nível da exibição, chamando library.unregister_objects ou library.unregister_db, ou excluindo a clean room.

Se já existir uma clean room e os dados estiverem registrados, basta chamar provider.enable_workflows_for_consumers e provider.enable_datasets_for_workflow para expor as tabelas especificadas aos usuários especificados.

O código a seguir cria três tabelas de amostra, cria uma nova clean room, vincula as tabelas e concede acesso de consulta de forma livre a essas tabelas para os colaboradores da clean room por meio da clean room.

----------------- Create sample data -----------------
USE ROLE MYROLE;
CREATE DATABASE freeform_db;

-- Create a table with an aggregation constraint.
CREATE OR REPLACE TABLE freeform_db.public.agg_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS;

CREATE AGGREGATION POLICY freeform_db.public.agg_policy AS ()
  RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

ALTER TABLE freeform_db.public.agg_constrained_table
  SET AGGREGATION POLICY freeform_db.public.agg_policy;

-- Create a table with a projection constraint.
CREATE OR REPLACE TABLE freeform_db.public.proj_constrained_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS;

CREATE OR REPLACE PROJECTION POLICY freeform_db.public.proj_policy AS ()
  RETURNS PROJECTION_CONSTRAINT ->
  PROJECTION_CONSTRAINT(ALLOW => false);

ALTER TABLE freeform_db.public.proj_constrained_table MODIFY COLUMN hashed_email
  SET PROJECTION POLICY freeform_db.public.proj_policy;

-- Create a table with a masking policy.
CREATE OR REPLACE TABLE freeform_db.public.masked_table
  AS SELECT * FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS;

CREATE OR REPLACE MASKING POLICY freeform_db.public.masking_policy
  AS (val string) RETURNS STRING ->
  CASE
    WHEN current_account() IN ('DCR_PROVIDER_PP6') THEN VAL
    ELSE '*********'
  END;

ALTER TABLE freeform_db.public.masked_table MODIFY COLUMN hashed_email
  SET MASKING POLICY freeform_db.public.masking_policy;

----------------- Create and publish a clean room that supports -----------------
----------------- free-form queries against this data.          -----------------

-- Create the clean room. Nothing new here.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.cleanroom_init($cleanroom_name, 'INTERNAL');

-- Link in the policy-protected tables from above. Nothing new here.
USE ROLE MYROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.register_db('freeform_db');
USE ROLE SAMOOHA_APP_ROLE;
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.link_datasets($cleanroom_name,
  ['freeform_db.public.agg_constrained_table',
  'freeform_db.public.proj_constrained_table',
  'freeform_db.public.masked_table']);

-- Grant the following consumer access to the tables specified next.
SET flow_name = 'freeform_sql';
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_workflows_for_consumers($cleanroom_name,
  [$flow_name],
  ['<CONSUMER_LOCATOR>']);

-- Grant the consumer specified above access to the specified tables.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.enable_datasets_for_workflow($cleanroom_name,
  $flow_name,
  ['freeform_db.public.agg_constrained_table',
   'freeform_db.public.proj_constrained_table',
    'freeform_db.public.masked_table']);

-- Add collaborators and publish, in the standard way.
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.add_consumers(
  $cleanroom_name, '<CONSUMER_LOCATOR>', '<ORG_NAME>.<CONSUMER_LOCATOR>');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.set_default_release_directive(
  $cleanroom_name, 'V1_0', '0');
CALL SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.provider.create_or_update_cleanroom_listing(
  $cleanroom_name);

Consumidor

Depois que o provedor tiver publicado uma clean room com fluxos de trabalho SQL de forma livre, os consumidores com acesso a essa clean room poderão executar consultas nas exibições expostas.

1. Instale a clean room da maneira padrão. Não há necessidade de vincular dados, pois o consumidor pode acessar suas tabelas diretamente.

2. Chame consumer.get_provider_freeform_sql_views para listar as exibições SQL de forma livre disponíveis para a conta e função atuais.

3. Execute as consultas SQL padrão com base nos dados. As políticas do produtor serão aplicadas aos dados.

-- Install the clean room.
USE ROLE SAMOOHA_APP_ROLE;
SET cleanroom_name = 'freeform queries';

CALL samooha_by_snowflake_local_db.consumer.install_cleanroom($cleanroom_name, '<PROVIDER_LOCATOR>');

-- List free form views available in the clean room.
CALL samooha_by_snowflake_local_db.consumer.GET_PROVIDER_FREEFORM_SQL_VIEWS($cleanroom_name);

-- Run queries on the views
SELECT * FROM <PROJECTION_POLICY_VIEW_NAME>;
SELECT * FROM <MASKING_POLICY_VIEW_NAME>;
SELECT COUNT(hashed_email), age_band
  FROM <AGGREGATION_POLICY_VIEW_NAME> group by age_band;

Provedor: crie uma clean room e defina as políticas¶

1. Crie uma clean room ou edite uma clean room existente e especifique tabelas ou exibições para sua tabela.

2. As políticas de junção especificadas durante o processo de criação da clean room são ignoradas ao usar o modelo de consulta SQL, mas são respeitadas em qualquer outro modelo.

3. Em Configure Analysis & Query, selecione Horizontal » SQL Query.

4. Na seção de configurações SQL Query, defina as seguintes propriedades:

   1. Em Tables, selecione as tabelas que devem estar disponíveis aos colaboradores da clean room em consultas de forma livre. Por padrão, todas as colunas das tabelas selecionadas podem ser projetadas, e as políticas de agregação não precisam ser aplicadas. Para controlar quais colunas podem ser projetadas e quais devem ser agregadas, é necessário definir políticas de coluna na próxima seção.

   2. Na seção Column Policies, defina os seguintes valores para controlar se ou como suas colunas podem ser usadas em uma consulta:

      1. Aggregation policy columns: especifique quais colunas devem ser agregadas para que apareçam nos resultados de consulta. Se você aplicar uma política de agregação a uma coluna e uma coluna for usada em uma consulta, os resultados deverão ser agregados. Todas as colunas listadas aqui serão adicionadas à seção Privacy settings.

      2. Projection policy columns: as colunas com uma política de projeção não podem ser projetadas (ou seja, incluídas em uma instrução SELECT). No entanto, os consumidores podem filtrar ou fazer junções com uma coluna que tenha uma política de projeção.

      3. Fully permitted columns: o consumidor pode usar SELECT, filtrar ou unir essas colunas sem restrições (agregação ou não).

   3. A seção Privacy settings lista todas as colunas com uma política de agregação aplicada. O valor Threshold indica quantas entidades devem existir para que esse valor apareça nos resultados. Por exemplo, se você definir um limite de 5 em uma coluna FIRST_NAME e o nome “Erasmus” aparecer apenas 4 vezes na tabela, todas as linhas com “Erasmus” serão filtradas antes de qualquer processamento (portanto, por exemplo, um COUNT(*) nessa tabela omitirá as 4 linhas com o tamanho de grupo abaixo do limite).

Consumidor: execute uma consulta de forma livre¶

1. Participe ou edite a clean room na UI de clean rooms.

2. Na seção Configure Analysis & Query, escolha as tabelas que serão usadas para consultas de forma livre.

3. Selecione Finish para salvar suas alterações.

4. Para executar uma consulta, selecione Run na clean room com o modelo de consulta SQL e selecione o modelo de consulta SQL.