Query Your Data Lake Directly From Slack

Overview

There are a plethora of Slack bots out there that are very powerful. Often times we get questions from management asking for information related to sales, customers and general information that we have in other dashboards or spread out across data sources such as Salesforce, S3, etc. This got me thinking, how can I make this easier not only for me, but for management to get the answers. I have used many bots and even created bots in the past to help solve similar problems, so why not use Dremio with Slack to get access to our data lake and make a bot that can answer these questions; Phrases such as “How many sales are in the south east?” or “How many prospects are in the POC phase?”

In this blog post we will examine how this implementation can be done using Dremio, Python and Slack bot APIs.

Requirements

• Python 3
• Basic Python skills to create your own functions
• System to run the bot from
• Slack workspace for testing
• URL to use with Slack’s slash commands
• Slack bot created with OAuth and slash commands setup; Start by creating a bot, enabling OAuth and slash commands and setting permissions for the bot. Slack has a comprehensive tutorial on how to get the bot done in their documentation.

Review the Slack python developer kit Github

Slack has created a developer kit for their api which can be accessed here. Review the documentation, and in the next section we will look at how we integrated Dremio calls.

Basic bot implementation

Let’s start by implementing a basic slack bot that will utilize the slash commands “/command”. The bot when called will display a dialog asking for the grain level for a report. When the user posts their response, the bot will call a function to run a query in Dremio, plot the results in Matplotlib and send the image back to the channel that the bot was called from.

I have made the code available for you to download here. The following breakdown shows details about each one of the steps that were executed during the implementation.

env.py

This file is used to set my environment variables at runtime and can be setup using the following code.

import os
os.environ['SLACK_API_TOKEN'] = 'slack_bot_token_goes_here'

Setting up your Dremio Client Auth

I set up Dremio Client Auth using this configuration details, you can learn more about the unofficial Dremio Client for Python here.

dremio_slack_bot.py

This is the main script that will be run. The bot will utilize Flask, a Python framework for a web server, to send and receive information to Slack. We begin by importing all of the necessary libraries and setting up the Dremio connection information. After creating the client and app we begin defining the endpoints that slack will call.

• Def setup_app() - initializes the Dremio and Slack client. It also holds the other functions.
• def nyc_report() - is the ‘/slack/nyc_request’ route for the dialog that the user fills out requesting a report.
• Report_grain - contains the mappings for what is displayed to the end user and what is sent back to the slack client and used in the sql query.
• builder - Uses Slack’s Python SDK to build a dialog box that is displayed to the end user when sending the slash command.
• def return_report() - is the ‘/slack’ is the route that will be used when a user submits a post. The function will call for the report and post the file to the channel that the user called from.
• def get_report() - is the function that sends the SQL query to Dremio, takes the results and generates a plot image.

Demo of the bot in action

Conclusion

Now that you have a working bot, you can start adding your own custom functions. Maybe you want to use it for stocks and alerts. You could have your bot periodically query Dremio for the information and send out automated reports. You could even use Plotly and send back visualizations like in the example. There are endless possibilities when you have quick, easy access to your data!

To learn more about Dremio visit our tutorials and resources, also if you would like to experiment with Dremio on your own virtual lab, go ahead and checkout Dremio University, and if you have any questions visit our community forums where we are all eager to help.