如何在Redshift外部表中存储带时区的TIMESTAMP时间戳数据

Redshift Spectrum创建外部表时不支持TIMESTAMP WITH TIME ZONE（timestamptz）类型，因此需要通过以下两种替代方案实现需求：

方案1：字符串存储+查询时转换（保留原始格式） #

适合需要严格保留2018-07-11T17:39:53.000Z原始字符串格式的场景

• 步骤1：修改外部表DDL，将created_date定义为VARCHAR类型

CREATE EXTERNAL TABLE schema.test
(
    user_id BIGINT,
    created_by BIGINT,
    created_date VARCHAR(30) -- 修改为字符串类型匹配Parquet中存储的字符串格式
)
PARTITIONED BY (application_id varchar(100), aggreate_type varchar(200), created DATE)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS 
    INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' 
LOCATION 's3://<>' 
table properties ('compression_type'='snappy', 'write.parallel'='off', 'write.maxfilesize.mb'='128');

• 步骤2：查询时按需转换为带时区的时间戳

SELECT 
  user_id,
  created_by,
  TO_TIMESTAMP(created_date, 'YYYY-MM-DDTHH:MI:SS.FFFZ') AS created_date_tz
FROM schema.test;

方案2：UTC标准TIMESTAMP存储+查询时追加时区（性能更优） #

适合追求查询性能、不需要保留原始字符串格式的场景，存储和查询效率远高于字符串方案

• 步骤1：修改UNLOAD逻辑，直接输出UTC标准TIMESTAMP类型，无需拼接字符串

UNLOAD ('SELECT 
    user_id
    ,created_by
    ,created_date AT TIME ZONE \'UTC\' AS created_date -- 统一转成UTC标准时间戳输出
FROM intake.ods_user_resource_role
WHERE res_role_id = \'185814211\'')
TO 's3://<>'
PARTITION BY (application_id, aggreate_type, created)
access_key_id '<>' secret_access_key '<>'
ENCRYPTED FORMAT AS PARQUET  ALLOWOVERWRITE  PARALLEL OFF  
;

• 步骤2：外部表DDL定义created_date为TIMESTAMP类型

CREATE EXTERNAL TABLE schema.test
(
    user_id BIGINT,
    created_by BIGINT,
    created_date TIMESTAMP -- 存储UTC标准时间戳
)
PARTITIONED BY (application_id varchar(100), aggreate_type varchar(200), created DATE)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS 
    INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' 
LOCATION 's3://<>' 
table properties ('compression_type'='snappy', 'write.parallel'='off', 'write.maxfilesize.mb'='128');

• 步骤3：查询时转换为带时区的时间戳

SELECT 
  user_id,
  created_by,
  created_date AT TIME ZONE 'UTC' AS created_date_tz
FROM schema.test;

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