Google BigQuery — List all partitions in a partitioned table

Google BigQuery — List all partitions in a partitioned table

Partitioned table

A partitioned table is a special table that is divided into segments, called partitions, that makes it easier to manage and query your data. By dividing a large table into smaller partitions, you can improve query performance, and you can control costs by reducing the number of bytes read by a query.

There are two types of table partitioning available in BigQuery:

  • Tables partitioned by ingestion time: Tables partitioned based on the data’s ingestion (load) date or arrival date.
  • Partitioned tables: Tables that are partitioned based on a TIMESTAMP or DATE column.

List all partitions:

Below query lists all partitions of a partitioned table. By using Legacy SQL Mode we can use Partition summary decorator (PARTITIONS_SUMMARY) to query and get the list of partitions in a partitioned table.

Legacy SQL

To use legacy sql, In you Big Query Editor, Go to More-> Query Setting-> SQL dialect -> enable Legacy

SELECT * FROM
   [PROJECT_ID.DATASET_ID.TABLE_ID$__PARTITIONS_SUMMARY__];

Standard SQL

For tables partitioned based ingestion time

SELECT _PARTITIONTIME as pt, FORMAT_TIMESTAMP("%Y%m%d", _PARTITIONTIME) as partition_id
FROM `PROJECT_ID.DATASET_ID.TABLE_ID`
GROUP BY _PARTITIONTIME
ORDER BY _PARTITIONTIME

--or 
SELECT _PARTITIONDATE as pt, FORMAT_TIMESTAMP("%Y%m%d", _PARTITIONDATE) as partition_id
FROM `PROJECT_ID.DATASET_ID.TABLE_ID`
GROUP BY _PARTITIONDATE
ORDER BY _PARTITIONDATE

Replace PROJECT_ID, DATASET_ID, TABLE_ID with corresponding values. PROJECT_ID is optional.

PROJECT_ID = Google Cloud Project ID
DATASET_ID = Dataset ID 
TABLE_ID = Table ID

Originally posted on medium.com

if you find this article useful, feel free to connect with me on twitter to stay updated on future articles.

Did you find this article valuable?

Support Suresh Kumar by becoming a sponsor. Any amount is appreciated!