执行CREATE TABLE新增列后部分站点字段返回NULL的问题排查

你执行单字段查询时结果正常：

SELECT 
 start_station_name,
 start_station_id,
 end_station_name,
 end_station_id,
FROM `casestudycyclistic-359515.DIvvyData.November2021` LIMIT 1000

但执行CREATE TABLE语句时，start_station_name、start_station_id、end_station_name、end_station_id多数值变为NULL：

CREATE TABLE `casestudycyclistic-359515.DIvvyData.November2021rev2` AS (
 SELECT
 ride_id,
 ended_at,
 (ended_at - started_at) AS ride_length,
 EXTRACT(DAYOFWEEK FROM ended_at) AS WEEKDAY,
started_at,
start_station_name,
start_station_id
end_station_name,
end_station_id,
start_lat,
start_lng,
end_lat,
end_lng,
member_casual,
FROM
`casestudycyclistic-359515.DIvvyData.November2021`
)

错误原因 #

SELECT子句里的start_station_id末尾缺少一个逗号，这会让SQL把end_station_name解析为start_station_id的别名，导致：

• 原本的start_station_id字段被重命名为end_station_name
• 真正的end_station_name和end_station_id没有被正确从原表中选取，最终出现大量NULL值
• 同时start_station_name的取值也会因为字段解析错误出现异常

修正后的代码 #

CREATE TABLE `casestudycyclistic-359515.DIvvyData.November2021rev2` AS (
 SELECT
 ride_id,
 ended_at,
 (ended_at - started_at) AS ride_length,
 EXTRACT(DAYOFWEEK FROM ended_at) AS WEEKDAY,
 started_at,
 start_station_name,
 start_station_id, -- 补上缺失的逗号
 end_station_name,
 end_station_id,
 start_lat,
 start_lng,
 end_lat,
 end_lng,
 member_casual -- 去掉末尾多余的逗号，避免语法报错
 FROM
 `casestudycyclistic-359515.DIvvyData.November2021`
)

内容的提问来源于stack exchange，提问作者Reed Amundarain