You can do machine learning using just SQL

The MindsDB[1] project I saw on GitHub recently made my eyes light up. It can perform machine learning related operations in the database. In other words, it can be built, trained, optimized and deployed using only SQL. With machine learning models, to get predictions, just query the data and the ML model.

MindsDB brings machine learning to the database by adopting the concept of AI tables. AI tables are machine learning models stored as virtual tables in a database. They help in making predictions based on data. You can perform time series, regression, and classification predictions in your database and get output almost instantly by querying an AI table using simple SQL statements.

Next, let’s look at a simple example provided by the official.

1. Apply for a free MindsDB cloud account so that you can experience it immediately. If you prefer local deployment, you can install their Docker version.

2. Connect to MindsDB from SQL client.

3. Use CREATE DATABASE to connect to the database. MindsDB has a sample database ready to use. Please use the CREATE DATABASE statement, as shown below:

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

After execution, you can get the following results:

Query OK, 0 rows affected (3.22 sec)

4. You can use standard SQL to preview the data, as shown below:

5. Use CREATE PREDICTOR to create a predictor:

CREATE PREDICTOR mindsdb.home_rentals_predictor
FROM example_data
(SELECT * FROM demo_data.home_rentals)
PREDICT rental_price;

After execution:

Query OK, 0 rows affected (9.79 sec)

6. Check the status of the predictor:

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

You will get the status of training or completed:

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

or

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

7. Execute prediction

The SELECT statement allows you to predict based on features, where the features are The input variable or column used to make the prediction. Now predict how much a 1000 square foot house with two bathrooms will rent for.

SELECT rental_price
FROM mindsdb.home_rentals_predictor
WHERE number_of_bathrooms=2
AND sqft=1000;

The results are as follows:

+--------------+
| rental_price |
+--------------+
| 1130 |
+--------------+

At this step, you have successfully trained a prediction model using SQL and obtained predicted data!

Features

1. Automatic data preprocessing, feature engineering and encoding

2. Classification, regression, time series tasks

3. No need for "traditional Deploy" to put the model into production

4. Get the model accuracy score and confidence interval for each prediction

5. You can join the ML model with existing data

6. Anomaly detection

7. Model interpretability analysis

8. Support GPU training

Support integration with the following databases:

Final words

It is really convenient to use machine learning using only SQL. For the technical details of MindsDB, you can visit the official document [2]. If it is helpful, please click to share with more friend.

Reference materials:

[1]MindsDB: https://github.com/mindsdb/mindsdb

[2]Documentation: docs.mindsdb.com

The above is the detailed content of You can do machine learning using just SQL. For more information, please follow other related articles on the PHP Chinese website!