Bring machine learning to your database
Does machine learning in your database even make sense ?
Machine learning is all about data. The more data you feed to your model, more accurate it's predictions are going to be. So, instead of having to establish a different mlops pipeline to have a model and use it for your product, it'll be much easier if you could just train some simple models and do it in your database itself. It'll help you:
Faster Insights: By embedding machine learning algorithms directly in your database, you can process data more quickly and gain insights faster.
Real-time processing: Machine learning algorithms can be used to process data in real-time, allowing you to make decisions quickly based on up-to-date information.
By performing analysis directly within your database, you can reduce the need for costly data transfers and processing.
Make machine learning more accessible: Machine learning can be used to analyze user behaviour and preferences, enabling you to deliver personalized experiences to each individual user. And this way developers who don't have much experience with machine learning can use it.
So, this makes sense. But how we do it ?
We'll use mindsdb. MindsDB enables you to use your data and make forecasts. It speeds up the ML development process by bringing machine learning into the database.
Mindsdb uses something known as ai tables.
what are ai tables ?
AI tables are special tables in a database that are designed to store data in a way that can be easily processed by artificial intelligence (AI) and machine learning algorithms. In simple terms, AI tables are like special containers for data that make it easier for computers to understand and work with the information stored in them.
Think of it like this: imagine you have a big box full of random things like books, toys, clothes, and so on. If you wanted to find a specific book in that box, it would take a lot of time and effort to search through all the items until you found it. However, if you organized all the items in the box by type (e.g., all the books together, all the toys together, etc.), it would be much easier to find what you're looking for.
That's essentially what AI tables do. They organize data in a way that makes it easier for computers to understand and work with. For example, if you have a database of customer information, you could create an AI table that organizes the data by things like age, location, their likes and dislikes. This would make it much easier for an AI algorithm to analyze the data and find patterns that could help you improve your business.
Try out mindsdb.
1. Create a MindsDB Cloud Account
Create your free MindsDB Cloud account to start practicing right away using the MindsDB Cloud Editor.
If you prefer a local MindsDB installation, follow the Deployment guides of MindsDB documentation. You can install MindsDB using Docker or follow the standard installation using pip.
2. Connect to MindsDB from a SQL Client
You can use the MindsDB Cloud Editor or open your preferred SQL client, such as DBeaver or MySQL CLI, and connect to MindsDB.
Using the MindsDB Cloud Editor
Log in to your MindsDB Cloud account. The Editor is the first thing you’ll see!
3. Connect a Database Using CREATE DATABASE
We have a sample database that you can use right away. To connect a database to your MindsDB Cloud account, use the CREATE DATABASE statement, as below.
CREATE DATABASE example_data
WITH ENGINE = "postgres",
PARAMETERS = {
"user": "demo_user",
"password": "demo_password",
"host": "3.220.66.106",
"port": "5432",
"database": "demo"
};
On execution, we get:
Query OK, 0 rows affected (3.22 sec)
4. Preview the Available Data Using SELECT
You can now preview the available data with a standard SELECT statement.
SELECT *
FROM example_data.demo_data.home_rentals
LIMIT 10;
On execution, we get:
+-----------------+---------------------+------+----------+----------------+---------------+--------------+--------------+
| number_of_rooms | number_of_bathrooms | sqft | location | days_on_market | initial_price | neighborhood | rental_price |
+-----------------+---------------------+------+----------+----------------+---------------+--------------+--------------+
| 0.0 | 1.0 | 484 | great | 10 | 2271 | south_side | 2271 |
| 1.0 | 1.0 | 674 | good | 1 | 2167 | downtown | 2167 |
| 1.0 | 1.0 | 554 | poor | 19 | 1883 | westbrae | 1883 |
| 0.0 | 1.0 | 529 | great | 3 | 2431 | south_side | 2431 |
| 3.0 | 2.0 | 1219 | great | 3 | 5510 | south_side | 5510 |
| 1.0 | 1.0 | 398 | great | 11 | 2272 | south_side | 2272 |
| 3.0 | 2.0 | 1190 | poor | 58 | 4463 | westbrae | 4124 |
| 1.0 | 1.0 | 730 | good | 0 | 2224 | downtown | 2224 |
| 0.0 | 1.0 | 298 | great | 9 | 2104 | south_side | 2104 |
| 2.0 | 1.0 | 878 | great | 8 | 3861 | south_side | 3861 |
+-----------------+---------------------+------+----------+----------------+---------------+--------------+--------------+
You could also browse the databases of MindsDB using the command below.
SHOW databases;
On execution, we get:
+---------------------+
| Database |
+---------------------+
| information_schema |
| mindsdb |
| files |
| example_data |
+---------------------+
To learn more about MindsDB tables structure, check out this guide.
5. Create a Model Using CREATE MODEL
Now you are ready to create your first model. Use the CREATE MODEL statement, as below.
CREATE MODEL mindsdb.home_rentals_model
FROM example_data
(SELECT * FROM demo_data.home_rentals)
PREDICT rental_price;
On execution, we get:
Query OK, 0 rows affected (9.79 sec)
6. Check the Status of a Model
It may take a couple of minutes until the model is trained. You can monitor the status of your model by executing the following command:
SELECT status
FROM mindsdb.models
WHERE name = 'home_rentals_model';
On execution, we get:
+------------+
| status |
+------------+
| generating |
+------------+
After a short time, we get:
+----------+
| status |
+----------+
| training |
+----------+
And finally, we get:
+----------+
| status |
+----------+
| complete |
+----------+
Alternatively, you can use the SHOW MODELS command as below.
SHOW MODELS
[FROM project_name]
[LIKE 'model_name']
[WHERE column_name = value];
Here is an example:
SHOW MODELS
FROM mindsdb
LIKE 'home_rentals_model'
WHERE status = 'complete';
The status of the model must be complete before you can start making predictions.
7. Make Predictions Using SELECT
The SELECT statement allows you to make predictions based on features, where features are the input variables, or input columns, that are used to make forecasts.
Let’s predict what would be the rental price of a 1000 square feet house with two bathrooms.
SELECT rental_price
FROM mindsdb.home_rentals_model
WHERE number_of_bathrooms = 2
AND sqft = 1000;
On execution, we get:
+--------------+
| rental_price |
+--------------+
| 1130 |
+--------------+
Here is how to make batch predictions:
SELECT m.rental_price, m.rental_price_explain
FROM mindsdb.home_rentals_model AS m
JOIN example_data.demo_data.home_rentals AS d;
Congratulations! If you got this far, you have successfully trained a predictive model using SQL and got the future data!