亚马逊AWS官方博客

使用Amazon Redshift ML构建机器学习应用

Amazon Redshift ML 使数据分析师和数据库开发人员可以在 Amazon Redshift 数据仓库中使用熟悉的 SQL 命令轻松创建、训练和应用机器学习模型。 借助 Redshift ML，用户可以利用 Amazon SageMaker，这是一项完全托管的机器学习服务，而无需学习新工具或语言。 只需使用 SQL 语句，便可利用Redshift 数据创建和训练 Amazon SageMaker 机器学习模型，然后使用这些模型进行预测。

Amazon Redshift 支持有监督学习，包括回归、二分类和多分类。回归是指预测连续值的问题，例如客户的总支出。二分类是指预测两种结果之一的问题，例如预测客户是否流失。多分类是指预测许多结果之一的问题，例如预测客户可能感兴趣的项目。数据分析师和数据科学家可以使用它来执行有监督学习，以解决范围包括预测、个性化或客户流失的各种问题。

在本篇文章中，我们将介绍使用标准 SQL 在Redshift 集群上使用机器学习。我们继续使用银行营销数据集，有关数据集的介绍，请参考第一篇《使用 Amazon SageMaker 构建机器学习应用》。首先使用Amazon Redshift的COPY命令把数据集导入到table中，然后使用CREATE MODEL方法创建模型，如下图所示，在创建模型的过程中，会调用Amazon SageMaker Autopilot，SageMaker Autopilot 将自动探索不同的解决方案以找到最佳模型，并通过Amazon SageMaker Neo将模型编译，最终将编译后的模型部署到Amazon Redshift Cluster中，通过Redshift Function调用模型进行预测。

1.首先登陆到AWS控制台，然后导航到Amazon Redshift页面，点击右侧的”Create cluster”按钮。

  • 在”Cluster identifier”中填入集群的名称，例如”redshift-cluster-1″
  • 选择”Free trial”
  • 在”Admin user name”中填入”awsuser”
  • 在”Admin user password”中填入密码，密码长度至少8位，其中至少包含一个数字，一个大写字母以及一个小写字母。

等待几分钟后，Redshift集群会创建成功。

2.为Redshift集群创建Role

Redshift集群需要用到三种策略。

  • AmazonS3FullAccess
  • AmazonRedshiftFullAccess
  • AmazonSageMakerFullAccess

登陆到控制台，导航到IAM，并进入到Roles页面，点击“Create role”。

在AWS service中选择“Redshift”，在“Select your use case”中选择“Redshift – Customizable”，点击“Next:Permission”，

选择3个Policy Name：AmazonRedshiftFullAccess，AmazonS3FullAccess，AmazonSageMakerFullAccess

保持默认，点击”Next:Tags”，保持默认，点击”Next:Review”，在Role Name中输入”testredshiftrole”，点击”Create role”。

在Redshift ML训练过程中，会调用到SageMaker AutoML的功能，因此要增加对SageMaker服务的访问权限。

在”testredshiftrole”中点击”Trust relationships”，”Edit trust relationship”，追加以下的json代码。

