CODEWITHSUNDEEP
google-bigquerytemp-tablestemp
Animesh Jain
Animesh Jain

Reputation: 11

Volatile(Temp) Tables in BQ

I am trying to find a way to create Volatile/Temp tables in BQ as we can do in Teradata. I tried begin/end and it worked but cant access the temp table via select * just after running it as getting error saying "Table must be qualified with a dataset (e.g. dataset.table)." Please suggest, TIA

tried created temp table via create temp table [tablename] with begin/end, it created but cant access the table. I am planning to create 10+ temp tables, later union them and create a final schema table

Upvotes: -1

Views: 158

Answers (2)

Nithish Kumar Ramisetty
Nithish Kumar Ramisetty

Reputation: 164

BigQuery uses temporary tables to cache query results that aren't written to a permanent table. The tables are created in a special dataset and named randomly. You can also create temporary tables for your own use within multi-statement queries and sessions.

You can refer to a temporary table by name for the duration of the current multi-statement query. This includes temporary tables created by a procedure within the multi-statement query. You cannot share temporary tables. Temporary tables reside in hidden _script% datasets with randomly generated names. Listing datasets article describes how to list hidden datasets.

After a query finishes, the temporary table exists for up to 24 hours. To view table structure and data, go to the BigQuery console, click Personal history, and choose the query that created the temporary table. Then, in the Destination table row, click Temporary table.

You can check more information about temporary and permanent tables in the following documentation.

Upvotes: 1

Kolban
Kolban

Reputation: 15276

You can create a temporary table using

CREATE TEMP TABLE <tablename> ...

Here is the link to the SQL documentation. From your question, it sounds like a reference error and not a table creation error. In BigQuery, tables are identified by:

<project>.<dataset>.<table>

In your question, you didn't declare what you used as a table reference nor what you used as a query reference. My suggestion is to explicitly declare at least the dataset and table name in your queries and don't assume defaults. That way there will be unambiguous reference.

Upvotes: 0

Related Questions