SQLServer Incorrect syntax near the keyword 'WITH'

Jreco
Contributor

‎07-11-2024 10:44 AM

Hi Mates!

I'm trying to get some data from an SQLServer using a query; the query has a WITH statement but I'm getting the following error:
raise convert_exception(
pyspark.errors.exceptions.connect.SparkConnectGrpcException: (com.microsoft.sqlserver.jdbc.SQLServerException) Incorrect syntax near the keyword 'WITH'.
This is my code for the example:

query = """with cte as ( SELECT TOP 10 * FROM dbo.TestTb wt) select * from cte"""
df = spark.read \
    .format("jdbc") \
    .option("url", sqlserver_option["url"]) \
    .option("dbtable", query) \
    .option("user", sqlserver_option["user"]) \
    .option("password", sqlserver_option["password"]) \
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
    .load()


I also tried to use: .option("query", query)

Both options return the same error.


Looking for similar cases, I found that in 2022 this issue was solved:

https://github.com/apache/spark/pull/28953

https://github.com/microsoft/sql-spark-connector/issues/147

https://github.com/apache/spark/pull/36440

Is it possible I'm doing something wrong?

Can someone give me a guide in this matter?

Thanks!!

0 Kudos