cancel
Showing results for 
Search instead for 
Did you mean: 
Data Governance
Join discussions on data governance practices, compliance, and security within the Databricks Community. Exchange strategies and insights to ensure data integrity and regulatory compliance.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

saltuk
Contributor

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions

saltuk
Contributor

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

View solution in original post

7 REPLIES 7

User16764241763
Honored Contributor

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

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.

image

Hubert-Dudek
Esteemed Contributor III

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)

thanks @Hubert Dudek​ ,

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

saltuk
Contributor

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
Esteemed Contributor III

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

done!

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group