Introducing hivedata.live v0.1.0 - A tool to visualize Hive Data with the help of Hive SQL

avatar
(Edited)

Ever since I started using Hive SQL, I always wanted to create a portal from where we can easily view all the possible data we can fetch from Hive with the help of Hive SQL. I would like to start this article by conveying my thanks and regards to @arcange. I don't think this tool would have been a possibility without his Hive SQL. I also have plans to continue working on this tool and even make this an open-source project in the future. The website/tool is accessible from the below location.

https://hivedata.live

The website is live but there is a high possibility for bugs and errors. Probably you can consider this as an alpha version. If you find any bugs or any improvement suggestions, please reach out to me on Discord or in the comments section.

image.png

The user interface of the website is pretty simple right now. It will be improved in the future as the application is progressing. The website now offers two different types of data right now. Some of my friends asked me about an option to view their year-end data. I thought it would be better to give them a tool instead of giving them data individually.

As the year is just starting, I wanted to release this as soon as possible for the users to view their year-end data. The tool displays the Author Rewards, Curation Rewards, Total Posts, and Total Comments from the year we choose for the selected account. The data will be grouped month-wise and displayed for the user. Please use the website and let me know your observations in the comments section.

I would like to share the SQL queries used to view each of these options one by one. If someone is willing to directly query Hive SQL, they can make use of the following queries.

Author Rewards

Those who can read the below query should be familiar with where to change the account name to get the result for your account details.

DECLARE @account AS VARCHAR(16) = 'bala41288'
DECLARE @year AS INT = 2021
DECLARE @SOY1 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year))   -- start of year
DECLARE @SOY2 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year+1)) -- start of next year

DECLARE @HPV DECIMAL(19,8) = (SELECT hive_per_vest FROM DynamicGlobalProperties);

SELECT 
    @year AS [year],
    MONTH(timestamp) AS [month], 
    SUM(hbd_payout) AS totalHBDPayout, 
    SUM(hive_payout) AS totalHivePayout, 
    SUM(vesting_payout) AS totalVestingPayout, 
    CAST(SUM(vesting_payout) * @HPV AS DECIMAL(18,3)) AS totalHP
FROM 
    VOAuthorRewards
WHERE 
    author = @account 
    AND (timestamp >= @SOY1 AND timestamp < @SOY2)
GROUP BY 
    MONTH(timestamp)
ORDER BY 
    MONTH(timestamp)

Curation Rewards

DECLARE @account AS VARCHAR(16) = 'bala41288'
DECLARE @year AS INT = 2021
DECLARE @SOY1 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year))   -- start of year
DECLARE @SOY2 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year+1)) -- start of next year

DECLARE @HPV DECIMAL(19,8) = (SELECT hive_per_vest FROM DynamicGlobalProperties);

SELECT 
    @year AS [year],
    MONTH(timestamp) AS [month], 
    SUM(reward) AS totalRewardInVests,
    CAST(SUM(reward) * @HPV AS DECIMAL(18,3)) AS totalHP
FROM 
    VOCurationRewards
WHERE 
    curator = @account 
    AND (timestamp >= @SOY1 AND timestamp < @SOY2)
GROUP BY 
    MONTH(timestamp)
ORDER BY 
    MONTH(timestamp)

Total Posts

DECLARE @account AS VARCHAR(16) = 'bala41288'
DECLARE @year AS INT = 2021
DECLARE @SOY1 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year))   -- start of year
DECLARE @SOY2 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year+1)) -- start of next year

SELECT  @year AS [year],
        MONTH(created) AS [month], 
        count(permlink) AS totalPosts
FROM "DBHive"."dbo"."Comments"
WHERE author = @account  
AND created >= @SOY1
AND created < @SOY2
AND parent_author = ''
GROUP BY MONTH(created)
ORDER BY MONTH(created) ASC

Total Comments

DECLARE @account AS VARCHAR(16) = 'bala41288'
DECLARE @year AS INT = 2021
DECLARE @SOY1 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year))   -- start of year
DECLARE @SOY2 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year+1)) -- start of next year

SELECT  @year AS [year],
        MONTH(created) AS [month], 
        count(permlink) AS totalComments
FROM "DBHive"."dbo"."Comments"
WHERE author = @account  
AND created >= @SOY1
AND created < @SOY2
AND parent_author <> ''
GROUP BY MONTH(created)
ORDER BY MONTH(created) ASC

I was thinking that the above will be the most common requirement to view the stats from the previous year. If you think I should be adding some more features to the site or some more queries, please let me know in the comments section. I can work on the same.





0
0
0.000
18 comments
avatar

This post has been manually curated by @bhattg from Indiaunited community. Join us on our Discord Server.

Do you know that you can earn a passive income by delegating to @indiaunited. We share 100 % of the curation rewards with the delegators.

Here are some handy links for delegations: 100HP, 250HP, 500HP, 1000HP.

Read our latest announcement post to get more information.

image.png

Please contribute to the community by upvoting this comment and posts made by @indiaunited.

0
0
0.000
avatar

This post has been manually curated by @bhattg from Indiaunited community. Join us on our Discord Server.

Do you know that you can earn a passive income by delegating your Leo power to @india-leo account? We share 100 % of the curation rewards with the delegators.

Please contribute to the community by upvoting this comment and posts made by @indiaunited.

0
0
0.000
avatar

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

You received more than 100 upvotes.
Your next target is to reach 200 upvotes.

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

Check out the last post from @hivebuzz:

PUD - PUH - PUM - It's all about to Power Up!
Christmas Challenge - 1000 Hive Power Delegation Winner
Support the HiveBuzz project. Vote for our proposal!
0
0
0.000
avatar

