# Get notified when an expensive BigQuery job executes using Eventarc and SendGrid

Author: [Mete Atamel](https://atamel.dev/) | Published: 2021-06-08 | Topics: Eventarc, Serverless, Google Cloud Platform, How To | Canonical: https://atamel.dev/posts/2021/06-08_bigquery-jobs-notifier/


## Events supported by Eventarc

Last week, I put together a [list of
events](https://github.com/GoogleCloudPlatform/eventarc-samples/tree/main/eventarc-events)
supported by Eventarc in our
[eventarc-samples](https://github.com/GoogleCloudPlatform/eventarc-samples)
repo. Thanks to our docs team, this list is now part of our official docs under
[reference
section](https://cloud.google.com/eventarc/docs/reference/supported-events).

After looking at the full list, I started thinking about some use cases enabled by
these events. I want to talk about one of those use cases today: **How to get
notified when an expensive BigQuery job executes?**

## Determine completed & expensive BigQuery jobs

The first question to answer is: How do you determine completed and expensive
BigQuery jobs? Thankfully, AuditLogs of BigQuery have all the information. Note
that AuditLogs are enabled by default in BigQuery, so you don't have to do
anything special to enable them.

To see completed BigQuery jobs, you can see AuditLogs logged under
`serviceName` of `bigquery.googleapis.com` and `methodName` of `jobservice.jobcompleted`:

![BigQuery AuditLog](/img/2021/bigquery-auditlog1.png)

To get more job details, you can check under `jobsCompletedEvent` where you have
access to what query was executed and more importantly how much it cost under
`totalBilledBytes`:

![BigQuery AuditLog](/img/2021/bigquery-auditlog2.png)

`totalBilledBytes` gives you an idea how expensive the query was. For example,
if the query was cached, this will be zero and cost nothing but for expensive
queries, it will be in GBs or more. In this sample, we'll assume queries 1GB or
more are expensive.

## Architecture

The architecture is pretty straightforward:

![BigQuery Jobs Notifier](/img/2021/bigquery-jobs-notifier.png)

1. A BigQuery user runs some BigQuery jobs. This generates some AuditLogs.
2. An Eventarc trigger filters these AuditLogs for `serviceName` of
   `bigquery.googleapis.com` and `methodName` of `jobservice.jobcompleted` and
   forwards to a Cloud Run service.
3. The Cloud Run service receives `jobcompleted` event, logs it and checks if
   `totalBilledBytes` is more than 1GB. If so, it uses SendGrid to send an email
   to a BigQuery admin.

## Before you begin

Before deploying services and triggers, go through some setup steps.

### Default Compute service account

Default compute service account will be used in the Audit Log triggers of Eventarc. Grant the
`eventarc.eventReceiver` role to the default compute service account:

```sh
export PROJECT_NUMBER="$(gcloud projects describe $(gcloud config get-value project) --format='value(projectNumber)')"

gcloud projects add-iam-policy-binding $(gcloud config get-value project) \
    --member=serviceAccount:${PROJECT_NUMBER}-compute@developer.gserviceaccount.com \
    --role='roles/eventarc.eventReceiver'
```

### Region, location, platform

Set region, location and platform for Cloud Run and Eventarc:

```sh
export REGION=us-central1
export GOOGLE_CLOUD_PROJECT=$(gcloud config get-value project)

gcloud config set run/platform managed
gcloud config set run/region ${REGION}
gcloud config set eventarc/location ${REGION}
```

## Notifier

This service will receive the BigQuery `jobcompleted` events, log the received
event and if `totalBilledBytes` is more than 1GB, it will use SendGrid to send an
email about the expensive query.

You need to setup a SendGrid account and create an API key. You can follow
SendGrid's API Keys [doc](https://app.sendgrid.com/settings/api_keys) for more
details on how to setup SendGrid.

The code of the service is in [app.py](https://github.com/GoogleCloudPlatform/eventarc-samples/blob/main/bigquery-jobs-notifier/app.py). You can take a look how to parse
the received CloudEvent, how to parse the AuditLog with the relevant info and
finally how to send an email for expensive queries.

Build and push the container image:

```sh
export SERVICE_NAME=bigquery-usage-notifier
export GOOGLE_CLOUD_PROJECT=$(gcloud config get-value project)
gcloud builds submit --tag gcr.io/${GOOGLE_CLOUD_PROJECT}/${SERVICE_NAME}
```

Deploy the service while passing in `TO_EMAILS` to email address where you want
to send the notification and `SENDGRID_API_KEY` with your send SendGrid API Key.

```sh
export TO_EMAILS=youremail@gmail.com
export SENDGRID_API_KEY=yoursendgridapikey

gcloud run deploy ${SERVICE_NAME} \
  --image gcr.io/${GOOGLE_CLOUD_PROJECT}/${SERVICE_NAME} \
  --allow-unauthenticated \
  --update-env-vars TO_EMAILS=${TO_EMAILS},SENDGRID_API_KEY=${SENDGRID_API_KEY}
```

## Trigger

Once the service is deployed, create a trigger to filter for the right BigQuery
events:

```sh
gcloud eventarc triggers create ${SERVICE_NAME}-trigger \
  --destination-run-service=${SERVICE_NAME} \
  --destination-run-region=${REGION} \
  --event-filters="type=google.cloud.audit.log.v1.written" \
  --event-filters="serviceName=bigquery.googleapis.com" \
  --event-filters="methodName=jobservice.jobcompleted" \
  --service-account=${PROJECT_NUMBER}-compute@developer.gserviceaccount.com
```

Before testing, make sure the trigger is ready:

```sh
gcloud eventarc triggers list

NAME                                ACTIVE
bigquery-usage-notifier-trigger      Yes
```

## Test

To test, you need to run a BigQuery job that results in 1GB or more billed
bytes. Here's a sample query to run using `bq` CLI:

```sh
bq query \
  --nouse_legacy_sql \
  --nouse_cache \
  'SELECT * FROM `bigquery-samples`.reddit.full'
```

Once the query completes, you should see the Cloud Run service log the query and
you should also receive an email like this one:

```
The following BigQuery job completed

principalEmail: atameldev@gmail.com
jobId: bqjob_r3293aa18ce3b8bed_00000179e689b8b9_1
createTime: 2021-06-07T12:54:16.783Z
query: SELECT * FROM `bigquery-samples`.reddit.full
totalBilledBytes: 1450180608.0, above 1GB? True
```

---

If you want to try this out, all the code is in our
[eventarc-samples](https://github.com/GoogleCloudPlatform/eventarc-samples/tree/main/bigquery-jobs-notifier)
repo. Feel free to reach out to me on Twitter
[@meteatamel](https://twitter.com/meteatamel)
for any questions/feedback.