Snowflake中Citibike VARCHAR日期字段转换与数据加载问题求助

一、查询时转换VARCHAR日期字段 #

你的日期格式为MM/DD/YYYY HH24:MI（示例：6/1/2013 0:00），只需用TO_TIMESTAMP指定匹配格式串完成转换，即可正常使用date_trunc等日期函数：

修正后的统计查询 #

select 
  date_trunc('hour', TO_TIMESTAMP(starttime, 'MM/DD/YYYY HH24:MI')) as "date",
  Count(*) as "num_trips",
  avg(tripduration)/60 as "avg duration (mins)",
  avg(haversine(start_station_latitude, start_station_longitude, 
    end_station_latitude, end_station_longitude)) as "avg distance (KM)"
from trips2
group by 1 order by 1;

常用转换场景示例 #

• 仅提取日期部分：

SELECT TO_DATE(starttime, 'MM/DD/YYYY') AS start_date
FROM trips2;

• 转换为指定格式的字符串：

SELECT TO_VARCHAR(TO_TIMESTAMP(starttime, 'MM/DD/YYYY HH24:MI'), 'YYYY-MM-DD HH24:MI') AS formatted_datetime
FROM trips2;

你之前的尝试存在这些问题：

• 拆分日期和时间再拼接属于多余操作，且格式串匹配错误
• 使用TO_DATE(STARTTIME, '')传递空格式串，无法触发解析逻辑
• 错误将字段名starttime用单引号包裹为字符串常量，导致解析的是字面量而非字段值

二、解决数据加载问题 #

1. 配置正确的文件格式加载 #

创建匹配CSV日期格式的文件格式，再用于数据加载：

CREATE OR REPLACE FILE FORMAT citibike_csv_format
TYPE = CSV
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
DATE_FORMAT = 'MM/DD/YYYY'
TIMESTAMP_FORMAT = 'MM/DD/YYYY HH24:MI'
SKIP_HEADER = 1; -- 若CSV包含表头行则启用

使用该格式加载数据到含TIMESTAMP_NTZ字段的表：

COPY INTO trips2 (starttime, tripduration, ...) -- 需列出所有字段
FROM @your_stage/path/to/citibike.csv
FILE_FORMAT = citibike_csv_format;

2. 排查加载错误 #

若加载失败，可先验证错误数据：

COPY INTO trips2
FROM @your_stage/path/to/citibike.csv
FILE_FORMAT = citibike_csv_format
VALIDATION_MODE = RETURN_ERRORS;

若存在脏数据，可跳过错误行并记录日志：

COPY INTO trips2
FROM @your_stage/path/to/citibike.csv
FILE_FORMAT = citibike_csv_format
ON_ERROR = CONTINUE;

3. 修正现有表字段类型 #

无需重新加载时，可直接修改现有表的字段类型：

ALTER TABLE trips2 ALTER COLUMN starttime SET DATA TYPE TIMESTAMP_NTZ
USING TO_TIMESTAMP(starttime, 'MM/DD/YYYY HH24:MI');

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