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!