Hello, everyone! I faced with this bug that i usin...
# connector-contributions
l
Hello, everyone! I faced with this bug that i using Airbyte to sync data PostgreSQL, but seem the primary/foregin keys not assigned to destination tables after sync operation. I tried many method but the result is the same. How can i solve this? Please give me some recommendations
n
You mean relationships between tables is not persisted as primary and foreign keys in destinations? That’s by design. Airbyte is a data movement platform, not a database migration / backup tool.
l
When I retrieve data from the REST API and configure it with the 'Full Refresh | Append' mode, the data gets duplicated after each retrieval because Airbyte doesn't use the ID from the REST API as the primary key in the database. Is there any way to set a primary key for Airbyte Destinations?
w
Full_refresh | Append basically mean you are going to pull over a full copy of all your data without deleting anything in the destination (basically duplicating it all). If you just want to do a full refresh each time without duplicating, try changing it to Full_Refresh | Overwrite.
l
@William Kaper I understand, but my API only returns a few changed records after the second call, it doesn't return the full data, so that configuration method won't work
w
What API is it? If the API has an updated field or a way to derive an updated field, you can configure the stream to be incremental and then you can use the type Incremental | Append OR Incremental | Append + Dedup to only pull incremental updates.
l
My API is spring boot rest api, when you create source from rest api, It doesn't allow you to use the configuration type Incremental | Append OR Incremental | Append + Dedup, the type Incremental | Append OR Incremental | Append + Dedup only for source is database
w
If there is no way to get update events from a webhook AND the records could change past a records creation event, you only choice is to do a full resync.
(At least, I think that's the case)
I'm not super-familiar with sprint boot rest APIs , so maybe there is a way to wire up to do incremental on a created/updated cursor field
l
I understand, so is there any way to change the primary key of the destination database? If possible, we can solve the update problem of the REST API
w
I'm just a 3rd party open source contributor so I don't want to speak for the devs, but I don't think airbyte works like that. You would need to get the source to be able to confirm to an incremental stream which would solve your problem. Otherwise, deduping the records on the destination side would need to be something post-processing
If this API you are using does have the ability to call it and only get incremental results back for the rest end point (everything new or updated since a point in time), then I think it would be possible to wire up source to be incremental
Are you building a custom source connector using no code or low code API?
l
Are you building a custom source connector using no code or low code API?
Im using low code
w
You want to pay attention to the cursor part
l
you mean cursor in destination ?
@William Kaper my source config as :
w
I've never build a connector with no-code/low-code, but it looks like you are using no-code. Open the first link above and watch the video for step 3
There are two videos, one for advanced, and one for adding incremental reads. That part of the config is below what your screen shot shows
Good luck! You may want to engage the #help-connector-development channel, which is probably the right channel for a question like this
l
I've never build a connector with no-code/low-code, but it looks like you are using no-code. Open the first link above and watch the video for step 3
So which build method are you using? Please let me know. Thank you very much
w
Python CDK but that's just because I've been modifying existing connectors that are community connectors. If I was hooking up bespoke APIs or something that the community didn't support, I'd absolutely go the no code / low code approach
l
yes, thanks you
@William Kaper I am running a self-hosted airbyte and migrating my data using the certified mysql source connector and the community mysql destination connector. I am having trouble with my primary keys as the destination tables end up without them and when I manually configure them the next sync overwrites them and I end up without primary keys again. Kindly assist
w
I dont use MySQL so I wont be much help. Why do you need primary keys in the first place?
Also, did you try asking #replication-troubleshooting?