如何在数据库里面使用SQL语句直接调用Amazon机器学习服务进行推理

摘要

本文主要讲解了如何在Amazon Aurora数据库里面使用SQL语句直接调用Amazon Comprehend 和Amazon SageMaker机器学习服务进行推理，让业务后端开发人员即使没有机器学习知识，也可以快速使用最熟悉的SQL语句调用机器学习服务，为业务提升价值。

前言

如何为数据库无缝添加人工智能/机器学习（以下简写为AI/ML）能力？随着最近几年AI/ML的火热，业务后端开发人员也在考虑如何直接方便地为数据库添加AI/ML能力，更好地为业务服务。比如，直接使用SQL语句就可以对用户行为进行欺诈检测，预测流失，评估收入，评论情绪分析，产品推荐，广告推荐等等。

Amazon Aurora 是一种与 MySQL 和 PostgreSQL 兼容的关系数据库，专为云而打造，既具有传统企业数据库的性能和可用性，又具有开源数据库的简单性和成本效益。它的速度最高可以达到标准 MySQL 数据库的五倍、标准PostgreSQL 数据库的三倍。它可以实现商用数据库的安全性、可用性和可靠性，而成本只有商用数据库的 1/10。全球众多的客户都在使用Amazon Aurora存储核心业务数据。

Amazon Aurora于2019年11月就开始支持了AI/ML能力，通过业务后端开发人员熟悉的 SQL 编程语言，直接无缝集成调用Amazon Comprehend和 Amazon SageMaker；不需要拥有机器学习经验，不需要拷贝移动数据，不需要学习新的单独的工具，就可以将AI/ML的能力添加到您的应用程序中。Amazon Aurora 直接从优化后的底层调用 Amazon Comprehend和Amazon SageMaker，不经过应用程序层，因此访问延迟低、性能优、吞吐大、安全高，也不会影响到Amazon Aurora数据库的性能。您可以调用Amazon SageMaker 中可用的任何 AI/ML 模型，也可以使用 Amazon Comprehend 进行情绪分析。

Amazon Comprehend 是一项完全托管的SaaS自然语言处理 (NLP) 服务，使用机器学习来发现文本中的洞察信息。Amazon Comprehend 提供自定义实体识别、自定义分类、关键词提取、情绪分析、实体识别和更多 API，以便您可以轻松将自然语言处理集成到您的应用程序中。目前Amazon Comprehend支持包含英语、中文简体、中文繁体等共12种语言文字。[6]

Amazon SageMaker 是一项完全托管的机器学习平台，它可以帮助开发人员和数据科学家快速准备构建、训练和部署机器学习 (ML) 模型。全球有众多各行客户使用Amazon SageMaker训练、部署自己的业务模型。

业务场景和架构

假设您是一家公司的业务后端开发人员，日常需要设计数据库SQL语句进行业务数据处理。现在您有2张数据表，comments 表存放用户的评论信息，subscribe表存放用户的信息和行为统计数据。您想快速知道用户的评论是好是坏，您也想预测用户是否会订阅您公司的产品等。虽然您没有很深的AI/ML能力，但是您在想能否为您的数据添加机器学习能力，有没有简单快捷的方式降低难度。

通过前言中的介绍，您了解到可以直接在SQL语句中调用Amazon Comprehend服务分析用户评论的情绪；在SQL语句中调用Amazon SageMaker推理服务判断用户是否会订阅产品或服务。于是您投入了半天时间，在亚马逊云科技的控制台快速上手、独立实现了您的业务目标。架构图如下:

使用MySQL Workbench远程访问亚马逊云科技上的数据库Aamzon Aurora，通过SQL语句调用Amazon Comprehend和Amazon SageMaker服务。

演示步骤

下面将详细讲解和演示如何在Amazon Aurora里面直接调用Amazon Comprehend和

Amazon SageMaker服务进行推理：

1.账号环境准备

本文中的演示，使用了亚马逊云科技美东1（us-east-1）区域，为避免语言翻译上的歧义，截图都使用了英文界面。欢迎您实际运行此演示，如果您选择了不同的区域和语言界面，请注意界面上的一些差异。

1.1.开通亚马逊云科技账号，登录控制台

如果您还没有账号，请访问https://thinkwithwp.com/cn/ 免费注册账户。如果已有账号请跳过这一步，直接登录。

1.2获取Amazon SageMaker上已部署好模型的Endpoint的名称和ARN

