Rows go missing on streaming insert into BigQuery in Pubsub consumer #876
Comments
|
How much of a delay have you tried between steps? I believe there is a delay between your streaming insert request succeeding and the rows arriving to the table's streaming buffer. Rows are only queryable once they have arrived to the streaming buffer. There is this one sneaky sentence in the docs that alludes to this: https://cloud.google.com/bigquery/streaming-data-into-bigquery#dataavailability
In the past I have generally found that the few seconds they refer to can end up being 1 - 5 minutes. I am not sure if it is better or worse now a days. I would try it again but this time with a 5 minute sleep before attempting to read the rows and seeing if you retrieve all the rows. I would only recommend you try the sleep to determine if the issue is the delay and not as a permanent solution since it does not guarantee that the rows have reached the streaming buffer if Big Query decides to have a bad day. Out of curiosity why do you need to read the rows immediately after inserting them? |
Good question, and I should have addressed that. On several occasions I've waited hours and the rows still never appeared. We aren't intending to query the data immediately, the test does it out of convenience. |
How are you checking that the rows haven't appeared? For example the row count in the table metadata tends to be out of date. I think in the past two or so months the row count has just been outright broken, several of my tables have had millions of rows for several weeks and still displayed a count of 0. I generally just ignore the row count at this point. The preview pane also generally has quite a large delay (can't remember how large but I think its in the hours range). Querying the table (1-5 minutes after insertion) is generally the only sure way to determine if the table contains the data. |
|
Good point, although I'm not relying on the table metadata or preview here; I do a SELECT * or SELECT COUNT(*) to tie-out. And as mentioned before, waiting has had no effect on this that I've seen. For example, this test I ran yesterday about 16 hours ago showed only 7/10 rows in BigQuery then and the table still only shows 7 rows in a SELECT *. Here's the publisher-test output: For completeness' sake, here's the response from each TabledataInsertAllCall showing each of the ten rows receiving a 200/success response and no error from the BigQuery v2 api:
And here's the Query Editor console showing the same results now, 16 hours later, in a SELECT *:
|
|
Can we isolate whether this is pubsub or bigquery? Can you log or otherwise verify that you're actually inserting all the rows? |
|
I think I might know what the issue is... I believe the issue is caused by the table name being reused. Try changing the table name in your test program and seeing if you retrieve all the results (I believe you should!). If I recall correctly, it has to do with the consistency model used in parts of Big Query. I don't know the exact details but I believe some of the rows are going to the old table since the deletion of the old table and creation of the new table with the same name hasn't propagated to the whole system yet. I believe the Big Query team currently recommends waiting 10 minutes before streaming to a table after you have recreated it after deleting it. Alternatively you could just use a different table name every time? Source: https://issuetracker.google.com/issues/64329577#comment3 |
|
Yep I was just noticing that it works every time on a new table and breaks on the second run. I know it's a niche case but can the API reply with an error if you try to stream to a table like this? We don't plan on dropping and re-creating tables (this was just testing), but it sounds like a known constraint that the API should acknowledge. |
|
You can try opening an issue on the Big Query issue tracker and asking: https://issuetracker.google.com/issues/new?component=187149&template=0 I wouldn't get my hopes up but you never know. |
|
I do remember running into this problem when writing integration tests. Thanks @BlueMonday for thinking of it. |
I'm having an issue in a Pubsub consumer where rows written to BigQuery using Put don't return an error but don't make it to the table. Here's the gist of the test I'm doing. The code sets up everything it needs except the BigQuery dataset which you'll need to create manually and change the variable on line 55. This is what the code does:
numMessagesnumber of messages to Pubsub topicOccasionally it will work as intended (if it does, run it a few times) but most of the time the BigQuery table is missing some (if not all) rows, and the Pubsub topic has no un-acknowledged messages after, either. The problem seems to be occurring more during the initial time when Receive starts; for example, using higher numMessages will still have a problem, particularly on the initial inserts, but lower loads lose a higher percentage of messages. Here's an example of output:
I've tested using delays in between steps, limits on ReceiveSettings, and delays inside the Receive func but no dice. Let me know if there is any more information I can provide.
The text was updated successfully, but these errors were encountered: