how clustering works in BigQuery

Question

I have a a table UNITARCHIVE partitionned by date, and clustered by UNIT, DUID.

the total size of the table 892 Mb.

when I try this query

SELECT * FROM `test-187010.ReportingDataset.UNITARCHIVE` WHERE duid="RRSF1" and unit="DUNIT"

Bigquery tell me, it will process 892 mb, I thought clustering is supposed to reduce the scanned size, I understand when I filter per date, the size is reduced dramatically, but i need the whole date range. is it by design or am I doing something wrong

score 1 · Accepted Answer · answered Sep 17 '19 at 03:29

1

To get the most benefits out of clustering, each partition needs to have a certain amount of data.

For example, if the minimum size of a cluster is 100MB (decided internally by BigQuery), and you have only 100MB of data per day, then querying 100 days will scan 100*100MB - regardless of the clustering strategy.

As an alternative with this amount of data, instead of partitioning by day, partition by year. Then you'll get the most benefits out of clustering with a low amount of data per day.

See Partition by week/year/month to get over the partition limit? for a reference table that shows this off.

answered Sep 17 '19 at 03:29

Felipe Hoffa

54,922
16
151
325

hahahah, so basically you are saying, I have a small table :) – Mim Sep 17 '19 at 03:41
1

not a small table - but a small amount of data per day – Felipe Hoffa Sep 17 '19 at 03:46
1

I understand :), looking forward when BI Engine support views then will be very happy :) – Mim Sep 17 '19 at 03:47

how clustering works in BigQuery

1 Answers1

Linked