CODEWITHSUNDEEP
google-bigquerygoogle-cloud-platform
Pramod Sripada
Pramod Sripada

Reputation: 261

Error with parametrized query in Google BigQuery

I am trying to write a query using Google BigQuery Python API. I am setting the project id and dataset name as parameters. I have looked into the parametrized queries implementation on Google Github.io. But when executing the query I get the following error

google.api_core.exceptions.BadRequest: 400 Invalid table name: @project:@dataset.AIRPORTS

I am confused whether we can substitute the project, dataset names with parameters.

Below is my code

from google.cloud import bigquery

client = bigquery.Client.from_service_account_json('service_account.json')
project = client.project

datasets = list(client.list_datasets())
dataset = datasets[0]
dataset_id = dataset.dataset_id

QUERY = (
        'SELECT * '
        'FROM `{}.{}.AIRPORTS`'.format(project, dataset_id)
    )

query = (
        'SELECT * '
        'FROM `@[email protected]`'
    )

TIMEOUT = 30
param1 = bigquery.ScalarQueryParameter('project', 'STRING', project)
param2 = bigquery.ScalarQueryParameter('dataset', 'STRING', dataset_id)
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = [param1, param2]

query_job = client.query(
    query, job_config=job_config)

iterator = query_job.result(timeout=TIMEOUT)
rows = list(iterator)

print(rows)

Upvotes: 2

Views: 3028

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

You can only use parameters in place of expressions, such as column_name = @param_value in a WHERE clause. A table name is not an expression, so you cannot use parameters in place of the project or dataset names. Note also that you need to use standard SQL in order to use parameters.

Upvotes: 4

Related Questions