Tag Archives: azure databricks

Standard

Posted by

Posted on

July 6, 2025

Posted under

Azure Databricks

Comments

Leave a comment

Azure Databricks Series: Step-by-Step Guide to Integrating Power BI with Databricks Model Serving

Watch this as a video on our you tube channel JBSWiki.

Are you ready to unlock the power of real-time machine learning predictions directly in your Power BI dashboards? 🤩

With Databricks Model Serving, we can host machine learning models as REST APIs. But how do we bring those predictions into Power BI?

In this blog, I’ll show you two practical methods to connect Power BI to Databricks Model Serving and fetch predictions step by step.

This is a follow-up to my earlier work where I explained how to create a Databricks Serving Model. If you missed that, check it out first so you have your serving endpoint ready to go!

💡 Why Integrate Power BI with Databricks Model Serving?

Businesses are increasingly driven by real-time insights. Instead of waiting for static reports, you can now embed ML predictions right inside your dashboards, empowering data-driven decisions at the speed of business.

Some benefits:

✅ Real-time predictions in dashboards
✅ No manual data exports
✅ Fully automated pipelines
✅ Empower business users with AI insights

🔗 The Architecture

Here’s how the integration works:

Power BI ➡ Python Script / Power Query ➡ Databricks Serving Endpoint ➡ Prediction Results ➡ Power BI visuals

All communication happens over secure REST APIs, usually authenticated with a Databricks Personal Access Token (PAT).

⚙️ Pre-requisites

Before diving in, ensure you have:

  • An Azure Databricks workspace
  • A deployed ML model as a Databricks Serving Endpoint
  • A Databricks Personal Access Token
  • Power BI Desktop installed
  • Basic knowledge of Power Query and Python

🛠 Method 1: Get Data → Python Script in Power BI

This is one of the easiest ways to connect Power BI to Databricks Serving endpoints if you’re comfortable writing Python.

🔹 How It Works

You’ll:

  1. Go to Home > Get Data > Python Script in Power BI.
  2. Paste the Python code that:
    • Makes an HTTP POST request to the Databricks model endpoint.
    • Converts predictions into a DataFrame for visualization.

✅ Example Python Script

Here’s a full working Python script for Power BI:

import requests
import json
import pandas as pd

# Databricks endpoint URL
endpoint_url = "https://adb-2345432345567.11.azuredatabricks.net/serving-endpoints/HDFC_High_price_prediction/invocations"

# Your Databricks PAT token
token = "Databricks_Pat_Token"

# Prepare the payload
payload = {
    "inputs": [
        {
            "Date": "2024-07-03",
            "OPEN": 2000,
            "HIGH": 2079,
            "LOW": 1987,
            "CLOSE": 2075
        }
    ]
}

headers = {
    "Authorization": f"Bearer {token}",
    "Content-Type": "application/json"
}

# Make the POST request
response = requests.post(endpoint_url, headers=headers, json=payload)

# Convert input payload to DataFrame
input_df = pd.DataFrame(payload["inputs"])

if response.ok:
    result_json = response.json()
    predictions = result_json.get("predictions", [])
    
    # Put into dataframe
    output_df = pd.DataFrame(predictions, columns=["Prediction"])
else:
    # Handle errors gracefully
    output_df = pd.DataFrame({"Error": [response.text]})

input_df
output_df

🔎 What This Does

  • Sends input data to Databricks Serving endpoint.
  • Receives predictions in JSON format.
  • Converts predictions to a Pandas DataFrame.
  • Returns it to Power BI for visualizations.

Power BI will import both input_df and output_df as separate tables. You can merge them if needed.

⚠️ Important Notes

  • Don’t hard-code secrets like tokens in production. Use Key Vaults or environment variables.
  • Keep an eye on API call costs and throttling.

🛠 Method 2: Enter Data → Power Query → Python Script

This method is powerful when you want business users to enter data manually in Power BI and instantly fetch predictions.

🔹 How It Works

  1. Use Enter Data in Power BI to create a table of inputs.
  2. Pass this table into a Python script via Power Query.
  3. Call the Databricks endpoint and merge predictions into your original data.

This allows users to dynamically modify input data in Power BI itself.

✅ Example Power Query Script

Below is the Power Query M code you’d place in Advanced Editor in Power BI:

