https://linen.dev logo
Join Slack
Powered by
Hey guys, any reason in particular "top 1" is used...
# help-connector-development
a
Hey guys, any reason in particular "top 1" is used on incremental syncs? Me, our data engineer and DBA have been scratching our heads for a while, and we assume it may be part of airbyte's governance for new connectors. If this isn't a fixed airbyte thing, is there a way to build more perfomanct connectors in the webapp?
s
hi @Alex Stenlake, what do you mean “top 1”?
are you referring to the practice of using a single high watermark/cursor value to keep track of which data has been synced, and using a single thread in an incremental sync to load data which came after it? This is as opposed to partitioning the range of data yet to be synced and fetching it in parallel?
(definitely reading a lot between the lines here 😄 )
a
Yeah pretty much. Inspecting the logs I see this:
Copy code
2023-03-01 04:55:51 [44msource[0m > Generated legacy state for 24 streams
2023-03-01 04:55:51 [44msource[0m > Generated legacy state for 24 streams
2023-03-01 04:55:51 [44msource[0m > Generated legacy state for 24 streams
2023-03-01 04:55:51 [44msource[0m > Generated legacy state for 24 streams
2023-03-01 04:55:51 [44msource[0m > State report for stream rpl_vdbo_mSource - original: null = null (count 0) -> latest: SourceID = 4753152 (count 1) <<--- This line
2023-03-01 04:55:51 [44msource[0m > Generated legacy state for 24 streams
2023-03-01 04:55:51 [44msource[0m > Generated legacy state for 24 streams
And checking queryStore on the source system we see the following; File deleted, pinging in DM just in case
What I'm assuming is going on is a very conservative update, one at a time to make sure no data is missed
But I can't find this behaviour documented anywhere, and (due to volume) we may need to rewrite/fork a connector to be a little more aggressive in its fetch
s
which connector are you using?
FWIW this is not an Airbyte platform limitation. Connectors have the freedom to structure their state/watermarks however they see fit, and parallelize however needed. But for certain categories of connectors, parallelization might not be worth the benefit: • For a large number of API connectors, the bottleneck is the rate limit. Many however would benefit from parallelism (and some like Facebook Marketing implement it) • Database connectors which use CDC don’t implement parallelism atm. Non-CDC ones, I’m not caught up to date on the state of the art.
a
MSSQL connector
I think it's an alpha connector so it wouldn't be supported, so its probably down to the connector developer's choice yeah?
s
Something worth mentioning is that the python CDK (which is used by pretty much all API connectors) doesn’t offer easy primitives for parallelism, but individual connectors can always implement it in their code base by overriding CDK behavior/starting from a more “base” abstraction layer
hmmm yeah MSSQL is not on the roadmap for improvement this quarter. I know we do some parallelism in Postgres. But that’s just pedestrian knowledge. I haven’t done DB work in a while. If that’s true, you may find some inspiration in that codebase
a
No worries - are you accepting PRs there in case we need to do anything? Personally the rate doesn't bother me if the cloud platform handles sufficient parallelism
s
ccing @Prateek Mukhedkar (Airbyte) as databases team lead -- he should be able to answer your question
a
No stress. Our DB isn't stressed, but out data engineer is a little nervous about the effect on network traffic
p
Copy code
State report for stream rpl_vdbo_mSource - original: null = null (count 0) -> latest: SourceID = 4753152 (count 1)
That should indicate the number of rows returned when doing an incremental sync. I am not completely familiar with how MSSQL source is doing incremental sync yet. How many rows were selected in this incremental sync run?
a
My guess is millions - I didn't see anything in the log there
But yes, the nature of my question is around why it's returning one row at a time...I assume there's a solid engineering reason for it
k
Is it normal for the connector to advance 1 row at a time during a sync? I'm seeing really slow performance on the Snowflake destination connector.. 0.4.61 (latest) and it behaves similarly as above. 
State report for stream TABLE_X - original: END_TIME = 2023-04-06T09:43:36.842000Z (count 1) -> latest: END_TIME = 2023-04-08T13:27:22.671000Z (count 1)
There are lots of spammy 
Generated legacy state
messages. Do you have any advice to suppress these, and in general help the sync go faster?
s
Raised a bug for this @Kevin Wang: https://github.com/airbytehq/airbyte/issues/26932
octavia thanks 1
12 Views
Open in Slack
PreviousNext
Powered by