Postgres Source - Unable to handle jsonb[] (regression) #29172

haithem-souala · 2023-08-07T17:54:38Z

Connector Name

destination-bigquery

Connector Version

1.7.2

What step the error happened?

During the sync

Revelant information

Normalization: raw data (json)
Source: Postgres v3.0.2

In table A (at Postgres) we have a field tags (jsonb[]), so the data stored, looks like this:

{"{\"key\": \"xxx_field\", \"value\": \"DEFAULT\", \"yyy_field\": true}","{\"key\": \"zzz_field\", \"value\": \"obfuscated\", \"rrr_field\": false}"}

Or like this:

{"{\"key\": \"xxx_field\", \"value\": \"DEFAULT\", \"yyy_field\": true}"}

The BigQuery Destination v2 connector, write the data in the _airbyte_data like this:

{
  "tags": "{\"{\\\"key\\\": \\\"origin\\\", \\\"value\\\": \\\"obfuscated\\\"}\"}"
}

In the final destination table, airbyte is unable to handle the tags field content, so it will be ignored with this message in the _airbyte_meta :

{"errors":["Problem with tags"]}

Expected bahaviour:
Just set the field content in the final destination table.

Relevant log output

No response

Contribute

Yes, I want to contribute

The text was updated successfully, but these errors were encountered:

evantahler · 2023-08-08T20:19:34Z

Grooming:

we will research sources sending JSONB[]s and see what destinations V1 does. destinations v2 is different, we will fix it, if not, we will wait until our JSONB project in the next few months.

evantahler · 2023-08-08T20:21:19Z

@haithem-souala - can you please share some examples of what this JSONb[] output looks like in BigQuery for both Destinations V1 and V2? Ideally, both a screenshot and CSV dump would be helpful, as we want to be very careful with escaping and quotes. Email would also work - I'm {firstname}@airbyte.io

haithem-souala · 2023-08-09T08:28:48Z

Fyi, in destination v1, it's working fine.

Source (Postgres):

                                                                                             tags                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"{\"key\": \"pricing\", \"value\": \"DEFAULT\", \"applyToOrchestration\": true}"}
 {"{\"key\": \"pricing\", \"value\": \"DEFAULT\", \"applyToOrchestration\": true}"}
 {"{\"key\": \"selected_carrier\", \"value\": \"obfuscated_val\", \"applyToOrchestration\": false}"}
 {"{\"key\": \"pricing\", \"value\": \"DEFAULT\", \"applyToOrchestration\": true}"}
 {"{\"key\": \"selected_carrier\", \"value\": \"obfuscated_val\", \"applyToOrchestration\": false}"}
 {"{\"key\": \"pricing\", \"value\": \"DEFAULT\", \"applyToOrchestration\": true}"}
 {"{\"key\": \"pricing\", \"value\": \"obfuscated_val\", \"applyToOrchestration\": true}"}
 {"{\"key\": \"carrierMethod\", \"value\": \"obfuscated_val\", \"applyToOrchestration\": true}"}
 {"{\"key\": \"pricing\", \"value\": \"DEFAULT\", \"applyToOrchestration\": true}","{\"key\": \"selected_carrier\", \"value\": \"obfuscated_val\", \"applyToOrchestration\": false}"}
 {"{\"key\": \"pricing\", \"value\": \"DEFAULT\", \"applyToOrchestration\": true}"}

Destination V1

_airbyte_raw_table_a:

{
    "tags": [
        "{\"key\": \"installation_date\", \"value\": \"2019-09-20\", \"applyToOrchestration\": false}"
    ]
}

table_a:

[{
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"obfuscated_val\\\", \\\"applyToOrchestration\\\": true}\""]
}, {
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"DEFAULT\\\"}\""]
}, {
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"DEFAULT\\\", \\\"applyToOrchestration\\\": true}\""]
}, {
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"DEFAULT\\\", \\\"applyToOrchestration\\\": true}\""]
}, {
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"obfuscated_val\\\", \\\"applyToOrchestration\\\": true}\""]
}, {
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"obfuscated_val\\\", \\\"applyToOrchestration\\\": true}\""]
}, {
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"obfuscated_val\\\", \\\"applyToOrchestration\\\": true}\""]
}, {
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"DEFAULT\\\", \\\"applyToOrchestration\\\": true}\""]
}, {
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"DEFAULT\\\", \\\"applyToOrchestration\\\": true}\""]
}, {
  "tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"DEFAULT\\\", \\\"applyToOrchestration\\\": true}\""]
}]

