CODEWITHSUNDEEP
sqlcachinggoogle-cloud-platformgoogle-bigquerybigdata
Chris Halcrow
Chris Halcrow

Reputation: 31950

BigQuery - Cacheing possibly isn't working. How do I diagnose?

I have a BigQuery query that is triggered via a Google Cloud Endpoints API written in Java, and I've configured the queryJobConfiguration as follows, to use the BigQuery query cache:

    // create new big query configuration
    BqEntity entity = new BqEntity();
    entity.queryJobConfiguration = QueryJobConfiguration
            .newBuilder(query)
            .setUseLegacySql(false)
            .setUseQueryCache(true)
            .build();

The cacheing doesn't appear to be working, and I need to confirm, definitively, whether or not this is really the case. When I re-issue a query by reloading data via the web front end, that results in an identical query to the last time it was run, I'm seeing no improvement in performance, and the performance issue appears to be at the back-end (it could be a number of factors - I want to check first if BigQuery cacheing is an issue).

My question is - how can I check whether cacheing was used for the query? I'm not seeing anywhere in The Google Cloud Console that lets me see which queries have been run, and whether the cache was used (I'm looking for the type of thing that SQL Server Profiler provides, but haven't found that in the Google Cloud Console and haven't found anything after Googling).

Upvotes: 0

Views: 432

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Cacheing possibly isn't working. How do I diagnose?
I need to confirm, definitively, whether or not this is really the case

There are two common ways to determine if BigQuery returned a result using the cache:

If you are using the GCP Console or the classic BigQuery web UI, the result string does not contain information about the number of processed bytes, and displays the word "cached".

Cache indicator in the UI

enter image description here

If you are using BigQuery API, the cacheHit property in the query result is set to true.

Upvotes: 1

Related Questions