Visualizing Data in Apache Druid with the Plotly Python Library

So, you’ve got data, now what? To make your data work for you it’s important to visualize and analyze that data. Data visualization is essential for analytics because it provides a clear and concise way to present complex data patterns and insights. In this blog, we will explore how to use the Python Plotly library to create visualizations of your Apache Druid data. I will show you how to connect to your Druid database using Python and execute SQL queries to fetch the data. Next, we will dive into the basics of data visualization with Plotly, including plotting graphs like pie charts and 3D scatter plots.

The Python Plotly Library

Python Plotly is a powerful library for data visualization and dashboards, making it an excellent choice for data scientists and analysts It supports a wide range of chart types, such as scatter plots, line plots, and bar charts. By connecting Python Plotly to your Druid data source, you can create dynamic visualizations. This combination allows for fast and efficient data exploration and analysis. The documentation and community support for Python Plotly is extensive, making it a popular choice among data scientists and analysts. Plus, Python Plotly’s integration with other programming languages, such as JavaScript and HTML, enables the creation of highly interactive dashboards. The open-source nature of both Druid and Python Plotly also ensures that you have access to a wide range of resources, such as blogs and GitHub repositories, for further learning and collaboration. Here are some factors to consider when choosing Plotly:

Interactivity: Plotly’s main strength is in creating interactive plots that can be used in Jupyter notebooks and web applications. If your project requires static graphs, another library like Matplotlib might be simpler to use.
Learning Curve: Plotly has a slightly steeper learning curve compared to simpler, less flexible libraries like Matplotlib and Seaborn. If you need to get up and running quickly and don’t need interactive features, you might prefer a simpler tool.
Customizability: Plotly charts are highly customizable, with control over almost every visual aspect of the chart. However, this can lead to more complex code compared to other libraries.
Online vs Offline Mode: Plotly supports both online and offline modes. The online mode allows for saving and sharing charts online, which can be very handy.
Community and Documentation: Plotly has robust documentation and an active community.
Export Options: Plotly provides flexibility in exporting your graphs in various formats including PNG, JPEG, SVG, and PDF.
Cost: Plotly is free and open-source for the basic library. However, advanced features such as Dash’s deployment server come with a cost.
Compatibility: Plotly can be used alongside Pandas, NumPy, and other Python scientific libraries, and it also plays nicely with Jupyter notebooks.

Quick Primer on Apache Druid

Effective data visualizations are not just about representing data accurately but also about doing so quickly. In interactive dashboards and analytical platforms, users expect to explore data without lag. Delays in loading or refreshing visualizations can impede the analytical process, leading to user frustration and potentially missed insights.

With Apache Druid’s capability to execute queries in sub-seconds, even across multi-billion-row datasets, it ensures that visualizations remain fluid and interactive. By providing swift access to underlying data, Druid empowers businesses to make faster decisions, recognize patterns in real time, and fully harness the power of their data through visual exploration.

Combining Druid with Plotly ensures that visual representations of data are swiftly delivered, enhancing the decision-making processes and overall end-user satisfaction. With the integration of these two technologies, users can create visually appealing and informative dashboards that allow them to gain deeper insights into their data at any scale.

Data Flow Diagram

The Data Flow Diagram (DFD) below summarizes the flow of information for this solution and provides an overview of the processes.

Each step is supported by code to automate the required functionality. The flow chart can serve as your roadmap of the processes that make up the solution.

The Solution

Software development is generally an operation of breaking down the issue you want to solve into several processes which are building blocks for the completed solution. The challenge is gathering insights from data by using effective visualization techniques. To solve this issue, I created a Flask which is a lightweight Python web application framework. I then wrote HTML and Python code to accomplish the necessary tasks, following the steps outlined below.

Generate Sample Data

To start visualizing Druid data with the Python Plotly Library, you need to first install and setup Druid to store the data. To install Druid, you can refer to the official instructions here. Once Druid is successfully installed and properly configured, we can visualize the data using the Python Plotly Library.

First, let’s create some sample data to be used in this exercise. The code below generates and saves random sales data into a CSV file. The data includes information such as product category, units sold, cost, and revenue. The script uses the Pandas library to create timestamps for each entry in 15-minute intervals from June 1st to June 30th, 2023. The generated data is then written into a CSV file named “sales_data.csv”. There is also a function called “upload_data”, that handles uploading the CSV data to another system or database.

Python

import csv
import random
import pandas as pd
from upload_csv_data import upload_data


