These are only a few examples I identified. Let’s talk in the comments about what other scenarios may create this need.<\/p>\n
A SQL Endpoint uses TDS, the same connection protocol as SQL Server and other Microsoft SQL flavours. Most software development languages are capable to make a connection to a Microsoft SQL flavour.<\/p>\n
The notebooks use powerful languages, such as pySpark and Scala. In this way, there are multiple methods to make the connection to a SQL Endpoint. For example, we can use ODBC, JDBC or many other connection options.<\/p>\n
However, I was looking for a more “Fabric native” method. There is one. It’s a bit strange, but at the moment, is the most “Fabric native” method.<\/p>\n
This method requires us to use libraries only available to Scala language. In this way, the notebook will need to be in Scala.<\/p>\n
\n
import<\/span> com.microsoft.spark.fabric.tds.implicits.read.FabricSparkTDSImplicits._<\/span>\nimport<\/span> com.microsoft.spark.fabric.Constants<\/span>\n\nval<\/span> df =<\/span> spark.<\/span>read.<\/span>option(<\/span>Constants<\/span>.<\/span>WorkspaceId<\/span>,<\/span> datausageWorkspaceId).<\/span>option(<\/span>Constants<\/span>.<\/span>DatabaseName<\/span>,<\/span> SQLEndpoint_name<\/span>).<\/span>synapsesql(<\/span>qry.<\/span>query)<\/span>\n<\/pre>\n<\/div>\nThese are the details about this code:<\/p>\n
\n- The two imports only work in Scala<\/li>\n
- The two constants contain strings with the option name we need to use for the read operation<\/li>\n
- We need to have the Workspace Id and SQL Endpoint name<\/li>\n
- The Workspace is considered the server and the SQL Endpoint is considered the database<\/li>\n
- At the end we execute the query<\/li>\n<\/ul>\n
Interaction between pySpark and Scala<\/h2>\n
My entire environment uses pySpark, while this solution requires Scala.<\/p>\n
In some situations, we may need to create an interaction between the pySpark environment and Scala environment.<\/p>\n
What we can do:<\/p>\n
\n- The parameters cell can be in Scala and the notebook can be called by pySpark notebooks. No problem about this.<\/li>\n
- The Scala cells can call pySpark notebooks.<\/li>\n
- By “call” this means either ‘%Run’ or ‘notebook.run’<\/li>\n<\/ul>\n
What we can’t do:<\/p>\n
\n- Scala cells can’t access pySpark variables, neither the opposite<\/li>\n<\/ul>\n
Exchanging values between languages<\/h2>\n
You may need to exchange values between the two languages from time to time. It depends on each scenario.<\/p>\n
The solution for this is to use spark configurations. In one language you can set a configuration with the the variable value and read the configuration in the other language to retrieve the value.<\/p>\n
Example – Set the configuration in pySpark:<\/p>\n
<\/p>\n
\n
spark.<\/span>conf.<\/span>set(\"lakemirrorSQLEndpointId\"<\/span>,lakemirrorSQLEndpointId)\n<\/pre>\n<\/div>\nRetrieve the configuration in Scala:<\/p>\n
<\/p>\n
\n
val<\/span> lakemirrorSQLEndpointId=<\/span>spark.<\/span>conf.<\/span>get(<\/span>\"lakemirrorSQLEndpointId\"<\/span>)<\/span>\n<\/pre>\n<\/div>\nExceptions to the basic syntax<\/h2>\n
The “synapsesql” method doesn’t accept the same syntaxes supported by a SQL Endpoint.<\/p>\n
These syntax may be perfect and acceptable by the SQL Endpoint, but “synapsesql” will reject them. It doesn’t help the error message being completely meaningless, taking us in the wrong directions.<\/p>\n
Another syntax option: Breaking the query in two<\/h2>\n
The queries can be broken down in two parts. This is only one of the ways to solve the problem. Let’s analyse how this syntax works<\/p>\n
<\/p>\n
\n
val<\/span> df =<\/span> spark.<\/span>read.<\/span>option(<\/span>Constants<\/span>.<\/span>WorkspaceId<\/span>,<\/span> wksId).<\/span>option(<\/span>Constants<\/span>.<\/span>DatabaseName<\/span>,<\/span> EndPointName<\/span>).<\/span>option(<\/span>\"prepareQuery\"<\/span>,<\/span> firstpart).<\/span>synapsesql(<\/span>secondpart)<\/span>\n<\/pre>\n<\/div>\nThis first option breaks the query into two parts. The first part is sent together the “option” method, the second part is sent using the “synapsesql” method.<\/p>\n
The relation between one query and another is made using CTE’s. Other options would be way more expensive, such as temporary views. The solution using CTE’s is like the code below:<\/p>\n
<\/p>\n
\n
%%<\/span>spark\n\nimport<\/span> com.microsoft.spark.fabric.tds.implicits.read.FabricSparkTDSImplicits._<\/span>\nimport<\/span> com.microsoft.spark.fabric.Constants<\/span>\n\nval<\/span> df =<\/span> spark.<\/span>read.<\/span>option(<\/span>Constants<\/span>.<\/span>WorkspaceId<\/span>,<\/span> \"bb457c73-bf10-4f53-8933-8c409192747a\"<\/span>)<\/span>\n.<\/span>option(<\/span>Constants<\/span>.<\/span>DatabaseName<\/span>,<\/span> \"interlake\"<\/span>)<\/span>\n.<\/span>option(<\/span>\"prepareQuery\"<\/span>,<\/span> \"with qry as (select * from fact_sale_1y_full where CustomerKey=204)\"<\/span>)<\/span>\n.<\/span>synapsesql(<\/span>\"select count(*) as total from qry\"<\/span>)<\/span>\n\ndisplay(<\/span>df)<\/span>\n<\/pre>\n<\/div>\nPrepareQuery and SynapseSQL difference<\/h2>\n
SynapseSQL<\/strong> method is processed by spark. Spark tries to optimize the query and make additional checks. In this way, some native syntaxes are not supported in synapsesql.<\/p>\nThe prepareQuery<\/strong>, on the other hand, sends the query “as is” to the target. In this way, the query is not affected by any spark processing.<\/p>\nThere are many syntaxes which can cause this problem and require to be broken down in CTE’s and sent in two pieces:<\/p>\n
\n- Common Table Expressions (CTEs)<\/li>\n
- Deeply nested queries<\/li>\n
- Window functions<\/li>\n
- Custom SQL Functions<\/li>\n
- Dynamic SQL<\/li>\n<\/ul>\n
Summary<\/h2>\n
After analysing some reasons that would require executing a query on a SQL Endpoint from a notebook, we analysed an interesting method to do this.<\/p>\n
This method still has limitations in relation to the language, but it’s interesting to notice how it uses Fabric libraries directly.<\/p>\n
This is far from be the only method to execute these queries, depending how the driver used to connect to the SQL endpoint.<\/p>\n","protected":false},"excerpt":{"rendered":"
Let’s analyse why we would like to query an SQL Endpoint. Once we understand why, we can dig into how to make a query to a SQL Endpoint. We use notebooks to connect directly to lakehouse. Except by the T-SQL notebook, the notebooks have a default lakehouse and work directly with it from spark. However,……<\/p>\n","protected":false},"author":50808,"featured_media":106041,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159164],"tags":[158997,159146,159288,159289],"coauthors":[6810],"class_list":["post-106032","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blogs","category-microsoft-fabric","tag-microsoft-fabric","tag-pyspark","tag-scala","tag-sqlendpoint"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106032","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=106032"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106032\/revisions"}],"predecessor-version":[{"id":106043,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106032\/revisions\/106043"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/106041"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=106032"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=106032"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=106032"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=106032"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}