BigQuery table partitioning by month

Question

I can't find any documentation relating to this. Is time_partitioning_type=DAY the only way to partition a table in BigQuery? Can this parameter take any other values besides a date?

score 5 · Accepted Answer · answered Jan 11 '17 at 14:30

5

Note that even if you partition on day granularity, you can still write your queries to operate at the level of months using an appropriate filter on _PARTITIONTIME. For example,

#standardSQL
SELECT * FROM MyDatePartitionedTable
WHERE DATE_TRUNC(EXTRACT(DATE FROM _PARTITIONTIME), MONTH) = '2017-01-01';

This selects all rows from January of this year.

answered Jan 11 '17 at 14:30

Elliott Brossard

32,095
2
67
99

Exactly what I was looking for! Thanks :) – Sara Briccoli Feb 07 '22 at 11:29

score 4 · Answer 2 · answered Jan 11 '17 at 08:50

4

Unfortunately not. BigQuery currently only supports date-partitioned tables.

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

BigQuery offers date-partitioned tables, which means that the table is divided into a separate partition for each date

answered Jan 11 '17 at 08:50

Graham Polley

14,393
4
44
80

2

Update 2022 - the linked-to documentation now documents how to do this: "For TIMESTAMP and DATETIME columns, the partitions can have either hourly, daily, monthly, or yearly granularity. For DATE columns, the partitions can have daily, monthly, or yearly granularity." – Aadmaa May 29 '22 at 15:29

David Salmela · Answer 3 · 2018-04-10T21:05:47.473

4

It seems like this would work:

#standardSQL
 CREATE OR REPLACE TABLE `My_Partition_Table`
 PARTITION BY event_month
 OPTIONS (
    description="this is a table partitioned by month"
  ) AS
  SELECT
   DATE_TRUNC(DATE(some_event_timestamp), month) as event_month,
   *
  FROM `TableThatNeedsPartitioning`

edited Apr 10 '18 at 21:05

answered Apr 10 '18 at 19:31

David Salmela

131
5

2

The only caveat is that you need to use event_month in your queries, so if you're looking for something on 2019-01-15 you would need to specify `and event_month='2019-01-01'` – David Salmela Jun 28 '19 at 06:58

score 0 · Answer 4 · answered Feb 20 '23 at 08:57

For those that run into the error "Too many partitions produced by query, allowed 4000, query produces at least X partitions", due to the 4000 partitions BigQuery limit as of 2023.02, you can do the following:

CREATE OR REPLACE TABLE `My_Partition_Table`
  PARTITION BY DATE_TRUNC(date_column, MONTH)
  OPTIONS (
    description="This is a table partitioned by month"
  ) AS
-- Your query

Basically, take @david-salmela 's answer, but move the DATE_TRUNC part to the PARTITION BY section.

It seems to work exactly like PARTITION BY date_column in terms of querying the table (e.g. WHERE date_column = "2023-02-20"), but my understanding is that you always retrieve data for a whole month in terms of cost.

BigQuery table partitioning by month

4 Answers4

Linked