Databricks SQL cannot Communicate With External Hive Metastore which runs on Postgre SQL

[saltuk]

I have created a premium cluster on Azure site. There were no problem Data Science and Engineering (DSAE) while i was binding Postgre SQL hive metastore. I have done all settings via Global init Scripts from Admin Console.

However when I try to adjust same setting for Databricks SQL, I am getting Error:

Org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

The setting as written below(Data Access Configuration). I cannot see any other logs from Databricks SQL, I cannot sniff any other causes.

spark.hadoop.fs.azure.account.auth.type.DataLake.dfs.core.windows.net OAuth
spark.hadoop.fs.azure.account.oauth.provider.type.DataLake.dfs.core.windows.net org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider
spark.hadoop.fs.azure.account.oauth2.client.id.DataLake.dfs.core.windows.net {{secrets/Veloxity/VeloxityDateLakeApplicationID}}
spark.hadoop.fs.azure.account.oauth2.client.secret.DataLake.dfs.core.windows.net {{secrets/Veloxity/VeloxityDataLakeSecretID}}
spark.hadoop.fs.azure.account.oauth2.client.endpoint.DataLake.dfs.core.windows.net https://login.microsoftonline.com/{{secrets/Veloxity/VeloxityTenantID}}/oauth2/token
spark.hadoop.javax.jdo.option.ConnectionURL jdbc:postgresql://#########.postgres.database.azure.com:5432/databricks_metastore?ssl=true
spark.hadoop.javax.jdo.option.ConnectionUserName ########@##########.postgres.database.azure.com
spark.hadoop.javax.jdo.option.ConnectionPassword #########
spark.hadoop.javax.jdo.option.ConnectionDriverName org.postgresql.Driver
spark.sql.hive.metastore.jars maven
spark.sql.hive.metastore.version 3.1.0
spark.hadoop.datanucleus.fixedDatastore false
spark.hadoop.hive.metastore.schema.verification false

I have read question written by  @[prasadvaze]  and answers from @Prabakar Ammeappin​  however i could not find solution yet. I have change "..metastore.jar maven" or tried to update version nothing has worked yet.

Could you please help me ?

[User16764241763]

Hello @saltuk bozok​ 

Are you by any chance using serverless SQL Endpoints?

https://databricks.com/blog/2021/08/30/announcing-databricks-serverless-sql.html

Some confs are not supported on Serverless Endpoints as mentioned here

https://docs.databricks.com/sql/admin/data-access-configuration.html

spark.sql.hive.metastore.* (spark.sql.hive.metastore.jars and spark.sql.hive.metastore.jars.path are unsupported for Serverless SQL endpoints.)

[saltuk]

hi @Arvind Ravish​ 

I am not using serverless SQL endpoints. I did not implement BI tools and the option is not show up on my admin console.

[Hubert-Dudek]

For me, it looks like a schema problem of external hive metastore.

You can try to add option "spark.sql.hive.metastore.schema.verification false" (or tryu to update schema)

[saltuk]

thanks @Hubert Dudek​ ,

I have tried that. But problem still same. getting exactly same error.

[saltuk · Accepted solution]

Find soluttion :

spark.hadoop.javax.jdo.option.ConnectionURL jdbc:postgresql://#########.postgres.database.azure.com:5432/databricks_metastore?ssl=true

needs to be

spark.hadoop.javax.jdo.option.ConnectionURL jdbc:postgresql://#########.postgres.database.azure.com:5432/databricks_metastore?sslmode=Require

[Hubert-Dudek]

Great!, Please mark your answer as the best one so it will help others in the future 🙂

[saltuk]

done!