BigQuery - Max Partiton Date for a Custom Partitioned Table

1
votes

Is there a metadata operation that can give me the max partitioned date/timestamp in use (for custom partitioned table not Ingest partitioning), such that I do not need to scan a whole table using MAX function? Or some other clever SQL way? Our source table is very large, and it gets a fresh snapshot of data most days - but then that data is generally for current_date()-1...but all in all I cant rely on much except for a query to tell me the max partition in use that doesnt cost the earth for a large table? thought?

SELECT MAX(custom_partition_field) FROM Y

1
google-bigquery

1 Answers

1
votes
#legacySQL
SELECT MAX(partition_id)
FROM [project:dataset.your_table$__PARTITIONS_SUMMARY__]

It is documented at Listing partitions in partitioned tables