What is the syntax for creating a permanent table within the dataset in a BigQuery SQL query?

Question

Summarize the problem:

I have a temporary table as a result of my query in order to combine 12 months' data by the use of WITH statement. I can save the temp. table manually by saving the view as a permanent table which I then export to use in my visualization app.

I would like to know the appropriate syntax to add to my query so that the procedure gets run seamlessly.

Describe what you’ve tried:

I tried using CREATE TABLE, EXECUTE IMMEDIATE (concat..., etc, but got errors.

When appropriate, show some code:

Could you please share with me the possible correct code to add to my below query (I shortened the code) temptable as my temporary table. say permtable to be my permanent table as project.data_set.permtable

Thank you.

WITH

  temptable AS ( 
        
        #May 2021
  SELECT
    ride_id,
    rideable_type,
    CAST(started_at AS TIMESTAMP ) AS started_at,
    CAST(ended_at AS TIMESTAMP ) AS ended_at,
    start_station_name,
    CAST(start_station_id AS STRING ) AS start_station_id,
    end_station_name,
    CAST(end_station_id AS STRING ) AS end_station_id,
    start_lat,
    start_lng,
    end_lat,
    end_lng,
    member_casual,
    timestamp_diff (ended_at,started_at, minute) AS trip_duration,
    EXTRACT(DAYOFWEEK FROM started_at) AS day_of_week
  FROM
    `project.data_set.table1`
  UNION ALL
    #June 2021
  SELECT
    ride_id,
    rideable_type,
    CAST(started_at AS TIMESTAMP ) AS started_at,
    CAST(ended_at AS TIMESTAMP ) AS ended_at,
    start_station_name,
    CAST(start_station_id AS STRING ) AS start_station_id,
    end_station_name,
    CAST(end_station_id AS STRING ) AS end_station_id,
    start_lat,
    start_lng,
    end_lat,
    end_lng,
    member_casual,
    timestamp_diff (ended_at,started_at, minute) AS trip_duration,
    EXTRACT(DAYOFWEEK FROM started_at) AS day_of_week
  FROM
    `project.data_set.table2` )

SELECT * FROM combined_rides
 WHERE
    NOT( start_station_id IS NULL
      OR end_station_id IS NULL
      OR start_lat IS NULL
      OR end_lat IS NULL
      OR start_station_name LIKE '%CHECKING%'
      OR end_station_name LIKE '%CHECKING%'
      OR trip_duration < 1 )

Answer 1

You can use the CREATE TABLE statement to accomplish so:

CREATE TABLE IF NOT EXISTS project.data_set.permtable
AS
WITH temptable AS (    
        #May 2021
  SELECT
    ride_id,
    rideable_type,
    CAST(started_at AS TIMESTAMP ) AS started_at,
    CAST(ended_at AS TIMESTAMP ) AS ended_at,
    start_station_name,
    CAST(start_station_id AS STRING ) AS start_station_id,
    end_station_name,
    CAST(end_station_id AS STRING ) AS end_station_id,
    start_lat,
    start_lng,
    end_lat,
    end_lng,
    member_casual,
    timestamp_diff (ended_at,started_at, minute) AS trip_duration,
    EXTRACT(DAYOFWEEK FROM started_at) AS day_of_week
  FROM
    `project.data_set.table1`
  UNION ALL
    #June 2021
  SELECT
    ride_id,
    rideable_type,
    CAST(started_at AS TIMESTAMP ) AS started_at,
    CAST(ended_at AS TIMESTAMP ) AS ended_at,
    start_station_name,
    CAST(start_station_id AS STRING ) AS start_station_id,
    end_station_name,
    CAST(end_station_id AS STRING ) AS end_station_id,
    start_lat,
    start_lng,
    end_lat,
    end_lng,
    member_casual,
    timestamp_diff (ended_at,started_at, minute) AS trip_duration,
    EXTRACT(DAYOFWEEK FROM started_at) AS day_of_week
  FROM
    `project.data_set.table2` )

SELECT * FROM combined_rides
 WHERE
    NOT( start_station_id IS NULL
      OR end_station_id IS NULL
      OR start_lat IS NULL
      OR end_lat IS NULL
      OR start_station_name LIKE '%CHECKING%'
      OR end_station_name LIKE '%CHECKING%'
      OR trip_duration < 1 );

And there you go, you will now have a permtable table inside the specified project and dataset!