Freiform-SQL-Abfragen auf Clean Room-Tabellen ausführen¶
Sie können Verbrauchern die Möglichkeit geben, Freiform-SQL-Abfragen auf ausgewählten Datensätzen in einem Clean Room durchzuführen, indem Sie entweder die Clean Room API oder UI verwenden.
Freiformabfragen in der Clean Room API¶
Sie können einen Clean Room so konfigurieren, dass Teilnehmer bestimmte Datensätze von außerhalb des Clean Room abfragen können. Teilnehmer können in jeder Umgebung, in der sie Zugriff auf den Clean Room haben, einschließlich Snowsight oder die Snowflake-API, Freiformabfragen auf diesen Datensätzen durchführen. Freiform-Datensätze verhalten sich wie standardmäßige, schreibgeschützte Ansichten, die mit SQL, Python oder anderen unterstützten Snowflake-Sprachen abgefragt werden können.
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.
Differentielle Privatsphäre wird bei Daten, die für Freiformabfragen freigegeben sind, nicht erzwungen. Dies umfasst sowohl differentielle Privatsphäre von Snowflake als auch differentielle Privatsphäre für Clean Rooms.
Ermöglichung von Freiformabfragen¶
Wichtig
Wenn ein Clean Room vor Juni 2025 erstellt wurde, muss der Anbieter den folgenden Patch installieren, um Freiformabfragen in diesem Clean Room mit der API zu ermöglichen:
USE ROLE SAMOOHA_APP_ROLE;
CALL samooha_by_snowflake_local_db.provider.patch_cleanroom($cleanroom_name,TRUE);
Anbieter
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:
Erstellen Sie den Clean Room auf die übliche Weise.
Registrieren und verknüpfen Sie die Datensätze im Clean Room auf die übliche Weise. Beachten Sie, dass Ihre Daten derzeit mithilfe der API registriert werden müssen. Sie können keine Ansichten in der Clean Room UI registrieren und diese für Freiform-Abfragen verwenden. Sie sollten alle Snowflake-Aggregations-, Verknüpfungs- oder andere Richtlinien anwenden, bevor Sie Ihre Daten außerhalb des Clean Room weitergeben.
Rufen Sie
provider.enable_workflows_for_consumers
auf, um bestimmten Benutzern freien Zugriff auf die Tabellen zu gewähren, die Sie im nächsten Schritt angeben.Rufen Sie
provider.enable_datasets_for_workflow
auf, um den Benutzern des vorherigen Schritts freien Zugriff auf die hier angegebenen Datensätze zu ermöglichen.Fügen Sie Ihre Teilnehmer auf die übliche Weise hinzu, indem Sie
provider.add_consumers
aufrufen.Veröffentlichen Sie Ihren Clean Room.
Wenn Sie die Berechtigung zur Abfrage dieser Tabellen entziehen möchten, können Sie dies auf Benutzerebene durch den Aufruf von
provider.disable_consumer_run_analysis
oderprovider.remove_consumers
, auf Ansichtsebene durch den Aufruf vonlibrary.unregister_objects
oderlibrary.unregister_db
oder durch Löschen des Clean Room tun.
Wenn ein Clean Room bereits existiert und die Daten registriert sind, können Sie einfach provider.enable_workflows_for_consumers
und provider.enable_datasets_for_workflow
aufrufen, um die angegebenen Tabellen den angegebenen Benutzern zugänglich zu machen.
Der folgende Code erstellt drei Beispieltabellen, legt einen neuen Clean Room an, verknüpft die Tabellen und gewährt den Clean Room-Teilnehmern über den Clean Room Zugriff auf diese Tabellen anhand von Freiformabfragen.
----------------- 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_FREEFORM.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_FREEFORM.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);
Verbraucher
Nachdem der Anbieter einen Clean Room mit Freiform-SQL-Workflows veröffentlicht hat, können Verbraucher mit Zugriff auf diesen Clean Room Abfragen auf den freigegebenen Ansichten durchführen.
Installieren Sie den Clean Room auf die übliche Weise. Es ist nicht nötig, Daten zu verknüpfen, da der Verbraucher direkt auf seine Tabellen zugreifen kann.
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.Führen Sie die SQL-Standardabfragen auf den Daten aus. Die Richtlinien des Produzenten werden für die Daten durchgesetzt.
-- 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;
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¶
Erstellen Sie einen Clean Room oder bearbeiten Sie einen vorhandenen Clean Room, und geben Sie Tabellen oder Ansichten für Ihre Tabelle an.
Während der Erstellung des Clean Room angegebene Verknüpfungsrichtlinien werden bei der Verwendung der SQL-Abfragevorlage ignoriert, aber bei allen anderen Vorlagen beachtet.
In Configure Analysis & Query wählen Sie Horizontal » SQL Query aus.
Legen Sie im Abschnitt für SQL Query-Einstellungen die folgenden Eigenschaften fest:
Wählen Sie unter Tables Tabellen aus, die den Teilnehmern im Clean Room in Freiformabfragen zur Verfügung stehen sollen. Standardmäßig können alle Spalten in den ausgewählten Tabellen projiziert werden und die Aggregationsrichtlinien müssen nicht angewendet werden. Um zu steuern, welche Spalten projiziert werden können und welche aggregiert werden müssen, müssen Sie im nächsten Abschnitt Spaltenrichtlinien festlegen.
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:
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.
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.
Fully permitted columns: Der Verbraucher kann diese Spalten auswählen (SELECT), filtern oder ohne Einschränkungen (Aggregation oder andere) verknüpfen.
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¶
Verknüpfen oder bearbeiten Sie den Clean Room in der Clean Room UI.
Wählen Sie im Abschnitt Configure Analysis & Query die Tabellen aus, die Sie für Freiformabfragen verwenden möchten.
Wählen Sie Finish aus, um Ihre Änderungen zu speichern.
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:
Im Abschnitt Query Configurations finden Sie die Kachel Tables.
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:
Im Abschnitt Query Configurations finden Sie die Kachel Tables.
Verwenden Sie die Dropdown-Liste, um eine Tabelle auszuwählen.
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:
Im Abschnitt Query Configurations finden Sie die Kachel Tables.
Verwenden Sie die Dropdown-Liste, um eine Tabelle auszuwählen.
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
Abfrageanforderungen für Aggregationsrichtlinien. In diesem Abschnitt können Sie zum Beispiel feststellen, dass die Funktionen MIN und MAX zur Aggregation den Anforderungen der Abfrage nicht genügen und daher nicht verwendet werden können.
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;
SELECT gender, regions FROM TABLE sample_db.demo.customer;
- 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;
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
- 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 sample_database_preprod.demo.customers c INNER JOIN sample_database_preprod.demo.customers p ON c.hashed_email = p.hashed_email GROUP BY ALL;
- DATE_TRUNC
Erlaubte
SELECT COUNT(*), DATE_TRUNC('week', date_joined) AS week FROM consumer_sample_database.audience_overlap.customers GROUP BY week;
- 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;