//CSV
bquxjob_37795b49_189d9620d09.csv

//Screenshot

haithem-souala · 2023-08-16T08:56:21Z

Any ETA? This issue prevent us from migrating the last DB.

cynthiaxyin · 2023-08-16T23:01:11Z

We were able to repro this issue in BigQuery and Snowflake and are investigating what the fix should be.

It looks like Postgres arrays are defined with curly braces instead of brackets, and v2 is not recognizing this syntax? And maybe there is a discrepancy in when the data is deserialized?

source data (curly brace annotation + all single quotes) {"{\"key\": \"pricing\", \"value\": \"DEFAULT\", \"applyToOrchestration\": true}"}
v1 raw data (bracket annotation + still single quotes) {"tags": ["{\"key\": \"installation_date\", \"value\": \"2019-09-20\", \"applyToOrchestration\": false}"]}
v1 normalized data (bracket annotation + inner triple quotes) {"tags": ["\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"obfuscated_val\\\", \\\"applyToOrchestration\\\": true}\""]}
v2 raw data (curly brace annotation + inner triple quotes) {"tags":"{\"{\\\"key\\\": \\\"pricing\\\", \\\"value\\\": \\\"DEFAULT\\\", \\\"applyToOrchestration\\\": true}\"}"}

Running the SQL that gets output in the V2 logs e.g. JSON_QUERY(`_airbyte_data`, '$.tags')), the result does seem to be better (actually an array) by switching out the curly braces for the brackets.

Normalization seemed to previously convert arrays to strings around here:

edgao · 2023-08-17T19:52:59Z

@haithem-souala are there any differences (other than the destinations v2 toggle) between your v1 and v2 connections? Specifically: is the v1 connection using CDC and the v2 connection using standard replication?

I ran a v1 sync in standard replication and got the same behavior you're seeing in v2:

And ran a v2 sync with CDC, which seems to have worked:

I think this is a bug in source-postgres, trying to find the relevant issue.

edgao · 2023-08-17T20:00:20Z

couldn't find an existing issue; submitted #29551

haithem-souala · 2023-08-17T21:12:49Z

The V1 and V2 are both standard replication.
V1: cursor based (table field)
V2: Xmin

The source-postgres version in V1 -> 1.0.30
The source-postgres version in V2 -> 3.0.2

I hope that will help debugging the bug.

edgao · 2023-08-17T21:42:18Z

interesting! This looks like a source-postgres regression between those versions; can you try downgrading the v2 connection to source-postgres:1.0.30? I ran a cursor-based sync with 1.0.30 + bigquery v2 and got some good results:

And I'll update the source-postgres issue to note that this is a regression.

