def get_domain_records(
self,
domain_kwargs: Dict,
) -> Selectable:
"""
Uses the given domain kwargs (which include row_condition, condition_parser, and ignore_row_if directives) to
obtain and/or query a batch. Returns in the format of an SqlAlchemy table/column(s) object.
Args:
domain_kwargs (dict) - A dictionary consisting of the domain kwargs specifying which data to obtain
Returns:
An SqlAlchemy table/column(s) (the selectable object for obtaining data on which to compute)
"""
batch_id = domain_kwargs.get("batch_id")
if batch_id is None:
# We allow no batch id specified if there is only one batch
if self.active_batch_data:
data_object = self.active_batch_data
else:
raise GreatExpectationsError(
"No batch is specified, but could not identify a loaded batch."
)
else:
if batch_id in self.loaded_batch_data_dict:
data_object = self.loaded_batch_data_dict[batch_id]
else:
raise GreatExpectationsError(
f"Unable to find batch with batch_id {batch_id}"
)
selectable: Selectable
if "table" in domain_kwargs and domain_kwargs["table"] is not None:
# TODO: Add logic to handle record_set_name once implemented
# (i.e. multiple record sets (tables) in one batch
if domain_kwargs["table"] != data_object.selectable.name:
selectable = sa.Table(
domain_kwargs["table"],
sa.MetaData(),
schema=data_object._schema_name,
)
else:
selectable = data_object.selectable
elif "query" in domain_kwargs:
raise ValueError(
"query is not currently supported by SqlAlchemyExecutionEngine"
)
else:
selectable = data_object.selectable
"""
If a custom query is passed, selectable will be TextClause and not formatted
as a subquery wrapped in "(subquery) alias". TextClause must first be converted
to TextualSelect using sa.columns() before it can be converted to type Subquery
"""
if TextClause and isinstance(selectable, TextClause):
selectable = selectable.columns().subquery()
# Filtering by row condition.
if (
"row_condition" in domain_kwargs
and domain_kwargs["row_condition"] is not None
):
condition_parser = domain_kwargs["condition_parser"]
if condition_parser == "great_expectations__experimental__":
parsed_condition = parse_condition_to_sqlalchemy(
domain_kwargs["row_condition"]
)
selectable = (
sa.select([sa.text("*")])
.select_from(selectable)
.where(parsed_condition)
)
else:
raise GreatExpectationsError(
"SqlAlchemyExecutionEngine only supports the great_expectations condition_parser."
)
# Filtering by filter_conditions
filter_conditions: List[RowCondition] = domain_kwargs.get(
"filter_conditions", []
)
# For SqlAlchemyExecutionEngine only one filter condition is allowed
if len(filter_conditions) == 1:
filter_condition = filter_conditions[0]
assert (
filter_condition.condition_type == RowConditionParserType.GE
), "filter_condition must be of type GE for SqlAlchemyExecutionEngine"
selectable = (
sa.select([sa.text("*")])
.select_from(selectable)
.where(parse_condition_to_sqlalchemy(filter_condition.condition))
)
elif len(filter_conditions) > 1:
raise GreatExpectationsError(
"SqlAlchemyExecutionEngine currently only supports a single filter condition."
)
if "column" in domain_kwargs:
return selectable
# Filtering by ignore_row_if directive
if (
"column_A" in domain_kwargs
and "column_B" in domain_kwargs
and "ignore_row_if" in domain_kwargs
):
if self.active_batch_data.use_quoted_name:
# Checking if case-sensitive and using appropriate name
# noinspection PyPep8Naming
column_A_name = quoted_name(domain_kwargs["column_A"], quote=True)
# noinspection PyPep8Naming
column_B_name = quoted_name(domain_kwargs["column_B"], quote=True)
else:
# noinspection PyPep8Naming
column_A_name = domain_kwargs["column_A"]
# noinspection PyPep8Naming
column_B_name = domain_kwargs["column_B"]
ignore_row_if = domain_kwargs["ignore_row_if"]
if ignore_row_if == "both_values_are_missing":
selectable = get_sqlalchemy_selectable(
sa.select([sa.text("*")])
.select_from(get_sqlalchemy_selectable(selectable))
.where(
sa.not_(
sa.and_(
sa.column(column_A_name) == None,
sa.column(column_B_name) == None,
)
)
)
)
elif ignore_row_if == "either_value_is_missing":
selectable = get_sqlalchemy_selectable(
sa.select([sa.text("*")])
.select_from(get_sqlalchemy_selectable(selectable))
.where(
sa.not_(
sa.or_(
sa.column(column_A_name) == None,
sa.column(column_B_name) == None,
)
)
)
)
else:
if ignore_row_if not in ["neither", "never"]:
raise ValueError(
f'Unrecognized value of ignore_row_if ("{ignore_row_if}").'
)
if ignore_row_if == "never":
# deprecated-v0.13.29
warnings.warn(
f"""The correct "no-action" value of the "ignore_row_if" directive for the column pair case is \
"neither" (the use of "{ignore_row_if}" is deprecated as of v0.13.29 and will be removed in v0.16). Please use "neither" moving forward.
""",
DeprecationWarning,
)
return selectable
if "column_list" in domain_kwargs and "ignore_row_if" in domain_kwargs:
if self.active_batch_data.use_quoted_name:
# Checking if case-sensitive and using appropriate name
column_list = [
quoted_name(domain_kwargs[column_name], quote=True)
for column_name in domain_kwargs["column_list"]
]
else:
column_list = domain_kwargs["column_list"]
ignore_row_if = domain_kwargs["ignore_row_if"]
if ignore_row_if == "all_values_are_missing":
selectable = get_sqlalchemy_selectable(
sa.select([sa.text("*")])
.select_from(get_sqlalchemy_selectable(selectable))
.where(
sa.not_(
sa.and_(
*(
sa.column(column_name) == None
for column_name in column_list
)
)
)
)
)
elif ignore_row_if == "any_value_is_missing":
selectable = get_sqlalchemy_selectable(
sa.select([sa.text("*")])
.select_from(get_sqlalchemy_selectable(selectable))
.where(
sa.not_(
sa.or_(
*(
sa.column(column_name) == None
for column_name in column_list
)
)
)
)
)
else:
if ignore_row_if != "never":
raise ValueError(
f'Unrecognized value of ignore_row_if ("{ignore_row_if}").'
)
return selectable
return selectable
def test_parse_condition_to_sqlalchemy(sa):
res = parse_condition_to_sqlalchemy('col("foo") > 5')
assert str(res) == "foo > :foo_1"
res = parse_condition_to_sqlalchemy('col("foo").notNull()')
assert str(res) == "foo IS NOT NULL"
def get_compute_domain(
self,
domain_kwargs: Dict,
domain_type: Union[str, "MetricDomainTypes"],
accessor_keys: Optional[Iterable[str]] = None,
) -> Tuple["sa.sql.Selectable", dict, dict]:
"""Uses a given batch dictionary and domain kwargs to obtain a SqlAlchemy column object.
Args:
domain_kwargs (dict) - A dictionary consisting of the domain kwargs specifying which data to obtain
domain_type (str or "MetricDomainTypes") - an Enum value indicating which metric domain the user would
like to be using, or a corresponding string value representing it. String types include "identity", "column",
"column_pair", "table" and "other". Enum types include capitalized versions of these from the class
MetricDomainTypes.
accessor_keys (str iterable) - keys that are part of the compute domain but should be ignored when describing
the domain and simply transferred with their associated values into accessor_domain_kwargs.
Returns:
SqlAlchemy column
"""
# Extracting value from enum if it is given for future computation
domain_type = MetricDomainTypes(domain_type)
batch_id = domain_kwargs.get("batch_id")
if batch_id is None:
# We allow no batch id specified if there is only one batch
if self.active_batch_data:
data_object = self.active_batch_data
else:
raise GreatExpectationsError(
"No batch is specified, but could not identify a loaded batch."
)
else:
if batch_id in self.loaded_batch_data_dict:
data_object = self.loaded_batch_data_dict[batch_id]
else:
raise GreatExpectationsError(
f"Unable to find batch with batch_id {batch_id}"
)
compute_domain_kwargs = copy.deepcopy(domain_kwargs)
accessor_domain_kwargs = dict()
if "table" in domain_kwargs and domain_kwargs["table"] is not None:
if domain_kwargs["table"] != data_object.record_set_name:
raise ValueError("Unrecognized table name.")
else:
selectable = data_object.selectable
elif "query" in domain_kwargs:
raise ValueError(
"query is not currently supported by SqlAlchemyExecutionEngine"
)
else:
selectable = data_object.selectable
if (
"row_condition" in domain_kwargs
and domain_kwargs["row_condition"] is not None
):
condition_parser = domain_kwargs["condition_parser"]
if condition_parser == "great_expectations__experimental__":
parsed_condition = parse_condition_to_sqlalchemy(
domain_kwargs["row_condition"]
)
selectable = sa.select(
"*", from_obj=selectable, whereclause=parsed_condition
)
else:
raise GreatExpectationsError(
"SqlAlchemyExecutionEngine only supports the great_expectations condition_parser."
)
# Warning user if accessor keys are in any domain that is not of type table, will be ignored
if (
domain_type != MetricDomainTypes.TABLE
and accessor_keys is not None
and len(accessor_keys) > 0
):
logger.warning(
"Accessor keys ignored since Metric Domain Type is not 'table'"
)
if domain_type == MetricDomainTypes.TABLE:
if accessor_keys is not None and len(accessor_keys) > 0:
for key in accessor_keys:
accessor_domain_kwargs[key] = compute_domain_kwargs.pop(key)
if len(domain_kwargs.keys()) > 0:
for key in compute_domain_kwargs.keys():
# Warning user if kwarg not "normal"
if key not in [
"batch_id",
"table",
"row_condition",
"condition_parser",
]:
logger.warning(
f"Unexpected key {key} found in domain_kwargs for domain type {domain_type.value}"
)
return selectable, compute_domain_kwargs, accessor_domain_kwargs
# If user has stated they want a column, checking if one is provided, and
elif domain_type == MetricDomainTypes.COLUMN:
if "column" in compute_domain_kwargs:
# Checking if case- sensitive and using appropriate name
if self.active_batch_data.use_quoted_name:
accessor_domain_kwargs["column"] = quoted_name(
compute_domain_kwargs.pop("column")
)
else:
accessor_domain_kwargs["column"] = compute_domain_kwargs.pop(
"column"
)
else:
# If column not given
raise GreatExpectationsError(
"Column not provided in compute_domain_kwargs"
)
# Else, if column pair values requested
elif domain_type == MetricDomainTypes.COLUMN_PAIR:
# Ensuring column_A and column_B parameters provided
if (
"column_A" in compute_domain_kwargs
and "column_B" in compute_domain_kwargs
):
if self.active_batch_data.use_quoted_name:
# If case matters...
accessor_domain_kwargs["column_A"] = quoted_name(
compute_domain_kwargs.pop("column_A")
)
accessor_domain_kwargs["column_B"] = quoted_name(
compute_domain_kwargs.pop("column_B")
)
else:
accessor_domain_kwargs["column_A"] = compute_domain_kwargs.pop(
"column_A"
)
accessor_domain_kwargs["column_B"] = compute_domain_kwargs.pop(
"column_B"
)
else:
raise GreatExpectationsError(
"column_A or column_B not found within compute_domain_kwargs"
)
# Checking if table or identity or other provided, column is not specified. If it is, warning the user
elif domain_type == MetricDomainTypes.MULTICOLUMN:
if "columns" in compute_domain_kwargs:
# If columns exist
accessor_domain_kwargs["columns"] = compute_domain_kwargs.pop("columns")
# Filtering if identity
elif domain_type == MetricDomainTypes.IDENTITY:
# If we would like our data to become a single column
if "column" in compute_domain_kwargs:
if self.active_batch_data.use_quoted_name:
selectable = sa.select(
[sa.column(quoted_name(compute_domain_kwargs["column"]))]
).select_from(selectable)
else:
selectable = sa.select(
[sa.column(compute_domain_kwargs["column"])]
).select_from(selectable)
# If we would like our data to now become a column pair
elif ("column_A" in compute_domain_kwargs) and (
"column_B" in compute_domain_kwargs
):
if self.active_batch_data.use_quoted_name:
selectable = sa.select(
[
sa.column(quoted_name(compute_domain_kwargs["column_A"])),
sa.column(quoted_name(compute_domain_kwargs["column_B"])),
]
).select_from(selectable)
else:
selectable = sa.select(
[
sa.column(compute_domain_kwargs["column_A"]),
sa.column(compute_domain_kwargs["column_B"]),
]
).select_from(selectable)
else:
# If we would like our data to become a multicolumn
if "columns" in compute_domain_kwargs:
if self.active_batch_data.use_quoted_name:
# Building a list of column objects used for sql alchemy selection
to_select = [
sa.column(quoted_name(col))
for col in compute_domain_kwargs["columns"]
]
selectable = sa.select(to_select).select_from(selectable)
else:
to_select = [
sa.column(col) for col in compute_domain_kwargs["columns"]
]
selectable = sa.select(to_select).select_from(selectable)
# Letting selectable fall through
return selectable, compute_domain_kwargs, accessor_domain_kwargs