As of January 1, 2020 this library no longer supports Python 2 on the latest released version. Library versions released prior to that date will continue to be available. For more information please visit Python 2 support on Google Cloud.

IPython Magics for BigQuery

To use these magics, you must first register them. Run the %load_ext magic in a Jupyter notebook cell.

%load_ext google.cloud.bigquery

This makes the %%bigquery magic available.

Code Samples

Running a query:

%%bigquery
SELECT name, SUM(number) as count
FROM `bigquery-public-data.usa_names.usa_1910_current`
GROUP BY name
ORDER BY count DESC
LIMIT 3

Running a parameterized query:

%%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

API Reference

IPython Magics

Install bigquery-magics and call %load_ext bigquery_magics to use the %%bigquery cell magic.

See the BigQuery Magics reference documentation.

class google.cloud.bigquery.magics.magics.Context

Storage for objects to be used throughout an IPython notebook session.

A Context object is initialized when the magics module is imported, and can be found at google.cloud.bigquery.magics.context.

property bigquery_client_options

client options to be used through IPython magics.

Note::

The client options do not need to be explicitly defined if no special network connections are required. Normally you would be using the https://bigquery.googleapis.com/ end point.

Example

Manually setting the endpoint:

>>> from google.cloud.bigquery import magics
>>> client_options = {}
>>> client_options['api_endpoint'] = "https://some.special.url"
>>> magics.context.bigquery_client_options = client_options

Type

google.api_core.client_options.ClientOptions

property bqstorage_client_options

client options to be used through IPython magics for the storage client.

Note::

The client options do not need to be explicitly defined if no special network connections are required. Normally you would be using the https://bigquerystorage.googleapis.com/ end point.

Example

Manually setting the endpoint:

>>> from google.cloud.bigquery import magics
>>> client_options = {}
>>> client_options['api_endpoint'] = "https://some.special.url"
>>> magics.context.bqstorage_client_options = client_options

Type

google.api_core.client_options.ClientOptions

property credentials

Credentials to use for queries performed through IPython magics.

Note

These credentials do not need to be explicitly defined if you are using Application Default Credentials. If you are not using Application Default Credentials, manually construct a google.auth.credentials.Credentials object and set it as the context credentials as demonstrated in the example below. See auth docs for more information on obtaining credentials.

Example

Manually setting the context credentials:

>>> from google.cloud.bigquery import magics
>>> from google.oauth2 import service_account
>>> credentials = (service_account
...     .Credentials.from_service_account_file(
...         '/path/to/key.json'))
>>> magics.context.credentials = credentials

Type

google.auth.credentials.Credentials

property default_query_job_config

Default job configuration for queries.

The context’s QueryJobConfig is used for queries. Some properties can be overridden with arguments to the magics.

Example

Manually setting the default value for maximum_bytes_billed to 100 MB:

>>> from google.cloud.bigquery import magics
>>> magics.context.default_query_job_config.maximum_bytes_billed = 100000000

Type

google.cloud.bigquery.job.QueryJobConfig

property progress_bar_type

Default progress bar type to use to display progress bar while executing queries through IPython magics.

Note::

Install the tqdm package to use this feature.

Example

Manually setting the progress_bar_type:

>>> from google.cloud.bigquery import magics
>>> magics.context.progress_bar_type = "tqdm_notebook"

Type

str

property project

Default project to use for queries performed through IPython magics.

Note

The project does not need to be explicitly defined if you have an environment default project set. If you do not have a default project set in your environment, manually assign the project as demonstrated in the example below.

Example

Manually setting the context project:

>>> from google.cloud.bigquery import magics
>>> magics.context.project = 'my-project'

Type

str