HiveSQL: Claim Reward Balances

The topics discussed in the previous posts, hive rewards, taxes, hive blockchain data, and hiveSQL wouldn't be complete without some code. To wrap up the discussion on this topic I would like to share some code and demonstrate how easy it is to use HiveSQL and get needed Hive blockchain data.

To use HiveSQL we need login credentials to access the database, and Hive account owners can get them for free. Please visit hivesql.io for instruction on how to obtain login information. It is highly recommended to gain proper knowledge of SQL and to issue queries before using HiveSQL. Since SQL queries is not difficult to learn, one can get sufficient skills in no time.

HiveSQL makes it easy for use to get any data from Hive blockchain. For example if we wanted to get claimed rewards transactions, there is TxClaimRewardBalances table. It has columns like reward_hbd, reward_hive, reward_vests, and timestamp.

Let's say I would like to get all the claim reward balance transactions for my account for this year. I could do so by issuing the following query:

SELECT reward_hbd, reward_hive, reward_vests, timestamp
FROM TxClaimRewardBalances
WHERE account = 'geekgirl'
AND timestamp BETWEEN '2022-01-01' AND '2022-02-7'
ORDER BY timestamp DESC

Claimed HP values are in vests. If we wanted to convert vests to HP, HiveSQL provides useful global values in DynamicGlobalProperties table. One of these useful values is hive_per_vest.

SELECT hive_per_vest
FROM DynamicGlobalProperties

Now we can multiply reward_vests with hive_per_vest to get the HP value.

You can use any MSSQL query software. However, my preference is to make queries with python code. To achieve the same result above, I can use the python code below. The benefits of using python is the ability to use the results in other code or apps. Also the ability to reuse the code, and even keep improving on the previous works.

import os
import pymssql
import datetime as dt
from pprint import pprint

def hive_sql(SQLCommand, limit):
    db = os.environ['HIVESQL'].split()
    conn = pymssql.connect(server=db[0], user=db[1], password=db[2], database=db[3])
    cursor = conn.cursor()
    cursor.execute(SQLCommand)
    result = cursor.fetchmany(limit)
    conn.close()
    return result

def get_hive_per_vest():
    SQLCommand = '''
    SELECT hive_per_vest
    FROM DynamicGlobalProperties
    '''
    result = hive_sql(SQLCommand, 1)
    return result[0][0]

def main():
    account_name = 'geekgirl'
    start_date = '2022-02-01'
    end_date = '2022-02-07'
    hive_per_vest = get_hive_per_vest()
    limit = 1000
    SQLCommand = f'''
    SELECT reward_hbd, reward_hive, reward_vests * {hive_per_vest}, timestamp
    FROM TxClaimRewardBalances
    WHERE account = '{account_name}'
    AND timestamp BETWEEN '{start_date}' AND '{end_date}'
    ORDER BY timestamp DESC
    '''
    result = hive_sql(SQLCommand, limit)
    pprint(result)
    print(len(result))

if __name__ == '__main__':
    main()

For this code to work, you need to make sure you have dependencies installed. In this case mainly pymssql. This may require additional configuration on your machine. The code itself is not complicated at all. It is basically the same SQL queries I showed above, but wrapped with some python code.

It only has three functions. Function hive_sql() purpose is to connect to the HiveSQL database and return the results. This is where you will need to include your login credentials. To hide my password, I used os.environ(). This function remains same for all of the HiveSQL query scripts.

The main() function is where, I build a query and call hive_sql() function to get the results. Lastly, get_hive_per_vest() function queries hive_per_vest to be used within the main() function. Everything else should be self-explanatory.

Of course, there is a lot more that can be done using HiveSQL. The purpose of this post was to show how easy it is to use. To echo what Arcange always says, HiveSQL is a shared infrastructure, please use it wisely with proper understanding of what you are trying to do.