pandasgoogle-bigqueryjupyter-notebookgoogle-colaboratorymagic-command

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


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?


Solution

  • 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