Hello! We are a writer team from Definer Inc. In this issue, you are wondering how to create a mechanism to notify BigQuery results to Slack. Let's take a look at the actual screens and resources to explain in detail.

Notifying BigQuery results to Slack provides real-time monitoring, facilitates collaboration, enables proactive alerts, automates workflows, centralizes data communication, and allows for customization and integration. It enhances visibility, collaboration, and automation within teams, making it easier to track data processes, share insights, and take proactive actions based on query results or data conditions. Here are a few specific use cases where notifying BigQuery results to Slack can be valuable:

• E-commerce Analytics
• Fraud Detection
• Marketing Campaign Monitoring
• Data Pipeline Monitoring
• Service-Level Agreement (SLA) Monitoring
• Data Collaboration and Reporting

This article summarizes helpful information and practices when you want to notify BigQuery results to Slack.

First, we will perform the setup. As a precondition, we assume that BigQuery already exists. (1) Creating a Pub/Sub topic Log in to GCP, and click "Create Pub/Sub" topic. (2) Schedule BigQuery Go to the BigQuery screen, and from "Scheduled Queries," configure the query's periodic execution settings. In the Notification Options, specify the Pub/Sub you just created. Preparation is now complete!

Next, we will create a notification to Slack with Cloud Functions. (1) Function Trigger Settings From the GCP console, go to the Cloud Functions screen and select "Create Function". Select "Cloud Pub/Sub" for the trigger type. (2) Code setup for the function The main Slack notification part is written in Python. In the following code, replace Slack web hook with your own Slack web hook.

import base64
import requests
import json
import os

def notify_slack(event, context):
    event_decode = base64.b64decode(event['data']).decode('utf-8')
    message = json.loads(event_decode)
    if message['state'] != 'FAILED':
        return
    name = message['name']
    error_status = message['errorStatus']['message']
    query = message['params']['query']
    error_summary = {
        "text": "Scheduled query failed.",
        "attachments": [{
            "color": "danger",
            "pretext": "Failed",
            "title": "Failed",
            "fields": [
                {
                    "title": "Name",
                    "value": name,
                },
                {
                    "title": "Error status",
                    "value": error_status,
                },
                {
                    "title": "Query",
                    "value": query,
                }],
        }]
    }

    url = ${Slack web hook}
    response = requests.post(url, data=json.dumps(error_summary))

    return print(response.text.encode('utf8'))                

 

Upon failure, Slack notified!

Create a mechanism to store Slack data with BigQuery from scratch.

・PrismScaler is a web service that enables the construction of multi-cloud infrastructures such as AWS, Azure, and GCP in just three steps, without requiring development and operation. ・PrismScaler is a web service that enables multi-cloud infrastructure construction such as AWS, Azure, GCP, etc. in just 3 steps without development and operation. ・The solution is designed for a wide range of usage scenarios such as cloud infrastructure construction/cloud migration, cloud maintenance and operation, and cost optimization, and can easily realize more than several hundred high-quality general-purpose cloud infrastructures by appropriately combining IaaS and PaaS.  

This article provides useful introductory information free of charge. For consultation and inquiries, please contact "Definer Inc".

・Definer Inc. provides one-stop solutions from upstream to downstream of IT. ・We are committed to providing integrated support for advanced IT technologies such as AI and cloud IT infrastructure, from consulting to requirement definition/design development/implementation, and maintenance and operation. ・We are committed to providing integrated support for advanced IT technologies such as AI and cloud IT infrastructure, from consulting to requirement definition, design development, implementation, maintenance, and operation. ・PrismScaler is a high-quality, rapid, "auto-configuration," "auto-monitoring," "problem detection," and "configuration visualization" for multi-cloud/IT infrastructure such as AWS, Azure, and GCP.