BigQuery Dynamic SQL with Python

Question

Recently GCP BQ supports for the dynamicSQL. I want to try this with Cloud Functions.

My BQ Dynamic SQL (which worked on UI)

declare cols string;
set cols=(select STRING_AGG (column_name,',') from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name='tbla');
EXECUTE IMMEDIATE format("""select %s from `my_db.tbla`""",cols);

I want to pass the table_name value from my python code, but the thing is, will it support by Python BQ lib?

any example python code?

I tried these codes, but no luck

Code 1:

def hello_gcs(event, context):
    table_name='tbla'
    client = bigquery.Client()
    job_config = bigquery.QueryJobConfig(use_legacy_sql=False)
    sql=( '''
declare cols string;
set cols=(select STRING_AGG (column_name,',') from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name=?);
EXECUTE IMMEDIATE format("""select @ col from `my_db.tbla`""") using cols
''',(table_name))
    query_job = client.query(sql, job_config=job_config)
    results = query_job.result()  
    for row in results:
       print("{} : {} views".format(row.url, row.view_count))

Error:

, line 130, in result raise self._exception google.api_core.exceptions.BadRequest: 400 Query error: Positional parameters are not supported at [3:104]

from google.cloud import bigquery

def hello_gcs(event, context):
    table_name='tbla'
    client = bigquery.Client()
    job_config = bigquery.QueryJobConfig(use_legacy_sql=False)
    sql=( '''
declare cols string;
set cols=(select STRING_AGG (column_name,',') from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name=%s);
EXECUTE IMMEDIATE format("""select @ col from `my_db.tbla`""") using cols
''',(table_name))
    query_job = client.query(sql, job_config=job_config)
    results = query_job.result()  
    for row in results:
       print("{} : {} views".format(row.url, row.view_count))

Error:

line 130, in result raise self._exception google.api_core.exceptions.BadRequest: 400 Syntax error: Illegal input character "%" at [3:104]

What's the error message?

Lak
– Lak

2020-07-31 04:40:29 +00:00
Commented Jul 31, 2020 at 4:40 — Lak
– Lak, Commented Jul 31, 2020 at 4:40
@Lak I have updated the question with the error message

TheDataGuy
– TheDataGuy

2020-07-31 08:40:51 +00:00
Commented Jul 31, 2020 at 8:40 — TheDataGuy
– TheDataGuy, Commented Jul 31, 2020 at 8:40

TheDataGuy · Accepted Answer · 2020-07-31 11:29:22Z

Finally, I found the right syntax for this. Also notices some mistakes in my code.

Mistakes:

SQL query syntax was wrong in SQL variable(sql=('''.......'''))
The print statement in the last line was wrong, my select query doesn't have url and view_count columns.
In Dynamic SQL, we have to pass the single quote in where condition(if it is a string)

Sample working codes:

Code 1:

from google.cloud import bigquery

table_name='tbla'
client = bigquery.Client()

job_config = bigquery.QueryJobConfig(use_legacy_sql=False)

sql="declare cols string;
set cols=(select STRING_AGG (column_name,',') 
from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name='{}');
EXECUTE IMMEDIATE format(\"\"\"select %s from `manan.tbla` \"\"\",cols)".format(table_name)

print(sql)

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

results = query_job.result()
for row in results:
        print(row)

Code 2:

from google.cloud import bigquery

table_name='tbla'
client = bigquery.Client()

job_config = bigquery.QueryJobConfig(use_legacy_sql=False)

sql="declare cols string;
set cols=(select STRING_AGG (column_name,',')
 from `my_db.INFORMATION_SCHEMA.COLUMNS` where table_name='{}');
EXECUTE IMMEDIATE format(\"\"\"select ? from `my_db.tbla` \"\"\") using cols".format(table_name)

print(sql)

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

results = query_job.result()
for row in results:
        print(row)

Code 3:

from google.cloud import bigquery

table_name='tbla'
client = bigquery.Client()

job_config = bigquery.QueryJobConfig(use_legacy_sql=False)

sql="declare cols string;set cols=(select STRING_AGG (column_name,',') from
`my_db.INFORMATION_SCHEMA.COLUMNS` where table_name='{}');EXECUTE IMMEDIATE
format(\"\"\"select @ col from `my_db.tbla` \"\"\") using cols as col".format(table_name)

print(sql)

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

results = query_job.result()
for row in results:
        print(row)

Soumendra Mishra · Accepted Answer · 2020-08-05 18:20:03Z

0

Jinja2 SQL template is a better choice for building dynamic SQL. Example:

create or replace table {{ params.targetTable }}
as
select
    {{ params.targetColumnList|join(',') }},
    cast(null as timestamp) as begin_timestamp,
    cast(null as timestamp) as end_timestamp
from
    {{ params.sourceTable }};

answered Aug 5, 2020 at 18:20

Soumendra Mishra

3,7131 gold badge18 silver badges40 bronze badges

Collectives™ on Stack Overflow

BigQuery Dynamic SQL with Python

I tried these codes, but no luck

Code 1:

2 Answers 2

Mistakes:

Sample working codes:

Code 1:

Code 2:

Code 3:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

I tried these codes, but no luck

Code 1:

2 Answers 2

Mistakes:

Sample working codes:

Code 1:

Code 2:

Code 3:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related