Hello!
Please support me in solving the issue when trying to create the expectation suite automatically using the profiler.
The same issue running the script in Jupiter Notebook trying to create the expectation interactively, with a sample batch of data.
Installed great_expectations version: 0.14.1
Pre-installed extension module: cx_Oracle
Successful connection to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit
Clients installed on local machine: 12.2.0 , 19.0.0, instantclient_21_3
Local machine OS: Windows 10 Enterprise
Steps to reproduce via cmd:
great_expectations suite new /enter/
: 3 /enter/
: l /enter/
: n /enter/
……………………
: asset_index /enter/
/enter/
/enter/
Result:
(cx_Oracle.DatabaseError) ORA-00920: invalid relational operator
[SQL: CREATE GLOBAL TEMPORARY TABLE ge_temp_f9155f45 ON COMMIT PRESERVE ROWS AS SELECT * FROM table_name WHERE true]
(Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)
Likely cause:
Trying to execute the SQL statement, but the WHERE clause contains TRUE and our DBMS does not recognize TRUE as a valid query statement.
Resolution:
I ask You to make changes in query scripts the TRUE statement to the 1=1 .
Error messages occurred as a result of running the below script in Jupyter Notebook.
import datetime
import pandas as pd
import great_expectations as ge
import great_expectations.jupyter_ux
from great_expectations.core.batch import BatchRequest
from great_expectations.checkpoint import SimpleCheckpoint
from great_expectations.exceptions import DataContextError
context = ge.data_context.DataContext()
# Note that if you modify this batch request, you may save the new version as a .json file
# to pass in later via the --batch-request option
batch_request = {‘datasource_name’: ‘test_dwh’, ‘data_connector_name’: ‘default_inferred_data_connector_name’, ‘data_asset_name’: ‘schema.table_name’, ‘limit’: 1000}
# Feel free to change the name of your suite here. Renaming this will not remove the other one.
expectation_suite_name = “test202201180900”
try:
suite = context.get_expectation_suite(expectation_suite_name=expectation_suite_name)
print(f’Loaded ExpectationSuite “{suite.expectation_suite_name}” containing {len(suite.expectations)} expectations.')
except DataContextError:
suite = context.create_expectation_suite(expectation_suite_name=expectation_suite_name)
print(f’Created ExpectationSuite “{suite.expectation_suite_name}”.')
validator = context.get_validator(
batch_request=BatchRequest(**batch_request),
expectation_suite_name=expectation_suite_name
)
column_names = [f’“{column_name}”’ for column_name in validator.columns()]
print(f"Columns: {', '.join(column_names)}.")
validator.head(n_rows=5, fetch_all=False)
Error messages:
2022-01-18T09:18:50+0200 - INFO - Great Expectations logging enabled at 20 level by JupyterUX module.
Loaded ExpectationSuite “test202201180900” containing 0 expectations.
---------------------------------------------------------------------------
DatabaseError Traceback (most recent call last)
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _execute_context **(self, dialect, constructor, statement, parameters, execution_options, *args, kw)
1801 if not evt_handled :
→ 1802 self.dialect.do_execute(
1803 cursor , statement , parameters , context
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py in do_execute (self, cursor, statement, parameters, context)
718 def do_execute ( self , cursor , statement , parameters , context = None ):
→ 719 cursor . execute ( statement , parameters )
720
DatabaseError : ORA-00905: missing keyword
The above exception was the direct cause of the following exception:
DatabaseError Traceback (most recent call last)
C:\Python\Python310\lib\site-packages\great_expectations\execution_engine\sqlalchemy_batch_data.py in _create_temporary_table (self, temp_table_name, query, temp_table_schema_name)
265 try :
→ 266 self . _engine . execute ( stmt_1 )
267 except DatabaseError :
in execute **(self, statement, *multiparams, params)
C:\Python\Python310\lib\site-packages\sqlalchemy\util\deprecations.py in warned **(fn, *args, kwargs)
400 _warn_with_version ( message , version , wtype , stacklevel = 3 )
→ 401 return fn (* args , ****** kwargs )
402
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in execute **(self, statement, *multiparams, params)
3138 connection = self . connect ( close_with_result = True )
→ 3139 return connection . execute ( statement , ***** multiparams , ****** params )
3140
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in execute **(self, statement, *multiparams, params)
1273
→ 1274 return self._exec_driver_sql(
1275 statement ,
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _exec_driver_sql (self, statement, multiparams, params, execution_options, future)
1577 dialect = self . dialect
→ 1578 ret = self._execute_context(
1579 dialect ,
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _execute_context **(self, dialect, constructor, statement, parameters, execution_options, *args, kw)
1844 except BaseException as e :
→ 1845 self._handle_dbapi_exception(
1846 e , statement , parameters , cursor , context
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _handle_dbapi_exception (self, e, statement, parameters, cursor, context)
2025 elif should_wrap :
→ 2026 util.raise_(
2027 sqlalchemy_exception , with_traceback = exc_info [ 2 ], from_ = e
C:\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py in raise_ (failed resolving arguments)
206 try :
→ 207 raise exception
208 finally :
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _execute_context **(self, dialect, constructor, statement, parameters, execution_options, *args, kw)
1801 if not evt_handled :
→ 1802 self.dialect.do_execute(
1803 cursor , statement , parameters , context
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py in do_execute (self, cursor, statement, parameters, context)
718 def do_execute ( self , cursor , statement , parameters , context = None ):
→ 719 cursor . execute ( statement , parameters )
720
DatabaseError : (cx_Oracle.DatabaseError) ORA-00905: missing keyword
[SQL: CREATE PRIVATE TEMPORARY TABLE ge_temp_32fedb65 ON COMMIT PRESERVE DEFINITION AS SELECT *
FROM schema.table_name
WHERE true]
(Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)
During handling of the above exception, another exception occurred:
DatabaseError Traceback (most recent call last)
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _execute_context **(self, dialect, constructor, statement, parameters, execution_options, *args, kw)
1801 if not evt_handled :
→ 1802 self.dialect.do_execute(
1803 cursor , statement , parameters , context
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py in do_execute (self, cursor, statement, parameters, context)
718 def do_execute ( self , cursor , statement , parameters , context = None ):
→ 719 cursor . execute ( statement , parameters )
720
DatabaseError : ORA-00920: invalid relational operator
The above exception was the direct cause of the following exception:
DatabaseError Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_12308/1408731878.py in
26
27
—> 28 validator = context.get_validator(
29 batch_request = BatchRequest (** batch_request ),
30 expectation_suite_name = expectation_suite_name
C:\Python\Python310\lib\site-packages\great_expectations\data_context\data_context.py in get_validator **(self, datasource_name, data_connector_name, data_asset_name, batch_request, batch_request_list, batch_data, data_connector_query, batch_identifiers, limit, index, custom_filter_function, sampling_method, sampling_kwargs, splitter_method, splitter_kwargs, runtime_parameters, query, path, batch_filter_parameters, expectation_suite_ge_cloud_id, batch_spec_passthrough, expectation_suite_name, expectation_suite, create_expectation_suite_with_name, kwargs)
1753 for batch_request in batch_request_list :
1754 batch_list.extend(
→ 1755 self.get_batch_list(
1756 datasource_name = datasource_name ,
1757 data_connector_name = data_connector_name ,
C:\Python\Python310\lib\site-packages\great_expectations\core\usage_statistics\usage_statistics.py in usage_statistics_wrapped_method **(*args, kwargs)
305 nested_update ( event_payload , args_payload_fn (* args , ****** kwargs ))
306
→ 307 result = func (* args , ****** kwargs )
308 message [ “success” ] = True
309 except Exception :
C:\Python\Python310\lib\site-packages\great_expectations\data_context\data_context.py in get_batch_list **(self, datasource_name, data_connector_name, data_asset_name, batch_request, batch_data, data_connector_query, batch_identifiers, limit, index, custom_filter_function, sampling_method, sampling_kwargs, splitter_method, splitter_kwargs, runtime_parameters, query, path, batch_filter_parameters, batch_spec_passthrough, kwargs)
1672 f"The given datasource could not be retrieved from the DataContext; please confirm that your configuration is accurate." ,
1673 )
→ 1674 return datasource . get_batch_list_from_batch_request ( batch_request = batch_request )
1675
1676 def get_validator(
C:\Python\Python310\lib\site-packages\great_expectations\datasource\new_datasource.py in get_batch_list_from_batch_request (self, batch_request)
191 batch_spec ,
192 batch_markers ,
→ 193 ) = data_connector . get_batch_data_and_metadata (
194 batch_definition = batch_definition
195 )
C:\Python\Python310\lib\site-packages\great_expectations\datasource\data_connector\data_connector.py in get_batch_data_and_metadata (self, batch_definition)
109 “”"
110 batch_spec : BatchSpec = self . build_batch_spec ( batch_definition = batch_definition )
→ 111 batch_data, batch_markers = self._execution_engine.get_batch_data_and_markers(
112 batch_spec = batch_spec
113 )
C:\Python\Python310\lib\site-packages\great_expectations\execution_engine\sqlalchemy_execution_engine.py in get_batch_data_and_markers (self, batch_spec)
1126 )
1127
→ 1128 batch_data = SqlAlchemyBatchData(
1129 execution_engine = self ,
1130 selectable = selectable ,
C:\Python\Python310\lib\site-packages\great_expectations\execution_engine\sqlalchemy_batch_data.py in init (self, execution_engine, record_set_name, schema_name, table_name, query, selectable, create_temp_table, temp_table_schema_name, temp_table_name, use_quoted_name, source_schema_name, source_table_name)
151 compile_kwargs ={ “literal_binds” : True },
152 )
→ 153 self._create_temporary_table(
154 temp_table_name = generated_table_name ,
155 query = query ,
C:\Python\Python310\lib\site-packages\great_expectations\execution_engine\sqlalchemy_batch_data.py in _create_temporary_table (self, temp_table_name, query, temp_table_schema_name)
266 self . _engine . execute ( stmt_1 )
267 except DatabaseError :
→ 268 self . _engine . execute ( stmt_2 )
269 else :
270 self . _engine . execute ( stmt )
in execute **(self, statement, *multiparams, params)
C:\Python\Python310\lib\site-packages\sqlalchemy\util\deprecations.py in warned **(fn, *args, kwargs)
399 if not skip_warning :
400 _warn_with_version ( message , version , wtype , stacklevel = 3 )
→ 401 return fn (* args , ****** kwargs )
402
403 doc = func . doc is not None and func . doc or “”
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in execute **(self, statement, *multiparams, params)
3137 “”"
3138 connection = self . connect ( close_with_result = True )
→ 3139 return connection . execute ( statement , ***** multiparams , ****** params )
3140
3141 @util.deprecated_20(
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in execute **(self, statement, *multiparams, params)
1272 )
1273
→ 1274 return self._exec_driver_sql(
1275 statement ,
1276 multiparams ,
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _exec_driver_sql (self, statement, multiparams, params, execution_options, future)
1576
1577 dialect = self . dialect
→ 1578 ret = self._execute_context(
1579 dialect ,
1580 dialect . execution_ctx_cls . _init_statement ,
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _execute_context **(self, dialect, constructor, statement, parameters, execution_options, *args, kw)
1843
1844 except BaseException as e :
→ 1845 self._handle_dbapi_exception(
1846 e , statement , parameters , cursor , context
1847 )
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _handle_dbapi_exception (self, e, statement, parameters, cursor, context)
2024 util . raise_ ( newraise , with_traceback = exc_info [ 2 ], from_ = e )
2025 elif should_wrap :
→ 2026 util.raise_(
2027 sqlalchemy_exception , with_traceback = exc_info [ 2 ], from_ = e
2028 )
C:\Python\Python310\lib\site-packages\sqlalchemy\util\compat.py in raise_ (failed resolving arguments)
205
206 try :
→ 207 raise exception
208 finally :
209 # credit to
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py in _execute_context **(self, dialect, constructor, statement, parameters, execution_options, *args, kw)
1800 break
1801 if not evt_handled :
→ 1802 self.dialect.do_execute(
1803 cursor , statement , parameters , context
1804 )
C:\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py in do_execute (self, cursor, statement, parameters, context)
717
718 def do_execute ( self , cursor , statement , parameters , context = None ):
→ 719 cursor . execute ( statement , parameters )
720
721 def do_execute_no_params ( self , cursor , statement , context = None ):
DatabaseError : (cx_Oracle.DatabaseError) ORA-00920: invalid relational operator
[SQL: CREATE GLOBAL TEMPORARY TABLE ge_temp_32fedb65 ON COMMIT PRESERVE ROWS AS SELECT *
FROM schema.table_name
WHERE true]
(Background on this error at: Error Messages — SQLAlchemy 1.4 Documentation)