Configure BigQuery notifications

Cloud Build can notify you of build updates by sending you notifications to desired channels, such as Slack or your SMTP server. This page explains how to configure notifications using the BigQuery notifier.

The BigQuery notifier provides functionality for you to specify filters on which builds you want to store in your database. For example, you can group builds by trigger ID, tags, or substitution values. The BigQuery notifier also writes data to BigQuery in a standardized format that includes computed fields not immediately accessible on the Build object, such as image size or execution duration. If you want to learn how to export log entries to BigQuery or another destination, see Exporting logs with the Google Cloud console.

Before you begin

Enable the Cloud Build, Cloud Run, Pub/Sub, and BigQuery APIs.
Enable the APIs

Install the Google Cloud CLI.

Configuring BigQuery notifications

The following section explains how you can manually configure HTTP notifications using the BigQuery notifier. If you would like to automate the configuration instead, see Automating configuration for notifications.

To configure BigQuery notifications:

Give your Cloud Run service account permission to create and write BigQuery tables, permission to fetch Artifact Registry data related to your build, and read and write access to Cloud Storage buckets:
1. Go to the IAM page in the Google Cloud console:
  
  Open the IAM page
2. Locate the Compute Engine default service account associated with with your project:
  
  Your Compute Engine default service account will look similar to the following:
```
project-number-compute@developer.gserviceaccount.com
```
3. Click on the pencil icon in the row containing your Compute Engine default service account. You will see the Edit access tab.
4. Click Add another role.
5. Add the following roles:
  - Artifact Registry Reader
  - BigQuery Data Editor
  - Storage Object Viewer
  The Artifact Registry Reader role enables you to fetch data for your images. The BigQuery Data Editor gives you read and write access to your data. The Storage Object Viewer gives you read access to Cloud Storage objects.
  
  Note: If you want to grant permissions at the bucket level instead of the project level, add the Storage Legacy Object Owner role to your bucket when you create one. To learn more, see Project-level roles vs. bucket-level roles.
6. Click Save.
Write a notifier configuration file to configure your BigQuery notifier and filter on build events:

In the following example notifier config file, the filter field uses Common Expression Language with the variable, build, to filter build events with a specified trigger ID:
```
apiVersion: cloud-build-notifiers/v1
kind: BigQueryNotifier
metadata:
  name: example-bigquery-notifier
spec:
  notification:
    filter: build.build_trigger_id == "123e4567-e89b-12d3-a456-426614174000"
    params:
      buildStatus: $(build.status)
    delivery:
      table: projects/project-id/datasets/dataset-name/tables/table-name
    template:
      type: golang
      uri: gs://example-gcs-bucket/bq.json
```
Where:
- buildStatus is a user-defined parameter. This parameter takes on the value of ${build.status}, the status of the build.
- project-id is the ID of your Google Cloud project.
- dataset-name is the name you want to give your dataset.
- table-name is the name you want to give your table.
- The uri field references the bq.json file. This file refers to a JSON template hosted on Cloud Storage and represents the information to insert into your bigquery table.
  
  Experimental — Customizing notifications using templates
  
  This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.
To view an example of a template file, see the bq.json file in the cloud-build-notifiers-repository.

The table-name in your notifier config file can refer to:
- a nonexistent table
- an empty table without a schema
- an existing table with a schema that matches the schema specifications in the BigQuery notifier
  
  Note: References to an existing table with a schema specification not matching the BigQuery notifier will result in an error.
We recommend you specify the build trigger ID as your filter since specifying the build trigger ID enables you to correlate build data for your triggers. You can also specify multiple trigger IDs in a list: build.build_trigger_id in ["example-id-123", "example-id-456"].

To obtain your trigger ID, run the following command, where trigger-name is the name of your trigger:

gcloud builds triggers describe trigger-name

The command will list fields associated with your trigger, including your trigger ID.

Note: Data is only stored for build events with a terminal status such as SUCCESS or FAILURE.

To view the example, see the notifier config file for the BigQuery notifier.

For additional fields you can filter by, see the Build resource. For additional filtering examples, see the Using CEL to filter build events.
Upload your notifier configuration file to a Cloud Storage bucket:
1. If you do not have a Cloud Storage bucket, run the following command to create a bucket, where bucket-name is the name you want to give your bucket, subject to naming requirements.
```
gcloud storage buckets create gs://bucket-name/
```
2. Upload the notifier config file to your bucket:
```
gcloud storage cp config-file-name gs://bucket-name/config-file-name
```
  Where:
  - bucket-name is the name of your bucket.
  - config-file-name is the name of your notifier config file.
