def send_email_alert(sendgrid_api_key, sender, query, job_id, project,
location, user_email, cc_list, total_cost,
giga_bytes_billed):
tab = " "
total_cost_trun = str(truncate(total_cost, 2))
giga_bytes_billed_trun = str(truncate(giga_bytes_billed, 2))
email_body = "Hey, <br> The following query has processed large amount of data:" \
+ "<br><strong>" + query + "</strong>" \
+ "<br>Job ID <strong>" + job_id + "</strong>" \
+ tab + "Query User <strong>" + user_email + "</strong>" \
+ "<br>" + "Gigabytes Billed <strong>" + giga_bytes_billed_trun + "</strong>" \
+ tab + "Query Cost <strong>$" + total_cost_trun + "</strong> " \
+ "<br>Project <strong>" + project + "</strong>" \
+ tab + "Location <strong>" + location + "</strong>"
message = Mail(from_email=sender,
to_emails=user_email,
subject='BigQuery job crossed threshold',
html_content=email_body)
if user_email in cc_list:
cc_list.remove(user_email)
for cc_email in cc_list:
message.personalizations[0].add_cc(Email(cc_email))
try:
sg = SendGridAPIClient(sendgrid_api_key)
sg.send(message)
except Exception as e:
utils.print_and_flush("Failed to send email alert. \n")
utils.print_and_flush(e)
def send_slack_alert(wekbook_url, web_api_token, dest_channel, query, job_id,
project, location, user_email, cost, gigabytes_billed):
try:
message_blocks = [{
"type": "section",
"text": {
"type": "mrkdwn",
"text":
"The following query has processed large amount of data:"
}
}, {
"type": "section",
"text": {
"type": "mrkdwn",
"text": "Query Syntax ```" + str(query) + "```"
}
}, {
"type":
"section",
"fields": [{
"type": "mrkdwn",
"text": "Job ID *" + str(job_id) + "*"
}, {
"type": "mrkdwn",
"text": "Query User *" + str(user_email) + "*"
}, {
"type":
"mrkdwn",
"text":
"Gigabytes Billed *" + str(truncate(gigabytes_billed, 2)) + "*"
}, {
"type": "mrkdwn",
"text": "Query Cost *$" + str(truncate(cost, 2)) + "*"
}, {
"type": "mrkdwn",
"text": "Project *" + str(project) + "*"
}, {
"type": "mrkdwn",
"text": "Location *" + str(location) + "*"
}]
}]
if wekbook_url:
send_slack_alert_webhook(wekbook_url, dest_channel, message_blocks)
else:
utils.print_and_flush("Webbook_URL not defined")
if web_api_token:
send_slack_alert_web_api(web_api_token, dest_channel,
message_blocks, user_email)
else:
utils.print_and_flush("Web API not defined")
except Exception as e:
utils.print_and_flush("Failed to send slack alert. \n")
utils.print_and_flush(e)
def send_slack_alert_web_api(web_api_token, dest_channel, message_blocks,
user_email):
utils.print_and_flush("Sending slack web api alert")
client = slack.WebClient(web_api_token)
try:
client.chat_postMessage(channel=dest_channel, blocks=message_blocks)
except Exception as e:
utils.print_and_flush("Failed to send slack alert to channel: " +
dest_channel)
utils.print_and_flush(e)
try:
slack_user = client.users_lookupByEmail(email=user_email)
if slack_user:
user_id = slack_user.data.get("user").get("id")
client.chat_postMessage(channel=user_id, blocks=message_blocks)
except Exception as e:
utils.print_and_flush("Failed to send slack alert to user: " +
user_email)
utils.print_and_flush(e)
def send_slack_alert_webhook(wekbook_url, dest_channel, blocks):
utils.print_and_flush("Sending slack webhook alert")
try:
data = {"blocks": blocks, "channel": dest_channel}
requests.post(wekbook_url,
data=json.dumps(data),
headers={'Content-Type': 'application/json'})
except Exception as e:
utils.print_and_flush("Failed to send slack alert. \n")
utils.print_and_flush(e)
if __name__ == '__main__':
model, df_stats_min, df_stats_max, X_train_columns, qualitatives, quantitatives, index, time_field, predict_field, feature_field, time_step = load_model_Omar(
)
fields_to_extract = [*feature_field, time_field, predict_field, '_id']
df_index = load(index, SCROLL_CHUNK_SIZE, fields_to_extract, time_field,
ES_HOST, ES_PORT)
df_index = df_index[feature_field + ['_id']]
list_of_updated_id = []
cpt = 0
for idx_start in df_index.index:
print_and_flush('predicting ' + str(cpt) + '/' + str(len(df_index)))
cpt += 1
time_start = np.datetime64(idx_start)
time_stop = time_start + np.timedelta64(time_step, 'm')
df_to_predict = df_index.loc[time_start:time_stop]
if df_to_predict.shape[0] > 1:
last_index = df_to_predict.index[df_to_predict.shape[0] - 1]
try:
# Normal case: no duplicated IDs
last_index_id = df_index.loc[last_index].loc['_id']
except:
# If the ID is duplicated, we take the first one
last_index_id = df_index.loc[last_index].iloc[0].loc['_id']
def print_resample_progress(n_progress, n):
n_progress += 1
print_and_flush('resample ' + str(float(n_progress)) + '/' + str(n))
return n_progress
def bq_snitch():
utils.print_and_flush("BQ-snitch triggered")
job_id = request.get_json()["protoPayload"]["serviceData"][
"jobCompletedEvent"]["job"]["jobName"]["jobId"]
alert_threshold = int(os.environ.get("ALERT_THRESHOLD"))
tera_bytes_cost = int(os.environ.get("TB_COST"))
client = bigquery.Client()
job = client.get_job(job_id)
if not hasattr(job, 'total_bytes_billed'):
return
project = job.project
location = job.location
bytes_per_tera_bytes = 2**40
total_tera_bytes_billed = job.total_bytes_billed / bytes_per_tera_bytes
total_cost = total_tera_bytes_billed * tera_bytes_cost
utils.print_and_flush("Total cost: " + str(total_cost))
if total_cost >= alert_threshold:
utils.print_and_flush("Job violated cost threshold limit: " +
str(alert_threshold) + "$")
giga_bytes_billed = total_tera_bytes_billed * 1024
slack_alert = str_to_bool(os.environ.get("SLACK_ALERT"))
if slack_alert:
utils.print_and_flush("Sending slack alert")
webhook_url = os.environ.get("SLACK_WEBHOOK_URL")
web_api_token = os.environ.get("SLACK_WEB_API_TOKEN")
dest_channel = os.environ.get("SLACK_WEB_API_DESTINATION_CHANNEL")
alert_channels.send_slack_alert(webhook_url, web_api_token,
dest_channel, job.query, job_id,
project, location, job.user_email,
total_cost, giga_bytes_billed)
email_alert = str_to_bool(os.environ.get("EMAIL_ALERT"))
if email_alert:
utils.print_and_flush("Sending email alert")
sender = os.environ.get("EMAIL_SENDER")
cc_list = os.environ.get("EMAIL_RECIPIENTS", "").split(",")
sendgrid_api_key = os.environ.get("SENDGRID_API_KEY")
alert_channels.send_email_alert(sendgrid_api_key, sender,
job.query, job_id, project,
location, job.user_email, cc_list,
total_cost, giga_bytes_billed)
external_handler = str_to_bool(os.environ.get("EXTERNAL_HANDLER"))
if external_handler:
external_handler_url = os.environ.get("EXTERNAL_HANDLER_URL")
external_handler_gcp_service = str_to_bool(
os.environ.get("EXTERNAL_HANDLER_GCP_SERVICE"))
utils.print_and_flush("Sending request to external handler" +
external_handler_url)
body = construct_post_body(job.query, job_id, project, location,
job.user_email, total_cost,
giga_bytes_billed)
json_data = json.dumps(body)
response_status = send_http(external_handler_url, json_data,
external_handler_gcp_service)
utils.print_and_flush("External handler response status code: " +
str(response_status))
else:
utils.print_and_flush("Job didn't violate cost threshold limit")
return "BQ-Snitch Finished"