AWS Glue: ETL Struct field (Dynamo DB) to Aurora Postgres json field

Question

Several issues:
1. AWS Auto-generates code that lists the struct type as an "Object" which fails:
ChangeSchema_node1685651062990 = ApplyMapping.apply(
    frame=AWSGlueDataCatalog_node1685651050820,
    mappings=[
        ("company_id", "string", "company_id", "string"),
        ("request_model", "string", "request_model", "string"),
        ("created", "string", "created", "timestamp"),
        ("response_id", "string", "response_id", "string"),
        ("usage_total_tokens", "long", "usage_total_tokens", "long"),
        ("usage_request_tokens", "long", "usage_request_tokens", "long"),
        ("usage_refinement_tokens", "long", "usage_refinement_tokens", "long"),
        ("api_key_id", "string", "api_key_id", "string"),
        ("usage_response_tokens", "long", "usage_response_tokens", "long"),
        ("user_id", "string", "user_id", "string"),
        ("response_model", "string", "response_model", "string"),
        ("response_text", "string", "response_text", "string"),
        ("id", "string", "id", "string"),
        ("request_payload", "object", "request_payload", "string"),
        ("request_id", "string", "request_id", "string"),
        ("bot_id", "string", "bot_id", "string"),
    ],
    transformation_ctx="ChangeSchema_node1685651062990",
)

To clear this I have to change "object" to "string"

2. But when attemptoing to write to the PostgresSQL database it fails because the target field "request_payload" is a json field, so I get the folllowing eeror: "An error occurred while calling o135.pyWriteDynamicFrame. ERROR: column "request_payload" is of type json but expression is of type character varying"

Is there a way to cast the field as json before it's written to PostgreSQL DB?

Answer

you can modify your AWS Glue script

```
import pyspark.sql.functions as F
dfc = ChangeSchema_node1685651062990.toDF()  # convert dynamic frame to dataframe
dfc = dfc.withColumn("request_payload", F.to_json("request_payload"))  # convert struct to json string
ChangeSchema_node1685651062990 = DynamicFrame.fromDF(dfc, glueContext, "ChangeSchema_node1685651062990")  # convert back to dynamic frame
```

AWS Glue: ETL Struct field (Dynamo DB) to Aurora Postgres json field

Relevanter Inhalt