Engineering

How to Automate Triggers to Send Slack Messages from BigQuery

November 17, 2023

Automate triggers to send data from BigQuery to Slack

TL;DR

This article outlines two approaches for automating Slack notifications via BigQuery triggers:

Method 1: Manual Python Script Setup:

  1. Authenticate with BigQuery and obtain a Slack webhook URL.
  2. Write a custom Python script to fetch data from BigQuery and
  3. Send Slack notifications based on predefined conditions.

Method 2: Automating with Locale.ai:

  1. Connect your BigQuery database to Locale.ai.
  2. Set up alerts in Locale.ai to monitor data using SQL queries.
  3. Configure Slack notifications with customizable message templates to notify status of query run.

Method 1  requires manual setup and maintenance. It also requires consistent manual monitoring to know if scripts fails. In contrast, Locale.ai simplifies the process with automation, providing customizable integration with various platforms beyond Slack.

Operations teams often need to monitor key metrics closely, such as daily sales performance, Inventory levels, Issue tracking, etc. Manually monitoring these metrics can be time-consuming and error-prone. Teams need to be proactive to stay ahead of the issue, and the most effective way to achieve this is through triggering the alerts to the right individuals. These automated triggers not only alert the team at the right time, it can also provide crucial info through which teams can resolve the issue quickly.

These alert triggers can be set up on any database or data warehouse. For those who uses BigQuery, In this detailed guide, we’ll walk you through how to automate triggers to send Slack messages from BigQuery

  1. Method #1: Manually Using Custom Python Script
  2. Method #2: Automating trigger through Locale

Method #1: Manually Using Custom Python Script

Let's explore how this can be achieved by fetching sales data from Google BigQuery and sending alerts to a Slack channel using Python when sales drop below a certain threshold.

Authenticate BigQuery

To interact with BigQuery, first set up authentication by creating a service account in Google Cloud Platform (GCP) and downloading the JSON key file. The service account must have read access to BigQuery and the datasets required.

Assign Roles on Big Query Database

Set the environment variable GOOGLE_APPLICATION_CREDENTIALS to the path of this JSON key file.


export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/keyfile.json"

Get Slack Webhook URL

Create a new Slack app in your workspace and navigate to the 'Incoming Webhooks' section. Activate incoming webhooks and create a new webhook to the desired Slack channel. Slack will provide a Webhook URL which will be used to send messages to this channel.

Activating incoming webhook on Slack

Python Script for Data Retrieval and Slack Notification


import time
from google.cloud import bigquery
from slack_sdk import WebClient
from slack_sdk.errors import SlackApiError
import os

# Set up Google Cloud credentials
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = '/path/to/your/keyfile.json'

# Initialize BigQuery client
client = bigquery.Client()

# Slack webhook URL
slack_webhook_url = 'your_slack_webhook_url'

def fetch_and_send():
    try:
        # SQL query to fetch products with sales below threshold
        query = """
            SELECT product_id, SUM(sales_amount) AS total_sales
            FROM `your_project.your_dataset.sales_data`
            WHERE DATE(sales_date) = CURRENT_DATE()
            GROUP BY product_id
            HAVING total_sales < 1000;
        """

        # Execute query
        query_job = client.query(query)
        results = query_job.result()

        # Format message for Slack
        message = "Sales Alert! Products with low sales today:\n"
        for row in results:
            message += f"Product ID: {row.product_id}, Total Sales: {row.total_sales}\n"

        # Send message to Slack
        slack_client = WebClient(url=slack_webhook_url)
        response = slack_client.chat_postMessage(channel='#your-channel', text=message)

    except Exception as e:
        print(f"An error occurred: {e}")

# Main loop
while True:
    fetch_and_send()
    time.sleep(3600)  # Sleep for 1 hour (3600 seconds)

Replace 'your_slack_bot_token', #your-channel, and the BigQuery table reference with appropriate values. This method uses Python to connect to BigQuery, execute the SQL query, process the results, and then format and send a message to a Slack channel using the Slack SDK.

This method can help you receive slack alerts but it comes with some evident limitations:

  • Maintenance The script currently includes basic error handling, which means if something goes wrong, it will let you know by printing an error message to the console. This is helpful for troubleshooting, but in a real-world scenario, you might want more comprehensive error management
  • Lack of Monitoring: Without external monitoring, you may not know if the script fails or stops running.
  • No Failover or Recovery: If the script encounters an error, it will not recover or retry unless explicitly programmed to do so.

Method #2: Automating triggers through Locale

Locale, no-code platfrom handles the heavy lifting, providing a maintenance-free, reliable, and user-friendly experience. With just a few clicks, you can automate alert triggers to Slack.

Step 1: Login to Locale, Go to Org Setting and Connect your BigQuery database from the data source.

Integrating BigQuery database to Locale

Step 2: Now go to Alerts → Create New Alerts. Choose your database → Click on Setup data to be monitored.

Database Connection setup on Locale platform

Step 3: Write your own SQL Query and click Run to verify the data. Once you are satisfied, click Done.

SQL Query to set the alert condition that will trigger alerts when sales drop below threshold

In the above example, if you want to get notified when sales drop below a certain threshold, you can use the below SQL script:


SELECT CURRENT_DATE() AS current_date, product_id, SUM(sales_amount) AS total_sales
    FROM `your_project.your_dataset.sales_data`
    WHERE DATE(sales_date) = CURRENT_DATE()
    GROUP BY product_id
    HAVING total_sales < 1000;

Step 4: Now Setup your schedule based on your requirement, this would automatically run the query based on the frequency you set. Click on Run the query at frequency and choose your schedule

Scheduling alert runs in Locale by setting alert frequency

Step 5: With Locale, You can also customize the Slack message to get the better idea. To configure the Slack message, Click on Configure incidents and enter your message in the Incident Title.

Configure incidents on Locale to provide better context to the users receiving alerts

Step 6: Final Step, Set up whom or on which channel the Slack alert should be sent. Go to the Setup Notification section and click on Add Notification then select Send Slack Message. Select whether you want the Slack message to be sent to a channel or a user. You can also preview and send test notifications.

Configuring Slack alerts to define whom to send alerts on Locale.ai

Save the alert. It will automatically run itself on the pre-set schedule and trigger a Slack notification.

Sample Slack alerts for sales drop below defined threshold

Conclusion

Automating triggers to Slack helps your team to be notified on their preferred channel so that they can quickly response to changes or issues. In this detailed guide, we looked at two methods which can help you to automate Slack alerts from BigQuery database.  Although Method #1, through Python Script is a viable option, it can be complex and error-prone to implement, especially if you need to handle complex notification scenarios.

Locale’s no-code platform makes it easy to automate alerts to Slack from your BigQuery database with minimal one-time setup. Locale helps you to:

  • Easy automation of triggers to Slack
  • Choose a schedule for sending notifications based on your requirements.
  • Dynamically choose people to send Slack message based on the query result allowing alerts only go to the person it needs.
  • Group the notifications ensuring less noise to the users.
  • Set up and provide a clear playbook for users to follow in case of any issue so that it can be resolved efficiently.
  • Set up an escalation rule to ensure issues are being resolved.
  • Moreover, if you want to expand sending reports/data to other destinations like Gmail, WhatsApp, MS Teams etc, you get to do that with 100+ integration options.

If you are looking for a simple and reliable way to automate triggers to Slack from your BigQuery Database, try Locale. Reserve your spot - talk to our specialist today.