I ran once code for similar data but it was “Unrecognized name: <column_name>”. Details below:
I ran below code on Bigquery:
create table `Cyclist_data.2020` as
select
ride_id, rideable_type, started_at, ended_at, member_casual,
extract(time FROM started_at) as start_time,
extract(day from started_at) as start_day,
extract(month from started_at) as start_month,
extract(year from started_at) as start_year,
extract(dayofweek from started_at) as start_weekday,
extract(time FROM ended_at) as end_time,
extract(day from ended_at) as end_day,
extract(month from ended_at) as end_month,
extract(year from ended_at) as end_year,
extract(dayofweek from ended_at) as end_weekday,
ST_distance(
st_geogpoint(start_lng, start_lat),
st_geogpoint(end_lng, end_lat),
false
) as distance_m
from
`Cyclist_data.*`
WHERE
ENDS_WITH(_TABLE_SUFFIX, '_2020');
create table `Cyclist_data.2021` as
select
ride_id, rideable_type, started_at, ended_at, member_casual,
extract(time FROM started_at) as start_time,
extract(day from started_at) as start_day,
extract(month from started_at) as start_month,
extract(year from started_at) as start_year,
extract(dayofweek from started_at) as start_weekday,
extract(time FROM ended_at) as end_time,
extract(day from ended_at) as end_day,
extract(month from ended_at) as end_month,
extract(year from ended_at) as end_year,
extract(dayofweek from ended_at) as end_weekday,
ST_distance(
st_geogpoint(start_lng, start_lat),
st_geogpoint(end_lng, end_lat),
false
) as distance_m
from
`Cyclist_data.*`
WHERE
ENDS_WITH(_TABLE_SUFFIX, '_2021');
Eveything with 2021 is exactly like 2020. But when 2020‘s code complete, 2021 was Unrecognized name: start_lng. I’m sure there are start_lng columns in _2021 tables. Below is run log and _2021‘s schema.
Thanks for all answer!!
4
Your first statement creates a table with the same name pattern matching your ‘from’ clause in the 2nd query, but it doesn’t have start_lng column. Maybe you can put both between BEGIN TRANSACTION and COMMIT TRANSACTION statements?
BEGIN TRANSACTION;
create table `Cyclist_data.2020` as
select
ride_id, ...
from
`Cyclist_data.*`
WHERE
ENDS_WITH(_TABLE_SUFFIX, '_2020');
create table `Cyclist_data.2021` as
select
ride_id, ...
from
`Cyclist_data.*`
WHERE
ENDS_WITH(_TABLE_SUFFIX, '_2021');
COMMIT TRANSACTION;