Freiform-SQL-Abfragen auf Clean Room-Tabellen ausführen

You can enable consumers to run free-form SQL queries on selected datasets in your clean room using either the clean room API or UI.

Free-form queries using the clean rooms API

You can configure a clean room to allow collaborators to query specific linked datasets from outside the clean room. Collaborators can run free-form queries on these datasets in any environment where they can access the clean room, including Snowsight or Snowflake CLI. Free-form datasets behave as standard, read-only views that can be queried using SQL, Python, or other supported Snowflake languages.

Bemerkung

When you grant a consumer permission to run free-form SQL queries in a clean room, that consumer can query the data from that clean room against any other data that they can access from their account.

Richtlinien und Unterstützung für differentielle Privatsphäre

Wenn Sie Clean Room-Daten für Freiformabfragen freigeben, werden alle Snowflake-Richtlinien beachtet. Clean Room-Richtlinien (Verknüpfungsrichtlinien, Spaltenrichtlinien) werden in Freiformabfragen nicht durchgesetzt.

Clean room differential privacy is not enforced on data exposed to free-form queries. This includes both Snowflake differential privacy and clean room differential privacy.

Ermöglichung von Freiformabfragen

Wichtig

If a clean room was created before June, 2025 the provider must patch their clean room by running the following code to enable free-form queries in that clean room:

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

Provider steps

Der Anbieter unternimmt die folgenden Schritte, um Datensätze in einem Clean Room den Teilnehmern in einem Clean Room über Freiformabfragen zur Verfügung zu stellen:

  1. Erstellen Sie den Clean Room auf die übliche Weise.

  2. Register and link the datasets into the clean room in the standard way using the API. Note that currently your data must be registered using the API; you cannot register views in the clean room UI and use them for free-form queries. You should apply any Snowflake aggregation, join, or other policies before sharing your data outside the clean room.

  3. Call provider.enable_workflows_for_consumers to allow specific users free-form access to the tables that you will specify in the next step. You must name this work flow freeform_sql.

  4. Rufen Sie provider.enable_datasets_for_workflow auf, um festzulegen, welche Datensätze im Clean Room abgefragt werden können.

  5. Fügen Sie Ihre Teilnehmer auf die übliche Weise hinzu, indem Sie provider.add_consumers aufrufen.

  6. Veröffentlichen Sie Ihren Clean Room.

  7. If you want to revoke permission to query these tables, you can do this at the user level by calling provider.disable_consumer_run_analysis or provider.remove_consumers, at the dataset level by calling library.unregister_objects or library.unregister_db, or by deleting the clean room.

If a clean room already exists and data is registered, you can simply call provider.enable_workflows_for_consumers and provider.enable_datasets_for_workflow to expose the specified datasets to the specified users.

The following code creates three sample tables and applies Snowflake policies to them, creates a new clean room, links in the tables, and grants free-form query access to those tables for clean room collaborators via the clean room. The highlighted code shows where you enable free-form queries in the 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.
-- The flow name must be 'freeform_sql'
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);
Copy

Consumer steps

After the provider has published a clean room with free-form SQL work flows, consumers with access to that clean room can run queries against the exposed views by following these steps:

  1. Install the clean room in the standard way. No need to link in consumer data, as the consumer will access their data in their local environment, not in the clean room.

  2. Rufen Sie consumer.get_provider_freeform_sql_views auf, um die Freiform-SQL-Ansichten aufzulisten, die für das aktuelle Konto und die Rolle verfügbar sind.

  3. Ausführen von Standard-SQL-Abfragen in den Daten.

-- 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;
Copy

Freiformabfragen in der Clean Room UI

Mit der SQL-Abfragevorlage in einem Clean Room können Verbraucher eine Freiform-SQL-Abfrage schreiben, um Daten im Clean Room abzufragen. Wenn Sie die SQL-Abfragevorlage verwenden, müssen Verbraucherabfragen bestimmte Anforderungen erfüllen, um erfolgreich Ergebnisse zu liefern. Diese Anforderungen werden dadurch bestimmt, wie der Datenanbieter seine Tabellen mit Datenschutzrichtlinien schützt.

Wenn Sie einen Clean Room in der UI erstellen oder aktualisieren, fügen Sie die SQL-Abfragevorlage zu Ihrem Clean Room hinzu und konfigurieren sie wie unten beschrieben.

