You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Use BigQuery Dataframes as Read-Connector to BigQuery #17326
BigQuery DataFrames provides a Pythonic DataFrame and machine learning (ML) API powered by the BigQuery engine.
With this you can execute Pandas operations directly on BigQuery engine. So maybe this API can be used to implement a better connector towards BigQuery, which also supports some lazy optimizations like filter pushdown, instead of using from_arrow with a hardcoded query executed by the Python BigQuery client.
Thanks @OELSJAN for the feature request! I'm curious to hear if there's anything we can do in BigQuery DataFrames to make these filter pushdown features easier to implement.
Also, for your awareness, we have on our backlog a request to make an official polars connector for BigQuery (watch issue googleapis/google-cloud-python#15710 for updates). I suspect that might be a good place to implement such optimizations, as a separate package could make dependencies a little easier to manage.
See also this request on the BigQuery DataFrames repo for some polars support. google/bigframes#735, which is mostly focused on the I/O piece similar to this request.
A question: are there other I/O methods that support push-down to the storage layer? I'm curious what hooks are available for such functionality.
Edit: Two reasons for asking: (1) it'd be lovely to hook into the existing optimizations somehow via some extension mechanism (note that much of these, such as row filters and column filters are supported via the BQ Storage Read API) and (2) it'd be great to introduce even more pushdown types, as BigQuery DataFrames supports aggregations, joins (to other BigQuery data sources or even local data if uploaded to a temp table or small enough to inline in SQL), and more.
A question: are there other I/O methods that support push-down to the storage layer? I'm curious what hooks are available for such functionality.
@tswast: Yes - for example, the Polars Iceberg integration1 supports various pushdown optimisations including predicates, range queries, and suchlike 👍
On a side-note, I've been meaning to look at integrating the BigQuery Client object as a valid connection type for our pl.read_database function for a while... should really get around to it!
How stable is this interface? I'm curious if this sort of connector is best contributed directly to the polars package or should be provided by a separate package (similar to how pandas has refactored BigQuery support out into pandas-gbq years ago).
Since this proposed feature is to go beyond predicates to potentially turning aggregates and such into BigQuery queries, maybe it's best to stick to the pola-rs/polar repo for now, as that scan functionality improves/extends?
I've started a BigQuery + polars gist with some ideas. I'll try to keep that up to date as I experiment with reads and writes. The first experiment, bigquery-to-polars-no-pyarrow-ipynb, is a barebones read API that doesn't require pyarrow to go from BigQuery table -> polars DataFrame.
Edit: Note that to make this work for queries could go a few ways. It's complicated because we're dealing with multiple APIs: BigQuery REST API for queries and BigQuery Storage Read API for tables:
forget trying to avoid pyarrow and use the query results to_arrow() method,
make sure we always use the "jobs.insert" REST API by calling query(), not query_and_wait(), pull out the destination table [which is always created for query jobs, but not necessarily with query_and_wait, then use the BQ Storage Read API as in my gist
Use query_and_wait but then somehow switch between the REST API to download results as Python objects (should only do this for small results) or see if there is a destination table and us that with the BQ Storage Read API.
That said, maybe query support is not necessary, since we will have read_database compatibility and want a scan_bigquery API with predicate push down support? If scan_bigquery pushes down more than just predicates though, such as aggregates to the query API, then we would need to solve this.
I am really overwhelmed by the stone i have set rolling here and i am delighted that this topic is now being adressed. Can't wait to process bigquery data in a more native way.
Still quite a few TODOs left (actually fetch the schema and convert to polars schema instead of hardcoding the test table, actually apply the predicates, actually apply the column filters). I'm feeling confident with this approach, though.
Note: I'm targeting a scan_bigquery() function first since it could actually be re-used for the pl.read_database (#17326 (comment)) code. Every query job (generated by Client.query(...)) in BigQuery actually generates a destination table that could be read from in the same way. Aside: Client.query_and_wait() might not create a table for small results, so there is some subtlety there, but TBD if it's worth it to include such optimizations)
Edit (2025-01-25): My test branch is going quite well. I'm able to push down several kinds of filters, including on the _PARTITIONDATE pseudo-column. I'm transitioning to writing tests and documentation. Hopefully will have something ready for a first pass review as I find time in the coming weeks. If anyone wants to take an early look and see if I'm on the write track, I'd love the feedback.
Excellent initiative @tswast ! I would be a frequent user of this feature. Having data stored in BigQuery and doing processing using polars is a great idea!
Activity
[-]Use BigQuery Dataframes as Connector to BigQuery[/-][+]Use BigQuery Dataframes as Read-Connector to BigQuery[/+]tswast commentedon Aug 28, 2024
Thanks @OELSJAN for the feature request! I'm curious to hear if there's anything we can do in BigQuery DataFrames to make these filter pushdown features easier to implement.
Also, for your awareness, we have on our backlog a request to make an official polars connector for BigQuery (watch issue googleapis/google-cloud-python#15710 for updates). I suspect that might be a good place to implement such optimizations, as a separate package could make dependencies a little easier to manage.
See also this request on the BigQuery DataFrames repo for some polars support. google/bigframes#735, which is mostly focused on the I/O piece similar to this request.
tswast commentedon Sep 3, 2024
A question: are there other I/O methods that support push-down to the storage layer? I'm curious what hooks are available for such functionality.
Edit: Two reasons for asking: (1) it'd be lovely to hook into the existing optimizations somehow via some extension mechanism (note that much of these, such as row filters and column filters are supported via the BQ Storage Read API) and (2) it'd be great to introduce even more pushdown types, as BigQuery DataFrames supports aggregations, joins (to other BigQuery data sources or even local data if uploaded to a temp table or small enough to inline in SQL), and more.
alexander-beedie commentedon Sep 4, 2024
@tswast: Yes - for example, the Polars Iceberg integration1 supports various pushdown optimisations including predicates, range queries, and suchlike 👍
On a side-note, I've been meaning to look at integrating the BigQuery Client object as a valid connection type for our
pl.read_databasefunction for a while... should really get around to it!Footnotes
feat(python): Add support for Iceberg (https://github.com/pola-rs/polars/pull/10375) ↩
tswast commentedon Sep 6, 2024
Very cool!
From what I've heard from folks (e.g. googleapis/google-cloud-python#15710), it's important to avoid unnecessary dependencies on pyarrow, so it's good to see that
_scan_python_functionhas an option to avoid that dependency.How stable is this interface? I'm curious if this sort of connector is best contributed directly to the polars package or should be provided by a separate package (similar to how pandas has refactored BigQuery support out into pandas-gbq years ago).
Since this proposed feature is to go beyond predicates to potentially turning aggregates and such into BigQuery queries, maybe it's best to stick to the pola-rs/polar repo for now, as that scan functionality improves/extends?
tswast commentedon Sep 6, 2024
I've started a BigQuery + polars gist with some ideas. I'll try to keep that up to date as I experiment with reads and writes. The first experiment, bigquery-to-polars-no-pyarrow-ipynb, is a barebones read API that doesn't require
pyarrowto go from BigQuery table -> polars DataFrame.This could be extended further to support a
scan_bigquerywith no extra dependencies (such as bigframes), as the BigQuery Storage Read API supports predicates (seerow_restrictionin https://cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.ReadSession.TableReadOptions). Maybe I'll give that a try soon, as it sounds like a fun project. :-)Edit: Note that to make this work for queries could go a few ways. It's complicated because we're dealing with multiple APIs: BigQuery REST API for queries and BigQuery Storage Read API for tables:
to_arrow()method,query(), notquery_and_wait(), pull out the destination table [which is always created for query jobs, but not necessarily with query_and_wait, then use the BQ Storage Read API as in my gistThat said, maybe query support is not necessary, since we will have
read_databasecompatibility and want ascan_bigqueryAPI with predicate push down support? Ifscan_bigquerypushes down more than just predicates though, such as aggregates to the query API, then we would need to solve this.OELSJAN commentedon Sep 17, 2024
I am really overwhelmed by the stone i have set rolling here and i am delighted that this topic is now being adressed. Can't wait to process bigquery data in a more native way.
tswast commentedon Jan 13, 2025
I'm making some good progress on a
scan_bigquery()function that avoids a pyarrow dependency in https://github.com/tswast/polars/blob/issue17326-scan-bigquery/py-polars/polars/io/bigquery.pyStill quite a few TODOs left (actually fetch the schema and convert to polars schema instead of hardcoding the test table, actually apply the predicates, actually apply the column filters). I'm feeling confident with this approach, though.
Note: I'm targeting a
scan_bigquery()function first since it could actually be re-used for thepl.read_database(#17326 (comment)) code. Every query job (generated byClient.query(...)) in BigQuery actually generates a destination table that could be read from in the same way. Aside:Client.query_and_wait()might not create a table for small results, so there is some subtlety there, but TBD if it's worth it to include such optimizations)Edit (2025-01-25): My test branch is going quite well. I'm able to push down several kinds of filters, including on the
_PARTITIONDATEpseudo-column. I'm transitioning to writing tests and documentation. Hopefully will have something ready for a first pass review as I find time in the coming weeks. If anyone wants to take an early look and see if I'm on the write track, I'd love the feedback.leonelqz commentedon May 10, 2025
Hi!
Excellent initiative @tswast ! I would be a frequent user of this feature. Having data stored in BigQuery and doing processing using polars is a great idea!
tswast commentedon Nov 2, 2026
I think we can close in favor of the recent release of the
polars-bigquerypackage. See: https://polars.bigquery.dev/ for the API reference and a brief getting started.