Create tables

Page Summary

Temporary and persistent tables help streamline queries by creating reusable intermediate results.
Created tables are subject to the same privacy checks, allowed functions, and field join limitations enforced throughout Ads Data Hub.
Temporary tables exist only for the duration of the query and can store unaggregated data.
Persistent tables last for 72 hours, can be used in other queries, and can also store unaggregated data, though their contents are only visible through privacy-checked query results.
You can create tables that are subject to privacy checks by using the OPTIONS(privacy_checked_export=true) clause.

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

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 
  
 project_name 
 . 
 dataset_name 
 . 
 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 
 . 
 TABLE_NAME 
 
  
 OPTIONS 
 ( 
 privacy_checked_export 
 = 
 true 
 ) 
  
 AS 
  
 query_statement 
 ;

For example, to export a table to the BigQuery destination PROJECT_NAME.DATASET.TABLE_NAME which contains a count of rows for each campaign ID from the table 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 
 ;

The following example expands on this idea and uses the same query to export two tables to 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 
 ;

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

  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 
 
 ;

The first table is created at PROJECT_NAME.DATASET.TABLE_NAME ,with the columns campaign_id and ct .
The second table is created at the location specified in the Destination table field of the job arguments, with the column ct .

Adding a filtered row summary

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

Naming multiple tables at runtime

When creating multiple tables in a query, there are two ways to name them:

Explicitly name tables in the SQL using the following format: PROJECT_NAME.DATASET.TABLE_NAME .
Use parameters as placeholders in the SQL, and then name the tables at runtime.

Create and update BigQuery tables

Ads Data Hub supports direct usage of certain BigQuery statements in scripts, making it possible to insert rows into an existing result table rather than generate a new one for every job. These statements must reference a direct table path in your BigQuery project, and cannot directly access Ads Data Hub tables or options (such as privacy_checked_export ).

Supported statements:

Example query:

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

Note that MERGE statements don't support modifying table schema. If you need to add or change columns in an existing report table, you can either update the table schema in BigQuery before running a job in Ads Data Hub, or run an Ads Data Hub job to copy the table with updated schema and options, for example:

  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 
 
 ;