def generate_data(row_num, timestamp):
	# List of product categories
	product_categories = ["Electronics", "Fashion", "Groceries", "Automobile", "Furniture"]
   
	# Randomly select a product category
	selected_category = random.choice(product_categories)
   
	# Generate random values for units sold, cost, and revenue
	units_sold = random.randint(10, 1000)
	cost = round(random.uniform(10, 1000), 2)
	revenue = round(cost * units_sold, 2)

	# Return the generated data as a list
	return [row_num, timestamp, selected_category, units_sold, cost, revenue]


def generate_csv(timestamps):
	# Define the file path for the CSV file
	filepath = '/Users/rick/IdeaProjects/CodeProjects/druid_visualizations/data/sales_data.csv'

	# Open the CSV file in write mode
	with open(filepath, 'w', newline='') as file:
    	# Create a CSV writer object
    	writer = csv.writer(file)
       
    	# Write the header row
    	writer.writerow(["RowNum", "Timestamp", "Category", "UnitsSold", "Cost", "Revenue"])

    	# Loop through the timestamps and generate data for each timestamp
    	for idx, timestamp in enumerate(timestamps, start=1):
        	# Generate data for each row
        	data_row = generate_data(idx, timestamp)
        	print(data_row)  # Print the generated data (optional)
           
        	# Write the data to the CSV file
        	writer.writerow(data_row)


if __name__ == "__main__":
	# Generate timestamps for each 15-minute interval from June 1st to June 30th, 2023
	timestamp_data = pd.date_range(start='2023-06-01', end='2023-06-30', freq='15min')
   
	# Call the function to generate and save CSV data
	generate_csv(timestamp_data)
   
	# Call the function to upload the CSV data to another system (not shown in the provided code)
	upload_data()

The dataset created represents sales information for different products captured at specific timestamps. Each row of data provides the following details:

RowNum: An identifier for each data row.
Timestamp: The date and time when the sales data was recorded.
Category: The product category of the item sold (e.g., Fashion, Automobile, Groceries, Furniture, Electronics).
UnitsSold: The number of units sold for the specific product at that timestamp.
Cost: The cost of each unit of the product.
Revenue: The total revenue generated from selling the specified number of units at the given cost.

For example, looking at the first row of data:

RowNum: 1
Timestamp: June 1st, 2023, at 00:00:00 (midnight)
Category: Fashion
UnitsSold: 938
Cost: $780.50
Revenue: $732,109.00

This means that at midnight on June 1st, 2023, 938 units of a product in the Fashion category were sold at a cost of $780.50 each, resulting in a total revenue of $732,109.00. The subsequent rows provide similar sales information for different products and timestamps, allowing for analysis of sales trends and performance over time. Here is a snapshot of the .csv data generated.

RowNum,Timestamp,Category,UnitsSold,Cost,Revenue
1,2023-06-01 00:00:00,Fashion,938,780.5,732109.0
2,2023-06-01 00:15:00,Automobile,283,206.65,58481.95
3,2023-06-01 00:30:00,Groceries,948,114.29,108346.92
4,2023-06-01 00:45:00,Groceries,927,216.14,200361.78
5,2023-06-01 01:00:00,Furniture,575,686.25,394593.75
6,2023-06-01 01:15:00,Fashion,226,327.38,73987.88
7,2023-06-01 01:30:00,Electronics,148,508.76,75296.48
8,2023-06-01 01:45:00,Fashion,686,665.87,456786.82
9,2023-06-01 02:00:00,Automobile,688,712.65,490303.2

Upload Data to Druid

The code below automates the process of uploading the data. The script first sets up the command to upload data by specifying the file containing the data and the URL of the Druid server. It then changes the current working directory to where the Druid tool is installed. Next, it tries to execute the specified command to upload the data to Druid.

If the data upload is successful, the script prints a message indicating that the data has been loaded into Druid. This script streamlines the uploading process, making it easier for users to access, analyze, and visualize their datasets.

Python

import os
import subprocess

def upload_data():
	cmd = 'bin/post-index-task --file /Users/rick/IdeaProjects/CodeProjects/druid_visualizations/insert_config.json --url http://localhost:8081'
	druid_directory = '/Users/rick/IdeaProjects/druid_2600/apache-druid-26.0.0'
	os.chdir(druid_directory)

	try:
    	output_message = subprocess.check_output(cmd, shell=True, stderr=subprocess.STDOUT)
    	print(output_message.decode())
    	print(f"Data loaded into Druid at http://localhost:8888/ according to /insert_config.json configuration.")
	except Exception as e:
    	print(f"Unexpected error occurred: {e}")

if __name__ == "__main__":
	upload_data()

Query Druid and Create Visualizations

