Hivemind Queries

I installed my own version of Hivemind so that I can play around with accessing some of the blockchain using Postgres. It took a while to sync. But once it did, oh nelly!!

What is Hivemind? The repo says:

Developer-friendly microservice powering social networks on the Steem blockchain.

Hive is a "consensus interpretation" layer for the Steem blockchain, maintaining the state of social features such as post feeds, follows, and communities. Written in Python, it synchronizes an SQL database with chain state, providing developers with a more flexible/extensible alternative to the raw steemd API.

This means that you can bypass steemd and access data in a more traditional way. Often, business solutions use SQL for data. But you can't use SQL on steemd. So Hivemind solves that problem.

Now, in reality, there's another goal to Hivemind, as mentioned in the @steemitblog application team update: Hivemind/Communities, Sign-Ups, Developer Tools, Condenser, and More!:

Work on Hivemind 1.0 remains a major focus. Over the past few weeks, we have been heavily testing compatibility between hive and condenser. We have also committed significant resources to documenting the steps required for Hivemind integration, which will help community developers deploy and take full advantage of hive once it is ready.

Hivemind will facilitate community front-ends. But the 1.0 version doesn't offer much in new features. It takes some of the load off of steemd, which is a great feature that I'm very excited about. But it's hard to convey why this is exciting. It's just a drop-in replacement for something that already works, right? Yes, but in doing so, it takes some of the load off of steemd. And that's a very good thing.

At it's core, it does that by making it easier to query the same data. But in addition to that purpose, if you can run your own Postgres database, you can do some interesting queries.

For example, I want to know what the top 10 apps are, by payout (all time). Well, the query for that looks like this:

SELECT   json::json->>'app', Sum(payout)
FROM     hive_posts_cache
GROUP BY json::json->>'app'
LIMIT 10;

And that gives us the following results:

App	Payout in SBD
steemit/0.1	1225887.103
busy/2.5.3	93830.625
dlive/0.1	86517.867
busy/2.5.4	63678.215
dtube/0.7	55127.314
busy/2.5.2	42774.839
unknown	38544.404
steemhunt/1.0.0	34304.329
esteem/1.6.0	31151.406
dsound/0.3	17792.419

Kinda cool, right?

Or, I can query for specific mentions with certain tags:

SELECT hive_posts.*
FROM   hive_posts
WHERE  ( hive_posts.id ) IN (
  SELECT hive_posts_cache.post_id
  FROM   hive_posts_cache
  WHERE  ( hive_posts_cache.body LIKE '%@inertia%' )
  AND    ( hive_posts_cache.body LIKE '%@whatsup%' ))
AND ( hive_posts.id ) IN (
  SELECT hive_post_tags.post_id
  FROM   hive_post_tags
  WHERE  ( hive_post_tags.tag ) IN ( 'community', 'shoutout' ));

That one's saying that each result must have two mentions and either tag, which gives us the following results:

@steemexperience/update-on-the-steem-experience
@wishmaiden/attention-noobs-come-join-the-voice-chat-community-at-steemspeak-com
@arsenal49/400-followers-steemspeak-randowhale
@vocalists-trail/thursday-shoutout
@steemexperience/3uaqax-update-on-the-steem-experience
@steemexperience/45ncqm-update-on-the-steem-experience

Or, lets say we want to query posts that must have all three tags: kitty pet and cute:

SELECT hive_posts.* 
FROM   hive_posts 
WHERE  ( hive_posts.id ) IN (
  SELECT hive_post_tags.post_id
  FROM   hive_post_tags
  WHERE  hive_post_tags.tag = 'kitty')
AND ( hive_posts.id ) IN (
  SELECT hive_post_tags.post_id
  FROM   hive_post_tags
  WHERE  hive_post_tags.tag = 'pet')
AND ( hive_posts.id ) IN (
  SELECT hive_post_tags.post_id
  FROM   hive_post_tags
  WHERE  hive_post_tags.tag = 'cute');

I'm really excited about this kind of query because normally, if we don't use SQL to do this kind of query, we get a huge result. For example, you might want all posts with kitty plus all posts with pet plus all posts with cute which would give you 23,844 results. But because I require all three tags in the result, I only get two:

@seoya/my-lovely-kitty-jelly
@justwatchout/8nin82lj

You can also ask for the most upvoted post (at this very moment):

SELECT hive_posts.*
FROM   hive_posts
INNER JOIN hive_posts_cache ON hive_posts_cache.post_id = hive_posts.id
ORDER  BY hive_posts_cache.rshares DESC
LIMIT  1;

... which is this:

@chbartist/right-before-the-daw

... and the most downvoted post:

SELECT hive_posts.*
FROM   hive_posts
INNER JOIN hive_posts_cache ON hive_posts_cache.post_id = hive_posts.id
ORDER  BY hive_posts_cache.rshares ASC
LIMIT  1;

... which is this:

@joanaltres/re-elfspice-dan-larimer-so-insecure-he-has-to-self-vote-to-put-his-posts-that-already-are-getting-enough-votes-to-the-top-of-trending-20170802t061503811z

Notice that the highest upvoted post is distinct from the highest paid post. This is because the market prices are a factor, as well as quadratic rewards, and the fact that this payout pre-dated the voting slider.

SELECT hive_posts.*
FROM   hive_posts
INNER JOIN hive_posts_cache ON hive_posts_cache.post_id = hive_posts.id
ORDER  BY hive_posts_cache.payout DESC
LIMIT  1;