Crear tablas

Las tablas temporales y persistentes te ayudan a optimizar las consultas, lo que facilita su comprensión y mantenimiento. Si creas resultados intermedios que puedes reutilizar y te brindas la opción de mantener los datos temporales sin agregar, puedes reducir los recursos necesarios para ejecutar consultas y mejorar el rendimiento.

Las tablas creadas están sujetas a las mismas verificaciones de privacidad, funciones permitidas y limitaciones de combinación de campos estáticas que se aplican en todo el Centro de Datos de Anuncios. Cuando los datos de la instrucción SELECT final se envían a BigQuery, se aplican las verificaciones de privacidad estándar.

Para crear tablas, sigue la sintaxis de BigQuery:

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

No se admiten las siguientes cláusulas:

  • IF NOT EXISTS
  • PARTITION BY
  • CLUSTER BY

Tablas temporales

Las tablas temporales (o temp) mejoran la legibilidad de las consultas y te permiten crear resultados intermedios que pueden incluir datos sin agregar.

Tablas temporales:

  • Solo existen a nivel de la sesión y vencen después de la ejecución de la búsqueda.
  • Solo se puede usar dentro de la consulta en la que se creó.
  • Es posible que almacene datos no agregados a los que aún no se les aplicaron verificaciones de privacidad.
  • Se hace referencia a ellos por nombre sin ningún espacio de nombres obligatorio.

Para crear una tabla temporal, usa la declaración CREATE TEMP TABLE. En este ejemplo, se crea una tabla temporal para almacenar los resultados de una consulta y, luego, se usa la tabla temporal en una subconsulta:

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

Tablas persistentes

Si necesitas crear una tabla intermedia para usarla en otras consultas, puedes crear una tabla persistente. La sintaxis es la misma que para las tablas temporales, sin la cláusula TEMP. Estas tablas duran 72 horas. Si ya existe una tabla con el nombre seleccionado, se reemplazará.

Las tablas persistentes pueden almacenar datos sin agregar, pero el contenido de la tabla no es visible, excepto a través de los resultados de las consultas verificadas por privacidad.

Tablas persistentes:

  • Vence después de 72 horas
  • Se puede usar fuera de la consulta que lo creó.
  • Es posible que almacene datos no agregados a los que aún no se les aplicaron verificaciones de privacidad.
  • Se hace referencia a ellos a través del espacio de nombres tmp, pero también se puede hacer referencia a ellos por nombre en la misma consulta.

Para crear una tabla persistente, usa la sentencia CREATE TABLE. En este ejemplo, se crea una tabla persistente para almacenar los resultados de una consulta y, luego, se usa la tabla persistente en una consulta posterior:

Consulta 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

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

Aplica verificaciones de privacidad

El Centro de Datos de Anuncios también admite una sintaxis de SQL para crear tablas de resultados intermedios que están sujetas a verificaciones de privacidad.

Para crear una tabla sujeta a verificaciones de privacidad, agrega la cláusula OPTIONS a tu consulta:

OPTIONS(privacy_checked_export=true)

En este ejemplo, se crea una tabla temporal a nivel de la sesión y se aplican verificaciones de privacidad:

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

En este ejemplo, se crea una tabla persistente, se aplican verificaciones de privacidad y se exporta a tu proyecto de 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
);

Exporta varias tablas con una sola consulta

Ads Data Hub admite una sintaxis de SQL flexible para exportar tablas de resultados visibles en los casos en que una tabla por consulta no es suficiente. Estas tablas están sujetas a las mismas verificaciones de privacidad que se aplican en todo Ads Data Hub.

La sintaxis para exportar una tabla junto con la expresión SQL principal es la siguiente:

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

Por ejemplo, para exportar una tabla al destino de BigQuery PROJECT_NAME.DATASET.TABLE_NAME, que contiene un recuento de filas para cada ID de campaña de la tabla adh.google_ads_impressions, haz lo siguiente:

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;

En el siguiente ejemplo, se amplía esta idea y se usa la misma consulta para exportar dos tablas a 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;

También se puede hacer referencia a las tablas en la misma consulta en la que se crearon. Por ejemplo, la siguiente consulta genera dos tablas:

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 primera tabla se crea en PROJECT_NAME.DATASET.TABLE_NAME,con las columnas campaign_id y ct.
  • La segunda tabla se crea en la ubicación especificada en el campo Tabla de destino de los argumentos del trabajo, con la columna ct.

Cómo agregar un resumen de filas filtradas

También es posible agregar un resumen de las filas filtradas a estas tablas. Obtén más información sobre los resúmenes de filas filtrados.

Cómo asignar nombres a varias tablas en el tiempo de ejecución

Cuando creas varias tablas en una consulta, existen dos formas de nombrarlas:

  • Nombra las tablas de forma explícita en SQL con el siguiente formato: PROJECT_NAME.DATASET.TABLE_NAME.
  • Usa parámetros como marcadores de posición en el código SQL y, luego, asigna nombres a las tablas en el tiempo de ejecución.

Crea y actualiza tablas de BigQuery

Ads Data Hub admite el uso directo de ciertas sentencias de BigQuery en las secuencias de comandos, lo que permite insertar filas en una tabla de resultados existente en lugar de generar una nueva para cada trabajo. Estas sentencias deben hacer referencia a una ruta de acceso directa a la tabla en tu proyecto de BigQuery y no pueden acceder directamente a las tablas ni a las opciones de Ads Data Hub (como privacy_checked_export).

Declaraciones admitidas:

Consulta de ejemplo:

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

Ten en cuenta que las instrucciones MERGE no admiten la modificación del esquema de la tabla. Si necesitas agregar o cambiar columnas en una tabla de informes existente, puedes actualizar el esquema de la tabla en BigQuery antes de ejecutar un trabajo en Ads Data Hub o ejecutar un trabajo de Ads Data Hub para copiar la tabla con el esquema y las opciones actualizados, por ejemplo:

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;