Now that the data has been loaded into Druid the next steps are to use code to execute queries to return the data for the visualizations and then render the visualizations on the website.

Writing SQL Queries for Druid

To execute SQL queries, you need to establish a connection to the Druid cluster using the Pydruid library. The process includes selecting desired columns, specifying filters or aggregations, and defining groupings or orderings.

Fetching Data from Druid Using Python

To fetch data from Druid using Python, you can utilize the `druidapi` library to interact with the Druid API. This allows you to retrieve the desired datasets for analysis and visualization purposes by executing SQL queries. Once you have fetched the data from Druid, you can leverage the Python Plotly library to create various types of visualizations such as line charts, bar charts, and scatter plots. These visualizations provide a comprehensive way to analyze and present the data obtained from Druid.

For this solution, I utilized a table, pie chart, and 3D Scatterplot visualizations. I followed the steps documented in the next section to create the table.

Create Table

This code is a web application built using Flask, to interact with the Druid database. The application provides a user interface to view and explore the sales data in a tabular format with pagination. When a user accesses the application, it sends a SQL query to Druid to fetch the sales data with the most recent sales first. The data is then formatted and processed using the Pandas library to display timestamps in a more readable format and convert cost and revenue values into dollars and cents. The application also includes a separate route for the visualization page, which I will discuss in a later section.

The Flask, app.py code can be seen below:

Python

from flask import Flask, render_template
from druidapi import client as druid_client
import pandas as pd

app = Flask(__name__)

API_URL = "http://localhost:8888"
QUERY = """
	SELECT *
	FROM sales_data
ORDER BY "__time" DESC
"""

PER_PAGE = 20  # Adjust as needed, this will be the number of rows per page


@app.route('/', defaults={'page': 1})
@app.route('/page/<int:page>')
def load_page(page):
	druid = druid_client(API_URL)
	sql_client = druid.sql
	results = sql_client.sql(QUERY)

	data = pd.DataFrame(results)

	# Convert '__time' to a more human-readable format
	data['__time'] = pd.to_datetime(data['__time']).dt.strftime('%Y-%m-%d %H:%M:%S')

	# Format 'Cost' and 'Revenue' columns as dollars and cents
	for col in ['Cost', 'Revenue']:
    	data[col] = data[col].apply(lambda x: '${:,.2f}'.format(x))

	# Implement pagination
	start = (page - 1) * PER_PAGE
	end = start + PER_PAGE
	page_data = data.iloc[start:end]
	total_pages = len(data) // PER_PAGE + (1 if len(data) % PER_PAGE else 0)

	data_list = page_data.to_dict(orient='records')

	return render_template('table_page.html', data=data_list, page=page, total_pages=total_pages)


@app.route('/visualization')
def visualization():
	return render_template('visualization_page.html')


if __name__ == '__main__':
	app.run()

Below is the HTML (table_page.html) for the webpage that renders the table chart. This code creates a web page that displays data in a tabular format. The template is designed to showcase information fetched from a database with specific columns like “RowNum,” “Timestamp,” “Category,” “UnitsSold,” “Cost,” and “Revenue.” The web page contains a header with a logo and title, followed by a table with headers representing column names. Using a loop, the template populates the table rows with data fetched from a provided list. The template also includes pagination functionality with “Next Page” and “Previous Page” buttons to navigate between different pages of the table. There is also a button labeled “Visualizations” that, when clicked, will redirect the user to a different page to display related visualizations.

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<title>Table View</title>
	<link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='styles.css') }}">
</head>
<body>
<div class="container">
	<header class="header">
    	<img src="{{ url_for('static', filename='druid_icon.png') }}" alt="Druid Logo">
        <h1>Table View</h1>
	</header>
	<table>
    	<thead>
    	<tr>
        	<th>RowNum</th>
        	<th>Timestamp</th>
        	<th>Category</th>
        	<th>UnitsSold</th>
        	<th>Cost</th>
        	<th>Revenue</th>
    	</tr>
    	</thead>
    	<tbody>
    	{% for row in data %}
        	<tr>
            	<td>{{ row['RowNum'] }}</td>
            	<td>{{ row['__time'] }}</td>
            	<td>{{ row['Category'] }}</td>
            	<td>{{ row['UnitsSold'] }}</td>
            	<td>{{ row['Cost'] }}</td>
            	<td>{{ row['Revenue'] }}</td>
        	</tr>
    	{% endfor %}
    	</tbody>
	</table>
	<!-- Page Navigation -->
	<nav class="page-navigation">
    	{% if page < total_pages %}
        	<a href="{{ url_for('load_page', page=page+1) }}" class="button right-button" title="Next Page">→</a>
    	{% endif %}
    	{% if page > 1 %}
        	<a href="{{ url_for('load_page', page=page-1) }}" class="button" title="Previous Page">←</a>
    	{% endif %}
	</nav>
	<!-- Button to open visualization_page.html -->
	<a href="/visualization" class="button">Visualizations</a>
