Welcome back! In my last post, Getting Started with HiveSQL: Crawling Hive Blockchain Data with Python, I showed how easy it is to retrieve data from the Hive blockchain using HiveSQL.
This time, we're going a step further—turning that data into a runnable web app that anyone can access online. We’ll also explore more advanced HiveSQL queries to pull interesting insights.
To follow along, you should have some basic knowledge of GitHub, since we'll be working with repositories. And for building our web app, we’ll use Streamlit—a powerful, lightweight framework for creating Python-based web applications.
If you enjoy this content and want to keep using HiveSQL for free, consider supporting this proposal. HiveSQL is an amazing tool for crawling and analyzing Hive blockchain data, and I fully support keeping it available to the community! 😍
A special shoutout to 
Streamlit is an open-source Python library that makes it ridiculously easy to build and share web applications for data visualization and analysis.
With just a few lines of code, you can create interactive dashboards and deploy them online without dealing with complex front-end frameworks. It’s perfect for quickly sharing insights from HiveSQL queries.
In my previous post, we already covered how to run queries against the HiveSQL database. Now, let's organize the code into a separate module (a standalone Python file).
I've also made a few improvements:
✅ It automatically detects which ODBC driver you have installed (thanks to a contribution from 
✅ It dynamically determines column names and converts SQL types to Pandas-friendly data types.
Before diving into the code, make sure you’ve created a GitHub repository to store your project.
I have created for this example the following repository which you can use as reference:
https://github.com/gamerbeaker007/HiveSQL
One important step:
🚨 Never commit your secrets.toml file! 🚨
This file contains sensitive credentials for accessing HiveSQL, so always add it to your .gitignore to prevent it from being uploaded. Keep it only on your local machine for testing.
You can use any Python IDE (Integrated Development Environment) for this project. I personally use PyCharm from JetBrains. The free Community Edition is more than enough for this use case. If you prefer, you can also use VS Code, Jupyter Notebook, or any other Python editor you're comfortable with.
Before diving into the code, let’s install the necessary Python packages. Open a terminal and run:
pip install pypyodbc pandas plotly streamlit
Let’s go over some key files that help set up our Streamlit application.
This file controls how Streamlit runs locally. For example, it can auto-refresh when you update your code, so you don’t have to restart the server manually.
# Needed for local development with Docker
[server]
runOnSave = true
headless = true
fileWatcherType = "poll"
This file contains sensitive information (like your HiveSQL username and password) that should never be committed to GitHub. Always add this file to .gitignore (more on that below).
[database]
username = ""
password = ""
💡 Tip: Keeping your credentials in secrets.toml ensures they remain private and secure.
This file contains the execute_query_df function from the previous blog, which runs queries against HiveSQL and returns the results as a Pandas DataFrame. We’re keeping it separate so we can reuse it across different applications.
This optional module sets up a logger to help you debug and track issues when the application is running. It’s especially useful after deploying your Streamlit app, as it helps you see what's happening behind the scenes.
This is the actual Streamlit app where we define the interface and display HiveSQL data. We'll take a deeper look at this in the next section.
This file tells Git which files should NOT be uploaded to your GitHub repository.
Example .gitignore file:
/venv/
/**/secrets.toml
/.idea/
This file lists all the Python packages required to run your application.
# streamlit~=1.41.1
pandas~=2.2.3
pypyodbc~=1.3.6
plotly~=5.24.1
💡 Note: Streamlit itself is not included in requirements.txt because it’s automatically provided by the Streamlit deployment platform.
Now that we have a structured project, in the next section, we’ll dive into the Streamlit application code and build an interactive dashboard for HiveSQL data. 🚀
The example_1_app.py file contains the complete Streamlit application that will be deployed. Let's break it down step by step.
1️⃣import logging
import plotly.express as px
import streamlit as st
from api import hive_sql
from util import log_util
2️⃣log_util.config_logger()
log = logging.getLogger("Example 1")
3️⃣st.set_page_config(layout="wide")
4️⃣st.title("Lets start with Example 1 Streamlit 🎉")
st.markdown("""
In this first example, we will combine the previous tutorial
on your first query with building a simple single-page application\n
We'll also demonstrate how easy it is to deploy using Streamlit.
""")
st.subheader("Simple quick query")
5️⃣if st.button("Run Top 100 query"):
log.info("Run Top 100 query")
6️⃣df = hive_sql.execute_query_df("""
SELECT TOP 100 name, balance
FROM Accounts
WHERE balance > 1000
ORDER BY balance DESC;
""")
7️⃣if df.empty:
log.error("No query result")
else:
log.info(f"Query result rows: {df.index.size}")
fig = px.bar(df, x='name', y='balance', title="Top 100 Hive Accounts by Balance")
8️⃣st.plotly_chart(fig, theme="streamlit")
1️⃣ Importing Required Modules
logging → Helps track errors and debugging information.
plotly.express → Used for interactive charts and visualizations.
streamlit → Our web app framework.
hive_sql → Our module that handles HiveSQL queries.
log_util → Our custom logger to track actions.
2️⃣ Setting Up the Logger (Optional)
Initializes a logger to track when the query is run.
Useful for debugging and logging errors when the app is deployed.
3️⃣ Setting the Page Layout (Optional)
layout="wide" → Expands the app to use the full width of the screen.
Default layout is centered, but wide mode makes data visualizations easier to read.
4️⃣ Adding a Title and Description
Sets the main title at the top of the page.
st.markdown() allows for multi-line text formatting.
Uses Markdown for styling (new lines, bold text, links, etc.).
Provides an introduction to the app.
5️⃣ Adding a Query Button
Creates a button labeled "Run Top 100 query".
When clicked, it logs the action and executes the HiveSQL query.
6️⃣ Executing the HiveSQL Query
Runs a HiveSQL query to get the top 100 accounts with a balance greater than 1000.
The query result is stored in a Pandas DataFrame (df).
7️⃣ Handling Query Results
If no results are returned, logs an error.
If results are found, logs the number of rows retrieved.
8️⃣ Displaying the Query Results as a Chart
Uses Plotly to generate a bar chart.
X-axis: Account name
Y-axis: Account balance
st.plotly_chart(fig) displays the chart in the Streamlit app.
For those who want to dig deeper, here’s a more advanced HiveSQL query that provides a richer dataset.
This time, when you press the button, a spinner will appear to indicate that data is being fetched. The query results are then processed, and we calculate the share of HP (Hive Power) per community.
🔍 What Does This Query Do?
st.subheader("More advanced query")
number_of_communities = hive_sql.execute_query_df("SELECT COUNT(name) as count FROM Communities").iloc[0]['count']
st.markdown(f"""
Now, we are going to build a query that analyzes Hive communities. \n
Out of {number_of_communities} total communities, we will identify the top 10 with the highest number of subscribers.\n
We will then retrieve the community name (title) and gather all subscribers' vesting shares,
converting them into Hive Power (HP). This will allow us to see which community holds the most HP based on its subscribed members.
""")
if st.button("Run Top 10 Communities query"):
log.info("Run Top 10 Communities query")
with st.spinner("Loading data... this can take a while"):
df = hive_sql.execute_query_df("""
WITH TopCommunities AS (
SELECT TOP 10 community, COUNT(subscriber) AS subscriber_count
FROM CommunitiesSubscribers
GROUP BY community
ORDER BY subscriber_count DESC
),
CommunityMembers AS (
SELECT cs.community, cs.subscriber AS account_name
FROM CommunitiesSubscribers cs
JOIN TopCommunities tc ON cs.community = tc.community
)
SELECT cm.account_name, cm.community, c.title, a.vesting_shares
FROM CommunityMembers cm
JOIN Communities c ON cm.community = c.name
JOIN Accounts a ON cm.account_name = a.name;
""")
conversion_factor = hive_sql.get_hive_per_mvest() / 1e6
df["hp"] = conversion_factor * df["vesting_shares"]
df = df.groupby('title', as_index=False)['hp'].sum()
df = df.sort_values(by='hp', ascending=False).reset_index(drop=True)
fig = px.bar(df, x='title', y='hp', title="Top 10 Communities and there HP")
st.plotly_chart(fig, theme="streamlit")
Now that we’ve gone through the code, it’s time to run it yourself and even deploy it online so others can access it!
To test your app locally, open your terminal and navigate to the application directory (inside your repository). Then run:
cd HiveSQL/example-1
streamlit run example_1_app.py
📌 Why navigate to the folder first?
If your repository contains multiple applications, you need to make sure you’re in the right directory before running the command.
Once the app starts, open your browser and go to:
👉 http://localhost:8501/
You should now see your HiveSQL Streamlit app running on your machine! 🎉
The real fun begins when we deploy the app so others can use it! Follow these simple steps to deploy your Streamlit app with just a few clicks.
1️⃣ Commit Your Code to GitHub
Make sure all your changes are pushed to GitHub.
3️⃣ Create a New Deployment
In your locally running app you can start deploying the app
Here is the live running app of this example:
https://hivesql-example1.streamlit.app/
Once deployed, you can check your app’s logs (only visible to you).
If your app isn’t used for a while, Streamlit automatically stops it to save resources. If this happens, you’ll see this screen:
No worries! Just click "Wake Up", and your app will be back online.
At first glance, this might seem like a lot of steps, but once you get the hang of it, it’s actually pretty easy! 🚀
Streamlit is incredibly powerful, and this is just the beginning—you can build much more complex applications with interactive features, dashboards, and even machine learning models.
If you're ever stuck on something, don’t hesitate to use AI!
Need help with Streamlit? Ask ChatGPT to generate sample code.
Struggling with a SQL query? AI can suggest optimized queries.
Debugging issues? AI can help explain errors and suggest fixes.
💡 Pro Tip: Always try to understand the AI-generated code before copying and pasting it. If you don’t take the time to learn how it works, you’ll struggle when things break or need tweaking. Make it a learning experience so you can build your skills over time!
That’s it for this blog! If you found it helpful or have suggestions for what you'd like to see next, drop a comment below. I’d love to cover more advanced Streamlit features, SQL optimizations, or other Hive blockchain topics in a future post!
Until next time, happy coding! 🚀🔥
PS: here you can find the example code in case you missed it:
https://github.com/gamerbeaker007/HiveSQL