Bigquery Stream Insert Missing Data

[zmm021]

I have been running testing for inserting data into Google Bigquery, and I have experienced data missing in my testing. Below is the detail about this issue.
Testing scenario: Insert data into Bigquery using the latest (v1.88.0) streaming API.
Code:

public Map<Long, List<BigQueryError>> performWriteRequest(TableId tableId,
                                                          List<InsertAllRequest.RowToInsert> rows) {
    InsertAllRequest request = createInsertAllRequest(tableId, rows);
    InsertAllResponse writeResponse = bigQuery.insertAll(request);
    if (writeResponse.hasErrors()) {
        System.out.println("Error inserting into BQ"); 
        return writeResponse.getInsertErrors();
    } else {
        logger.debug("table insertion completed with no reported errors"); 
        return new HashMap<>();
    }
}

Testing information:

• Each round the program calls Bigquery 10K - 20K times.
• Each Bigquery.insertAll request only inserts 1 row (some rounds also insert 100 rows per request), without using rowid to do deduplication, so rows should not be filtered.
• Each round takes about 5 minutes so it is not possible to hit any quota limitation (data is small).
• Totally tested 30-40 rounds.
• Some rounds use exactly the same data, others use different data.
• I did not drop the table and recreate, always use the new table with a different name.
• In the final round of the test, the program also pushed data to GCS and dump to a local file as well for comparison.

Observations:

• In about 5-10 rounds I see the data missing. No pattern shows when and what data might be missing, kind like random.
• No error returned from the Bigquery.insertall, from the client-side, all requests were successfully executed.
• There is a retry policy in the program, but it is never triggered since no error returned.
• Important: For new tables, I observed that the estimated number of rows in Bigquery stream buffer equals with the number of rows that I inserted, also equals with the number of rows in the file pushed to GCS(last round testing). But using select count(), I get fewer rows (about 1 missing in every 10-20K requests). After a while, the stream buffer info will be gone and the number of rows showed in the table info is the same with select count(), which is smaller than it is supposed to be.

I understand that the stream buffer only provides an estimated number, and we should not trust it, but every time after the data push, the number is exactly the same with the number of rows being pushed. Maybe this suggests that Bigquery received the data but finally dropped it for some unknown reason? This might be a bug.

This issue might related to #7433, #876, googleapis/google-cloud-java#3344 , and #3822.

[zmm021]

Thanks for your reply. How many rounds of experiments you executed? The issue does not happen all the time, upon the function I provided, I used threadpool and insert concurrently, I can reproduce the issue around every 10 times 2k insert request. Praful Makani <notifications@github.com> 于 2019年9月4日周三 上午7:08写道：

…

@zmm021 <https://github.com/zmm021> I followed your steps and tested with your information but not able reproduce missing data. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/googleapis/google-cloud-java/issues/6179?email_source=notifications&email_token=AHJ6HCRNI54IR2BTOWA3SYDQH6JLNA5CNFSM4IQHDHJKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD53GU7Y#issuecomment-527854207>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AHJ6HCTBSBXR3JQPMFAOUQLQH6JLNANCNFSM4IQHDHJA> .

[JD-V]

I am also facing this issue. Please keep us posted if any updates.

[janrockdev]

+1

[cumhuronat]

I'm facing the same bug on the nodejs library.

[stephaniewang526]

Hi, thank you for your question.

We are not aware of any known issues for this case. There are known issues if the table was recently deleted and recreated.

Could you provide us with a code example that can allow us to reproduce the issue? We will investigate further based on that.

Thanks,
Stephanie

[stephaniewang526]

Closing due to no activity.

[anicoll]

Hi, thank you for your question.

We are not aware of any known issues for this case. There are known issues if the table was recently deleted and recreated.

Could you provide us with a code example that can allow us to reproduce the issue? We will investigate further based on that.

Thanks,
Stephanie

@stephaniewang526 Can you expand on the issue around newly deleted/created tables?
I am having this exact issue and is reproducible 9/10 times.

I can provide code examples if needed.

Golang using v1.6.0 OR v1.4.0
This never used to be an issue for us but has recently become a problem

[larssn]

Also seeing this occasionally, where a row is lost without any apparent errors. It's rare but not irrelevant, and too periodic to reproduce.

[jnt0009]

I am running into this issue as well where occasionally the first row or two do not make it into the table.
Node JS: 14.16.1
@google-cloud/bigquery: 5.6.0
No errors are returned.

[stephaniewang526]

@jnt0009 please open an issue in the bigquery NodeJs repo.