{
      "Effect": "Allow",
      "Principal": {
        "Service": "sagemaker.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
 },

点击”Update trust policy”，保存后效果如下图所示。

在”testredshiftrole”的详细信息页面，记录下ARN。

3.关联角色

在刚刚创建的集群页面，点击”Properties”

点击”Associate IAM role”按钮，选择刚刚创建的”testredshiftrole”，如下图所示。

4.导入数据集

首先从链接下载数据集（https://archive.ics.uci.edu/ml/machine-learning-databases/00222/），压缩包中有三个文件，我们把bank-additional-full.csv作为训练集，bank-additional.csv作为测试集。 为了方便识别训练文件和测试文件，我们把bank-additional-full.csv重命名为bank-train.csv，bank-additional.csv改名为bank-test.csv。把修改后的两个文件上传到S3存储桶中。

返回到Redshift的页面，在左侧的导航栏中点击“Query editor”，点击“Connect to database”

在Query 1中输入以下内容，并点击”Run”按钮

CREATE TABLE bank_details_training(
   age numeric,
   job varchar,
   marital varchar,
   education varchar,
   "default" varchar,
   housing varchar,
   loan varchar,
   contact varchar,
   month varchar,
   day_of_week varchar,
   duration numeric,
   campaign numeric,
   pdays numeric,
   previous numeric,
   poutcome varchar,
   emp_var_rate numeric,
   cons_price_idx numeric,     
   cons_conf_idx numeric,     
   euribor3m numeric,
   nr_employed numeric,
   y boolean ) ;


导入数据，新建editor Query 2

COPY bank_details_training from '<<YOUR_BUCKET_NAME>>' REGION '<<YOUR_REGION_NAME>>' IAM_ROLE '<<COPY_YOUR_ROLE_ARN>>' CSV IGNOREHEADER 1 delimiter ',';

点击”Run”

<<YOUR_BUCKET_NAME>>:替换为数据所在存储桶的名称

<<YOUR_REGION_NAME>>：替换为Redshift集群所在的区域（例如us-east-1）

<<COPY_YOUR_ROLE_ARN>>:替换为在上文testredshiftrole详情页面的Role ARN

在editor中输入以下SQL语句，检查数据是否正确导入。

select * from bank_details_training

同样建立建立表并导入测试数据。

CREATE TABLE bank_details_inference(
   age numeric,
   job varchar,
   marital varchar,
   education varchar,
   "default" varchar,
   housing varchar,
   loan varchar,
   contact varchar,
   month varchar,
   day_of_week varchar,
   duration numeric,
   campaign numeric,
   pdays numeric,
   previous numeric,
   poutcome varchar,
   emp_var_rate numeric,
   cons_price_idx numeric,     
   cons_conf_idx numeric,     
   euribor3m numeric,
   nr_employed numeric,
   y boolean ) ;

COPY bank_details_inference from 's3://<<YOUR_BUCKET_NAME>>/dataset/test' REGION '<<YOUR_REGION_NAME>>' IAM_ROLE '<<COPY_YOUR_ROLE_ARN>>' CSV IGNOREHEADER 1 delimiter ',';

 
       
 <<YOUR_BUCKET_NAME>>:替换为数据所在存储桶的名称 <<YOUR_REGION_NAME>>：替换为Redshift集群所在的区域（例如us-east-1） <<COPY_YOUR_ROLE_ARN>>:替换为在上文testredshiftrole详情页面的Role ARN 
       
5.创建模型
 在editor中输入以下代码。 注意在替换<<S3_BUCKET_NAME_WITHOUT_S3_PREFIX >>的名字时，直接输入BUCKET的名称，不需要加s3前缀。 CREATE MODEL 命令以异步方式运行，在底层Redshift会使用到SageMaker AutoML的功能。我们设置最长的运行时间为1小时。 在这个SQL语句中，我们可以看到创建了一个SQL函数func_model_bank_marketing，在后面我们会用到这个函数进行预测。 
       

           
        

            
         
CREATE MODEL model_bank_marketing
FROM (
SELECT    
   age ,
   job ,
   marital ,
   education ,
   "default" ,
   housing ,
   loan ,
   contact ,
   month ,
   day_of_week ,
   duration ,
   campaign ,
   pdays ,
   previous ,
   poutcome ,
   emp_var_rate ,
   cons_price_idx ,     
   cons_conf_idx ,     
   euribor3m ,
   nr_employed ,
   y
FROM
    bank_details_training )
    TARGET y
FUNCTION func_model_bank_marketing
IAM_ROLE '<<COPY_YOUR_ROLE_ARN>>'
SETTINGS (
  S3_BUCKET '<<S3_BUCKET_NAME_WITHOUT_S3_PREFIX >>',
  MAX_RUNTIME 3600
  )
;
 
        
 
       
 
       
<< S3_BUCKET_NAME_WITHOUT_S3_PREFIX >>:替换为数据所在存储桶的名称，注意这里不带S3前缀。 <<COPY_YOUR_ROLE_ARN>>:替换为在上文testredshiftrole详情页面的Role ARN 新建一个editor，运行以下脚本，可以查看运行状态。 show model model_bank_marketing; 我们可以看到，模型正在训练。
 
       
 
       
切换到SageMaker页面中的”Training Jobs”，我们可以看到在redshift创建模型的过程中，启动了多个训练任务以自动寻找最佳的模型。
 
       
 
       
大约1个小时后，训练完成。我们反复运行上面的命令，查询”Model State”，当训练完成后，状态变为”Ready”。运行以下脚本，查看在测试集的准确率。其中num_correct表示在测试集上预测正确的数量，total表示总共测试集的数量，num_correct/total表示预测的准确率。
 
       
 
        
WITH infer_data
 AS (
    SELECT  y as actual, func_model_bank_marketing(age,job,marital,education,"default",housing,loan,contact,month,day_of_week,duration,campaign,pdays,previous,poutcome,emp_var_rate,cons_price_idx,cons_conf_idx,euribor3m,nr_employed) AS predicted,
     CASE WHEN actual = predicted THEN 1::INT
         ELSE 0::INT END AS correct
    FROM bank_details_inference
    ),
 aggr_data AS (
     SELECT SUM(correct) as num_correct, COUNT(*) as total FROM infer_data
 )
 SELECT (num_correct::float/total::float) AS accuracy FROM aggr_data;

 
       
 
       
 
       
运行以下的SQL语句，显示预测结果。
 
       
WITH term_data AS ( SELECT func_model_bank_marketing( age,job,marital,education,"default",housing,loan,contact,month,day_of_week,duration,campaign,pdays,previous,poutcome,emp_var_rate,cons_price_idx,cons_conf_idx,euribor3m,nr_employed) AS predicted
FROM bank_details_inference )
SELECT
CASE WHEN predicted = 'Y'  THEN 'Yes-will-do-a-term-deposit'
     WHEN predicted = 'N'  THEN 'No-term-deposit'
     ELSE 'Neither' END as deposit_prediction,
COUNT(1) AS count
from term_data GROUP BY 1;
 
       
 
       
 
       
结论
 
       
本篇文章介绍了使用Amazon Redshift ML进行模型创建和预测的基本方法。对于数据仓库管理员，不需要掌握机器学习的理论，就可以轻松的使用SQL创建和管理机器学习模型。同时，Amazon Redshift ML的模型也支持部署到Amazon Sagemaker Endpoint中，实现全托管的模型在线服务，有兴趣的读者可以参考Amazon Redshift ML在线文档。
 
       
本篇作者
 
       
 
        
 
         
 
           
         
 
         
张铮
 
         
张铮是亚马逊云科技机器学习产品技术专家，负责基于亚马逊云科技的机器学习方案的咨询与设计，专注于帮助客户打造云端机器学习解决方案。