BigQuery: query time partitioned table row counts per partition for 0 bytes (free) query

4
votes

With an ingestion time partitioned table it's possible to query for the count of rows per partition for free. Bytes billed of 0.

SELECT DATE(_PARTITIONTME) AS dd, COUNT(*) FROM ds.ingestion_time_partitioned GROUP BY dd
# free

With a column based time partitioned table the equivalent query costs what I'd expect for reading in the column (not free).

SELECT DATE(_timestamp) AS dd, COUNT(*) FROM ds.column_time_partitioned GROUP BY dd
# not free

Is there any way to run the second query so it's free? I kinda assumed the two tables are structured similarly, so whatever metadata allows the first query to be free would also exist in the second table.

1
google-bigquery
Did you see cloud.google.com/bigquery/docs/…? - Elliott Brossard
@ElliottBrossard Good call, I'd forgotten about that metadata. I can't get row count from that, but I can get the last_modified_timestamp which ends up also solving my problem. - Macattack

1 Answers

3
votes

This is not possible with column based partitioned table. In your first query, you are selecting the pseudo column _PARTITIONTIME (and not any column with data in the table) while your second query is selecting the partitioned column named _timestamp which will incur cost because your reading its data.

https://cloud.google.com/bigquery/docs/querying-partitioned-tables