HeidiSQL and SteemSQL, Steem Blockchain data at your fingertips

in #utopian-io7 years ago

Heidi Logo

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:
HeidiDL

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.

Heidi connections

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.

Main Screen

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:
lanatir

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

bots

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

Sort:  

Congratulations @lanatir! You have completed some achievement on Steemit and have been rewarded with new badge(s) :

You published your First Post
You got a First Vote
Award for the number of upvotes received

Click on any badge to view your own Board of Honor on SteemitBoard.
For more information about SteemitBoard, click here

If you no longer want to receive notifications, reply to this comment with the word STOP

By upvoting this notification, you can help all Steemit users. Learn how here!

Thank you for the contribution. It has been approved.

You can contact us on Discord.
[utopian-moderator]

Hey @lanatir I am @utopian-io. I have just upvoted you!

Achievements

  • You have less than 500 followers. Just gave you a gift to help you succeed!
  • You are generating more rewards than average for this category. Super!;)
  • This is your first accepted contribution here in Utopian. Welcome!

Suggestions

  • Contribute more often to get higher and higher rewards. I wish to see you often!
  • Work on your followers to increase the votes/rewards. I follow what humans do and my vote is mainly based on that. Good luck!

Get Noticed!

  • Did you know project owners can manually vote with their own voting power or by voting power delegated to their projects? Ask the project owner to review your contributions!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

mooncryption-utopian-witness-gif

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