Creare tabelle

Le tabelle temporanee e quelle permanenti ti aiutano a semplificare le query, rendendole più facili da capire e gestire. Generando risultati intermedi riutilizzabili e grazie alla possibilità di mantenere i dati temporanei non aggregati, puoi ridurre il numero di risorse necessarie per eseguire le query, migliorando così il rendimento.

Le tabelle create sono soggette agli stessi controlli per la privacy statici, alle stesse funzioni consentite, e alle stesse limitazioni dell'unione dei campi applicati in Ads Data Hub. I controlli per la privacy standard vengono applicati quando i dati dell'istruzione finale SELECT vengono inviati a BigQuery.

Per creare le tabelle, utilizza la sintassi di BigQuery:

CREATE [OR REPLACE] [TEMP | TEMPORARY] TABLE TABLE_NAME
 [OPTIONS(privacy_checked_export=<true | false>)] AS query_statement;

Queste clausole non sono supportate:

  • IF NOT EXISTS
  • PARTITION BY
  • CLUSTER BY

Tabelle temporanee

Le Tabelle temporanee (temp) migliorano la leggibilità delle query e ti consentono di generare risultati intermedi che possono includere dati non aggregati.

Le tabelle temporanee:

  • Esistono solo a livello di sessione e scadono dopo l'esecuzione della query.
  • Possono essere utilizzate solo all'interno della query in cui sono state create.
  • Potrebbe archiviare dati non aggregati a cui non sono ancora stati applicati controlli della privacy
  • Vengono chiamati per nome senza alcuno spazio dei nomi richiesto

Per creare una tabella temporanea, utilizza l'istruzione CREATE TEMP TABLE. In questo esempio, viene creata una tabella temporanea per archiviare i risultati di una query e poi utilizzare la tabella temporanea in una sottoquery:

-- Get a list of creative IDs and store in a temporary table called creative_list:
CREATE TEMP TABLE creative_list AS (
  SELECT
    adgroup_id,
    advertiser_id,
    creative_id
  FROM
    adh.dv360_youtube_impressions
);
-- Return creatives with a count of impressions greater than 100
SELECT
  creative_id,
  COUNT(*) AS imps
FROM
  creative_list
WHERE
  imps > 100
GROUP BY
  creative_id;

Tabelle permanenti

Se hai bisogno di creare una tabella intermedia da utilizzare in altre query, puoi creare una tabella permanente. La sintassi è la stessa delle tabelle temporanee, ma senza la clausola TEMP. Queste tabelle durano 72 ore. Se esiste già una tabella con il nome selezionato, questa viene sovrascritta.

Le tabelle permanenti possono archiviare dati non aggregati, ma i contenuti della tabella non sono visibili se non tramite risultati di query con controllo della privacy.

Le tabelle permanenti:

  • Scadono dopo 72 ore.
  • Possono essere utilizzate al di fuori della query in cui sono state create.
  • Potrebbe archiviare dati non aggregati a cui non sono ancora stati applicati controlli della privacy
  • Vengono fatto riferimento tramite lo spazio dei nomi tmp, ma è possibile fare riferimento anche per nome nella stessa query

Per creare una tabella persistente, utilizza l'istruzione CREATE TABLE. In questo esempio, viene creata una tabella persistente per archiviare i risultati di una query e poi utilizzare la tabella permanente in una query successiva:

Query 1

-- Get a list of creative IDs and store in a persistent table called creative_list:
CREATE TABLE creative_list AS (
  SELECT
    adgroup_id,
    advertiser_id,
    creative_id
  FROM
    adh.dv360_youtube_impressions
);

-- Return the total count of impressions in the table
SELECT
  COUNT(*) AS imps
FROM
  tmp.creative_list -- Alternative: creative_list

Query 2

-- Return creatives which had more than 100 impressions
SELECT
  creative_id,
  COUNT(*) AS imps
FROM
  tmp.creative_list
WHERE
  imps > 100
GROUP BY
  creative_id;

Applicare i controlli per la privacy

Ads Data Hub supporta anche la sintassi SQL per creare tabelle di risultati intermedi soggette a controlli per la privacy.

Per creare una tabella soggetta a controlli per la privacy, aggiungi la clausola OPTIONS alla query:

OPTIONS(privacy_checked_export=true)

In questo esempio, viene creata una tabella temporanea a livello di sessione e vengono applicati i controlli per la privacy:

-- Get a list of creative IDs and store in a temporary table called creative_list:
CREATE TEMP TABLE creative_list OPTIONS(privacy_checked_export=true) AS (
  SELECT
    adgroup_id,
    advertiser_id,
    creative_id
  FROM
    adh.dv360_youtube_impressions
);

In questo esempio viene creata una tabella persistente, vengono applicati i controlli per la privacy e viene eseguita l'esportazione nel progetto Google Cloud:

-- Get a list of creative IDs and store in a persistent table called creative_list:
CREATE TABLE project_name.dataset_name.creative_list` OPTIONS(privacy_checked_export=true) AS (
  SELECT
    adgroup_id,
    advertiser_id,
    creative_id
  FROM
    adh.dv360_youtube_impressions
);

Esportare più tabelle utilizzando una sola query

Ads Data Hub supporta una sintassi SQL flessibile per esportare le tabelle di risultati visibili nei casi in cui una tabella per query non sia sufficiente. Queste tabelle sono soggette agli stessi controlli per la privacy applicati in Ads Data Hub.

La sintassi per esportare una tabella insieme all'espressione SQL principale è:

CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME
  OPTIONS(privacy_checked_export=true) AS query_statement;

Ad esempio, per esportare una tabella nella destinazione BigQuery PROJECT_NAME.DATASET.TABLE_NAME contenente un conteggio di righe per ogni ID campagna dalla tabella adh.google_ads_impressions:

CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME
  OPTIONS(privacy_checked_export=true) AS
  SELECT campaign_id, COUNT(*) AS ct
  FROM adh.google_ads_impressions
  GROUP BY campaign_id;

Il seguente esempio espande questa idea e utilizza la stessa query per esportare due tabelle in BigQuery:

CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME
  OPTIONS(privacy_checked_export=true) AS
  SELECT campaign_id, COUNT(*) AS ct
  FROM adh.google_ads_impressions
  GROUP BY campaign_id;

CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME_2
  OPTIONS(privacy_checked_export=true) AS
  SELECT advertiser_id, COUNT(*) AS ct
  FROM adh.google_ads_impressions
  GROUP BY advertiser_id;

È inoltre possibile fare riferimento alle tabelle nella stessa query in cui sono state create. Ad esempio, la seguente query restituisce due tabelle:

CREATE TABLE PROJECT_NAME.DATASET.TABLE_NAME
  OPTIONS(privacy_checked_export=true) AS
  SELECT campaign_id, COUNT(*) AS ct
  FROM adh.google_ads_impressions
  GROUP BY campaign_id;

SELECT ct FROM PROJECT_NAME.DATASET.TABLE_NAME;
  • La prima tabella viene creata in PROJECT_NAME.DATASET.TABLE_NAME,con le colonne campaign_id e ct.
  • La seconda tabella viene creata nella posizione specificata nel campo Tabella di destinazione degli argomenti del job, con la colonna ct.

Aggiungere un riepilogo delle righe filtrate

È anche possibile aggiungere un riepilogo di righe filtrate a queste tabelle. Scopri cosa sono i riepiloghi di righe filtrate.

Assegnare un nome a più tabelle in fase di runtime

Quando crei più tabelle in una query, puoi assegnare loro un nome in due modi:

  • Assegna un nome esplicito alle tabelle in SQL utilizzando il seguente formato: PROJECT_NAME.DATASET.TABLE_NAME.
  • Utilizza i parametri come segnaposto nel codice SQL, quindi assegna un nome alle tabelle in fase di esecuzione.

Creare e aggiornare le tabelle BigQuery

Ads Data Hub supporta l'utilizzo diretto di determinate istruzioni BigQuery negli script, consentendo di inserire righe in una tabella dei risultati esistente anziché generarne una nuova per ogni job. Queste istruzioni devono fare riferimento a un percorso di tabella diretto nel progetto BigQuery e non possono accedere direttamente alle tabelle o alle opzioni di Ads Data Hub (ad esempio privacy_checked_export).

Istruzioni supportate:

Query di esempio:

-- Compute new data for the current job. This table can be used with MERGE.
CREATE TEMP TABLE new_data OPTIONS(privacy_checked_export=TRUE) AS
SELECT
  DATE(event_time, @time_zone) AS event_date,
  <more grouping keys>,
  COUNT(*) AS impressions,
  <more metrics>
FROM ...
GROUP BY ALL;

-- Initialize a result table on the first run of the query, which may include
-- date partitioning and/or clustering for efficiency.
CREATE TABLE IF NOT EXISTS PROJECT_NAME.DATASET.TABLE_NAME (<columns>)
PARTITION BY event_date CLUSTER BY ...
OPTIONS(partition_expiration_days=365);

-- Atomically insert new results or update existing ones.
MERGE PROJECT_NAME.DATASET.TABLE_NAME a
USING new_data b ON a.data_date = b.data_date AND <more grouping keys>
WHEN MATCHED THEN
  UPDATE SET a.impressions = b.impressions, <more metrics>
WHEN NOT MATCHED THEN
  INSERT (<all columns>) VALUES(<all columns>);

Tieni presente che le istruzioni MERGE non supportano la modifica dello schema della tabella. Se devi aggiungere o modificare colonne in una tabella di report esistente, puoi aggiornare lo schema della tabella in BigQuery prima di eseguire un job in Ads Data Hub oppure eseguire un job Ads Data Hub per copiare la tabella con schema e opzioni aggiornati, ad esempio:

CREATE OR REPLACE TABLE PROJECT_NAME.DATASET.TABLE_NAME
PARTITION BY event_date CLUSTER BY ... OPTIONS(...) AS
SELECT
  <new columns>
FROM PROJECT_NAME.DATASET.TABLE_NAME;