How to write a data from notebook to serverless SQL pool?

Question

Hi, I want to write the data to synapse serverless SQL from the Pyspark notebook with the error

Py4JJavaError: An error occurred while calling o3873.save. : com.microsoft.sqlserver.jdbc.SQLServerException: CREATE TABLE write_to_test_table is not supported.

I have created a SQL user with the role as db_owner and db_datawriter following https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/sql-authentication?tabs=serverless And tried to execute the tutorial: Azure SQL Database and SQL Server connector for Apache Spark https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/data-sources/apache-spark-sql-connector#write-data There is no problem to read the data from SQL but the following error is encountered while writing data in Synapse notebook:

---------------------------------------------------------------------------
Py4JJavaError                             Traceback (most recent call last)
/tmp/ipykernel_6676/1101652390.py in 

Answer 1

Hello @Ava Tse Thanks for the question and using MS Q&A platform. The reason you are getting that error is because the CREATE TABLE is not supported in Serverless

You will have to use CETAS - CREATE EXTERNAL TABLE AS SELECT . Thanks
Himanshu

Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues.