CODEWITHSUNDEEP
google-bigquerypartitioning
Ilja
Ilja

Reputation: 1053

BigQuery - how many entries per partition?

I have big partitioned tables and try to figure out how many entries are in each day-partition. So far I used a for loop in a script but there must be a simpler way doing it.

Google did not help me. Does anyone know the right query?

Thanks

Upvotes: 3

Views: 3258

Answers (1)

Pentium10
Pentium10

Reputation: 207828

you can run the following query to count how many entries you have in each partition

#standardSQL
SELECT
  _PARTITIONTIME AS pt,
  COUNT(1)
FROM
  `dataset.table` 
GROUP BY
  1
ORDER BY
  1 DESC

and

#legacySQL
SELECT
  _PARTITIONTIME AS pt,
  COUNT(1)
FROM
  [dataset:table]
GROUP BY
  1
ORDER BY
  1 DESC

it returns a table like this, please note that the NULL entries are still in streaming buffer. Hint: to obtain records which are in streaming buffer us a query with NULL.

+-------------------------+-----+--+
| 2017-02-14 00:00:00 UTC | 252 |  |
+-------------------------+-----+--+
| 2017-02-13 00:00:00 UTC | 257 |  |
+-------------------------+-----+--+
| 2017-02-12 00:00:00 UTC | 188 |  |
+-------------------------+-----+--+
| 2017-02-11 00:00:00 UTC | 234 |  |
+-------------------------+-----+--+
| 2017-02-10 00:00:00 UTC | 107 |  |
+-------------------------+-----+--+
| null                    | 13  |  |
+-------------------------+-----+--+

Upvotes: 5

Related Questions