To accelerate access to Hive external tables, ByteHouse has developed a Hive metadata cache capability that stores metadata information in server memory. This reduces the overhead of accessing the Hive Metastore and significantly improves query performance when the same table or partition is frequently accessed by business operations. This article introduces the Hive metadata cache feature and its usage.

Hive metadata cache process #

ByteHouse supports caching Hive external table metadata in the dedicated server's memory at four levels: table structure, partition name, partition information, and remote file.

Hive metadata cache levels #

The Hive metadata cache includes four levels: Schema Cache (table structure cache), Partition Name Cache (partition name cache), Partition Cache (partition information cache), and Remote File Cache (remote file cache). Details are as follows:

Cache strategy #

The metadata cache strategy uses a combination of "Least Recently Used (LRU) cache" and "Time to Live (TTL)". The specific logic is as follows:

• An independent TTL is set for the cache key of each metadata record. Within the TTL validity period, the system does not repeatedly send data requests to the remote source but reads data directly from the local cache to improve access efficiency.

• When the cache key's TTL expires, the system will automatically delete the cache content corresponding to that key, then send a request to the Hive Metastore to obtain the latest data, and update the TTL for that key to ensure the timeliness of the cached data.

• The TTL for different cache levels is as follows:

  Cache level	TTL time
  Schema Cache (Table structure cache)	900 seconds
  Partition Name Cache (Partition name cache)	900 seconds
  Partition Cache (Partition information cache)	21600 seconds
  Remote File Cache (Remote file cache)	21600 seconds

The Hive metadata cache feature depends on a dedicated server. You need to purchase a dedicated server before use. Enabling the Hive metadata cache will occupy the memory of the dedicated server. For an introduction and activation instructions for the dedicated server, see Private server.

ByteHouse supports enabling the Hive metadata cache by configuring user SQL-level parameters. After enabling, you can monitor the usage of the Hive metadata cache through the tenant bh_system.query_log system table. Note that the relevant parameters must be added for each query; otherwise, ByteHouse will not enable the Hive metadata cache.

Enable metadata cache #

You can control the enabled state of the Hive metadata cache and the expiration time for each cache level at the query level by adding user SQL-level settings parameters each time you execute an SQL statement. Currently, enabling this feature during table creation is not supported.
ByteHouse supports two methods to enable this feature: configure via SQL parameter group, or directly add settings parameters in the query statement.

Enable via SQL parameter group #

When executing SQL statements in the ByteHouse cloud data warehouse console, you can add Hive metadata-related parameters as an SQL parameter group. Select this parameter group before executing the SQL statement, and ByteHouse will add this parameter group by default when executing the SQL statement. For configuration instructions, please refer to use SQL Param Group. For currently supported settings parameters, see Parameter description. Examples are as follows:

<sql_parameters>
       <enable_hive_schema_cache>false</enable_hive_schema_cache>
       <hive_schema_cache_ttl>1000</hive_schema_cache_ttl>
</sql_parameters>

Enable via settings parameter #

You can add settings parameters each time you execute an SQL statement to enable the Hive metadata cache. For currently supported settings parameters, see Parameter description. Examples are as follows:

-- Enable during query
SELECT * FROM ext.las 
SETTINGS 
    enable_hive_schema_cache = 'true', -- Enable Hive table structure cache
    hive_schema_cache_ttl = '1000'; -- Set Hive table structure cache expiration time to 1000 seconds

Parameter description #

Monitor cache metrics #

You can view cache metrics by querying the tenant's bh_system.query_log system table, including whether the SQL hit the Hive metadata cache and the number of expired cache discoveries.

You can query and obtain the numerical value of the corresponding cache metric in the bh_system.query_log system table according to the specific Key in the event source ProfileEvents. The correspondence between cache metrics and event source keys is described as follows:

Examples of query methods are as follows:

SELECT
    k,
    v
FROM bh_system.query_log
ARRAY JOIN
    mapKeys(ProfileEvents) AS k,
    mapValues(ProfileEvents) AS v
WHERE (query_id = '4e9xxxxx-xxxx-xxxx-xxxx-0bxxxxxxxxxx') AND (k LIKE '%DataLake%')

Parameter description

Examples of output results are as follows: