cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

SQLServer Incorrect syntax near the keyword 'WITH'

Jreco
Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

szymon_dybczak
Contributor III

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 🙂

 

Slash_0-1720728701965.png

 

 

 

 

View solution in original post

2 REPLIES 2

szymon_dybczak
Contributor III

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 🙂

 

Slash_0-1720728701965.png

 

 

 

 

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!

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group