HiveSQL: Claim Reward Balances

in #hive-1679223 years ago

hivesql.png

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.

Posted Using LeoFinance Beta

Sort:  

You can do the whole thing with one SQL query:

SELECT
   reward_hbd,
   reward_hive,
   reward_vests * (SELECT hive_per_vest FROM DynamicGlobalProperties) AS reward_HP,
   timestamp
FROM
   TxClaimRewardBalances
WHERE
   account = '{account_name}'
   AND timestamp BETWEEN '{start_date}' AND '{end_date}'
ORDER BY
   timestamp DESC

The query optimizer will take care of executing your subquery only once. This way, you generate only one round trip to HiveSQL ;)

Thanks for the instruction. I often would see the word SQL, but not have any idea what it meant. It's just a coding language.. it's so cool that we can use it.


The rewards earned on this comment will go directly to the person sharing the post on Twitter as long as they are registered with @poshtoken. Sign up at https://hiveposh.com.

It is really easy to use after reading your post. Thanks for sharing this unique information. HiveSQL.io is very useful for checking reward balances as you have explained well.

@geekgirl keep to amaze me with her update keep up the good work

This is really informating,I have never really try it,in short it never occured to me but I will definitely go and try this.thanks so much for this information

I practice enough using Python, the first time I see this type of practice, I think I will start making content of this type anyway, excellent publication, it has motivated me.

You are really on top of the game because you are making me amazing with your update. Keep moving us forward

I think this is simple and straight forward enough. At least someone who can navigate his hive dashboard can get it done as well.

This looks really great at the different contexts of the SQL structures you have showed here.Great analytics came on display, and I guess your own tutorial support made other users to know deeply about this code and its application.

Nice skill of coding, you have! 😊👍

It looks simple enough to understand. However the hive per vest is calculated as the current value of vest and not really the historic right?

Posted Using LeoFinance Beta

You are right, hive per vest is the current one.

I've been thinking we should look at organising a Hive Hackathon! It would be a great way to get developers from outside the Hive ecosystem involved, and it might lead to some cool projects

It is already done from time to time.

Congratulations @geekgirl! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s):

You published more than 600 posts.
Your next target is to reach 650 posts.

You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

To support your work, I also upvoted your post!

Check out the last post from @hivebuzz:

Hive Power Up Month - Feedback from February day 5
Hive Power Up Month challenge - Feedback from February day 3
Our Hive Power delegations to the last Power Up Month challenge Winners