Search code examples
google-bigqueryslack

Is there a way to configure Big Query and slack to post a message on success of a scheduled query?

I have many scheduled queries running on a project and it is getting hard to keep track of which script or query is breaking. Is there a way that I can add send a slack notification after each scheduled query runs successfully?

This would hopefully let me go through the logs on slack each morning to see which query broke.

Solution

  • You can use Cloud Logging and Notification Channel to achieve your goal.

    In a nutshell what we gonna do is that we will setup logs-based alert policy which will notify Slack channel (configured via Notification Channel).

    First, please create a channel connected with Slack - steps can be found here.

    Now let's focus on BigQuery. Please go to the "Schedules Queries" page in BigQuery and select (click) any query.

    enter image description here

    Please click again on the query details to get more information.

    enter image description here

    "Run details" box will appear on the right side of the screen. Please click the "View in Logs explorer" button.

    enter image description here

    Now, we are in the Logs Explorer. Please click the "Create alert" button.

    enter image description here

    Please provide an alert name, and click "Next".

    enter image description here

    In the second step of creating logs-based alert policy ("Choose logs to include in the alert") you need to create an inclusion filter (more information here), for example (to catch logs from all BigQuery scheduled queries):

    resource.type=bigquery_dts_config severity>=DEFAULT

    You can use the "Preview Logs" option to check which logs will be captured.

    enter image description here

    In the third step, please set notification frequency and autoclose duration to the values you want.

    In the last, fourth step please choose your notification channel connected with Slack that you created at the beginning.

    enter image description here

    Please click save.

    Voila! From now you will get notifications about BigQuery queries in your Slack channel.