BigQuery load job fails due to permission issues

Question

I'm using the following code to upload data from CSV file in Google Storage to BigQuery table:

  from google.cloud import bigquery
  client = bigquery.Client()
  dataset_id = 'e'
  dataset_ref = client.dataset(dataset_id)
  job_config = bigquery.LoadJobConfig()
  job_config.schema = [
      bigquery.SchemaField('itemcode', 'STRING'),
      bigquery.SchemaField('itemname', 'STRING'),
  ]
  job_config.skip_leading_rows = 1
  # The source format defaults to CSV, so the line below is optional.
  #job_config.source_format = bigquery.SourceFormat.CSV
  uri = 'gs://e/2018-07-15/inventory.csv.gz'
  load_job = client.load_table_from_uri(
      uri,
      dataset_ref.table('inventory'),
      job_config=job_config)  # API request

  assert load_job.job_type == 'load'

  load_job.result()  # Waits for table load to complete.

  assert load_job.state == 'DONE'
  assert client.get_table(dataset_ref.table('inventory')).num_rows == 10

The Project is called BI it's ID is: BI-bi I have a dataset called e in this dataset I have a table called inventory with two columns itemcode and itemname.

For some reason this code fail with the following error:

> google.api_core.exceptions.Forbidden: 403 POST
> https://www.googleapis.com/bigquery/v2/projects/USER/jobs: Access
> Denied: Dataset BI-bi:BI: The user [email protected]
> does not have bigquery.tables.create permission for dataset BI-bi:BI

When I'm going to the USER permissions I see that it has the following roles:

Big Query Job user
Storage Admin

The other options are:

I'm lost here. The docs doesn't explain what to do here.

error log:

> Traceback (most recent call last):   File "inventory.py", line 160,
> in <module>
>     job_config=job_config)  # API request   File "/usr/local/lib/python2.7/dist-packages/google/cloud/bigquery/client.py",
> line 689, in load_table_from_uri
>     job._begin(retry=retry)   File "/usr/local/lib/python2.7/dist-packages/google/cloud/bigquery/job.py",
> line 397, in _begin
>     method='POST', path=path, data=self._build_resource())   File "/usr/local/lib/python2.7/dist-packages/google/cloud/bigquery/client.py",
> line 271, in _call_api
>     return call()   File "/usr/local/lib/python2.7/dist-packages/google/api_core/retry.py",
> line 260, in retry_wrapped_func
>     on_error=on_error,   File "/usr/local/lib/python2.7/dist-packages/google/api_core/retry.py",
> line 177, in retry_target
>     return target()   File "/usr/local/lib/python2.7/dist-packages/google/cloud/_http.py", line
> 293, in api_request
>     raise exceptions.from_http_response(response)
> google.api_core.exceptions.Forbidden: 403 POST
> https://www.googleapis.com/bigquery/v2/projects/USER/jobs: Access
> Denied: Dataset BI-bi:BI: The user [email protected]
> does not have bigquery.tables.create permission for dataset BI-bi:BI

The error message talks about BI-bi:BI, not BI-bi:e. It's as if the dataset weren't specified properly - which I can see could cause a problem. Are you able to dump the load job request to see what it's doing? — Jon Skeet
The documentation for the specific library you're using should tell you how to enable request logging. I'm guessing this is the Python library? But is that error message exactly what's shown, and is the code exactly as shown as well? Or might that BI/e difference be due to not being the result of running that exact code? — Jon Skeet
That's a stack trace - I'm asking for a log of the HTTP request that was sent to BigQuery. It might also be instructive to print out dataset_ref.table('inventory') — Jon Skeet
You could dump load_job as well... that would be useful, if it shows the JSON that would be sent. — Jon Skeet

Steeve Steeve · Accepted Answer · 2018-09-04T15:44:00

I was able to find this public reference saying:

When you are loading data into BigQuery from Cloud Storage, you must be granted the bigquery.dataOwner or bigquery.dataEditor role at the project level or at the dataset level.

And

To load data from a Cloud Storage bucket, you must be granted storage.objects.get permissions at the project level or on that individual bucket.

This will help you to solve the permission issue while loading data from your bucket to BigQuery

BigQuery load job fails due to permission issues

1 Answers