Cannot use QueryAsset with Microsoft SQL Server (MSSQL)

GX Core Support
1

Table assets work fine, but I can’t get QueryAssets working. If I leave the “create temp tables” setting on, code crashes with an error because this is not a valid MSSQL query -

OperationalError: (pymssql._pymssql.OperationalError) (156, b"Incorrect syntax near the keyword 'into'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
[SQL: SELECT * 
FROM (SELECT * into #gx_temp_3e2c9491 from t1 where c1 is not null) AS anon_1 
WHERE 1 = 1]

If I set create temp tables to False, I can construct a validator, but Expectations fail with the following error -

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/execution_engine/execution_engine.py:548, in ExecutionEngine._process_direct_and_bundled_metric_computation_configurations(self, metric_fn_direct_configurations, metric_fn_bundle_configurations)
    545 try:
    546     resolved_metrics[
    547         metric_computation_configuration.metric_configuration.id
--> 548     ] = metric_computation_configuration.metric_fn(  # type: ignore[misc] # F not callable
    549         **metric_computation_configuration.metric_provider_kwargs
    550     )
    551 except Exception as e:

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/expectations/metrics/metric_provider.py:50, in metric_value.<locals>.wrapper.<locals>.inner_func(*args, **kwargs)
     48 @wraps(metric_fn)
     49 def inner_func(*args, **kwargs):
---> 50     return metric_fn(*args, **kwargs)

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/expectations/metrics/table_metrics/table_columns.py:43, in TableColumns._sqlalchemy(cls, execution_engine, metric_domain_kwargs, metric_value_kwargs, metrics, runtime_configuration)
     42 column_metadata = metrics["table.column_types"]
---> 43 return [col["name"] for col in column_metadata]

TypeError: 'NoneType' object is not iterable

The above exception was the direct cause of the following exception:

MetricResolutionError                     Traceback (most recent call last)
/home/dgallagher/source/cr_health_check/Untitled.ipynb Cell 13 line 1
----> <a href='vscode-notebook-cell://wsl%2Bubuntu/home/dgallagher/source/cr_health_check/Untitled.ipynb#X14sdnNjb2RlLXJlbW90ZQ%3D%3D?line=0'>1</a> validator.expect_column_values_to_not_be_null(column="VehicleNumber")

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/validator/validator.py:594, in Validator.validate_expectation.<locals>.inst_expectation(*args, **kwargs)
    588         validation_result = ExpectationValidationResult(
    589             success=False,
    590             exception_info=exception_info,
    591             expectation_config=configuration,
    592         )
    593     else:
--> 594         raise err
    596 if self._include_rendered_content:
    597     validation_result.render()

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/validator/validator.py:557, in Validator.validate_expectation.<locals>.inst_expectation(*args, **kwargs)
    553     validation_result = ExpectationValidationResult(
    554         expectation_config=copy.deepcopy(expectation.configuration)
    555     )
    556 else:
--> 557     validation_result = expectation.validate(
    558         validator=self,
    559         evaluation_parameters=self._expectation_suite.evaluation_parameters,
    560         data_context=self._data_context,
    561         runtime_configuration=basic_runtime_configuration,
    562     )
    564 # If validate has set active_validation to true, then we do not save the config to avoid
    565 # saving updating expectation configs to the same suite during validation runs
    566 if self._active_validation is True:

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/expectations/expectation.py:1276, in Expectation.validate(self, validator, configuration, evaluation_parameters, interactive_evaluation, data_context, runtime_configuration)
   1267 self._warn_if_result_format_config_in_expectation_configuration(
   1268     configuration=configuration
   1269 )
   1271 configuration.process_evaluation_parameters(
   1272     evaluation_parameters, interactive_evaluation, data_context
   1273 )
   1274 expectation_validation_result_list: list[
   1275     ExpectationValidationResult
-> 1276 ] = validator.graph_validate(
   1277     configurations=[configuration],
   1278     runtime_configuration=runtime_configuration,
   1279 )
   1280 return expectation_validation_result_list[0]

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/validator/validator.py:1069, in Validator.graph_validate(self, configurations, runtime_configuration)
   1067         return evrs
   1068     else:
-> 1069         raise err
   1071 configuration: ExpectationConfiguration
   1072 result: ExpectationValidationResult

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/validator/validator.py:1048, in Validator.graph_validate(self, configurations, runtime_configuration)
   1041 resolved_metrics: _MetricsDict
   1043 try:
   1044     (
   1045         resolved_metrics,
   1046         evrs,
   1047         processed_configurations,
-> 1048     ) = self._resolve_suite_level_graph_and_process_metric_evaluation_errors(
   1049         graph=graph,
   1050         runtime_configuration=runtime_configuration,
   1051         expectation_validation_graphs=expectation_validation_graphs,
   1052         evrs=evrs,
   1053         processed_configurations=processed_configurations,
   1054         show_progress_bars=self._determine_progress_bars(),
   1055     )
   1056 except Exception as err:
   1057     # If a general Exception occurs during the execution of "ValidationGraph.resolve()", then
   1058     # all expectations in the suite are impacted, because it is impossible to attribute the failure to a metric.
   1059     if catch_exceptions:

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/validator/validator.py:1207, in Validator._resolve_suite_level_graph_and_process_metric_evaluation_errors(self, graph, runtime_configuration, expectation_validation_graphs, evrs, processed_configurations, show_progress_bars)
   1199 resolved_metrics: _MetricsDict
   1200 aborted_metrics_info: Dict[
   1201     _MetricKey,
   1202     Dict[str, Union[MetricConfiguration, Set[ExceptionInfo], int]],
   1203 ]
   1204 (
   1205     resolved_metrics,
   1206     aborted_metrics_info,
-> 1207 ) = self._metrics_calculator.resolve_validation_graph(
   1208     graph=graph,
   1209     runtime_configuration=runtime_configuration,
   1210     min_graph_edges_pbar_enable=0,
   1211 )
   1213 # Trace MetricResolutionError occurrences to expectations relying on corresponding malfunctioning metrics.
   1214 rejected_configurations: List[ExpectationConfiguration] = []

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/validator/metrics_calculator.py:287, in MetricsCalculator.resolve_validation_graph(self, graph, runtime_configuration, min_graph_edges_pbar_enable)
    282 resolved_metrics: _MetricsDict
    283 aborted_metrics_info: Dict[
    284     _MetricKey,
    285     Dict[str, Union[MetricConfiguration, Set[ExceptionInfo], int]],
    286 ]
--> 287 resolved_metrics, aborted_metrics_info = graph.resolve(
    288     runtime_configuration=runtime_configuration,
    289     min_graph_edges_pbar_enable=min_graph_edges_pbar_enable,
    290     show_progress_bars=self._show_progress_bars,
    291 )
    292 return resolved_metrics, aborted_metrics_info

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/validator/validation_graph.py:209, in ValidationGraph.resolve(self, runtime_configuration, min_graph_edges_pbar_enable, show_progress_bars)
    203 resolved_metrics: Dict[_MetricKey, MetricValue] = {}
    205 # updates graph with aborted metrics
    206 aborted_metrics_info: Dict[
    207     _MetricKey,
    208     Dict[str, Union[MetricConfiguration, Set[ExceptionInfo], int]],
--> 209 ] = self._resolve(
    210     metrics=resolved_metrics,
    211     runtime_configuration=runtime_configuration,
    212     min_graph_edges_pbar_enable=min_graph_edges_pbar_enable,
    213     show_progress_bars=show_progress_bars,
    214 )
    216 return resolved_metrics, aborted_metrics_info

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/validator/validation_graph.py:315, in ValidationGraph._resolve(self, metrics, runtime_configuration, min_graph_edges_pbar_enable, show_progress_bars)
    311                 failed_metric_info[failed_metric.id]["exception_info"] = {
    312                     exception_info
    313                 }
    314     else:
--> 315         raise err
    316 except Exception as e:
    317     if catch_exceptions:

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/validator/validation_graph.py:285, in ValidationGraph._resolve(self, metrics, runtime_configuration, min_graph_edges_pbar_enable, show_progress_bars)
    280         computable_metrics.add(metric)
    282 try:
    283     # Access "ExecutionEngine.resolve_metrics()" method, to resolve missing "MetricConfiguration" objects.
    284     metrics.update(
--> 285         self._execution_engine.resolve_metrics(
    286             metrics_to_resolve=computable_metrics,  # type: ignore[arg-type]  # Metric typing needs further refinement.
    287             metrics=metrics,  # type: ignore[arg-type]  # Metric typing needs further refinement.
    288             runtime_configuration=runtime_configuration,
    289         )
    290     )
    291     progress_bar.update(len(computable_metrics))
    292     progress_bar.refresh()

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/execution_engine/execution_engine.py:283, in ExecutionEngine.resolve_metrics(self, metrics_to_resolve, metrics, runtime_configuration)
    274 metric_fn_bundle_configurations: List[MetricComputationConfiguration]
    275 (
    276     metric_fn_direct_configurations,
    277     metric_fn_bundle_configurations,
   (...)
    281     runtime_configuration=runtime_configuration,
    282 )
--> 283 return self._process_direct_and_bundled_metric_computation_configurations(
    284     metric_fn_direct_configurations=metric_fn_direct_configurations,
    285     metric_fn_bundle_configurations=metric_fn_bundle_configurations,
    286 )

File ~/source/cr_health_check/.venv/lib/python3.10/site-packages/great_expectations/execution_engine/execution_engine.py:552, in ExecutionEngine._process_direct_and_bundled_metric_computation_configurations(self, metric_fn_direct_configurations, metric_fn_bundle_configurations)
    546         resolved_metrics[
    547             metric_computation_configuration.metric_configuration.id
    548         ] = metric_computation_configuration.metric_fn(  # type: ignore[misc] # F not callable
    549             **metric_computation_configuration.metric_provider_kwargs
    550         )
    551     except Exception as e:
--> 552         raise gx_exceptions.MetricResolutionError(
    553             message=str(e),
    554             failed_metrics=(
    555                 metric_computation_configuration.metric_configuration,
    556             ),
    557         ) from e
    559 try:
    560     # an engine-specific way of computing metrics together
    561     resolved_metric_bundle: Dict[
    562         Tuple[str, str, str], MetricValue
    563     ] = self.resolve_metric_bundle(
    564         metric_fn_bundle=metric_fn_bundle_configurations
    565     )

MetricResolutionError: 'NoneType' object is not iterable
2

Hi @oneextrafact would you please share your GX code? This will help me better diagnose what might be happening on your end.

3

I’d rather not create temp tables anyway, so this is the code I’m using to set up the asset and call an expectation on the validator:

table_asset = datasource.add_query_asset(name="sta", query="select c1 from t1 where c2 is not null")

mbr = table_asset.build_batch_request()

validator = context.get_validator(
    batch_request=mbr,
    expectation_suite_name="tss"
)

print(validator.head())


validator.expect_column_min_to_be_between(column="c1", min_value=1)

This gives the stack trace above. The head() method works, just not the expect_.

4

@HaebichanGX, for what it’s worth the problem is in sqlalchemy_batch_data. I replaced the code for temp table gen with this:

    elif dialect == GXSqlDialect.MSSQL:
        # Insert "into #{temp_table_name}" in the custom sql query right before the "from" clause
        # Split is case-sensitive so detect case.
        # Note: transforming query to uppercase/lowercase has unintended consequences (i.e.,
        # changing column names), so this is not an option!
        # noinspection PyUnresolvedReferences
        if isinstance(query, sa.dialects.mssql.base.MSSQLCompiler):
            query = query.string  # extracting string from MSSQLCompiler object

        querymod = query.split("*", maxsplit=1)
        stmt = f"{querymod[0]} * into {{temp_table_name}} {querymod[1]}".format(
            temp_table_name=temp_table_name
        )

I don’t think that’ll be the permanent fix, but I can run expectations with it in place

5

you may try this combination - it works for me WITHOUT “create_temp_table: False” key:
SQLAlchemy==2.0.0
pyodbc==5.0.1
great_expectations[sqlalchemy]==0.18.6

If you add create_temp_table: False key, it will produce the same NoneType error

if you try 0.18.7 - you get incorrect select … into syntax error