Adding a variable from python environment to queries when using BigQuery Jupyter magic command

Question

I have a few cells in my Jupyter notebook that is querying data from Google's BigQuery. Parts of these queries share the same variables. I am looking for the cleanest way to factor these variables so I don't have to manually set them individually every time I want to make a change.

Here is an example of what I have:

%%bigquery df
select * from `project.dataset.table`
where date_str like '2019-02-%'

Here is what I am looking for:

date_str_query = '2019-02-%' # this variable would be in the python environment

%%bigquery df
select * from `project.dataset.table`
where date_str like date_str_query

Damião Martins · Accepted Answer · 2023-12-11 20:51:52Z

15

##### UPDATE #####

New documentation has a detailed description of various inputs to the magic command. It can be found here: https://cloud.google.com/python/docs/reference/bigquery/latest/magics

Example:

%%bigquery --params {"corpus_name": "hamlet", "limit": 10}
SELECT word, SUM(word_count) as count
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = @corpus_name
GROUP BY word
ORDER BY count DESC
LIMIT @limit

##### ORIGINAL POST #####

Unfortunately, this part is missing from the official documentation, and I had to scrape through the code to finally find a solution. I'm documenting my findings here in hopes to help people with the same issue. There are two ways to do this.

Way 1 [not using the magic command at all]:

date_str_query = '2019-02-%'
sql = """
select * from `project.dataset.table`
where date_str like {0}
""".format(date_str_query)
df = client.query(sql).to_dataframe()

Way 2 [magic command rules!]:

params = {'date_str_query': '%-2019-02-%'}

%%bigquery df --params $params
select * from `project.dataset.table`
where date_str like @date_str_query

I prefer Way 2 because you get SQL syntax highlighting and a bunch of other features with the magic command.

References:

Google's Documentation: https://cloud.google.com/bigquery/docs/visualize-jupyter

The source code: https://github.com/googleapis/google-cloud-python/blob/master/bigquery/google/cloud/bigquery/magics.py

edited Dec 11, 2023 at 20:51

Damião Martins

1,8691 gold badge12 silver badges23 bronze badges

answered Apr 17, 2019 at 15:33

sinapan

1,0001 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

patricksurry Apr 30 at 14:02

I struggled to make this work with a float parameter value, eg. params={"rate": 0.1} getting an error about non-json-serializable values. Eventually I found that I could work around by passing as a string with cast(@rate as float64) in the query. Confusing!

Collectives™ on Stack Overflow

Adding a variable from python environment to queries when using BigQuery Jupyter magic command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related