Amazon Glue加载数据到Redshift时Decimal字段导致重复执行失败

问题场景 #

我有一个简单的Amazon Glue任务，负责将S3中的CSV数据加载到Redshift，中间通过Transform步骤重命名字段并修改数据类型。首次执行基本正常，数据成功入库，但后续所有执行都失败。核心问题是：Glue首次能正确创建Redshift表，但表已存在时，所有被转换为decimal类型的字段都会触发异常逻辑。

源CSV文件内容 #

Text value,Average whatever,Another string,Just a number
A1,2.2,test,5
A2,5,test2,7

转换逻辑与自动生成代码 #

以下为可视化任务自动生成的代码，未手动编辑：

# Script generated for node Amazon S3
AmazonS3_node1710618800725 = glueContext.create_dynamic_frame.from_options(format_options={"quoteChar": "\"", "withHeader": True, "separator": ","}, connection_type="s3", format="csv", connection_options={"paths": ["s3://<source-s3-bucket>/test/gonna_fail/data.csv"]}, transformation_ctx="AmazonS3_node1710618800725")

# Script generated for node Change Schema
ChangeSchema_node1710691042153 = ApplyMapping.apply(frame=AmazonS3_node1710618800725, mappings=[("Text value", "string", "text_value", "string"), ("Average whatever", "string", "average_whatever", "decimal"), ("Another string", "string", "another_string", "string"), ("Just a number", "string", "just_a_number", "decimal")], transformation_ctx="ChangeSchema_node1710691042153")

# Script generated for node Amazon Redshift
AmazonRedshift_node1710618808047 = glueContext.write_dynamic_frame.from_options(frame=ChangeSchema_node1710691042153, connection_type="redshift", connection_options={"redshiftTmpDir": "s3://aws-glue-assets-xxx-eu-central-1/temporary/", "useConnectionProperties": "true", "dbtable": "raw_data.gonna_fail", "connectionName": "serverless-redshift", "preactions": "DROP TABLE IF EXISTS raw_data.gonna_fail; CREATE TABLE IF NOT EXISTS raw_data.gonna_fail (text_value VARCHAR, average_whatever DECIMAL, another_string VARCHAR, just_a_number DECIMAL);"}, transformation_ctx="AmazonRedshift_node1710618808047")

执行日志分析 #

首次执行 #

Redshift日志记录：

7:26:26 PM  CREATE TABLE IF NOT EXISTS "raw_data"."gonna_fail" ("text_value" VARCHAR(MAX), "average_whatever" DECIMAL(10,2), "another_string" VARCHAR(MAX), "just_a_number" DECIMAL(10,2)) DISTSTYLE EVEN

表创建成功，但随后出现错误Expected command status BEGIN, got CREATE TABLE，30秒后任务重试成功：

7:26:56 PM  CREATE TABLE IF NOT EXISTS "raw_data"."gonna_fail" ("text_value" VARCHAR(MAX), "average_whatever" DECIMAL(10,2), "another_string" VARCHAR(MAX), "just_a_number" DECIMAL(10,2)) DISTSTYLE EVEN 
7:26:56 PM  DROP TABLE IF EXISTS raw_data.gonna_fail 
7:26:56 PM   CREATE TABLE IF NOT EXISTS raw_data.gonna_fail (text_value VARCHAR, average_whatever DECIMAL, another_string VARCHAR, just_a_number DECIMAL) 
7:26:56 PM  COPY "raw_data"."gonna_fail" ("text_value","average_whatever","another_string","just_a_number") FROM 's3://aws-glue-assets-xxx-eu-central-1/temporary/63e30430-67f0-4ab2-b539-22180ae2920b/manifest.json' FORMAT AS CSV NULL AS '@NULL@' manifest CREDENTIALS '' 

第二次执行 #

每个decimal字段都会被添加新列，列名为原字段名拼接数据类型：

7:29:19 PM  ALTER TABLE raw_data.gonna_fail add "average_whatever_decimal(10,2)" DECIMAL(10,2) default NULL; 
7:29:19 PM   ALTER TABLE raw_data.gonna_fail add "just_a_number_decimal(10,2)" DECIMAL(10,2) default NULL; 

此次加载失败，30秒后重试：
自动执行两次CREATE TABLE语句（包含preactions）：

7:29:54 PM  CREATE TABLE IF NOT EXISTS "raw_data"."gonna_fail" ("text_value" VARCHAR(MAX), "average_whatever" DECIMAL(10,2), "another_string" VARCHAR(MAX), "just_a_number" DECIMAL(10,2), "just_a_number_decimal(10,2)" DECIMAL(10,2), "average_whatever_decimal(10,2)" DECIMAL(10,2)) DISTSTYLE EVEN 

preactions执行：

7:29:54 PM  DROP TABLE IF EXISTS raw_data.gonna_fail 
7:29:54 PM   CREATE TABLE IF NOT EXISTS raw_data.gonna_fail (text_value VARCHAR, average_whatever DECIMAL, another_string VARCHAR, just_a_number DECIMAL) 

生成错误的COPY语句：

7:29:54 PM  COPY "raw_data"."gonna_fail" ("text_value","average_whatever","another_string","just_a_number","just_a_number_decimal(10,2)","average_whatever_decimal(10,2)") FROM 's3://aws-glue-assets-xxx-eu-central-1/temporary/d46ca4ae-86cc-4444-addd-6c54c376a2a1/manifest.json' FORMAT AS CSV NULL AS '@NULL@' manifest CREDENTIALS ''

Spark重试3次后任务报错，Glue中显示错误：

Caused by: com.amazon.redshift.util.RedshiftException: ERROR: column "just_a_number_decimal(10,2)" of relation "gonna_fail" does not exist

注：检查frame的.schema().fields，未发现这些额外的错误字段。

内容的提问来源于stack exchange，提问作者Michał Malus