</div>
</body>
</html>

This page utilizes the styles.css file which is shown below:

body {
	font-family: Arial, sans-serif;
	color: #f5f5f5;
	margin: 0;
	padding: 0;
	background-color: #000000;
	display: flex;
	justify-content: space-evenly;
	align-items: stretch;
	height: 100vh;
	box-sizing: border-box;
	padding: 50px 0;
}

.container {
	width: 90%;
	margin: 0 auto;
	padding: 20px;
}
body {
	font-family: Arial, sans-serif;
	color: #f5f5f5;
	margin: 0;
	padding: 0;
	background-color: #000000;
	display: flex;
	justify-content: space-evenly;
	align-items: stretch;
	height: 100vh;
	box-sizing: border-box;
	padding: 50px 0;
}

.container {
	width: 90%;
	margin: 0 auto;
	padding: 20px;
}

.header {
	display: flex;
	align-items: center;
}

.header img {
	height: 50px;
	margin-right: 3ch;
}

.header h1 {
	color: #0bc6df;
}

table {
	width: 100%;
	border-collapse: collapse;
	margin-bottom: 20px;
	color: #f5f5f5;
}

th, td {
	border: 1px solid #ddd;
	padding: 8px;
	text-align: left;
}

th {
	background-color: #0bc6df;
}

.button {
	background-color: #0bc6df;
	border: none;
	color: white;
	padding: 10px 20px;
	text-align: center;
	text-decoration: none;
	display: inline-block;
	font-size: 14px;
	margin: 4px 2px;
	cursor: pointer;
	float: right;
	border-radius: 30px;
}

.button:hover {
	background-color: #09a7b1;
}

iframe {
	width: 100%;
	height: 100%;
	border: none;
}

.scatterplot-container {
	margin-top: -20px;
	margin-left: -20px;
}

Here is an example of the table produced:

Create Pie Chart

The code to create the Pie Chart, get_data_create_pie.py which is shown below, generates the visualization using Plotly and the Druid API. It fetches data from a Druid database through a specified API URL and SQL query, processes it, and creates a pie chart to represent the total revenue per category. The results obtained from the query are saved to a CSV file, which is then used to generate the pie chart using Plotly’s make_subplots function. The resulting pie chart is displayed and saved as an HTML file. The code is organized into several functions, each responsible for a specific task, such as fetching data from the Druid database, creating the pie chart, saving results to CSV, and saving the pie chart as an HTML file. When executed, the code produces a pie chart showing the total revenue per category and saves it as an HTML file, pie_chart.html which is shown on the visualization page.

Python

import csv
import pandas as pd
import plotly.graph_objects as go
import plotly.io as pio
from druidapi import client as druid_client
from plotly.subplots import make_subplots

# API URL and file paths for data and visualization
API_URL = "http://localhost:8888"
FILE_PATH = '/Users/rick/IdeaProjects/CodeProjects/druid_visualizations/data/results_pie_chart.csv'
HTML_PATH = 'static/pie_chart.html'

QUERY = """
	SELECT
    	Category,
    	__time,
    	SUM(Revenue) AS TotalRevenue
	FROM
    	sales_data
	WHERE
    	__time >= TIMESTAMP '2023-06-01 00:00:00'
    	AND __time < TIMESTAMP '2023-06-30 00:00:00'
	GROUP BY
    	Category, __time
"""


# Function to save query results to a CSV file
def save_results_to_csv(results, file_path):
	headers = results[0].keys()
	with open(file_path, 'w', newline='') as output_file:
    	writer = csv.DictWriter(output_file, fieldnames=headers)
    	writer.writeheader()
    	writer.writerows(results)


# Function to create a pie chart using Plotly
def create_pie_chart(df):
	fig = make_subplots(rows=1, cols=1)

	fig.add_trace(
    	go.Pie(labels=df['Category'], values=df['TotalRevenue']),
        row=1, col=1
	)

	fig.update_layout(
    	title={
        	'text': 'Total Revenue per Category',
        	'y': 0.97,
        	'x': 0.5,
        	'xanchor': 'center',
        	'yanchor': 'top'
    	},
    	legend=dict(
        	orientation="h",
        	yanchor="bottom",
        	y=1.02,
        	xanchor="right",
        	x=1
    	),
    	autosize=False,
    	width=1000,
    	height=1000,
    	margin=dict(
        	l=50,
        	r=50,
        	b=100,
        	t=100,
        	pad=10
    	)
	)
	return fig


