Data Request: Monthly Comment History
The following request was made in the #freecompliments discord server a few days ago:
This is in my wheelhouse. Like most advanced skills, it is easy if you know how, but the hard part is knowing how.
Software
I don't like to use SQL, but I have a lot of experience with accessing HiveSQL using Microsoft's PowerBI analytics software. It's a free download, and a powerful piece of software. We're going to dramatically under-use its capabilities for this project!
Data Source
We're going to use @arcange's HiveSQL database for this project. It's the easiest way for a non-engineer to access Hive data. It's publicly funded via the HiveDAO, so go support the latest proposal before you proceed any further!
There's a one-time fee for registration, but after that access is free (as long as it continues to receive HiveDAO funding)! I used to pay monthly for this, and it was worth every penny. Registration only takes a few minutes and then you get access credentials that you can plug into Power BI and you're on your way.
Choosing Tables
HiveSQL has a lot to choose from, and Power BI will run a lot faster if we only grab what we need. Since we want comments data, we're going to choose the 'Comments' table. We could also use TxComments, but I like Comments better.
Without getting too technical, it takes the comments from TxComments and enriches them with relevant data from other Tx tables. It's just better, in my opinion, but to each their own!
Select only what you need!
Import will download the whole table onto your computer (you can refresh it later to download more data). Do this if you're going to join with data from other sources.
DirectQuery will dynamically create SQL queries to pass to the database whenever you refresh your reports. Since we're working with only one table and keeping our query concise, that's what we'll use.
If your reports take longer than a second or two to refresh, switch to Import so that you're not abusing the database. Then you don't have to worry about it.
Building Reports
Once you're connected to HiveSQL using PowerBI, building reports is literally as easy as drag and drop. Seriously! Since I already had a .pbix file saved with my HiveSQL credentials, it took me only a few minutes to build the report I wanted. It took me at least ten times as long to create this guide as it took to build the report in the first place!
I chose 'Category' as a report-level filter and dropped in the community ID. The documentation tells us that 'Category' is the first tag. When you post in community, it defaults the community ID as the first tag (and you can't change). Comments automatically have the same category as the post they replied on, so this category will include everything that happens within the community page.
Additional filters are 'created' which is the date the comment was posted, and 'depth' which will identify whether it's a post or a comment.
For 'created' date, I used relative date filters (last 30 days, excluding today) so that it will automatically update the filter range whenever I open the file. You can also use previous # of weeks, months, etc. depending on your requirements. If @freecompliments decides the best usefulness for this data is monthly, then previous 1 month will be a better filter than previous 30 days.
Posts have a depth of 0, and comments have a depth of 1 or greater (top-level comment, response to comment, etc.) I initially filtered this as greater than or equal to 1 (correct!) but when I did a little revision, I forgot there was a zero and updated to it to greater than 1 (incorrect!) so now I've fixed it back.
For the table itself, I chose author, permlink, depth, and body. Depth wasn't needed, but I wanted to demonstrate that I included all the comments, not just top-level. I incorrectly chose parent_permlink the first time (the permlink of the post or comment receiving the reply) and it made it look like some comments were missing.
Voila, this table, which can be easily exported into a .csv format and shared via Discord, google sheets, or whatever! You can easily format that into markdown within Excel or Google sheets and drop into a post, too!
Just for kicks, I created a couple more quick visuals that make it easier for me to see what's happening in the data, so my total page looks like this:
The key advantage to this layout is the descending order by comment count on the right (actually still uses the wrong permlink field in the second visual; I better fix that). I can click on any name and it will automatically filter the left visual to show only their comments. Nice!
Go Forth and Build!!
Now you've seen how easy it is, go build! You've also seen how easy it is to make mistakes, but that just means it's fast to iterate. Collect requirements, build something quick, gather feedback, iterate! Working with data is fun and easy when you have the right tools.
This is an amazing write-up! Thanks so much for sharing this. 😊
This is very valuable information for anyone who wants to dig into the database and extract basic information. I can't help but envy smart minds like yours, even though I know a lot of is achieved through hard work and a constant uptake of knowledge as well.
Cheers, man!
Thank you for this essential post! Enjoy our curation, 5 HSBI, and a 14 day promotion! 😊
We'll also add an additional 20 HSBI because you brought in at least 10 new members here with your interview of @freecompliments.
Thanks! I'm happy to have been of help to your inspiring community.
Great information and write up. Thanks for sharing this with us.
I never knew one could do this… hehehe 🤭
Have a wonderful Tuesday!
Yes, so much cool data is available. This is really just barely scratching the surface.
I can imagine…
One just needs to know hehehe 🤭
Very interesting!
Too bad PowerBI analytics software is not available for the Mac, so I'm out of luck. One of these days I'm going to have to figure out how to use HiveSQL, both for better managing Blockchain Poets and for personal data too.
You can do all of the same within Excel, but I'm a lot less familiar with connecting up the data that way. Power BI was originally built as an analytics engine (Power Pivots?) within Excel, and then they built a better UI and spun it out into its own product. I find Power BI a lot easier to work with though, and it's better for data integrity since you can't change field contents by mistake.
!DHEDGE
This post has been selected for upvote from our token accounts by @freecompliments! Based on your tags you received upvotes from the following account(s):
- @dhedge.bonus
- @dhedge.neoxag
- @dhedge.waiv
@freecompliments has 4 vote calls left today.
Hold 10 or more DHEDGE to unlock daily dividends and gain access to upvote rounds on your posts from @dhedge. Hold 100 or more DHEDGE to unlock thread votes. Calling in our curation accounts currently has a minimum holding requirement of 100 DHEDGE. The more DHEDGE you hold, the higher upvote you can call in. Buy DHEDGE on Tribaldex or earn some daily by joining one of our many delegation pools at app.dhedge.cc.
I've always wanted to start playing with HiveSQL but haven't had the time to do it yet.
Thanks to your nice and helpful post I have a good starting point
!discovery 40
This post was shared and voted inside the discord by the curators team of discovery-it
Join our Community and follow our Curation Trail
Discovery-it is also a Witness, vote for us here
Delegate to us for passive income. Check our 80% fee-back Program
Fantastic!
Wonderful!
This type of content should be pinned in the Hive story!
Thank you very much for sharing such knowledge with us in such detail, allowing interested people (like me) the opportunity to access the Hive database.
I want to record that I am only seeing this post 11 days after it was published, and thanks to the promotion that is being applied!
For me, this post is the best content I have consumed on hive in 2023. I thank you and wish you great success!
Stay well, stay with !LUV
Translated by Google
Se você gostou do conteúdo deste comentário, do elogio recebido, ou está se sentindo para baixo e precisa de um ombro amigo... gostaria de tornar o dia de alguém um pouco melhor? ou tem um post interessante que gostaria de compartilhar e você acha que merece elogios? por favor, junte-se à comunidade FreeCompliments Recebemos a todos de braços abertos. :)
@josephsavage, @crazyphantombr(1/10) sent LUV. | connect | community | HiveWiki | NFT | <>< daily
! help(no space) to get help on Hive. InfoNoted... it'll be part of the nominations for post of the month! :D
Feel free to tag me if you want a post (that's in the community) included in the nominations in the future, since you're such an integral part of the community.
definitely yes!