0

I am trying to load TPCH DATA against a table LINEITEM in the TPCH dataset.

I need to create quarterly partitions against this table. By default daily partitions are created. Since the table is huge, Big query table job fails after 2000 partitions or so.

How to create quarterly partitions for the LINEITEM table?

Anand Viswanathan
  • 1
  • Did this here: https://stackoverflow.com/questions/56125048/partition-by-week-year-month-to-get-over-the-partition-limit – Felipe Hoffa Sep 26 '19 at 00:53

1 Answers1

0

You are limited to DATE OR TIMESTAMP partitions in BigQuery.

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

You should be able to hit up to 4,000 partitions, but you might be hitting the following limit (from the link): An individual operation can commit data into up to 2,000 distinct partitions.. Can you break up your load job into smaller steps?

rtenha
  • 3,349
  • 1
  • 6
  • 19
  • Thanks for your quick answer. Even 4000 partitions cant work. Since this would be a daily partitions across many years (2000-2019 or so). I was looking at quarterly partition support or yearly partition support. IF any support is there? Daily partitions wont work – Anand Viswanathan Sep 27 '19 at 07:16
  • As part of "CREATE TABLE SYNTAX" if I try to use "PARTITION by date_trunc(l_shipdate,year). It does not take the syntax. I cant add a new column say l_shipdate_year to the table as the syntax of my query need to change. TPCH queries primarily uses l_shipdate > or l_shipdate < . Now to take advantage of the partition pruning I might have to add a new filter l_shipdate_year > to do partition pruning apart from my regular filter l_shipdate > and l_shipdate <. Is there any way to avoid change the query and still take advantage of partitioning.? – Anand Viswanathan Sep 27 '19 at 09:23