Over the past few weeks, I stumbled upon a great way to access and analyze Hive blockchain data without needing to run my own node or deal with slow complex API calls. Enter HiveSQL—a powerful tool that lets you query Hive data like a pro!
In this blog, I'll walk you through the basics of setting up HiveSQL and using Python to extract useful data. If you've ever wanted to dive deeper into Hive's data, create custom analytics, or just explore what's happening on the blockchain, you're in the right place.
Before we get started, you'll need:
✅ Some basic Python knowledge
✅ A HiveSQL account (I'll show you how to get one)
✅ A bit of patience for the setup—trust me, it’s worth it!
HiveSQL is a publicly available Microsoft SQL Server database that stores Hive blockchain data in a structured and easily accessible way. Instead of running your own Hive node or scraping data from public APIs, you can use SQL queries to quickly retrieve the information you need.
✔ Faster queries – No need to download blockchain data manually
✔ Easy access – Works with any tool that can connect to an MS-SQL database
✔ Powerful analysis – Great for running reports, tracking trends, and finding valuable insights
Rather than making countless API calls, you simply send an SQL query to the HiveSQL server and get back exactly the data you need—fast and efficient!
➡ Support the HiveSQL proposal to keep it running for free!
HiveSQL Proposal - Keep It Free To Use For The Community
To access HiveSQL, you’ll need an account. There’s a small one-time registration fee of 1 HBD to prevent spam.
Follow this guide to register:
🔗 HiveSQL Registration Guide
If you're on Windows, you’ll need to install the ODBC driver to connect to HiveSQL.
To perform SQL queries using Python, install the official Microsoft ODBC Driver 17 (18) for SQL Server (x64):
🔗 Download Here
➡For Other OS platform check out this link:
HiveSQL for Python developers
ODBC (Open Database Connectivity) is a standard way to connect applications (like Python) to databases (like HiveSQL). It acts as a bridge between your code and the database, allowing you to send SQL queries and receive results.
Now that HiveSQL is set up, let’s install the necessary Python packages:
pip install pypyodbc pandas plotly
Here's what each package does:
📌 pypyodbc – Connects to the SQL Server and runs queries
📌 pandas – Handles large datasets efficiently
📌 plotly – Creates beautiful interactive charts
The setup process might seem a bit tedious, but you only have to do it once. Now comes the fun part—querying Hive data and visualizing it! Stay tuned for the next post, where we’ll start pulling real data from the blockchain! 🚀
Now that we’ve set up HiveSQL, it’s time to write some Python code to pull data and visualize it. First, we need to set up a connection to the database and create a function that runs SQL queries and returns a pandas DataFrame. This will make it super easy to work with the data later.
Before we start querying, let's set up a function to connect to HiveSQL and execute SQL queries. This function will:
✅ Establish a connection to the database
✅ Run a given SQL query
✅ Return the results as a pandas DataFrame
import logging
import pypyodbc
import pandas as pd
db_username = ""
db_password = ""
# Connection string using secrets
connection_string = f"""
Driver={{ODBC Driver 17 for SQL Server}};
Server=vip.hivesql.io;
Database=DBHive;
UID={db_username};
PWD={db_password};
TrustServerCertificate=yes;
Encrypt=yes
"""
# Setup logger
log = logging.getLogger()
def execute_query_df(query):
"""
Executes a SQL query and returns a Pandas DataFrame.
Parameters:
- query: str, the SQL query to execute.
Returns:
- pd.DataFrame with query results or an empty DataFrame if there's an error or no results.
"""
connection = None
try:
# Setup connection
connection = pypyodbc.connect(connection_string)
cursor = connection.cursor()
# Execute the query
cursor.execute(query)
# Fetch column names dynamically from the cursor description
columns = [column[0] for column in cursor.description] if cursor.description else []
# Fetch results
result = cursor.fetchall()
return pd.DataFrame(result, columns=columns) if result else pd.DataFrame(columns=columns)
except pypyodbc.Error as e:
log.error(f"Database error: {e}")
return pd.DataFrame() # Return an empty DataFrame on error
finally:
if connection:
connection.close()
Now that we have our connection set up, let’s start running queries and visualizing the data! 🎉
If you’re not familiar with SQL, don’t worry! You can always search online for SQL query examples, or just ask ChatGPT to help you. 😆
Let’s start with a simple query that pulls the top 100 accounts with the highest balances and plots them in a bar chart.
Now let’s run this query and visualize the results using Plotly.
import plotly.express as px
if __name__ == "__main__":
df = execute_query_df("""
SELECT TOP 100 name, balance
FROM Accounts
WHERE balance > 1000
ORDER BY balance DESC;
""")
fig = px.bar(df, x='name', y='balance', title="Top 100 Hive Accounts by Balance")
fig.show()
With just a few lines of code, we've queried HiveSQL and generated a bar chart of the top accounts by balance.
Why Use Plotly?
✔ Interactive charts – Hover over bars to see exact values
✔ Easy customization – Change colors, labels, and chart types
✔ Great for data analysis – Perfect for exploring Hive data
The possibilities are endless when it comes to what data you can query and visualize. Plotly allows you to tweak almost everything—axis labels, colors, chart types, and even animations!
Here’s the full script in case you want to copy and paste it:
Click to expand full code
import logging import pypyodbc import pandas as pd import plotly.express as px db_username = "" db_password = "" # Connection string using secrets connection_string = f""" Driver={{ODBC Driver 17 for SQL Server}}; Server=vip.hivesql.io; Database=DBHive; UID={db_username}; PWD={db_password}; TrustServerCertificate=yes; Encrypt=yes """ # Setup logger log = logging.getLogger() def execute_query_df(query): """ Executes a SQL query and returns a Pandas DataFrame. Parameters: - query: str, the SQL query to execute. Returns: - pd.DataFrame with query results or an empty DataFrame if there's an error or no results. """ connection = None try: # Setup connection connection = pypyodbc.connect(connection_string) cursor = connection.cursor() # Execute Query cursor.execute(query) # Fetch column names dynamically from the cursor description columns = [column[0] for column in cursor.description] if cursor.description else [] # Fetch results result = cursor.fetchall() return pd.DataFrame(result, columns=columns) if result else pd.DataFrame(columns=columns) except pypyodbc.Error as e: log.error(f"Database error: {e}") return pd.DataFrame() # Return empty DataFrame on error finally: if connection: connection.close() if __name__ == "__main__": df = execute_query_df(""" SELECT TOP 100 name, balance FROM Accounts WHERE balance > 1000 ORDER BY balance DESC; """) fig = px.bar(df, x='name', y='balance', title="Top 100 Hive Accounts by Balance") fig.show()
I know this is just the basics, and many Hive users in the community might already be familiar with these methods (or even have better ones!). But if you're new to HiveSQL, I hope this guide helps you get started!
If you’re interested in more advanced topics, let me know in the comments! 🚀 Some possible ideas for future blogs:
📌 More advanced SQL queries – Finding active users or trending posts
📌 Better visualizations – combining chart and updating layouts
📌 Deploying a live app – Making an interactive Hive data dashboard
Let me know what you’d like to see next! As always, feedback is welcome, and I hope you enjoyed this post. 😃