def run_alert_query(alert: Alert) -> Optional[bool]:
"""
Execute alert.sql and return value if any rows are returned
"""
logger.info("Processing alert ID: %i", alert.id)
database = alert.database
if not database:
logger.error("Alert database not preset")
return None
if not alert.sql:
logger.error("Alert SQL not preset")
return None
parsed_query = ParsedQuery(alert.sql)
sql = parsed_query.stripped()
state = None
dttm_start = datetime.utcnow()
df = pd.DataFrame()
try:
logger.info("Evaluating SQL for alert %s", alert)
df = database.get_df(sql)
except Exception as exc: # pylint: disable=broad-except
state = AlertState.ERROR
logging.exception(exc)
logging.error("Failed at evaluating alert: %s (%s)", alert.label,
alert.id)
dttm_end = datetime.utcnow()
if state != AlertState.ERROR:
alert.last_eval_dttm = datetime.utcnow()
if not df.empty:
# Looking for truthy cells
for row in df.to_records():
if any(row):
state = AlertState.TRIGGER
deliver_alert(alert)
break
if not state:
state = AlertState.PASS
alert.last_state = state
alert.logs.append(
AlertLog(
scheduled_dttm=dttm_start,
dttm_start=dttm_start,
dttm_end=dttm_end,
state=state,
))
db.session.commit()
return None
def create_alert(
dbsession,
sql: str,
validator_type: Optional[str] = None,
validator_config: Optional[str] = None,
) -> Alert:
alert = Alert(
label="test_alert",
active=True,
crontab="* * * * *",
slice_id=dbsession.query(Slice).all()[0].id,
recipients="*****@*****.**",
slack_channel="#test_channel",
)
dbsession.add(alert)
dbsession.commit()
sql_observer = SQLObserver(
sql=sql,
alert_id=alert.id,
database_id=utils.get_example_database().id,
)
if validator_type and validator_config:
validator = Validator(
validator_type=validator_type,
config=validator_config,
alert_id=alert.id,
)
dbsession.add(validator)
dbsession.add(sql_observer)
dbsession.commit()
return alert
def setup_database():
with app.app_context():
slice_id = db.session.query(Slice).all()[0].id
database_id = utils.get_example_database().id
alerts = [
Alert(
id=1,
label="alert_1",
active=True,
crontab="*/1 * * * *",
sql="SELECT 0",
alert_type="email",
slice_id=slice_id,
database_id=database_id,
),
Alert(
id=2,
label="alert_2",
active=True,
crontab="*/1 * * * *",
sql="SELECT 55",
alert_type="email",
slice_id=slice_id,
database_id=database_id,
),
Alert(
id=3,
label="alert_3",
active=False,
crontab="*/1 * * * *",
sql="UPDATE 55",
alert_type="email",
slice_id=slice_id,
database_id=database_id,
),
Alert(id=4, active=False, label="alert_4", database_id=-1),
Alert(id=5, active=False, label="alert_5",
database_id=database_id),
]
db.session.bulk_save_objects(alerts)
db.session.commit()
yield db.session
db.session.query(AlertLog).delete()
db.session.query(Alert).delete()
def setup_database():
with app.app_context():
slice_id = db.session.query(Slice).all()[0].id
database_id = utils.get_example_database().id
alert1 = Alert(
id=1,
label="alert_1",
active=True,
crontab="*/1 * * * *",
sql="SELECT 0",
alert_type="email",
slice_id=slice_id,
database_id=database_id,
)
alert2 = Alert(
id=2,
label="alert_2",
active=True,
crontab="*/1 * * * *",
sql="SELECT 55",
alert_type="email",
slice_id=slice_id,
database_id=database_id,
)
alert3 = Alert(
id=3,
label="alert_3",
active=False,
crontab="*/1 * * * *",
sql="UPDATE 55",
alert_type="email",
slice_id=slice_id,
database_id=database_id,
)
alert4 = Alert(id=4, active=False, label="alert_4", database_id=-1)
alert5 = Alert(id=5, active=False, label="alert_5", database_id=database_id)
for num in range(1, 6):
eval(f"db.session.add(alert{num})")
db.session.commit()
yield db.session
db.session.query(AlertLog).delete()
db.session.query(Alert).delete()
def operator_validator(alert: Alert, validator_config: str) -> bool:
"""
Returns True if a recent observation is greater than or equal to
the value given in the validator config
"""
observation = alert.get_last_observation()
if not observation or observation.value in (None, np.nan):
return False
operator = json.loads(validator_config)["op"]
threshold = json.loads(validator_config)["threshold"]
return OPERATOR_FUNCTIONS[operator](observation.value, threshold)
def not_null_validator(
alert: Alert,
validator_config: str # pylint: disable=unused-argument
) -> bool:
"""Returns True if a recent observation is not NULL"""
observation = alert.get_last_observation()
# TODO: Validate malformed observations/observations with errors separately
if (not observation or observation.error_msg
or observation.value in (0, None, np.nan)):
return False
return True
def setup_database():
with app.app_context():
example_database = utils.get_example_database()
example_database.get_sqla_engine().execute(
"CREATE TABLE test_table AS SELECT 1 as first, 2 as second")
example_database.get_sqla_engine().execute(
"INSERT INTO test_table (first, second) VALUES (3, 4)")
no_observer_alert = Alert(crontab="* * * * *", label="No Observer")
db.session.add(no_observer_alert)
db.session.commit()
yield db.session
db.session.query(SQLObservation).delete()
db.session.query(SQLObserver).delete()
db.session.query(Validator).delete()
db.session.query(AlertLog).delete()
db.session.query(Alert).delete()
def create_alert(
db_session: Session,
sql: str,
validator_type: AlertValidatorType = AlertValidatorType.OPERATOR,
validator_config: str = "",
) -> Alert:
db_session.commit()
alert = Alert(
label="test_alert",
active=True,
crontab="* * * * *",
slice_id=db_session.query(Slice).all()[0].id,
recipients="*****@*****.**",
slack_channel="#test_channel",
sql=sql,
database_id=utils.get_example_database().id,
validator_type=validator_type,
validator_config=validator_config,
)
db_session.add(alert)
db_session.commit()
return alert
