PostgresML 教程：用 SQL 做机器学习

机器学习的总体趋势是将数据移动到模型的环境中进行训练。但如果我们反过来做呢？既然当今的数据库规模远大于机器学习模型，如果把模型带到数据集旁边，不是会更简单吗？

这正是 PostgresML 的核心思想——数据不动，代码进库。这种“反向逻辑”的机器学习带来了一系列切实可行的好处，足以让您重新思考“数据库”的概念。

什么是 PostgresML，以及为何选择它？

PostgresML 是一个基于流行的 PostgreSQL 数据库构建的全栈机器学习平台。它引入了一种称为“库内机器学习（in-database ML）”的新范式，让您无需在每一步使用单独的工具，即可在 SQL 中完成许多机器学习任务。

尽管相对较新，PostgresML 仍承诺带来以下确定性的优势：

• 库内机器学习：可直接在 PostgreSQL 数据库内训练、部署并运行机器学习模型。无需在数据库与外部 ML 框架之间频繁搬运数据，从而在各个环节提升效率、降低延迟。
• SQL API：如果您既热爱 SQL 又热爱机器学习，postgresml.org 也许会成为您最喜欢的网站。该平台允许您用 SQL 的 SELECT 语句来训练、微调并部署机器学习模型。对于不熟悉多种 ML 框架的数据分析师与科学家来说，这一特性可能会彻底重塑他们的日常工作流。
• 预训练模型：PostgresML 可与 HuggingFace 轻松集成，进而访问数百个预训练模型，如 Llama、Falcon、Bert、Mistral 等。
• 定制与灵活性：PostgresML 支持来自 Scikit-learn、XGBoost、LGBM、PyTorch 和 TensorFlow 的 50+ 算法。您可以直接在数据库中训练与部署针对多种监督学习任务的 ML 模型。
• 与现有生态集成：由于 PostgresML 本质上是数据库，凡是支持 Postgres 的环境（几乎到处）都能与之交互。如果用 SQL 做机器学习让您觉得有些“不习惯”，平台还为 16 种语言提供了 SDK（对 JavaScript、Python 与 Rust 的支持最佳）。

在本教程中，我们将沿着典型的机器学习工作流，试驾上述全部功能：

1. 加载数据
2. 预处理数据
3. 训练模型
4. 微调超参数
5. 部署到生产

……全部在 Postgres 数据库内部完成。让我们开始吧！

用 PostgresML 实现端到端的监督学习工作流

开始之前：注册 PostgresML 免费层

首先，在 https://postgresml.org/signup 创建一个免费账户：

接着，选择免费层，资源相当慷慨：

注册完成后，您将进入 PostgresML 控制台。在这里，您可以管理不同的“库内机器学习”项目及相关资源。

在“Manage”部分，您可以根据计算需求扩缩您的环境：

1. 安装并设置 Postgres

顾名思义，使用 PostgresML 需要在系统上安装 PostgreSQL。如果您还不熟悉 Postgres，它号称是世界上最先进的开源数据库（其官网如是说）。

以下是针对不同平台的 PostgreSQL 安装指南：

• Windows
• Mac OS
• Linux

我使用的是 WSL2，所以可以直接执行：

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib
$ sudo passwd postgres  # Set a new Postgres password
# Close and reopen your terminal and done!
技术支持

随后，用下面的命令验证安装：

$ psql --version
psql (PostgreSQL) 12.18 (Ubuntu 12.18-0ubuntu0.20.04.1)
技术支持

psql 是官方的 Postgres 客户端，可在终端中连接与管理数据库。

我理解对大多数人来说，在“丑陋”的终端里运行 SQL 并不理想，因此安装完 Postgres 后，您也可以安装如下 VSCode 扩展：

2. 连接到数据库

还记得您的 PostgresML 控制台首页吗？请在单独的标签页中保持打开：

现在，使用 psql，您可用自己的凭据输入以下命令，与 PostgresML 服务器建立连接：

$ psql -h "host" \
      -U "username" \
      -p 6432 \
      -d "database_name"
技术支持

如果您使用 VSCode 扩展，可按以下步骤操作（摘自该扩展的安装页）：

1. 打开命令面板（Ctrl + Shift + P）。
2. 搜索并选择“PostgreSQL: New Query”。
3. 在命令面板中选择“Create Connection Profile”。根据提示输入您的 Postgres 实例的主机名、数据库、用户名与密码。

这样就连接到了您的 PostgresML 数据库。

下一步，创建允许我们运行 PostgresML 函数的 pgml 扩展：

CREATE EXTENSION IF NOT EXISTS pgml;

NOTICE:  extension "pgml" already exists, skipping
CREATE EXTENSION
-- Your output may be different.
技术支持

打印 pgml 版本以检查是否成功：

