SQS to S3 (Parse body to JSON)

Hi All - Apologies if this the incorrect channel. I’m new to Airbyte so currently building our first connection between SQS and S3 and my question is around the body of the message being a string - Is there a way to parse the body to JSON. We have previously used another solution http://Tray.io where the connector performed this functionality or has anybody any advice on how to perform this pre-S3

TIA

Hey can you help us understand what is the current process if you are using source (source-amazon-sqs) and as destination (destination-s3)?

Yes of course.

We have a 3rd party that post message to a SQS queue which contains data we use in our Snowflake DW.

Within the current workflow with Tray it pulls the message down from SQS with any attributes we need but also parses the body of the message into JSON.

If you pull a message from SQS via the Airbyte connector or using AWS CLI then the body of the message is a string which is escaped

see example

{
    "_airbyte_ab_id": "01d47543-e26f-4c2e-b7c7-8ae4269a9baf",
    "_airbyte_emitted_at": 1653388090000,
    "_airbyte_data": {
        "id": "8ed6e48e-78d6-4062-93a5-cac297ace902",
        "body": "{\"externalId\":\"fc3a6f4c-fd0d-4fc4-9ff7-3b6596031b13\",\"payload\":{\"_id\":\"6288ad66ad298506de94d15d\",\"createdAt\":\"2022-05-21T09:14:33.251Z\",\"date\":\"2022-05-21T08:02:48.000Z\",\"deleted\":false,\"distance\":5048,\"duration\":1723,\"isValid\":true,\"points\":51,\"type\":\"Running\",\"uid\":\"612884acc9777d0007976e11\",\"updatedAt\":null,\"timezone\":1,\"provider\":\"garmin\",\"climb\":50,\"descent\":58,\"deletedAt\":null},\"resourceType\":\"activity\",\"status\":\"CREATED\"}",
        "attributes": null
    }
}

I was wondering if this could be completed before it gets posted to a S3 bucket.

Hey I got that. we don’t have a out of box solution but there are 2 ways you can do this.

  1. You can use our API to change the connection catalog https://airbyte-public-api-docs.s3.us-east-2.amazonaws.com/rapidoc-api-docs.html#post-/v1/connections/update and change the body from string to object and give the respective keys so that in normalisation we can create a new table for this.
  2. Other way is you can handle this in custom DBT

Hi there from the Community Assistance team.
We’re letting you know about an issue we discovered with the back-end process we use to handle topics and responses on the forum. If you experienced a situation where you posted the last message in a topic that did not receive any further replies, please open a new topic to continue the discussion. In addition, if you’re having a problem and find a closed topic on the subject, go ahead and open a new topic on it and we’ll follow up with you. We apologize for the inconvenience, and appreciate your willingness to work with us to provide a supportive community.