HeidiSQL
HeidiSQL is an open source tool that allows you to connect to the SQL databases MySQL, SQL Server, and PostgreSQL. You can write queries and save them locally to easily run again in the future.
What Will I Learn?
I'll outline how you can download and get started with HeidiSQL generally, and particularly with SteemSQL.
Requirements
I'm running Windows 10, but the download page below notes that you can run it in Wine on Linux and OS X but I can't speak to that personally so please comment if you've tried this out.
Difficulty
- Basic
Tutorial Contents
- Download HeidiSQL
- Create Session
- Connect to SteemSQL
- Basic Tables
- Comments
- Accounts
- Followers
- TxVotes
- Writing Queries
- Joining Tables
- Example Queries
- Important Notes
- Conclusion
Download
You can quickly download HeidiSQL here: https://www.heidisql.com/download.php
The Portable version runs in place without any install, while the Installer will run through your normal routine. This is a personal preference but if you're not very technical the Installer is probably your best bet. There's nothing tricky in the install, basically the standard Next, Next, Next...Finish process.
Create Session
When you first open HeidiSQL you are presented with the Session Manager which allows you to create new sessions, place them in folders, and connect to each of them. Click the New button to start creating a session:
Connect to SteemSQL
SteemSQL is a public Microsoft SQL Server containing all of the data in the Steem blockchain and is managed by @arcange. All due thanks to him for providing public access to this data!
As shown in the image above and outlined at http://steemsql.com/ you can connect with these credentials:
Server: sql.steemsql.com
User: steemit
Password: steemit
Database: DBSteem
Port: 1433
Once connected we can see all of the tables:
Now, technically there are no tables at all and instead every accessible object in the database is a View constructed from the tables. This really doesn't matter but the more technical among us might be interested. In time, I am hoping to better understand these views and how they were constructed from the underlying data.
Basic Tables
As you can see, there are a lot of tables. My exploration of SteemSQL is barely a week old, so this is pretty light today but I hope to dig in SO much deeper. These are just a few of the obvious tables to look at and start playing around.
Comments
This table contains all Posts as well as Replies thereto. That's may be a little confusing, but it's easy to handle.
Accounts
As the name implies, these are all of the accounts.
Followers
If you wanna know who is following a particular account this table will tell you.
TxVotes
This table shows all of the votes.
This isn't even scratching the surface, but I wanted to give you a starting point to jump off and continue exploring on your own.
Writing Queries
Now, this is where it starts to get fun!
When you open HeidiSQL there will be an empty Query tab waiting for you to start constructing your first SQL statement.
A cool feature that comes in really handy when there are a ton of fields and you're still pretty unfamiliar is the dotted auto-population of fields. Ok, I made those words up so let me explain. If you type out a full table name in the query tab, for example Comments and you then type a period on the end (no space) and then wait, a list of all of the fields in that table will appear:
Especially now when this is all new to you, it's easy to simply type the table name and hit dot to see the available fields.
Important Note
This database contains the entire blockchain so be careful with your queries or it may take forever to complete. A simple fix is to limit your queries to only a few records by including SELECT TOP 10 in all your queries, where 10 is the total number of records to return.
Joining Tables
Most of these tables have relationships to other tables that we can join on to get further insights.
I'll run through a few of these joins across the tables I've mentioned.
Accounts to Followers
FROM Accounts
INNER JOIN Followers
ON Accounts.name = Followers.following
Comments to Votes
FROM Comments
INNER JOIN TxVotes
ON Comments.author = TxVotes.author
AND Comments.permlink = TxVotes.permlink
Accounts to Comments
FROM Accounts
INNER JOIN Comments
ON Accounts.name = Comments.author
Example Queries
And of course, a few example queries for fun!
Most Recent Posts
We can get the 10 most recent posts with this query:
SELECT TOP 10 'https://steemit.com/@' + author + '/' + permlink AS URL
FROM Comments
WHERE parent_author = ''
ORDER BY created DESC
All of the replies have a parent_author referring to the author of the original post, while this field is empty for the post itself so we add the WHERE parent_author = '' to restrict our query to just posts and not replies. You can reuse this clause in your queries if you want to look only at Posts and not Replies.
Accounts with Most Followers
SELECT TOP 10 Accounts.name, COUNT(*) AS followers
FROM Accounts
INNER JOIN Followers
ON Accounts.name = Followers.following
GROUP BY Accounts.name
ORDER BY COUNT(*) DESC
Posts with Most Votes in Last 24 Hours
SELECT TOP 10 'https://steemit.com/@' + Comments.author + '/' + Comments.permlink AS URL, COUNT(*) AS votes
FROM Comments
INNER JOIN TxVotes
ON Comments.author = TxVotes.author
AND Comments.permlink = TxVotes.permlink
WHERE created > DATEADD(day, -1, CURRENT_TIMESTAMP)
GROUP BY Comments.author, Comments.permlink
ORDER BY COUNT(*) DESC
Accounts with Most Posts in Last 30 Days
SELECT TOP 10 Accounts.name, COUNT(*)
FROM Accounts
INNER JOIN Comments
ON Accounts.name = Comments.author
WHERE Comments.parent_author = ''
AND Comments.created > DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY Accounts.name
ORDER BY COUNT(*) DESC
Important Notes
Some of these queries are slow and depending on what you write they can get really slow. Several of these joins I've outlined are across text fields which really contributes to the degradation of performance as we normally want to join across numeric fields.
That said, the time will come when your query just keeps running and HeidiSQL either does nothing, or completely refuses to respond. While you can try clicking around with your mouse to End Task, that has varying results so the best way is to go to the command line and run this:
taskkill /IM heidisql.exe /F
Conclusion
This should be enough to get you started writing some fun queries and exploring the blockchain. It's almost embarrassing to admit how much I like to sit and write queries, so I was beyond excited to learn about this massive dataset that I can explore. As I learn more about SteemSQL, I'll be sure to post updates with all my insights
I'm trying to connect more with the Steemit community, particularly those digging into the technology side of things like this, so please share any tips to help me engage more deeply with everyone here!
Posted on Utopian.io - Rewarding Open Source Contributors
Thank you for the contribution. It has been approved.
You can contact us on Discord.
[utopian-moderator]
Sorry about that! I went back and implemented the template so it should be gtg now.
Hey @blervin I am @utopian-io. I have just upvoted you!
Achievements
Suggestions
Get Noticed!
Community-Driven Witness!
I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x
Great article.....
Thanks so much!
Nice post !!
Thanks, I appreciate it!