AI Query Generator Slack Bot for BigQuery

 import re
import slack_sdk
from slackeventsapi import SlackEventAdapter
from flask import Flask, request
from typing import List
from abilities import llm_prompt
from google.cloud import bigquery
import logging
from prettytable import PrettyTable
import os

logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.WARNING)

PROJECT_ID = os.environ['PROJECT_ID']
DATASET_ID = os.environ['DATASET_ID']
TABLE_ID = os.environ['TABLE_ID']
SLACK_BOT_TOKEN = os.environ['SLACK_BOT_TOKEN']

processed_messages = []

def generate_query(
    question: str, schema: str, event_types: str, table_info: str,  previous_query: str = None, errors: List[str] = None
) -> str:

About this template

This app allows users to interact with a Slack bot, ask a question about the data in a table or request the table schema, and then uses the latest ChatGPT to generate a query that is executed on BigQuery to return the results. The app includes a retry mechanism for query generation in case of an error (up to two retries) and provides the LLM with the table info to generate more accurate queries. The table schema is only printed if it is successfully retrieved. All errors from retries are now passed to the LLM. The generated query is printed before the results, and the results are displayed in a pretty table format. The bot uses the Slack API to send and receive messages and parses the user's message to determine the action to take. The bot always responds in a thread to the original message.

Introduction to the AI Query Generator Slack Bot for BigQuery Template

Welcome to the AI Query Generator Slack Bot for BigQuery template! This template is designed to help you create a Slack bot that interacts with users, allowing them to ask questions about data in a BigQuery table or request the table schema. The bot then uses the latest ChatGPT to generate a SQL query, which is executed on BigQuery to return the results. This step-by-step guide will walk you through the process of setting up and using this template on the Lazy platform.

Getting Started

To begin using this template, click on "Start with this Template" on the Lazy platform. This will pre-populate the code in the Lazy Builder interface, so you won't need to copy, paste, or delete any code manually.

Initial Setup: Adding Environment Secrets

Before testing the app, you'll need to set up some environment secrets within the Lazy Builder. These are necessary for the bot to interact with Google BigQuery and Slack. Here's what you need to do:

  • Navigate to the Environment Secrets tab within the Lazy Builder.
  • Add the following secrets with their respective values:
    • PROJECT_ID: Your Google Cloud project ID.
    • DATASET_ID: The dataset ID in BigQuery where your table is located.
    • TABLE_ID: The table ID in BigQuery that you want to query.
    • SLACK_BOT_TOKEN: Your Slack bot token, which you can obtain from your Slack app settings.
    • SLACK_SIGNING_SECRET: Your Slack app's signing secret, also available in your Slack app settings.
  • Ensure that you have the correct permissions and roles set up in Google Cloud to access BigQuery and perform queries.

Test: Pressing the Test Button

Once you have set up the environment secrets, press the "Test" button on the Lazy platform. This will begin the deployment of the app and launch the Lazy CLI. If the code requires any user input, you will be prompted to provide it through the Lazy CLI.

Entering Input

If the template requires user input, you will be prompted for it after pressing the "Test" button. Follow the instructions in the Lazy CLI to provide the necessary input.

Using the App

After the app is deployed, you can interact with the Slack bot by sending messages directly in Slack. The bot will respond to queries about the data or schema requests in a thread to the original message. If the bot generates a query, it will execute it on BigQuery and return the results in a formatted table.

Integrating the App

If you need to integrate this Slack bot with other tools or services, you may need to provide the Slack bot's server link, which will be printed by the Lazy builder CLI after deployment. For example, you might add this link to your Slack app's event subscriptions to receive messages from Slack.

Remember, this bot is designed to work within the Slack environment, so you'll need to ensure that your Slack workspace is set up to interact with the bot. This includes installing the bot to your workspace and inviting it to the channels where you want it to be active.

That's it! You're now ready to use the AI Query Generator Slack Bot for BigQuery. If you encounter any issues or have questions, refer to the documentation links provided in the code or reach out to Lazy's customer support for assistance.

Category
Last published
June 15, 2024

More templates like this

PDF Data Extraction and Excel Transfer

An app for extracting name, phone number, and email data from PDF files and transferring it to Excel.

PDF
Python

Free Unlimited DALL-E-3 - v2

Demo at https://app--266a4010-2c1e-410b-a9e6-93252d88a540.app.getlazy.ai/ - You get the api key at https://api.discord.rocks

OpenAI

GPT-4 Exam Generator

This app uses GPT-4 to generate a PDF of a new exam based on an uploaded PDF of a past exam.

PDF
OpenAI
Python
Home
/
AI Query Generator Slack Bot for BigQuery