![](/img/r/8/r8lwc6le.png)
Cloudera Runtime Query results cache
Query results cache
Hive filters and caches similar or identical queries in the query results cache. Caching repetitive queries can reduce
the load substantially when hundreds or thousands of users of BI tools and web services query Hive.
Some operations support vast user populations, who connect to Hive using BI systems such as Tableau. In these
situations, repetitious queries are inevitable. The query results cache, which is on by default, filters and stores
common queries in a cache. When you issue the query again, Hive retrieves the query result from a cache instead of
recomputing the result, which takes a load off the backend system.
Every query that runs in Hive 3 stores its result in a cache. Hive evicts invalid data from the cache if the input table
changes. For example, if you preform aggregation and the base table changes, queries you run most frequently stay
in cache, but stale queries are evicted. The query results cache works with managed tables only because Hive cannot
track changes to an external table. If you join external and managed tables, Hive falls back to executing the full query.
The query results cache works with ACID tables. If you update an ACID table, Hive reruns the query automatically.
Configuring the query results cache
You can enable and disable the query results cache from the command line. You might want to do so to debug a
query. You disable the query results cache in HiveServer by setting the following parameter to false: SET hive.que
ry.results.cache.enabled=false;
By default, Hive allocates 2GB for the query results cache. You can change this setting by configuring the following
parameter in bytes: hive.query.results.cache.max.size
Best practices for performance tuning
Review certain performance tuning guidelines related to configuring the cluster, storing data, and writing queries so
that you can protect your cluster and dependent services, automatically scale resources to handle queries, and so on.
Best practices
• Use Ranger security service to protect your cluster and dependent services.
• Store data using the ORC File format. Others, such as Parquet are supported, but not as fast for Hive queries.
• Ensure that queries are fully vectorized by examining explain plans.
Related Information
Custom Configuration (about Cloudera Manager Safety Valve)
Example of using the Cloudera Manager Safety Valve
ORC file format
You can conserve storage in a number of ways, but using the Optimized Row Columnar (ORC) file format for storing
Apache Hive data is most effective. ORC is the default storage for Hive data.
The ORC file format for Hive data storage is recommended for the following reasons:
• Efficient compression: Stored as columns and compressed, which leads to smaller disk reads. The columnar
format is also ideal for vectorization optimizations.
• Fast reads: ORC has a built-in index, min/max values, and other aggregates that cause entire stripes to be skipped
during reads. In addition, predicate pushdown pushes filters into reads so that minimal rows are read. And Bloom
filters further reduce the number of rows that are returned.
4