topic Re: SQLServer Incorrect syntax near the keyword 'WITH' in Data Engineering

SQLServer Incorrect syntax near the keyword 'WITH'

Jreco — Thu, 11 Jul 2024 17:44:43 GMT

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!!

Re: SQLServer Incorrect syntax near the keyword 'WITH'

szymon_dybczak — Thu, 11 Jul 2024 20:11:48 GMT

Hi @Jreco ,

You need to use prepare query option and then query like below:

url = "jdbc:sqlserver://server_name:1433;database=db_name" df = spark.read \ .format("jdbc") \ .option("url", url) \ .option("prepareQuery", "with cte as ( SELECT TOP 10 * FROM dbo.TestTb wt)") \ .option("query", "SELECT * FROM cte") \ .option("user", "user_name") \ .option("password", "password") .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver").load()

I've tested it and it works 🙂

Re: SQLServer Incorrect syntax near the keyword 'WITH'

Jreco — Thu, 11 Jul 2024 23:11:11 GMT

Thanks for your response @szymon_dybczak was helpful!

Do you know where I can get some additional information about those options?

Thank you so much!