great_expectations
great_expectations copied to clipboard
great-expectations
Error: [SQL Server]Incorrect syntax near the keyword 'into'
Discussed in https://github.com/great-expectations/great_expectations/discussions/9765
Originally posted by kapilsaini2009 April 16, 2024 I am trying to run GX with MSSQL but getting incorrect syntax error.
Problem:
Generated SQL by the framework for the given query is incorrect. Input query for RuntimeBatchRequest - "select * from summary_data" Generated SQL- SELECT * FROM (SELECT * into #gx_temp_405773af from summary_data) AS anon_1 WHERE 1 = 1
Error Message:
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'into'. (156) (SQLExecDirectW)") [SQL: SELECT * FROM (SELECT * into #gx_temp_405773af from summary_data) AS anon_1 WHERE 1 = 1]
Code:
asset = 'summary_data'
query = "select * from summary_data"
batch_request = RuntimeBatchRequest(
datasource_name='sql_server_datasource',
data_connector_name='sql_gx_data_connector',
data_asset_name=f'{asset}',
runtime_parameters={
"query": query,
},
batch_identifiers={"sql_gx_identifier": 'summary_checkpoint'}
)
validation_result = context.run_checkpoint(checkpoint_name="my_quickstart_checkpoint",
expectation_suite_name="summary_data_suite_1", batch_request=batch_request)
Error Logs:
Traceback (most recent call last): File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context self.dialect.do_execute( File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute cursor.execute(statement, parameters) pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'into'. (156) (SQLExecDirectW)")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/u1/kasaini/kpfiles/gx/gx/gx_db_batch.py", line 86, in
Can someone please suggest what is wrong in my implementation or is there any other issue ?
I knew this issue and it is because of using SQLALchemy with SQL Server. I will fix it.
Hello @kapilsaini2009. With the launch of Great Expectations Core (GX 1.0), we are closing old issues posted regarding previous versions. Moving forward, we will focus our resources on supporting and improving GX Core (version 1.0 and beyond). If you find that an issue you previously reported still exists in GX Core, we encourage you to resubmit it against the new version. With more resources dedicated to community support, we aim to tackle new issues swiftly. For specific details on what is GX-supported vs community-supported, you can reference our integration and support policy.
To get started on your transition to GX Core, check out the GX Core quickstart (click “Full example code” tab to see a code example).
You can also join our upcoming community meeting on August 28th at 9am PT (noon ET / 4pm UTC) for a comprehensive rundown of everything GX Core, plus Q&A as time permits. Go to https://greatexpectations.io/meetup and click “follow calendar” to follow the GX community calendar.
Thank you for being part of the GX community and thank you for submitting this issue. We're excited about this new chapter and look forward to your feedback on GX Core. 🤗
