GCP BigQuery wont access a single partition

Question

I'm experiencing a problem with a GBQ query which goes through all the data in a table, even though the table is partitioned.

The partitioning is set to DAY on the datetime field.

Currently the query I'm trying to run looks like this:

SELECT * FROM `project.dataset.table` t
WHERE datetime = "2023-09-11"

The datetime field is a TIMESTAMP, so I tried it like this: date(datetime) = "2023-09-11" and it still wanted to access all 3GB of data, every time.

I played around with DATE_TRUNC() function, but that was unsuccessful.

Did you try the opposite? To parse the "2023-09-11" in Timestamp? But it should not affect the result. Are you sure that you have data in different partition? Are you sure about the partitioning config and field? — guillaume blaquiere
– guillaume blaquiere, Commented Sep 29, 2023 at 8:58
I'm sure of the partitioning configuration and field. And I did try the opposite with timestamp. Didn't fix the issue :/ — chocho.boss
– chocho.boss, Commented Sep 29, 2023 at 11:08
You should share more, a minimal amount of code to reproduce your issue. At least the DDL of your table — guillaume blaquiere
– guillaume blaquiere, Commented Sep 29, 2023 at 13:56

Ugur Yigit · Accepted Answer · 2023-09-28 15:37:12Z

1

Partitioning for small tables won't improve the performance or reduce the cost that's why they are offering clustering instead of partitioning for these kind of tables.

Consider clustering a table instead of partitioning a table in the following circumstances:
Partitioning results in a small amount of data per partition (approximately less than 10 GB)

answered Sep 28, 2023 at 15:37

Ugur Yigit

2301 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

chocho.boss Over a year ago

For some reason I wasn't able to find the information you presented in your answer being described in the GCP documentation. Could you provide a source of said data?

Ugur Yigit Over a year ago

you can see it there: cloud.google.com/bigquery/docs/partitioned-tables

guillaume blaquiere Over a year ago

It's true, and there won't be optimization. But the processed data should differ with or without partition.

Collectives™ on Stack Overflow

GCP BigQuery wont access a single partition

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related