Calculating Google BigQuery Dataset Size Using Queries with the PARTITIONS View

Google Cloud BigQuery pricing is largely divided into charges for storing data and charges for issuing queries. When you run a query, you can preview approximately how much data will be read, and if you want to avoid high costs, you can control it by reducing the frequency of query issuance.

On the other hand, the storage-based charging is something to be mindful of. When integrating data into BigQuery from various data sources, you often want to check how much data you are actually storing.

There are various calculation methods

Many people think the same way, and various methods have been used for calculation.

https://www.techblogchan.com/entry/2019/10/15/110000

Since the billing amount varies depending on the storage tier, some people have taken that into account as well.

How to check dataset data capacity size and calculate costs in BigQuery

While those methods are perfectly fine, I found a method using the PARTITIONS view in the Google documentation, so I gave it a try.

Calculating Dataset Size with the PARTITIONS View

Queries using the PARTITIONS view are in preview at the time of this article's publication.

https://cloud.google.com/bigquery/docs/information-schema-tables?hl=ja#partitions_view

Let's immediately try the query described in the documentation. Since this is a query against public data, anyone should be able to execute it. I have converted the byte count to GB to make it easier to read.

SELECT storage_tier, SUM(total_billable_bytes) / 1000000000 billable_GBs
FROM `bigquery-public-data.crypto_bitcoin.INFORMATION_SCHEMA.PARTITIONS`
GROUP BY storage_tier

The result was as follows.

For nginx access log results

Nginx logs from a load balancer are output to Cloud Logging, and furthermore, Cloud Logging can be integrated with BigQuery using a log router.

https://cloud.google.com/logging/docs/export?hl=ja

I also performed a size check on these accumulated access logs.

SELECT storage_tier, SUM(total_billable_bytes) / 1000000000 billable_GBs
FROM `super_project.access_log.INFORMATION_SCHEMA.PARTITIONS`
GROUP BY storage_tier

It was about 7.5GB. Considering it's for billing reference, it's helpful to be able to GROUP BY storage_tier. If you have an opportunity to calculate the size, please try the method using the PARTITIONS view as well.