Tutorial to Predict the Energy Usage using MindsDB and MongoDB

What is MindsDB?

Data that lives in your database is a valuable asset. MindsDB enables you to use your data and make forecasts. It speeds up the ML development process by bringing machine learning into the database.

With MindsDB, you can build, train, optimize, and deploy your ML models without the need for other platforms. And to get the forecasts, simply query your data and ML models. Read along to see some examples.

What are AI Tables?

MindsDB brings machine learning into databases by employing the concept of AI Tables.

AI Tables are machine learning models stored as virtual tables inside a database. They facilitate making predictions based on your data. You can perform time series, regression, and classification predictions within your database and get the output almost instantly by querying an AI Table with simple SQL statements.

What will we be learning in this tutorial?

Part 1 : Setting up the requirements

First and foremost, we will prepare our setup that is essential to start forecasting with MindsDB and MongoAPI.

1. Download MongoDB and MongoDB Compass
2. Getting started with MindsDB
3. Integrating MindsDB with MongoDB

Part 2 : Generating ML Models

We will see how to create and train ML models in our database. In this tutorial, we will be predicting the Energy Usage using this dataset.

1. Preparing the database
2. Understanding our Problem Statement
3. Creating the Predictor Model
4. Querying the Predictor Model

Part 1 : Setting up the requirements

We will be explaining this section briefly, so that we can move on to our predictions.

Download MongoDB and MongoDB Compass

To get started we must have both MongoDB Community Edition and MongoDB Compass installed and working in our systems.

Once you are done with the installation of both MongoDB and MongoDB Compass we can get going with our tutorial.

Getting started with MindsDB

MindsDB provides all users with a free MindsDB Cloud version that they can access to generate predictions on their database. You can sign up for the free MindsDB Cloud Version by following the setup guide. Verify your email and log into your account and you are ready to go. Once done, you should be seeing a page like this :

If you wish, you can choose to install MindsDB on your local system using docker image or by using PyPI. However, we will be working with Minds DB Cloud in this tutorial.

Integrating MindsDB with MongoDB

MindsDB provides us the ability to integrate with MongoDB using the MongoAPI. We can do so by following the given steps.

Open your MongoDB Compass. On the left navigation panel, You will have an option for a New Connection. Click on that Option and you will be provided with the details of your connection.

In the URI Section enter the following :

mongodb://cloud.mindsdb.com/

Click on the Advanced Connection Options dropdown. Here your host will be detected as MindsDB Cloud.

In the Authentication option enter your MindsDB Username and Password. Then click on Save and Connect, give your connection a name and select and color.

If you successfully create a connection you will be displayed a page similar to this :

In the bottom panel of this page, you will see the the Mongo Shell bar, enlarge it and type the following queries and click Enter.

> use mindsdb
> show collections

If you get a result like this, it means that we have succeeded in integrating MindsDB with MongoDB. Now let us move to the second part of our tutorial where we will be generating an ML model.

Part 2 : Generating ML Models

Preparing the database

We will be preparing our database on which we can run our queries and perform our forecasts. On the MindsDB Cloud console, click on the last icon in the left navigation bar. You will see a 'Select Your Data Sources' page. We can add a variety of data sources, however, for this tutorial we will be working with .csv files.

Go to the files section and click on Import File. Import your csv file and provide a name for your database table in which the contents of the .csv file will be stored. Click on Save and Continue.

We need to import the data to our MongoDB database. We can use the databases.insertOne() command for this purpose.

To do so, go to the Mongo Shell and type the following command :

db.databases.insertOne({
    name: "household_usage", // database name
    engine: "mongodb", // database engine 
    connection_args: {
        "port": 27017, // default connection port
        "host": "mongodb+srv://readonly:6CKCDJFzLIxOtKhu@cluster0.uhsuhui.mongodb.net/test", // connection host
        "database": "household_usage" // database connection          
    }
});

On clicking Enter, you must receive the following response :

If you get such a response, that means your database is successfully created!

Understanding our Problem Statement

We saw earlier that we will be predicting the household energy usage using this Kaggle dataset. Let us take a closer look into our database that we have set up. Our database consists of the following fields :

