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 )
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!