CODEWITHSUNDEEP
sqlgoogle-bigquerygoogle-cloud-platform
Jeff
Jeff

Reputation: 161

BigQuery Paramaterized Search

I am trying to use paramaters in a SQL statement to BigQuery.

I am using the code I found on https://cloud.google.com/bigquery/docs/parameterized-queries I tried connecting to the same data table they used and to my own.

Everything executes just fine, no errors pop up but no results either. I feel like I am doing some small thing wrong but cannot figure out what it is. Can anyone else?

from google.cloud import bigquery

def query_named_params(corpus, min_word_count):
    client = bigquery.Client()
    query = """
        SELECT word, word_count
        FROM `bigquery-public-data.samples.shakespeare`
        WHERE corpus = @corpus
        AND word_count >= @min_word_count
        ORDER BY word_count DESC;
        """
    query_params = [
        bigquery.ScalarQueryParameter('corpus', 'STRING', corpus),
        bigquery.ScalarQueryParameter(
            'min_word_count', 'INT64', min_word_count)
    ]
    job_config = bigquery.QueryJobConfig()
    job_config.use_legacy_sql = False
    job_config.query_parameters = query_params
    query_job = client.query(query, job_config=job_config)

    query_job.result()  # Wait for job to complete

    # print the results.
    destination_table_ref = query_job.destination
    table = client.get_table(destination_table_ref)
    for row in client.list_rows(table):
        print(row.min_word_count)

SOLUTION

Here is the final code that works. The problem was that the examples given by google set both parts of the parameters with the same name (corpus AND corpus) etc. I had them swapped. I also needed to pass the parameter names in the final query. The below works just fine.

from google.cloud import bigquery

def query_named_params(corpus, word_count):
    client = bigquery.Client()
    query = """
        SELECT word, word_count
        FROM `bigquery-public-data.samples.shakespeare`
        WHERE corpus = @corpus
        AND word_count >= @min_word_count
        ORDER BY word_count DESC;
        """
    query_params = [
        bigquery.ScalarQueryParameter('corpus', 'STRING', 'sonnets'),
        bigquery.ScalarQueryParameter(
            'min_word_count', 'INT64', 126)
    ]
    job_config = bigquery.QueryJobConfig()
    job_config.use_legacy_sql = False
    job_config.query_parameters = query_params
    query_job = client.query(query, job_config=job_config)
    r = query_job.result()  # Wait for job to complete
    for row in r:
        print(row)

if __name__ == '__main__':
    query_named_params('corpus', 'min_word_count')

Upvotes: 1

Views: 117

Answers (1)

Willian Fuks
Willian Fuks

Reputation: 11777

See if this works instead:

(...)
job_config = bigquery.QueryJobConfig()
job_config.use_legacy_sql = False
job_config.query_parameters = query_params
query_job = client.query(query, job_config=job_config)

r = query_job.result()  # Wait for job to complete
for row in r:
    print(row)

In your code you tried connecting to the temporary table to load the results which can be retrieved directly from QueryResults.

Upvotes: 2

Related Questions