SELECT pgml.version();

2.8.2 (98f114891db58acd472e068c44272b198274b11d)
技术支持

3. 加载数据

现在，让我们把一些数据加载进数据库。为简单起见，我们只用Kaggle 的 Diamonds 数据集创建单个表。您可以从网站下载 CSV 文件，或使用下方 Python 代码片段：

import seaborn as sns

diamonds = sns.load_dataset("diamonds")
diamonds.to_csv("diamonds.csv", index=False)
技术支持

然后，使用正确的模式运行以下 CREATE 命令：

CREATE TABLE IF NOT EXISTS diamonds (
   index SERIAL PRIMARY KEY,
   carat FLOAT,
   cut VARCHAR(255),
   color VARCHAR(255),
   clarity VARCHAR(255),
   depth FLOAT,
   table_ FLOAT,
   price INT,
   x FLOAT,
   y FLOAT,
   z FLOAT
)
技术支持

该命令会创建名为 diamonds 的表，我们随后可用 CSV 文件中的行来填充它：

INSERT INTO diamonds
   (carat, cut, color, clarity, depth, table_, price, x, y, z)
   FROM '~/full/path/to/diamonds.csv'
   DELIMITER ','
   CSV HEADER;
技术支持

最后，打印表头部数据，以确认上一条命令成功执行：

SELECT * FROM diamonds
LIMIT 10;
技术支持

现在，我们可以开始训练模型了！

4. 训练模型

基础训练

监督学习模型的训练使用 pgml.train 函数。其基础结构如下：

SELECT pgml.train(
 project_name => 'Diamond prices prediction',
 task => 'regression',
 relation_name => 'diamonds',
 y_column_name => 'price',
 algorithm => 'xgboost'
);
技术支持

上述 SQL 语句会为 diamonds 表拟合一个 XGBoost 回归器，用于预测钻石价格。运行该命令时，您会注意到它非常快（几乎瞬间完成），尽管我们有 5 万多行数据。

切换任务也很容易。下面演示如何拟合一个多分类器（别忘了更改项目名称）：

SELECT pgml.train(
 project_name => 'Diamond cut quality prediction',
 task => 'classification',
 relation_name => 'diamonds',
 y_column_name => 'cut',
 algorithm => 'xgboost',
 test_size => 0.1
);
技术支持

请注意这次我们指定了 test_size 参数——也支持自定义数据切分！

我们还将默认算法 linear 改成了 xgboost，当然也可以选择 Scikit-learn 或其他受支持框架中的任一模型。

预处理

这次我们来尝试拟合一个随机森林模型：

……

等等！diamonds 数据集中包含需要编码的文本特征。由于 XGBoost 能原生处理类别变量，之前的查询没有报错。但随机森林可能并非如此。因此，让我们在查询中加入一些预处理步骤：

SELECT pgml.train(
   project_name => 'Diamond prices prediction',
   task => 'regression',
   relation_name => 'diamonds',
   y_column_name => 'price',
   algorithm => 'random_forest',
   preprocess => '{
       "carat": {"scale": "standard"},
       "depth": {"scale": "standard"},
       "table_": {"scale": "standard"},
       "cut": {"encode": "target", "scale": "standard"},
       "color": {"encode": "target", "scale": "standard"},
       "clarity": {"encode": "target", "scale": "standard"}
   }'::JSONB
);
技术支持

预处理逻辑在一个 JSONB 对象中定义，用以将各列映射到相应的变换步骤。对数值特征，我们使用标准化（虽然对随机森林并非必需）。对于类别列，我们先使用目标编码，再进行标准化。

PostgresML 还提供了其他可选的预处理函数。以下是可用的编码方式完整列表：

pgml 也支持常见的缺失值填补技术：

以下是可用的缩放器列表：

虽然它不如 Scikit-learn 那样拥有庞大的预处理器套件，但对大多数监督学习任务而言已足够。

指定超参数

到目前为止，我们一直在使用算法的默认参数。然而在真实问题中，默认值几乎从来不是最优选择。若要指定自定义值，可使用 pgml.train 的 hyperparams 参数：

SELECT pgml.train(
   project_name => 'Diamond prices prediction',
   task => 'regression',
   relation_name => 'diamonds',
   y_column_name => 'price',
   algorithm => 'xgboost',
   hyperparams => '{
       "n_estimators": 1000, "max_depth": 5, "eta": 0.01, "subsample": 0.7
   }'::JSONB
);
技术支持

这一次，我们拟合了一个拥有 1000 棵树、最大深度为 5、学习率 0.01 的 XGBoost 回归器。眼下我们只是“盲射”，因此这些超参数未必能带来理想结果。

超参数调优