注：如果您或您的团队不熟悉Amazon SageMaker，也可以暂时先跳过这一步。

这一步需要您已在Amazon SageMaker上部署好了模型，如果你还没有部署，请参考“在Amazon SageMaker里面部署模型 ” [2]

然后在Amazon SageMaker控制台上找到对应的endpoint，拷贝它的名称和ARN，如下图

本文演示以Amazon SageMaker案例“Customer Subscribe Prediction with Amazon SageMaker Autopilot”[3]为例，该示例中，我们使用UCI机器学习数据集中的Bank Makerting Data Set，基于数据集里面客户的信息，历史营销记录，营销活动信息，外部环境信息等数据，使用Amazon SageMaker AutoML训练出最优的机器学习模型，来预测用户是否对客户产品（如银行定期存款）是否订阅（“yes”或“no”）。您可以在Amazon SageMaker Studio下载打开链接上的Python Notebook文件，然后按提示逐步运行就可以训练出一个模型，并部署得到可用于推理的Endpoint。

注意：Amazon SageMaker Endpoint要和Amazon Aurora所在区域使用同一个区域。

1.3.创建Amazon Aurora 实例

Amazon Aurora支持MySQL 和 PostgreSQL引擎，如果要使用AI/ML能力，创建的Amazon Aurora MySQL集群需要Aurora MySQL 2.07.0或更高版本；如果要创建Amazon Aurora PostgreSQL集群，需要Aurora PostgreSQL 10或更高版本。

本演示使用Aurora MySQL 2.10.2.

打开Amazon RDS控制台，选择 Databases，然后点击 Create Database按钮
默认选择 Standard Create，默认选择Amazon Aurora
默认选择Amazon Aurora MySQL-Compatible Edition，默认选择Provisioned，在下拉框中选择Aurora(MySQL 5.7) 2.10.2 默认选择Production
输入集群名称，如 aurora-ml-database，设定admin的密码。
选择Burstable classes (includes t classes)，然后从下拉列表中选择db.t3.medium; 选择Don’t create an Aurora Replica
在Virtual private cloud (VPC) 下拉框里面选择 Create new VPC；在Subnet group下拉框里面选择 Create new DB Subnet Group；Public Access选择Yes[注意：本文出于演示目的选择了Yes，即数据库可以公开外网访问，强烈建议在上生产时一定选择No，即只能内网访问，外网不可以直接访问]；VPC security group选择 Create new，输入名称，如aurora-ml-securitygroup
其他保持不变，点击 Create database；然后等待几分钟创建成功。
确认Status为Available后，选择集群aurora-ml-database，在下面的 Connectivity & security tab里面，选择Select a service to connect to this cluster，从下拉框里面依次选择Amazon Comprehend， Amazon SageMaker，然后依次点击 Connect service；选择Amazon SageMaker时需要填入1.2步中拷贝的ARN。如果跳过了1.2步，这一步也跳过选择Amazon SageMaker。等待底下列表中添加的role从Pending变为Active。
进入IAM控制台，选择左边菜单Roles，然后搜索框输入rds-；然后分别点击上面创建的2个role链接，拷贝相应ARN
在RDS控制台拷贝记录Endpoints里面Type为Writer instance的Endpoint name
下面修改安全组权限。选中 aurora-ml-database-instance-1，然后点击Security Group
然后点击 Inbound rules tab，然后点击 Edit inbound rules 按钮
点击Source下拉框，选择Anywhere-IPv4，然后点击Save rules按钮

1.4.配置Aurora 集群参数组

我们需要为1.3步中创建好的Amazon Aurora数据库集群配置参数组。

点击Amazon RDS控制台左边菜单上的 Parameter groups，然后点击 Create parameter group按钮
选择 aurora-mysql5.7；选择DB Cluster Parameter Group；输入Group名称，如 aurora-ml-cluster-group1；然后点击Create按钮
然后在参数组列表中点击刚刚创建好的aurora-ml-cluster-group1；在搜索框搜索aws_default_，点击Edit parameters按钮，分别对应拷贝输入1.3步中创建的Comprehend，Sagemaker role的arn。如果之前跳过了Sagemaker的设置，这里aws_default_sagemaker_role的值留空即可。点击save changes按钮进行保存
接下来把新建的参数组修改到上面的Amazon Aurora集群上。点击左边菜单Databases，选择aurorl-ml-database集群；点击Modify按钮
在DB cluster parameter group列表里面选择刚创建好的aurora-ml-cluster-group1；然后点击页面最下端的Continue按钮。
点选Apply immediately；然后点击Modify cluster按钮
接下来重启Aurora集群。先停止，点击Actions列表中的Stop，等完全停止后，再点击Actions列表中的Start，等待Aurora集群状态变为Active。

