pybigquery

SQLAlchemy dialect and API client for BigQuery.

Usage

SQLAchemy

from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *
engine = create_engine('bigquery://project')
table = Table('dataset.table', MetaData(bind=engine), autoload=True)
print(select([func.count('*')], from_obj=table).scalar())

API Client

from pybigquery.api import ApiClient
api_client = ApiClient()
print(api_client.dry_run_query(query=sqlstr).total_bytes_processed)

Project

project in bigquery://project is used to instantiate BigQuery client with the specific project ID. To infer project from the environment, use bigquery:// – without project

Authentication

Follow the Google Cloud library guide for authentication. Alternatively, you can provide the path to a service account JSON file in create_engine():

engine = create_engine('bigquery://', credentials_path='/path/to/keyfile.json')

Location

To specify location of your datasets pass location to create_engine():

engine = create_engine('bigquery://project', location="asia-northeast1")

Table names

To query tables from non-default projects, use the following format for the table name: project.dataset.table, e.g.:

sample_table = Table('bigquery-public-data.samples.natality')

Batch size

By default, arraysize is set to 5000. arraysize is used to set the batch size for fetching results. To change it, pass arraysize to create_engine():

engine = create_engine('bigquery://project', arraysize=1000)

Adding a Default Dataset

If you want to have the Client use a default dataset, specify it as the “database” portion of the connection string.

engine = create_engine('bigquery://project/dataset')

When using a default dataset, don’t include the dataset name in the table name, e.g.:

table = Table('table_name')

Note that specyfing a default dataset doesn’t restrict execution of queries to that particular dataset when using raw queries, e.g.:

# Set default dataset to dataset_a
engine = create_engine('bigquery://project/dataset_a')

# This will still execute and return rows from dataset_b
engine.execute('SELECT * FROM dataset_b.table').fetchall()

Connection String Parameters

There are many situations where you can’t call create_engine directly, such as when using tools like Flask SQLAlchemy. For situations like these, or for situations where you want the Client to have a default_query_job_config, you can pass many arguments in the query of the connection string.

The credentials_path, location, and arraysize parameters are used by this library, and the rest are used to create a QueryJobConfig

Note that if you want to use query strings, it will be more reliable if you use three slashes, so 'bigquery:///?a=b' will work reliably, but 'bigquery://?a=b' might be interpreted as having a “database” of ?a=b, depending on the system being used to parse the connection string.

Here are examples of all the supported arguments. Any not present are either for legacy sql (which isn’t supported by this library), or are too complex and are not implemented.

engine = create_engine(
    'bigquery://some-project/some-dataset' '?'
    'credentials_path=/some/path/to.json' '&'
    'location=some-location' '&'
    'arraysize=1000' '&'
    'clustering_fields=a,b,c' '&'
    'create_disposition=CREATE_IF_NEEDED' '&'
    'destination=different-project.different-dataset.table' '&'
    'destination_encryption_configuration=some-configuration' '&'
    'dry_run=true' '&'
    'labels=a:b,c:d' '&'
    'maximum_bytes_billed=1000' '&'
    'priority=INTERACTIVE' '&'
    'schema_update_options=ALLOW_FIELD_ADDITION,ALLOW_FIELD_RELAXATION' '&'
    'use_query_cache=true' '&'
    'write_disposition=WRITE_APPEND'
)

Creating tables

To add metadata to a table:

table = Table('mytable', ..., bigquery_description='my table description', bigquery_friendly_name='my table friendly name')

To add metadata to a column:

Column('mycolumn', doc='my column description')

Requirements

Install using

pip install pybigquery

Testing

Load sample tables:

./scripts/load_test_data.sh

This will create a dataset test_pybigquery with tables named sample_one_row and sample.

Set up an environment and run tests:

pyvenv .env
source .env/bin/activate
pip install -r dev_requirements.txt
pytest

Metadata

Release files for pybigquery 0.4.15

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pybigquery 0.4.15
File	Size	Uploaded
pybigquery-0.4.15.tar.gz	16.3 kB	Apr 23, 2020	Details

Built distribution (wheel)

Table of built distributions (wheels) for pybigquery 0.4.15
File	Interpreter	ABI	Platform	Reset
pybigquery-0.4.15-py2.py3-none-any.whl 11.3 kB Apr 23, 2020	Python 2, Python 3	none	any	Details

Total release size: 27.6 kB

Release files / pybigquery-0.4.15.tar.gz

Download URL	pybigquery-0.4.15.tar.gz
Size	16.3 kB
Tags	Source
SHA-256 checksum How to use checksums	`8cd2b0b947867eea70c39c85e2c6b69c7187fd649d9472658dd284391c5584c8`
BLAKE2b-256 checksum How to use checksums	`39f37aef0befd6f1903b4b5c135059bb9b1d6e6eae01abf0a014d67f2def75d1`
Upload date	Apr 23, 2020
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/3.1.1 pkginfo/1.5.0.1 requests/2.23.0 setuptools/46.1.3.post20200325 requests-toolbelt/0.9.1 tqdm/4.43.0 CPython/3.7.3`

Release files / pybigquery-0.4.15-py2.py3-none-any.whl

Download URL	pybigquery-0.4.15-py2.py3-none-any.whl
Size	11.3 kB
Tags	Python 2 Python 3
SHA-256 checksum How to use checksums	`753b0df79df0918a11f194ec71b91d8859f1977289429babf7de328c7b1a013f`
BLAKE2b-256 checksum How to use checksums	`3c9a1f6f748396f7cd499ae3b2ea34e829ba3b8054ce09979646d2de20cdb959`
Upload date	Apr 23, 2020
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/3.1.1 pkginfo/1.5.0.1 requests/2.23.0 setuptools/46.1.3.post20200325 requests-toolbelt/0.9.1 tqdm/4.43.0 CPython/3.7.3`