[Question] RowIterator documentation on bigquery storage api not entirely clear

[code-mc]

In the RowIterator documentation the following sentence is included in the different dataframe methods: "Reading from a specific partition or snapshot is not currently supported by this method."

see: https://googleapis.dev/python/bigquery/1.24.0/generated/google.cloud.bigquery.table.RowIterator.html

To me it is not entirely clear of what the implications of this are. While I'd love to use the bigquery storage api it is unclear to me from this sentence if it will charge me for the size of the entire dataset when I execute a partition-bound query (that for instance only queries 1% of the table's partitions). Can you clarify a bit more what it exactly means? We have multiple tables that are in the range of 1TB in size which would incur a cost of 5$ per query if the cost savings of using partition bound queries are ignored.

Thanks.

[shollyman]

So, I'll try to clarify here first, and then see how we may want to approach this in the documentation. First, some background:

The use of the BigQuery Storage API governs row fetching from a table. In BigQuery, it's not necessary to run a query to perform raw table scanning (e.g. in another engine, you may need to issue a SELECT * FROM table to do this). This is true regardless of use of the storage API, but is obviously much slower using the older tabledata.list method for paginated row reads.

When you do run a query in BigQuery, those results can either be materialized in a named table by setting the destination explicitly. When you don't, the results are materialized in a cached table, sometimes called an anonymous table. More info can be found in this page.

In your case, you seem to be describing running a query on a partitioned table where you leverage the partitioning to reduce query costs. In this case, the RowIterator is walking the destination table of the query, not the table(s) the query referenced. The caveat is trying to articulate that one of the advanced features of the Storage API, namely the ability to supply custom predicates (WHERE clauses) for raw scans, isn't supported when called via this RowIterator wrapper.

Does this help?

[code-mc]

Ok I think it's clear now. So just to make sure I understood correctly: when executing a query that only scans a subset of all the partitions that are available in a table, the "results" of this query are first written to a separate destination table, and the size of this destination table is used to determine the associated cost for the bigquery storage API. So if only 1% of the partitions are included in the destination table I can expect to only be billed for 1% of the total data in the source table.

Thanks for the in depth explanation!

[shollyman]

Yes. To make up some numbers, let's say you're running a classic scan-filter-aggregate style query. The source table is 10TB, but your query has a WHERE clause that targets a partitioned column, which reduces the scan of the query to 1TB. The resulting table is just the aggregated result rows, so let's call it 1MB.

Your query would have incurred 1 TB of scanned bytes cost, and reading the results of that query would have resulted in 1MB of storage API usage.

[code-mc]

Ok thanks!

[jurasan]

I did a test doing the same query via BQ UI, and Storage API.
Query used lots of WHERE conditions and table is partitioned and clustered on the columns used in conditions.
Afterwards from the billing I could deduce amount of data read from storage, and it was around the same in both cases.