使用SQL实现机器学习预测 - 云原生数据仓库AnalyticDB

本文介绍如何在云原生数据仓库 AnalyticDB MySQL 版中，利用SQL语言快速部署BST（Behavior Sequence Transformer）模型执行机器学习任务，包括从数据加工、模型创建、训练到最终的评估与预测的整个流程。

使用场景

您可以通过SQL查询快速部署模型训练和推理服务，轻松利用SQL来完成数据预处理、训练和推理。

AnalyticDB for MySQL支持部署BST模型，该模型适用于需要理解用户行为模式、分析用户偏好、预测未来趋势以及实现个性化推荐等应用场景。例如，在游戏或电子商务领域，通过捕捉用户行为之间的长期依赖关系，可以更准确地理解和预测用户的行为模式和偏好，从而提供个性化的服务和推荐。

更多BST模型的介绍

BST模型是一种用于处理行为序列数据的模型。该模型接收由行为事件ID组成的序列作为输入，并输出0或1，作为对这个序列分类的结果。

假设在一款游戏中，玩家的交互行为可以被记录并转换成一系列的行为事件。

例如，一位玩家在游戏中可能产生如下行为序列：登录、领取登录奖励、接受任务、战斗、战斗、战斗、完成任务、充值、战斗、登出。

这些具体的行为序列会被转化为以下事件ID序列：0、1、2、3、3、3、4、5、3、6。此序列随后将以字符串格式提供给BST模型。模型分析后，将返回一个分类结果，即0或1，用来指示特定的行为序列属于哪个预设类别。

前提条件

集群的产品系列为湖仓版。
集群的内核需为3.2.4.0及以上版本。
说明
查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。
已开通AI资源组功能。AI资源组功能处于公测阶段，请联系技术支持开通AI资源组功能。

步骤一：创建AI资源组并绑定普通资源组

使用SQL执行机器学习任务，需要两种资源组：

AI资源组：专为支持机器学习任务设计，用于管理机器学习任务所需的GPU资源，支持模型训练和预测中的计算密集型操作。
普通资源组：用于处理常规SQL查询，如生成训练数据或执行预测函数。

SQL会被首先提交至普通资源组执行，若执行过程中发现SQL有AI计算需求，则会自动投递AI计算到与之绑定的AI资源组上执行。

因此请先按照下列步骤创建AI资源组并绑定普通资源组。

登录云原生数据仓库AnalyticDB MySQL控制台，在左上角选择集群所在地域。在左侧导航栏，单击集群列表。在集群列表上方，选择产品系列，然后单击目标集群ID。
在左侧导航栏，单击集群管理>资源管理，单击资源组管理页签。

单击资源组列表右上角的新增资源组，并创建AI资源组。

参数名称	说明
资源组名称	自定义资源组的名称。输入长度在2~30位，以字母开头，只能包含字母、数字和下划线。
任务类型	在下拉列表中选择AI型资源组。重要如果没有AI选项，说明您的实例尚未开通AI资源组功能，请联系技术支持。
规格	GPU、CPU和内存的资源组合。可选择ADB.MLLarge.24、ADB.MLLarge.2或者ADB.MLAdvavced.6。
最小个数	资源组合的最小数量。
最大个数	资源组合的最大数量。

单击确定，完成新建资源组。
选择目标普通资源组操作列的修改，并使用ML作业投递规则将该资源组绑定到新创建的目标AI资源组。

步骤二：数据加工

数据加工是将业务中产生的原始数据转换为适合模型训练所需格式的过程。这一过程紧密依赖于三个关键因素：原始数据的具体形态、原始数据的存储位置以及模型对输入数据格式的要求。

模型训练数据表结构要求

输入特征列：此列为字符串类型，包含一系列以逗号分隔的整数。每个条目代表一个样本的特征向量。
目标标签列：此列包含两个可能的值（例如0或1），用于表示分类任务中的类别标签。

数据表结构示例：('1,2,3',0)，('3,2,1',1)。

数据加工方法

如果您的原始数据格式和模型训练所需格式有差异，请使用下面方法加工数据：

准备JAR包：把编写的Spark数据加工程序打包成的JAR包，上传到OSS存储空间。
配置Spark作业参数：在提交Spark作业时，配置作业需要的参数。配置参数详情请参见Spark应用配置参数说明。

如果您的原始数据符合模型训练所需格式，可省略此步骤。

步骤三：模型创建与训练

创建一个机器学习模型，定义模型参数、指定训练数据以及检查模型状态。

在左侧导航栏，单击作业开发 > SQL开发。
在SQLConsole中使用模型语句，创建模型并训练。

-- 创建模型
-- 指定资源组
/*+resource_group=itrain*/
CREATE MODEL bstdemo.bst-- 模型名称
OPTIONS (
  model_type='bst_classification', -- 模型类型
  feature_cols=(event_list),  -- 特征列
  target_cols=(target),  -- 要预测的目标列
  hyperparameters = (  -- 其他模型超参
    use_best_ckpt = 'False',
    early_stopping_patience='0'
  )
)
AS SELECT event_list, target FROM bstdemo.adb; -- 模型数据来源，此查询的结果将会提供给上述引用的变量进行训练

-- 查看模型训练状态，当状态进入READY后即训练完成
SHOW MODEL bstdemo.bst;

步骤四：模型评估

评估已经创建好的机器学习模型，验证模型正确性。

-- 语句形式与创建模型类似
/*resource_group=rg1*/
EVALUATE MODEL bstdemo.bst
OPTIONS (
    feature_cols=(event_list), 
    target_cols=(target),  
)
AS SELECT event_list, target FROM bstdemo.adb01;

步骤五：模型预测

从表中选取特征列，将这些数据传递给已训练好的模型，并获取基于这些输入数据的预测结果。

-- 使用训练好的模型进行预测；
-- ml_predict函数的第一个参数是模型名称，后续入参是模型的输入列；
SELECT ML_PREDICT('bstdemo.bst', event_list) FROM bstdemo.adb02;