Create a Temporary Table in Bigquery

 



Here is the code:

Begin

-- Find the top 100 names from the year 2020.  CREATE TEMP TABLE rocky_name (name STRING)

  AS

    SELECT name

    FROM bigquery-public-data.usa_names.usa_1990_current

    WHERE year = 2020

    ORDER BY number DESC LIMIT 10;

-- Which names appear as words in rocky plays?

  SELECT    name AS rocky_name    FROM rocky_test

    WHERE name IN (  SELECT word    FROM bigquery-public-data.samples.rocky );

End

The above Query need a sessions else it will show an error as below:

Use of CREATE TEMPORARY TABLE requires a script or session

Just simply we create a temporary table with full control of the life cycle  using DDL script along with a table expire timestamp.

CREATE TABLE [PROJECTID].[DATASET].rocky_test

OPTIONS(

  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), 

                                     INTERVAL 1 DAY)

) AS (

      SELECT state, name, sum(number) as namecnt

      FROM bigquery-public-data.usa_names.usa_1990_current

      GROUP BY state, name, number

)


Using the below Query you can get your table records:

select * from [PROJECTID].[DATASET].rocky_test


Table will be delete/expire in a specific  time :


SELECT ddl FROM test.INFORMATION_SCHEMA.TABLES WHERE table_name = “rocky_test”;

CREATE TABLE [PROJECTID].[DATASET].rocky_test

(

  state STRING,

  name STRING,

  namecnt INT64

)

OPTIONS(

  expiration_timestamp=TIMESTAMP "2022–04–29T08:37:10.015Z"

);



Here is the reference Documentation

Post a Comment

0 Comments

Youtube Channel Image
TechAdvice Subscribe To watch more Blogging Tutorials
Subscribe