Unable to access the hive_metastore schema

Ensure all clusters use the same Hive metastore version and Apache Spark configurations are set.

Written by girish.sharma

Last published at: December 20th, 2024

Problem

You’re trying to access the hive_metastore schema and receive an error message. 

 

summary: Error in SQL statement: AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: Unable to fetch table <table-name>. Exception thrown when executing query: SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE, A0.CREATE_TIME, A0.LAST_ACCESS_TIME, A0.OWNER, A0.RETENTION, A0.REWRITE_ENABLED, A0.TBL_NAME, A0.TBL_TYPE, A0.TBL_ID FROM TBLS A0 LEFT OUTER JOIN DBS B0 ON A0.DB_ID = B0.DB_ID WHERE A0.TBL_NAME = ? AND B0.`NAME` = ?, data: com.databricks.backend.common.rpc.DatabricksExceptions$SQLExecutionException: org.apache.spark.sql.AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: Unable to fetch table <table-name>. Exception thrown when executing

 

Additionally, you may see a java.sql.SQLException indicating an unknown column 'A0.REWRITE_ENABLED' in the field list.

 

Cause

Different clusters are configured with different versions of the Hive metastore. For example, one cluster might use spark.sql.hive.metastore.version: 2.3.7 while another uses spark.sql.hive.metastore.version: 2.3.9. 

Specific Apache Spark configurations may also be missing, such as spark.sql.hive.metastore.jars. 

 

Solution

Ensure that all clusters use the same Hive metastore version. For example, set spark.sql.hive.metastore.version to 2.3.7 on all clusters.

Add the following configuration to the Spark settings of the affected clusters. 

 

Hive 2.3.7 (Databricks Runtime 7.0 - 9.x) or Hive 2.3.9 (Databricks Runtime 10.0 and above): 

     set spark.sql.hive.metastore.jars to builtin

 

For all other Hive versions, Azure Databricks recommends that you download the metastore JARs and set the configuration spark.sql.hive.metastore.jars to point to the downloaded JARs. For more information, review the External Apache Hive metastore (legacy) documentation.  

 

Additionally, ensure that you or a given user have the necessary permissions to access the tables in the Hive metastore. This includes checking permissions on the external Hive metastore side.