ClickHouse DB::Exception: No Hive metastore

The "DB::Exception: No Hive metastore" error in ClickHouse indicates that the server cannot connect to or locate the configured Hive metastore service. The NO_HIVEMETASTORE error code is raised when ClickHouse attempts to interact with a Hive table engine or related functionality but the metastore endpoint is unreachable, not configured, or not responding.

Impact

Without a working Hive metastore connection, ClickHouse cannot query Hive-managed tables, retrieve schema metadata, or list partitions. Any queries against Hive table engines will fail. This blocks data lake integration workflows and prevents ClickHouse from serving as a query layer over Hive-managed data.

Common Causes

1. The Hive metastore service is down or has not been started
2. Incorrect metastore host or port in the ClickHouse configuration
3. Network connectivity issues or firewall rules blocking the Thrift port (typically 9083)
4. The Hive metastore configuration was removed or never added to the ClickHouse config
5. DNS resolution failure for the metastore hostname
6. The Hive metastore is overloaded and not accepting new connections
7. Authentication or Kerberos configuration issues preventing connection

Troubleshooting and Resolution Steps

1. Verify the Hive metastore service is running on the expected host:

   # Check if the metastore process is running
   ps aux | grep HiveMetaStore
   # Or check the service status
   systemctl status hive-metastore
   

2. Confirm the metastore host and port in your ClickHouse configuration:

   <hive_metastore>
       <host>hive-metastore-host</host>
       <port>9083</port>
   </hive_metastore>
   

3. Test network connectivity from the ClickHouse server to the metastore:

   telnet hive-metastore-host 9083
   # or
   nc -zv hive-metastore-host 9083
   

4. Check DNS resolution for the metastore hostname:

   nslookup hive-metastore-host
   

5. Review firewall rules to ensure port 9083 (or your configured port) is open between the ClickHouse server and the Hive metastore host.

6. If using Kerberos authentication, verify that the Kerberos ticket is valid and the keytab file is accessible to the ClickHouse process.

7. Examine the Hive metastore logs for errors or connection rejections that might indicate the root cause on the metastore side.

8. Restart the Hive metastore service if it appears hung or unresponsive, and retry the ClickHouse query.

Best Practices

• Monitor Hive metastore availability with health checks and alerting.
• Use a highly available Hive metastore deployment (multiple instances behind a load balancer) for production workloads.
• Document the metastore connection details alongside the ClickHouse configuration for easier troubleshooting.
• Test metastore connectivity during ClickHouse deployment and configuration changes.
• Keep Hive metastore and ClickHouse network configurations in sync, especially after infrastructure migrations.

Frequently Asked Questions

Q: What port does the Hive metastore typically use?
A: The default Hive metastore Thrift port is 9083. This can be customized in the Hive configuration, so verify the actual port with your Hive administrator.

Q: Can ClickHouse work with a remote Hive metastore in a different data center?
A: Yes, as long as network connectivity and latency are acceptable. High latency may slow down metadata operations, but the functionality will work. Ensure firewall rules permit the connection.

Q: Does ClickHouse support the Hive metastore over TLS?
A: Support depends on your ClickHouse version. Check the documentation for your release to confirm whether TLS connections to the Hive metastore are supported.

Q: Will this error appear if the Hive metastore is temporarily unavailable?
A: Yes. The NO_HIVEMETASTORE error appears whenever ClickHouse cannot reach the metastore at query time. If the metastore comes back online, subsequent queries will succeed without a ClickHouse restart.