Adding multiple partitioned columns to BigQuery table from SQL query

Question

I've been trying to add multiple partition columns, to a BigQuery table, but it seems to only take one field, even if I add multiple partition fields in the query parameters.

I'm partitioning by date time and integer range.

It only takes the later of the pair to create partitions and ignores the first partition field.

Any ideas, would be appreciated?

Moule

Please provide an example of your data and what you have tried so far – Intramuscular

Answer 1

BigQuery only supports partitioning on one column. If you want to partition on multiple columns, you can consider partitioning+clustering. The table can be clustered on up to 4 columns.

Raquel

Answer 2

I use coalesce to combine the columns and partition the new field created from coalesce, worked for my purpose.

Grosmark

I think you mean CONCAT or something similar? How useful are concatenated fields (as string) for query optimization? For clustering, I can see how it might help for large number of records over few combinations. – Fichu