CODEWITHSUNDEEP
google-bigquery
Vasundhara
Vasundhara

Reputation: 745

How to create temporary table in Google BigQuery

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.

Upvotes: 68

Views: 214497

Answers (11)

Simon
Simon

Reputation: 791

Update September 2022:

As per the documentation, you can create a temporary table like:

    CREATE TEMP TABLE continents(name STRING, visitors INT64)
    AS
     select  geo.continent, count(distinct user_pseudo_id)  as Continent_Visitors
    FROM `firebaseProject.dataset.events_date`
    group by geo.continent order by Continent_Visitors desc;
    SELECT * from continents;
Drop table continents;

Upvotes: 2

shary.sharath
shary.sharath

Reputation: 709

I followed Google's official document while learning UDF and encountered the issue: use of create temporary table requires a script or session

Erroneous script: 

CREATE TEMP TABLE users
AS  SELECT 1 id, 10 age
UNION ALL SELECT 2, 30
UNION ALL SELECT 3, 10;

Solution:

BEGIN
    CREATE TEMP TABLE users
    AS  SELECT 1 id, 10 age
    UNION ALL SELECT 2, 30
    UNION ALL SELECT 3, 10;
END;

Upvotes: 3

DBear
DBear

Reputation: 111

It's 2022, and if you type the codes to create a TEMP table in BQ's interactive windows, it will not work. Probably will display below error message:

enter image description here

Vaguely it will give you an idea that your interactive windows should be tied with some session. There is the official documentation on how to create sessions etc.,

The short and easy method for me was go to MORE menu of the Google BigQuery Interactive windows, select Query Settings

enter image description here

It will display below SS (as of 2022 April) enter image description here

Enable/click Use session mode and SAVE. That's it enjoy your Temporary Tables :D

Upvotes: 7

Towsif Ahamed Labib
Towsif Ahamed Labib

Reputation: 686

To create and store your data on the fly, you can specify optional _SESSION qualifier to create temporary table.

CREATE TEMP TABLE _SESSION.tmp_01
AS
 SELECT name FROM `bigquery-public-data`.usa_names.usa_1910_current
 WHERE year = 2017 
;

Here you can create the table from a complex query starting after 'AS' and the temporary table will be created at once and will be deleted after 24 hours.

To access the table,

select * from _SESSION.tmp_01;

Upvotes: 1

Yun Zhang
Yun Zhang

Reputation: 5503

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;

Upvotes: 27

Ayaz Hoda
Ayaz Hoda

Reputation: 71

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

Upvotes: 4

Kuldeep Pal
Kuldeep Pal

Reputation: 121

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 ;

Upvotes: 12

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

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.

Upvotes: 34

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

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

Upvotes: 41

Fardream
Fardream

Reputation: 664

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;

Upvotes: 13

Feczo
Feczo

Reputation: 598

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

Upvotes: 2

Related Questions