Configuring 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

Cloud Build notifiers

Cloud Build sends all build event updates, along with build metadata, to Pub/Sub on the cloud-builds topic. Cloud Build notifiers can be configured to listen to that topic, filter the messages it receives, and send messages to your service.

Cloud Build notifiers are Docker images that can be run as containers on Cloud Run. When polled by a subscriber application, Cloud Build notifiers use pull subscriptions to deliver messages to the configured service. All notifiers use a common YAML spec for configuration, stored in Cloud Storage.

Cloud Build provides and maintains deployable notifier images in the cloud-build-notifiers repository. The following table lists available notifiers:

Notifier Description
bigquery writes build data to a BigQuery table
http sends a JSON payload to another HTTP endpoint
slack uses a Slack webhook to post messages to a Slack channel
smtp sends emails via an SMTP server

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:

  1. 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 permissions 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 and write access to Cloud Storage objects.

    6. Click Save.

  2. 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"
        delivery:
          table: projects/project-id/datasets/dataset-name/tables/table-name
    

    Where:

    • project-id is the ID of your 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 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

    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 beta builds triggers describe trigger-name

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

    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.

  3. 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.

      gsutil mb gs://bucket-name/
      
    2. Upload the notifier config file to your bucket:

      gsutil 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.
  4. 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.
    • 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 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.

  5. 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 Cloud project.
    • project-number is your Cloud project number.
  6. 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.

  7. 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 Cloud project.
  8. After enabling the Pub/Sub API, you should see the cloud-builds topic automatically created for you on Pub/Sub Topics page. If you do not see the cloud-builds topic, run the following command to manually create the topic to receive build update messages for your notifier:

    gcloud pubsub topics create cloud-builds
    
  9. 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 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:

  1. Open the BigQuery console page:

    Open the BigQuery page

  2. Under Resources, click on the project ID you use to configure your BigQuery notifier.

  3. Click on the your dataset name.

  4. 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:

  1. Open the BigQuery console page:

    Open the BigQuery page

  2. Under Resources, click on the table name you would like to query.

  3. Write your SQL query in the Query editor.

Example queries

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.

What's next