# Function to save the pie chart as an HTML file
def save_html_pie_chart(fig, html_path):
	pie_chart_html = pio.to_html(fig, full_html=False, include_plotlyjs='cdn')
	with open(html_path, "w") as file:
    	file.write(pie_chart_html)


# Function to run the query and save results to a DataFrame
def run_query_and_save_results():
	druid = druid_client(API_URL)
	sql_client = druid.sql
	results = sql_client.sql(QUERY)
	save_results_to_csv(results, FILE_PATH)
	return pd.DataFrame(results)


if __name__ == "__main__":
	df = run_query_and_save_results()
	pie_chart = create_pie_chart(df)
	pie_chart.show()
	save_html_pie_chart(pie_chart, HTML_PATH)

Here is the code for the visualization_page.html which contains iframes for the pie chart and scatter plot.

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<title>Table View</title>
	<link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='styles.css') }}">
</head>
<body>
<div class="container">
	<header class="header">
    	<img src="{{ url_for('static', filename='druid_icon.png') }}" alt="Druid Logo">
        <h1>Table View</h1>
	</header>
	<table>
    	<thead>
    	<tr>
        	<th>RowNum</th>
        	<th>Timestamp</th>
        	<th>Category</th>
        	<th>UnitsSold</th>
        	<th>Cost</th>
        	<th>Revenue</th>
    	</tr>
    	</thead>
    	<tbody>
    	{% for row in data %}
        	<tr>
            	<td>{{ row['RowNum'] }}</td>
            	<td>{{ row['__time'] }}</td>
            	<td>{{ row['Category'] }}</td>
            	<td>{{ row['UnitsSold'] }}</td>
            	<td>{{ row['Cost'] }}</td>
            	<td>{{ row['Revenue'] }}</td>
        	</tr>
    	{% endfor %}
    	</tbody>
	</table>
	<!-- Page Navigation -->
	<nav class="page-navigation">
    	{% if page < total_pages %}
        	<a href="{{ url_for('load_page', page=page+1) }}" class="button right-button" title="Next Page">→</a>
    	{% endif %}
    	{% if page > 1 %}
        	<a href="{{ url_for('load_page', page=page-1) }}" class="button" title="Previous Page">←</a>
    	{% endif %}
	</nav>
	<!-- Button to open visualization_page.html -->
	<a href="/visualization" class="button">Visualizations</a>
</div>
</body>
</html>

This page utilizes the styles.css file which is shown below:

CSS

body {
	font-family: Arial, sans-serif;
	color: #f5f5f5;
	margin: 0;
	padding: 0;
	background-color: #000000;
	display: flex;
	justify-content: space-evenly;
	align-items: stretch;
	height: 100vh;
	box-sizing: border-box;
	padding: 50px 0;
}

.container {
	width: 90%;
	margin: 0 auto;
	padding: 20px;
}
body {
	font-family: Arial, sans-serif;
	color: #f5f5f5;
	margin: 0;
	padding: 0;
	background-color: #000000;
	display: flex;
	justify-content: space-evenly;
	align-items: stretch;
	height: 100vh;
	box-sizing: border-box;
	padding: 50px 0;
}

.container {
	width: 90%;
	margin: 0 auto;
	padding: 20px;
}

.header {
	display: flex;
	align-items: center;
}

.header img {
	height: 50px;
	margin-right: 3ch;
}

.header h1 {
	color: #0bc6df;
}

table {
	width: 100%;
	border-collapse: collapse;
	margin-bottom: 20px;
	color: #f5f5f5;
}

th, td {
	border: 1px solid #ddd;
	padding: 8px;
	text-align: left;
}

th {
	background-color: #0bc6df;
}

.button {
	background-color: #0bc6df;
	border: none;
	color: white;
	padding: 10px 20px;
	text-align: center;
	text-decoration: none;
	display: inline-block;
	font-size: 14px;
	margin: 4px 2px;
	cursor: pointer;
	float: right;
	border-radius: 30px;
}

.button:hover {
	background-color: #09a7b1;
}

iframe {
	width: 100%;
	height: 100%;
	border: none;
}

.scatterplot-container {
	margin-top: -20px;
	margin-left: -20px;
}

Here is an example of the pie chart produced:

Create 3D Scatter Plot

