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 as the default configuration for BigQuery
# DataFrames.
bpd.options.bigquery.ordering_mode = "partial"


def query_standard_sql(project_id: str = "your-project-id") -> bpd.DataFrame:
    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

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 = client.query(sql, project=project_id).to_dataframe()

Querying data with the legacy SQL syntax

The following sample shows how to run a query using legacy SQL syntax. See the GoogleSQL migration guide for guidance on updating your queries to GoogleSQL.

bigquery-dataframes

BigQuery DataFrames does not support legacy SQL syntax. Use GoogleSQL syntax instead.

pandas-gbq

import pandas

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

df = pandas.read_gbq(sql, dialect="legacy")

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
"""
query_config = bigquery.QueryJobConfig(use_legacy_sql=True)

df = client.query(sql, job_config=query_config).to_dataframe()

Using the BigQuery Storage API to download large results

Use the BigQuery Storage API to speed up downloads of large results by 15 to 31 times.

bigquery-dataframes

import bigframes.pandas as bpd

import pandas as pd

# Set partial ordering mode as the default configuration for BigQuery
# DataFrames.
bpd.options.bigquery.ordering_mode = "partial"


def query_bqstorage() -> pd.DataFrame:
    sql = """
    SELECT name FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE state = 'TX'
    LIMIT 100
    """

    # Read query results into a server-side DataFrame without downloading data.
    df = bpd.read_gbq(sql)

    # When downloading results to an in-memory pandas DataFrame,
    # bigquery-dataframes automatically uses the BigQuery Storage API if
    # installed.
    pandas_df = df.to_pandas()
    return pandas_df

pandas-gbq

import pandas

sql = "SELECT * FROM `bigquery-public-data.irs_990.irs_990_2012`"

# Use the BigQuery Storage API to download results more quickly.
df = pandas.read_gbq(sql, dialect="standard", use_bqstorage_api=True)

google-cloud-bigquery

from google.cloud import bigquery

client = bigquery.Client()
sql = "SELECT * FROM `bigquery-public-data.irs_990.irs_990_2012`"

# The client library uses the BigQuery Storage API to download results to a
# pandas dataframe if the API is enabled on the project, the
# `google-cloud-bigquery-storage` package is installed, and the `pyarrow`
# package is installed.
df = client.query(sql).to_dataframe()

Running a query with a configuration

Sending a configuration with a BigQuery API request is required to perform certain complex operations, such as running a parameterized query or specifying a destination table to store the query results. In bigquery-dataframes (read_gbq) and pandas-gbq, the configuration must be sent as a dictionary in the format of a query request. In google-cloud-bigquery, job configuration classes are provided, such as QueryJobConfig, which contain the necessary properties to configure complex jobs.

The following sample shows how to run a query with named parameters.

bigquery-dataframes

import bigframes.pandas as bpd

# Set partial ordering mode as the default configuration for BigQuery
# DataFrames.
bpd.options.bigquery.ordering_mode = "partial"


def query_parameters() -> bpd.DataFrame:
    sql = """
    SELECT name FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE state = @state
    LIMIT 100
    """

    query_config = {
        "query": {
            "parameterMode": "NAMED",
            "queryParameters": [
                {
                    "name": "state",
                    "parameterType": {"type": "STRING"},
                    "parameterValue": {"value": "TX"},
                }
            ],
        }
    }

    df = bpd.read_gbq(sql, configuration=query_config)
    return df

pandas-gbq

import pandas

sql = """
    SELECT name
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE state = @state
    LIMIT @limit
"""
query_config = {
    "query": {
        "parameterMode": "NAMED",
        "queryParameters": [
            {
                "name": "state",
                "parameterType": {"type": "STRING"},
                "parameterValue": {"value": "TX"},
            },
            {
                "name": "limit",
                "parameterType": {"type": "INTEGER"},
                "parameterValue": {"value": 100},
            },
        ],
    }
}

df = pandas.read_gbq(sql, configuration=query_config)

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 = @state
    LIMIT @limit
"""
query_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("state", "STRING", "TX"),
        bigquery.ScalarQueryParameter("limit", "INTEGER", 100),
    ]
)

df = client.query(sql, job_config=query_config).to_dataframe()

Loading a pandas DataFrame to a BigQuery table

All three libraries support uploading data from a pandas DataFrame to a new table in BigQuery. Key differences include:

	bigquery-dataframes	pandas-gbq	google-cloud-bigquery
