Free PHPFree PHP scripts classes and info

BigQuery: Use “temporary tables” via WITH (named subqueries)

In Google BigQuery we can define named subqueries via WITH clauses. Those WITH clauses are a very comfortable way to structure complex queries as it allows to reference those queries like actual tables later on.

Note: BigQuery also supports actcual temporary tables via CREATE TEMPORARY TABLE. See the official documention on temporary tables for further infos. This is out of scope for this snippet, though.

Code

WITH filtered_data as (
  SELECT
    id
  FROM
    table
  WHERE
    id BETWEEN 5 and 10
)
SELECT
  *
FROM
  filtered_data  

Working Example

Run on BigQuery

Open in BigQuery Console

Links

• Gist on Github
• Example on BigQuery
• Answer to "How to create temporary table in Google BigQuery" on Stackoverflow

Use cases

Named subqueries are a great way to structure complex queries and give sub-results a meaningful name. When working with partitioned tables, I always use temporary tables via WITH to make sure I restrict the query to scan only a limited number of partitions.

Conceptual example:

DECLARE from_date TIMESTAMP DEFAULT "2018-04-09";
DECLARE to_date TIMESTAMP DEFAULT "2018-04-10";

WITH huge_table_partition as(
  SELECT
    *
  FROM
    huge_table
  WHERE
    _PARTITIONTIME BETWEEN from_date AND to_date
)

SELECT
  *
FROM
  huge_table_partition