Console

Follow these steps to create a log-based metric that sends an alert whenmore than three materialized view refreshes fail in a 10-minute interval.

Create a log-based metric

1. To set up Logs Explorer, follow the instructions inView and analyze logs.

2. In Logs Explorer, confirm that theShow query setting is toggled on.

   When you use the Google Cloud console, the scope of theproject is the single project that is selected inthe Google Cloud console project picker. To learn how to addadditional projects, seeAdd projects to a metricsscope.

3. In theQuery pane, paste the following query to capture all failedautomatic materialized view refresh jobs in the current project'slogging scope:

   severity:"ERROR"protoPayload.metadata.jobChange.after:"DONE"protoPayload.metadata.jobChange.job.jobConfig.queryConfig.query=~"CALL BQ.REFRESH_MATERIALIZED_VIEW\('.*'\)"protoPayload.resourceName=~".*materialized_view_refresh_[\w]"

4. ClickRun query.

5. ClickActions, and then selectCreate metric.

6. To create an alert based on the number of errors, for themetric type selectCounter and enter aLog-based metricname andDescription for your metric. TheUnits field canbe left blank.

7. To define your metric filter in theFilter selection section,apply the following settings:

   • Use theSelect project or log bucket menu to choose whether themetric counts the log entries in your Google Cloud project or onlythose log entries in a specific log bucket.

   • Create a filter that collects only the log entries that you want tocount in your metric using thelogging query language.You can also use regular expressions to create your metric's filters.

   • To see which log entries match your filter, clickPreview logs.

8. ClickAdd label.

9. Enter a uniqueLabel name andDescription to help you identifyyour metric. LeaveLabel type asString, the default.

10. ForField name, enter the following string:

    protoPayload.metadata.jobChange.job.jobConfig.queryConfig.query

11. ForRegular expression, enter the following string:

    CALLBQ.REFRESH_MATERIALIZED_VIEW\('(.*)'\)

12. ClickDone and clickCreate metric.

For more information about counter metrics, seeConfigure counter metrics.

Create an alert

Complete the following steps to create an alert policy that specifies theconditions and sends an email when three materialized view refresh jobsfail within a ten-minute period. This option provides added flexibility whenconfiguring an alerting policy. If you create a logs-based metric directly, analert is sent each time a failed materialized view refresh error is present inthe logs.

1. In the Google Cloud console, go to theLog-based Metrics page.

   Go to the Log-based Metrics

2. Adjacent to your user-defined logs-based metric formaterialized view refreshes, clickmore_vertMore actions>Create alert from metric.

3. InSelect a metric, select the name of the metric you specifiedpreviously forLog-based metric name.

4. InAdd filters, add an additional filter to the alertbased on the naming convention of the materialized view defined in theRegular expression field.

   This step is useful if you need to define aseparate notification channel for multiple teams that use the sameproject but are logically divided by the materialized view namingconvention. For more information about alert criteria, seeFilter charted data in "Select metrics when using Metrics Explorer".

   

5. In theRolling window setting of theTransform data section,specify a value greater than 10 minutes to ensure that multiple logentries matching your filter are counted, and clickNext.

6. SpecifyThreshold value,3 for example, and optionally, configuretheAlert trigger andThreshold position fields. ClickNext.

7. Choose a notification channel for alerting.

8. ClickCreate policy.

When the number of failed materialized view refreshes exceeds your threshold,your notification channel is alerted.

Terraform

You can create a custom metric, alert policy, notification channel, andlogging scope by using Terraform. The following Terraform sample uses a queryto monitor and log each failed materialized view refresh job.

resource"google_logging_metric""failed_mv_refresh_metric"{project=var.project_idname=var.logging_metric_namefilter=trimspace(<<EOTseverity="ERROR"ANDprotoPayload.metadata.jobChange.after="DONE"ANDprotoPayload.metadata.jobChange.job.jobConfig.queryConfig.query=~"CALL BQ.REFRESH_MATERIALIZED_VIEW\('.*'\)"ANDprotoPayload.resourceName=~".*materialized_view_refresh_[\\w]"EOT)metric_descriptor{metric_kind="DELTA"value_type="INT64"unit="1"display_name="Failed Materialized View Refresh Count"labels{key="materialized_view_name"value_type="STRING"description="The name of the materialized view that failed to refresh."}}label_extractors={"materialized_view_name"="REGEXP_EXTRACT(protoPayload.metadata.jobChange.job.jobConfig.queryConfig.query, \"CALL BQ\\.REFRESH_MATERIALIZED_VIEW\\('(.*)'\\)\")"}}

The following sample creates an alert which can be used to send an email whenthe number of failed materialized view refresh jobs exceeds a threshold.

resource"google_monitoring_alert_policy""failed_mv_refresh_alert"{project=var.project_iddisplay_name=var.alert_policy_display_namecombiner="OR"conditions{display_name="Condition: Materialized View Refresh Failure Count Exceeds Threshold"condition_threshold{filter="metric.type=\"logging.googleapis.com/user/${google_logging_metric.failed_mv_refresh_metric.name}\" AND resource.type=\"bigquery_project\""duration="${var.alert_duration_seconds}s"comparison="COMPARISON_GT"threshold_value=var.alert_threshold_countaggregations{alignment_period="${var.alert_rolling_window_seconds}s"per_series_aligner="ALIGN_DELTA"cross_series_reducer="REDUCE_SUM"group_by_fields=[]}trigger{count=1}}}notification_channels=[google_monitoring_notification_channel.email_channel.id,]}

For additional examples, see the following:

• google_logging_metric
• google_monitoring_alert_policy
• google_monitoring_notification_channel
• google_logging_log_scope