Hadoop3.0+Hive2.3.2+Spark2.3集群，Spark查Hive表遇HIVE_STATS_JDBC_TIMEOUT问题求助

Hey there! Let's work through this timeout issue you're facing when querying Hive tables. First off, the good news: the HIVE_STATS_JDBC_TIMEOUT bug fixed in Hive 2.1.1 is indeed included in Hive 2.3.2, so this error isn't a regression—it's almost certainly a configuration tweak or performance bottleneck in your cluster setup. Here are actionable fixes to resolve it:

1. Adjust JDBC Timeout Configuration for Stats #

The most direct fix is to increase the timeout threshold for Hive's statistics JDBC calls. You can do this either at the session level or globally:

• Session-level (temporary fix): Run this command before your query:

  SET hive.stats.jdbc.timeout=120000; -- Sets timeout to 120 seconds (value is in milliseconds)
  SET hive.stats.retries.wait=5000; -- Optional: Increase wait time between retries to 5 seconds
  

• Global fix (persistent): Add these properties to your hive-site.xml file:

  <property>
      <name>hive.stats.jdbc.timeout</name>
      <value>120000</value>
      <description>Timeout in milliseconds for JDBC calls when fetching statistics</description>
  </property>
  <property>
      <name>hive.stats.retries.wait</name>
      <value>5000</value>
      <description>Wait time between retries for statistics collection</description>
  </property>
  

  Don't forget to restart Hive services after updating the config.

2. Optimize Your Metastore Database Performance #

Timeout errors often stem from slow queries on the Hive metastore database (like MySQL, PostgreSQL, etc.). Try these optimizations:

• Check for slow queries: Use your database's query logging tool to identify long-running metastore queries (e.g., queries on TBLS, PARTITIONS, or STATISTICS tables).
• Add indexes: Ensure critical metastore tables have proper indexes. For example, adding an index on TBLS.DB_ID or PARTITIONS.TBL_ID can speed up statistics lookup.
• Clean up old data: If your metastore has accumulated years of unused tables/partitions, archiving or deleting them can reduce query latency.
• Tune database connection settings: Update your hive.metastore.connection.url to include performance-focused parameters (e.g., for MySQL: jdbc:mysql://<metastore-host>:3306/hive?useSSL=false&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8). You can also increase the metastore client socket timeout with:

  <property>
      <name>hive.metastore.client.socket.timeout</name>
      <value>1800</value>
      <description>Socket timeout in seconds for metastore client connections</description>
  </property>
  

3. Disable Automatic Statistics Gathering (Emergency Workaround) #

If you need a quick fix to unblock queries and don't rely on Hive's automatic statistics right now, you can disable it:

• Session-level:

  SET hive.stats.autogather=false;
  

• Global: Add this to hive-site.xml:

  <property>
      <name>hive.stats.autogather</name>
      <value>false</value>
      <description>Disable automatic gathering of statistics during table/partition creation</description>
  </property>
  

  Note: This is a temporary workaround—statistics help Hive optimize queries, so you'll want to re-enable it once the underlying issue is fixed.

4. Verify Spark-Hive Compatibility #

Since you're using Spark 2.3 with Hive 2.3.2, double-check that your Spark configuration aligns with Hive's:

• Ensure Spark is using the same hive-site.xml as your Hive cluster (copy it to Spark's conf directory if needed).
• Confirm Spark's metastore settings match Hive's, especially timeout parameters, to avoid mismatched connection limits.

内容的提问来源于stack exchange，提问作者Tomasz Krol