Introduction

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.

---

🚨 Important Announcement

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 @arcange for the great support—especially when I accidentally blocked my own IP during a test run because I used the actual database instead of my mocked code. Lesson learned! 🤣

---

What is Streamlit?

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.

Let's Get Started

Structuring the Project

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 @kalkulus ).
✅ It dynamically determines column names and converts SQL types to Pandas-friendly data types.

Setting Up the Repository

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.

Setting Up the Streamlit Application

Choosing a Python IDE

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.

Installing Required Packages

Before diving into the code, let’s install the necessary Python packages. Open a terminal and run:

pip install pypyodbc pandas plotly streamlit

• pypyodbc → Connects to the HiveSQL database
• pandas → Handles and processes tabular data
• plotly → Enables interactive charts and visualizations
• streamlit → Builds the web app

Important Files in a Streamlit Project

Let’s go over some key files that help set up our Streamlit application.

1️⃣

config.toml – Streamlit Configuration

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"

• runOnSave = true → Automatically refreshes the app when you modify the code (no need to restart the server).
• headless = true → Prevents a new browser window from opening every time you restart the server.
• fileWatcherType = "poll" → Ensures file changes are detected when runOnSave is enabled.

2️⃣

secrets.toml – Store Sensitive Credentials

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.

3️⃣

hive_sql.py – Query Execution Module

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.

4️⃣

log_util.py – Logging Utility

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.

5️⃣

example_1_app.py – The Main Streamlit Application

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.

6️⃣

.gitignore – Prevent Unwanted Files from Being Committed

This file tells Git which files should NOT be uploaded to your GitHub repository.

Example .gitignore file:

/venv/  
/**/secrets.toml  
/.idea/

• /venv/ → Excludes the virtual environment directory
• /**/secrets.toml → Ensures credentials are never committed
• /.idea/ → Excludes PyCharm project-specific files

7️⃣

requirements.txt – Dependencies for Deployment

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.

What's Next?

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. 🚀

Understanding example_1_app.py

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")

🛠️

Breaking Down the Code

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.

🎁

Bonus: More Advanced Query

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?

• Fetches all Hive communities and their subscribed members.
• Retrieves the HP (Hive Power) of each member.
• Aggregates the total HP per community by summing up the HP of its members.
• Groups the results by community.
• Displays a bar chart of the top 10 communities holding the most HP.

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")

🚀

How to Deploy Your Streamlit App

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!

🏃

Running the App Locally

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! 🎉

🌍

Deploying Your App Online

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/

Viewing Logs & Monitoring

Once deployed, you can check your app’s logs (only visible to you).

Waking Up a Stopped App

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.

Closing Notes

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.

💡

Using AI to Make Things Easier

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