def _query_join_relation(
self,
query: Query,
root_relation: str,
aliases_mapping: Dict[str, AliasedClass] = None,
) -> Query:
"""
Helper function that applies necessary joins for dotted columns on a
SQLAlchemy query object
:param query: SQLAlchemy query object
:param root_relation: The root part of a dotted column, so the root relation
:return: Transformed SQLAlchemy Query
"""
if aliases_mapping is None:
aliases_mapping = {}
relations = self.get_related_model_and_join(root_relation)
for relation in relations:
model_relation, relation_join = relation
# Use alias if it's not a custom relation
if not hasattr(relation_join, "clauses"):
model_relation = aliased(model_relation, name=root_relation)
aliases_mapping[root_relation] = model_relation
relation_pk = self.get_pk(model_relation)
if relation_join.left.foreign_keys:
relation_join = BinaryExpression(relation_join.left,
relation_pk,
relation_join.operator)
else:
relation_join = BinaryExpression(relation_join.right,
relation_pk,
relation_join.operator)
query = query.join(model_relation, relation_join, isouter=True)
return query
def _query_join_relation(self, query: BaseQuery,
root_relation: str) -> BaseQuery:
"""
Helper function that applies necessary joins for dotted columns on a
SQLAlchemy query object
:param query: SQLAlchemy query object
:param root_relation: The root part of a dotted column, so the root relation
:return: Transformed SQLAlchemy Query
"""
relations = self.get_related_model_and_join(root_relation)
for relation in relations:
model_relation, relation_join = relation
# Support multiple joins for the same table
if self.is_model_already_joined(query, model_relation):
# Since the join already exists apply a new aliased one
model_relation = aliased(model_relation)
# The binary expression needs to be inverted
relation_pk = self.get_pk(model_relation)
relation_join = BinaryExpression(relation_join.left,
relation_pk,
relation_join.operator)
query = query.join(model_relation, relation_join, isouter=True)
return query
def get_dialect_regex_expression(column, regex, dialect, positive=True):
try:
# postgres
if issubclass(dialect.dialect, sa.dialects.postgresql.dialect):
if positive:
return BinaryExpression(column, literal(regex), custom_op("~"))
else:
return BinaryExpression(column, literal(regex),
custom_op("!~"))
except AttributeError:
pass
try:
# redshift
if issubclass(dialect.dialect,
sqlalchemy_redshift.dialect.RedshiftDialect):
if positive:
return BinaryExpression(column, literal(regex), custom_op("~"))
else:
return BinaryExpression(column, literal(regex),
custom_op("!~"))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# MySQL
if issubclass(dialect.dialect, sa.dialects.mysql.dialect):
if positive:
return BinaryExpression(column, literal(regex),
custom_op("REGEXP"))
else:
return BinaryExpression(column, literal(regex),
custom_op("NOT REGEXP"))
except AttributeError:
pass
try:
# Snowflake
if issubclass(
dialect.dialect,
snowflake.sqlalchemy.snowdialect.SnowflakeDialect,
):
if positive:
return BinaryExpression(column, literal(regex),
custom_op("RLIKE"))
else:
return BinaryExpression(column, literal(regex),
custom_op("NOT RLIKE"))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# Bigquery
if issubclass(dialect.dialect,
pybigquery.sqlalchemy_bigquery.BigQueryDialect):
if positive:
return sa.func.REGEXP_CONTAINS(column, literal(regex))
else:
return sa.not_(sa.func.REGEXP_CONTAINS(column, literal(regex)))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
return None
def get_dialect_regex_expression(column, regex, dialect, positive=True):
try:
# postgres
if issubclass(dialect.dialect, sa.dialects.postgresql.dialect):
if positive:
return BinaryExpression(column, literal(regex), custom_op("~"))
else:
return BinaryExpression(column, literal(regex),
custom_op("!~"))
except AttributeError:
pass
try:
# redshift
# noinspection PyUnresolvedReferences
if issubclass(dialect.dialect,
sqlalchemy_redshift.dialect.RedshiftDialect):
if positive:
return BinaryExpression(column, literal(regex), custom_op("~"))
else:
return BinaryExpression(column, literal(regex),
custom_op("!~"))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# MySQL
if issubclass(dialect.dialect, sa.dialects.mysql.dialect):
if positive:
return BinaryExpression(column, literal(regex),
custom_op("REGEXP"))
else:
return BinaryExpression(column, literal(regex),
custom_op("NOT REGEXP"))
except AttributeError:
pass
try:
# Snowflake
if issubclass(
dialect.dialect,
snowflake.sqlalchemy.snowdialect.SnowflakeDialect,
):
if positive:
return BinaryExpression(column, literal(regex),
custom_op("RLIKE"))
else:
return BinaryExpression(column, literal(regex),
custom_op("NOT RLIKE"))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# Bigquery
if hasattr(dialect, "BigQueryDialect"):
if positive:
return sa.func.REGEXP_CONTAINS(column, literal(regex))
else:
return sa.not_(sa.func.REGEXP_CONTAINS(column, literal(regex)))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
logger.debug(
"Unable to load BigQueryDialect dialect while running get_dialect_regex_expression in expectations.metrics.util",
exc_info=True,
)
pass
try:
# Dremio
if hasattr(dialect, "DremioDialect"):
if positive:
return sa.func.REGEXP_MATCHES(column, literal(regex))
else:
return sa.not_(sa.func.REGEXP_MATCHES(column, literal(regex)))
except (
AttributeError,
TypeError,
): # TypeError can occur if the driver was not installed and so is None
pass
try:
# Teradata
if issubclass(dialect.dialect,
teradatasqlalchemy.dialect.TeradataDialect):
if positive:
return sa.func.REGEXP_SIMILAR(column, literal(regex),
literal("i")) == 1
else:
return sa.func.REGEXP_SIMILAR(column, literal(regex),
literal("i")) == 0
except (AttributeError, TypeError):
pass
return None