[BigQuery] Streaming insert drops records?

[martinstuder]

I'm facing an issue with BigQuery streaming inserts (Table.insert(...), specifically insert(Iterable<InsertAllRequest.RowToInsert> rows, boolean skipInvalidRows, boolean ignoreUnknownValues) with skipInvalidRows = false and ignoreUnknownValues = false) where (sometimes) records don't seem to be available after one or more insert requests. The InsertAllRequests complete successfully, i.e. no exceptions are thrown and no errors are reported (InsertAllResponse.hasErrors returns false). I checked availability of streamed data in the BigQuery Web UI and using the Table.list(...) API. According to https://cloud.google.com/bigquery/streaming-data-into-bigquery I would expect streamed data to be available for query a few seconds after insertion. In cases where some records were missing after the initial check, I tried again after 10s, 30s, 60s, 1h, ... but to no avail. So it looks like the records have been dropped for some reason.

[martinstuder]

On https://cloud.google.com/bigquery/troubleshooting-errors#streaming it says: "Certain operations in BigQuery do not interact with the streaming buffer, such as table copy jobs and API methods like tabledata.list". So I guess this includes Table.list(...). Is this also true for the BigQuery Web UI? Is there any other means in the API of paging through a table that does consider the streaming buffer?

[andreamlin]

Hi @martinstuder,

Thanks for submitting this issue!

The lack of streaming on certain BigQuery methods is a limitation of the API itself, so the problem would persist using the Web UI or HTTP REST requests. Indeed, an internal bug was just filed by someone using the Python library who ran into the same problem.

The internal bug is live and has traction; it definitely seems to be a problem that the Insert request returns without error even though the operation did not complete server-side.

[andreamlin]

@martinstuder Would you mind checking response status code in addition to checking for errors presence? The request might fail even before reaching streaming system, due to quota exceeded, for example. In this case 503 would be returned instead of 200.

[martinstuder]

@andreamlin Thank you for your reply. InsertAllReponse does not expose a status code and as such I would expect any non-200 status to be turned into an appropriate BigQueryException when calling table.insert(Iterable<InsertAllRequest.RowToInsert> rows, boolean skipInvalidRows, boolean ignoreUnknownValues).

[andreamlin]

@martinstuder Fair point :) I'll just keep this issue updated while they're working on it.

[taylor-rolison]

Are there any updates on this issue?

We have a relatively complex Dataflow pipeline where we are streaming data from Pubsub and writing to GCS, Azure, and BiqQuery. We are seeing a small number of dropped rows in BiqQuery but no missing data in the other destinations. All the writers use the same pubsub code and we are sure the issue is in the BiqQuery writer. The insertAll response does no contain any errors, but we can not say for certain if all the rows were inserted because there is no way to easily tell that without doing a separate query and there are timing/buffer issues related to that.

[vitamon]

I had similar experience, my service account had not enough permissions, but InsertAll run smoothly and swallowed any exceptions. But rest api returned errors like this:

error { domain: "cloud.helix.ErrorDomain" code: "ACCESS_DENIED" argument: "Table" argument: "mytable.usage_events" 
argument: "The user my-service-account@iam.gserviceaccount.com does not have bigquery.tables.updateData permission for table myproject:mytable.usage_events

[pmakani]

I had similar experience, my service account had not enough permissions, but InsertAll run smoothly and swallowed any exceptions. But rest api returned errors like this:

error { domain: "cloud.helix.ErrorDomain" code: "ACCESS_DENIED" argument: "Table" argument: "mytable.usage_events" 
argument: "The user my-service-account@iam.gserviceaccount.com does not have bigquery.tables.updateData permission for table myproject:mytable.usage_events

@vitamon
I tried to reproduce this but its working fine for me. Below is full exception log. Can you please try with latest version ?

Exception in thread "main" com.google.cloud.bigquery.BigQueryException: Access Denied: Table bigquery-3344:3344_dataset.3344_table: The user bigquery-viewer@bigquery-3344.iam.gserviceaccount.com does not have bigquery.tables.updateData permission for table bigquery-3344:3344_dataset.3344_table.
	at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.translate(HttpBigQueryRpc.java:100)
	at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.insertAll(HttpBigQueryRpc.java:309)
	at com.google.cloud.bigquery.BigQueryImpl.insertAll(BigQueryImpl.java:599)
	at com.google.cloud.bigquery.BigqueryStreamingInsert.insertData(BigqueryStreamingInsert.java:48)
	at com.google.cloud.bigquery.BigqueryStreamingInsert.main(BigqueryStreamingInsert.java:16)
Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 403 Forbidden
{
  "code" : 403,
  "errors" : [ {
    "domain" : "global",
    "message" : "Access Denied: Table bigquery-3344:3344_dataset.3344_table: The user bigquery-viewer@bigquery-3344.iam.gserviceaccount.com does not have bigquery.tables.updateData permission for table bigquery-3344:3344_dataset.3344_table.",
    "reason" : "accessDenied"
  } ],
  "message" : "Access Denied: Table bigquery-3344:3344_dataset.3344_table: The user bigquery-viewer@bigquery-3344.iam.gserviceaccount.com does not have bigquery.tables.updateData permission for table bigquery-3344:3344_dataset.3344_table.",
  "status" : "PERMISSION_DENIED"
}
	at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:150)
	at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:113)
	at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:40)
	at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:401)
	at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1132)
	at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:499)
	at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:432)
	at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:549)
	at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.insertAll(HttpBigQueryRpc.java:307)
	... 3 more

[vitamon]

Just checked on version "google-cloud-bigquery:1.66.0" -- yes, it throws the exception now.

[zmm021]

Anyone experiencing this issue now? I am using the 1.88.0 version for some testing and experienced a few times of record missing, every 10K API call may occur 1-2 times of record missing, the API did not return any error.
PS：it happens when I insert the data in a batch way (100 rows per request), my recent 11 times 10000 requests (1 row per request) works fine, my testing data are the same one, and I am not using rowid. So maybe it is not the API side, but the server buffer related issue?