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
0 Comments