Azure Synapse temp table issues

[Shubhkishore]

Discussed in https://github.com/great-expectations/great_expectations/discussions/5078

^{Originally posted by Shubhkishore May 10, 2022} Hi,

I am trying to use Great Expectations with Azure synapse dedicated sql pool. I am running into issues when trying to create an expectation suite. The error is as follows:

ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'tempdb.pdw.INFORMATION_SCHEMA_COLUMNS'. (208) (SQLExecDirectW)")

ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'tempdb.pdw.INFORMATION_SCHEMA_COLUMNS'. (208) (SQLExecDirectW)")
[SQL: SELECT tempdb.[INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME] 
FROM tempdb.[INFORMATION_SCHEMA].[COLUMNS] 
WHERE tempdb.[INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME] LIKE CAST(? AS NVARCHAR(max))]
[parameters: ('#ge_temp_5f5d7fe6[_][_][_]%',)]
(Background on this error at: https://sqlalche.me/e/14/f405)

I see that Azure synapse has been used successfully with Great Expectations. Could someone please help me with this? 🙇

EDIT: It seems like the expectation suite gets created if the expectations are at table level but I'm getting the above error when there are column level expectations present. Will Appreciate any help on this!

[Shubhkishore]

I connected with the Microsoft support team about the tempdb.pdw.INFORMATION_SCHEMA_COLUMNS not being present in the user database. They said this object is only present in the master database and not in the user database.

Does that mean we would not be able to add column-level expectations in a user database?

[Shubhkishore]

It seems like the issue is only with V2 API and not in the V3 API. I was able to add expectation suite using the V3 API.

I wanted to use GE in Airflow but we are currently using a version 1.x Does that mean the great expectations operator for Airflow will not work with V3 API? Compatibility notes mentioned over here:

The Great Expectations V3 API requires Airflow 2.1+. If you're still running Airflow 1.x, you need to upgrade to at least 2.1 before using v0.1.0+ of the GreatExpectationsOperator.

[talagluck]

Hi @Shubhkishore - thank you for raising and thank you for all of the additional context!

The Great Expectations Airflow Operator currently only works with V3 and with Airflow 2.1+. If you are trying to run Great Expectations with Airflow 1.x, you will need to use the Python Operator or write your own Operator.

I'm going to close this for now as it appears the initial Issue has been resolved.