How do I connect to Azure synapse dedicated pool from a spark application in k8s?

Question

So the main question is how can I connect to an azure synapse dedicated pool without databricks and connect it via an external spark app that runs on eks. What driver should I use?

Currently, I have a scala-spark code and I compile it to generate a flat jar and I added a jdbc jar connector but I don't know if this driver is the correct one for synapse or if I need another extra driver for synapse because I have some issues when I try to write to a synapse-dedicated table.

Drivers:

jdbc: "com.microsoft.sqlserver" % "mssql-jdbc" % "9.4.0.jre11",
scala: "2.12.10"
java 11
spark: 3.1.1

Answer 1

I created a dedicated sql pool in Azure synapse . Also , created a table 'tableA' and inserted one row in it. I want to connect to the sql pool using Synapse notebook and get the content of the table using spark session. For this , I am using pyspark code for doing that.

1. Create a new notebook and attach spark pool with it .
2. Create a code cell to initialize parameters which holds connection details for your dedicated sql pool

3. Create a new cell and add the below piece of code to connect to ded sql pool and retrieve the content of the table

import pyodbc as odbc 
import pandas as pd
from pyspark.sql import SparkSession 

filecontent=""" select top 10 * from tableA""" 

odbcConnectionString = 'Driver={{ODBC Driver 17 for SQL Server}};Server={0};Database={1};UID={2};PWD={3}'.format(dedsqlpoolname,dedsqlpooldbname,dedsqlpoolusername,password) 

sqlCon = odbc.connect(odbcConnectionString) 

sqlCursor = sqlCon.cursor() 

df1 = pd.read_sql(filecontent, sqlCon) 

display(df1) 

df2 = pd.DataFrame(sqlCursor.execute(filecontent)) 

sqlCon.commit() 

sqlCon.close()