HeidiSQL and SteemSQL, Steem Blockchain data at your fingertips

Hi Steemians,

this tutorial is about the great open source tool HeidiSQL and how you can use this tool in combination with Steemsql by @Arcange to have access to all data that is currently on the Steem Blockchain.

I will start easy on all of you, so there is no knowledge in SQL needed ( i will explain the first steps ).

First of all, we have to download and install the software, therefore please visit the HeidiSQL Homepage and choose the appropriate link. The Installer worked just fine for me.

Here is a picture to illustrate the process:

After successfully finishing the download please install the tool to the location you want.
After this is completed please check the checkbox to launch HeidiSQL and you will see the following screen asking you for a server to connect to.

The settings that you can see marked in red are the ones that you need to input. If you want to see them for yourself please take a look the page of @arcange SteemSQL
The password is the same as the username, simply steemit in non capital letters.
The Open button starts the connection attempt to the server.

When the connection has been successfully established you will see the following screen, showing you the details of the SQL version of the Steem Blockchain.

I know that this screenshot is a bit overwhelming, therefore i have used colors to divide up the 3 main sections of the screen.

To the left, with the red rectangle around it is the Database content screen, where we can select tables to be displayed in more detail.
This is done on the right side within the blue rectangle, this is the detail window showing details and also the results or our queries later on.
The yellow rectangle in the lower part of the screen is the Area where the status messages are displayed.

All set up, ready to go!

So now we are all set up, connection is working and the information of the blockchain is at your fingertips. What will you do first ?

Ah wait, there is one important thing not yet discussed, since this is a SQL Database we need to use SQL (structured query language) to access the data within.
To be able to write SQL in Heidi you need to click in the Detail Window on the blue arrow reading Query besides it.

Ok, lets take a look how this SQL works. You need to do a selection on a table and filter this by a few criteria.
As a first example you want to take a look a yourself and see when your account has been created. Account related information is within the view Accounts.
The SQL for such a query would be the following: SELECT * FROM Accounts WHERE "name" = 'lanatir'
Two things i would like to mention, in most cases you should select exactly what you want to have and not take the * and mind all the characters, the ticks are important.

The result should look something like this:

The Accounts view holds masses of data, use your own name and scroll to the right in the results window. It is impressive.

Ok, this is nothing new you know most of these things anyway. But how about letting you know who has been talking about you today?
The SQL for this looks like this: SELECT author, title, body, permlink FROM TxComments WHERE "body" like '%lanatir%' and "timestamp" > '2017-12-09'

Sadly this Database is not indexed so we can not use the CONTAIN function, or i am just to dumb today :)
Therefore please be very very cautious with this query. If you change the timestamp to a longer period it will take very long to complete.

Or you want to see who sent today SBD to a voting bot?
The SQL for this looks like this: SELECT "from", "to", amount FROM TxTransfers WHERE "amount_symbol" = 'SBD' AND "timestamp" > '2017-12-09' AND "memo" LIKE 'http%'

This is the result for this query today ( it contains 2997 rows ) :

As you can see there is a lot of data waiting to be analyzed!

If this has been of value and you would want to know more, please write a comment, there is much more to say!

Thanks alot for reading
Kind regards
Lanatir
ps. thanks to @arcange for the SteemSQL, it works like a charm!

---

Posted on Utopian.io - Rewarding Open Source Contributors

---