allow the body of `%%bigquery` cell magic to be a `$variable`

[tswast]

Is your feature request related to a problem? Please describe.

When using BigQuery notebooks. There are queries that would be nice to be parametrized, but the parameters are in places where BigQuery parameterized queries don't work. For example, you can't parametrize the table ID.

Describe the solution you'd like

When a $variable is the only thing in the query body, replace it with the value from the variable in Python. This is similar to how the first %%bigquery line works.

Cell 1

yourprojectid = "swast-scratch"
query = "SELECT * FROM something"

Cell 2

%%bigquery --project $yourprojectid
$query

Describe alternatives you've considered

One could try and find any $variable in the query body, but this would be a breaking change.

Additional context

Currently the %%bigquery has some logic to see if the body is a table ID or a full query. We should retain this logic, and it should happen after the replacement of the variable. Detecting if the body is only a $variable should be similar to the detection of if the body is only a table ID.

Feature request is from an email thread "python magics - fully parameterized query" with @minhaz and @shollyman

[plamut]

I have some free cycles next week, is this already in progress or just assigned for now?
Thanks! 🙂

Update: Discussed offline, I can handle this and then move on to some v3 issues, and can assign Lo as a reviewer for onboarding purposes.