Using an SQL data source (Design Philosophy?)

[kristopher-wood]

Hi all!

I've been pondering how to design my River implementation and I've settled on using a time series database as my stream. It also occurred to me that I could store things like predictions, metrics, etc in separate tables so that I can analyze performance between runs with different parameters models, etc to see what performs best on the data I'm working with.

Therefore, this is more of a philosophical question than a technical one.

What are some pros and cons of integrating River with a database back end for storing the data stream and the output of the algorithm?

Has anyone in the community here done this before? If so, what worked and what didn't?

[MaxHalford]

I think there is no established way to do things. Whatever works for you is fine.

In my opinion, what is really important is to have a proper dataset to train and evaluate on. I say train and evaluate because, as you may know, a single dataset can be used for both tasks when you do online learning. It's what we call progressive validation. Therefore, the ideal setup is to have a dataset where you know the arrival times of the labels. This way, you can simulate a production scenario by showing the x and ys to the model in the exact same order as what happened in production. I wrote a blog post on this here.

Now, the way you obtain this dataset is entirely up to you. Using a database is fine. Writing your predictions and metrics to a database is fine too. In my experience, data scientists don't use databases too much for prototyping and instead go for files. I would say using a database is cleaner. But it's really a matter of taste and habit. Again, what's really important is this idea of having a good dataset to train and evaluate on.

In production, you might not be using a relational database, but instead a message queue. See this great article for more information.

Note that at some point I would like to develop a tool on top of River to assist in developing and productionalising online models. At the moment, we're lacking in patterns and tooling that we can all align on.

[kristopher-wood]

Hi Max,

Thank you for the awesome reply! In the time between making my initial post and reading your reply I got River streaming data from my database and it works just as beautifully as it did from CSV. :)

The next tasks will be saving the parameters for each version of the algorithm, its predictions, and its metrics to a table in the database for later analysis. Before I do that, though, I'll read the links you shared.

Thanks again!
Kris

[kristopher-wood]

One more quick reply here. You say:

At the moment, we're lacking in patterns and tooling that we can all align on.

What do you need in order to make that happen? My project is aimed at production rather than research so I'd be happy to help with anything that makes River more production oriented. ;)

[MaxHalford]

Well I have a lot of ideas in my head. I briefly summarised them here and here. Last year I even wrote a first sketch called Chantilly.

Two things are preventing me from doing this right now:

1. My day job is quite consuming.
2. I don't have a production scenario to work on. I'm worried that if I build this tool by myself I'm going to miss out on what people actually need. I need to talk to people from industry who have these needs, and potentially work on such a project with them.

[kristopher-wood]

Very nice! Chantilly is very similar to what I'm trying to implement for my prototype at the moment. I like how you've implemented it as an API. That answers my daemon vs instance question in my other thread as well.

I have two goals in using SQL for storage. First and foremost is to aid in creating a real-time dashboard from which I can watch the ground truth data flow in, and watch the predictions flow ahead, while also keeping an eye on how my changes impact metrics and performance.

Longer term, I want to experiment with temporally distant long term memory in making correlations / associations, and in determining optimal actions for persistent production online ML agents.