Destination Bigquery returns 404 when uploading data + resumable #3549
Comments
marcosmarxm
added
type/bug
User logs syncing Stripe to Bigquery |
|
@sherifnada tagging you because this is the second case this error happens. Not sure if checkpointing can handle this because is intermittent failing... |
marcosmarxm
changed the title
|
Hi, this is happening to me on Postgres => BQ on a huge database first sync. The sync process gets stuck at that point. |
|
User could sync after selecting only a subset of tables.
|
|
my guess is that the right way to deal with this is bulk loading data into BQ via staging on GCS. @andresbravog would that be an acceptable solution for you? |
|
@sherifnada we already have a workaround by creating a script that waits for each job to finish and ads one more table to the connection each time (controlling when the process gets stuck). My take here is that the process has two main bugs:
Ideally, we would like to make BQ destination error prune. |
|
Running into a similar issue here with Stripe to BigQuery.
|
|
Seeing the same problem when trying to load a few tables, with one of them containing nearly a Billion rows from MSSQL to BigQuery. The exception happens pretty late in the sync, after it already ran for a few hours. |
|
hey everyone, we're working on this in the current sprint. It's not 100% clear yet what the fix will be, so our first step is to identify that, then we'll have a more concrete ETA. But it's something we're currently working on. |
|
Hi @sherifnada, I think this issue is documented by Google Big Query as a quota limit hit that might happen at BigQuery level (>1TB load data) or at job level: https://cloud.google.com/bigquery/quotas#load_jobs I think is very possible that we hit the jobs per table limit quota. The suggestion I've seen is to simply implement a retry strategy. |
|
Still working on attempts to reproduce the issue and code checking, but got some idea why this could happen. Each stream seems to have its own buffer. |
|
Another finding\assumption, The actual reason of fail seems to an out of memory error. The assumption to check if statement is valid:
To check if helps:
UPD. a. With default BigQuery's native client - 15 MB b. With - 7 MB c. With - 1 MB Obviously, as smaller chunk size as less Heap memory consumed, but that also causes a slower uploading |
|
Hi @andresbravog @flagbug @hendrikjd. |
|
In our case tmp tables are there but not raw ones. |
|
Hi there. Still experiencing some issues with reproducing this bug and understanding the root cause. Need some help from those who can reproduce it. Thanks in advance! Already even tried to upload 600'000'000 to few big query tables, but it didn't fail Gently asking for those who can reproduce it to attach For now, the only option I see is that the native SDK bigquery client fails with out of heap memory at the moment when it's called like writer.write(...) from "acceptTracked" method. I can reproduce such outOfMemory (HEAP) using some local unit\integration tests running directly on pc without docker containers), but the main web app doesn't receive any info that the container crashed. In this case we get all tmp tables created and empty (as the consumer had not been closed correctly) which more or less meets what @andresbravog also observed. But then it's not clear where the 404 comes from? Maybe something tries to send messages to died destination connector in the middle... @sherifnada @tuliren @subodh1810 maybe you have any idea as engineers with a great understanding of this project? |
This feels like the OOME is caused by too many big query writers. The S3 destination had similar problem before. We use a separate writer for each stream (the |
|
Sorry, I replied too soon. What I mentioned above is already included in your investigation. #5029 looks like a good solution. Alternatively, we can automatically decrease the chunk size if there are many streams. |
For me it's only "...tmp..." tables. All of them are empty. |
|
@hendrikjd and everyone else: like @etsybaev mentioned above, we were really stumped trying to reproduce this issue, so we released a new version of BQ with a lot of debugging logs that may help us understand what's going on. Could you upgrade to the latest bigquery destination connector and see if the issue persists and if so share logs? |
|
HI there. |
|
I've tried twice more with the updated connector. Once with default value for chunk size and once with it set to 5. Neither were successful. Here's the logs for both. I'm only getting the error with Stripe -> BigQeury so I tried to find an alternative for that source. Looking through the Fivetran docs I noticed that they don't support BigQuery Sandbox accounts which is what I'm using right now. Not sure if that's related with the issue here but felt like it's worth pointing out. The limitations of sandbox are listed here. |
|
It seems like I've found how to reproduce it. The key point here is not how big is the data set that you are going to upload and how long it's being uploaded. But the main point is how much time did it take to read records from source DB. We create a destination connector's container at the very beginning along with the source connector's container as far as I see far now. The google big query clients and tmp dirs are created at the time when the destination container is created. Then we wait for source to read data meanwhile, the destination one is in an idle state.
Next steps:
By the way, checked how much memory it takes to write 18 streams with chunk buffer equals to 5MB. Here is the result UPD: it seems like google SDK client doesn't have any kind of "keep alive" requests. It also doesn't have an explicit option to force flush its buffer other than just close a connection. But we do not have an internal message from the source that says that read completed and no more messages to write expected (as far as I see, but maybe I missing something). |
|
amazing work @etsybaev , seems like you're getting very close here. So it sounds like the main hypothesis then is if the time difference between two interactions with a BQ Stream ("interact" can mean open, write, or close) then we receive a 404? Like you noted, I'm not sure there is an "easy" way around this. Ideally we need to create a job as late as possible for each stream. So we could potentially do lazy creation. That doesn't solve the "close" issue though where a stream receives all of its data at the beginning of the sync, but the sync takes long enough that by the time we close the stream it has been a long time. I think the most guaranteed way around this is using Bulk loading as it would allow us to stage the data on GCS entirely before loading it into bigquery. This is already implemented in Snowflake so we'd need something similar implemented for bigquery. WDYT? If you agree @etsybaev could you create an issue for this and move it into prioritized for scoping? we should work on that soon. |
|
Hi @sherifnada. |
|
Hi, |
karinakuz
added
connectors/destinations-warehouse
and removed
connectors/destinations-api
labels
Expected Behavior
Sync between Shopify and BQ works
Current Behavior
It's not totally clear why this happens...
BigQuery returns 404 when uploading data after running a few hours.
From Google Cloud API Page here
Logs
Steps to Reproduce
It's not clear how to reproduce. Maybe this happens when trying to sync large Shopify datasets to BQ.
Severity of the bug for you
Very low / Low / Medium / High / Critical
Airbyte Version
0.23.0-alpha
Connector Version (if applicable)
0.2.2 Shopify
Additional context
Slack convo
┆Issue is synchronized with this Asana task by Unito
The text was updated successfully, but these errors were encountered: