CODEWITHSUNDEEP
google-bigquery
user1965449
user1965449

Reputation: 2931

BigQuery table partitioning by month

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?

Upvotes: 3

Views: 17992

Answers (4)

NumesSanguis
NumesSanguis

Reputation: 6292

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.

Upvotes: 1

David Salmela
David Salmela

Reputation: 141

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`

Upvotes: 4

Elliott Brossard
Elliott Brossard

Reputation: 33725

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.

Upvotes: 5

Graham Polley
Graham Polley

Reputation: 14781

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

Upvotes: 4

Related Questions