How to create temporary table in Google BigQuery

Question

Is there any way to create a temporary table in Google BigQuery through:

SELECT * INTO <temp table> 
FROM <table name> 

same as we can create in SQL?

For complex queries, I need to create temporary tables to store my data.

Answer 1

2018 update - definitive answer with DDL

With BigQuery's DDL support you can create a table from the results a query - and specify its expiration at creation time. For example, for 3 days:

#standardSQL
CREATE TABLE `fh-bigquery.public_dump.vtemp`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
) AS
SELECT corpus, COUNT(*) c
FROM `bigquery-public-data.samples.shakespeare`
GROUP BY corpus

• Docs: https://cloud.google.com/bigquery/docs/data-definition-language

Answer 2

2019 update -- With BigQuery scripting (Beta now), CREATE TEMP TABLE is officially supported. See public documentation here.

2018 update: https://stackoverflow.com/a/50227484/132438

Every query in bigquery creates a temporary table with the results. Temporary unless you give a name to the destination table, then you are in control of its lifecycle.

Use the api to see the temporary table name, or name your tables when querying.

Answer 3

A temporary table can be created with WITH in the "New Standard SQL". See WITH clause.

An example given by Google:

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;

Answer 4

2019 update -- With BigQuery scripting, CREATE TEMP TABLE is officially supported. See public documentation here.

CREATE TEMP TABLE Example
(
  x INT64,
  y STRING
);

INSERT INTO Example
VALUES (5, 'foo');

INSERT INTO Example
VALUES (6, 'bar');

SELECT *
FROM Example;

Answer 5

To create a temporary table, use the TEMP or TEMPORARY keyword when you use the CREATE TABLE statement and use of CREATE TEMPORARY TABLE requires a script , so its better to start with begin statement.

Begin CREATE TEMP TABLE <table_name> as select * from <table_name> where <condition>; End ;

Answer 6

Example of creating temp tables in GCP bigquery

CREATE TABLE  `project_ID_XXXX.Sales.superStore2011` 
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
) AS
SELECT 
  Product_Name,Product_Category, SUM(profit) Total_Profit,  FORMAT_DATE("%Y",Order_Date) AS Year
FROM 
  `project_ID_XXXX.Sales.superStore` 
WHERE
  FORMAT_DATE("%Y",Order_Date)="2011"
GROUP BY 
  Product_Name,Product_Category,Order_Date
ORDER BY 
   Year, Total_Profit DESC
LIMIT 5 

Answer 7

Take the SQL sample of

SELECT name,count FROM mydataset.babynames 
  WHERE gender = 'M' ORDER BY count DESC LIMIT 6 INTO mydataset.happyhalloween;

The easiest command line equivalent is

bq query --destination_table=mydataset.happyhalloween \ 
"SELECT name,count FROM mydataset.babynames WHERE gender = 'M' \
ORDER BY count DESC LIMIT 6"

See the documentation here: https://cloud.google.com/bigquery/bq-command-line-tool#createtablequery