Anbieter: Clean Room erstellen und Richtlinien festlegen

  1. Erstellen Sie einen Clean Room oder bearbeiten Sie einen vorhandenen Clean Room, und geben Sie Tabellen oder Ansichten für Ihre Tabelle an.

  2. Während der Erstellung des Clean Room angegebene Verknüpfungsrichtlinien werden bei der Verwendung der SQL-Abfragevorlage ignoriert, aber bei allen anderen Vorlagen beachtet.

  3. Wählen Sie unter Configure Analysis & Query Horizontal » SQL Query aus.

  4. Legen Sie im Abschnitt für SQL Query-Einstellungen die folgenden Eigenschaften fest:

    1. Under Tables, select tables that should be available to clean room collaborators in free-form queries. By default, aggregation policies do not need to be applied. To control which columns can be projected, and which must be aggregated, you must set column policies in the next section.

      Wichtig

      In Freiform-Abfragen in der Clean Rooms-UI können Sie keine Tabelle mit einem Namen verwenden, der auf „LIST“ (Groß- oder Kleinschreibung) endet.

    2. Legen Sie im Abschnitt Column Policies die folgenden Werte fest, um zu steuern, ob oder wie Ihre Spalten in einer Abfrage verwendet werden können:

      1. Aggregation policy columns: Geben Sie an, welche Spalten aggregiert werden müssen, damit sie in den Abfrageergebnissen erscheinen. Wenn Sie eine Aggregationsrichtlinie auf eine Spalte anwenden und eine Spalte in einer Abfrage verwendet wird, dann müssen die Ergebnisse aggregiert werden. Alle hier aufgeführten Spalten werden dem Abschnitt Privacy settings hinzugefügt.

      2. Projection policy columns: Spalten mit einer Projektionsrichtlinie können nicht projiziert werden (d. h. in eine SELECT-Anweisung aufgenommen werden). Verbraucher können jedoch nach einer Spalte mit einer Projektionsrichtlinie filtern oder verknüpfen.

      3. Fully permitted columns: Der Verbraucher kann diese Spalten auswählen (SELECT), filtern oder ohne Einschränkungen (Aggregation oder andere) verknüpfen.

    3. Der Abschnitt Privacy settings listet alle Spalten auf, auf die eine Aggregationsrichtlinie angewendet wurde. Der Wert Threshold gibt an, wie viele Entitäten vorhanden sein müssen, damit dieser Wert in den Ergebnissen erscheint. Wenn Sie z. B. für eine Spalte FIRST_NAME einen Schwellenwert von 5 festlegen und der Name „Erasmus“ nur 4 Mal in der Tabelle vorkommt, werden alle Zeilen mit „Erasmus“ herausgefiltert, bevor irgendeine Verarbeitung stattgefunden hat (so wird z. B. eine COUNT(*) in einer solchen Tabelle die 4 Zeilen mit der Gruppengröße unterhalb des Schwellenwerts auslassen).

Verbraucher: Freiformabfrage ausführen

  1. Verknüpfen oder bearbeiten Sie den Clean Room in der Clean Room UI.

  2. Wählen Sie im Abschnitt Configure Analysis & Query die Tabellen aus, die Sie für Freiformabfragen verwenden möchten.

    Wichtig

    In Freiform-Abfragen in der Clean Rooms-UI können Sie keine Tabelle mit einem Namen verwenden, der auf „LIST“ (Groß- oder Kleinschreibung) endet.

  3. Wählen Sie Finish aus, um Ihre Änderungen zu speichern.

  4. Um eine Abfrage auszuführen, wählen Sie Run im Clean Room mit der SQL-Abfragevorlage und wählen Sie die SQL-Abfragevorlage.

Spalten für Verknüpfung und Filterung auswählen

Sie können auf jeder Spalte, für die eine Richtlinie gilt oder die vollständig zugelassen ist, verknüpfen und filtern. Um festzustellen, ob eine Spalte verknüpft oder in einem Filter verwendet werden kann:

  1. Im Abschnitt Query Configurations finden Sie die Kachel Tables.

  2. Verwenden Sie die Dropdown-Liste, um eine Tabelle auszuwählen. Sie können alle aufgeführten Spalten verknüpfen und filtern.

Projektionsspalten auswählen

Bei Abfragen, die mit der SQL-Abfragevorlage ausgeführt werden, gibt es Einschränkungen hinsichtlich der Spalten, die projiziert (in einer SELECT-Anweisung verwendet) werden können.

Um festzustellen, ob Ihre Abfrage eine Spalte projizieren kann:

  1. Im Abschnitt Query Configurations finden Sie die Kachel Tables.

  2. Verwenden Sie die Dropdown-Liste, um eine Tabelle auszuwählen.

  3. Suchen Sie nach Spalten, die mit einer Projektionsrichtlinie gekennzeichnet sind, was bedeutet, dass Sie sie nicht projizieren können. Sie können alle Spalten projizieren, mit Ausnahme derjenigen, die mit dem Projektionsrichtlinien-Label versehen sind.