let
    Source = HDFC_Input,
    RunPython = Python.Execute("
import pandas as pd
import requests
import json

# Power BI table comes in as 'dataset'
input_df = dataset

# Force Date column to string
if 'Date' in input_df.columns:
    input_df['Date'] = input_df['Date'].astype(str)

# Convert numeric columns to floats
for col in input_df.columns:
    if pd.api.types.is_numeric_dtype(input_df[col]):
        input_df[col] = input_df[col].apply(lambda x: float(x) if pd.notnull(x) else None)

inputs = input_df.to_dict(orient='records')

payload = {
    'inputs': inputs
}

endpoint_url = 'https://adb-2345432345567.11.azuredatabricks.net/serving-endpoints/HDFC_High_price_prediction/invocations'
token = 'Databricks_Pat_Token'
headers = {
    'Authorization': f'Bearer {token}',
    'Content-Type': 'application/json'
}

response = requests.post(endpoint_url, headers=headers, json=payload)

if response.ok:
    result_json = response.json()
    
    try:
        predictions = result_json['predictions']
        
        # Handle possible formats
        if isinstance(predictions, list) and isinstance(predictions[0], (int, float)):
            output_df = pd.DataFrame({'Prediction': predictions})
        elif isinstance(predictions, list) and isinstance(predictions[0], list):
            output_df = pd.DataFrame(predictions, columns=['Prediction'])
        elif isinstance(predictions, list) and isinstance(predictions[0], dict):
            output_df = pd.DataFrame(predictions)
        else:
            output_df = pd.DataFrame({'Error': ['Unsupported prediction format']})
    except Exception as e:
        output_df = pd.DataFrame({'Error': [str(e)]})
else:
    output_df = pd.DataFrame({'Error': [response.text]})

# Merge prediction into input
final_df = input_df.copy()
try:
    final_df['Prediction'] = output_df['Prediction']
except:
    final_df['Error'] = output_df.iloc[:, 0]

final_df
", [dataset=Source])
in
    RunPython

🔎 What This Does

  • Takes user-entered data from Power BI as a table.
  • Converts it to JSON payload.
  • Calls Databricks Model Serving endpoint.
  • Handles various possible prediction formats:
    • single numeric predictions
    • lists of predictions
    • dictionaries of results
  • Merges predictions back into the original table.

💡 Advantages of This Method

✅ Super flexible for dynamic inputs
✅ Great for PoC demos and interactive reports
✅ Business-friendly approach—no code needed by users
✅ Predictions update automatically when inputs change

⚠️ Limitations and Considerations

  • Python scripting in Power BI requires the Python runtime installed locally.
  • Personal Access Tokens should be secured (e.g. not stored in plain text).
  • There might be latency if your model takes time to compute predictions.

🎯 Use Cases

Integrating Databricks Model Serving into Power BI opens up endless possibilities:

✅ Stock Price Prediction
✅ Sales Forecasting
✅ Customer Churn Analysis
✅ Fraud Detection
✅ Predictive Maintenance

🚀 Conclusion

Integrating Databricks Model Serving with Power BI is a game-changer for real-time analytics. Whether you use the Python script approach or Power Query with Enter Data, you’re enabling truly interactive, predictive dashboards that empower business users.

✅ Next Steps

  • Make sure your Databricks Serving endpoint is production-ready.
  • Move sensitive tokens to secure stores like Azure Key Vault.
  • Optimize API call performance for large-scale use.
  • Explore scheduled refreshes in Power BI Service to automate insights.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

July 6, 2025

Posted under

Azure Databricks

Comments

Leave a comment

Azure Databricks Series: Deploying Custom Model with Model Serving & Python Integration Step by Step

Watch this as a video on our you tube channel JBSWiki.

In the ever-evolving world of data and AI, one of the biggest challenges is bridging the gap between building a machine learning model and putting it into production so it can generate real business value.

Imagine this: you’ve spent weeks training a model to predict stock prices with great accuracy. It’s sitting in your Databricks workspace, looking perfect. But how do you actually use it in real-world applications to serve predictions in real time?

This is where Databricks Model Serving steps in to save the day.

In this blog, I’ll show you how to:

✅ Deploy a custom machine learning model as a serving endpoint in Databricks
✅ Understand why model serving is crucial in production environments
✅ Call your deployed model using Python code for real-time predictions

Let’s dive in!

🎯 Why Model Serving Matters

Training a machine learning model is only half the battle. In production environments, you often need:

  • Real-time predictions for dynamic applications like stock price forecasting, fraud detection, or recommendation systems.
  • A scalable, secure way to expose your model to other systems or applications.
  • Low-latency responses without needing to run entire notebooks or pipelines every time you want a prediction.

Databricks Model Serving solves these challenges by turning your trained model into a REST API. This means you can easily integrate machine learning into your applications, dashboards, and workflows without reinventing the wheel.

🧩 How Model Serving Fits into the Modern ML Workflow

Here’s how Databricks Model Serving fits into the bigger picture:

  1. Data Collection & Storage — Gather raw data into Azure Data Lake Storage or other data lakes.
  2. Data Engineering & Transformation — Clean and prepare the data using Databricks notebooks and Delta Lake.
  3. Model Training & Experimentation — Train models with MLflow and notebooks.
  4. Model Registration — Save your best model versions into the MLflow Model Registry.
  5. Model Serving — Deploy the model as an endpoint using Databricks Model Serving.
  6. Prediction Consumption — Call the endpoint from Python, applications, dashboards, or other services.

In this blog, we’ll focus on steps 5 and 6: Model Serving and how to consume predictions.

🚀 Deploying Your Custom Model in Databricks

Before we can call our model from Python, we need to deploy it as a serving endpoint.

If you haven’t done this yet, here’s a quick high-level overview of the steps:

  • Register Your Model in the MLflow Model Registry.
  • Navigate to Model Serving in the Databricks UI.
  • Select the model version you want to deploy.
  • Choose the compute size for serving (small, medium, large, etc.).
  • Click Deploy.

Databricks will handle all the heavy lifting, spinning up the infrastructure required to serve your model as a REST API endpoint.

For this example, let’s assume you’ve already deployed a model named HDFC_High_price_prediction.

🔗 Example Use Case: Stock Price Prediction

Let’s say we’ve built a model to predict high prices for HDFC Bank stock based on daily trading data.

We now want to:

  • Send trading data (like open, high, low, close prices) to our deployed endpoint.
  • Receive a prediction for the stock’s future high price.

This enables us to make real-time predictions and integrate them into trading dashboards, alerting systems, or further analytics.

🐍 Calling the Databricks Model Serving Endpoint Using Python

Now comes the fun part: calling your deployed model endpoint using Python!

Below is a Python script you can run from:

  • A Databricks notebook
  • A local Python environment
  • An application server

Here’s how to do it:

import requests
import json

# Databricks endpoint URL
endpoint_url = "https://adb-131152523232571.21.azuredatabricks.net/serving-endpoints/HDFC_High_price_prediction/invocations"

# Your Databricks PAT token
token = " Databricks PAT token"

# Prepare the payload
payload = {
    "inputs": [
        {
            "Date": "2024-07-03",  
            "OPEN": 2000,
            "HIGH": 2079,
            "LOW": 1987,
            "CLOSE": 2075
        }
    ]
}

headers = {
    "Authorization": f"Bearer {token}",
    "Content-Type": "application/json"
}

response = requests.post(endpoint_url, headers=headers, json=payload)

print("Status Code:", response.status_code)
print("Response:", response.json())

💡 How This Code Works

Let’s break it down:

  • endpoint_url → This is your Databricks Model Serving URL. You’ll find this in the Databricks UI under your deployed endpoint details.
  • token → This is your Databricks Personal Access Token (PAT). It’s crucial for authenticating API calls securely. Never share your PAT publicly.
  • payload → This JSON object represents your input data. It matches the format your model expects, e.g., columns for Date, OPEN, HIGH, LOW, and CLOSE prices.
  • headers → Standard HTTP headers, including the Authorization Bearer token and Content-Type.
  • requests.post() → This sends your data to the model’s endpoint and returns a prediction.
  • response.json() → Prints the model’s prediction result!

If everything is configured correctly, you’ll receive a JSON response containing your predicted value.

✅ Common Use Cases for Databricks Model Serving

Here are just a few real-world scenarios where Databricks Model Serving shines:

  • Financial institutions predicting stock prices or risk scores in real time.
  • Retail companies delivering personalized product recommendations to customers.
  • Healthcare providers forecasting patient outcomes or prioritizing triage.
  • Manufacturing industries performing predictive maintenance on equipment.
  • Energy companies optimizing grids or predicting power demands.

Databricks Model Serving makes it easy to turn machine learning into real-time business value.

🔒 Best Practices for Secure Model Serving

When deploying and consuming model endpoints:

✅ Always protect your tokens. Store them securely and never hard-code them in publicly visible code.

✅ Use versioning in Databricks MLflow Model Registry to manage updates and rollbacks safely.

✅ Monitor endpoint performance using Databricks’ built-in dashboards for latency, error rates, and cost management.

✅ Keep your input payloads clean and aligned with what your model expects to avoid errors.

🌟 Wrapping Up

Databricks Model Serving is a game changer for getting machine learning models into production quickly and reliably. Instead of wrestling with complex infrastructure, you can deploy your models with just a few clicks and call them from anywhere using Python.

In this blog, we’ve explored:

✅ Why model serving is crucial in modern ML workflows
✅ How Databricks simplifies deployment as an API
✅ How to invoke your model endpoint using Python

Whether you’re building models for financial forecasting, customer personalization, or predictive maintenance, Databricks Model Serving lets you bring your machine learning innovations to life in production.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

April 15, 2025

Posted under

Azure Databricks

Comments

Leave a comment

Azure Databricks Series: Displaying All Serverless SQL Warehouses in Your Workspace

When working with Azure Databricks, it’s often necessary to programmatically retrieve and manage metadata about your compute resources. One such resource is the Serverless SQL Warehouse, designed for cost-effective and scalable interactive analytics.

In this blog, we’ll walk through a step-by-step Python script that helps you list all Serverless SQL Warehouses in your Databricks workspace using the REST API and persist the results in a Delta table for further analysis.

🔍 Why Monitor Serverless SQL Warehouses?

Serverless SQL Warehouses are a key part of many organizations’ data strategies due to:

  • Auto-scaling capabilities
  • No infrastructure management
  • Pay-per-use pricing model

By tracking your serverless SQL endpoints, you can gain insights into:

  • Who created them
  • Their sizes and memory footprints
  • Auto-stop configurations
  • Their current state (running/stopped)

🛠️ Solution Overview

We’ll use the Databricks SQL Endpoints API to get the list of all SQL Warehouses, filter out only the serverless ones, enrich the data with approximate memory, and save it into a Delta table using PySpark.

🧪 Code Walkthrough

import requests
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType, IntegerType

instance = "https://adb-1311537494242340.26.azuredatabricks.net/"
token = "dapia***************************"

headers = {
    "Authorization": f"Bearer {token}"
}

url = f"{instance}/api/2.0/sql/endpoints"
response = requests.get(url, headers=headers)
data = response.json()

def size_to_memory(cluster_size):
    mapping = {
        "2X-Small": "64 GB",
        "X-Small": "128 GB",
        "Small": "256 GB",
        "Medium": "512 GB",
        "Large": "1 TB",
        "X-Large": "2 TB",
        "2X-Large": "4 TB",
        "3X-Large": "8 TB",
        "4X-Large": "16 TB"
    }
    return mapping.get(cluster_size, "Unknown")

# Prepare the data
records = []
for endpoint in data.get("endpoints", []):
    if endpoint.get("enable_serverless_compute", False):
        records.append({
            "name": endpoint["name"],
            "id": endpoint["id"],
            "cluster_size": endpoint["cluster_size"],
            "approx_memory": size_to_memory(endpoint["cluster_size"]),
            "auto_stop_mins": endpoint["auto_stop_mins"],
            "creator": endpoint["creator_name"],
            "state": endpoint["state"]
        })
# Create Spark DataFrame
df = spark.createDataFrame(records)

# Save to Delta (overwrite or append as needed)
df.write.format("delta").mode("overwrite").saveAsTable("default.serverless_sql_warehouses")

display(spark.table("default.serverless_sql_warehouses"))

✅ Sample Output

💡 Pro Tips

  • 🔐 Never hardcode tokens in production scripts. Use Azure Key Vault or Databricks secrets to securely manage secrets.
  • 🛑 Consider implementing pagination if your workspace has many warehouses.
  • 📊 Use this Delta table as a source for monitoring dashboards in Power BI or Databricks SQL.

📚 Conclusion

With just a few lines of code, you can automate the discovery of all serverless SQL warehouses, store their metadata in a Delta Lake, and use it for reporting, auditing, or monitoring purposes. This is particularly useful in large-scale environments where managing SQL compute efficiently is crucial.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.