How To Visualize MindsDB Predictions with Tableau.

#mindsdb #machinelearning #sql #tableau

Introduction

In this tutorial, we'll use MindsDb to build, train, and query a machine learning model and then use Tableau to visualize the results.

If you're new to MindsDB, I recommend reading my previous article, in which I explain what MindsDB is, the problem it solves, and how to get started with it.

To complete this tutorial, you must have a working MindsDB connection, either locally or via cloud.mindsdb.com.

You can use this guide to connect to MindsDB Cloud.

The Data

This tutorial uses Apple's (APPL) stock price from September 1984 to November 2017. You can get the data here, save it as a CSV file, and upload it to MindsDB's cloud system with the file name apple_stock_price. You can learn how to upload the data here. The stock price data consists of:

Date: The trading date.
Open: The stock price at the start of the trading day.
Close: The stock price at the end of the trading day.
High: The stock's highest price throughout a trading day.
Low: The stock's lowest price throughout a trading day.
Volume: The number of shares exchanged during the trading day.
OpenInt: Included in the data but does not have any meaning, so it won't be when creating the predictor. Now that you've uploaded the file, you can query it to confirm the data is present.

SELECT Date, Open, High, Low, Close, Volume FROM files.apple_stock_price LIMIT 5;

Query Output.

Date	Open	High	Low	Close	Volume
1984-09-07	0.42388	0.42902	0.41874	0.4238807	23220030
1984-09-10	0.42388	0.42516	0.41366	0.42134	18022532
1984-09-11	0.42516	0.43668	0.42516	0.42902	42498199
1984-09-12	0.42902	0.43157	0.41618	0.41618	37125801
1984-09-13	0.43927	0.44052	0.43927	0.43927	57822062

Creating the Predictor.

This article will go over projecting the stock's closing price and visualizing it in Tableau.

To begin, create a predictor (MindsDB word for model) that uses the stock price to forecast the stock's closing price. You can learn more about building a predictor by checking here. The syntax for explaining time series predictions is near the bottom of the page.

Note: In machine learning, what you forecast in the data is referred to as a label, and what you train the model/predictor on, is referred to as a feature. The 'close' column in the data above is the label, while the others are features.

You can create the predictor using the SQL syntax below:

CREATE PREDICTOR mindsdb.apple_stock_predictor
FROM files
    (SELECT Date, Open, High, Low, Close, Volume
    FROM apple_stock_price)
PREDICT Close
ORDER BY Date
WINDOW 20
HORIZON 365;

The following describes the commands above:

CREATE PREDICTOR: Creates a predictor with the name apple_stock_predictor in the mindsdb table.
FROM files: Points to the table containing the data.
PREDICT Close: Dictates the column to predict.
ORDER BY: Shows the column to arrange the data during training.
WINDOW: Decides how many rows to "look back" into when creating a prediction. In this case, “always look back 20 rows” when making predictions.
HORIZON: Specifies the number of future predictions. The default is 1, for the code above, 365 future predictions. You can check the predictor status with the query below:

SELECT status
FROM mindsdb.predictors
WHERE name = 'apple_stock_predictor'

This syntax returns either of the following responses:

Generating.
Training.
Complete.
Error.

Now, forecast the stock price using the newly created predictor with the query below:

SELECT Date , Close as `Closing Price`
FROM mindsdb.apple_stock_predictor
JOIN files.apple_stock_price as a
WHERE a.Date > LATEST
LIMIT 5;

The LATEST keyword tells MindsDB to predict forward from the last recorded observation.

Query Output.

Date	Closing Price
2017-11-11	120.0069968945692
2017-11-12	125.92226412840009
2017-11-13	124.78782931643252
2017-11-14	127.94804057834217
2017-11-15	126.16535730239313

You can also check the predictor's accuracy with the query below to see how it performs.

SELECT accuracy
FROM mindsdb.predictors
WHERE name = 'apple_stock_predictor'

Query Output.

accuracy
0.534

The accuracy score ranges from 0 to 1, and from the result, the model will be accurate about half the time.

This tutorial will use Tableau Public to create visualizations of our predictions. Due to the inability of Tableau Public to connect to the MySQL database, the predictions will be saved as a .csv file and uploaded to Tableau using the text file option.

Connecting Tableau to MindsDB

Log into your Tableau account and create a new workbook.
From the connectors tab, select the MySQL option from the list of options.
Input "cloud-mysql.mindsdb.com" for Server, "3306" for Port, "mindsdb" for Database "your mindsdb cloud email" for Username, "your mindsdb cloud password" for Password, and Sign in.

Before you can visualize the predictions in Tableau, you must first choose a data source. And because the stock predictions in this article are generated using a SQL statement, you will need to create a custom SQL query in Tableau to generate the data source. To do this:

First, select the New Custom SQL at the left side of the window and use the query below to generate the stock prices for the next year. You can preview the results or directly load the data into Tableau.
```
SELECT Date , Close as `Closing Price`
FROM mindsdb.apple_stock_predictor
JOIN files.apple_stock_price as a
WHERE a.Date > LATEST
LIMIT 365
```
Create an extract of the data under the connection heading at the top right of the window. You do this to facilitate data conversion to the appropriate data type. The extraction should take one to two minutes.
Move to the Sheet tab and right-click on the Closing Price measure and Date dimension to convert their data types to Number(decimal) and Date, respectively. Additionally, when right-clicking on the Closing Price measure, choose the option to convert it to a continuous measure.
Drag the Closing Price measure to the row shelf and the Date dimension to the column shelf.
Finally, right-click on the Date dimension and switch from Year to Month. Change the visualization type to Line as well.

Here is the final visualization:

Conclusion.

This article explained how to visualize your model's predictions from MindsDB in Tableau. To learn more about MindsDB check out the documentation. Also, you can connect with the community on Slack to answer questions and share ideas.

Thank you for taking the time to read this article. If you enjoyed it, please forward it to others who might find it useful. You can also find me on Twitter | LinkedIn | GitHub. See you in my next article.