How to use "temporary tables" via WITH (named subqueries) in Google BigQuery

Posted by Pascal Landau on 2020-05-29 13:00:00

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

BigQuery Console: How to use temporay tables via WITH named subqueries example

Links

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

Wanna stay in touch?

Since you ended up on this blog, chances are pretty high that you're into Software Development (probably PHP, Laravel, Docker or Google Big Query) and I'm a big fan of feedback and networking.

So - if you'd like to stay in touch, feel free to shoot me an email with a couple of words about yourself and/or connect with me on LinkedIn or Twitter or simply subscribe to my RSS feed or go the crazy route and subscribe via mail and don't forget to leave a comment :)

Subscribe to posts via mail

We use Mailchimp as our newsletter provider. By clicking subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.
Waving bear

Comments