Use open source Python libraries

You can choose from among three Python libraries in BigQuery, based on your use case.

	Use case	Maximum data size	Description
bigquery-dataframes	Python based data processing and ML operations with server-side processing	Scalable to multi-terabyte datasets (server-side pushdown)	Pandas and scikit-learn APIs implemented with server-side pushdown. For more information, see Introduction to BigQuery DataFrames.
pandas-gbq	Python based data processing using client side data copy	Limited by client memory	Lets you move data to and from Python DataFrames on the client side. For more information, see the documentation and source code.
google-cloud-bigquery	BigQuery deployment, administration, and SQL-based querying	Limited by client memory	Python package that wraps all the BigQuery APIs. For more information, see the documentation and source code.

Using BigQuery DataFrames, pandas-gbq, and google-cloud-bigquery

The BigQuery DataFrames (bigframes) library provides a pythonic DataFrame and ML API with server-side query processing. The pandas-gbq library provides a simple interface for running queries and uploading pandas DataFrames to BigQuery. It is a thin wrapper around the BigQuery client library, google-cloud-bigquery.

Install the libraries

To use the code samples in this guide, install the bigframes, pandas-gbq, and google-cloud-bigquery packages:

pip install --upgrade bigframes pandas-gbq 'google-cloud-bigquery[bqstorage,pandas]'

Running Queries

All three libraries support querying data stored in BigQuery. Key differences between the libraries include:

	bigquery-dataframes	pandas-gbq	google-cloud-bigquery
Default SQL syntax	GoogleSQL	GoogleSQL (configurable with `pandas_gbq.context.dialect`)	GoogleSQL
Query configurations	Configurable using `bpd.options.bigquery` or `read_gbq` parameters	Sent as dictionary in the format of a query request.	Use the `QueryJobConfig` class, which contains properties for the various API configuration options.

Querying data with the GoogleSQL syntax

The following sample shows how to run a GoogleSQL query with and without explicitly specifying a project. For all three libraries, if a project is not specified, the project will be determined from the default credentials.

bigquery-dataframes

import bigframes.pandas as bpd

# Set partial ordering mode for BigQuery DataFrames.
bpd.options.bigquery.ordering_mode = "partial"


def query_standard_sql(project_id: str = "your-project-id") -> bpd.DataFrame:
    """Runs a standard SQL query using BigQuery DataFrames."""
    sql = """
    SELECT name FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE state = 'TX'
    LIMIT 100
    """

    # Run a query alongside existing SQL. The project will be determined from
    # default credentials.
    df = bpd.read_gbq(sql)

    # Run a query after explicitly specifying a project.
    bpd.close_session()
    bpd.options.bigquery.project = project_id
    df = bpd.read_gbq(sql)
    return df


# Run the sample:
# df = query_standard_sql("your-project-id")
# print(df.head())

pandas-gbq

import pandas

sql = """
    SELECT name
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE state = 'TX'
    LIMIT 100
"""

# Run a Standard SQL query using the environment's default project
df = pandas.read_gbq(sql, dialect="standard")

# Run a Standard SQL query with the project set explicitly
project_id = "your-project-id"
df = pandas.read_gbq(sql, project_id=project_id, dialect="standard")

google-cloud-bigquery

from google.cloud import bigquery

client = bigquery.Client()
sql = """
    SELECT name
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE state = 'TX'
    LIMIT 100
"""

# Run a Standard SQL query using the environment's default project
df = client.query(sql).to_dataframe()

# Run a Standard SQL query with the project set explicitly
project_id = "your-project-id"
df =