fix(bigquery): allow insert to target dataset/table in another project

[alvarowolfx]

Allow insertAll API to target tables in a dataset that lives in a separated project, other than the project usage on the main client and which is used for billing.

I haven't added integration tests, since we would not have write access to two different projects in our CI pipelines. But I tested locally with this given code:

$ export GOOGLE_CLOUD_PROJECT=projectA
$ bq mk --table test_dataset.ruby-test-001 name:STRING,value:NUMERIC

bigquery = Google::Cloud::Bigquery.new
project_id = "projectB"
dataset_id = "test_dataset"
table_id = "ruby-test-001"
dataset  = bigquery.dataset dataset_id, project_id: project_id
table    = dataset.table table_id

row_data = [
  { name: "Alice", value: 5  },
  { name: "Bob",   value: 10 }
]
response = table.insert row_data

if response.success?
  puts "Inserted rows successfully"
else
  puts "Failed to insert #{response.error_rows.count} rows"
end

inserter = table.insert_async do |result|
  if result.error?
    puts result.error
  else
    puts "inserted #{result.insert_count} rows with #{result.error_count} errors"
  end
end

inserter.insert row_data

inserter.stop.wait!

Follow up on #27681 and #27368

[dazuma]

I don't understand how this changes things. Under what circumstance would the dataset's project ID be different from the project ID of the service backing the dataset?

[dazuma]

Oh, I see, it's now possible for the client (whose project is tied to the billing account) to get and construct a Dataset object from a different project. So this is actually a bug fix rather than a feature: in such a case, we're making sure the correct project gets set when doing an insert rows operation. Is that correct?

[alvarowolfx]

@dazuma This is a very common scenario among BigQuery users, where operations are run in project A to modify data stored in project B. So you need to target tables in a dataset that lives in a separated project, other than the project usage on the main client and which for example is used for billing.

Another example: a central project for an org holds all the long term stored data, but each department isolates their operational costs from one another by using individual projects to run queries and load jobs, etc.

This is the same need that was mentioned on #27681 and #27368

[alvarowolfx]

Oh, I see, it's now possible for the client (whose project is tied to the billing account) to get and construct a Dataset object from a different project. So this is actually a bug fix rather than a feature: in such a case, we're making sure the correct project gets set when doing an insert rows operation. Is that correct?

yeah, it might make sense to change it to a fix instead of feature.

[dazuma]

If this is indeed a bug fix (and I think it should be: I would expect a new feature to include an addition to the public interface, and this PR doesn't make any public interface changes at all), then I'd like to see new unit tests that show the fixed case. You can mock out the backend service, as many of the unit tests do, but just test that the correct project ID gets passed down to the backend service.

[dazuma]

Thanks much!