Data Storage, OLAP

Visualizing Data with Apache Druid: Building Real-Time Dashboards and Analytics

by Kinshuk Dutta
This entry is part 3 of 7 in the series DRUID Series

DRUID Series

Introduction

In previous posts, we explored Druid’s setup, performance tuning, and machine learning integrations. This post focuses on visualization, the final step in turning raw data into actionable insights. We’ll cover Druid’s integration with popular visualization tools like Apache Superset and Grafana, providing a guide to building real-time dashboards. For our E-commerce Sales Analytics Dashboard, we’ll connect Apache Druid to your existing Superset instance running on http://localhost:8088, set up as part of the blog Superset Basics, to visualize data and bring insights to life.

1. Why Visualization Matters in Real-Time Analytics

Data visualization allows us to understand trends, spot anomalies, and track key metrics in real time. When combined with Druid’s real-time ingestion and fast querying, visualization tools transform raw data into actionable, visual insights that can be customized for various business needs:

  • Sales and Revenue Monitoring: See daily or hourly sales and revenue in real time, broken down by product or category.
  • Anomaly Alerts: Detect unusual activity quickly, with visual alerts highlighting spikes or dips in expected behavior.
  • ML-Driven Forecasting: Incorporate machine learning models to forecast sales or user engagement, allowing for proactive decision-making.

2. Integrating Apache Druid with Superset

Since you already have Superset installed on http://localhost:8088, we’ll focus on connecting Druid to this instance to start visualizing e-commerce data quickly.

A. Setting Up the Druid Data Source in Superset

  1. Add Druid as a Data Source:
    • In Superset, go to Data > Databases.
    • Click + Database and select Druid from the list.
    • In the SQLAlchemy URI field, input your Druid broker URL, typically druid://localhost:8082/druid/v2/sql/.
    • Click Test Connection to confirm the connection.
  2. Create a New Dataset:
    • Once connected, navigate to Datasets and select the Druid database.
    • Add the dataset for your project, like ecommerce_sales, to begin building visualizations.

B. Building Visualizations in Superset

  1. Sales Metrics:
    • For tracking total sales and revenue, create a line chart to visualize daily or hourly sales trends.
    • Use the bar chart visualization to display revenue broken down by product categories.
  2. Customer Activity Heatmap:
    • Use Superset’s heatmap chart to show peak times for customer activity, segmented by hour and day.
    • Set the time granularity to hourly to see customer behavior patterns in real time.
  3. Anomaly Detection Alerts:
    • To visualize anomalies, color-code the data points based on machine learning predictions. For instance, set high sales spikes as red to signify unusual activity.
    • Integrate ML models using the ml_integration.py script to feed predictions into Superset, creating a dynamic view of predicted vs. actual sales.
  4. Forecasting:
    • Display daily sales predictions by adding a trend line to the sales chart.
    • Compare the ML predictions with actual values to identify trends and deviations.

3. Real-Time Monitoring with Grafana

Grafana is another powerful visualization tool, especially for time-series data, and can complement Superset’s analytics with real-time alerts and monitoring.

Connecting Grafana to Druid

  1. Install Druid Plugin for Grafana: Set up the Druid plugin or connect via the HTTP API.
  2. Configure Real-Time Metrics: Create panels for live metrics, like customer engagement or sales per minute.
  3. Anomaly Alerts: Use Grafana’s alerting feature to notify you of detected anomalies.

4. Enhancing the Sample Project: E-commerce Sales Analytics Dashboard

We’ll extend the E-commerce Sales Analytics Dashboard to include visualization, machine learning predictions, and anomaly detection in Superset. Here’s how to build a more robust and responsive analytics solution.

Updated Project Structure

plaintext
ecommerce-druid-analytics/
├── data/
│ ├── sample_data.csv # Sample e-commerce data
├── druid_configs/
│ ├── ingestion_spec.json # Batch ingestion spec
│ ├── kafka_ingestion_spec.json # Real-time Kafka ingestion spec
│ ├── tuning_config.json # Performance tuning configuration
├── src/
│ ├── main.py # Python script for loading data into Kafka
│ ├── kafka_producer.py # Kafka producer script
│ ├── query_optimization.py # Query optimization functions
│ ├── ml_integration.py # Machine learning integration and predictions
│ ├── anomaly_detection.py # Anomaly detection functions
│ ├── visualization_setup.py # Visualization setup for Superset and Grafana
└── visualizations/
├── superset_dashboard.json # Superset dashboard configuration
├── grafana_dashboard.json # Grafana dashboard configuration
└── test_cases/
├── test_dashboard_load.py # Testing script for dashboard loading and rendering

5. Practical Example: Configuring ML and Anomaly Detection in Superset

With your Superset instance running at http://localhost:8088, you can easily integrate machine learning predictions and anomaly detection into your dashboard.

A. Prediction Model Integration

Using Scikit-Learn or TensorFlow, load data from Druid, train a model on sales data, and save the model:

python
from sklearn.linear_model import LinearRegression
model = LinearRegression()
model.fit(X_train, y_train)
# Save the model for future predictions

In the ml_integration.py file, add a function that loads the model and runs daily predictions on your data:

python

import joblib

# Load the trained model
model = joblib.load(‘path/to/saved_model.pkl’)

def predict_sales(data):
predictions = model.predict(data)
return predictions

Feed these predictions into Superset for visualization.

B. Anomaly Detection Alerts in Superset

In Superset, configure anomaly alerts to monitor for unusual spikes in activity. Here’s how to visualize anomalies flagged by the model:

  1. Create an Alerting Metric: Set up an alert metric for high sales spikes in Superset. Use color-coding to highlight anomalies based on ML predictions (e.g., red for high spikes).
  2. Display Anomalies on Line Charts: Visualize predictions alongside actual values in a line chart, marking anomalies with distinct colors.

Conclusion

This fifth blog post completes the E-commerce Sales Analytics Dashboard by adding powerful visualization features using your existing Superset instance and Grafana. With these tools, you can monitor metrics, visualize predictions, and detect anomalies in real time, making the dashboard a comprehensive analytics solution.

In the next post, we’ll dive into Advanced Data Security and Access Control in Apache Druid to help secure sensitive data and manage access in a multi-user environment. Stay tuned as we continue expanding the capabilities of Druid!

Series Navigation<< Securing and Finalizing Your Apache Druid Project: Access Control, Data Security, and Project SummaryExtending Apache Druid with Machine Learning: Predictive Analytics and Anomaly Detection >>