https://linen.dev logo
Join Slack
Powered by
Hi Guys, I'm a new Airbyte user. I did the setup u...
# announcements
h
Hi Guys, I'm a new Airbyte user. I did the setup using the docker-compose using a r5.xlarge EC2 Instance. In my first test trying to move a small table (1000 rows) from postgres to snowflake, airbyte works fine. When I try to move a bigger table (2M Rows) from postgres to snowflake, the process never end with the log:
Copy code
2021-01-09 19:25:59 INFO (/tmp/workspace/16/1) LineGobbler(voidCall):69 - 2021-01-09 19:25:59 [32mINFO[m i.a.i.d.s.SnowflakeDestination$DestinationImpl(insertBufferedRecords):164 - {} - max size of batch: 500
The error message is:
Copy code
Process is taking too long to finish. Killing it
Sync worker failed.
io.airbyte.workers.WorkerException: target process wasn't successful
There's any way to increase the BATCH_SIZE param? and there's any best practices to process a table with more than 1M rows?
m
Hi Hudson
Does it freeze ?
Or do you continue to see logs and it is too slow
h
Hi Michael, now it's on the third attempt.
Looks like the process is restarted after sometime.
m
Could you post the logs?
h
m
looking at it
hum I think I know what is going on
we need to better detect the end of the stream. In the meantime I believe changing the batch size could help get it through
let me think about the best way to unblock you
h
Thanks for the really fast answer Michel.
m
ok, I am going to build new version of the snowflake connector, I will let you know how you can pick it up
would you mind creating an issue describing your problem so we use it as the base for a long term fix?
h
Sure, let me do it now.
m
no that is correct
I want to bump it up to 10k
but I would have to make sure it works
do you want to take a shot at it?
I can guide you through the process if you want
You can build a dev version of the connector and test it on your workstation
h
Yes, let me try here
m
it should just be a matter of: 1. modify the code 2. run 
./gradlew :airbyte-integrations:connectors:destination-snowflake:build
that will generate the :dev version of the image for snowflake
h
Nice, thank you so much, I will let you know soon.
HI Michael, I have new :dev version image for snowflake,, can I rebuild my containers directly or do I need to make some change into the docker-compose?
m
you can start docker-compose directl
I will tell you how you can start using the dev version in airbyte
once you're in the UI
h
I'm in the UI now, I need to change the tag version in the admin page?
m
yep 🙂
h
Amazing
m
what size did you pick?
h
I'm using your suggestion 10k
m
🤞
I think we need to improve our timeout mechanism here.
it should work if your batch size is 500, just take a lot of time, but it should be able to terminate
how does it look right now?
h
The process still running but using the 10k:
Copy code
2021-01-09 20:31:46 INFO (/tmp/workspace/41/0) LineGobbler(voidCall):69 - 2021-01-09 20:31:46 [32mINFO[m i.a.i.d.s.SnowflakeSqlOperations(insertRecords):60 - {} - actual size of batch: 10000
2021-01-09 20:31:49 INFO (/tmp/workspace/41/0) LineGobbler(voidCall):69 - 2021-01-09 20:31:49 [32mINFO[m i.a.i.d.b.BufferedStreamConsumer(writeStreamsWithNRecords):184 - {} - max size of batch: 10000
I got the same error again:
Copy code
2021-01-09 20:34:34 WARN (/tmp/workspace/41/0) WorkerUtils(gentleClose):49 - Process is taking too long to finish. Killing it
2021-01-09 20:34:35 ERROR (/tmp/workspace/41/0) LineGobbler(voidCall):72 - Error when reading stream
java.io.IOException: Stream closed
There's any parameter where I can configure the timeout?
m
can you send me the log again?
yes it is possible to change the timeout
(through the code though)
h
m
could you try to bump the batch size to 20k ?
h
Sure, I'm also bumping the 
MIN_RECORDS
to 5k. What do you think?
m
I don't think you need the min_records one
you're always maxing out the the buffer
I think you were very close to finish if you have 2M
the faulty timeout is here: io/airbyte/workers/protocols/airbyte/DefaultAirbyteDestination.java:103
you could try to make it 10min
I can tell you how to run it locally to test if you want
h
I got it, I will keep the 
MIN_RECORDS
. Yes I want try to increase to 10 min, thanks.
m
at that point you might need to run: 
./gradlew build
after that you just need to run
Copy code
VERSION=dev docker-compose up
and it will use the local dev version
h
Nice, let me try it.
Does it possible to clean my env before to test this new version? Removing the data files?
m
yes
you can do 
docker-compose down -v
it will remove all your airbyte configuration
h
Thanks I missed the 
-v
option before.
m
did you get it to work?
h
Not yet, I'm building some dependencies, I had an problem with nodejs
m
if you encouter any issues, you can put them on the chat
we're trying to fix all these developer experience issues
h
I had some problems trying to install node on amazon-linux, in the end I just used the binary version and works fine. Now I'm running the gradlew again. Thanks again for the support :)
Hi Michel, The process now is returning a new error:
Copy code
Exception in thread "main" java.lang.RuntimeException: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 1 at position 141
2021-01-09 22:43:42 ERROR (/tmp/workspace/11/0) LineGobbler(voidCall):69 - maximum number of expressions in a list exceeded, expected at most 16,384, got 20,000
logs-11-0.txt
m
ok. I guess we shouldn't go above 10k for the batch size
we will need to test all the other warehouses
(we can take care of it)
h
m
can you restore the 10k et try with your timeout change?
h
Sure, I'm going to it and let you know
All good now:
Copy code
completed destination: class io.airbyte.integrations.destination.snowflake.SnowflakeDestination
2021-01-09 23:26:08 INFO (/tmp/workspace/10/0) DefaultSyncWorker(run):110 - Running normalization.
2021-01-09 23:26:08 INFO (/tmp/workspace/10/0) NormalizationRunner$NoOpNormalizationRunner(normalize):67 - Running no op logger
2021-01-09 23:26:08 INFO (/tmp/workspace/10/0) DefaultSyncWorker(run):128 - sync summary: io.airbyte.config.StandardSyncSummary@5b74a51[status=completed,recordsSynced=3191021,bytesSynced=411250596,startTime=1610233962174,endTime=1610234768125]
Changing the timeout and the BATCH_SIZE the job finished with success. Thanks a lot for the help!
m
Hurray!
wanna submit a PR?
I realized you ran without the normalization
it means you will only have the raw table
out of curiosity btw how long did it take to sync?
h
Yes I do, can I submit it directly to master? The process takes: 13 minutes for 3,191,021 records.
m
no, you need to fork and push to your fork then submit from your fork branch to merge to master
h
Perfect, thanks.
m
Thank you for the PR!
h
Thank you for the help again
m
I will let the team review it. Should be shipped next week! Thank you so much for taking the time
h
Nice!
Open in Slack
PreviousNext
Powered by