Type support	Converts the local pandas DataFrame to a `bigframes.pandas.DataFrame` using `read_pandas` (using Parquet or CSV under the hood), supporting nested and array values. You then save it to a table with `to_gbq`.	Converts the DataFrame to CSV format before sending to the API, which does not support nested or array values.	Converts the DataFrame to Parquet or CSV format before sending to the API, which supports nested and array values. Choose Parquet for struct and array values and CSV for date and time serialization flexibility. Parquet is the default choice. Note that `pyarrow`, which is the parquet engine used to send the DataFrame data to the BigQuery API, must be installed to load the DataFrame to a table.
Load configurations	Use the `if_exists` parameter (`'fail'`, `'replace'`, or `'append'`) when saving with `to_gbq`.	You can optionally specify a table schema.	Use the `LoadJobConfig` class, which contains properties for the various API configuration options.

bigquery-dataframes

import bigframes.pandas as bpd

import pandas as pd

# Set partial ordering mode as the default configuration for BigQuery
# DataFrames.
bpd.options.bigquery.ordering_mode = "partial"


def upload_from_dataframe(
    table_id: str = "your-project.your_dataset.your_table_name",
) -> bpd.DataFrame:
    # Create a local pandas DataFrame.
    df = pd.DataFrame(
        {
            "my_string": ["a", "b", "c"],
            "my_int64": [1, 2, 3],
            "my_float64": [4.0, 5.0, 6.0],
        }
    )

    # Convert the local pandas DataFrame to a BigQuery DataFrame.
    bq_df = bpd.read_pandas(df)

    # Write the DataFrame to a BigQuery table.
    bq_df.to_gbq(table_id, if_exists="replace")
    return bq_df

pandas-gbq

import pandas

df = pandas.DataFrame(
    {
        "my_string": ["a", "b", "c"],
        "my_int64": [1, 2, 3],
        "my_float64": [4.0, 5.0, 6.0],
        "my_timestamp": [
            pandas.Timestamp("1998-09-04T16:03:14"),
            pandas.Timestamp("2010-09-13T12:03:45"),
            pandas.Timestamp("2015-10-02T16:00:00"),
        ],
    }
)
table_id = "my_dataset.new_table"

df.to_gbq(table_id)

google-cloud-bigquery

The google-cloud-bigquery package requires the pyarrow library to serialize a pandas DataFrame to a Parquet file.

Install the pyarrow package:

pip install pyarrow

from google.cloud import bigquery
import pandas

df = pandas.DataFrame(
    {
        "my_string": ["a", "b", "c"],
        "my_int64": [1, 2, 3],
        "my_float64": [4.0, 5.0, 6.0],
        "my_timestamp": [
            pandas.Timestamp("1998-09-04T16:03:14"),
            pandas.Timestamp("2010-09-13T12:03:45"),
            pandas.Timestamp("2015-10-02T16:00:00"),
        ],
    }
)
client = bigquery.Client()
table_id = "my_dataset.new_table"
# Since string columns use the "object" dtype, pass in a (partial) schema
# to ensure the correct BigQuery data type.
job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("my_string", "STRING"),
    ]
)

job = client.load_table_from_dataframe(df, table_id, job_config=job_config)

# Wait for the load job to complete.
job.result()

Features not supported by pandas-gbq and bigquery-dataframes

While the pandas-gbq and bigquery-dataframes libraries provide useful interfaces for querying data and writing data to tables, it does not cover many of the BigQuery API features, including but not limited to:

Managing datasets, including creating new datasets, updating dataset properties, and deleting datasets
Loading data into BigQuery from formats other than pandas DataFrames or from pandas DataFrames with JSON columns
Managing tables, including listing tables in a dataset, copying table data, and deleting tables
Exporting BigQuery data directly to Cloud Storage

Troubleshooting connection pool errors

Error string: Connection pool is full, discarding connection: bigquery.googleapis.com. Connection pool size: 10

If you use the default BigQuery client object in Python, you are limited to a maximum of 10 threads because the default pool size for the Python HTTPAdapter is 10. To use more than 10 connections, create a custom requests.adapters.HTTPAdapter object. For example:

client = bigquery.Client()
adapter = requests.adapters.HTTPAdapter(pool_connections=128,
pool_maxsize=128,max_retries=3)
client._http.mount("https://",adapter)
client._http._auth_request.session.mount("https://",adapter)
query_job = client.query(QUERY)