Redshift COPY加载S3 JSON数据报Invalid null byte错误如何解决

错误根因 #

• JSON格式的Redshift COPY操作原生不支持NULL AS参数，因此添加该参数后会触发不支持的操作报错
• S3源文件中对应字段包含多个转义Unicode空字符\u0000，Redshift解析JSON时会将其转换为实际空字节，而varchar类型字段不允许存储空字节，因此触发Invalid null byte - field longer than 1 byte报错

可行解决方案 #

方案1：预处理源文件 #

在执行COPY操作前，通过AWS Lambda、Glue或其他数据处理程序，批量将S3中JSON文件里的\u0000字符替换为空字符串或自定义占位值，处理完成后再执行COPY命令。

方案2：加载时实时转换（推荐） #

使用Redshift COPY命令的TRANSFORM参数，在加载过程中直接替换空字节，无需修改表结构或预处理源文件，示例命令如下：

COPY <table name> (col1, col2, 目标字段名, ...)
FROM 's3://...' 
CREDENTIALS '<credentials>' 
FORMAT AS JSON 'auto' 
TRANSFORM (目标字段名 = REPLACE(@目标字段名, CHR(0), ''))
GZIP 
TRUNCATECOLUMNS 
ACCEPTINVCHARS 
EMPTYASNULL 
TIMEFORMAT AS 'auto' 
REGION '<region>' 
manifest;

说明：TRANSFORM参数可以在COPY加载时对指定字段做自定义函数处理，此处用REPLACE将空字节（对应CHR(0)）替换为空字符串。

方案3：临时表中转清洗 #

如果你的Redshift版本不支持COPY TRANSFORM功能，可以先创建临时表，将目标字段设置为SUPER类型，完成COPY后清洗数据再插入正式表，示例操作如下：

1. 创建临时表

CREATE TEMP TABLE temp_load_table (
  col1 类型,
  col2 类型,
  target_col SUPER,
  ...
);

2. 执行COPY加载到临时表

COPY temp_load_table FROM 's3://...' 
CREDENTIALS '<credentials>' 
FORMAT AS JSON 'auto' 
GZIP 
TRUNCATECOLUMNS 
ACCEPTINVCHARS 
EMPTYASNULL 
TIMEFORMAT AS 'auto' 
REGION '<region>' 
manifest;

3. 清洗后插入正式表

INSERT INTO 正式表名 (col1, col2, target_col, ...)
SELECT 
  col1,
  col2,
  REPLACE(target_col::VARCHAR(255), CHR(0), '') AS target_col,
  ...
FROM temp_load_table;

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