Using cached query results

BigQuery writes all query results to a table. The table is eitherexplicitly identified by the user (a destination table), or it is a temporary,cached results table. If you run the exact same query again,BigQuery returns the results fromthe cached table, if it exists.Temporary, cached results tables are maintained per-user,per-project. Depending on your edition, you might have access tocached results from other users running queries in thesame project. There are no storage costs for cached query result tables, but ifyou write query results to a permanent table, you are charged forstoring the data.

All query results, including bothinteractive and batch queries,are cached in temporary tables for approximately 24 hours with someexceptions.

Limitations

Using the query cache is subject to the following limitations:

• When you run a duplicate query, BigQuery attempts to reusecached results. To retrieve data from the cache, the duplicate query text mustbe the same as the original query.
• For query results to persist in a cached results table, the result set must besmaller than the maximum response size. For more information about managinglarge result sets, seeReturning large query results.
• You cannot target cached result tables withDMLstatements.
• Although current semantics allow it, the use of cached results asinput for dependent jobs is discouraged. For example, you shouldn'tsubmit query jobs that retrieve results from the cache table. Instead, writeyour results to a named destination table. To simplify cleanup, features suchas the dataset leveldefaultTableExpirationMs property can expire the dataautomatically after a given duration.

Pricing and quotas

Cached query results are stored as temporary tables. You aren't charged for thestorage of cached query results in temporary tables. When query results areretrieved from a cached results table, the job statistics propertystatistics.query.cacheHit returns astrue, and you are not charged for thequery. Though you are not charged for queries that use cached results, thequeries are subject to the BigQueryquota policies.In addition to reducing costs, queries that use cached results aresignificantly faster because BigQuery does not need to computethe result set.

Exceptions to query caching

Query results are not cached:

• When a destination table is specified in the job configuration, theGoogle Cloud console, the bq command-line tool, or the API.
• If any of the referenced tables or logical views have changed since theresults were previously cached.
• When any of the tables referenced by the query have recently receivedstreaming inserts (table has data in the write-optimized storage) even if no newrows have arrived.
• If the query uses non-deterministic functions; for example, date and timefunctions such asCURRENT_TIMESTAMP() andCURRENT_DATE, andother functions such asSESSION_USER(), it returns different valuesdepending on when a query is executed.
• If you are querying multiple tables using awildcard.
• If the cached results have expired; typical cache lifetime is 24 hours, butthe cached results are best-effort and may be invalidated sooner.
• If the query runs against anexternal data source.other than Cloud Storage. (GoogleSQLqueries on Cloud Storage are supported by cached query results.)
• If the query runs against a table protected byrow-level security,then the results are not cached.
• If the query runs against a table protected bycolumn-level security,including data masking, results might not be cached.
• If the query text has changed in any way, including modified whitespace orcomments.

How cached results are stored

When you run a query, a temporary, cached results table is created in a specialtype ofhidden datasetreferred to as ananonymous dataset.Unlike regular datasets whichinherit permissions from the IAM resource hierarchy model (project andorganization permissions), access to anonymous datasets is restricted to theowner. The owner of an anonymous dataset is the user who ran the query thatproduced the cached result. In addition, thebigquery.jobs.create permissionis checked on the project to verify that the user has access to the project.

BigQuery doesn't support sharing anonymous datasets. If youintend to sharequery results, don't use the cached results stored in an anonymous dataset.Instead, write the results to a named destination table.

Although the user that runs the query has full access to the dataset and thecached results table, using them as inputs for dependent jobs is discouraged.

The names of anonymous datasets begin with an underscore.This hides them from the datasets list in the Google Cloud console.You can list anonymous datasets and audit anonymous dataset access controls byusing the bq command-line tool or the API.

For more information about listing and getting information about datasets,including anonymous datasets,seeListing datasets.

Cross-user caching

If you have the required permissions to execute a query, the results of whichare cached in your project for another user, then BigQueryreturns results from the cache. The cached result is copied into your personalanonymous dataset and remains there for 24 hours from when you ran the query.

Cross-user caching is available if you are using the Enterprise orEnterprise Plusedition. The samelimits and exceptions for single-user caching apply tocross-user caching.

Disabling retrieval of cached results

TheUse cached results option reuses results from a previous run of thesame query unless the tables being queried have changed. Using cached results isonly beneficial for repeated queries. For new queries, theUse cachedresults option has no effect, though it is enabled by default.

When you repeat a query with theUse cached results option disabled,the existing cached result is overwritten. This requires BigQueryto compute the query result, and you are charged for the query. This isparticularly useful in benchmarking scenarios.

If you want to disable retrieving cached results and force live evaluation of aquery job, you can set theconfiguration.query.useQueryCacheproperty of your query job tofalse.

To disable theUse cached results option:

 bq query \ --nouse_cache \ --batch \ 'SELECT    name,    count  FROM`my-project`.mydataset.names_2013  WHERE    gender = "M"  ORDER BY    count DESC  LIMIT    6'

