Synapses Serverless Pool -Read External Tables

Question

I am trying to read the data from external table which is configured in synapses serverless pool using pyspark notebook (I want that to be in a dataframe), however i am not able to do that. Can someone guide me how we can do that. I tried the following code.

server_name = "jdbc:sqlserver://<<sservername>>-ondemand.sql.azuresynapse.net"
database_name = "<<dbname>>"
url = server_name + ";" + "databaseName=" + database_name + ";"

table_name = "dbo.NameList"
username = "sqladmin"
password = "<<password>>" # Please specify password here
driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

namelistdf = spark.read.format("jdbc") \
    .option("url", url) \
    .option("dbtable", table_name )\
    .option("user", username) \
    .option("password", password) \
    .option("driver", driverClass) \
    .load()

namelistdf.show()
namelistdf.createOrReplaceTempView('namelistdf')

Answer 1

Depending on the type of the external data source, there are two types of external tables:

• Hadoop external tables that you can use to read and export data in various data formats such as CSV, Parquet, and ORC. Hadoop external tables are available in dedicated SQL pools, but they aren't available in serverless SQL pools.

• Native external tables that you can use to read and export data in various data formats such as CSV and Parquet. Native external tables are available in serverless SQL pools, and they are in public preview in dedicated SQL pools.

Make sure you have a Native external table which is stored in your Primary storage account and then you can use below sample code to read the file using pyspark.

%%pyspark
df = spark.read.load('abfss://[email protected]/NYCTripSmall.parquet', format='parquet')
display(df.limit(10))

Refer Use external tables with Synapse SQL and Analyze with Apache Spark for more details.