CODEWITHSUNDEEP
pythongoogle-cloud-platformgoogle-bigquery
aaron Nottingham
aaron Nottingham

Reputation: 1

BigQuery - Scheduled Query Error "PermissionDenied: 403 The caller does not have permission"

Having an issue with Scheduled Queries, using a python script, I get the error: "google.api_core.exceptions.PermissionDenied: 403 The caller does not have permission", when running the script below.

#!/usr/bin/python
import sys
import json
import time
from google.cloud import bigquery_datatransfer
from google.oauth2 import service_account

prj_id = "project-id"
ds_id = "dataset_id"

gcp_info = json.load(open("key-file.json"))

creds = service_account.Credentials.from_service_account_info(gcp_info)
s_creds = creds.with_scopes(
    [
        'https://www.googleapis.com/auth/cloud-platform',
        'https://www.googleapis.com/auth/bigquery',
    ]
)
s_acc = "service-account@project_id.iam.gserviceaccount.com"

bq_tc = bigquery_datatransfer.DataTransferServiceClient(credentials=s_creds)
dataset = prj_id + '.' + ds_id + '.'

def main():
    argc = len(sys.argv) - 1
    if argc != 1:
        print("Usage: python3 /root/gcp_new-query.py <Temp-Table>")
        sys.exit()


    t_id = dataset + sys.argv[1] + '-temp'
    t2_id = dataset + sys.argv[1] + '-data'

    q2 = """
    DELETE FROM `{}` WHERE AddressID > 0 AND MsgTS < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), 
    INTERVAL 60 MINUTE)
    """.format(t_id)

    p = bq_tc.common_project_path(prj_id)

    tc_cfg2 = bigquery_datatransfer.TransferConfig(
        destination_dataset_id=ds_id,
        display_name=sys.argv[1]+"-RM-Old-Data",
        data_source_id="scheduled_query",
        params={
            "query": q2,
        },
        schedule="every hour",
    )

    tc_cfg2 = bq_tc.create_transfer_config(
        bigquery_datatransfer.CreateTransferConfigRequest(
            parent=p,
            transfer_config=tc_cfg2,
            service_account_name=s_acc,
        )
    )

    print("Created scheduled query '{}'".format(tc_cfg2.name))
main()

As soon as it gets into create_transfer_config(), I get the error. I have gone through the documentation and made sure all of the right permissions have been granted to "service-account@project_id", those being:

Am I missing permissions or is there something in my script that's not quite right? Apologies if I haven't explained everything all that well.

EDIT: I have also made sure that the service account has an associated key json file.

-A.

Upvotes: 0

Views: 576

Answers (1)

aaron Nottingham
aaron Nottingham

Reputation: 1

I found a solution in the end. I simply used:

import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "/path/to/json.json"

In the place of:

gcp_info = json.load(open("key-file.json"))

creds = service_account.Credentials.from_service_account_info(gcp_info)
s_creds = creds.with_scopes(
    [
        'https://www.googleapis.com/auth/cloud-platform',
        'https://www.googleapis.com/auth/bigquery',
    ]
)
s_acc = "service-account@project_id.iam.gserviceaccount.com"

Upvotes: 0

Related Questions