It's not working for me.... I think k you need to add enter or go option. ...anyhow mere putting the name is not pulling any data

0
0
0.000
avatar

After putting the name, you should click on the buttons

image.png

0
0
0.000
avatar

Do you think I should change this user experience? Two people already said the same thing. 🤔🤔

0
0
0.000
avatar

Yes changing a but if interface would be great, actually the button looks like the tab, might be change the color or button or include an extra button called Get Data.

0
0
0.000
avatar

Cool. I will see what I can do. 👍

0
0
0.000
avatar

You have to update the year too, 2022 is not showing

0
0
0.000
avatar
(Edited)

DECLARE @account NVARCHAR(50)
SET @account = 'bala41288'

Use VARCHAR(). NVARCHAR needlessly use twice too many bytes.
Maximum length of Hive account name is 16
You can group both declaration and assignment in one sentence

-> DECLARE @account VARCHAR(16) = 'bala41288'

DECLARE @year NVARCHAR(10)
SET @year = '2021'

...
  DATEPART(month, timestamp) AS month, 
  DATEPART(year, timestamp) AS year, 
...
  AND timestamp >= @year + '-01-01'
  AND timestamp < @year + '-12-31' 
...
  GROUP BY DATEPART(month, TIMESTAMP), DATEPART(year, timestamp)
  ORDER BY MONTH asc

Do not use characters for date elements. This generates unnecessary char <-> int conversions!
Use MONTH(timestamp) and YEAR(timestamp), it's faster because it doesn't require parameters parsing.
MONTH and YEAR are reserved keywords and should be bracketed if used for a column name to avoid potential errors of misinterpretation.

AND timestamp < @year + '-12-31'

Wrong test because you are excluding the very last day of the year.

GROUP BY DATEPART(month, TIMESTAMP), DATEPART(year, timestamp)

DATEPART(year, timestamp) is useless because you are already filtering on only one year.

cast((((SELECT total_vesting_fund_hive FROM dbo.DynamicGlobalProperties WITH (NOLOCK)) * sum(vesting_payout)) / (SELECT total_vesting_shares FROM dbo.DynamicGlobalProperties WITH (NOLOCK))) as numeric(36,3))

OMG, you are issuing a sub-select and recomputing the HIVE per VESTS for each row! The DynamicGlobalProperties table provides such pre-computed value.
The WITH (NOLOCK) clause is no more required.

An optimized and correctly written query would be

DECLARE @account AS VARCHAR(16) = 'bala41288'
DECLARE @year AS INT = 2021
DECLARE @SOY1 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year))   -- start of year
DECLARE @SOY2 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year+1)) -- start of next year

DECLARE @HPV DECIMAL(19,8) = (SELECT hive_per_vest FROM DynamicGlobalProperties);

SELECT 
    YEAR(timestamp) AS [year],
    MONTH(timestamp) AS [month], 
    SUM(hbd_payout) AS totalHBDPayout, 
    SUM(hive_payout) AS totalHivePayout, 
    SUM(vesting_payout) AS totalVestingPayout, 
    SUM(vesting_payout) * @HPV AS totalHP
FROM 
    VOAuthorRewards
WHERE 
    author = @account 
    AND (timestamp >= @SOY1 AND timestamp < @SOY2)
GROUP BY 
    MONTH(timestamp)
ORDER BY 
    MONTH(timestamp)

I challenge you to update your others queries given the hint I provided here!

0
0
0.000
avatar

Great. I knew I was good in SQL but definitely not the best. Thanks, I have updated the post and my apps too. In my apps, I don't use declare, so didn't have to make big changes though.

OMG, you are issuing a sub-select and recomputing the HIVE per VESTS for each row! The DynamicGlobalProperties table provides such pre-computed value.

This is a good catch. I knew that was heavy but did not know there was a pre-computed value. This will definitely help to optimize all my queries I use elsewhere too.

Use MONTH(timestamp) and YEAR(timestamp), it's faster because it doesn't require parameters parsing.

That's good to know. I'm more used to SQL Server 2008 R2 and SQL Server 2012. Good to have these inbuilt functions in the latest versions.

Wrong test because you are excluding the very last day of the year.

Oh yeah, that's a mistake. I was going to use <= but missed the = part in the first two queries.

I challenge you to update your others queries given the hint I provided here!

You don't have to challenge me. 😀 You can just say or ask and I'm a good learner. 🙂 I'm also going to contact you on Discord to get your help to optimize a few more queries I regularly use. Hope it is okay. I don't want a poorly optimized query hitting your server multiple times a day.

0
0
0.000
avatar

I'm also going to contact you on Discord to get your help

Anytime

0
0
0.000
avatar

Hey, this was cool!
I would like to ask you, the data in December 2021 - are not complete, right? Because we are still waiting for some posts to pay out.
I was thinking of a way for someone to fetch all their posts of the past, for a specific hashtag, would that possible? i.e. If I wish to see all my posts with the tag hivestockphotos (because I did not keep them)
I may have some more ideas, I ll be in touch!
Thanks again and happy new year!

Posted Using LeoFinance Beta

0
0
0.000
avatar

Yeah there was a small bug in calculating the December 31st data. I have fixed it. It should hopefully be good now.

Yeah, I will try to add a feature to search based on tag soon. it is a big tricky I guess but let me give it a try. Thanks for the suggestion.

0
0
0.000
avatar
(Edited)

Wow this is a nice tool. I like that I can see the historical data in the past for my account as well.

Posted Using LeoFinance Beta

0
0
0.000
avatar

Glad you liked it. Cheers!

0
0
0.000