Get the Latest Blog Articles from Hive using HiveSQL and Python

Did you know that you can access tons of Hive data without touching the Hive blockchain at all?

Yep!

And it is free *

^{* While they get funded from their proposal}

Introducing HiveSQL

What HiveSQL does is continuously create a database of all the information being generated on the blockchain.

It's not a replacement for the blockchain, more an easy to access cache of all the information stored there:

HiveSQL is a publicly available Microsoft SQL database containing all the Hive blockchain data.
Data are structured and easily accessible from any application able to connect to an MS-SQL Server database. It allows you to easily access data contained in the Hive blockchain and perform analysis or find valuable information.

Why use SQL?

As they say on the HiveSQL site:

Browsing the blockchain over and over to retrieve and compute values is time and resource-consuming.
Instead of having a local copy of the blockchain or downloading the whole data from some external public node to process it, you will send your query to HiveSQL server and get the requested information.
HiveSQL makes it possible to produce quick answers to complex questions.

Getting started

First, we need to install the requisite software and drivers. On Windows this is pretty much going to be in place, but for Linux and Mac we need some stuff.

The Python library we need is PyODBC which can be installed using Pip.

Linux:

Use this shell script

On my Ubuntu server I got a pyodbc error ERROR: Command errored out with exit status 1: but this fixed it:

sudo apt install python3-pip python3-dev unixodbc-dev
pip3 install --user pyodbc

MacOS:

Use Homebrew (requires Xcode CLI tools)

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql17 mssql-tools

Register for a HiveSQL Login

Next we need to get some credentials so we have to send @HiveSQL 1HBD (no memo needed.)

Your credentials will be sent back as an encrypted memo which you can then read using Peakd or Hive Keychain:

Set up your credentials as environment variables

You do not want your credentials to get out into the wild. The easiest thing while in development is to use environment variables.

On Linux/Mac you can simply use

export MYVAR=MYVALUE

To store those permanently, edit your shell profile (eg. .bash_profile)

For your web server, you will need the environment variables set up in the server config. Ubuntu Apache has them in your site configuration:

sudo nano /etc/apache2/sites-enabled/000-default-le-ssl.conf

Then add the variables line by line:

SetEnv TEST true

Once changed, restart Apache:

sudo systemctl restart apache2

You can test in Python to see if your variables take by doing a simple CGI test web page:

#!/home/chrisg/miniconda3/bin/python3
import cgi
# Turn on debug mode.
import cgitb
cgitb.enable()
cgi.test()

Querying the HiveSQL Database

To actually connect we need a connection to the server, and we pass along all our parameters such as our login details that we grabbed from the encrypted memo above:

    connection = pyodbc.connect(driver='{ODBC Driver 17 for SQL Server}', host=db_host, database=db_name, user=db_user, password=db_password)

Then we can read back from the database. For example, we can list the most recent blog posts:

    cursor = connection.cursor()
    sql_command ="select * " \
                 " from Comments " \
                 " where depth=0 " \
                 " order by ID desc;"

    result = cursor.execute(sql_command)
    result = result.fetchmany(10)

Let's switch it up to get the one most recent article by category (top level tag):

Python Code

import os
import sys
import pyodbc



def get_connection():

    db_host = os.environ['DBServer']
    db_name = os.environ['Database']
    db_user = os.environ['DBLogin']
    db_password = os.environ['DBPassword']

    connection = pyodbc.connect(driver='{ODBC Driver 17 for SQL Server}', host=db_host, database=db_name, user=db_user, password=db_password)
    return connection


def get_latest(connection, keyword):

    cursor = connection.cursor()
    sql_command ="select top (1) id, title, category, " \
                 "created, '@'+author+'/'+permlink as link " \
                 " from Comments " \
                 " where depth=0 and category='{}' " \
                 " order by ID desc;".format(keyword)

    result = cursor.execute(sql_command)
    result = result.fetchmany(1)
    return result



def persist(item_list):

    with open('previously_seen_ids.txt', 'wb') as fp:
        pickle.dump(item_list, fp)

connection=get_connection()
print(get_latest(connection, "hive"))