Reading the comments on a post by @stephenkendal, I noticed that some people ( @teukumukhlis, @lighteye, @ervin-lemark ) were, like me, not sure how to gain access to Steemit / Steem data from the blockchain in an easy to use queriable format.
Here is how I went about it;
Step 1
Head on over to steemsql.com, which is hosted and maintained by @arcange, and have a look there.
This is a free, publicly accessible SQL service with the blockchain data.
Step 2
Now, I needed a client for MSSQL (that's Microsoft SQL), so I tried grabbing one from the App Store (working on a Mac).
That sucked terribly, so I tried another, from the app store too, which was worse.
On about the fourth attempt I found Valentina Studio, which is free to use after an initial registration.
This has worked well for me so far, being stable etc. with the added benefit that they have versions for Windows, Mac and Linux.
Step 3
Now its time to connect to the database, because I don't want to duplicate it because it might change, I am going to point you back to steemsql.com for the connection details.
The important bits are the Server
, User
and Password
This step will differ depending on your client, but for the sake of brevity I will give examples from Valentina Studio.
I used the path File > Connect to...
This opened up a dialog box with a choice of database server types on the left. Remember that we are connecting to a MSSQL server, so click on that. This was the first in my list.
You can see some of the fields filled in, in the image. Go ahead and fill the blanks.
Connection Name
- Put anything in here, for reference only.
Connection Method
- leave this as Standard TCP/IP
Host
- Mine is sql.steemit.com
, but just make sure it's the same as it is on the site.
User
- Again, this comes from steemsql.com, mine is steemit
and yours will probably be the same
Password
- From the site, as above.
Database
- You can safely ignore this, but if you HAVE to fill it in, use SteemDB
as this is the database we will be working with.
Now just click the magical 'Connect' button and you should be faced with the following screen. If not, double check the connection details.
If you entered a database name in the previous window, you will have it selected now. If not, click on SteemDB
.
By now your screen should look similar to this;
Now click on the Open
button to load up that database.
Step 4
Last step is to locate the Open SQL Editor...
button at the bottom, and click on it.
Now you are ready to start querying Steem and Steemit data from the blockchain!
In conclusion
Gaining access to the Steem / Steemit data from the blockchain has been made incredibly easy, thanks to the work done by @arcange (follow! now!).
If you have even the most remote interest in what's available I encourage you to hook up a connection to the service and play around.
I will be following this post up with more, focussing on simple queries and explaining what the tables and fields mean (as I understand them). I am no expert, but am going to share what I know as well as my opinion ;)
Thanks for taking the time to read this, I would love to hear from you in the comments!
I have other posts too;
On life (more or less)
- Stumbling onto an Minimum Viable Product
- You are never as fast as when you are slow
- I am speaking at the next PHP meetup in Portugal
3D printing
And living in Portugal
@shaunmza I see you do PHP. Have you figured out how to use PHP to make the queries?
To be honest I haven't tried with PHP, but I saw @davidk released some code, here's a link that may help;
https://steemit.com/steemit/@davidk/steemphp-new-functions-added-part-1
thanks, will check out. I started studying Python last night and may have to use that.
Great to have reached this. I have been struggling with programming languages. This is the first step I was anxiously expecting to find. A big thank you to you, @shaunmza .
I am happy to hear it helped!
Good luck.
Great explanation @shaunmza, thank you very much.
Fantastic! I was sitting on the fence on whether I should mention you or not, so glad like it!
HAHAHAHAHAHAHHAHAA YOU FUCKING LOSER!!! YOU'LL NOTICE NOBODY HAS DOWNVOTED ANYTHING SINCE YOU STARTED THIS EXCEPT YOU!!!!
AGAIN, I WIN, YOU LOSE!!!!!!!
Thanks a lot for this tutorial. Very straightforward and informative, great!
I will add it to the Steem Data Resources - Collection Of Posts About Steem Bots, Data And Mining, Issue No. 4.
I would like to add that steemsql is an aggregate with some info not present. Like timestamps, for example.
Resteemed!
Thanks so much!
Regarding the timestamps, this is something I will need to read up on. I have seen that there are dates, but I may be missing your point.
Do you have a link that explains this a bit more?
No link, sorry.
With no timestamps I mean that there is no direct data when an event happened (post, comment, vote, flag, ...).
I can come up with a test case / example but I don't know when I'll have time to do it :)
Do you mean the time it took place vs. the time it was stored in a block on the blockchain?
Thanks for the mention and thanks for sharing the above. Stephen
Can this be used to explore Steem Blockchain? Like all the activity that has happened?
To my knowledge, yes, but...
I think there may be some data thats missing?
So my final answer is it depends on what you want from it, but you can always make 100% sure in the #steemsql channel on https://steemit.chat
unfort. SteemSql is no longer free ;(