如何按指定配置在Databricks中配置外部Hive元存储？遇实例化与版本问题

尝试配置连接Azure SQL Server的外部Hive元存储时，出现元存储无法实例化及Hive版本不匹配类错误。

spark.hadoop.javax.jdo.option.ConnectionDriverName com.microsoft.sqlserver.jdbc.SQLServerDriver
spark.hadoop.javax.jdo.option.ConnectionURL .database.windows.net:1433;Initial Catalog=hivemetastore;

# Skip this one if <hive-version> is 0.13.x.
spark.sql.hive.metastore.version biltin

spark.hadoop.javax.jdo.option.ConnectionUserName <user>
datanucleus.fixedDatastore false
spark.hadoop.javax.jdo.option.ConnectionPassword <pwd>

spark.sql.hive.metastore.jars /databricks/hive_metastore_jars/*
datanucleus.schema.autoCreateTables true

hive.metastore.schema.verification false
hive.metastore.schema.verification.record.version false

spark.databricks.cluster.profile singleNode
spark.databricks.delta.preview.enabled true
spark.master local[*, 4]

1. 修复ConnectionURL配置错误 #

• 缺失Azure SQL Server实例名称：URL开头需替换为<your-server-name>.database.windows.net
• 必须添加Azure SQL强制加密参数，完整URL格式应为：

  jdbc:sqlserver://<your-server-name>.database.windows.net:1433;database=hivemetastore;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
  

2. 修正Hive版本拼写与配置 #

• spark.sql.hive.metastore.version字段拼写错误：biltin改为builtin（对应Databricks内置Hive版本）
• 若指定自定义Hive版本，需填写具体版本号（如2.3.9），并确保spark.sql.hive.metastore.jars路径下的jar包版本匹配

3. 统一配置前缀 #

Hive相关的非Spark原生配置需添加spark.hadoop.前缀，否则无法被Spark正确传递给Hive元存储：

• datanucleus.fixedDatastore false → spark.hadoop.datanucleus.fixedDatastore false
• datanucleus.schema.autoCreateTables true → spark.hadoop.datanucleus.schema.autoCreateTables true
• hive.metastore.schema.verification false → spark.hadoop.hive.metastore.schema.verification false
• hive.metastore.schema.verification.record.version false → spark.hadoop.hive.metastore.schema.verification.record.version false

4. 确保JDBC驱动存在 #

• 检查集群是否已安装Azure SQL Server JDBC驱动，若未安装，需通过Databricks库安装com.microsoft.sqlserver:mssql-jdbc:9.4.1.jre8（或对应JDK版本的驱动）

修正后的完整配置示例 #

spark.hadoop.javax.jdo.option.ConnectionDriverName com.microsoft.sqlserver.jdbc.SQLServerDriver
spark.hadoop.javax.jdo.option.ConnectionURL jdbc:sqlserver://<your-server-name>.database.windows.net:1433;database=hivemetastore;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

spark.sql.hive.metastore.version builtin

spark.hadoop.javax.jdo.option.ConnectionUserName <user>
spark.hadoop.datanucleus.fixedDatastore false
spark.hadoop.javax.jdo.option.ConnectionPassword <pwd>

spark.sql.hive.metastore.jars /databricks/hive_metastore_jars/*
spark.hadoop.datanucleus.schema.autoCreateTables true

spark.hadoop.hive.metastore.schema.verification false
spark.hadoop.hive.metastore.schema.verification.record.version false

spark.databricks.cluster.profile singleNode
spark.databricks.delta.preview.enabled true
spark.master local[*, 4]

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