Cannot execute CTE query via Azure DataBricks

Question

I'm trying to execute CTE query via data bricks getting syntax error for SQL query. Is there any other to use CTE from Data bricks?

Thanks in Advance .

pushdown_query = """(WITH t(x, y) AS (SELECT 1, 2) SELECT * FROM t WHERE x = 1 AND y = 2) as Test """ df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties) display(df)

Error:- "com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword WITH."

Answer 1

Just remove the as test syntax:

WITH t(x, y) AS (SELECT 1, 2) SELECT * FROM t WHERE x = 1 AND y = 2

This will work.

Answer 2

I think CTE functionality is stripped out of Azure SQL Server, which is also known as Synapse. You may be able to re-write some of your queries to do what you need, without using the standard CTE syntax.

These links should shed some light on the situation.

https://github.com/uglide/azure-content/blob/master/articles/sql-data-warehouse/sql-data-warehouse-migrate-code.md

https://docs.microsoft.com/en-us/azure/azure-sql/database/transact-sql-tsql-differences-sql-server