topic Re: Databricks SQL cannot Communicate With External Hive Metastore which runs on Postgre SQL in Data Governance

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

saltuk — Mon, 20 Jun 2022 09:06:14 GMT

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 ?

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

User16764241763 — Mon, 20 Jun 2022 11:18:01 GMT

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.)

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

saltuk — Mon, 20 Jun 2022 12:52:29 GMT

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.

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

Hubert-Dudek — Mon, 20 Jun 2022 17:36:43 GMT

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)

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

saltuk — Mon, 20 Jun 2022 20:03:07 GMT

thanks @Hubert Dudek ,

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

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

saltuk — Tue, 21 Jun 2022 16:15:34 GMT

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

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

Hubert-Dudek — Wed, 22 Jun 2022 16:40:05 GMT

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

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

saltuk — Thu, 23 Jun 2022 09:37:05 GMT

done!