Deploy your notifier to Cloud Run:
```
 gcloud run deploy service-name \
   --image=us-east1-docker.pkg.dev/gcb-release/cloud-build-notifiers/bigquery:latest \
   --no-allow-unauthenticated \
   --update-env-vars=CONFIG_PATH=config-path,PROJECT_ID=project-id
```
Where:
- service-name is the name of the Cloud Run service to which you're deploying the image.
  
  Note: service-name references the new notifier service you are creating.
- config-path is the path to the configuration file for your BigQuery notifier, gs://bucket-name/config-file-name.
- project-id is the ID of your Google Cloud project.
The gcloud run deploy command pulls the latest version of your built image from Artifact Registry. Cloud Build supports notifier images for nine months. After nine months, Cloud Build deletes the image version. If you would like to use a prior image version, you will need to specify the full semantic version of the image tag in the image attribute of your gcloud run deploy command. Previous image versions and tags can be found in Artifact Registry.

Note: While running this command, you may be prompted to specify additional arguments such as --region or --platform. To learn more about parameters you can pass to the gcloud run deploy command, see Deploying to Cloud Run. If you are unable to deploy your notifier using this command, see the Cloud Run troubleshooting guide for solutions on how to resolve Deployment errors.
Grant Pub/Sub permissions to create authentication tokens in your project:
```
 gcloud projects add-iam-policy-binding project-id \
   --member=serviceAccount:service-project-number@gcp-sa-pubsub.iam.gserviceaccount.com \
   --role=roles/iam.serviceAccountTokenCreator
```
Where:
- project-id is the ID of your Google Cloud project.
- project-number is your Google Cloud project number.
Note: If the Pub/Sub service account does not exist on your project, see Create and use subscriptions.
Create a service account to represent your Pub/Sub subscription identity:
```
gcloud iam service-accounts create cloud-run-pubsub-invoker \
  --display-name "Cloud Run Pub/Sub Invoker"
```
You can use cloud-run-pubsub-invoker or use a name unique within your Google Cloud project.
Give the cloud-run-pubsub-invoker service account the Cloud Run Invoker permission:
```
gcloud run services add-iam-policy-binding service-name \
   --member=serviceAccount:cloud-run-pubsub-invoker@project-id.iam.gserviceaccount.com \
   --role=roles/run.invoker
```
Where:
- service-name is the name of the Cloud Run service to which you're deploying the image.
- project-id is the ID of your Google Cloud project.
Create the cloud-builds topic to receive build update messages for your notifier:
```
gcloud pubsub topics create cloud-builds
```
Create a Pub/Sub push subscriber for your notifier:
```
 gcloud pubsub subscriptions create subscriber-id \
   --topic=cloud-builds \
   --push-endpoint=service-url \
   --push-auth-service-account=cloud-run-pubsub-invoker@project-id.iam.gserviceaccount.com
```
Where:
- subscriber-id is the name you want to give your subscription.
- service-url is the Cloud Run-generated URL for your new service.
- project-id is the ID of your Google Cloud project.

Notifications for your Cloud Build project are now set up.

The next time you invoke a build, your table will be updated with the latest data that matches the filter you've configured for your BigQuery notifier.

Viewing build data

To view your build data in BigQuery:

Open the BigQuery console page:

Open the BigQuery page
Under Resources, click on the project ID you use to configure your BigQuery notifier.
Click on the your dataset name.
Click on your table name.

You can now see information related to your table including its schema and a preview of your build data as listed in the table.

Accessing build data

You can query data in your table using the bq command line tool or the BigQuery console.

CLI

To query data in your table using the bq command-line tool, run the following command in your terminal where sql-query is your query:

bq query sql-query

If you plan to use the query examples on this page, make sure you specify the --nouse_legacy_sql flag in your command since. The bq command-line tool uses Legacy SQL whereas the example queries do not. Run the following command in your terminal to query data without Legacy SQL:

bq query sql-query --nouse_legacy_sql

Console

To query data in your table using the BigQuery console:

Open the BigQuery console page:

Open the BigQuery page
Under Resources, click on the table name you would like to query.
Write your SQL query in the Query editor.

Using queries to access build data

The following example queries show how you can access build data for your build event, following configuration of the BigQuery notifier:

Overall build history

SELECT * FROM `projectID.datasetName.tableName`

Build counts grouped by status

SELECT STATUS, COUNT(*)
FROM `projectID.datasetName.tableName`
GROUP BY STATUS

Daily deployment frequency for the current week

SELECT DAY, COUNT(STATUS) AS Deployments
FROM (SELECT DATETIME_TRUNC(CreateTime, WEEK) AS WEEK,
      DATETIME_TRUNC(CreateTime, DAY) AS DAY,
      STATUS
      FROM `projectID.datasetName.tableName`
      WHERE STATUS="SUCCESS")
