如何让Redshift正确识别Parquet外部表中的时间戳格式

问题根源分析 #

你遇到的日期严重偏移，本质是Redshift对Parquet中时间戳列的解析逻辑和你存储的类型不匹配：要么是Parquet里的时间戳没存成标准的timestamp类型（比如存成了字符串或错误的数值类型），要么是Redshift对timestamp的解析规则和你的数据格式不兼容。

分步解决方法 #

1. 先确认Parquet文件的列类型

首先得搞清楚你的Parquet里时间戳列到底是什么类型，别光看pandas显示的表面值。用pyarrow工具查看schema：

import pyarrow.parquet as pq
# 替换成你的Parquet文件路径或S3路径
schema = pq.read_schema("s3://your-bucket/your-file.parquet")
print(schema)

输出里如果看到arbitrum_block_timestamp: string，说明你存的是字符串类型；如果是timestamp[ns]，就是标准的时间戳类型。

2. 针对字符串类型的处理

如果Parquet里是字符串格式的时间戳：

• 方案A：重新生成Parquet时存成标准timestamp类型
  修改你的Python代码，确保datetime列以原生timestamp类型写入Parquet（用pyarrow引擎更可靠）：

  # 先正确转换时间戳
  timestamp_column = next((col for col in df.columns if 'timestamp' in col), None)
  if timestamp_column:
      # 确保生成datetime64[ns]类型的列
      df['arbitrum_block_timestamp'] = pd.to_datetime(df[timestamp_column], unit='s')
  # 用pyarrow引擎写入Parquet，保留timestamp类型
  df.to_parquet("output.parquet", engine="pyarrow")
  

  之后重新创建Redshift外部表，列类型还是TIMESTAMP即可。

• 方案B：在Redshift查询时手动转换
  如果不想重新生成Parquet，先把外部表的列定义为VARCHAR，查询时用TO_TIMESTAMP函数指定格式转换：

  CREATE EXTERNAL TABLE arbitrum_schema.processed_arbitrum_blocks(
    hash VARCHAR,
    number INT,
    arbitrum_block_timestamp VARCHAR
  )
  STORED AS PARQUET
  LOCATION 's3://bloc...ocks/';
  
  -- 查询时转换为正确的时间戳
  SELECT TO_TIMESTAMP(arbitrum_block_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS arbitrum_block_timestamp
  FROM arbitrum_schema.processed_arbitrum_blocks LIMIT 5;
  

3. 针对标准timestamp类型的处理

如果Parquet里已经是timestamp[ns]类型，但Redshift还是解析错误：

• 尝试切换为TIMESTAMPTZ类型
  Redshift的TIMESTAMP是不带时区的，TIMESTAMPTZ带时区，可能是时区不匹配导致的偏移。修改外部表定义：

  CREATE EXTERNAL TABLE arbitrum_schema.processed_arbitrum_blocks(
    ...
    arbitrum_block_timestamp TIMESTAMPTZ
  )
  STORED AS PARQUET
  LOCATION 's3://bloc...ocks/';
  

• 强制转换时区
  如果你的时间戳是UTC时区，查询时显式指定时区转换：

  SELECT CONVERT_TIMEZONE('UTC', arbitrum_block_timestamp) AS arbitrum_block_timestamp
  FROM arbitrum_schema.processed_arbitrum_blocks LIMIT 5;
  

4. 终极兜底方案

如果以上都不行，直接在Python转换时生成带微秒的时间戳字符串，确保Redshift能完美解析：

if timestamp_column:
    # 转换为带微秒的字符串格式
    df['arbitrum_block_timestamp'] = pd.to_datetime(df[timestamp_column], unit='s').dt.strftime('%Y-%m-%d %H:%M:%S.%f')

然后写入Parquet，Redshift外部表定义为TIMESTAMP即可，因为这种格式正好匹配Redshift的期望。

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