Hit cache when streaming inserts to one partition but querying a different partition [109634903]

Assigned

Feature Request

Status Update

No update yet.

Description

vg...@google.com

created issue #1

Jun 4, 2018 07:53AM

As per [1], query results are not cached: "When any of the tables referenced by the query have recently received streaming inserts".

Working with partitioned tables, I want to be able to hit the cache when doing streaming inserts to one partition but querying a different partition.

[1]:

https://cloud.google.com/bigquery/docs/cached-results#query-caching

Comments

bl...@google.com <bl...@google.com> Jun 4, 2018 07:56AM

Assigned to km...@google.com.

km...@google.com <km...@google.com> Jun 4, 2018 04:22PM

Reassigned to ep...@google.com.

ha...@gmail.com <ha...@gmail.com> #2Jul 11, 2018 10:21PM

This actually worked fine until May. Now it doesn’t.

pt...@gmail.com <pt...@gmail.com> #3Jan 3, 2019 09:24AM

Any update on this request? Or suggestion on how to handle the case of a table partioned by day where we would like the queries to hit the cache when querying old partitions multiple times?

[Deleted User] <[Deleted User]> #4Feb 28, 2019 09:49PM

Hello, I'd be interested in this feature as well and have a very similar question to the above. I'd also be interested in knowing more about the behavior and reasons why is that the cache is disabled for streaming tables, no matter how old the partitions are. I have a streaming pipeline that captures order data (partitioned on DAY) and it would be a massive help if we could query older order data using cache. Is it possible to treat a partition that has not been streamed into in a while as a non-streaming?

hz...@semios.com <hz...@semios.com> #5Aug 13, 2020 11:57PM

yes, We have this issue as well. It's impacting our core use cases of BQ. would like to have this fixed soon.

[Deleted User] <[Deleted User]> #6Jul 27, 2021 11:02AM

What is the status on this?

bw...@google.com <bw...@google.com> Aug 10, 2021 03:24PM

Reassigned to bw...@google.com.

ar...@perimeter.company <ar...@perimeter.company> #7Apr 15, 2022 06:56PM

I'm interested in this feature as well.

ca...@gmail.com <ca...@gmail.com> #8Oct 28, 2022 11:31AM

bw...@google.com <bw...@google.com> Dec 1, 2022 05:54AM

Reassigned to ni...@google.com.

ni...@google.com <ni...@google.com> #9Dec 1, 2022 05:42PM

A good approach to handle this scenario with features that exist today would be to work with a new BigQuery materialized views capability currently in preview called max_staleness [1].

The BigQuery materialized views max_staleness option helps you achieve consistently high performance with controlled costs when processing large, frequently changing datasets by allowing you to adjust the freshness of the results to tune query performance. There are some great descriptions around how this functions and why queries benefit from it within the documentation mentioned [1].

[1]

https://cloud.google.com/bigquery/docs/materialized-views-create#max_staleness

tr...@gmail.com <tr...@gmail.com> #10Feb 16, 2023 09:29AM

+1, I assume this is the main feature request for supporting caching of static partition, has discussed here:
https://stackoverflow.com/questions/38992980/does-each-partition-of-a-date-partitioned-table-in-google-big-query-have-its-own

Feedback from a practical usage. We use BQ as analytics backend for streaming data in the public transport domain.
We have many large tables with date-partitioning enabled, and streaming inserts occur typically only on the last partition.
Having the query caching automatically exploiting this fact, would mean we could concentrate on business logic instead of cost savings measures (e.g. materialized views, application side caching).

Message last modified on Feb 16, 2023 10:04AM