import("context""fmt""io""cloud.google.com/go/bigquery""google.golang.org/api/iterator")// queryDisableCache demonstrates issuing a query and requesting that the query cache is bypassed.funcqueryDisableCache(wio.Writer,projectIDstring)error{// projectID := "my-project-id"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()q:=client.Query("SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;")q.DisableQueryCache=true// Location must match that of the dataset(s) referenced in the query.q.Location="US"// Run the query and print results when the query job is completed.job,err:=q.Run(ctx)iferr!=nil{returnerr}status,err:=job.Wait(ctx)iferr!=nil{returnerr}iferr:=status.Err();err!=nil{returnerr}it,err:=job.Read(ctx)for{varrow[]bigquery.Valueerr:=it.Next(&row)iferr==iterator.Done{break}iferr!=nil{returnerr}fmt.Fprintln(w,row)}returnnil}

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.TableResult;// Sample to running a query with the cache disabled.publicclassQueryDisableCache{publicstaticvoidrunQueryDisableCache(){Stringquery="SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";queryDisableCache(query);}publicstaticvoidqueryDisableCache(Stringquery){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();QueryJobConfigurationqueryConfig=QueryJobConfiguration.newBuilder(query)// Disable the query cache to force live query evaluation..setUseQueryCache(false).build();TableResultresults=bigquery.query(queryConfig);results.iterateAll().forEach(row->row.forEach(val->System.out.printf("%s,",val.toString())));System.out.println("Query disable cache performed successfully.");}catch(BigQueryException|InterruptedExceptione){System.out.println("Query not performed \n"+e.toString());}}}

// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');asyncfunctionqueryDisableCache(){// Queries the Shakespeare dataset with the cache disabled.// Create a clientconstbigquery=newBigQuery();constquery=`SELECT corpus    FROM \`bigquery-public-data.samples.shakespeare\`    GROUP BY corpus`;constoptions={query:query,// Location must match that of the dataset(s) referenced in the query.location:'US',useQueryCache:false,};// Run the query as a jobconst[job]=awaitbigquery.createQueryJob(options);console.log(`Job${job.id} started.`);// Wait for the query to finishconst[rows]=awaitjob.getQueryResults();// Print the resultsconsole.log('Rows:');rows.forEach(row=>console.log(row));}

use Google\Cloud\BigQuery\BigQueryClient;/** Uncomment and populate these variables in your code */// $projectId = 'The Google project ID';// $query = 'SELECT id, view_count FROM `bigquery-public-data.stackoverflow.posts_questions`';// Construct a BigQuery client object.$bigQuery = new BigQueryClient([    'projectId' => $projectId,]);// Set job configs$jobConfig = $bigQuery->query($query);$jobConfig->useQueryCache(false);// Extract query results$queryResults = $bigQuery->runQuery($jobConfig);$i = 0;foreach ($queryResults as $row) {    printf('--- Row %s ---' . PHP_EOL, ++$i);    foreach ($row as $column => $value) {        printf('%s: %s' . PHP_EOL, $column, json_encode($value));    }}printf('Found %s row(s)' . PHP_EOL, $i);

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()job_config=bigquery.QueryJobConfig(use_query_cache=False)sql="""    SELECT corpus    FROM `bigquery-public-data.samples.shakespeare`    GROUP BY corpus;"""query_job=client.query(sql,job_config=job_config)# Make an API request.forrowinquery_job:print(row)

Ensuring use of the cache

If you use thejobs.insert methodto run a query, you can force a query job to fail unless cached results can beused by setting thecreateDisposition property of thequery jobconfiguration toCREATE_NEVER.

If the query result does not exist in the cache, aNOT_FOUND error isreturned.

 bq query \ --require_cache \ --batch \ 'SELECT    name,    count  FROM`my-project`.mydataset.names_2013  WHERE    gender = "M"  ORDER BY    count DESC  LIMIT    6'

Verifying use of the cache

Use one of the following methods to determine if BigQuery returned a result using the cache:

• Use the Google Cloud console. Go toQuery results and clickJob Information.Bytes processed shows0 B (results cached).
• Use theBigQuery API.ThecacheHit property in the query result is set totrue.

Impact of column-level security

By default, BigQuery caches query results for 24 hours, with theexceptions noted previously. Queries against a tableprotected bycolumn-level security might not be cached. IfBigQuery does cache the result, the 24-hour cache lifetimeapplies.

A change such as removing a group or a user from theData Catalog Fine Grained Reader role used for a policy tag doesnot invalidate the 24-hour cache. A change to theData Catalog FineGrained Reader access control group itself is propagated immediately, but thechange does not invalidate the cache.

The impact is if a user ran a query, the query results remain visible to theuser on screen. The user can also retrieve those results from the cache even ifthey lost access to the data within the last 24 hours.

During the 24 hours after a user is removed from theData CatalogFine Grained Reader role for a policy tag, the user can access the cacheddata only for data that the user was previously allowed to see. If rows areadded to the table, the user can't see the added rows, even if the resultsare cached.