Menu
I am trying to find the total number of partitions in a bigQuery partitioned table. I am using below query:
`SELECT
_PARTITIONTIME AS pt, COUNT(1)
FROM
`dataset_name.table_name`
GROUP BY 1
ORDER BY 1 DESC`
I took a break from Bigquery for almost 4 months, and I remember this query used to work earlier. Am I missing something?
597
Ingestion-time partitioned tables contain a pseudo-column named _PARTITIONTIME , which is the partitioning column. The value of the column is the UTC ingestion time for each row, truncated to the partition boundary (such as hourly or daily), as a TIMESTAMP value.
You can use the sys. partitions system catalog view to return partition info for a table and most kinds of views. You can use the sys. dm_db_partition_stats system dynamic management view to return page and row-count information for every partition in the current database.
CREATE PARTITION FUNCTION CatsPartitionFunction (int) AS RANGE LEFT FOR VALUES (-1, 5, 100); This tells us how the data is stored, according to the values in the partitioning column. So we can now run a query that only returns data from a specific partition.
As @ElliottBrossard mentioned in the comments, _PARTITIONTIME is a pseudo column available on partitioned tables only. If your table is not partitioned, the query will not work.
You can find more information regarding partitioned tables here.
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
