Cache in BigQuery from bigquery command line tool

Question

I am using bigquery command line tool. How can I enable cache through BigQuery command line tool.

Answer 1

Queries are cached automatically, with the following exceptions:

• queries that use non-deterministic functions, like now(), or rand() are not cached.
• queries that specify a destination table are not cached (if you've got the result in a destination table, you shouldn't need a cache).
• cached results are flushed when any of the source tables change.
• results are only cached for 24 hours.

You can see this by looking up the job object from a query. For example:

$ bq query select 17
Waiting on bqjob_r4c80a6944b4dff0_0000014165a4f730_1 ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
|  17 |
+-----+

This actually ran the query and added it to the cache. Now let's run it again:

$ bq query select 17
Waiting on bqjob_r27fa3d897b8dfb3e_0000014165a66b50_1 ... (0s) Current status: DONE
+-----+
| f0_ |
+-----+
|  17 |
+-----+

That query result should have ben fetched from the cache. This will be visible in the statistics.query.cacheHit member on the job resource. Let's check:

$ bq --format=prettyjson show -j bqjob_r27fa3d897b8dfb3e_0000014165a66b50_1
{
  "configuration": {
    "query": {
      ...
      "query": "select 17",
    }
  },
  ...
  "statistics": {
    "creationTime": "1380389907722",
    "endTime": "1380389908018",
    "query": {
      "cacheHit": true,
      "totalBytesProcessed": "0"
    },
    "startTime": "1380389907853",
  },
}

Answer 2

As explained in https://cloud.google.com/bigquery/docs/cached-results:

• caching is enabled by default
• it has no effect for new queries
• you can disable caching from command line using the --nouse_cache flag