Databricks Genie with Microsoft Teams

A Microsoft Teams bot that integrates with Databricks Genie to enable natural language querying of data within Teams conversations. Users can ask questions in natural language, and the bot leverages Databricks Genie's AI capabilities to generate SQL queries and return data insights directly in Teams.

Overview

This project bridges Microsoft Teams and Databricks Genie, allowing teams to collaborate and analyze data without leaving their Teams environment. The bot handles:

• Natural language question processing
• Databricks Genie API integration for intelligent query generation
• File upload/download capabilities for data sharing
• Conversation history management
• Adaptive Card UI for rich interactions

Features

Core Capabilities

• Natural Language Processing: Ask questions in plain English and get data insights
• Multi-turn Conversations: Maintain conversation context across multiple exchanges
• Genie Spaces Management: List and select from available Databricks Genie spaces
• Query Result Formatting: Display query results, schema information, and descriptions
• File Handling: Upload and download data files through Teams

User Experience

• Adaptive Cards: Interactive UI elements for better user engagement
• Conversation Tracking: Persistent conversation state across user sessions
• Error Handling: Graceful error messages and logging

Project Structure

.
├── main.py                          # Application entry point
├── config.py                        # Configuration management
├── requirements.txt                 # Python dependencies
├── example.env                      # Environment variable template
├── Bots/
│   ├── teams_genie_bot.py          # Main Teams activity handler
│   └── Handlers/
│       ├── message_handler.py       # Message routing logic
│       ├── card_action_handler.py   # Adaptive card interactions
│       ├── file_card_handler.py     # File upload/download handling
│       ├── handle_questions.py      # Question processing
│       ├── genie_list_handler.py    # Space listing
│       └── space_selection_handler.py # Space selection logic
├── Modules/
│   ├── Genie.py                     # Databricks Genie API wrapper
│   ├── database.py                  # Database management
│   └── AdaptiveCardTemplate.py      # Card template definitions
├── Models/
│   └── user_state.py                # User conversation state model
└── Utils/
    ├── bot_utils.py                 # Utility functions
    ├── llm_summarizer.py            # LLM-based text summarization
    ├── response_formatter.py         # Response formatting
    └── sync_lru_cache_async.py       # Async caching utilities

Prerequisites

• Python 3.8+
• Microsoft Azure
• Databricks workspace with Genie enabled
• Access to Microsoft Teams

Installation

1. Clone the repository

   git clone https://github.com/henry-richard7/Databricks-Genie-With-Microsoft-Teams.git
   cd Databricks-Genie-With-Microsoft-Teams

2. Create a virtual environment

   python -m venv venv
   venv\Scripts\activate

3. Install dependencies

   pip install -r requirements.txt

4. Configure environment variables

   • Copy example.env to .env
   • Fill in the required values:

Configuration

Environment Variables

Create a .env file with the following variables:

# Microsoft Bot Framework
MicrosoftAppId=<your-app-id>
MicrosoftAppPassword=<your-app-password>
MicrosoftAppTenantId=<your-tenant-id>
MicrosoftAppType=MultiTenant

# Databricks Configuration
DATABRICKS_HOST=<Your-Databricks-Host-URL>
DATABRICKS_TOKEN=dapi<your-personal-access-token>
DATABRICKS_LLM_ENDPOINT=databricks-gpt-oss-20b

# Database (Optional - defaults to SQLite)
database_url=sqlite:///genie_bot.db

Microsoft Bot Framework Setup

1. Deploy the code in any platform of your choice Azure Web App Services or locally on your machine using ngrok.
2. Go to Teams Developer Portal | Bot Framework and create a new bot.
3. Configure the messaging endpoint to your bot's URL: https://your-domain/api/messages
4. Got to Teams Developer Portal | Apps and create a New App.
5. Once created Configure -> App features click on Bot.
6. Identify your bot -> Select the bot you have created in step 3. In What can your bot do? tick Upload and download files. In Select the scopes where people can use your bot select all 3 options.

Databricks Setup

1. Create a Databricks workspace and enable Genie
2. Generate a Personal Access Token (PAT)
3. Create or select a Genie space for queries
4. Update the environment variables with your credentials

Running the Bot

Development

python main.py

The bot will start on http://localhost:3978

Production

Using Gunicorn:

gunicorn --worker-class aiohttp.GunicornWebWorker --workers 4 main:APP

How It All Works - Simple Flow