要把模型效果“卷”到极致，监督学习任务中总少不了超参数调优。pgml 在 train 函数中也支持该流程。下面演示如何对一小组超参数组合执行网格搜索：

SELECT pgml.train(
   'Diamond prices prediction',
   algorithm => 'xgboost',
   search => 'grid',
   search_params => '{
       "max_depth": [5, 7],
       "n_estimators": [1000, 1500],
       "eta": [0.1, 0.01, 0.001]
   }'::JSONB
);
技术支持

此时，我只想让您品味一下我们正在做的事：我们正在用 SQL 来调优 XGBoost 回归器的超参数！这句话我从没想过自己会写出来。

5. 评估模型

在撰写本教程时，PostgresML 的评估工具仍不够完善。我们目前只有 predict 函数可用。以下演示如何对一个新的单样本进行预测：

SELECT pgml.predict(
   project_name => 'Diamond prices prediction',
   features => ARRAY[0.7, "Ideal", "A", "IF", 67.9, 56.9, 7024, 5.85, 5.87, 3.71]
);
技术支持

predict 会自动使用您实验中表现最佳的模型。对于回归任务，它会选择 R-squared 最高的模型；而分类任务则选择 F1 分数最高的模型。

如需指定某个具体模型进行预测，您可以提供其 ID：

SELECT pgml.predict(
  2' -- Use model with ID of 2,
   features => ARRAY[0.7, "Ideal", "A", "IF", 67.9, 56.9, 7024, 5.85, 5.87, 3.71]
)
技术支持

但如何找到模型 ID 呢？下面的代码片段会打印包含所需信息的精美表格：

SELECT models.id, models.algorithm, models.metrics
FROM pgml.models
JOIN pgml.projects
 ON projects.id = models.project_id
WHERE projects.name = 'Diamond prices prediction';
技术支持

通过这条语句，您可以发现许多关于 pgml 的变量与度量，如 models.id、pgml.models、pgml.projects 等等。试着对它们执行 `SELECT` 语句，感受一下它们的作用。

6. 部署模型

与传统 MLOps 不同，在 PostgresML 中这一步最为轻松。pgml 扩展会自动将您实验中表现最好的模型暴露为默认模型。因此，用户发往服务器的任意 SELECT pgml.predict 语句都会使用该模型进行推理。

如果您想更精细地控制要投产的模型，可使用 pgml.deploy 函数。其结构如下：

SELECT * FROM pgml.deploy(
   project_name TEXT,
   strategy TEXT DEFAULT 'best_score',
   algorithm TEXT DEFAULT NULL
)
技术支持

除了 best_score，还提供 most_recent 与 rollback 等部署策略：

通过组合 strategy 与 algorithm 参数，您可以进一步缩小部署范围。比如，下面这条语句会部署得分最高的 XGBoost 模型，而不是整个项目中表现最好的模型。

SELECT * FROM pgml.deploy(
   project_name => 'Diamond prices prediction',
   strategy => 'best_score',
   algorithm => 'xgboost'
)
技术支持

使用 PostgresML 还能做什么？

监督学习只是 PostgresML 的一小部分。事实上，PostgresML 的核心价值主张是“在 Postgres 中构建 AI 应用”。为此，他们甚至在首页放了一个小型 SQL 编辑器：

您也可以直接在本教程所用的环境中运行这些示例！

那么，如果您想用 PostgresML 构建一个面向消费者的机器学习服务，应该如何落地？以下是一些可能的步骤（在您已有模型的前提下）：

1. 构建用户界面——多半是一个网站。如今，借助 Streamlit、Taipy 这类专为 ML 应用设计的工具，您可以在数小时内搭建好网站。
2. 用服务器端语言（如 Python 或 Node.js，取决于您的前端实现）编写后端组件。
3. 使用 psycopg2（Python）或 pg-promise（JavaScript）等库连接到您的 PostgresML 数据库。
4. 通过 SQL 查询，从数据库表中取回预测所需的数据。
5. 在将数据送入机器学习模型前，在后端代码中执行必要的数据预处理步骤。
6. 当用户在界面中进行交互并触发预测时，后端服务器接收相关信息并调用 pgml.predict。

这只是一个粗略的框架，具体实现会因用例而有较大差异。

结语

PostgresML 为机器学习引入了全新的范式，要掌握它并非一篇教程就能涵盖。为加深理解并探索更多用法，请阅读 PostgresML 文档中的用例部分。

如果您的 SQL 技能稍显生疏，可以学习 DataCamp 的短课程 创建 PostgreSQL 数据库，或者直接学习完整的 SQL 数据分析师职业路径。

鉴于 PostgresML 高度聚焦 AI，您也可以补充一些 AI 基础知识：

• 大型语言模型（LLM）概念
• 如何用 LangChain 构建 LLM 应用
• 微调 LLM 应用