CODEWITHSUNDEEP
google-bigquery
dshack
dshack

Reputation: 273

How can I trigger an email or other notification based on a BigQuery query?

I would like to receive a notification, ideally via email, when some threshold is met in Google BigQuery. For example, if the query is:

SELECT name, count(id) FROM terrible_things
WHERE date(terrible_thing) < -1d

Then I would want to get an alert when there were greater than 0 results, and I would want that alert to contain the name of each object and how many there were.

Upvotes: 10

Views: 16525

Answers (2)

MonicaPC
MonicaPC

Reputation: 374

You can create a daily BigQuery Scheduled query that when it fails the user that created it will get an email.

  1. You'll need to create a dataset in advance (e.g. notifications).
  2. Run this query in the BigQuery Studio
CREATE TABLE IF NOT EXISTS notifications.alerts AS
(SELECT name, count(id) FROM terrible_things
WHERE date(terrible_thing) < DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) );
SELECT IF((SELECT COUNT(*) FROM notifications.alerts WHERE date(terrible_thing) < DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) = 0,
     'All good',
     ERROR('There are terrible_things'))
  1. Select SCHEDULE --> Create New Scheduled query Fill in Name, Repeat frequency - daily, At hour: 12:00 UTC and Notification options --> select Send email notifications

You get an email only if the query produced results, however you'll need to check your table notifications.alerts to see the objects.

Upvotes: 1

shollyman
shollyman

Reputation: 4384

BigQuery does not provide the kinds of services you'd need to build this without involving other technologies. However, you should be able to use something like appengine (which does have a task scheduling mechanism) to periodically issue your monitoring query probe, check the results of the job, and alert if there are nonzero rows in the results. Alternately, you could do this locally using some scripting and leveraging the BQ command line tool.

You could also refine things by using BQ's table decorators to only scan the data that's arrived since you last ran your monitoring query, if you retain knowledge of the last probe's execution in the calling system.

In short: Something else needs to issue the queries and react based on the outcome, but BQ can certainly evaluate the data.

Upvotes: 10

Related Questions