I recently got access to STEEM SQL (monthly subscription) and have been poking around with querying various data of the STEEM Blockchain, which has been fun. I have a bunch of queries that I want to play with, and especially a main focus will soon be concerning traffic to bid bots and curation. I'll have to see what the others have come up with as well, since I know there has already been some data pulled for that. And of course, if any of you have requests I can see if I can find anything, just let me know in the comments below!

Managing Queries and Redash

Note that the STEEM SQL page has a tutorial for how to get set up with Excel. However, I do not have Excel so I was looking for alternatives.

Anyway, specifically I wanted a nice interface where I could manage my queries and perhaps even visualize them, and a friend of mine suggested Redash. You can use their hosting, or you can use their open source code to host one yourself locally, and that's what I opted to do.

This is what the interface looks like, if you are curious and didn't click into Redash website. Feel free to go there to see more cool things it can do.

There's even a "New Visualization" button that I can't wait to play around with. Anyway, this was a query people in the #helpie discord were asking me to look at, in this case how many accounts had more than a certain threshold for SP. (Yeah, what's currently there isn't exactly the right query, but you get the idea).

The other helpful feature is that on the left it shows the list of tables, and when you click them, you get a list of fields. As you type in the SQL editor, it also has auto-complete for keywords and table names and field names as well. Unfortunately it doesn't show the type, but still workable.

What I usually do here then is to download the data to CSV and import it into Google sheets where I do the processing that you might do on top in Excel. Not as seamless, but it works fine for my purposes.

Setting Up Redash

The instructions are here and I essentially followed the provisioning script, with one tweak. If you use the script, you need to make sure it's using python2. So I had to replace a bunch of pip commands with pip2 due to the quirk in my setup.

After that, it was straightforward to login with the given STEEM SQL credentials and get the first query set up.

I don't have it set up to be accessible from my domain, and right now I set it up so it goes through an SSH tunnel, but it appears that Redash has functionality to set up users and google authentication. Might be an idea that one could modify this to use STEEM authentication and do subscription access through this mechanism in addition to the raw SQL access.

Anyway, that sums up a recent day. Let me know if you have any requests, and I'll add it to my list of things to try.