1

I am trying to schedule query using big query data transfer api and giving required permission bigquery.admin and enabled the big query transfer api. Permission Documentation: https://cloud.google.com/bigquery-transfer/docs/enable-transfer-service Also tried with project owner permission to the service account. But still giving same error.

Code Documentation: (Setting up a scheduled query with a service account) https://cloud.google.com/bigquery/docs/scheduling-queries

Part in which error coming

 transfer_config = transfer_client.create_transfer_config(
            bigquery_datatransfer.CreateTransferConfigRequest(
                parent=parent,
                transfer_config=transfer_config,
                service_account_name=service_account_name,
            )
        )

Error StackTrace

Traceback (most recent call last):
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/google/api_core/grpc_helpers.py", line 73, in error_remapped_callable
    return callable_(*args, **kwargs)
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/grpc/_channel.py", line 946, in __call__
    return _end_unary_response_blocking(state, call, False, None)
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/grpc/_channel.py", line 849, in _end_unary_response_blocking
    raise _InactiveRpcError(state)
grpc._channel._InactiveRpcError: <_InactiveRpcError of RPC that terminated with:
        status = StatusCode.PERMISSION_DENIED
        details = "The caller does not have permission"
        debug_error_string = "{"created":"@1633536014.842657676","description":"Error received from peer ipv4:142.250.192.138:443","file":"src/core/lib/surface/call.cc","file_line":1070,"grpc_message":"The caller does not have permission","grpc_status":7}"
>

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "__main__.py", line 728, in <module>
    mbc.schedule_query()
  File "/home/ubuntu/prod/trell-ds-framework/data_engineering/data_migration/schedule_quries.py", line 62, in schedule_query
    service_account_name=service_account_name,
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/google/cloud/bigquery_datatransfer_v1/services/data_transfer_service/client.py", line 647, in create_transfer_config
    response = rpc(request, retry=retry, timeout=timeout, metadata=metadata,)
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/google/api_core/gapic_v1/method.py", line 145, in __call__
    return wrapped_func(*args, **kwargs)
  File "/home/ubuntu/prod/venv_trellai/lib/python3.6/site-packages/google/api_core/grpc_helpers.py", line 75, in error_remapped_callable
    six.raise_from(exceptions.from_grpc_error(exc), exc)
  File "<string>", line 3, in raise_from
google.api_core.exceptions.PermissionDenied: 403 The caller does not have permission

Service file have all these credentials below.

BigQuery Admin

BigQuery Data Transfer Service Agent

Service Account Token Creator

Storage Admin

I am already setting up json authentication cred in environment variable but still gives permission error.

os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = Constants.BIG_QUERY_SERVICE_ACCOUNT_CRED

Can anyone help me out here? Thanks in advance.

iamabhaykmr
  • 1,803
  • 3
  • 24
  • 49

2 Answers2

1

Take a look at this page on authentication: https://cloud.google.com/bigquery/docs/authentication/service-account-file#python

Assuming you're using Service Account, you can provide the credentials explicitly to confirm they work as expected:

from google.cloud import bigquery
from google.oauth2 import service_account

# TODO(developer): Set key_path to the path to the service account key
#                  file.
# key_path = "path/to/service_account.json"

credentials = service_account.Credentials.from_service_account_file(
    key_path, scopes=["https://www.googleapis.com/auth/cloud-platform"],
)

client = bigquery.Client(credentials=credentials, project=credentials.project_id,)
Sergii Tkachenko
  • 121
  • 6
  • 1
    Thanks for the effort @sergii . Its able to authentication print(client_) print this – iamabhaykmr Oct 06 '21 at 18:23
  • So now you know the credentials are correct, the next logical step would be to confirm that 1) `GOOGLE_APPLICATION_CREDENTIALS` exported correctly. Try to export it as actual environment variable, not set `os.environ`. It's possible that fcloud auth init happens before you set os.environ. 2) Confirm Python process can read the file set in GOOGLE_APPLICATION_CREDENTIALS. When the variable set properly, authentication should happen automatically, see https://cloud.google.com/docs/authentication/getting-started. – Sergii Tkachenko Oct 06 '21 at 18:32
  • It is able to authenticate and create transfer config using ` transfer_config = bigquery_datatransfer.TransferConfig(` . Its just giving permission error at `transfer_client.create_transfer_config(` – iamabhaykmr Oct 06 '21 at 18:48
  • Also I tried using authenticating using this. It works. credentials = service_account.Credentials.from_service_account_file(Constants.BIG_QUERY_TRELLATALE_SERVICE_ACCOUNT_CRED) transfer_client = bigquery_datatransfer.DataTransferServiceClient(credentials=credentials) – iamabhaykmr Oct 06 '21 at 18:56
  • Also printed, ` print("os env ", os.environ['GOOGLE_APPLICATION_CREDENTIALS'])` and it printed correct authentication service account cred – iamabhaykmr Oct 06 '21 at 19:01
  • Does this service account have permissions for `create_transfer_config`? https://cloud.google.com/bigquery-transfer/docs/working-with-transfers#updating_credentials – Sergii Tkachenko Oct 06 '21 at 19:03
  • Yes, it does . BigQuery Admin BigQuery Data Transfer Service Agent Service Account Token Creator Storage Admin Currently service account have these all permission – iamabhaykmr Oct 06 '21 at 19:21
  • Hm. Maybe `parent` incorrect, and pointing to another project where this account doesn't have permissions? Also take a look at the audit logs in the Log Explorer, they may have more info on why your request gets rejected. – Sergii Tkachenko Oct 06 '21 at 20:04
0

I would recommend you to see if the service account you are using is referring to the project you are using and has all the permissions needed to schedule the query. My best guess is that you are pointing to another project with the service account.

Also, you need one extra role for the service account that is the next one “Service Account Token Creator”.

Eduardo Ortiz
  • 715
  • 3
  • 14