Predicting Gold Price with MindsDB

Pritish Sinha — Sun, 23 Oct 2022 06:11:12 +0000

Introduction

In this tutorial, we'll create and train a machine learning model, or as we call it, gold_pred_model. By querying the model, we'll predict the gold prices through time series forecasting.

Make sure you have access to a working MindsDB installation, either locally or at MindsDB Cloud.

If you want to learn how to set up your account at MindsDB Cloud, follow this guide. Another way is to set up MindsDB locally using Docker or Python.

Let's get started.

Data Setup

Connecting Database

Link your chosen database to MindsDB using CREATE DATABASE query

CREATE DATABASE example_db
WITH ENGINE = "postgres",
PARAMETERS = {
    "user": "demo_user",
    "password": "demo_password",
    "host": "3.220.66.106",
    "port": "5432",
    "database": "demo"
    };

For this tutorial we are using kaggle's the Learn Time Series Forecasting From Gold Price data.

You can download the CSV data file here (we use the gold_price_data.csv file) and upload it via MindsDB SQL Editor.

Understanding the Data

We use the gold price dataset, where we have one column as date column and another represents value of gold at that time.
Run following SQL query to have an insight:

=== "Connecting as a file"

SELECT * 
FROM files.gold_price_data 
LIMIT 5;

=== "Connecting as a database"

SELECT * 
FROM example_db.demo_data.gold_price_data 
LIMIT 5;

Above query should return 5 rows from the gold_price_data database.
Below is the sample data stored

+-----------+-----+
| Date     |Value |
+-----------+-----+
|1970-01-01|35.2  |
|1970-04-01|35.1  |
|1970-07-01|35.4  |
|1970-10-01|36.2  |
|1971-01-01|37.4  |
+-----------+-----+

Where:

| Column  | Description                                                           | Data Type | Usage   |
| --------| --------------------------------------------------------------------------------------------|
| `Date`  | A date column (or other time step column)                             | `integer` | Feature |
| `Value` | A column that represents a metric or a value that you want to forecast| `integer` | Label   |

As dataset indicates we are going for time series forecasting for price prediction

What exactly is time series forecasting?

Rather than using outliers or categories to make predictions, time series forecasting describes predictions that are made with time-stamped historical data.

Training a Predictor

Let's create and train the machine learning model. For that, we use the #!sql CREATE PREDICTOR statement and specify the input columns used to train #!sql FROM (features) and what we want to #!sql PREDICT (labels).

CREATE PREDICTOR mindsdb.predictor_name (Use the name you want)

FROM databse_name                       (Use the Database Name)

(SELECT * FROM table_name)              (Use the training table name)

PREDICT target_parameter;               (Use the parameter to predict)

For out tutorial we gonna run below query:

CREATE PREDICTOR 
  mindsdb.gold_pred_model
FROM files
  (SELECT * FROM gold_price_data)
PREDICT Value;

This query should return successful status without any errors in the Result Viewer.

Status of a Predictor

A predictor may take a couple of minutes for the training to complete. You can monitor the status of the predictor by using this SQL command:

SELECT status
FROM mindsdb.predictors
WHERE name='gold_pred_model';

If we run it right after creating a predictor, we get this output:

+------------+
| status     |
+------------+
| generating |
+------------+

A bit later, this is the output:

+----------+
| status   |
+----------+
| training |
+----------+

And at last, this should be the output:

+----------+
| status   |
+----------+
| complete |
+----------+

Now, if the status of our predictor says complete, we can start making predictions!

Describing the Model

Now, that we have created a model and already trained it to do the prediction for us, let us dive deep into it and find out the minute details about the model.

MindsDB provides the DESCRIBE statement to explain the different attributes of the available models. We can describe a predictor in the following ways.

By Features

DESCRIBE mindsdb.model_name.features;

By Model

DESCRIBE mindsdb.model_name.model;

By Model Ensemble

DESCRIBE mindsdb.model_name.Model;

Here let's try Describe by Ensemble so we gonna run following query for that

DESCRIBE gold_pred_model.ensemble;

This statement returns a JSON-type object explaining the attributes used to select the best candidate model to do the target prediction.

Making Predictions

We are now ready to do our first prediction based on the features using the SELECT statement. We can predict the price of gold during specific time as run the query.

SELECT target_column_name                  (Name of the Target Column - Value)

FROM mindsdb.model_name                    (Model Name - gold_pred_model)

WHERE feature_name="value_of_the_feature"; (Feature Name - Date)

Conclusion

We have finally predicted the gold prices as we wanted by using a predictor model powered by MindsDB. As all of you would have noticed, it is so simple to get this task done. All you need to do is connect your database, import a dataset, and run a few simple queries to get the prediction model up and running.

What's Next?

Have fun while trying it out yourself!

Bookmark MindsDB repository on GitHub.
Sign up for a free MindsDB account.
Engage with the MindsDB community on Slack or GitHub to ask questions and share your ideas and thoughts.

Conclusion

Having finished this tutorial, I wanted to give you a quick recap of what we have learned. Initially, we created a MindsDB Cloud account, imported data, created a table using the cloud GUI, trained a Predictor model, explained its details in three different ways, and finally predicted the Gold rates. As this tutorial is over, it would be a great idea to sign up for your own MindsDB account and try it out.

My First Open Source Experience

Pritish Sinha — Thu, 15 Oct 2020 17:12:14 +0000

DEV Community: Pritish Sinha

Predicting Gold Price with MindsDB

Introduction

Data Setup

Connecting Database

Understanding the Data

Training a Predictor

Status of a Predictor

Describing the Model

Making Predictions

Conclusion

What's Next?

Conclusion

My First Open Source Experience

What I Learned From Hacktoberfest