topic Re: Insert Into SQLServer Table in Data Engineering

Insert Into SQLServer Table

Unimog — Wed, 05 Mar 2025 22:02:56 GMT

I'm trying to insert and update data in an SQLServer table from a python script. No matter what I try, it seems to give me this error:
The input query contains unsupported data source(s). Only csv, json, avro, delta, kafka, parquet, orc, text, unity_catalog, binaryFile, xml, simplescan, iceberg, mysql, postgresql, sqlserver, redshift, snowflake, sqldw, databricks, bigquery, oracle, salesforce, salesforce_data_cloud, teradata, workday_raas, mongodb data sources are supported on serverless compute...

- I've tried from spark.sql on serverless and non-serverless compute.
- I've tried updating using dataframe.write...
- I've tried over a federated connection as well as opening a jdbc connection.
- Based on the message and suggestions from the assistant, I've tried writing the input data to a delta table first, then updating from there.

Is anyone out there able to write to an existing sql server table who could give me some hints?

Re: Insert Into SQLServer Table

Nivethan_Venkat — Wed, 05 Mar 2025 23:18:52 GMT

Hi @Unimog, Is it possible to provide the dataframe write statement? Or more details on the error apart from allowed file formats

Re: Insert Into SQLServer Table

Unimog — Wed, 05 Mar 2025 23:44:24 GMT

Hi Nivethan, I have tried a bunch of methods to do this. Here is one:
update_df.write

.format("sqlserver")

.mode("append")

.option("host", "xxx.database.windows.net")

.option("user", "xxx")

.option("password", "xxx")

.option("dbtable", "xxx")

.option("batchsize", 20000)

.option("mssqlIsolationLevel", "READ_UNCOMMITTED")

.save()

Re: Insert Into SQLServer Table

Unimog — Wed, 05 Mar 2025 23:47:05 GMT

Here is the full text of the error message:

The input query contains unsupported data source(s). Only csv, json, avro, delta, kafka, parquet, orc, text, unity_catalog, binaryFile, xml, simplescan, iceberg, mysql, postgresql, sqlserver, redshift, snowflake, sqldw, databricks, bigquery, oracle, salesforce, salesforce_data_cloud, teradata, workday_raas, mongodb data sources are supported on serverless compute, and only csv, json, avro, delta, kafka, parquet, orc, text, unity_catalog, binaryFile, xml, simplescan, iceberg data sources are allowed to run DML on serverless compute.

Re: Insert Into SQLServer Table

Nivethan_Venkat — Thu, 06 Mar 2025 00:04:36 GMT

Hi @Unimog,

Currently the support for data sources are limited to as mentioned in the General Limitations for serverless compute as of now: General Serverless Limitations

Support for data sources is limited to AVRO, BINARYFILE, CSV, DELTA, JSON, KAFKA, ORC, PARQUET, ORC, TEXT, and XML.

Hopefully, it will soon be enabled for more data formats including sqlserver as mentioned in your dataframe write query. But when you are using Delta as a format it should work ideally without any issues.

Re: Insert Into SQLServer Table

Unimog — Thu, 06 Mar 2025 15:39:18 GMT

I thought I had already tried this on general purpose compute, but apparently not. After reading the docs you referenced, I retried using standard compute and it works perfectly.

Thanks!