機器學習選擇 Python 還是 R 語言？要不直接用 SQL 吧！

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

    MindsDB 簡介
    MindsDB 安裝
    使用案例
        配置 MySQL
        配置 MindsDB
        訓練模型
        預測結果
        刪除模型
        多目標預測
    總結

機器學習離不開數(shù)據(jù)，機器學習框架（TensorFlow、scikit-learn、Pytorch 等）通常都需要從 CSV 文件或者其他數(shù)據(jù)源讀取數(shù)據(jù)，其中主要的數(shù)據(jù)源就是數(shù)據(jù)庫。所以，無論你是使用 Python、R 還是其他編程語言，都需要編寫一些代碼獲取和準備數(shù)據(jù)；同時這種數(shù)據(jù)的格式轉換和傳輸都需要消耗一定的時間和資源。

如果能夠直接在數(shù)據(jù)庫中訓練機器學習模型并且進行預測，而且一切都使用 SQL 語句完成，是不是更加簡單高效呢??！今天我們給大家介紹一個基于數(shù)據(jù)庫的機器學習平臺：MindsDB，了解一下如何通過 SQL 語句實現(xiàn)機器學習預測模型。

如果覺得文章有用，歡迎評論??、點贊??、推薦??
MindsDB 簡介

MindsDB 是一個基于數(shù)據(jù)庫的開源 AI 平臺，通過直接為數(shù)據(jù)庫引入機器學習功能，從而提高工作效率。MindsDB 可以直接通過 SQL 語句創(chuàng)建、訓練、測試模型以及進行預測，目前支持的數(shù)據(jù)庫系統(tǒng)包括 MySQL、MariaDB、SQL Server、PostgreSQL、ClickHouse、MongoDB 等。

MindsDB 是一個基于 Python（Pytorch）的 AutoML 框架，也支持通過編碼進行模型的訓練和預測，除了數(shù)據(jù)庫之外還支持 CSV、Excel、JSON、文本文件、pandas DataFrame、URL、s3 文件等數(shù)據(jù)源。 MindsDB 使用 lightwood 代碼庫實現(xiàn)了簡化的自動機器學習。MindsDB 還提供了 JavaScript 和 Python 開發(fā)包，可以通過 HTTP 接口實現(xiàn)所有功能。

除此之外，MindsDB 還提供了一個圖形管理界面 MindsDB Scout，可以從數(shù)據(jù)庫、本地文件、或者外部數(shù)據(jù)源上傳、分析以及可視化數(shù)據(jù)，訓練模型以及查詢結果。

MindsDB 安裝

MindsDB 需要 python 3.6 以上版本，推薦使用虛擬環(huán)境。首先創(chuàng)建并激活環(huán)境：

python -m venv venv

Windows

.\venv\Scripts\activate

Linux、macOS

source venv/bin/activate

然后使用 pip 安裝 MindsDB：

pip install mindsdb

最后運行 MindsDB 服務：

python -m mindsdb

運行成功返回以下類似信息：

…
mysql API: started on 47335

• GUI available at http://127.0.0.1:47334/index.html
  http API: started on 47334

通過地址 http://127.0.0.1:47334 可以訪問 MindsDB API；通過地址 http://127.0.0.1:47334/index.html 可以訪問圖形界面 MindsDB Scout。

??其他的安裝方式（包括 Docker）和相關問題，可以參考官方文檔。

使用案例

接下來我們使用 MindsDB 和 MySQL 數(shù)據(jù)庫訓練一個預測消費的機器學習模型，并且對數(shù)據(jù)進行預測。

配置 MySQL

首先，MySQL 需要啟動 FEDERATED 存儲引擎支持；可以在啟動服務時使用 --federated 命令行參數(shù)或者在配置文件中的 [mysqld] 部分增加一行內(nèi)容：

federated

啟動 MySQL 服務后使用 SHOW engines 語句確認支持 FEDERATED 存儲引擎：

mysql> SHOW engines\G
*************************** 1. row ***************************
Engine: FEDERATED
Support: YES
Comment: Federated MySQL storage engine
Transactions: NO
XA: NO
Savepoints: NO
…

