Create tables

AI-generated Key Takeaways

• Ads Data Hub allows the creation of temporary and persistent tables to streamline queries and improve performance.

• Temporary tables are session-specific, expire after execution, and bypass aggregation requirements; persistent tables last 72 hours and are subject to aggregation.

• Both table types can have privacy checks applied using OPTIONS(privacy_checked_export=true) for added data protection.

• Ads Data Hub enables exporting multiple result tables within a single query, offering flexibility for data retrieval.

• Created tables adhere to the same privacy checks, allowed functions, and field join limitations as standard Ads Data Hub queries.

Temporary tables and persistent tables help you streamline queries, making them easier to understand and maintain. By creating intermediate results that you can reuse, while giving you the option to keep the temporary data unaggregated, you can reduce the resources required to execute queries, improving performance.

Created tables are subject to the same static privacy checks, allowed functions, and field join limitations enforced throughout Ads Data Hub. Standard privacy checks apply when the data from the final SELECT statement is output to BigQuery.

To create tables, follow the BigQuery syntax:

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

These clauses are not supported:

• IF NOT EXISTS
• PARTITION BY
• CLUSTER BY

Temporary tables

Temporary (or temp) tables improve query readability, and allow you to create intermediate results that may include unaggregated data.

Temp tables:

• Only exist at the session level, and expire after query execution
• Can only be used inside of the query in which it was created
• May store unaggregated data that has not yet applied privacy checks
• Are referenced by name without any required namespace

To create a temp table, use the CREATE TEMP TABLE statement. This example creates a temp table to store the results of a query, then uses the temp table in a subquery:

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

Persistent tables

If you need to create an intermediate table to use in other queries, you can create a persistent table. The syntax is the same as for temp tables, without the TEMP clause. These tables last for 72 hours. If a table with the selected name already exists, it is overwritten.

Persistent tables may store unaggregated data, but the contents of the table are not visible except through privacy-checked query results.

Persistent tables:

• Expire after 72 hours
• Can be used outside of the query that created it
• May store unaggregated data that has not yet applied privacy checks
• Are referenced through the tmp namespace, but can also be referenced by name in the same query

To create a persistent table, use the CREATE TABLE statement. This example creates a persistent table to store the results of a query, then uses the persistent table in a later query:

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;

Note: When you run a query that consists of only CREATE TABLE statements, you still need to enter a destination table. The destination table is ignored during execution.

Apply privacy checks

Ads Data Hub also supports a SQL syntax to create intermediate result tables that are subject to privacy checks.

To create a table that is subject to privacy checks, add the OPTIONS clause to your query:

OPTIONS(privacy_checked_export=true)

This example creates a session-level temp table and applies privacy checks:

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

This example creates a persistent table, applies privacy checks, and exports it to your Google Cloud project:

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

Export multiple tables using a single query

Ads Data Hub supports a flexible SQL syntax to export viewable result tables in cases where one table per query is not enough. These tables are subject to the same privacy checks enforced throughout Ads Data Hub.

The syntax for exporting a table alongside the main SQL expression is:

CREATE TABLE <project_name.dataset_name.table_name>
  OPTIONS(privacy_checked_export=true) AS query_statement;

Note: The CREATE TABLE syntax without OPTIONS(privacy_checked_export=true) creates a non-viewable persistent table in the tmp.* namespace which is deleted after 72 hours (this time is subject to change).

For example, to export a table to the BigQuery destination myproject.mydataset.mytable which contains a count of rows for each campaign ID from the table adh.google_ads_impressions:

CREATE TABLE `myproject.mydataset.mytable` OPTIONS(privacy_checked_export=true) AS
  SELECT campaign_id, COUNT(*) AS ct
  FROM adh.google_ads_impressions
  GROUP BY campaign_id;

Tables may also be referenced in the same query in which they were created. For example, the query:

CREATE TABLE `myproject.mydataset.mytable` OPTIONS(privacy_checked_export=true) AS
  SELECT campaign_id, COUNT(*) AS ct
  FROM adh.google_ads_impressions
  GROUP BY campaign_id;
SELECT ct FROM `myproject.mydataset.mytable`;

outputs two tables:

• One at myproject.mydataset.mytable, with the columns campaign_id and ct
• One at the location specified in the Destination table field of the job arguments, with the column ct

It is also possible to add a filtered-row summary to these tables. Read about filtered row summaries.