The code to create the Scatter Plot, get_data_create_scatter.py which is shown below, connects to the Druid database through an API URL, executing a SQL query to retrieve data about sales. The results are then saved as a CSV file. Using Plotly, the script creates a 3D scatter plot that visualizes the total units sold, average cost, and total revenue for each product category obtained from the query. The scatter plot is saved as an HTML file (scatter_plot.html) included on the visualization_page.html shown earlier.

Python

import csv
import pandas as pd
import plotly.graph_objects as go
import plotly.io as pio
from druidapi import client as druid_client

# API URL and file paths for data and visualization
API_URL = "http://localhost:8888"
FILE_PATH = '/Users/rick/IdeaProjects/CodeProjects/druid_visualizations/data/results_scatter_plot.csv'
HTML_PATH_SCATTER = 'static/scatter_plot.html'

QUERY = """
	SELECT
    	Category,
    	SUM(UnitsSold) as TotalUnitsSold,
    	AVG(Cost) as AverageCost,
    	SUM(Revenue) AS TotalRevenue
	FROM
    	sales_data
	WHERE
    	__time >= TIMESTAMP '2023-06-01 00:00:00'
    	AND __time < TIMESTAMP '2023-06-30 00:00:00'
	GROUP BY
    	Category
"""


# Function to save query results to a CSV file
def save_results_to_csv(results, file_path):
	headers = results[0].keys()
	with open(file_path, 'w', newline='') as output_file:
    	writer = csv.DictWriter(output_file, fieldnames=headers)
    	writer.writeheader()
    	writer.writerows(results)


# Function to create a 3D scatter plot using Plotly
def create_scatter_plot(df):
	categories = df['Category'].unique()
	traces = [go.Scatter3d(
    	x=df[df['Category'] == category]['TotalUnitsSold'],
    	y=df[df['Category'] == category]['AverageCost'],
    	z=df[df['Category'] == category]['TotalRevenue'],
    	text=df[df['Category'] == category]['Category'],
    	mode='markers',
    	name=category,
    	marker=dict(
        	size=8,
        	opacity=0.8
    	),
    	hoverinfo='text'
	) for category in categories]

	layout = go.Layout(
    	title={
        	'text': 'Units Sold, Cost, and Revenue',
        	'font': {
            	'size': 24,
            	'family': 'Arial Black'
        	}
    	},
    	scene=dict(
        	xaxis=dict(title='Units Sold'),
        	yaxis=dict(title='Cost'),
        	zaxis=dict(title='Revenue'),
        	aspectmode="cube"  # enforce equal aspect ratio
  	  ),
    	autosize=False,
    	width=1000,
    	height=1000,
    	margin=dict(
        	l=150,  # Increase left margin to make room for legend
        	r=50,
        	b=100,
        	t=100,
        	pad=10
    	),
    	legend=dict(
        	x=1,  # Change these values to move legend
        	y=1.5,
        	orientation="v",
        	xanchor="left",
        	yanchor="middle"
    	)
	)

	return go.Figure(data=traces, layout=layout)


# Function to save the 3D scatter plot as an HTML file
def save_html_scatter_plot(fig, html_path):
	scatter_plot_html = pio.to_html(fig, full_html=False, include_plotlyjs='cdn')
	with open(html_path, "w") as file:
    	file.write(scatter_plot_html)


# Function to run the query and save results to a DataFrame
def run_query_and_save_results():
	druid = druid_client(API_URL)
	sql_client = druid.sql
	results = sql_client.sql(QUERY)
	save_results_to_csv(results, FILE_PATH)
	return pd.DataFrame(results)


if __name__ == "__main__":
	df = run_query_and_save_results()
	scatter_fig = create_scatter_plot(df)
	scatter_fig.show()
	save_html_scatter_plot(scatter_fig, HTML_PATH_SCATTER)

Here is an example of the three-dimensional scatter plot produced:

What Have We Learned

Throughout this blog post, we’ve taken a deep dive into the world of data visualization using Python’s Plotly library and Druid. We began by briefly discussing the importance of data visualization and the key features of both Druid and Plotly, which make them indispensable tools in this domain. We then ventured into the practical application of these tools, exploring how to generate data and ingest it into Druid, and interacting with Druid using Python and SQL. We also walked through the process of creating a web application that allows us to display data in a tabular format and create intriguing visualizations like pie charts and 3D scatter plots.

Key Points:

The critical role of data visualization in analytics and decision-making
Druid’s capabilities make it suitable for high-performance analytics on large datasets
An overview of Python’s Plotly library, including its strengths and considerations for use
The process of generating sample data and uploading the data to Druid
How to interact with Druid using Python and SQL
Generating a table, pie charts, and 3D scatter plots using Python’s Plotly library to visualize the data in a Flask web application

