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:

pipinstall--upgradebigframespandas-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 

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

importpandas
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 

fromgoogle.cloudimport 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 

importpandas
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 

fromgoogle.cloudimport 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 

importbigframes.pandasasbpd
importpandasaspd
# Set partial ordering mode as the default configuration for BigQuery
# DataFrames.
bpd.options.bigquery.ordering_mode = "partial"
defquery_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 

importpandas
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 

fromgoogle.cloudimport 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 

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

importpandas
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 

fromgoogle.cloudimport 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

Both pandas-gbq and google-cloud-bigquery support uploading data from a pandas DataFrame to a new table in BigQuery. Key differences include the following:

pandas-gbq google-cloud-bigquery
Type support Converts the DataFrame to CSV format before sending to the API, which doesn't 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 You can optionally specify a table schema. Use the LoadJobConfig class, which contains properties for the various API configuration options.

pandas-gbq 

importpandas
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:

pipinstallpyarrow


fromgoogle.cloudimport bigquery
importpandas
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:

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)

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026年08月26日 UTC.