1.5.下载客户端，并连接到Amazon Aurora 实例，创建样例表，插入样例数据

访问MySQL网站（https://dev.mysql.com/downloads/workbench/），根据您当前操作系统下载对应的MySQL Workbench。然后点击安装包按提示安装。

安装成功后，运行MySQL Workbench，选择最左上角图标，然后点击+创建 MySQL Connection
在Connection Name输入名称，如aurora-ml-database；在Hostname拷贝上面的writer instance的Endpoint name；username改为admin；点击Store in Keychain输入密码保存；然后点击Test Connection按钮确保能弹出左边表示连接成功的信息。点击OK按钮。点击Close按钮。
然后可以看到首页已有connection连接，点击aurora-ml-database

执行右边语句

创建数据库auroraml；

创建表comments，并插入5条记录。

假设该表主要记录用户对购买过商品的评论，我们想得到这些评论是消极抱怨的，还是积极夸赞的，还是中立的。

create database auroraml;

use auroraml;

CREATE TABLE IF NOT EXISTS `comments` (
 `comment_id` INT AUTO_INCREMENT PRIMARY KEY,
 `language_code` VARCHAR(255) NOT NULL,
 `comment_text` VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `comments` (language_code,comment_text) VALUES
("en","This is very useful, thank you for writing it!"),
("en","Awesome, I was waiting for this feature."),
("zh","产品质量很好，又不贵，喜欢."),
("en","I don’t like how this was implemented."),
("en","Horrible writing, I have read better books.");

执行右边语句，创建表 subscribe。假设该表主要记录了某银行的用户信息，我们想知道这些用户是否会订阅某产品。

相应字段解释可以参见参考资料[3]上的说明。

use auroraml;
CREATE TABLE IF NOT EXISTS `subscribe`(
       age bigint(20),
       job varchar(2048), 
       marital varchar(2048),
       education varchar(2048),
       default2 varchar(2048),
       housing varchar(2048), 
       loan varchar(2048), 
       contact varchar(2048), 
       month2 varchar(2048),
       day_of_week varchar(2048),
       duration bigint(20), 
       campaign bigint(20), 
       pdays bigint(20), 
       previous bigint(20), 
       poutcome varchar(2048),
       emp_var_rate double, 
       cons_price_idx double,
       cons_conf_idx double, 
       euribor3m double, 
       nr_employed double)ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行右边语句，插入5条记录，[注意：这些记录可以使用来自中前面1.2步骤中的SageMaker案例中的测试集]

INSERT INTO `subscribe`
VALUES (33,'admin.','married','university.degree','unknown','yes','no','cellular','aug','thu',59,1,999,0,'nonexistent',1.4,93.444,-36.1,4.968,5228.1);

INSERT INTO `subscribe`
VALUES (57,'blue-collar','married','basic.9y','unknown','yes','no','cellular','nov','fri',57,7,999,1,'failure',-0.1,93.2,-42.0,4.021,5195.8);

INSERT INTO `subscribe`
VALUES (49,'management','married','university.degree','no','no','no','cellular','apr','thu',289,1,999,1,'failure',-1.8,93.075,-47.1,1.41,5099.1);

INSERT INTO `subscribe`
VALUES (37,'technician','married','professional.course','unknown','yes','yes','cellular','jul','tue',177,1,999,0,'nonexistent',1.4,93.91799999999999,-42.7,4.961,5228.1);

INSERT INTO `subscribe`
VALUES (33,'admin.','married','university.degree','no','yes','no','cellular','sep','thu',272,2,999,0,'nonexistent',-1.1,94.199,-37.5,0.878,4963.6);

1.6.授权访问Amazon Comprehend和Amazon SageMaker

执行右边语句赋权SQL调用AI/ML机器学习服务

GRANT INVOKE SAGEMAKER ON *.* TO 'admin'@'%';

GRANT INVOKE COMPREHEND ON *.* TO 'admin'@'%';

2.调用Amazon Comprehend服务分析用户评论的情绪

从 Amazon Aurora调用 Amazon Comprehend 就像调用 SQL 函数那样轻松。Aurora 机器学习提供了两个内置的 Amazon Comprehend 函数： aws_comprehend_detect_sentiment() 和 aws_comprehend_detect_sentiment_confidence()，用于调用 Amazon Comprehend 进行情绪分析。

aws_comprehend_detect_sentiment() 返回’POSITIVE’, ‘NEGATIVE’, ‘NEUTRAL’, ‘MIXED’这4个值中的一个。’POSITIVE’表示正向的、积极的，’NEGATIVE’表示负向的、消极的，’NEUTRAL’表示中立，’MIXED’表示混合前面几种。

aws_comprehend_detect_sentiment_confidence()返回双精度值表示aws_comprehend_detect_sentiment结果的置信度。

具体语法如下，其中input_text 表示输入的文本，language_code表示文本的语言代码，可选参数max_batch_size表示批处理的数量，越大表示每次处理的文本数量越大，但是延迟会同步提高，实际使用时要有所平衡。

-- Returns one of 'POSITIVE', 'NEGATIVE', 'NEUTRAL', 'MIXED'

aws_comprehend_detect_sentiment(

  input_text

  ,language_code

  [,max_batch_size]  -- default is 25. should be greater than 0

)




-- Returns a double value that indicates confidence of the result of aws_comprehend_detect_sentiment.

aws_comprehend_detect_sentiment_confidence(

  input_text

  ,language_code

  [,max_batch_size]  -- default is 25. should be greater than 0.

)

在MySQL Workbench

执行右边语句对表 comments里面的用户评论进行情绪分析；

use auroraml;

SELECT comment_text,

aws_comprehend_detect_sentiment(comment_text, 'en') AS sentiment,

aws_comprehend_detect_sentiment_confidence(comment_text, 'en') AS confidence

FROM comments WHERE language_code ='en' ;




SELECT comment_text,

aws_comprehend_detect_sentiment(comment_text, 'zh') AS sentiment,

aws_comprehend_detect_sentiment_confidence(comment_text, 'zh') AS confidence

FROM comments WHERE language_code ='zh' ;

结果如下，您可自行对比文本确认返回的情绪分析是否正确，您也可以插入更多评论，包括不同语言的评论进行验证测试：

3.调用Amazon SageMaker服务判断用户是否会订阅

如果要调用Amazon SageMaker Endpoints，首先需要创建自定义函数，指定参数，调用的endpoint，返回结果类型，批大小等。注意不可以使用除numeric或 VARCHAR类型外的其他类型。

具体语法如下，

CREATE FUNCTION function_name (arg1 type1, arg2 type2, ...) -- variable number of arguments

  [DEFINER = user]                                             -- same as existing MySQL CREATE FUNCTION

  RETURNS mysql_type       -- For example, INTEGER, REAL, ...

  [SQL SECURITY { DEFINER | INVOKER } ]                         -- same as existing MySQL CREATE FUNCTION

  ALIAS AWS_SAGEMAKER_INVOKE_ENDPOINT   -- ALIAS replaces the stored function body. Only AWS_SAGEMAKER_INVOKE_ENDPOINT is supported for now.

  ENDPOINT NAME 'endpoint_name'

  [MAX_BATCH_SIZE max_batch_size];     -- default is 10,000

在MySQL Workbench

执行右边语句创建自定义函数；注意endpoint name来自1.2中拷贝的Amazon SageMaker endpoint名称。

use auroraml;

CREATE FUNCTION will_subscribe (

       age bigint(20),

       job varchar(2048),

       marital varchar(2048),

       education varchar(2048),

       default2 varchar(2048),

       housing varchar(2048),

       loan varchar(2048),

       contact varchar(2048),

       month2 varchar(2048),

       day_of_week varchar(2048),

       duration bigint(20),

       campaign bigint(20),

       pdays bigint(20),

       previous bigint(20),

       poutcome varchar(2048),

       emp_var_rate double,

       cons_price_idx double,

       cons_conf_idx double,

       euribor3m double,

       nr_employed double)

RETURNS varchar(2048) CHARSET utf8

       alias aws_sagemaker_invoke_endpoint

       endpoint name 'automl-subscribe-21-05-26-305MbF-012-496bdec821-06-33-29-ep';

授权访问已创建好的自定义函数 GRANT EXECUTE ON FUNCTION auroraml.will_subscribe TO 'admin'@'%';

执行右边语句查看SageMaker推理结果

SELECT *,will_subscribe (age,job,marital, education, default2, housing, loan, contact, month2,day_of_week,duration, campaign, pdays,previous,poutcome,emp_var_rate, cons_price_idx, cons_conf_idx, euribor3m, nr_employed

) will_subscribe FROM subscribe;

结果如下，您可以看到正常的推理出用户是否订阅的结果，yes表示会订阅，no表示不回订阅：

4.监控Amazon Aurora机器学习的性能

在MySQL Workbench里面运行下列命令，查看和机器学习性能相关的全局变量的值：

show status like ‘Aurora_ml%’;

可以使用 FLUSH STATUS 语句重置这些状态变量。

Aurora_ml_logical_response_cnt：Aurora MySQL 从 ML 服务接收的响应次数总和。

Aurora_ml_actual_request_cnt：Aurora MySQL 从 ML 服务接收的请求次数总和。

Aurora_ml_actual_response_cnt：Aurora MySQL 从 ML 服务接收的响应次数总和。

Aurora_ml_cache_hit_cnt：Aurora MySQL 从 ML 服务接收的内部缓存命中次数总和。

Aurora_ml_single_request_cnt：非批处理模式评估的 ML 函数总和。

类似下列截图：

5.清除环境

当你完成上述演示实验后，若不再需要实验环境，请及时清除创建的资源，避免带来成本上的开销。

打开Amazon RDS控制台，删除上面创建的集群，如aurora-ml-database
打开Amazon Sagemaker，删除上面创建的Endpoint，同时打开Amazon S3，删除相应桶里面的上传的训练文件、生成的模型等文件。

总结

通过上面的演示和讲解，您无需专业的机器学习知识，就可以简单快速地向现有的数据库应用程序添加基于AI/ML的能力；您也无需构建自定义集成服务或另外学习工具。您可以将机器学习处理作为对存储函数的调用直接嵌入到SQL查询中；您也无需将数据移出数据库即可执行机器学习操作；也无需转换或重新导入机器学习操作的结果即可在数据库应用程序中使用它们。

限于篇幅和时间，本演示只演示了在Amazon Aurora MySQL里面使用SQL语句调用Amazon Comprehend 和Amazon SageMaker机器学习服务进行推理，若您使用的是Amazon Aurora PostgreSQL，请参看官方文档[1]和实验教程[4]，博客[5]。

参考资料

[1]Amazon Aurora用户指南 https://docs.thinkwithwp.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html

[2]在SageMaker里面部署模型

https://docs.thinkwithwp.com/sagemaker/latest/dg/how-it-works-deployment.html

[3] Customer Subscribe Prediction with Amazon SageMaker Autopilot https://github.com/shenshaoyong/awssample/blob/master/SageMaker/Autopilot/autopilot_customer_subscribe_bankdataset.ipynb

[4] 动手实验Perform sentiment analysis with Amazon Aurora ML integration

https://thinkwithwp.com/getting-started/hands-on/sentiment-analysis-amazon-aurora-ml-integration/

[5] New for Amazon Aurora – Use Machine Learning Directly From Your Databases https://thinkwithwp.com/blogs/aws/new-for-amazon-aurora-use-machine-learning-directly-from-your-databases/

[6] Amazon Comprehend Supported Languages

https://docs.thinkwithwp.com/comprehend/latest/dg/supported-languages.html

[7] Amazon Aurora Labs for MySQL – Use SageMaker with Aurora

https://awsauroralabsmy.com/ml/sagemaker/

点击Amazon RDS控制台左边菜单上的 Parameter groups，然后点击 Create parameter group按钮
选择 aurora-mysql5.7；选择DB Cluster Parameter Group；输入Group名称，如 aurora-ml-cluster-group1；然后点击Create按钮
然后在参数组列表中点击刚刚创建好的aurora-ml-cluster-group1；在搜索框搜索aws_default_，点击Edit parameters按钮，分别对应拷贝输入1.3步中创建的Comprehend，Sagemaker role的arn。如果之前跳过了Sagemaker的设置，这里aws_default_sagemaker_role的值留空即可。点击save changes按钮进行保存
接下来把新建的参数组修改到上面的Amazon Aurora集群上。点击左边菜单Databases，选择aurorl-ml-database集群；点击Modify按钮
在DB cluster parameter group列表里面选择刚创建好的aurora-ml-cluster-group1；然后点击页面最下端的Continue按钮。
点选Apply immediately；然后点击Modify cluster按钮
接下来重启Aurora集群。先停止，点击Actions列表中的Stop，等完全停止后，再点击Actions列表中的Start，等待Aurora集群状态变为Active。

亚马逊AWS官方博客