Skip to content

Twitch Link Collector - Collecting Links Posted in Various twitch.tv Chats - UPDATED 2023.07.27

An application that collects every link that is posted in a twitch chat.

Originally posted on the 2022.12.23

Twitch Link Collector - TLC

Its purpose is to collect all urls and links posted in specific chatrooms on twitch.tv. The goal is to collect as many links as possible and find patterns and usage statistics. The application consists of three components, the link collector, a stats site and a database.

The following paragraphs describe the first implementation and idea. Each update to this project is under a new heading on this page. In the end this article should describe the journey from the beginning until the end with every step in between.

List of updates:

  • 2023.05.29
  • 2023.07.27

Link Collector

The link collector joins a given list of channels, if a message is sent in one of those channels it checks if the message contains any links. All links in a message are complied as a list and inserted into the database.

Chatrooms on twitch.tv are just basic irc rooms, so listening to them can be done via almost any irc library. Twitch even provides an anonymous way to connect to chatrooms, so there is no need for an account. In the future I would like to write my own irc listener, as i dont have a need for all the data you get via the twitch specific libraries.

Database

The database layout is very simple. It consists of one table per logged channel. The table consists of the columns, id, timestamp and the link. I chose not to log the users who post the links, since that data is not relevant to me.

I used a PostgreSQL database for the performance, but every other database can also work. When choosing you have to consider what chats you will be listening to, some chats post a lot more links than others.

Graph Site

It uses a library of Apache ECharts (https://echarts.apache.org/en/index.html) for Go called go-echarts/go-echarts (GitHub).

When running it queries the amount of links per logged channel and compiles the amounts in a bar chart at the very top. After that every other channel gets a line chart, starting a specified date.

Deployment

All the components are deployable using a docker stack. They all share a single custom network connecting all of them. Only the graph site needs to be accessed from a different network to be exposed to the internet.

Why

What am I doing with all the data?

I don’t know yet, right now I am only collecting it. After it ran for some months I will try to compile the data and make a list of the most posted domains.

Maybe the data will have some use in the future, possibly for my bachelors.

UPDATE - 2023.05.29

Some things have changed since I posted this back in 2022.12.23. Namely, the database used, the finding of links and graphing the data.

As of the 2023.05.29 I have logged over 5 648 033 links in 133 channels. These channels mostly consist of the top 100 channels and various german streamers added to the mix.

Database

When I started this project I used a PostgreSQL database, each channel was stored in a separate table. Each table consisted of a unique link id, the timestamp it was logged on and the link itself. Back then it was what seemed the smartest thing to do, to keep graphing queries quick, since I would only have to query one smaller table and not one giant table. This did work pretty good at the beginning.

Soon a channel hit the 500 000 link mark. Not a lot, but enough to make some queries very slow, e.g. filtering by domains using a regular expression. I kept continuing to make more and more precise queries and the time to run those became annoying and cumbersome.

Recently I switched to using ClickHouse (clickhouse.com) as the database to hold all data. Someone I know used it in a project of theirs, after investigating it a bit I thought why not, it can only get better.

Switching to ClickHouse was very easy, they provide a simple docker container with no configuration required, and a direct way to insert the data from a PostgreSQL database into a ClickHouse database. At the beginning I said I used a table per channel, doing that bit me in the ass, since I would have ot run one query per table. Thankfully ClickHouse provides a CLI client which you can also call using a bash and a python script. So I wrote a script that imported all tables:

The python script below just writes the command that I need to run to a file, could this be done in the bash script? Yes.

channelString = "<LIST OF CHANNELS HERE AS LIST SEPARATED BY COMMAS>"

if __name__ == '__main__':
    channelList = channelString.split(",")
    with open ("./clickhouse-imports.sh", "w") as f:
        for channel in channelList:
            command = "clickhouse-client --host <CLICKHOUSE CONTAINER IP HERE> --port 9000 --query "INSERT INTO links.all_links (channel, logged_on, link) SELECT '{0}', logged_on, link FROM postgresql('<POSTGRESQL CONTAINER IP HERE>:5432','links','{0}','<POSTGRES USERNAME>','<POSTGRES USER PASSWORD HERE>')"\n".format(channel)
            f.write(command)

This resulted in a file containing a bunch of these lines:

clickhouse-client --host <CLICKHOUSE CONTAINER IP HERE> --port 9000 --query "INSERT INTO links.all_links (channel, logged_on, link) SELECT '{0}', logged_on, link FROM postgresql('<POSTGRESQL CONTAINER IP HERE>:5432','links','{0}','<POSTGRES USERNAME>','<POSTGRES USER PASSWORD HERE>')"

Note when using that script: You will have to specify the --user <USERNAME HERE> --password <PASSWORD HERE> for your ClickHouse user when you set it up that way.

The import of all the data took seconds and worked flawlessly.

Now I have all channels in a single ClickHouse table. Consisting of the channel name, the link and when it was logged. Using a combination of the channel name and the logged on as a primary key.

Code

The main logger is still written in GoLang. I now use regex to find links, instead of checking if a word starts with http:// or https://. This gives better results and can be fine-tuned way better. It is also faster.

To keep up with huge amount of messages I spawn a simple goroutine per incoming message. These goroutines just consist of finding links and inserting them in into the database, nothing more. So far nothing broke and it works flawlessly.

Graph Site

The graph site has not been updated and probably never will. It was more of a test to see what I could have done, but it was not very usable and very static.

If I had to redo it, I would use a tool like Plotly or similar to make it.

Graphing

As previously stated I ditched the custom graphing site. I now use Grafana (grafana.com).

Graphing all this data is a lot easier and faster. You can easily switch between channels using custom variables and data ranges.

UPDATE - 2023.07.27

This is not a technical update, but more of an “update on my thoughts and ideas”.

The Data

The data itself is interesting but doesn’t tell you a lot. Sure, the date something has been posted on can be mapped to specific events and stuff, but not a lot more.

Logging if the channel it was posted in was live could also be interesting. Does service Y only get posted when the stream is live?

What about the status of a user? Are they a moderator, a subscriber and so on?

The Twitch Link Collector

For whatever reason I have to restart it every 24 hours, I don’t know why, attempts at trying to find a pattern have been unfruitful. A basic docker restart tlc-collector as a cron-job firing every 24 hours fixes it. Since the time to start is super tiny, it doesn’t really matter. I also choose a time in the middle of the european day, since most channels will be offline during that time.