I inherited from a colleague some SQL queries that make heavy use of the SQL WITH clause against a MS SQL database, but I can't seem to be able to run them and get the result in the form of pandas dataframes using azureml-dataprep.
As a simple example, consider e.g. the code
import azureml.dataprep as dprep
import time
secret = dprep.register_secret(value="flowerpower", id="id")
db = dprep.MSSQLDataSource(server_name="myserver.net, 12345",
database_name="Flowers",
user_name="davide",
password=secret)
I then define two queries, which should be completely equivalent:
flower_query = '''SELECT TOP (100) [Sepal.Length], [Sepal.Width]
FROM [Flowers].[Iris]'''
# unnecessarily convoluted, but still perfectly legit syntax:
flower_with_query = '''WITH I AS (SELECT TOP (100) [Sepal.Length], [Sepal.Width]
FROM [Flowers].[Iris])
SELECT * FROM I'''
The following "non-WITH" query runs without problems:
flower_dataflow = dprep.read_sql(db, flower_query)
flower_df = flower_dataflow.to_pandas_dataframe()
The WITH variant
flower_dataflow = dprep.read_sql(db, flower_with_query) # using the WITH query
flower_df = flower_dataflow.to_pandas_dataframe()
fails with
ExecutionError: Error Code: ScriptExecution.DatabaseQuery Failed Step: b1da84ed-e429-4752-ac98-4cb74b9ca114 Error Message: ScriptExecutionException was caused by DatabaseQueryException.
'MSSQL' encountered exception of with error code '156' while executing query 'SELECT * FROM (WITH I AS (SELECT TOP (100) [Sepal.Length], [Sepal.Width] FROM [Flowers].[Iris]) SELECT * FROM I) as subquery'. SqlException class: 15. HResult: x80131904. Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.
From the error, it seems as if the library is prepending a SELECT * FROM to my original query (?), and that gives rise to problems.
Prepending a semicolon ; to my flower_with_query is not good enough to solve, and when doing so I get
'MSSQL' encountered exception of with error code '102' while executing query 'SELECT * FROM (;WITH I AS (SELECT TOP (100)...
Is there a limitation to the set of queries that can be run through azureml-dataprep? If so, is there a workaround that allows to run successfully queries without any SQL refactoring? I am aware of ODBC drivers but I'd rather use a solution that needs the least possible configuration (azureml-dataprep excels at that I think).
I am using azureml-dataprep-2.0.7 and azureml-core-1.13.0.