by Lazy Sloth
Send a daily report of some metrics from BigQuery to Slack
import logging
import time
from typing import Dict, Any
from abilities import run_query_on_bigquery
import os
import slack_sdk
from slack_sdk import WebClient
from slack_sdk.errors import SlackApiError
from datetime import datetime, timedelta
from tabulate import tabulate
import requests
import json
import pytz
from apscheduler.schedulers.background import BackgroundScheduler
logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.WARNING)
#TODO replace the query and title
TITLE = "Example title"
QUERY = """
your query goes here
"""
Send a daily report of some metrics from BigQuery to Slack
Created: | Last Updated:
Introduction to the Daily BigQuery Report to Slack Template
Welcome to the Daily BigQuery Report to Slack Template! This template is designed to help you automate the process of fetching data from BigQuery, formatting it into an easy-to-read table, and sending this report to a Slack channel of your choice. This is particularly useful for teams who need to keep track of key metrics and want to receive daily updates without manual intervention.
Getting Started with the Template
To begin using this template, simply click on "Start with this Template" on the Lazy platform. This will set up the template in your Lazy Builder interface, ready for customization and use.
Initial Setup: Adding Environment Secrets
Before you can use this template, you'll need to set up a couple of environment secrets that the code requires to function properly:
- SLACK_API_TOKEN: Your Slack API token which allows the app to post messages to your Slack channel.
- SLACK_CHANNEL: The ID of the Slack channel where you want the reports to be sent.
To add these environment secrets, navigate to the Environment Secrets tab within the Lazy Builder and enter the key-value pairs for the SLACK_API_TOKEN and SLACK_CHANNEL.
Test: Pressing the Test Button
Once you have configured the environment secrets, it's time to test the app to ensure everything is working as expected. Press the "Test" button in the Lazy Builder. This will deploy the app and launch the Lazy CLI.
Entering Input
If the template code requires any user input through the CLI, you will be prompted to provide it after pressing the Test button. In this template, there is no need for additional user input as the query and Slack channel are already defined in the code and environment secrets.
Using the App
There is no frontend experience for this app as its primary function is to send data to Slack. Once the app is running, it will automatically perform its task without further interaction.
Integrating the App
After testing the app and confirming that the reports are being sent to your Slack channel, you may want to customize the QUERY and TITLE in the code to match the specific data you wish to report on. Replace the placeholder text in the QUERY constant with your actual BigQuery SQL query and update the TITLE constant with a descriptive title for your report.
Remember, you can always modify the scheduler settings in the code if you need the report to be sent at a different time or with a different frequency. The scheduler is currently set to trigger the send_metrics function every day at 10 am UK time.
If you need to refer to any documentation or require assistance, please use the links provided in the code comments or reach out to the Lazy customer support for further help.
That's it! You're now ready to receive daily BigQuery reports in your Slack channel, all automated through your Lazy app.
Technologies
![SQL](https://cyclr.com/wp-content/uploads/2022/03/ext-550.png)
![Python](https://i.pinimg.com/originals/2f/9c/11/2f9c11f9e55efbf1791f12c06d60729b.jpg)
![Slack](https://a.slack-edge.com/80588/marketing/img/meta/slack_hash_256.png)