Prevent Data Loss: Set Up BigQuery Error Notifications

BigQuery has gained significant popularity and is increasingly utilized in numerous projects. Google’s cloud-based data warehouse solution is known for its scalability, speed, and ability to handle large datasets efficiently. As more organizations integrate BigQuery into their data processing and analytics workflows, robust monitoring and alerting mechanisms are essential.

Implementing alerts for jobs that encounter errors is crucial to maintaining a reliable data pipeline. Errors in BigQuery can arise from various sources, including scheduled queries that fail to execute correctly, issues during data load operations, and daily exports from Google Analytics 4 (GA4), among others. If left unchecked, these errors can disrupt data processing activities and lead to inaccuracies in data analysis.

By setting up effective alerting systems, teams can promptly identify and address issues, ensuring the smooth functioning of their data pipelines. Since BigQuery does not provide native error alerting, one possible solution is to use Log-based alerting. Keep reading to find a script that helps set up Big Query error notifications.

Table of Contents:

1. Exploring the Logs
2. Alert Creation
3. Semi-automated Alarm Installation 

Exploring the Logs

Start from exploring the logs. To explore them for the project navigate to https://console.cloud.google.com/logs/query. Here, you can find all logged interactions across services in the project*. Depending on your needs, you can write a query to explore log records. For example, the query below will select all records for BigQuery service with a severity of more or equal NOTICE*.

resource.type=”bigquery_resource” — used to include only BigQuery related logs severity>=NOTICE — used to select severity

Side notes:
1. AND between clauses may be omitted.
2. Quotes also may be omitted.
3. *EMERGENCY > ALERT > CRITICAL > ERROR > WARNING > NOTICE > INFO > DEBUG > DEFAULT

However, dry-runs* errors are also presented in the list, and to avoid viewing these records, we should add some extra conditions to exclude them. Otherwise, we will receive a notification for these errors as well. *Dry runs don’t use query slots, and you are not charged for performing a dry run.

Also, it’s better to exclude jobs that ran manually from the BigQuery console, as we know that errors and notifications will be redundant. Let’s add some clauses to do that:

– – include only Errors for BigQuery service
severity=ERROR
AND resource.type=”bigquery_resource”

– – New clauses:
– – exclude jobs which contains “bqux” as these jobs are run from the interface
AND NOT protoPayload.serviceData.jobCompletedEvent.job.jobName.jobId:”bqux”
– – include only completed jobs
AND protoPayload.methodName=”jobservice.jobcompleted”

After running that query you may see the records that met our conditions. If we are good with the output we receive, we may create notifications based on these error records. So, let’s create an alert based on them.

Alert Creation

Then let’s see how you can create an alarm in Big Query.

• Enter your query and then click the “Create alert” button.

• Enter your Policy Name and Severity Level. Optionally, you can write a piece of Documentation about your policy.

• Ensure that the query you have written is correct. To do that, click the “Preview logs” button and review the logs.

In that section, you can also create log-based labels, allowing you to extract parts of logs and then provide them as separate fields in the alert.

• Configure how often you will receive alerts between incidents and when the incident will be auto-closed.

• The final and most important step in setting up our notifications is to choose the channels to be notified about our incidents.

If you have not configured notification channels, click Manage Notification Channels and configure the required channels there, such as Google Chat, Email, or Slack.

After configuring and choosing channels, click save to save your policy. When an error occurs, you will be notified about the incident.

Semi-automated Alarm Installation

To simplify the above steps, our expert, Rustam Faskhutdinov, created a script that allows setting the alerting up within a few minutes. Just follow these 6 steps.

First of all, to use it, you should activate Cloud Shell:

To execute a script that will configure the notification policy run the following command in a shell:

pip3 install -r <(curl -s https://raw.githubusercontent.com/RustamFaskhutdinov/bigquery_error_alerting/main/requirements.txt) && python3 <(curl -s https://raw.githubusercontent.com/RustamFaskhutdinov/bigquery_error_alerting/main/create_bq_alert_policy.py)

Step 1

Select the mode currently only initialize is available. So enter 0. 

Step 2

Then, we need to select a project from the list by entering the project number or the full ID of the project.

Step 3

After choosing a project, the script will check if the policy with the name BigQuery Error Alert has already been created. If not, you need to select what notification channels you would like to use. Currently, two types of channels are available for setting up from the script: email and Google Space and also you may choose both (recommended).

Step 4

We choose to configure both Email and Google Space.

First, we need to type email addresses that will be used as Notification channels. Type the required email address (one per line) and an empty string to finish your input. Invalid email addresses will be ignored.

Then, we need to enter the Space name (only for the Notification channel display name) and Space ID* (how to create a space and find the ID will be shown below).

After that, you will see the notification channel brief. What channels already exist, and what will be created? To continue the creation process, please click Enter.

If Space does not exist, you will see the following error. Click enter to continue (only email channels will be used).

Step 5

Then, you should include or exclude jobs created by Looker Studio.

Step 6

The last step is Notification channel creation.

If the channel was created successfully, you will see the appropriate message and the link to the policy details.

If you need help optimizing this powerful tool, do not hesitate to contact VIDEN experts and receive support tailored to your business needs.