Describe the bug
I am running validations on a redshift spectrum external table with files stored in s3. When running the validation i pass a query to the checkpoint batch_request to get to a single column from the table to avoid a "spectrum can not select *" error, however, i get the following from the great expectations results that are returned:
"exception_message": "Textual SQL expression 'ge_temp_00df7feb' should be explicitly declared as text('ge_temp_00df7feb')"
This seems to happen when using a RuntimeDataConnector.
Changing this line to the below solves this issue:
from sqlalchemy import text
query: Select = sa.select([sa.text("*")]).select_from(text(selectable)).limit(1)
Expected behavior
Should not get this error:
Textual SQL expression 'ge_temp_00df7feb' should be explicitly declared as text('ge_temp_00df7feb')"
Environment (please complete the following information):
- Operating System: MacOS
- Great Expectations Version: 0.15.22
Additional context
Full traceback
"exception_traceback": "Traceback (most recent call last):\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/expectations/metrics/util.py", line 339, in get_sqlalchemy_column_metadata\n columns = inspector.get_columns(\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 390, in get_columns\n col_defs = self.dialect.get_columns(\n File "", line 2, in get_columns\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 52, in cache\n ret = fn(self, con, *args, **kw)\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy_redshift/dialect.py", line 617, in get_columns\n cols = self._get_redshift_columns(connection, table_name, schema, **kw)\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy_redshift/dialect.py", line 851, in _get_redshift_columns\n return all_schema_columns[key]\nKeyError: RelationKey(name='ge_temp_00df7feb', schema='public')\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/execution_engine/execution_engine.py", line 393, in resolve_metrics\n resolved_metrics[metric_to_resolve.id] = metric_fn(\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/expectations/metrics/metric_provider.py", line 34, in inner_func\n return metric_fn(args, **kwargs)\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/expectations/metrics/table_metrics/table_column_types.py", line 71, in _sqlalchemy\n return _get_sqlalchemy_column_metadata(execution_engine.engine, batch_data)\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/expectations/metrics/table_metrics/table_column_types.py", line 100, in _get_sqlalchemy_column_metadata\n return get_sqlalchemy_column_metadata(\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/expectations/metrics/util.py", line 351, in get_sqlalchemy_column_metadata\n columns = column_reflection_fallback(\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/expectations/metrics/util.py", line 573, in column_reflection_fallback\n query: Select = sa.select([sa.text("")]).select_from(selectable).limit(1)\n File "", line 2, in select_from\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy/sql/base.py", line 47, in _generative\n fn(self, *args[1:], **kw)\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 3673, in select_from\n self.append_from(fromclause)\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 3865, in append_from\n fromclause = _interpret_as_from(fromclause)\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 60, in _interpret_as_from\n no_text_coercion(element)\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 4733, in no_text_coercion\n util.raise(\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise\n raise exception\nsqlalchemy.exc.ArgumentError: Textual SQL expression 'ge_temp_00df7feb' should be explicitly declared as text('ge_temp_00df7feb')\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/validator/validator.py", line 1314, in resolve_validation_graph\n self._resolve_metrics(\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/validator/validator.py", line 2237, in _resolve_metrics\n return execution_engine.resolve_metrics(\n File "/Users/tommywatts/.pyenv/versions/ge/lib/python3.8/site-packages/great_expectations/execution_engine/execution_engine.py", line 397, in resolve_metrics\n raise ge_exceptions.MetricResolutionError(\ngreat_expectations.exceptions.exceptions.MetricResolutionError: Textual SQL expression 'ge_temp_00df7feb' should be explicitly declared as text('ge_temp_00df7feb')\n"
Howdy @tommy-watts-depop :wave: thanks for raising this with us and being a part of this lovely community :bow:
I created the above PR to make the change a bit easier to read; however, if you would like to submit a contribution yourself we'd greatly encourage it, and will review it all the same. 🔬
@AFineDayFor Thanks for this, i have raised my own PR here https://github.com/great-expectations/great_expectations/pull/6150
Addressed in https://github.com/great-expectations/great_expectations/pull/6159
great_expectations copied to clipboard
great-expectations
