Exploring Hive data with HiveSQL

in Programming & Dev11 months ago

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,
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,
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.


Nice guide you can throw me in the Brit List if you want

I actually found you already and you will be on the list next month! The list should be a fair bit longer with lots more active people. Eventually we can look at doing meetups again. Maybe see you at one.

Agh! Your on it! yes that would be lovely I can't wait for things to return to normality! I was actually suprised at the number of people from the UK on Hive so thank you for actually compiling that, very intriging finding us all

Glad to see you finally enjoy HiveSQL services. Bette late than never 😉

Sorry about that. Just one of those things that I had not got around too, but now I love it! Thanks for making it available to us.

I just heard about this the other day as well. I really like SQL when I was in college. I wish they had offered more than one class on it. I think I could have had a pretty decent career for myself if I had been given the opportunity to specialize in it. Lots of places when data manipulated and presented in an easy to read form.

It would be great to build up a set of queries that people can take and adapt to their needs.

I think, the JSON meta data is going to be very useful - given that dapps can publish anything on the second layer.

I've dabbled in SQL for the past 500 years. I'll have to give this a crack. Looks exciting. Thanks for the info.

Thanks for continuing to make Hive awesome.