• TYPE : This is an information column. The value is 'Electric usage' for all the observations.
• DATE : Date of electric consumption. There is no timestamp in this field.
• START TIME : Start time of the consumption.
• END TIME : End time of the consumption
• USAGE : Consumption in kWh
• UNITS : This column denotes measurement unit. It is kWh for all the observations.
• COST : Cost of consumption in $.
• NOTES : Mostly an empty column

We can run the following query in our MindsDB Console to see our database where we can see all our fields :

SELECT * FROM files.houshold_energy LIMIT 10;

This is what will be displayed :

Now let us understand what we are trying to predict. We have been given a database consisting of various fields and we want to predict the energy usage according to its features. We are going to train an ML model that learns how the energy usage of a house varies according to the features. And once we have trained our model, we can input the details of a house and our ML Model will predict what its energy usage will be.

Sounds like a difficult task? Let us see how MindsDB can do that for us in a simple query!

Creating the Predictor Model

Now that our database is ready, we can go ahead and create our ML Model. As we have seen, The Predictor Model is basically a trained Machine Learning Model that can be used to predict or forecast a particular value known as the target variable or target value.

go to the Mongo Shell and type the following command :

db.predictors.insert({ name: "energy_usage_predictor", predict: "USAGE", connection: "household_usage", "select_data_query": "db.household_usage.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.

If there are no hiccups, we will get a Query Successfully Completed message.

{
  acknowledged: true,
  insertedIds: {
    '0': ObjectId("63efea4dc0d76e58c3b32e89")
  }
}

And that’s it! We have created and trained a Machine Learning model by a single query! That is the magic of MindsDB!

Querying the Predictor Model

We can see our machine learning model specifications by typing the following command in our Mongo Shell :

db.predictors.find({name:"energy_usage_predictor"})

When we press Enter we get all the details of our Predictor Model like its status, accuracy, target value and errors.

{
  NAME: 'energy_usage_predictor',
  ENGINE: 'lightwood',
  PROJECT: 'mindsdb',
  VERSION: 1,
  STATUS: 'complete',
  ACCURACY: 0.999,
  PREDICT: 'USAGE',
  UPDATE_STATUS: 'up_to_date',
  MINDSDB_VERSION: '23.1.3.2',
  ERROR: null,
  SELECT_DATA_QUERY: 'db.household_usage.find({})',
  TRAINING_OPTIONS: "{'target': 'USAGE', 'using': {}}",
  TAG: null
}

Now finally we can query our ML model to predict the target value of a particular entry.

The query for that is :

db.energy_usage_predictor.find({
TYPE: "Electric usage",
DATE: "10/23/2016",
STARTTIME: "2:00",
ENDTIME: "2:30",
UNITS: "kWh",
COST: "$0.01",
NOTES: ""})

And lo and behold! Our model predicts the energy usage of the house according to its attributes entered by us :

{
  DATE: '10/23/2016',
  COST: '$0.01',
  'START TIME': null,
  'END TIME': null,
  USAGE: 0.3982020318508148,
  select_data_query: null,
  when_data: null,
  USAGE_original: null,
  USAGE_confidence: 0.99,
  USAGE_explain: '{"predicted_value": 0.3982020318508148, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 0.36186112493276595, "confidence_upper_bound": 0.4345429387688637}',
  USAGE_anomaly: null,
  USAGE_min: 0.36186112493276595,
  USAGE_max: 0.4345429387688637,
  TYPE: 'Electric usage',
  STARTTIME: '2:00',
  ENDTIME: '2:30',
  UNITS: 'kWh',
  NOTES: ''
}

Conclusion :

Using MindsDB we have successfully created and trained a Machine Learning model in our database and unlocked the ability to generate in-database forecasts. You can visit the MindsDB Documentation to know the various features of MindsDB.

What’s Next?

If you enjoyed following along to this tutorial, make sure to Sign Up for a free MindsDB Cloud account and continue exploring! Kaggle is a great resource to find similar datasets and you can create and train an ML model of your own with the help of MindsDB. You can also check them out on GitHub.