topic Re: SQL endpoint is unable to connect to external hive metastore ( Azure databricks) in Data Engineering

SQL endpoint is unable to connect to external hive metastore ( Azure databricks)

prasadvaze — Mon, 13 Dec 2021 21:01:31 GMT

Using Azure databricks, I have set up SQL Endpoint with the connection details that match with global init script. I am able to browse tables from regular cluster in Data Engineering module but i get below error when trying a query using SQL Endpoint

*****************

on the schema browser left pane error : - Failed to load Databases list.

on the query results pane error:-

Error running query

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

************************

The SQL end point settings are as below - (user id and passwd are in azure key vault)

************************

spark.sql.hive.metastore.* true

spark.hadoop.fs.azure.account.auth.type.<storage_acct>.dfs.core.windows.net OAuth

spark.hadoop.fs.azure.account.oauth.provider.type.<storage_acct>.dfs.core.windows.net org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider

spark.hadoop.fs.azure.account.oauth2.client.id.<storage_acct>.dfs.core.windows.net {{secrets/<secret_scope>/client-id}}

spark.hadoop.fs.azure.account.oauth2.client.secret.<storage_acct>.dfs.core.windows.net {{secrets/<secret_scope>/client-secret}}

spark.hadoop.fs.azure.account.oauth2.client.endpoint.<storage_acct>.dfs.core.windows.net https://login.microsoftonline.com/<tenant_id>/oauth2/token

spark.hadoop.javax.jdo.option.ConnectionURL jdbc://<sql server name>.database.windows.net:1433;database=<db_name>

spark.hadoop.javax.jdo.option.ConnectionUserName metastoredbuser

spark.hadoop.javax.jdo.option.ConnectionPassword {{secrets/<secret_scope>/metastoredbpwd}}

spark.hadoop.javax.jdo.option.ConnectionDriverName com.microsoft.sqlserver.jdbc.SQLServerDriver

spark.sql.hive.metastore.version 2.3.7

spark.sql.hive.metastore.jars /databricks/hive_metastore_jars/*

************************

Re: SQL endpoint is unable to connect to external hive metastore ( Azure databricks)

Prabakar — Tue, 14 Dec 2021 08:55:02 GMT

Hi @prasad vaze instead of

spark.sql.hive.metastore.jars /databricks/hive_metastore_jars/*

please use

spark.sql.hive.metastore.jars maven

and let me know if it works.

Re: SQL endpoint is unable to connect to external hive metastore ( Azure databricks)

Prabakar — Tue, 14 Dec 2021 10:52:04 GMT

DBFS support for SQL is still not available and we have a feature request for this. So using the init script won't help. You need to get the jar from Maven for now.

Re: SQL endpoint is unable to connect to external hive metastore ( Azure databricks)

prasadvaze — Tue, 14 Dec 2021 22:20:46 GMT

This worked. But I am puzzled -- in another workspace I have downloaded the jars from maven and stored on DBFS. And the sql end point there is SUCCESSFULLY using below "spark.sql.hive.metastore.jars /databricks/hive_metastore_jars/*" .. So how is that working? This is confusing or is it a bug?

Re: SQL endpoint is unable to connect to external hive metastore ( Azure databricks)

prasadvaze — Tue, 14 Dec 2021 22:24:10 GMT

Is DBFS support for SQL end point coming ? when?

As I mentioned in previous reply i see that in one of my workspaces using DBFS location for jars from SQL end point works but for another workspace it is not (but using maven works). The later workspace was created 3 days ago and the previous one was created 3 months ago. So something has changed behind the scenes for databricks SQL and I don't know where to find about upcoming changes. What's the future guidance ? use maven coordinates for jars or download jars into DBFS?

Re: SQL endpoint is unable to connect to external hive metastore ( Azure databricks)

prasadvaze — Tue, 14 Dec 2021 22:24:57 GMT

yes @Kaniz Fatma but I have follow up question in this thread.

Thanks Prabhakar

Re: SQL endpoint is unable to connect to external hive metastore ( Azure databricks)

prasadvaze — Mon, 20 Dec 2021 03:37:52 GMT

@Prabakar Ammeappin @Kaniz Fatma Also I found out that after delta table is created in external metastore (and the table data resides in ADLS) then in the sql end point settings I do not need to provide ADLS connection details. I only provided metastore connection details and sql endpoint was able to query the delta table. It is because metastore table stores the ADLS data folder name and path of delta table ( I have yet to find out the exact table in metastore that stores delta table's ADLS folder location , if you know this then pls let me know)