接下來我們需要準備一些訓練模型的數(shù)據(jù)，本文使用的示例數(shù)據(jù)來源于首爾的空氣污染測量數(shù)據(jù)，可以點此下載。其中 train.csv 是訓練數(shù)據(jù)，test.csv 是測試數(shù)據(jù)。在 MySQL 中執(zhí)行以下命令創(chuàng)建一個新的數(shù)據(jù)庫 data 和數(shù)據(jù)表 pollution_measurement 并加載訓練數(shù)據(jù)：

CREATE DATABASE IF NOT EXISTS data;

CREATE TABLE data.pollution_measurement(
measurement_date datetime,
station_code varchar(10),
address varchar(100),
latitude double,
longitude double,
so2 decimal(10, 5),
no2 decimal(10, 5),
o3 decimal(10, 5),
co decimal(10, 5),
pm10 decimal(5, 1),
pm25 decimal(5, 1)
);

LOAD DATA INFILE ‘train.csv’
INTO TABLE data.pollution_measurement columns terminated by ‘,’;

查看一下導入的訓練數(shù)據(jù)：

SELECT count(*) FROM data.pollution_measurement;

SELECT * FROM data.pollution_measurement LIMIT 5;

配置 MindsDB

首先為 MindsDB 創(chuàng)建一個新的配置文件 config.json，內(nèi)容如下：

{
“api”: {
“http”: {
“host”: “0.0.0.0”,
“port”: “47334”
},
“mysql”: {
“host”: “127.0.0.1”,
“password”: “Qwer!1234”,
“port”: “47335”,
“user”: “root”
}
},
“config_version”: “1.3”,
“debug”: true,
“integrations”: {
“default_mysql”: {
“enabled”: true,
“host”: “192.168.56.104”,
“password”: “pass1234!”,
“port”: 3306,
“type”: “mysql”,
“user”: “root”
}
},
“l(fā)og”: {
“l(fā)evel”: {
“console”: “DEBUG”,
“file”: “INFO”
}
},
“storage_dir”: “/storage”
}

其中的選項如下：

api[‘http’] ，用于配置 MindsDB http 服務器的啟動信息：
    host（默認 127.0.0.1）， mindsdb 服務器地址。
    port（默認 47334），mindsdb 服務器端口。
api[‘mysql’] ，用于配置通過 MySQL 協(xié)議進行數(shù)據(jù)庫集成的信息：
    user（默認 root）。
    password（默認為空）。
    host（默認為 127.0.0.1）。
    port（默認為 47335）。
integrations[‘default_mysql’]，指定集成類型，這里是 default_mysql。相關的配置項如下：
    user（默認 root）。MySQL 用戶名。
    host（默認 127.0.0.1）。MySQL 服務器地址。
    password，MySQL 用戶密碼。
    type，集成類型。包括 mariadb、postgresql、mysql、clickhouse、mongodb 等。
    port（默認 3306）。MySQL 服務的 TCP/IP 端口。
    enabled（true | false）。是否啟用該集成。
log[‘level’]，日志配置（可選項）：
    console，日志級別，包括 INFO、DEBUG、ERROR。
    file，日志文件位置。
storage_dir，mindsdb 默認和配置的存儲目錄。

然后重新啟動 MindsDB 服務：

python -m mindsdb --api=mysql --config=“C:\Users\dongx\venv\Scripts\config.json”

其中，–api 參數(shù)指定 API 的類型為 mysql.； --config 參數(shù)指定了配置文件的位置。

啟動服務之后會連接 MySQL 數(shù)據(jù)庫，創(chuàng)建一個新的數(shù)據(jù)庫 mindsdb，并且創(chuàng)建兩個 FEDERATED 存儲引擎表：

mysql> use mindsdb;
mysql> show tables;
±------------------+
| Tables_in_mindsdb |
±------------------+
| commands |
| predictors |
±------------------+
2 rows in set (0.01 sec)

其中，predictors 是用于存儲預測模型、訓練狀態(tài)、準確率、目標變量以及訓練選項的表：

