A few versions ago (v0.33.xx), I used to update some .yaml config files by exporting my airbyte config through the UI, update some of the config files, then re-upload the whole folder to apply my fixes.
Now, after upgrading my airbyte version (v0.35.67) and from what I understood, the process has changed as we have to directly update the psql database. My question here are:
Is there already an existing guide explaining how to do so ?
It is my understanding that we have to update the cursor defined in the state table. Is it the only manipulation or do we have to update other tables ?
I still have a question regarding this subject: In many scenario, the reason I have to define my cursor manually is because my source has evolved → How can I update my connection manually ?
For instance, a new table is added in my source and I don’t want to reset my destination data or my connection. Then, what I used to do was taking my .yaml files, adding manually my new table with its cursor, then re-uploading the config to apply my changes and integrate my new table. And it was going the same way when I wanted to add new columns in my source tables.
I know that the schema change subject is in the Airbyte roadmap, but as of today, what would be the best approach to apply this kind of changes in the Airbyte database ? What table should I target ?
Yes I did, by following @marcosmarxm answer with the following command example :
UPDATE state SET state = jsonb_set(state, '{state, streams, 0, cursor}', '"2022-01-25T00:00:00.000000Z"', false) WHERE connection_id = '592fd59f-2aa9-4852-9d2b-8d12e269faa1';
FYI, if like me you’re running Airbyte on Kubernetes, you can make it much easier by port-forwarding airbyte-db service, then update directly the state table with any UI client like DBeaver for instance.
Did you ever needed to set the cursor value manually for the first sync?
In my case I want to set the initial value to avoid Airbyte to sync old and useless MongoDB data.