Skip to content

Connecting to BigQuery using Opteryx

This short guide demonstrates how to connect to BigQuery using Opteryx using SQLAlchemy.

Installation

Install Opteryx and libraries for connecting to BigQuery.

$ pip install opteryx sqlalchemy sqlalchemy-bigquery google-cloud-bigquery-storage

Registering BigQuery with Opteryx

Create a SQLAlchemy Engine and register it as a store with Opteryx.

import opteryx
from opteryx.connectors import SqlConnector
from sqlalchemy.engine import create_engine

# Create an SqlAlchemy Engine connecting to your GCP project.
# See the following page for more information:
# https://pypi.org/project/sqlalchemy-bigquery/
GCP_PROJECT:str = "your GCP project"
engine = create_engine(f"bigquery://{GCP_PROJECT}")

# Register as a store, so we know queries for relations with the
# provided prefix (bq) should be directed to BigQuery
opteryx.register_store(
    prefix="bq",  # The prefix to indicate to use this store
    connector=SqlConnector,
    remove_prefix=True,  # the prefix isn't part of the BigQuery table name
    engine=engine  # The SqlAlchemy Engine we created above
)

Parameters Explained

  • prefix A string to identify which queries should be directed to this database.
  • connector The type of connector to use.
  • remove_prefix Boolean flag to indicate if the prefix should be removed when querying the actual BigQuery table.
  • engine SQLAlchemy Engine to connect to BigQuery.

Querying BigQuery from Opteryx

# Execute query against the store.
result = opteryx.query("SELECT * FROM bq.planets LIMIT 5;")
result.head()

Other Services

Opteryx supports connecting to other SQL engines using this method, such as MySQL, CockroachDB and DuckDB.