Capstone Project V2
Second run at collecting data for my Capstone Project, however based on SQL Server in Azure with some improvements included.
A couple months after I finished my Capstone Project and shut off the server, I decided to spin a new version up that will start fresh and collect new data. Since I was continuously making changes and updating items during my first run, I had to stop and start it, so I might have lost the opportunity to collect some data. I also used this time to adjust what I used previously. Instead of using MySQL, I switched to using a SQL Server in Azure, as I wanted to gain some more experience with it.
The transition from MySQL to SQL Server in Azure was fairly straight forward. The most time consuming part involved manually adjusting the syntax specific items that was related to MySQL, and adjusting it for SQL Server. Primarily this involved the queries that were pre-made to work with MySQL. Most of it was an easy switch over, but some items there was not a 1:1 comparison which required some further research before I could get it working.
This was only ran temporarily from September 9th, 2023 at 02:00AM until December 31, 2023.
What changed?
Not much had changed with this run of the Capstone Project. It was mainly focused on changing the backend database portion and keeping the application running consistently instead of turning it on and off during development.
I made some small improvements but didn’t do too much with them, such as separating the total chat messages sent to track per day messages sent. I also added tracking for Hype Chats that was a new form of supporting streamers. However, about a day after I configured it to start tracking, Twitch completely got rid of it.
I also worked on importing the Capstone project data into SQL Server in Azure for historical purposes.
New Stats for V2
Over the couple of months of running this 24/7 during this run, a lot of data was able to be tracked. I increased the number of streamers being tracked to 501 to see if this was able to handle that increased load. And it seemed to work great.
- $23,019,190 worth of subscriptions!
- $1,392,281 worth of bits!
- 393,220,000 messages sent!
- 402,608 deleted messages!
- 1,611,308 timeouts!
- 23,814,672,323 seconds or 755.16 years users spent timed out across all channels!
- 168,504 bans!
- 25,059 raids with a total of 11,506,744 viewers that have raided the tracked channels/streamers!
- 205,038 viewers that have supported the tracked streamers
- 501 total streamers being tracked!
As mentioned in my previous run, about a week before I turned it off, I added tracking of all messages sent across all channels / streamers tracked. Since this was in place from the start of this run, it was able to gather a more thorough set of data for messages sent.
I think this was another great run from a data perspective. A lot of data was collected, and in all but one event type, we had more data collected compared to the last running of this project.
We had 131,357 fewer timeouts across the tracked channels, even though we had 74 more channels / streamers being tracked. And regardless of this decrease in the number of timeouts, there was a large increase in the amount of seconds / years that users spent timed out across all channels. An overall increase of 9,889,777,680 seconds or 313.61 years.
Overall
I think overall it was a success in getting the backend portion changed to SQL Server within Azure, and adjusting the project to leverage this database. I was able to gain some experience with it, which was something I was looking to do regardless of whether it was with this project or not.
Database statistics:
- 4,517,095 rows in the subscriptions table.
- 379,136 rows in the bits table.
- 108 rows in the messages table. (Each day, a new row was added at exactly 12:00 AM to track the total messages sent per day)
- 402,608 rows in the deleted messages table.
- 1,779,812 rows in the mod actions table (Includes timeouts and bans).
- 25,059 rows in the raided table.
- 205,038 rows in the viewers table.