Conclusion

In conclusion, the combination of Python’s Plotly library and Druid provides a powerful, flexible, and interactive platform for data visualization. This blog post aimed to provide a comprehensive guide for harnessing these tools, from creating and loading data, to generating intricate visualizations. The ability to transform raw data into meaningful insights is very important in today’s data-driven world. By leveraging the capabilities of Plotly and Druid, you can take a significant step in this direction. Remember, the journey of data visualization is filled with constant learning and experimentation. So, continue to explore, create, and innovate!

Next Steps

Imply Polaris, the fully managed version of Apache Druid, offers more powerful visualizations than those discussed in this blog. You can experience Polaris firsthand by signing up for a free trial. Use it to connect to your data sources or use a sample dataset to explore its many intuitive features.

About the Author

Rick Jacobs is a Senior Technical Product Marketing Manager at Imply. His varied background includes experience at IBM, Cloudera, and Couchbase. He has over 20 years of technology experience garnered from serving in development, consulting, data science, sales engineering, and other roles. He holds several academic degrees including an MS in Computational Science from George Mason University. When not working on technology, Rick is trying to learn Spanish and pursuing his dream of becoming a beach bum.

Other blogs you might find interesting

No records found...

Sep 21, 2023

Migrate Analytics Data from MongoDB to Apache Druid

This blog presents a concise guide on migrating data from MongoDB to Druid. It includes Python scripts to extract data from MongoDB, save it as CSV, and then ingest it into Druid. It also touches on maintaining...

Learn More

Sep 21, 2023

How Druid Facilitates Real-Time Analytics for Mass Transit

Mass transit plays a key role in reimagining life in a warmer, more densely populated world. Learn how Apache Druid helps power data and analytics for mass transit.

Learn More

Sep 19, 2023

Migrate Analytics Data from Snowflake to Apache Druid

This blog outlines the steps needed to migrate data from Snowflake to Apache Druid, a platform designed for high-performance analytical queries. The article covers the migration process, including Python scripts...

Learn More

Sep 15, 2023

Apache Kafka, Flink, and Druid: Open Source Essentials for Real-Time Applications

Apache Kafka, Flink, and Druid, when used together, create a real-time data architecture that eliminates all these wait states. In this blog post, we’ll explore how the combination of these tools enables...

Learn More

Sep 05, 2023

Bringing Real-Time Data to Solar Power with Apache Druid

In a rapidly warming world, solar power is critical for decarbonization. Learn how Apache Druid empowers a solar equipment manufacturer to provide real-time data to users, from utility plant operators to homeowners

Learn More

Sep 05, 2023

When to Build (Versus Buy) an Observability Application

Observability is the key to software reliability. Here’s how to decide whether to build or buy your own solution—and why Apache Druid is a popular database for real-time observability

Learn More

Aug 29, 2023

How Innowatts Simplifies Utility Management with Apache Druid

Data is a key driver of progress and innovation in all aspects of our society and economy. By bringing digital data to physical hardware, the Internet of Things (IoT) bridges the gap between the online and...

Learn More

Aug 14, 2023

Three Ways to Use Apache Druid for Machine Learning Workflows

An excellent addition to any machine learning environment, Apache Druid® can facilitate analytics, streamline monitoring, and add real-time data to operations and training

Learn More

Aug 11, 2023

Introducing Apache Druid 27.0.0

Apache Druid® is an open-source distributed database designed for real-time analytics at scale. Apache Druid 27.0 contains over 350 commits & 46 contributors. This release's focus is on stability and scaling...

Learn More

Aug 10, 2023

Unleashing Real-Time Analytics in APJ: Introducing Imply Polaris on AWS AP-South-1

Imply, the company founded by the original creators of Apache Druid, has exciting news for developers in India seeking to build real-time analytics applications. Introducing Imply Polaris, a powerful database-as-a-Service...

Learn More

Aug 03, 2023

Embedding Visualizations using React and Express

In this guide, we will walk you through creating a very simple web app that shows a different embedded chart for each user selected from a drop-down. While this example is simple it highlights the possibilities...

Learn More

Jul 25, 2023

Apache Druid: Making 1000+ QPS for Analytics Look Easy

This 2-part blog post explores key technical considerations to support high QPS for analytics and the strengths of Apache Druid

Learn More

Jul 25, 2023

Things to Consider When Scaling Analytics for High QPS

This 2-part blog post explores key technical considerations to support high QPS for analytics and the strengths of Apache Druid

Learn More

Jul 20, 2023

