CODEWITHSUNDEEP
google-bigquery
S.Mohsen sh
S.Mohsen sh

Reputation: 2116

In Google BigQuery, how to get storage size of a partition in time-partitioned table?

I can query for storage size of a table in BigQuery using SELECT size_bytes FROM dataset.__TABLES__ WHERE table_id='mytable', but this only works for finding total size of table. How to get size of a specific partition from a time-partitioned table, for example I want to find out how much data is stored in mytable$20180701.

I know I can for example copy that partition to a non-partitioned table and use the method above, but I feel this can't be the right method.

Upvotes: 2

Views: 4724

Answers (3)

PetrosP
PetrosP

Reputation: 665

In the latest BQ versions there is a preview of PARTITIONS view. The way to get the bytes of a partition for a table is:

SELECT
  TOTAL_LOGICAL_BYTES
FROM
  `your_project.your_dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE
  TABLE_NAME = 'your_table'
  AND
  PARTITION_ID = '20240610' # Partition date in yyyymmdd
;

Upvotes: 1

Vinodraj Jayaraman
Vinodraj Jayaraman

Reputation: 41

In order to access pseduo columns like PARTITIONS_SUMMARY, _PARTITIONTIME you can follow the below steps.

  1. In the Google Cloud console, open BigQuery page.
  2. Click the More > Query settings button.
  3. Scroll down and expand Advanced options section, for SQL dialect, select Legacy, then click Save.

This will work for fetching pseduo columns. uncheck the Legacy option when not required.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

You can use dryRun for this - or in UI just type SELECT * FROM mytable$20180701 and see in Validator how much bytes will be processed - this is the size of the table

Upvotes: 5

Related Questions