(in the meantime, I'm bisecting source-postgres versions to find the newest version where jsonb[] works as expected)

edgao · 2023-08-17T22:00:12Z

looks like 1.0.42 is the highest version of source-postgres that supports jsonb[] correctly, if you want to give it a try

haithem-souala · 2023-08-18T12:16:05Z

Unfortunately, i cannot downgrade source-postgres to 1.0.42, as it might affect other streams.

edgao · 2023-08-18T15:07:17Z

might affect other streams

can you explain some more on this? My understanding is that the two major releases between 1.x and 3.x were upgrading the underlying debezium library in 2.0.0 (which shouldn't affect non-CDC syncs) and changing how source-postgres's cursor works in CDC mode in 3.0.0 (which, again, only affects CDC syncs).

also tagging in @prateekmukhedkar for DB sources expertise, in case I missed something in the changelog.

haithem-souala · 2023-08-28T09:22:03Z

might affect other streams

can you explain some more on this? My understanding is that the two major releases between 1.x and 3.x were upgrading the underlying debezium library in 2.0.0 (which shouldn't affect non-CDC syncs) and changing how source-postgres's cursor works in CDC mode in 3.0.0 (which, again, only affects CDC syncs).

also tagging in @prateekmukhedkar for DB sources expertise, in case I missed something in the changelog.

Yeah, i don't know what changed "really" between the 1.0.42 and the 3.X, if someone can confirm the changes are related only to CDC mode, ill be down for a downgrade of the connector.

haithem-souala · 2023-09-01T10:18:34Z

might affect other streams

can you explain some more on this? My understanding is that the two major releases between 1.x and 3.x were upgrading the underlying debezium library in 2.0.0 (which shouldn't affect non-CDC syncs) and changing how source-postgres's cursor works in CDC mode in 3.0.0 (which, again, only affects CDC syncs).

also tagging in @prateekmukhedkar for DB sources expertise, in case I missed something in the changelog.

Hey @prateekmukhedkar, we're migration the syncs to V2 destination, and this issue blocking us to migrate the last two sources.

edgao · 2023-09-26T17:48:23Z

@haithem-souala source-postgres:3.1.9 has been released, which should work correctly for you - can you give it a try?

haithem-souala · 2023-09-27T15:41:18Z

Yes, it's working fine, thank you @edgao!

haithem-souala added type/bug Something isn't working area/connectors Connector related issues needs-triage labels Aug 7, 2023

octavia-squidington-iii added autoteam team/connectors-python labels Aug 7, 2023

This was referenced Aug 7, 2023

[Docs] No Deduped + History, Append + Deduped is the future! #29114

Merged

[Roadmap] Airbyte Destinations V2: Improved Data Syncing & Error Handling #26028

Closed

evantahler added team/destinations Destinations team's backlog and removed needs-triage team/connectors-python autoteam labels Aug 7, 2023

jbfbell added the needed-for-v2-release label Aug 15, 2023

edgao self-assigned this Aug 17, 2023

edgao mentioned this issue Aug 17, 2023

Source postgres: Cursor-based replication reads JSONB[] columns incorrectly #29551

Closed

edgao removed the needed-for-v2-release label Aug 23, 2023

evantahler added the team/db-dw-sources Backlog for Database and Data Warehouse Sources team label Aug 28, 2023

evantahler changed the title ~~Bigquery Destination V2 - Unable to handle jsonb[]~~ Postgres Source - Unable to handle jsonb[] (regression) Aug 28, 2023

evantahler unassigned edgao Aug 29, 2023

postamar mentioned this issue Sep 18, 2023

🐛 source-postgres: fix jsonb[] handling bug #30534

Merged

postamar closed this as completed in #30534 Sep 26, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Postgres Source - Unable to handle jsonb[] (regression) #29172

Postgres Source - Unable to handle jsonb[] (regression) #29172

haithem-souala commented Aug 7, 2023 •

edited by jbfbell

Loading

evantahler commented Aug 8, 2023

evantahler commented Aug 8, 2023 •

edited

Loading

haithem-souala commented Aug 9, 2023 •

edited

Loading

haithem-souala commented Aug 16, 2023

cynthiaxyin commented Aug 16, 2023

edgao commented Aug 17, 2023 •

edited

Loading

edgao commented Aug 17, 2023

haithem-souala commented Aug 17, 2023 •

edited

Loading

edgao commented Aug 17, 2023 •

edited

Loading

edgao commented Aug 17, 2023

haithem-souala commented Aug 18, 2023

edgao commented Aug 18, 2023

haithem-souala commented Aug 28, 2023

haithem-souala commented Sep 1, 2023

edgao commented Sep 26, 2023

haithem-souala commented Sep 27, 2023

Postgres Source - Unable to handle jsonb[] (regression) #29172

Postgres Source - Unable to handle jsonb[] (regression) #29172

Comments

haithem-souala commented Aug 7, 2023 • edited by jbfbell Loading

Connector Name

Connector Version

What step the error happened?

Revelant information

Relevant log output

Contribute

evantahler commented Aug 8, 2023

evantahler commented Aug 8, 2023 • edited Loading

haithem-souala commented Aug 9, 2023 • edited Loading

haithem-souala commented Aug 16, 2023

cynthiaxyin commented Aug 16, 2023

edgao commented Aug 17, 2023 • edited Loading

edgao commented Aug 17, 2023

haithem-souala commented Aug 17, 2023 • edited Loading

edgao commented Aug 17, 2023 • edited Loading

edgao commented Aug 17, 2023

haithem-souala commented Aug 18, 2023

edgao commented Aug 18, 2023

haithem-souala commented Aug 28, 2023

haithem-souala commented Sep 1, 2023

edgao commented Sep 26, 2023

haithem-souala commented Sep 27, 2023

haithem-souala commented Aug 7, 2023 •

edited by jbfbell

Loading

evantahler commented Aug 8, 2023 •

edited

Loading

haithem-souala commented Aug 9, 2023 •

edited

Loading

edgao commented Aug 17, 2023 •

edited

Loading

haithem-souala commented Aug 17, 2023 •

edited

Loading

edgao commented Aug 17, 2023 •

edited

Loading