Redshift COPY导入JSON：如何将字符串"null"转为TIMESTAMPTZ类型NULL值

使用Redshift的COPY命令导入JSON文件到表时，JSON中shipDate字段的值是带双引号的字符串"null"（格式如shipDate: "null"），Redshift会将其识别为字符串类型，尝试转换为TIMESTAMPTZ类型时失败，stl_load_errors日志提示无效数据。

相关信息如下：

• 表DDL语句：

CREATE TABLE IF NOT EXISTS quantum.quantum_customer_order_update
(
    order_id     VARCHAR(50) DISTKEY,
    ship_date    TIMESTAMPTZ
)
    DISTSTYLE KEY
    SORTKEY (order_id);

• 示例JSON数据：

{"orderId" : "88e52685ee8097b91d182b4fc4f64adf", "shipDate" : "null"}

• 当前使用的COPY命令：

COPY orders(order_id, ship_date)
    FROM 's3://bucket/orders.json'
    ACCESS_KEY_ID ''
    SECRET_ACCESS_KEY ''
FORMAT AS JSON 's3://bucket/json_paths.json'
TIMEFORMAT 'auto';

需求：让COPY命令将shipDate字段的"null"字符串存储为NULL值。

方法1：用COPY命令的NULL AS参数全局处理 #

直接在COPY命令中添加NULL AS 'null'参数，告诉Redshift将字符串'null'解析为NULL值，这样JSON中的"null"会被自动转换为TIMESTAMPTZ类型的NULL，避免转换失败。

修改后的COPY命令：

COPY orders(order_id, ship_date)
    FROM 's3://bucket/orders.json'
    ACCESS_KEY_ID ''
    SECRET_ACCESS_KEY ''
FORMAT AS JSON 's3://bucket/json_paths.json'
TIMEFORMAT 'auto'
NULL AS 'null';

注意：该参数对所有字段生效，如果其他字段也存在"null"字符串，同样会被转换为NULL。

方法2：通过JSON路径文件针对单个字段处理 #

如果仅需要对ship_date字段做转换（不影响其他字段的"null"字符串），可以修改你的JSON路径文件，使用Redshift支持的JSON路径函数实现转换。

假设原JSON路径文件内容为：

{
    "jsonpaths": [
        "$.orderId",
        "$.shipDate"
    ]
}

修改后的JSON路径文件：

{
    "jsonpaths": [
        "$.orderId",
        "coalesce(nullif($.shipDate, 'null'), null)"
    ]
}

这里nullif($.shipDate, 'null')会把值为'null'的字段转为NULL，coalesce则确保最终返回合法的NULL值，从而匹配TIMESTAMPTZ类型的字段。

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