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

It is impossible using spark.read jdbc. When you use dbtable or query parameters, effect is to insert your SQL code as a subquery inside larger SELECT statement.

Spark docs for dbtable param are poor, IMHO, but you can see where this heading in query doc.

As an example, spark will issue a query of the following form to the JDBC Source.

SELECT <columns> FROM (<user_specified_query>) spark_gen_alias

The thing we specify gets turned into a subquery.

Add that with the fact that a WITH clause must not be included in a subquery. I'm looking for proof of that claim in the Microsoft docs.

Closest I can find is in doc referred to above, which says WITH must be followed by single SELECT statement. https://github.com/uglide/azure-content/blob/master/articles/sql-data-warehouse/sql-data-warehouse-migrate-code.md

Answer 3

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