Exploring Hive data with HiveSQL

I have heard mention of @hivesql by @arcange, but it took this post by @abh12345 to push me into actually trying it out and I wish I had done so sooner.

In order to you this service you need to make a tiny transfer to @hivesql. In return you get your password in an encrypted memo. If you have your memo key in Hve Keychain then you can decode it in Peakd.

You will need some application to access the database,which is on SQL Server. I think you can use Excel, but as a Linux user I just looked around for apps and found DBeaver that can work with most types of database.

Logging in took a little working out. I needed to set the Schema to HiveDB before I could actually see the data.

Once I sorted that out I was able to run some queries. I have done lots of SQL for work, but the basics can be learnt fairly quickly.

I wanted to check if known abuser was using any of his many accounts to milk rewards. He has created hundreds of accounts that conveniently have his main one as the recovery account. Currently he has some posts on one, but it looks like all rewards have been removed. If he uses a different account this would pick it up.

select author, 'https://hive.blog/'+ url,
pending_payout_value,
C.created,
body_length as CharacterCount
from Comments c
where c.author in (select name from Accounts a where a.recovery_account ='crystalliu')
and datediff(minute, C.created, GETUTCDATE()) < 602450
and c.pending_payout_value > 2
order by c.created DESC

Another query looks for users with specific locations. I am looking for people to add to the Brit List and this has thrown up lots more. I have searched for various things like England and Wales. I have to check each one as the details can be misleading. UK can also signify Ukraine and there is little consistency. I had some issues reading the JSON metadata that was a bit scrambled, but @arcange fixed that for me and everyone else can benefit. There may still be some issues with cases that have odd characters. I needed the first bit of the WHERE to make this run without errors.

select a.name, a.post_count, a.last_vote_time,
JSON_VALUE(json_metadata,'$.profile.location'),
a.json_metadata, a.recovery_account
from Accounts a
where a.json_metadata like '%UK%' and a.json_metadata not like '%Ukraine%'
and JSON_VALUE(json_metadata,'$.profile.location') like '%UK%'
and a.last_vote_time > '2021'
order by name

It would be great to build up a set of queries that people can take and adapt to their needs. @geekgirl has a load of posts about HiveSQL that I need to look into. Combining SQL with Python has lots of possibilities.

I can see this tool being used a lot.