topic Re: How to import SqlDWRelation from com.databricks.spark.sqldw in Data Engineering

How to import SqlDWRelation from com.databricks.spark.sqldw

WillJMSFT — Tue, 30 Nov 2021 05:02:03 GMT

Hello, All - I'm working on a project using the SQL DataWarehouse connector built into Databricks (https://docs.databricks.com/data/data-sources/azure/synapse-analytics.html). From there, I'm trying to extract information from the logical plan / logical relation which will help me identify the table and database (or at least JDBC URL) that is being used to query the data.

I believe I need to cast from BaseRelation to SqlDWRelation after extracting the relation from the logical plan as seen below.

val df = spark.read.format("com.databricks.spark.sqldw")
.option("url", sqlDwUrl)
.option("tempDir", tempDir)
.option("forwardSparkAzureStorageCredentials", "true")
.option("dbTable", tableName)
.load()
val logicalPlan = df.queryExecution.logical
val logicalRelation = logicalPlan.asInstanceOf[LogicalRelation]
val sqlBaseRelation = logicalRelation.relation

However, after doing so, I try to cast `sqlBaseRelation.asInstanceOf[SqlDWRelation]` and it results in an error that looks like:

object SqlDWRelation in package sqldw cannot be accessed in package com.databricks.spark.sqldw

Edit: The above error makes sense since it's a private class 😞

When looking at the jar provided in the workspace for the SQL DW connector, I can see SqlDWRelation as a (edit ~~public~~ ) private class ~~but it's not available via tab / autocomplete inside a Databricks Notebook~~.

Has anyone here ever attempted to work with a SqlDWRelation and extract information about the table and database names for the given logicalrelation? Thank you for any guidance!

In my environment, I'm using:

Azure Databricks
Databricks Runtime 9.1
Scala Notebook
Azure Synapse SQL Pools

Re: How to import SqlDWRelation from com.databricks.spark.sqldw

-werners- — Tue, 30 Nov 2021 09:07:47 GMT

Have you tried to pass as simple query to Synapse to see if that works?

Re: How to import SqlDWRelation from com.databricks.spark.sqldw

WillJMSFT — Tue, 30 Nov 2021 13:57:19 GMT

@Werner Stinckens Thanks for the reply! The SQL DW Connector itself is working just fine and I can retrieve the results from the SQL DW. I'm trying to extract the metadata (i.e. the Server, Database, and Table name) from the logical plan (or through any other means).

Re: How to import SqlDWRelation from com.databricks.spark.sqldw

-werners- — Tue, 30 Nov 2021 15:56:01 GMT

Lol so I was typing a response but now I see you edited your post.

A private method cannot be called indeed.

I worked with the connector but only to run queries and such, not to fetch metadata.

But the golden rule is: if autocomplete shows it, you can use it.

Maybe with a lot of effort because of missing docs, but it can be done.

Re: How to import SqlDWRelation from com.databricks.spark.sqldw

WillJMSFT — Tue, 30 Nov 2021 16:01:09 GMT

Ah! Yes! Sorry about that! I realized this morning that it was a private class and that's why I wasn't seeing it show up in autocomplete.

The next step is to figure out how we can still access that data even when it's using a BaseRelation and not just the (private) SqlDWRelation.

I really appreciate your replies, @werners!

Re: How to import SqlDWRelation from com.databricks.spark.sqldw

jose_gonzalez — Wed, 01 Dec 2021 00:06:50 GMT

Hi @Will Johnson ,

If @Werner Stinckens 's reply helped you to solved this issue, could you select it as best response? it will help to move it to the top.