mysql> show create table predictors\G
*************************** 1. row ***************************
Table: predictors
Create Table: CREATE TABLE predictors (
name varchar(500) DEFAULT NULL,
status varchar(500) DEFAULT NULL,
accuracy varchar(500) DEFAULT NULL,
predict varchar(500) DEFAULT NULL,
select_data_query varchar(500) DEFAULT NULL,
external_datasource varchar(500) DEFAULT NULL,
training_options varchar(500) DEFAULT NULL,
KEY name_key (name)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CONNECTION=‘mysql://root_default_mysql:Qwer!1234@192.168.56.1:47335/mindsdb/predictors’
1 row in set (0.00 sec)

訓練模型

接下來我們就可以通過 SQL 語句訓練一個新的機器學習模型，只需要往 mindsdb.predictors 表中插入一條數(shù)據(jù)即可，例如：

INSERT INTO mindsdb.predictors(name, predict, select_data_query)
VALUES (‘a(chǎn)irq_predictor’, ‘so2’, ‘SELECT * FROM data.pollution_measurement’);

其中，

name 是預測模型（predictor）的名稱，類型為字符串；
predict 是想要預測的目標特征，類型為字符串。示例中為二氧化硫（SO2）含量；
select_data_query，為模型提供訓練數(shù)據(jù)的查詢語句，類型為字符串。

該語句需要等待一段時間，訓練完成后 predictors 表中會生成一條記錄：

mysql> select * from mindsdb.predictors;
±---------------±---------±---------±--------±--------------------±--------------------±-----------------+
| name | status | accuracy | predict | select_data_query | external_datasource | training_options |
±---------------±---------±---------±--------±--------------------±--------------------±-----------------+
| airq_predictor | complete | 0.987 | so2 | MySqlDS: mysql/None | | |
±---------------±---------±---------±--------±--------------------±--------------------±-----------------+
1 row in set (0.61 sec)

其中 status 字段為 complete 表示模型訓練完成。
預測結果

預測模型訓練完成之后，還會創(chuàng)建一個模型表 airq_predictor。直接查詢模型表就可以獲取預測的目標變量、置信度以及解釋信息，例如：

SELECT
so2 AS predicted,
so2_confidence AS confidence,
so2_explain AS info
FROM airq_predictor
WHERE (no2 = 0.005)
AND (co = 1.2)
AND (pm10 = 5)\G

*************************** 1. row ***************************
predicted: 0.001156540079952395
confidence: 0.9869
info: {
“predicted_value”: 0.001156540079952395,
“confidence”: 0.9869,
“prediction_quality”: “very confident”,
“confidence_interval”: [0.003184904620383531, 0.013975553923630717],
“important_missing_information”: [“Station code”, “Latitude”, “O3”],
“confidence_composition”: {
“co”: 0.006
},
“extra_insights”: {
“if_missing”: [{
“no2”: 0.007549311956155897
}, {
“co”: 0.005459383721227349
}, {
“pm10”: 0.003870252306568623
}]
}
}
1 row in set (0.00 sec)

從結果可以看出，MindsDB 預測的二氧化硫數(shù)值為 0.0011565，置信度為 98.69 %。
刪除模型

如果想要刪除之前創(chuàng)建的預測模型，可以執(zhí)行以下 SQL 語句：

INSERT INTO mindsdb.commands values (‘DELETE predictor airq_predictor’);

多目標預測

另外，MindsDB 也可以訓練預測多個目標特征的模型，只需要為 predict 字段指定一個逗號分隔的預測目標。例如，使用以下語句創(chuàng)建一個預測 so2 和 pm25 的模型：

INSERT INTO mindsdb.predictors(name, predict, select_data_query)
VALUES (‘a(chǎn)irq_predictor’, ‘so2, pm25’, ‘SELECT * FROM data.pollution_measurement’);

然后使用以下語句進行預測：

SELECT
so2 AS predicted,
so2_confidence AS confidence,
so2_explain AS info
FROM
mindsdb.airq_predictor
WHERE
select_data_query=‘SELECT pm25 FROM mindsdb.airq_predictor limit 10’;

總結

本文介紹了如何利用 MindsDB 機器學習平臺編寫 SQL 語句實現(xiàn)預測模型，可以看出這種方式非常簡單易用。當然，MindsDB 還提供了一個更簡單的圖形用戶界面 Scout，意味著直接在頁面點擊即可完成模型的訓練和預測。

除了使用 SQL 語句之外，MindsDB 同樣支持編寫 Python 或者 Javascript 代碼創(chuàng)建預測模型，更多的功能和示例可以參考官方文檔 和 GitHub 示例。