CODEWITHSUNDEEP
google-bigquery
codey
codey

Reputation: 23

Access to $__PARTITIONS_SUMMARY__ info using standard SQL

How can I query info corresponding to partition_id and last_modified_time from $__PARTITIONS_SUMMARY__ pseudo-table using standard SQL?

AKA, how should I translate

SELECT
    partition_id,
    last_modified_time as last_modified
FROM
    [Mydataset.Table$__PARTITIONS_SUMMARY__]

to standard SQL?

Upvotes: 2

Views: 3103

Answers (3)

vreyespue
vreyespue

Reputation: 781

Another possibility in standard SQL would be to retrieve the information contained within the table metadata INFORMATION_SCHEMA.PARTITIONS. Note that in this case the partitions of all tables within the database are pooled together, and therefore it is convenient to select the specific table in the where clause.

SELECT table_name, partition_id, last_modified_time, total_rows
FROM `project.database.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name='product'
ORDER BY partition_id DESC

Upvotes: 1

nab_at_stack
nab_at_stack

Reputation: 1

Open new Big Query UI in your project. below Query Editor, Go to More-> Query Setting-> SQL dialect -> enable Legacy.. And Run below SQL.

It will give you all information about partitions in the table.

SELECT * FROM
   [<dataset>.<table_name>$__PARTITIONS_SUMMARY__];

Upvotes: -1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

__PARTITIONS_SUMMARY__ is supported in Legacy SQL only

The best you can do without cost incurring is to query by _PARTITIONTIME (this obviously will work for ingestion time partitioned tables only) 

SELECT 
    _PARTITIONTIME pt,
    COUNT(1) rows_in_partition
FROM `project.dataset.partitioned_table`
GROUP BY 1
ORDER BY 1

Also, you can submit Feature Request here

Upvotes: 1

Related Questions