target-redshift wrongly transforming single-quote to double-quote

[afonsodiniz]

Describe the bug
Hi,

I'm using Meltano to integrate Jira data into our Redshift datawarehouse.

Tap used for get data:

• https://hub.meltano.com/extractors/tap-jira--singer-io/

I have a column from Jira's Changelogs entity that is a Json array and below you can see an example of one:

[{"field": "summary", "fieldtype": "jira", "fieldId": "summary", "from": null, "fromString": null, "to": null, "toString": "A'Test Company"}]

When I run meltano --log-level=debug run tap-jira target-redshift what is written in the table is:

[{"field": "summary", "fieldtype": "jira", "fieldId": "summary", "from": null, "fromString": null, "to": null, "toString": "A"Test Company"}] with ", instead of ', which makes my pipeline to break.

To debug the issue:

• Check if was tap-jira issue: ran meltano invoke tap-jira > json.txt, which correctly wrote row "A'Test Company"

• ran meltano invoke tap-jira > json.txt, which correctly wrote row "A'Test Company"

I've looked into the code repo and I found this, that might be causing the problem:

target-redshift/target_redshift/sinks.py

Line 213 in d1048dd

{key: str(value).replace("'", '"').replace("None", "") for key, value in record.items()}

To Reproduce
Steps to reproduce the behavior:

1. meltano install tap-jira
2. meltano install target-redshift
3. configure both tap and target
4. run: meltano --log-level=debug run tap-jira target-redshift

Expected behavior
The expected behavior would be the exact same row outputed from tap, be inserted on redshift.

Desktop (please complete the following information):

• OS: macOS 14.2 (23C64)
• Browser: Chrome

Additional context
This is actually making my pipelines to break and I'd be really appreciated if this can be taken into account ASAP.

Thanks in advance.

[edgarrmondragon]

@tobiascadee any interest in a PR to address this?

[tobiascadee]

Hi! Sorry I was on parental leave for 4 weeks. I definitely have an interest in a PR to fix this. although it is a bit of a tough issue. When not using schema flattening quoting can break the s3 copy action in some cases. But I would be happy to review a possible solution!

@afonsodiniz could you include the error message?