┌─────────────────────────────────────────────────────────────────┐
│                    MICROSOFT TEAMS                              │
│              User types message in chat                          │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ↓
┌─────────────────────────────────────────────────────────────────┐
│            MAIN BOT (teams_genie_bot.py)                         │
│   • Receives message from Teams                                  │
│   • Calls process_message() to handle it                         │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ↓
┌─────────────────────────────────────────────────────────────────┐
│        MESSAGE HANDLER (message_handler.py)                      │
│                                                                  │
│  Is it "list genie spaces"? ──→ Go to GenieListHandler         │
│           ↓ No                                                   │
│       Is it a button click? ──→ Go to CardActionHandler         │
│           ↓ No                                                   │
│       → Must be a question ──→ Go to HandleQuestions            │
└──────────────────────────┬──────────────────────────────────────┘
                           │
        ┌──────────────────┼──────────────────┐
        ↓                  ↓                  ↓
    ┌────────────┐  ┌────────────┐  ┌──────────────┐
    │ LIST SPACES│  │SHOW SPACES │  │ ASK GENIE    │
    │HANDLER     │  │HANDLER     │  │(HandleQuestio
    └────────────┘  └────────────┘  └──────────────┘
        │                │                  │
        ├────────────────┼──────────────────┤
        │                │                  │
        └────────────────┼──────────────────┘
                         ↓
        ┌────────────────────────────────────┐
        │  DATABASE (database.py)             │
        │ • Save user's selected space        │
        │ • Save conversation ID              │
        │ • Remember chat history             │
        └────────────────────────────────────┘
                         │
        ┌────────────────┴──────────────────┐
        │                                    │
        ↓                                    ↓
┌──────────────────────┐          ┌──────────────────────┐
│  GENIE API (Genie.py)│          │ ADAPTIVE CARDS       │
│                      │          │ (response_formatter) │
│ • Ask question to    │          │                      │
│   Databricks Genie   │          │ • Format results     │
│ • Get spaces         │          │ • Make pretty cards  │
│ • Run queries        │          │ • Create buttons     │
└──────────────────────┘          └──────────────────────┘
        ↓
┌──────────────────────┐
│ DATABRICKS WORKSPACE │
│                      │
│ • Runs the query     │
│ • Returns results    │
└──────────────────────┘
        │
        └─────→ [Results go back through handlers] → [Display in Teams]

What Each Part Does

Component	File	What It Does
Main Bot	teams_genie_bot.py	Catches all Teams messages and passes them to handlers
Message Router	message_handler.py	Decides: Is it a question? Space command? Button click? Routes accordingly
Question Handler	handle_questions.py	Takes your question, gets it answered, formats the response
Space Lister	genie_list_handler.py	Shows available Genie spaces with nice clickable buttons
Card Handler	card_action_handler.py	Handles button clicks and form submissions
File Handler	file_card_handler.py	Manages file uploads and downloads
Genie API	Modules/Genie.py	Talks to Databricks, sends questions, gets answers
Database	Modules/database.py	Remembers who you are, what space you picked, previous conversations
Response Formatter	Utils/response_formatter.py	Makes raw data look pretty in Teams

Real Example: What Happens When You Ask a Question

You in Teams: "How many users?"
                    ↓
Main Bot catches it
                    ↓
Message Handler says "This is a question"
                    ↓
HandleQuestions does this:
  1. Look up your space ID from database
  2. Look up your conversation ID (if you're continuing chat)
  3. Send question to Genie API: "How many users?"
  4. Genie writes SQL and runs it on Databricks
  5. Get back: {data: [...], columns: [...]}
                    ↓
ResponseFormatter makes it pretty:
  - Turns data into a table
  - Adds nice formatting
  - Creates an Adaptive Card
                    ↓
Send back to Teams as formatted message with table
                    ↓
You see: "📊 Results: Users: 15,234"

Dependencies

Key Python packages:

• botbuilder-core: Microsoft Bot Framework SDK
• botbuilder-integration-aiohttp: Async HTTP integration
• databricks-sdk: Databricks API client
• databricks-langchain: LangChain integration for LLMs
• sqlalchemy: ORM for database management
• pydantic-settings: Configuration management
• langchain: LLM chain orchestration
• aiohttp: Async HTTP client
• pandas: Data manipulation
• openpyxl: Excel file handling

See requirements.txt for the complete list with versions.

Database

By default, the bot uses SQLite (genie_bot.db). To use a different database:

database_url=postgresql://user:password@localhost/genie_bot

Supported databases:

• SQLite (default): sqlite:///genie_bot.db
• PostgreSQL: postgresql://user:pass@localhost/dbname
• MySQL: mysql+pymysql://user:pass@localhost/dbname

Deployment

Azure Bot Service

1. Create a Web App Bot in Azure Portal
2. Configure deployment settings
3. Deploy using Git or ZIP deployment
4. Update messaging endpoint in bot settings

Contributing

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch
3. Commit your changes
4. Push to the branch
5. Create a Pull Request

Support

For issues, questions, or feature requests, please open an issue on the GitHub repository.