Aggregationsanforderungen

Wenn der Anbieter einer Spalte eine Aggregationsrichtlinie zugewiesen hat, müssen alle Abfragen, die mit der SQL-Abfragevorlage ausgeführt werden, aggregierte Ergebnisse liefern.

So bestimmen Sie, ob Ihre Abfrage Ergebnisse aggregieren muss:

  1. Im Abschnitt Query Configurations finden Sie die Kachel Tables.

  2. Verwenden Sie die Dropdown-Liste, um eine Tabelle auszuwählen.

  3. Suchen Sie nach Spalten, die mit einer Aggregationsrichtlinie gekennzeichnet sind. Wenn es mindestens ein Aggregationsrichtlinien-Label gibt, müssen Sie in Ihrer Abfrage eine Aggregation verwenden.

Eine Anleitung, wie Sie eine erfolgreiche Abfrage für Daten schreiben, die durch eine Aggregationsrichtlinie geschützt sind, finden Sie unter

Anforderungen grafisch darstellen

Damit Snowflake ein Diagramm erstellen kann:

  • Die Ergebnistabelle muss mindestens eine Spalte mit Messwerten (numerisch) und eine Spalte mit Dimensionen (Kategorie) enthalten

  • Der Measure-Spaltename muss das folgende Präfix oder Suffix haben (Groß-/Kleinschreibung wird nicht berücksichtigt):

    • Präfixe für Spaltennamen:

      • COUNT

      • SUM

      • AVG

      • MIN

      • MAX

      • OUTPUT

      • OVERLAP

    • Suffix des Spaltennamens:

      • _OVERLAP

Snowflake erstellt ein Diagramm, das die erste in Frage kommende Measure-Spalten und die erste Dimensionsspalte in einer Ergebnistabelle verwendet.

Einschränkungen

  • Eine ORDER BY-Klausel hat keinen Einfluss darauf, wie die Ergebnisse der Analyse angezeigt werden.

Beispielabfragen

In diesem Abschnitt erfahren Sie, was eine Abfrage enthalten kann und was nicht, wenn Sie eine Analyse mit der SQL-Abfragevorlage durchführen.

Abfragen ohne eine Aggregationsfunktion

Unter bestimmten Umständen können Sie Rückgabewerte ohne Verwendung einer Aggregationsfunktion zurückgeben.

Erlaubte

Nicht erlaubt

 
SELECT gender, regions
  FROM TABLE sample_db.demo.customer
  GROUP BY gender, region;
Copy
 
SELECT gender, regions
  FROM TABLE sample_db.demo.customer;
Copy
Allgemeine Tabellenausdrücke (Common Table Expressions, (CTEs))

Erlaubte

Nicht erlaubt

 
WITH audience AS
  (SELECT COUNT(DISTINCT t1.hashed_email),
    t1.status
    FROM provider_db.overlap.customers t1
    JOIN consumer_db.overlap.customers t2
      ON t1.hashed_email = t2.hashed_email
    GROUP BY t1.status);

SELECT * FROM audience;
Copy
 
WITH audience AS
  (SELECT t1.hashed_email,
    t1.status
    FROM provider_db.overlap.customers quoted t1
    JOIN consumer_db.overlap.customers t2
      ON t1.hashed_email = t2.hashed_email
    GROUP BY t1.status)

SELECT * FROM audience
Copy
CREATE, ALTER, TRUNCATE

Eine Abfrage kann nicht CREATE, ALTER oder TRUNCATE verwenden.

Abfrage mit Verknüpfungen

Erlaubte

 
SELECT p.education_level,
  c.status,
  AVG(p.days_active),
  COUNT(DISTINCT p.age_band)
  FROM  samooha_sample_database.demo.customers c
  INNER JOIN
  samooha_sample_database.demo.customers p
    ON  c.hashed_email = p.hashed_email
  GROUP BY ALL;
Copy
DATE_TRUNC

Erlaubte

 
SELECT COUNT(*),
  DATE_TRUNC('week', date_joined) AS week
  FROM consumer_sample_database.audience_overlap.customers
  GROUP BY week;
Copy
Bezeichner in Anführungszeichen

Erlaubte

 
SELECT COUNT(DISTINCT t1."hashed_email")
  FROM provider_sample_database.audience_overlap."customers quoted" t1
  INNER JOIN
  consumer_sample_database.audience_overlap.customers t2
    ON t1."hashed_email" = t2.hashed_email;
Copy
Sprache: Deutsch