Hello Hivers, developers and all others,

in this post I explain, how I got an HiveSQL account, installed a SQL client and executed my first SQL query:

Hive data

There are several possibilities to access the Hive data.

API

You can get the data via the API interface. Here is an example:

curl -s --data '{"jsonrpc":"2.0", "method":"bridge.get_account_posts", "params":{"sort":"blog", "account": "achimmertens", "limit": 1}, "id":1}' https://api.hive.blog

Here is more information about the API-Interface

GUI

This is ok for some developers but inconvenient for normal use. Therefore we have the second option: We use a Graphical User Interface (GUI) like https://peakd.com or ecency and click just some buttons. In the background these tools access the hive blockchain usually via the API interface as well.

But there is also another way to get to the data:

HiveSQL

@arcange operates a HiveSQL server, which mirrows all the data, that is in the hive blockchain.
The advantage is, it is much faster and it relieves the Hive API nodes, when you do big searches.
A SQL server is usually optimised for big data (but you can also slow it down with bad requests), so it is the better way to use it for deep searches, instead of stressing API-nodes.

How To Register

I have send 1 HBD to @hivesql

I got an answer like this:

So, this is an encrypted answer. To read its content I had to put my hive memo key into the tool "Hive Keychain" and read the reply there:

Installing the SQL Client Squirrel

There are several tools you can use to access the Hive Microsoft SQL server. I played with Heidi SQL, but didn't succeed with the driver. But it worked with Squirrel for Windows:

Download

Precondition: Java needs to be installed.
I downloaded the squirrel installation jar file from here.
Then I executed it and included the Microsoft SQL Server Plugin:

java -jar squirrel-sql-4.6.0-standard.jar

Driver

I startet the Squirrel client and clicked on Help to get more information about the driver:

So I downloaded the JTDS driver and put it into a folder of my MS Windows PC.

Then I added that driver as shown in this image:

The jdbc-URL is concatenated with the infos from my reply in the wallet:

jdbc:jtds:sqlserver://vip.hivesql.io:1433;databaseName=DBHive

Connection

To connect to HiveSQL I added an alias as shown in the picture:

The connection worked with the first try.

First Query

Now I have put in my first SQL query. It searches in the comments for the word "!CHARY" for all users except 'arcange' and shows the author, title, body and url:

SELECT 
   author, title, body, url 
FROM 
   Comments 
WHERE 
   author <> 'arcange'
   AND CONTAINS(body, '"!CHARY"')

In the first run I only got "<Clob>" (large objects) as answers , which maybe faster but is not really helpfull. So I changed that in Files/Global Preferences/Data Type Controls:

I checked the results. Yes, in the resulting urls are posts, that contain the word "chary", but not with an "!". So I will work on that, but this is bits and pieces.

Result

Now I am able to search very fast and convinient everything I want to know in the Hive database.
I am aware, that I can stress the SQL server (i.e. by forgetting the "where"-clause and allowing millions of answers) and try to avoid that.

The next thing is to create a JavaScript, which reads the HiveSQL server and creates some reports from it (i.e. who has the "!CHARY" notification in the replies and to be able to reward the authors of that posts).

Remark

Operating the HIVE SQL Server costs money, so it is funded by the HIVE community. If you think it is worthy to get funded in the future, please vote for it.

For more information please read: https://docs.hivesql.io/ or join the discord channel.

Thank you for reading,

Achim Mertens

How To Get Access To HiveSQL With Squirrel