How to pass a variable in a full cell magic command in Jupyter/Colab?

Question

My code uses SQL to query a database hosted in BigQuery. Say I have a list of items stored in a variable:

list = ['a','b','c']

And I want to use that list as a parameter on a query like this:

%%bigquery --project xxx query

SELECT *
FROM `xxx.database.table`
WHERE items in list

As the magic command that calls the database is a full-cell command, how can I make some escape to get it to call the environment variables in the SQL query?

Answer 1

You can try UNNEST and the query in BIGQUERY works like this:

SELECT * FROM `xx.mytable` WHERE items in UNNEST (['a','b','c']) 

In your code it should look like this:

SELECT * FROM `xx.mytable` WHERE items in UNNEST (list)

EDIT

I found two different ways to pass variables in Python.

The first approach is below. Is from google documentation[1].

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = """
    SELECT * FROM `xx.mytable` WHERE items in UNNEST (@list)
 """
job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ArrayQueryParameter("list", "STRING", ["a", "b", "c"]),
    ]
)
query_job = client.query(query, job_config=job_config)  # Make an API request.

for row in query_job:
    print("{}: \t{}".format(row.name, row.count))

The second approach is in the next document[2]. In your code should look like:

params = {'list': '[“a”,”b”,”c”]'}
%%bigquery df --params $params --project xxx query
select * from `xx.mytable` 
where items in unnest (@list)

I also found some documentation[3] where it shows the parameters for %%bigquery magic.

[1]https://cloud.google.com/bigquery/docs/parameterized-queries#using_arrays_in_parameterized_queries [2]https://notebook.community/GoogleCloudPlatform/python-docs-samples/notebooks/tutorials/bigquery/BigQuery%20query%20magic [3]https://googleapis.dev/python/bigquery/latest/magics.html