WHERE WEEK = DATETIME_TRUNC(CURRENT_DATETIME(), WEEK)
GROUP BY DAY

To see more example queries, see the Cloud Build BigQuery Notifier README in the cloud-build-notifiers repository on GitHub. To learn more about how to query data using BigQuery, see Querying and viewing data.

Using CEL to filter build events

Cloud Build uses CEL with the variable, build, on fields listed in the Build resource to access fields associated with your build event such as your trigger ID, image list, or substitution values. You can use the filter string to filter build events in your build config file using any field listed in the Build resource. To find the exact syntax associated with your field, see the cloudbuild.proto file.

Filtering by trigger ID

To filter by trigger ID, specify the value of your trigger ID in the filter field using build.build_trigger_id, where trigger-id is your trigger ID as a string:

filter: build.build_trigger_id == trigger-id

Filtering by status

To filter by status, specify the build status you want to filter on in the filter field using build.status.

The following example shows how to filter build events with a SUCCESS status using the filter field:

filter: build.status == Build.Status.SUCCESS

You can also filter builds with varying statuses. The following example shows how to filter build events that have a SUCCESS, FAILURE, or TIMEOUT status using the filter field:

filter: build.status in [Build.Status.SUCCESS, Build.Status.FAILURE, Build.Status.TIMEOUT]

To see additional status values you can filter by, see Status under the Build resource reference.

Filtering by tag

To filter by tag, specify the value of your tag in the filter field using build.tags, where tag-name is the name of your tag:

filter: tag-name in build.tags

You can filter based on the number of tags specified in your build event using size. In the following example, the filter field filters build events that have exactly two tags specified with one tag specified as v1:

filter: size(build.tags) == 2 && "v1" in build.tags

Filtering by images

To filter by images, specify the value of your image in the filter field using build.images, where image-name is the full name of your image as listed in Artifact Registry such as us-east1-docker.pkg.dev/my-project/docker-repo/image-one:

filter: image-name in build.images

In the following example, the filter filters on build events that have either us-east1-docker.pkg.dev/my-project/docker-repo/image-one or us-east1-docker.pkg.dev/my-project/docker-repo/image-two specified as image names:

filter: "us-east1-docker.pkg.dev/my-project/docker-repo/image-one" in build.images || "us-east1-docker.pkg.dev/my-project/docker-repo/image-one" in build.images

Filtering by time

You can filter build events based on a build's create time, start time, or finish time by specifying one of the following options in your filter field: build.create_time, build.start_time, or build.finish_time.

In the following example, the filter field uses timestamp to filter build events with a request time to create the build at July 20, 2020 at 6:00 AM:

filter: build.create_time == timestamp("2020-07-20:T06:00:00Z")

You can also filter on build events by time comparisons. In the following example, the filter field uses timestamp to filter build events with a start time between July 20, 2020 at 6:00 AM and July 30, 2020 at 6:00 AM.

filter: timestamp("2020-07-20:T06:00:00Z") >= build.start_time && build.start_time <= timestamp("2020-07-30:T06:00:00Z")

To learn more about how timezones are expressed in CEL, see the language definition for timezones.

To filter by duration of a build, you can use duration to compare timestamps. In the following example, the filter field uses duration to filter build events with a builds that run for at least five minutes:

filter: build.finish_time - build.start_time >= duration("5m")

Filtering by substitution

You can filter by substitution by specifying the substitution variable in the filter field using build.substitutions. In the following example, the filter field lists builds that contain the substitution variable substitution-variable and checks if the substitution-variable matches the specified substitution-value:

filter: build.substitutions[substitution-variable] == substitution-value

Where:

substitution-variable is the name of your substitution variable.
substitution-value is the name of your substitution value.

You can also filter by default substitution variable values. In the following example, the filter field lists builds that have the branch name master and builds that have the repository name github.com/user/my-example-repo. The default substitution variables BRANCH_NAME and REPO_NAME are passed in as keys to the build.substitutions:

filter: build.substitutions["BRANCH_NAME"] == "master" && build.substitutions["REPO_NAME"] == "github.com/user/my-example-repo"

If you want to filter on strings using regular expressions, you can use the built-in matches function. In the example below, the filter field filters for builds with a status of FAILURE or TIMEOUT and that also have a build substitution variable TAG_NAME with a value that matches the regular expression v{DIGIT}.{DIGIT}.{3 DIGITS}).

filter: build.status in [Build.Status.FAILURE, Build.Status.TIMEOUT] && build.substitutions["TAG_NAME"].matches("^v\\d{1}\\.\\d{1}\\.\\d{3}$")

To see a list of default substitution values, see Using default substitutions.

What's next

Learn about Cloud Build notifiers.
Learn how to subscribe to build notifications.
Learn how to write a Cloud Build build configuration file.