Automate Streaming Data Ingestion with Kafka and Druid

In this blog post, we explore the integration of Kafka and Druid for data stream management and analysis, emphasizing automatic topic detection and ingestion. We delve into the creation of 'Ingestion Spec',...

Learn More

Jul 12, 2023

Schema Auto-Discovery with Apache Druid

This guide explores configuring Apache Druid to receive Kafka streaming messages. To demonstrate Druid's game-changing automatic schema discovery. Using a real-world scenario where data changes are handled...

Learn More

Jul 11, 2023

What’s new in Imply Polaris – Q2 2023

Imply Polaris, our ever-evolving Database-as-a-Service, recently focused on global expansion, enhanced security, and improved data handling and visualization. This fully managed cloud service, based on Apache...

Learn More

Jun 06, 2023

Introducing hands-on developer tutorials for Apache Druid

The objective of this blog is to introduce the new set of interactive tutorials focused on the Druid API fundamentals. These tutorials are available as Jupyter Notebooks and can be downloaded as a Docker container.

Learn More

Jun 01, 2023

Introducing Schema Auto-Discovery in Apache Druid

In this blog article I’ll unpack schema auto-discovery, a new feature now available in Druid 26.0, that enables Druid to automatically discover data fields and data types and update tables to match changing...

Learn More

May 30, 2023

Exploring Unnest in Druid

Druid now has a new function, Unnest. Unnest explodes an array into individual elements. This blog contains design methodology and examples for this new Unnest function both from native and SQL binding perspectives.

Learn More

May 28, 2023

What’s new in Imply Polaris – Our Real-Time Analytics DBaaS

Every week we add new features and capabilities to Imply Polaris. This month, we’ve expanded security capabilities, added new query functionality, and made it easier to monitor your service with your preferred...

Learn More

May 24, 2023

Introducing Apache Druid 26.0

Apache Druid® 26.0, an open-source distributed database for real-time analytics, has seen significant improvements with 411 new commits, a 40% increase from version 25.0. The expanded contributor base of 60...

Learn More

May 22, 2023

ACID and Apache Druid

ACID and Druid, an interesting dive into some of the Druid capabilities in the light of ACID compliance

Learn More

May 21, 2023

How to Build a Sentiment Analysis Application with ChatGPT and Druid

Leveraging ChatGPT for sentiment analysis, when combined with Apache Druid, offers results from large data volumes. This integration is easily achievable, revealing valuable insights and trends for businesses...

Learn More

May 21, 2023

Snowflake and Apache Druid

In this blog, we will compare Snowflake and Druid. It is important to note that reporting data warehouses and real-time analytics databases are different domains. Choosing the right tool for your specific requirements...

Learn More

May 20, 2023

Learn how to achieve sub-second responses with Apache Druid

Learn how to achieve sub-second responses with Apache Druid. This article is an in-depth look at how Druid resolves queries and describes data modeling techniques that improve performance.

Learn More

May 19, 2023

Apache Druid – Recovering Dropped Segments

Apache Druid uses load rules to manage the ageing of segments from one historical tier to another and finally to purge old segments from the cluster. In this article, we’ll show what happens when you make...

Learn More

May 18, 2023

Real-Time Analytics: Building Blocks and Architecture

This blog identifies the key technical considerations for real-time analytics. It answers what is the right data architecture and why. It spotlights the technologies used at Confluent, Reddit, Target and 1000s...

Learn More

May 17, 2023

Transactions Come and Go, but Events are Forever

For decades, analytics has focused on Transactions. While Transactions are still important, the future of analytics is understanding Events.

Learn More

May 16, 2023

What’s new in Imply Polaris – Our Real-Time Analytics DBaaS

This blog explains some of the new features, functionality and connectivity added to Imply Polaris over the last two months. We've expanded ingestion capabilities, simplified operations and increased reliability...

Learn More

May 15, 2023

Elasticsearch and Druid

This blog will help you understand what Elasticsearch and Druid do well and will help you decide whether you need one or both to reach your goals

Learn More

May 14, 2023

Wow, that was easy – Up and running with Apache Druid

The objective of this blog is to provide a step-by-step guide on setting up Druid locally, including the use of SQL ingestion for importing data and executing analytical queries.

Learn More

May 13, 2023

APACHE DRUID

IMPLY PRODUCTS

INTEGRATIONS

By Functional Use

By Application

FEATURED

DRUID CASE STUDIES

LEARN MORE

CONNECT WITH US

GET SUPPORT

FEATURED

COMPANY

NEWS

Other blogs you might find interesting

Let us help with your analytics apps