One thing I get asked for a lot on steemstats.com is historical data. The problem I ran into was in the way steemstats was designed - everything is loaded in real time from the blockchain. I needed a way to store and retrieve data based on time and date - steemdb.com (steem database) is the concept I ended up with, which will allow all of us to use this data.
Before I go further, this post will be split into two parts: the first part is for everyone and outlines the benefits steemdb.com brings to steem users; and the second part for the developers interested in learning and/or contributing.
Also - This is a super early alpha, it might crash :)
Introducing steemdb.com, the website
steemdb is about being able to look at data in different ways, as well as back historically in time, using techniques that aren't currently available through the steem blockchain. It also serves as a playground for new ideas and experiments. It's a block explorer at heart, but doesn't feel like your typical explorer.
I'd invite you to check out my account overview here:
or you can even continue reading this post via steemdb:
Currently steemdb allows you to do the following things:
- View detailed account information + historical charts
- View detailed information about posts
- View the current witnesses + mining queue
- Search for accounts using the box in the upper right
- (Experiment #1) Browse posts by creation date, filter by tag, sorted by either votes or payouts
You currently can't do anything that requires a login - steemdb is all about exploration for the time being.
This post is going to get long, so for those of you who need a TLDR, I'd invite you to scroll through the small gallery of images below that show the various pages available.
Account View
Example: https://steemdb.com/@jesta
The account view breaks down the different components of an account into separate tabs and tries to make the information easy to digest. You can look at anyone's account with this detail. It also charts and shows historical data for some areas of information to track changes over time. It's not mean to be a replacement to steemstats, but a tool that steemstats could link to to learn about others.
Much of this data will be available on steemstats.com in the near future. I'll also hopefully be providing some public APIs of the same thing for everyone else to use. This is all dependent on how optimized I can make the data and how much the infrastructure would cost to scale.
Post View
This was my take on how I'd want to explore the data behind a post. It's not interactive at this point, but already provides a bit more insight than we have here on steemit. It features the content broken out into a tabular view based on data aspect, and a sidebar that shows you about the author, other things they've written, and other meta information.
I've also included buttons on each post to quickly view it on steemit.com, just in case you discover something you'd like to vote on or respond to.
Witness View
This section is a mirror of the witness page on https://steemd.com/witnesses. It displays information important to the witnesses and miners on the steem network.
Posts by Date (Experiment #1)
Example: https://steemdb.com (the current homepage)
This section was designed as an experimental view of data on the blockchain. This was my first attempt at organizing the data on steem in a more "content silo'd" fashion to see how it could be browsed.
I plan on doing a number of experiments on different ways to view the content in an attempt to help determine the best way to present it. My thought is that I'll be able to move a lot faster (since it's a database, not a blockchain) and help prototype what sort of things we may see here someday on steemit.com.
Currently, the posts page is set to:
- Grouping: It's grouped by the
creation date. You only view one day at a time, and can go forwards and backwards in time. - Sorting: Posts by default are sorted by payouts which can be changed to votes.
- First Tag Priority: If you click on a tag, if will only show you posts where that is the first tag.
It's pretty interesting to go back in time and view what was popular on specific dates. It's even more interesting when you pick a tag, and then paginate by date. I've found a few posts that I never would have seen by doing this.
What's next for steemdb.com (the website)?
The short version is more historical data, charts, APIs and ways to view the data that exists on the blockchain.
One of the biggest goals with steemdb is to help people learn about all the various aspects of the steem platform. steemstats.com has the same goal, but just from a very different angle. steemstats.com is about someone specific, and steemdb.com is about everyone in general.
Over time, I think that these two projects will integrate with each other more and more.
The steemdb platform will also be used to show the community alternate ways that the content here on steem can be presented. I plan on trying out a bunch of interfaces you're likely familiar with to see what works best and what the community would like.
I feel the best way to actually garner feedback is with a prototype, so I plan on delivering them, hopefully with the help of others!
Speaking of which...
Introducing aaroncox/steemdb - the code
https://github.com/aaroncox/steemdb
Let me start by saying I most definitely followed the mantra of "do things that don't scale" for this prototype. If I never updated this code ever again, it wouldn't be scalable to the size that I believe steem can achieve. In fact I don't even know how the servers will hold up with the announcement and general usage of the site. Gotta love these alpha versions!
It's also not really that easy to use yet! The reasons for this are:
- It's 2 weeks old
- I used technologies I didn't have to learn
- I'm learning the best ways to retrieve the data from the blockchain
Just like steem itself, if you're going to get involved, you're going to have to be able to figure how things work without documentation (for now). Learn to ask questions - lots of them.
I'd invite you to join the #steemdb channel on steemit.chat, as I'll try to focus any interested parties there. If you join to promote your posts... you're gonna get the evil eye.
The technologies behind steemdb currently
Technology stack was chosen based on technologies I've used recently, not what's best. I'm still working full time on other projects and steem is still only a portion of my time each week. So if I needed a hammer, and all I could find was a rock, I used it.
That doesn't mean these technologies can't change and be adapted. Remember, it's an alpha and an experiment...
Currently these are the components that make up the project:
- Data Storage: MongoDB. Say what you will about it, but I was able to throw blocks of json at it, index it, and then begin querying/aggregating it.
- The Website: Powered by PHP7/nginx, using the Phalcon 3 framework, and a small collection of composer packages. On the frontend it uses semantic-ui for UI and plottable.js for charts. It connects to MongoDB as well as directly to the steem blockchain (very lightly).
- The Services: Written in Python3, and powered by Piston, these services act as the synchronization tools between the blockchain and the database.
All of this is encapsulated in a variety of docker containers that's controlled through docker-compose.
Future direction of the project
Since this is the first release, a firm direction hasn't been set in stone. I'd like to see a number of improvements, as well as some optimizations, to really firm up it's core.
Right now off the top of my head, I believe it's immediate future includes:
- Some build processes to help manage js/css dependencies. It's very manual right now. You'll actually find a nice symlink from the public folder into the bower components.
- Cleaning up the synchronization scripts so they don't store quite so much data. Right now there's a lot of duplicate data, including posts, so the database is huge. Luckily disk space is cheap right now, and the project is still rather limited.
- The charts aren't in the best of places. The JS is actually stored in a volt file simply because docker was fighting me by corrupting my JS files.
- Most of the aggregation queries should be moved out of the controllers and into the appropriate models for reusability. There's very little duplicate code right now, but it might get out of hand if that pattern continues.
There are also some loftier goals of removing the frontend from PHP, and using something like react. This would make the PHP layer purely an API for the frontend to interact with.
How do I run the damn thing?
I will get a full development environment guide up in the coming weeks. It's going to take a LOT of disk space and some patience for the setup. You're going to have to build your own entire database version of the 4+ million blocks in the blockchain. Currently, completely unoptimized, it's consuming 21.943GB of disk space on my server.
If this is something you're interested in - the first step I'd take in getting started is setup a local steemd instance and synchronize the entire blockchain to a local computer/server. It just so happens I wrote a guide on a web instance of steemd a few weeks ago. I'd start there.
The first step of running steemdb is going to be letting the services run for like 6-12 hours as it creates your database.
Wrapping things up
I'm pretty excited about this project, being able to query the database for pretty much any bit of information is like having a super power. I will do my best to keep the community up to date of changes, digest feedback, and help shape it into another great tool for the community to use.
Philosophically, steemdb is what I'd consider to be the 3rd and final part of the trinity of projects I've been working on:
- steemstats: All about you and your data
- steemdb: Aggregate information about everyone and everything
- steempress: Taking your data and letting you start your own website
These projects combined will hopefully form a powerful open source combination to help contribute to the overall steem ecosystem. steempress is up next in the list of priorities, with two new themes to implement and a few projects that have expressed interest in using it to power their ideas.
As a current developer, backup witness, writer and normal guy - I hope to continue being a meaningful part of this community far into the future. Thank you all for your support!
This is amazing! I love visualizing data!
Wawoo, awesome DB!
This is not great - it is exceptional!!!
Where do you find the time to do it all? Expertise is no problem as I can see :)
Extremely cool and useful. You came up with a proper data warehouse for a steem blockchain.
Is this a first for any blockchain?
It comes as somewhat lame but ... keep on the good work ;)
Agreed, I'll add some options for you to sort on that page ;)
Fantastic post! Thank you for your dedication to this enterprise and thrive on! All for one and one for all! I'll surely be using this and you got an extra upvote from here. Namaste :)
Thank you for everything you do for the Steem community! I find your tools extremely helpful. I have some suggestions I think would make them even better.
Suggested Improvements for Steemstats:
1. Add notifications for new followers
2. Allow for sorting of followers, sort by:
3. Add cumulative statistics for followers
Oh some of these are fun :)
I'll add them to my list and see what I can do!
Excellent, I look forward to seeing what you produce!
This is AWESOME @jesta! I just released a little tool for Steemit a few days ago -- http://st33m.it! It provides stats and analytics plus free custom short vanity URLs for Steemit articles. I'm not much of a coder, but I wanted to provide a valuable tool for this community. I hope you'll check it out :) Thanks for all your great contributions!
You've outdone yourself this time @jesta. This historical data is really useful. I particularly like the incoming/ outgoing votes graph, don't think I've seen that feature anywhere else. Good job.
Thanks :)
I had a lot of ideas for charts, I just haven't had time to implement them all just yet. I actually have some prototypes sitting here on this URL for more global stats:
https://steemdb.com/charts
They take a few seconds to load, but show the capabilities of the database :)
A second take of your newest tools gives me another opportunity to applaud you.
Raw data tab within posts is an essential help.
Here I can copy the body and paste it into new posts :)
Comes handy when you want to respin articles which are older that 12 hours and you can't edit them any longer.
THANKS!!!
Great tool! I pretty much confirmed my suspicion that my account is broken using it.
One question, can you make the overview tab go "all the way?" Paginate it or whatever if needed, but I've been finding POW's and not seeing them credited for a few days now and can only see the last attempt on that tab...
And if anyone sees this and thinks they can help me with my mining issue I've posted here: https://steemit.com/steemit-help/@telos/did-i-break-my-miner
(Screenshots from Steemdb to come in the morning.)
Yeah definitely a good suggestion. I'll add it to my list!
Awesome website @jesta! Glad there are a lot of steemians contributing to the grow of this platform. I like to consider myself and active contibutor and recently I also released a cool website!
Steem Locator
A website to find Venues accepting Steem or SBD all over the world. Please check it out!
https://steemit.com/steem/@oumar/steemlocator-com-locate-businesses-accepting-steem-my-contribution-to-this-community
Lets continue supporting this awesome community!
^vote
thnx
welcome to my art
https://steemit.com/introduceyourself/@romanskv/hello-i-m-a-photographer-amateur-roman-skvortsov
Wow! Another fantastic contribution, @jesta! I was super excited that you included a tech section! =D
Awesome! I can't wait to play around with this 😀
I hope you don't mind Me throwing in a shameless plug on your behalf- if you haven't already you should upvote @jesta as a witness. This is definitely someone we want running the backbone of Steem!
https://steemit.com/~witnesses
Great work!
Is it update for steemstats or something different?
How often database is updated, I see my account was updated 4 hours ago?
Love charts btw, they look great!
Totally different website, as I explained in the OP. My projects are:
👍
thanks information @jesta
service number 1 for steemit.
Fantastic stuff as always, Aaron :) Looks like I won't be visiting Steemd.com much henceforth.
This is great, thanks for all of your hard work on steemdb.com!
Thanks for making this tool available. I especially support Experiment 1 Vote per day https://steemit.com/introduceyourself/@steempowerwhale/i-am-steempowerwhale-and-i-am-here-to-upvote-your-lifetime-dreams
This Steem blockchain explorer is long overdue and steemd can use some competition :) Great work!! Again !!!!!!
@jesta Really appreciate all your hard work and contributions to the evolution and development of this platform. Your efforts have helped me immensely. I have Steemstats open 24/7. It's my "go to" page for all things Steemit.
This is very cool. I'm loving the various sites that let me view Steemit data in different ways and this is yet another option. Thanks for doing this
Awesome @jesta!
I have a few ideas for other Steemit related services I have been mulling around in my head and it seems every idea I come up with the current API is a major design limitation. It's just too difficult and expensive to get at older data.
The only way I could think of to solve this is to pull every block and parse the information I need into a local db that is optimized for the searches I want to perform. I haven't had the initiative to attempt that though, and now you have gone and done it. Superb!
I look forward to looking over the code when I have the time to see if I can integrate into my projects. Super good work man!
Great work @jesta, almost what I needed in terms of additional functionality that steemstats did not have. The historical data charts are actually quite useful to see how things have progressed.
You probably have not seen my idea, but maybe you can consider further extending some of the functionality like I'm describing here: https://steemit.com/steemit/@cryptos/idea-new-tool-that-can-help-curators-on-steemit
I have written announcement to the Russian-speaking community
[steemdb.com] Анонс аналитического сайта - Bockchain Explorer с открытым исходным кодом
@jesta, Thank you for a great tool supplementing steamstats.com
@Jesta this is a really good start - so congrats on this - i will def push a donation to you ). A couple of thoughts my side:
Keep it up )
Nice.. Great work. I appreciate your contribution in steemit :)
This is beautiful work.
Congratulations again and thanks for being such an essential part of the community.
So exciting, @jesta! I've been waiting for this since you teased it in the chat. Thank you for all you're doing for the community!
Great work! @jesta
Actually, I made a similar website http://www.steemb.com and submit a post
in here https://steemit.com/steem/@steven1980/heavy-tool-steem-reward-income-report-your-steem-income-at-a-glance-english-version
Why my post is so poor? :-)
www.steemb.com provides income information for Steemit users. On this website, you can see how much money you have earned from steem website. There are details, a summary and a chart. The amount is converted into US dollars using steem's real time exchange rates.
I saw that post the other day and gave you an upvote.
I'm just guessing here - but it may have not done as well simply because it's a lot simpler and most of those features already exist elsewhere. Right now (as far as I can tell) all that steemb.com does is:
The post itself could have just been missed by many too, we all know there are some content discovery issues that happen from time to time. You could also open source it for extra credit, which would help others learn from your work :)
Thank you! I will add more useful feature to www.steemb.com
Fantastic work. Can it be made like blockchainsql.io, where we can run SELECT query?
Possibly in the future, it's an option. Right now if I let anyone run any query they wanted it's likely it would grind the server to a halt. It's only optimized to run the queries I've setup - so it'd take some work to get it fast enough to handle other requests.
It's also not SQL, but I get the premise of the idea ;)
Stupendous! How do you create such tools so fast?!? Do you not sleep?
I think it takes me more time to explore, understand, and appreciate your work than it does for you to create it. Keep it up.
I've been building sites like these forever... though I have lost a bit of sleep ;)
Thanks!
Incredibly awesome, i'm looking into it right now, really appreciate your work, thank you :)
I like numbers, here have an upvote, im sorry my upvote is worthless, i havnt been blessed by the gatekeepers yet.
@jesta, Another great site!
Can you add the number next to the miner queue like steemd.com? Thanks
Thanks, and done! Numbers have been added.
Great! Thanks!
It is also great that you included the block number on under blocks.
Is it possible to tally up the number of blocks found in the 45-days chart where witness and pow is?
Both good suggestions, I'll see what I can do!
I know you are very busy with all these projects.
Thank you for take it into consideration.
@jesta
https://steemdb.com/@oflyhigh
curation_reward 1 minute ago View '_elements/tx/curation_reward' was not found in any of the views directory #0 [internal function]: Phalcon\Mvc\View->_engineRender(Array, '_elements/tx/cu...', false, false) #1 [internal function]: Phalcon\Mvc\View->partial('_elements/tx/cu...', NULL) #2 /var/www/com_steemdb/cache/volt/_var_www_com_steemdb_app_views_account_view.volt.php(227): Phalcon\Mvc\View\Engine->partial('_elements/tx/cu...') #3 [internal function]: unknown() #4 [internal function]: Phalcon\Mvc\View\Engine\Volt->render('/var/www/com_st...', Array, true) #5 [internal function]: Phalcon\Mvc\View->_engineRender(Array, 'account/view', true, true, NULL) #6 [internal function]: Phalcon\Mvc\View->render('account', 'view', Array) #7 /var/www/com_steemdb/public/index.php(41): Phalcon\Mvc\Application->handle() #8 {main}What's wrong?
I visited the page of my friend @oflyhigh , and got above message
Now, It's seems normal.
Should be fixed now. Appears that one of the operations was renamed and was causing problems.
thank you.
thanks information, support @jesta
Very awesome work from a genius like yourself.
how get user blog posts list through api?
There's a bunch of ways that return different sets of blog posts.
If you're looking for posts by a single user, you can use
get_state t3ran13, which will return the account as well as all of the blog posts by that user.I'd recommend checking out @lantto's api explorer to browse through all the possible api calls:
http://steemroll.io/api-explorer/
thank you very much!
This post has been linked to from another place on Steem.
Learn more about linkback bot v0.3
Upvote if you want the bot to continue posting linkbacks for your posts. Flag if otherwise. Built by @ontofractal