Repository navigation
bigquery table.insert fails for identical rows #1041
Description
Activity
Thanks for offering! I think you just want to skip the insertId then, since its purpose is to prevent duplicate data from being inserted. If that sounds right, we could just add an options object like:
var opts = { allowDuplicates: true // (default: false) }; table.insert(rows, [opts], callback);
When it's
true, we just wouldn't set theinsertId.Sent a PR: #1045
@JamesCharlesworth while trying to test this out for #1045, I can't actually reproduce the error. Can you show me some code I can use that should cause the error?
https://github.com/GoogleCloudPlatform/gcloud-node/blob/c7b49cb8fec15d6d159db6d7b8832e8ac4e50d0e/lib/bigquery/table.js#L902-L910
As of now md5(JSON.stringify(row)) is used as insertId, This is not a robust one. JSON.Stringify is not guaranteed to give same result for similar objects. and md5 can be same for two different strings.I think the insertId generation should be left to user. If user is copying data from another db, then user may prefer to use existing db id as insertId. Just like mentioned in Google API Docs the insertId should be a 'opt-in' feature. the insertId deduplication is also not guaranteed, so generating insertId automatically may lead to inconsistent behaviour.
I think the insertId generation should be left to user.
👍 Would you (or @JamesCharlesworth) be open for sending a PR that realizes your vision for how that should be implemented?
I looked into code and tried to remove insertId feature. But it will break existing applications.
Instead we can add new api 'insertAll' and make it transparent. User should build and pass the request body, as per spec. In this way, user can use 'insertId' ,'templateSuffix', 'ignoreUnknownValues', 'skipInvalidRows' features. In current implementation of 'insert', user can't use any of these features.
I prefer, gcloud-node should be just utility library to call api endpoints. Its should not add any magic to the call. I just checked python api, its very clean, and just matches with api spec.
I prefer, gcloud-node should be just utility library to call api endpoints
That's more like https://github.com/google/google-api-nodejs-client -- which is the Node version of the Python library you linked to. We purposely design an easier to use API around the raw, upstream JSON API.
And that comes with challenges. It's not always clear where our API needs to allow 100% upstream transparency and when lesser coverage is okay. Each call is different than the next. That's why we chose to default
insertIdin the first place-- when sensible, we want each method to be completely "ready-to-go" for the common use case.I still agree
insertIdcan have its quirks, so it's probably best to avoid defaulting it to a value. The questions I think we have are:- Do we need to allow
insertId,templateSuffix, andignoreUnknownValuesto be set at all? The way our library is now is the way its always been-- defaultinginsertIdand not allowing the other properties to be set at all. This issue was the first to bring it up, and it can be solved by just removinginsertId. - If we do allow setting these properties, how can we do it?
2a. A newinsertAllmethod
2b. ??
If we can find a good answer to 2, I'm all for supporting it. I would prefer we blend support into the original
insertmethod somehow, e.g.var row = { json: {/*...*/}, insertId: '...' }; table.insert([row, row], { raw: true }, function() {});
@callmehiphop thoughts?
- Do we need to allow
I was not aware of https://github.com/google/google-api-nodejs-client , Probably you can link to the library in README.md, the current repo name https://github.com/GoogleCloudPlatform/gcloud-node looks like official library. somehow i landed here from google search results, and assumed that this is official library. Thanks.
In fact, we do!
Sorry for the confusion.
thanks, I missed it. If possible, add to this page also. https://cloud.google.com/nodejs/
@stephenplusplus IMO introducing options for this method seems like the way to go. I like the raw option. Maybe we could also include an option for generating the insertIds when raw is not flagged.
Was this resolved by #1068?
Sure was!
19 remaining items
@callmehiphop might this be addressed by the bigquery work you're currently looking at?
@danoscarmike AFAIK there is not a requirement that covers this specific issue, however if we could get some feedback from a BigQuery team member I'd be happy to sneak it in.
@jba could you run this by the best POC on BigQuery? Thanks!
@danoscarmike There's so much going on in this issue that I'm not sure what "this" is. But I can tell you:
- BQ insert IDs are per-row.
- The Go client lets the user provide their own in the "standard" case.
- There are also cases (like uploading Go values directly, which for convenience we allow and convert into rows) where there is no surface for allowing the user to provide an insert ID, and we do not attempt to construct one. This can result in duplicate rows. We document this clearly.
- In their recent set of requests, BQ team did not mention anything about client handling of insert IDs.
If that still leaves open questions for BQ team, let me know what they are and I'll relay.
- added a commit that references this issue
on Jan 17, 2023 - added a commit that references this issue
on May 21, 2025 - added a commit that references this issue
on Jun 4, 2025 - added a commit that references this issue
on Mar 11, 2026
It seems to be failing sometimes when you insert identical rows in parallel. I think this is because the insertId is being generated from a stringified md5 hash of the row. Would you be open to a pull request to pass in your own insert id in an optional metadata object? It could fallback to how it operates now.
https://github.com/GoogleCloudPlatform/gcloud-node/blob/fbf1ecc36c45c48aeee5469e9729a529d94ee610/lib/bigquery/table.js#L904