Part 11: Replacing Spreadsheet with a MariaDB Sidecar Microservice

The Ticker News Sentiment Analysis System (TNSAS we’ll call it) has gone through quite a transformation lately. Not only have we taken advantage of our containerization to scale the tone analysis service independently from the content scraper service, but we have also taken advantage of Redis based caching to reduce the total number of calls sent to IBM Watson to an absolute minimum as well as providing a log of all scraped documents and their related analysis.

We report this data via the console and copy and paste to a spreadsheet for tracking over time. This is not ideal and require significant manual data collection over an extended period time to get data at a resolution small enough to enable something approaching a real time system. We need a solution that can stand up and collect data consistently and then report it per ticker.

Setting up a MariaDB Sidecar

Setting up MariaDB to use as an RDBMS that will store our tone results and pricing info is super simple. We simply add another container to our docker-compose.yml file. We ground this container to a physical on host volume store. This means the data stored will persist as we stop and start the service. To add MariaDB we add the following to our docker-compose.yml

With MariaDB you want to ground the version of the server using the specific version tag of the container. Here we use 10.1.22. You can also see we use .mysql subdirectory for storing the data on the host. We expose port 3306 so we can connect to it and provide our environment configuration file with the following configuration parameters:

Using a MariaDB Container

We start up our container ecosystem with docker-compose up and then use something such as MySQL Workbench, Navicat, or SquirrelSQL. We can see that the stockworks database already exists and that our .mysql subdirectory on the host is populated with MySQL.

We want to create a table where we can record our data.

We then install 

which is a pure python connector to MySQL. This is a bit easier to use than installing the MySQLDb library for python. We make a connection to the database

and then when we report the data, we also record it to mysql

And that’s it. We can now get a report on the data for a particular ticker

The updates happen automatically when we run the analysis so now we approach a solution that can operate in real-time.