Predicting gold prices with MindsDB and MongoDB
Tags: mongodb, mindsdb, ai, tutorial
Published: 2022-10-26 20:15:11

## Introduction
This tutorial will show how to use MindsDB to predict gold prices based on historical data stored in a MongoDB database.

### What is MindsDB?
MindsDB is an Open Source technology which adds machine learning capabilities to databases, enabling making predictions over standard databases through standard queries.

### What is MongoDB?
MongoDB is a popular non-relational document database that provides support for JSON-like storage.


![Image showing how MindsDB works. On the left side, an application which queries a traditional database. On the right, an application which queries MindsDB, which consults a traditional database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5olpkcjqbjq52od29158.png)


By using these two, we can predict data values in large databases. In this tutorial, we will predict the gold price over time using [this Kaggle dataset](https://www.kaggle.com/datasets/arashnic/learn-time-series-forecasting-from-gold-price).

---


## Setting everything up

MindsDB is available both using their cloud service [(cloud.mindsdb.com)](cloud.mindsdb.com) or locally, and so is MongoDB, available locally and in their hosted version - [MongoDB Atlas](atlas.mongodb.com). 

In this tutorial, I will focus in running both MongoDB and MindsDB in local docker containers, although it is quite similar to how the cloud version works - you can check out [the documentation](https://docs.mindsdb.com/connect/mongo-shell/)

### Requirements
- docker environment. For an introduction on what is Docker and how to use it, checkout [this tutorial](https://docs.docker.com/get-started/)
- docker-compose, to create and run the multi-container docker application
- [mongosh client](https://www.mongodb.com/docs/mongodb-shell/install/#std-label-mdb-shell-install), to run queries against the MongoDB instance.

## Creating the docker containers

First, we will create the docker containers. Create a folder and create a file named `docker-compose.yml`:
{% embed https://gist.github.com/yagueto/1f76fad5dbc5531d66466e6b801dbcf0.js %}

This will create two containers: mongodb and mindsdb. The first one uses default authentication and everybody will be able to access it. While this should **never** be publicly available, this is just a first-time approach and will do for the time being. We also use a network bridge between both containers to allow connections between them.

For making data persistent across restarts, create a folder inside the first one called `database`, which will be mounted by both containers and where data will be saved.

By now we should have the following directory structure:
```
.
└── gold-price-estimation/
    ├── docker-compose.yml
    └── database/
        └── ...
```
To start the containers, open a terminal, go inside the `gold-price-estimation` folder and run `docker-compose up -d` (you might need to have superuser privileges, depending on your configuration). You now have MindsDB and MongoDB running in your system!

Check this out by going to [127.0.0.1:47334](127.0.0.1:47334). You should find MindDB's web UI. 


![Screenshot of MindsDB's web UI, featuring a large text box for running SQL queries](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vq4u7s8zabrn9c5zmrig.png)



If something didn't work out, double-check the previous steps and if it persists feel free to leave a comment below.

## Importing the data in the database
After downloading [the .csv file from Kaggle](https://www.kaggle.com/datasets/arashnic/learn-time-series-forecasting-from-gold-price), we need to copy the file from the host to the container by running `docker cp file_name.csv mongodb:/file_name.csv`. 

To import the database, we need to first access the docker container with `docker exec -it mongodb bash`, after which we will be presented with a bash command line. Then, run:
```
mongoimport --type csv -d golddb -c price --headerline --drop gold.csv
```

Here, `golddb` is the name of the database and `price` is the table name.

Leave the container with `exit` and check whether the data is correctly imported: 
```
mongosh --host mongodb --port 27017
USE golddb;
db.price.find({}); 
```
This should output the first entries in the database.

## Create a connection from MindsDB to MongoDB

We need to specify MindsDB where our database is. For this, run:
```
mongosh --host mindsdb --port 47336
```
In the client, execute:
```
USE mindsdb;
db.databases.insertOne({ name: "mongo_gold", engine: "mongodb", connection_args: { "port": 27017, "host": "mongodb", "database": "golddb" } })
```
With this, we first connect our client to MindsDB’s database. Inside it, we select the database `mindsdb` and insert an entry with the parameters of the connection:
- `mongo_gold`: name by which mindsdb identifies the connection
- `mongodb`: URL/IP of the host, in this case the name of our docker container.

This command should return 
```
{
  acknowledged: true,
  insertedId: ObjectId("635433076e11662dcd20042c")
}
```
, which tells us that it has saved the configuration. Runnning
`db.databases.find()` confirms it by returning
```
{
    name: 'mongo_gold',
    database_type: 'mongodb',
    host: 'mongodb',
    port: '27017',
    user: null
}
```

All that remains is to create the predictor!

## Creating the predictor
While still inside the client, we run:

```
db.predictors.insert({ name: "mongo_gold_p", predict: "Value", connection: "mongo_gold", "select_data_query": "db.gold.find()" });
```
What do those parameters mean?
- `name`: name by which mindsdb identifies the predictor
- `predict`: name of the column in the database which values we want to predict
- `connection`: name we created previously by which Mindsdb identifies the connection
- `select_data_query`: this allows to specify specific rows in the database by using standard MongoDB queries. For this example, we will use all rows.

We should receive an acknowledged message, and then MindsDB will start working on creating the predictor. You can check the status of the model generation with:
```
db.predictors.find({name: "model_name"});
```

## Test it out!
Once the status is `complete`, it will be ready to answer to queries. 

Go ahead and predict a value with:
```
USE mongo_gold; 
db.mongo_gold_p.find({Date: "1975-07-01"})
```
If it returns the prediction (along with some expressions like confidence, among others - full description [in the documentation](https://docs.mindsdb.com/mongo/find/)), congratulations! 


<!--
- Docker containers

- Enter mongo container → import csv `mongoimport --type csv -d golddb -c price --headerline --drop gold.csv`

- Check in mongo if created

- Create connection: `mongosh --host mindsdb --port 47336`
    - Use mindsdb
    - ` db.databases.insertOne({ name: "mongo_gold", engine: "mongodb", connection_args: { "port": 27017, "host": "mongodb", "database": "golddb" } })
` 

- `db.databases.find()` returns `{
    name: 'mongo_gold',
    database_type: 'mongodb',
    host: 'mongodb',
    port: '27017',
    user: null
  }`

- `db.predictors.insert({ name: "mongo_gold_p", predict: "Value", connection: "mongo_gold", "select_data_query": "db.gold.find()" }); ` returns acknowledged

- `use mindsdb; db.predictors.find()`

- `use mongo_gold`; `db.mongo_gold_p.find({Date: "1975-07-01"})`
-->
## Conclusion
You have successfully created a MongoDB database which, extended thanks to the power of MindsDB, is able to generate predictions based on the data provided. 

For more info, [make sure to check out the docs](https://docs.mindsdb.com/mongo/collection-structure/)

Thank you for reading my tutorial, if it has been useful for you remember to give it a like and share it with your friends! Also, check out my [Github profile](https://github.com/yagueto).