SQLServer2017 与 R 机器学习服务（二） - 实践

原文：annas-archive.org/md5/bdd1eee2028e59d46863473336d2f170

译者：飞龙

协议：CC BY-NC-SA 4.0

第六章：预测建模

预测建模是一个过程，它使用高级统计和概率算法来预测结果，基于预训练和构建的模型或函数。这些算法可以根据预测变量的结果分为算法家族。结果通常是预测的值，它解释了未来的行为。几个变量或输入数据组成一个数学函数，也称为模型（因此也称为数据建模），这些输入数据试图解释或预测结果。为了更好地理解预测建模，本章将包括以下主题：

  • 数据建模

  • 高级预测算法

  • 预测分析

  • 部署和使用预测解决方案

  • 在 SQL Server 数据库中使用 R 服务进行预测

本章的重点将在于深入探讨如何在 SQL Server 2016/2017 中使用 R 来解决典型的商业问题，以了解预测建模的应用。在企业环境中，一个商业问题可以从一个非常广泛的方面来定义。例如，在医学领域，预测建模可以帮助理解和解决的一个典型问题可能是：药物 C 中成分 A 和 B 的变化是否有助于治愈疾病？此外，在冶金行业，我们能否模拟防腐蚀涂料漆随时间老化的过程——或者在零售业，顾客如何根据他们的需求或行为在商店中选择更好的产品？可以说，我们的日常生活与预测和预测息息相关。通常，我们所有人面对的每一个物流问题都是一个关于可能非常相关主题的简单问题：如果我晚 5 分钟离开家去上班，如果我走一条捷径，这会影响我的驾驶时间吗？等等。实际上，我们可以说，我们的日常决策是我们采取的所有行动的输出总和。

数据建模

数据建模是一个过程，我们试图找到一组独立变量或输入数据的一个函数（或所谓的模型）。就像在数据仓库中，建模是指基于物理数据结构建立概念框架，并在 ORM 或 UML（甚至 CRC）图的帮助下探索数据结构，这与在预测分析中探索结构时看到的是一样的。在后一种情况下，数据建模是探索两个或多个变量之间的结构（或关系）。这些关系可以表示为一个函数，并且本质上存储为模型。

要开始建模，我们将使用以下 GitHub 仓库中可用的 Microsoft 数据：

github.com/Microsoft/sql-server-samples/tree/master/samples/features/machine-learning-services/python/getting-started/rental-prediction

不要在这个 Python 示例中感到困惑：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00087.jpeg

下载此数据库将下载TutorialDB.bak文件，你只需将其恢复到已安装 R in-database 的 SQL Server 实例中。此数据库是本章附带代码的一部分。

建模数据的一部分是设置对后续预测工作方式的了解。因此，在这个阶段，我们将创建对变量及其相互关系的理解。从下载的文件中创建恢复，并运行以下从备份 T-SQL 命令的恢复：

USE [master]
BACKUP LOG [TutorialDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\TutorialDB_LogBackup_2018-01-01_23-59-09.bak'
WITH NOFORMAT, NOINIT, NAME = N'TutorialDB_LogBackup_2018-01-01_23-59-09', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [TutorialDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\TutorialDB.bak'
WITH FILE = 2, MOVE N'TutorialDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TutorialDB.mdf',
MOVE N'TutorialDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TutorialDB_log.ldf', NOUNLOAD, STATS = 5
GO

或者，你可以在 SSMS 中简单地使用RESTORE命令：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00088.gif

现在，你将拥有已恢复的数据库和dbo.rental_data表可供使用。目前，这已经足够了。

数据集准备就绪后，我们现在可以通过探索和理解变量及其相互关系来开始建模数据。这种快速探索可以在 SQL Operation Studio 中进行（下载链接：docs.microsoft.com/en-us/sql/sql-operations-studio/download)，我们将使用一个简单的查询：

SELECT RentalCount,Day,Month, Holiday, Snow FROM rental_data

除了标准的表格视图结果外，这还将提供一个很好的图表查看器，其中变量的简单图形表示将帮助你更好地了解数据：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00089.jpeg

但如果没有对描述性统计学的总体理解，我们就不会继续。因此，使用RevoScaleR包中的rxSummary函数将得到期望的结果：

EXEC sp_execute_external_Script
@LANGUAGE = N'R'
,@script = N'
dr_rent <- InputDataSet
dr_rent <- data.frame(dr_rent)
summary <- rxSummary(~ RentalCount  + Year + Month + Day  + WeekDay + Snow + Holiday , data = dr_rent)
OutputDataSet <- summary$sDataFrame'
,@input_data_1 = N'SELECT  RentalCount, Year, Month, Day, WeekDay, Snow, Holiday FROM rental_data'
WITH RESULT SETS ((
[Name]   NVARCHAR(100)
,Mean   NUMERIC(16,3)
,StdDev  NUMERIC(16,3)
,[Min]   INT
,[Max]  INT
,ValidObs  INT
,MissingObs INT
));
GO

以下是一个简单的描述性统计表格的结果：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00090.gif

探索单变量和多变量统计是前一章第五章，“RevoScaleR 包”的一部分，但在这里我们将更多地关注双变量和多变量统计。在我们开始之前，让我们进一步探索相关性。根据探索变量名称和描述性统计，常识会告诉我们，在假期期间，租赁数量应该更高。可以通过相关系数来检查这一点。以下是一个简单的例子：

EXEC sp_execute_external_Script
@LANGUAGE = N'R'
,@script = N'
dr_rent <- InputDataSet
OutputDataSet <- data.frame(cor(dr_rent$Holiday, dr_rent$RentalCount))
'
,@input_data_1 = N'SELECT  Holiday, RentalCount FROM rental_data'
WITH RESULT SETS ((
cor NUMERIC(10,3)
));
GO

这将给出0.332的双变量关系的概念。这是一个弱相关性，但却是正相关的：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00091.gif

这仅仅意味着，如果RentalCount变量值增加，假期的数量也会增加。这确实是有道理的，因为如果假期越来越多，预期的租赁数量也会增加。

现在，我们可以通过结合每个变量来继续探索和寻找相关性。这类似于进行 CROSS JOIN，但还有更简单的方法来做这件事。其中一种方法当然是，当然，通过使用常识并选择有意义的关联：

EXEC sp_execute_external_Script
@LANGUAGE = N'R'
,@script = N'
dr_rent <- InputDataSet
dr_rent <- data.frame(dr_rent)
cor_HR <- cor(dr_rent$Holiday, dr_rent$RentalCount)
cor_FR <- cor(as.numeric(dr_rent$FWeekDay), dr_rent$RentalCount)
cor_MR <- cor(dr_rent$Month, dr_rent$RentalCount)
cor_YR <- cor(dr_rent$Year,dr_rent$RentalCount)
d <- data.frame(cbind(cor_HR, cor_FR, cor_MR, cor_YR))
OutputDataSet <- d'
,@input_data_1 = N'SELECT  Holiday, RentalCount,Month,FWeekDay, Year FROM rental_data'
WITH RESULT SETS ((
cor_HR NUMERIC(10,3)
,cor_FR NUMERIC(10,3)
,cor_MR NUMERIC(10,3)
,cor_YR NUMERIC(10,3)
));
GO

如下图中所示，我们得到了以下结果。对结果的理解和解释非常重要。因此，假期时间到目前为止是与租赁计数变量最相关的变量。无论是星期几还是年份，都没有起到任何显著的作用。Month和RentalCount之间存在一个非常微小的负相关性-0.110，这可以理解为月份较高的月份可能有较低的租赁计数，反之亦然。由于这种相关性非常弱，因此没有必要对这种特定的相关性大惊小怪（即使它有意义或没有意义）：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00092.gif

同样，可以通过绘制箱线图来探索每个变量中值的分布：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00093.gif

第二种方法是绘制变量之间的相关性图。一种方法是调用corrplot R 库，它为你提供了一个非常强大和有用的可视化。我倾向于创建以下代码：

EXEC sp_execute_external_Script
@LANGUAGE = N'R'
,@script = N'
library(corrplot)  # * footnote
dr_rent <- InputDataSet
dr_rent$FWeekDay <- as.numeric(dr_rent$FWeekDay)
dr_rent$FHoliday <- as.numeric(dr_rent$FHoliday)
dr_rent$FSnow <- as.numeric(dr_rent$FSnow)
cor.mtest <- function(mat, ...) {
mat <- as.matrix(mat)
n <- ncol(mat)
p.mat<- matrix(NA, n, n)
diag(p.mat) <- 0
for (i in 1:(n - 1)) {
for (j in (i + 1):n) {
tmp <- cor.test(mat[, i], mat[, j], ...)
p.mat[i, j] <- p.mat[j, i] <- tmp$p.value
}
}
colnames(p.mat) <- rownames(p.mat) <- colnames(mat)
p.mat
}
p.mat <- cor.mtest(dr_rent)
R<-cor(dr_rent)
col <- colorRampPalette(c("#BB4444", "#EE9988", "#FFFFFF", "#77AADD", "#4477AA"))
image_file = tempfile();
jpeg(filename = image_file);
plot_corr <- corrplot(R, method="color", col=col(200),
type="upper", order="hclust",
addCoef.col = "black", # Add coefficient of correlation
tl.col="black", tl.srt=45, #Text label color and rotation
# Combine with significance
p.mat = p.mat, sig.level = 0.01, insig = "blank",
# hide correlation coefficient on the principal diagonal
diag=FALSE)
dev.off();
OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));  '
,@input_data_1 = N'SELECT  *  FROM rental_data'
WITH RESULT SETS ((
correlation_plot varbinary(max)
));
GO

从 corrplot lattice 文档复制并稍作修改的代码。

这个过程可以直接在 SSRS 或 Power BI 套件或 Excel 中实现和使用；可视化效果如下：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00094.jpeg

在单一图表中，有经验的眼睛会立即看到相关性和它们的统计显著性。因此，这里可以看到0.33 RentalCount和Holiday的相关性，同时RentalCount和Snow也有0.19的正相关性。但如果我们想探索值分散（方差）的行为，我们也可以包括方差分析。

如果你正在处理大型数据集或 XDF 数据格式，RevoScaleR包还配备了计算和计算相关矩阵的函数。以下是一个使用rxCovCor（或者，也可以使用rxCor或rxCov）的 R 代码：

Formula_correlation =  ~ RentalCount + Year + Month + Day  + WeekDay + Snow + Holiday
allCor <- rxCovCor(Formula_correlation, data = dr_rent, type = "Cor")
allCor

这给出了与所有之前计算的相关性相同的结果：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00095.gif

此输出还具有查看标准差、平均值和权重总和的能力，但最好的部分是它将结果存储在数据框中，可以轻松导入或与其他 T-SQL 表一起使用。结果可以使用allCov$CovCor（R 语言将结果存储为列表对象，可以通过使用美元符号$并引用列表名称来检索每个列表——在这种情况下，CovCor）来调用。

当我们想要进一步调查到目前为止最高的RentalCount和Holiday之间的相关性时，方差分析（ANOVA）将是适当的方法。我们将比较变量Holiday的两个组（或水平）（0不是假期，而1是假期）之间的租赁计数是否有差异。通过这样做，计算 F 统计量和其显著性将告诉我们组间方差与组内方差的比率：

EXEC sp_execute_external_Script
@LANGUAGE = N'R'
,@script = N'
#ANOVA
ANOVA <- aov(RentalCount ~ Holiday, data = InputDataSet)
F_Stat<- unlist(summary(ANOVA))[7]
F_Stat_Sig <- unlist(summary(ANOVA))[9]
df <- cbind(F_Stat, F_Stat_Sig)
OutputDataSet <- data.frame(df)'
,@input_data_1 = N'SELECT  RentalCount,Holiday FROM rental_data'
WITH RESULT SETS ((
F_Statistic NVARCHAR(200)
,Statistical_Significance NUMERIC(16,5)
));
GO

在使用 R 代码运行 T-SQL 代码进行 ANOVA 统计计算后，输出结果以这种方式创建，返回 F 统计量和统计显著性。以下图显示了返回的结果：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00096.gif

结果告诉我们，F 统计量在统计上具有显著性——尽管它很小——这意味着均值很可能不相等（在这种情况下，我们会拒绝零假设）。要找到差异所在，TukeyHDS测试会给我们更多信息。

只为了说明差异，因为我们不会深入细节，我们可以使用租赁的假日分布差异的stripchart可视化：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00097.gif

使用 R 代码：

stripchart(RentalCount ~ Holiday, vertical=TRUE, pch=9,
data=dr_rent_ANOVA, xlab="Holiday day (Yes/No)", ylab="Rental count", method="jitter", jitter=0.09)

案例的分布可以告诉我们，在假日，平均租赁次数为400或更高，而在正常日子里，10到50之间有大量的计数密度。

在确定哪些特征（变量）适合进一步分析和预测算法时，我们可以使用减少基尼均值的计算。randomForest包中有一个 Gini 均值函数可用，因此，让我们调用该函数并查看要使用的变量：

EXEC sp_execute_external_Script
@LANGUAGE = N'R'
,@script = N'
library(randomForest)
dr_rent  <- InputDataSet
fit_RF <- randomForest(factor(dr_rent$RentalCount)~., data=dr_rent)
vp_rf <- importance(fit_RF)
vp_rf<- data.frame(vp_rf)
imena <- row.names(vp_rf)
vp_rf <- data.frame(cbind(imena, vp_rf))
OutputDataSet <- vp_rf'
,@input_data_1 = N'SELECT  *  FROM rental_data'
WITH RESULT SETS ((
Variable NVARCHAR(200)
,MeanDecreaseGini NUMERIC(16,5)
));
GO

使用 T-SQL 代码，我们返回减少的基尼系数：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00098.gif

基尼系数也可以用散点图的形式直观表示，这样用户可以立即确定哪些变量对模型贡献最大。为了简洁起见，此图的代码包含在代码中，但不在书中。

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00099.gif

现在可以确定以下哪些变量在模型中扮演任何角色或有所贡献。MeanDecreaseGini被绘制为varImpPlot(fit_RF)。从技术上讲，这是确定哪些变量或输入参数影响最小或最大的方法，但每种技术都会给出一些方面——模型中可能好的，以及可能不好的。比较相关矩阵和平均减少图中的Holiday变量，你可以看到它给出了不同的方法和不同的结果。最显著的是那些通过几种不同的方法，特定变量根本不起任何重要作用的情况。

高级预测算法和分析

到目前为止，我们已经检查了RevoScaleR包中可用的数据准备和数据分析函数。除了这些函数之外，预测分类或回归问题也可以完成，尤其是在处理大型数据集时。

我将只提及其中的一些。完整的列表可在网上找到（docs.microsoft.com/en-us/machine-learning-server/r-reference/revoscaler/revoscaler），以下是一些要点：

  • rxLinMod：这是用于构建和预测线性模型

  • rxLogit：这个函数用于构建和预测逻辑回归模型

  • rxGlm：这个函数用于创建广义线性模型

  • rxDTree：这个函数用于创建分类或回归树

  • rxBTrees：这个函数用于构建分类或回归决策森林——即使用随机梯度提升算法

  • rxDForest：这个函数用于构建分类或回归决策森林模型

  • rxNaiveBayes：这个函数用于构建朴素贝叶斯分类模型

所有这些算法都是监督算法家族的一部分，其中RevoScaleR包中唯一的无监督（或非指导）算法是rxKMeans，它用于处理聚类。

使用我们之前使用的相同数据集，我们插入并开始使用rxLinMod和rxGlm来演示如何在 T-SQL 中使用它们：

USE RentalDB;
GO
-- rxLinMod
EXEC sp_execute_external_Script
@LANGUAGE = N'R'
,@script = N'
library(RevoScaleR)
dr_rent <- InputDataSet
Formula_supervised =  RentalCount ~ Year + Month + Day  + WeekDay + Snow + Holiday
#Create Linear Model 
rent_lm <- rxLinMod(formula=Formula_supervised, data = dr_rent)
#PREDICT   
rent_Pred <- rxPredict(modelObject = rent_lm, data = dr_rent, extraVarsToWrite = c("RentalCount","Year","Month","Day"), writeModelVars = TRUE)
OutputDataSet <- data.frame(rent_Pred)
'
,@input_data_1 = N'SELECT RentalCount,Year, Month, Day, WeekDay,Snow,Holiday  FROM rental_data'
WITH RESULT SETS ((
RentalCount_Pred    NUMERIC(16,3)
,RentalCount  NUMERIC(16,3)
,YearINT
,MonthINT
,DayINT
,WeekDayINT
,Snow  INT
,Holiday INT
));
GO
-- rxGlm
EXEC sp_execute_external_Script
@LANGUAGE = N'R'
,@script = N'
library(RevoScaleR)
dr_rent <- InputDataSet
Formula_supervised =  RentalCount ~ Year + Month + Day  + WeekDay + Snow + Holiday
#PREDICT   
rent_glm <- rxGlm(formula = Formula_supervised, family = Gamma, dropFirst = TRUE, data = dr_rent)
rent_Pred <- rxPredict(modelObject = rent_glm, data = dr_rent, extraVarsToWrite=c("RentalCount","Year","Month","Day"), writeModelVars = TRUE)
OutputDataSet <- data.frame(rent_Pred)'
,@input_data_1 = N'SELECT RentalCount,Year, Month, Day, WeekDay,Snow,Holiday  FROM rental_data'
WITH RESULT SETS ((
RentalCount_Pred    NUMERIC(16,3)
,RentalCount  NUMERIC(16,3)
,YearINT
,MonthINT
,DayINT
,WeekDayINT
,Snow  INT
,Holiday INT
));
GO
-- rxDTree
EXEC sp_execute_external_Script
@LANGUAGE = N'R'
,@script = N'
library(RevoScaleR)
dr_rent <- InputDataSet
Formula_supervised =  RentalCount ~ Year + Month + Day  + WeekDay + Snow + Holiday
#PREDICT   
rent_dt <- rxDTree(formula = Formula_supervised, data = dr_rent)
rent_Pred <- rxPredict(modelObject = rent_dt, data = dr_rent, extraVarsToWrite=c("RentalCount","Year","Month","Day"), writeModelVars = TRUE)
OutputDataSet <- data.frame(rent_Pred)
'
,@input_data_1 = N'SELECT RentalCount,Year, Month, Day, WeekDay,Snow,Holiday  FROM rental_data'
WITH RESULT SETS ((
RentalCount_Pred    NUMERIC(16,3)
,RentalCount  NUMERIC(16,3)
,YearINT
,MonthINT
,DayINT
,WeekDayINT
,Snow  INT
,Holiday INT
));
GO

这两个都会根据输入的数据集提供预测值，以及新的预测值：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00100.gif

一个好奇的眼睛会告诉你，预测值与原始值相差甚远。因此，两种情况下的预测公式都在尝试根据变量：Year（年）、Month（月）、Day（日）、WeekDay（星期几）、Snow（雪）和Holiday（假日）来预测变量RentalCount。公式设置如下：

Formula_supervised =  RentalCount ~ Year + Month + Day  + WeekDay + Snow + Holiday

比较变量RentalCount_Pred和RentalCount将显示真实值和预测值之间的差异/偏移量。

在前面的示例中，您还可以比较所有三个算法的结果。如果您对所有三个数据集进行逐个观察的比较，您可以立即看到哪些算法表现最好：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00101.jpeg

因此，黄色条形表示原始值，迄今为止，决策树算法在给定上述公式并理解数据洞察的情况下，是最准确的。图表仅代表随机选取的观察结果。这也可以通过计算准确度或衡量标准来实现，该标准计算了与原始值之间的偏差程度。

部署和使用预测解决方案

在开发数据库内解决方案并为其持续开发（以及部署）创建时，应考虑几个方面。首先，数据科学家将工作的环境。你可能给他们一个强大的独立服务器，甚至在云中分配适当的座位。他们需要它，尤其是在训练模型时。这非常重要，因为你不希望你的高薪统计学家和数学家等待模型计算和生成。因此，启用通往高度可扩展的 CPU 和 RAM 强大计算的路是由必要的。其次，你必须将数据放在那里。无论是在云中还是在本地，将数据放在那里（以及稍后返回）不应被忽视，因为这可能是你失去宝贵时间的地方。最后，拥有设置正确的环境、环境变量、包以及所有专有软件路径的启用也是非常重要的。

RevoScaleR包自带了一个用于轻松切换计算环境的函数。我们现在将在 R 中调用一个简单的命令：

rxSetComputeContext(local)
rxSetComputeContext(sql)

通过这样做，你可以设置本地计算环境（即客户端的机器）或服务器端，在这种情况下，一个独立的 R 服务器将驻留。通过简单的函数调用，计算上下文（或简单环境）就会切换，当然，要考虑到所有数据都存储在两边（这样你可以避免不必要的传输），并且所有服务器环境变量都设置正确。

对于训练模型，可以选择一些良好的实践。将数据分割用于训练、测试或训练、测试和验证是几种实践。此外，一个非常好的实践是测试训练/测试/验证数据集的百分比。你可能得到 50/50 或 60/40 或 70/30 的百分比，但通常你会在挖掘数据时进行这项操作并做出决定。之后，你还应该考虑数据的验证；有几种方法可供选择，从留一法（LOO）或 10 折法或 5 折法来选择用于验证结果的数据。

不深入探讨这个主题，为了使这个演示更简单，我们可以现场决定采用 70/30 的百分比。由于我们有幸使用 T-SQL 数据库，我们可以选择并将训练子集存储在表中，或者创建一个视图，或者决定我们想要取的 70%。

-- We can set 70% of the original data
-- IN SQL Server
SELECT
TOP (70)PERCENT
*
INTO dbo.Train_rental_data
FROM rental_data
ORDER BY ABS (CAST(BINARY_CHECKSUM(RentalCount,NEWID())asint))ASC
-- (318 rows affected)
-- Or we can set by the year; year 2013 and 2014 for training and 2015 for testing? making it cca 70% for training as well
SELECT COUNT(*),YEAR FROM rental_data GROUP BY YEAR

这也严重依赖于你的商业模式。第一种方法简单地从原始数据集中取出 70%的数据，而第二种选择语句大致取出原始数据的 70%，但基于租赁年份进行分割。这可能会对模型的行为产生关键影响，也会影响你希望决策如何受到这种影响，尤其是商业模式。

清晰并覆盖这些内容后，一个最佳实践是将训练好的模型存储在表中以实现更快的预测。现在我们将创建一个如下所示的表：

-- or in R
EXEC sp_execute_external_Script
@language = N'R'
,@script = N'
library(caTools)
set.seed(2910)
dr_rent <- InputDataSet
Split <- .70
sample = sample.split(dr_rent$RentalCount, SplitRatio = Split)
train_dr_rent <- subset(dr_rent, sample == TRUE)
test_dr_rent  <- subset(dr_rent, sample == FALSE)
OutputDataSet <- data.frame(train_dr_rent)
'
,@input_data_1 = N'SELECT * FROM rental_data'
WITH RESULT SETS ((
[Year] INT
,[Month] INT
,[Day] INT
,[RentalCount] INT
,[WeekDay] INT
,[Holiday] INT
,[Snow] INT
,[FHoliday] INT
,[FSnow] INT
,[FWeekDay] INT
));
GO

由于set.seed已被定义，无论你在哪里运行此代码，你都将始终得到相同的子集。如果你想得到不同的结果，你应该将其注释掉。

采样再次完成后，根据你预测的问题，你需要定义你的预测公式。在这种情况下，我正在使用公式转换器来创建一个合适的公式：

-- Variables to keep
-- and creating formula
EXEC sp_execute_external_Script
@language = N'R'
,@script = N'
dr_rent <- InputDataSet
variables_all <- rxGetVarNames(dr_rent)
variables_to_remove <- c("FSnow", "FWeekDay", "FHoliday")
traning_variables <- variables_all[!(variables_all %in% c("RentalCount", variables_to_remove))]
#use as.formula to create an object
formula <- as.formula(paste("RentalCount ~", paste(traning_variables, collapse = "+")))
#formula <- paste("RentalCount ~", paste(traning_variables, collapse = "+"))
OutputDataSet <- data.frame(formula)'
,@input_data_1 = N'SELECT * FROM dbo.Train_rental_data'
WITH RESULT SETS ((
[Formula_supervised] NVARCHAR(1000)
));
GO

通过一个过程创建公式，使其不是硬编码的，也是一种非常实用的方法，尤其是在企业环境中，数据科学家会设置独立变量的池，然后在数据工程师选择要包含哪些变量之前，将数据推送到计算模型并部署它。

双变量和多变量统计过程也可以给数据工程师和管理员提供更好的洞察力和理解，了解数据和如何操作以及相关性，以及没有不想要的关联或不起作用的变量。

清晰地了解这一点后，我们可以设置和构建运行模型训练并存储在数据库中的过程。由于本章篇幅有限，我将只展示创建一个过程的示例；其余的过程可以在配套的章节材料中找到：

T-SQL 中随机森林的流程看起来是这样的：

-- Random forest
DROP PROCEDURE IF EXISTS dbo.forest_model;
GO
CREATE OR ALTER PROCEDURE dbo.forest_model(
@trained_model VARBINARY(MAX)OUTPUT
,@accuracy FLOATOUTPUT
)
AS
BEGIN
EXEC sp_execute_external_script
@language = N'R'
,@script = N'
library(RevoScaleR)
library(caTools)
library(MLmetrics)
dr_rent <- InputDataSet
set.seed(2910)
Split <- .70
sample = sample.split(dr_rent$RentalCount, SplitRatio = Split)
train_dr_rent <- subset(dr_rent, sample == TRUE)
test_dr_rent  <- subset(dr_rent, sample == FALSE)
y_train <- train_dr_rent$RentalCount
y_test <- test_dr_rent$RentalCount
variables_all <- rxGetVarNames(dr_rent)
variables_to_remove <- c("FSnow", "FWeekDay", "FHoliday")
traning_variables <- variables_all[!(variables_all %in% c("RentalCount", variables_to_remove))]
formula <- as.formula(paste("RentalCount ~", paste(traning_variables, collapse = "+")))
forest_model <- rxDForest(formula = formula,
data = train_dr_rent,
nTree = 40,
minSplit = 10,
minBucket = 5,
cp = 0.00005,
seed = 5)
trained_model <- as.raw(serialize(forest_model, connection=NULL))
#calculating accuracy
y_predicted<- rxPredict(forest_model,test_dr_rent)
predict_forest <-data.frame(actual=y_test,pred=y_predicted)
#ConfMat <- confusionMatrix(table(predict_forest$actual,predict_forest$RentalCount_Pred))
#accuracy <- ConfMat$overall[1]
accu <- LogLoss(y_pred = predict_forest$RentalCount_Pred , y_true =predict_forest$actual)
accuracy <- accu'
,@input_data_1 = N'SELECT * FROM dbo.rental_data'
,@params = N'@trained_model VARBINARY(MAX) OUTPUT, @accuracy FLOAT OUTPUT'
,@trained_model = @trained_model OUTPUT
,@accuracy = @accuracy OUTPUT;
END;
GO

我在过程中添加了一些额外的东西，这样每次训练模型时都会添加一些额外的信息。这是准确性，它也会让数据工程师和后期阶段的管理员对决定哪个模型优于其他模型有很好的洞察力。

你可以简单地按照以下方式运行该过程：

DECLARE @model VARBINARY(MAX);
DECLARE @accur FLOAT;
EXEC dbo.forest_model@model OUTPUT, @accur OUTPUT;
INSERT INTO [dbo].Rental_data_models VALUES ('Random_forest_V1', @model, @accur);
GO

这将填充存储模型的目标表。结果应存储在表 [dbo].[Rental_data_models] 中：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00102.gif

完成这些后，你需要设置评估过程，这将有助于确定哪个模型表现最好。然而，这部分可以使用 Power BI、报告服务或简单地使用 R 来完成。

这是 R 代码的一部分，可以包含在你的可视化工具中以便更容易理解：

library(RevoScaleR)
library(caTools)
library(MLmetrics)
#evaluate_model function; Source: Microsoft 
evaluate_model <- function(observed, predicted_probability, threshold, model_name) {
# Given the observed labels and the predicted probability, plot the ROC curve and determine the AUC. 
data <- data.frame(observed, predicted_probability)
data$observed <- as.numeric(as.character(data$observed))
if(model_name =="RF"){
rxRocCurve(actualVarName = "observed", predVarNames = "predicted_probability", data = data, numBreaks = 1000, title = "RF" )
}else{
rxRocCurve(actualVarName = "observed", predVarNames = "predicted_probability", data = data, numBreaks = 1000, title = "GBT" )
}
ROC <- rxRoc(actualVarName = "observed", predVarNames = "predicted_probability", data = data, numBreaks = 1000)
auc <- rxAuc(ROC)
# Given the predicted probability and the threshold, determine the binary prediction. 
predicted <- ifelse(predicted_probability > threshold, 1, 0)
  predicted <- factor(predicted, levels = c(0, 1))
  # Build the corresponding Confusion Matrix, then compute the Accuracy, Precision, Recall, and F-Score. 
  confusion <- table(observed, predicted)
  print(model_name)
  print(confusion)
  tp <- confusion[1, 1]
  fn <- confusion[1, 2]
  fp <- confusion[2, 1]
  tn <- confusion[2, 2]
  accuracy <- (tp + tn) / (tp + fn + fp + tn)
  precision <- tp / (tp + fp)
  recall <- tp / (tp + fn)
  fscore <- 2 * (precision * recall) / (precision + recall)
  # Return the computed metrics. 
  metrics <- list("Accuracy" = accuracy,
  "Precision" = precision,
  "Recall" = recall,
  "F-Score" = fscore,
  "AUC" = auc)
  return(metrics)
  }
  RF_Scoring <- rxPredict(forest_model, data = train_dr_rent, overwrite = T, type = "response",extraVarsToWrite = c("RentalCount"))
  Prediction_RF <- rxImport(inData = RF_Scoring, stringsAsFactors = T, outFile = NULL)
  observed <- Prediction_RF$RentalCount
  # Compute the performance metrics of the model. 
  Metrics_RF <- evaluate_model(observed = observed, predicted_probability = Prediction_RF$RentalCount_Pred , model_name = "RF", threshold=50)
  # Make Predictions, then import them into R. The observed Conversion_Flag is kept through the argument extraVarsToWrite. 
  GBT_Scoring <- rxPredict(btree_model,data = train_dr_rent, overwrite = T, type="prob",extraVarsToWrite = c("RentalCount"))
  Prediction_GBT <- rxImport(inData = GBT_Scoring, stringsAsFactors = T, outFile = NULL)
  observed <- Prediction_GBT$RentalCount

观察到的值应该告诉你哪个模型表现最好。一旦你完成了这个，你就可以选择模型并查看预测是如何进行的。

在 SQL Server 数据库中使用 R 服务进行预测

调用存储过程是组织代码并立即开始预测的最简单方法。

再次，这里只展示如何创建一个存储过程来预测新的数据集的示例：

CREATE OR ALTER PROCEDURE [dbo].[Predicting_rentalCount]
(
@model VARCHAR(30)
,@query NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @nar_model VARBINARY(MAX) = (SELECT model FROM [dbo].[Rental_data_models] WHERE model_name = @model);
EXEC sp_execute_external_script
@language = N'R'
,@script = N'
#input from query
new_data <- InputDataSet
#model from query
model <- unserialize(nar_model)
#prediction
prediction <- rxPredict(model,data = new_data, overwrite = TRUE, type="response",extraVarsToWrite = c("RentalCount"))
Prediction_New <- rxImport(inData = prediction, stringsAsFactors = T, outFile = NULL)
OutputDataSet <- data.frame(Prediction_New)
'
,@input_data_1 =  @query
,@params = N'@nar_model VARBINARY(MAX)'
,@nar_model = @nar_model
WITH RESULT SETS((
Prediction_new NVARCHAR(1000)
, OrigPredictecCount NVARCHAR(1000)
))
END;

完成这些后，你可以开始使用以下代码进行预测：

-- Example of running predictions against selected model
EXEC [dbo].[Predicting_rentalCount]
@model = N'Random_forest_V1'
,@query = N'SELECT
2014 AS Year
,5 AS Month
,12 AS Day
,1 AS WeekDay
,0 AS Holiday
,0 AS Snow
,0 AS RentalCount'

结果，你将得到Year、Month、Day、WeekDay、Holiday和Snow变量的预测值：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00103.gif

故意将字段OrigPredictedCount设置为0，但新的预测值是278.996，这是基于输入变量的。在检查模型学习情况时，最好也检查原始值：

SELECT
*
FROM Rental_data
WHERE [year] = 2014
AND [day] = 12

我们看到在month= 5`中没有值，因此模型必须从其他值中学习：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00104.gif

现在我们已经涵盖了监督预测算法，让我们快速跳入聚类——RevoScaleR包支持的唯一无向算法的一部分。

以下是如何创建简单聚类的示例：

library("cluster")
# and remove the Fholidays and Fsnow variables 
DF <- DF[c(1,2,3,4,5,6,7)]
XDF <- paste(tempfile(), "xdf", sep=".")
if (file.exists(XDF)) file.remove(XDF)
rxDataStep(inData = DF, outFile = XDF)
# grab 3 random rows for starting  
centers <- DF[sample.int(NROW(DF), 3, replace = TRUE),]
Formula =  ~ Year + Month + Day + RentalCount + WeekDay + Holiday + Snow
# Example using an XDF file as a data source 
z <- rxKmeans(formula=Formula, data = DF, centers = centers)
clusplot(DF, z$cluster, color=TRUE, shade=TRUE, labels=4, lines=0, plotchar = TRUE)

以下输出是聚类的展示：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00105.gif

要探索聚类并尝试不同的聚类数量，直接使用 R 代码或创建一个报告来使用 Power BI、Excel 或 SSRS 探索聚类特征，这是肯定的事情。

添加有关聚类中心、统计信息如withinSS、betweenSS、totSS等额外信息也将帮助我们理解聚类。

Scree 图也是选择正确聚类数量的额外且非常棒的表现方式。将此类图形添加到报告中也有助于用户选择正确的聚类数量，并帮助他们理解聚类是如何形成的。

Scree 图 R 代码用于确定肘部在哪里发生，以及它是否有正确的聚类数量；如果是的话，三个聚类将是一个最佳数量：

wss <- (nrow(DF) - 1) * sum(apply(DF, 2, var))
for (i in 2:20)
wss[i] <- sum(kmeans(DF, centers = i)$withinss)
plot(1:20, wss, type = "b", xlab = "Number of Clusters", ylab = "Within groups sum of squares")

在这个图表中，我们可以看到肘部在哪里形成，我们可以确定最佳解决方案是三个聚类：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00106.gif

将所有内容整合到报告中（SSRS 报告）使探索更加完善：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00107.jpeg

用户可以通过选择所需的聚类数量来更改聚类数量，报告将相应地更改。该报告基于三个额外的过程，这些过程基于输入的聚类数量导出图形：

CREATE OR ALTER  PROCEDURE [dbo].[Clustering_rentalCount]
(
@nof_clusters VARCHAR(2)
)
AS
BEGIN
DECLARE @SQLStat NVARCHAR(4000)
SET @SQLStat = 'SELECT  * FROM rental_data'
DECLARE @RStat NVARCHAR(4000)
SET @RStat = 'library(ggplot2)
library(RevoScaleR)
library(cluster)
image_file <- tempfile()
jpeg(filename = image_file, width = 400, height = 400)
DF <- data.frame(dr_rent)
DF <- DF[c(1,2,3,4,5,6,7)]
XDF <- paste(tempfile(), "xdf", sep=".")
if (file.exists(XDF)) file.remove(XDF)
rxDataStep(inData = DF, outFile = XDF)
centers <- DF[sample.int(NROW(DF), 3, replace = TRUE),]
Formula =  ~ Year + Month + Day + RentalCount + WeekDay + Holiday + Snow
rxKmeans(formula = Formula, data = XDF, numClusters='+@nof_clusters+')
z <- rxKmeans(formula=Formula, data = DF, numClusters='+@nof_clusters+')
clusplot(DF, z$cluster, color=TRUE, shade=TRUE, labels=4, lines=0, plotchar = TRUE)
dev.off()
OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6))'
EXECUTE sp_execute_external_script
@language = N'R'
,@script = @RStat
,@input_data_1 = @SQLStat
,@input_data_1_name = N'dr_rent'
WITH RESULT SETS ((plot varbinary(max)))
END;
GO

在 SSMS 中运行此操作将给出一个 var binary 字符串，但将此过程的结果作为图像添加到 SSRS 或 Power BI/Excel 中，将得到一个由 R 生成的图表。

在构建预测分析系统后，为探索性项目添加一个漂亮的可视化效果，对于商业用户、数据整理员和工程师来说，无疑是一个非常好的总结。

摘要

在本章中，我们介绍了RevoScaleR包的可扩展功能，以便基于探索的数据集提供快速和良好的预测。在上一章《使用 RevoScaleR 包进行统计学习》中，我们介绍了数据探索、准备以及简单和双变量统计。本章展示了RevoScaleR包是如何设计用来处理大型数据集的（克服了 RAM 和单 CPU 的限制），实现了数据溢出到磁盘和多线程。同样的程序也可以在 R 的数据库实例中使用，以便将预测结果发送到你的业务和数据库中的数据。我们也涵盖了这一方面，探索了不同的算法并比较了解决方案。一旦你选定了模型，你可能想使用PREDICT子句。这是 SQL Server 2017 的一个新特性，具有略微不同的架构。请注意，目前（在撰写本章时）如果你想使用PREDICT子句，模型大小不能超过 100 MB。目前，只有RevoScaleR和MicrosoftML包支持使用这个子句，而且并不是所有的RevoScaleR（和 MicrosoftML）算法都支持——目前支持的是rxLinMod、rxLogit、rxBTrees、rxDtree、rxdForest。然而，使用PREDICT子句进行的实时评分将在 SQL Server 的下一个版本中肯定会有发展和演变。

我们需要预测一个分类或回归问题。大多数问题都可以使用RevoScaleR包来支持，其中许多算法也得到了MicrosoftML包中提供的新附加分类器的支持。探索这两个包将给你的决策提供急需的推动。此外，将序列化的模型存储到数据库中是存储和调用经过训练的模型（函数）的最佳方式，这些模型可以通过添加简单的逻辑实现使用 SQL Server 代理或触发器进行重新训练。

在第七章《将 R 代码投入运行》中，你将学习如何将你的模型和解决方案投入运行，并探索不同的实现方法和一些良好的实践。

第七章：实施 R 代码

如您在上一章中学习了预测建模的基础知识并探索了 RevoScaleR 包中可用的高级预测算法，现在是学习如何实施它的好时机。本章讨论了您如何在 SQL Server 2016 和 SQL Server 2017 中实施 R 预测模型。

将 SQL Server 和机器学习结合在一起的想法是将分析保持接近数据并消除成本以及安全风险。此外，使用 Microsoft R 库有助于提高您 R 解决方案的可扩展性和性能。

本章概述了将您的 R 预测模型实施为一个强大工作流程的步骤，该工作流程集成在 SQL Server 中。首先，我们将讨论使用扩展性框架、本地评分（SQL Server 2017）和实时评分将现有 R 模型集成到 SQL Server 中的概念。然后，我们将讨论如何在 SQL Server 中运行 R 模型如何管理角色和权限。您还将学习如何使用正确的工具在 SQL Server 中实施 R 模型，以及如何将 R 模型作为工作流程的一部分执行，包括 PowerShell、SQL Server Agent 作业和 SSIS。

集成现有 R 模型

本节将现有的 R 代码（生成 R 模型并针对 SQL Server 数据集运行）纳入一个工作流程，其中模型可以定期刷新和评估，然后用于预测分析。以下图显示了 R 脚本中的典型预测建模工作流程：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00108.jpeg

图 7.1：典型的预测建模工作流程

要在 SQL Server 中集成此脚本，您需要将工作流程分为三个步骤：

  1. 准备训练数据

  2. 使用 T-SQL 训练和保存模型

  3. 实施模型

在本节中，最后两个步骤将使用 sp_execute_external_script，该命令调用一个 R 进程。这些步骤使用 SQL Server 扩展性框架，将在后面进行描述。

前提条件 - 准备数据

我们将使用来自 R: In-Database Analytics for SQL Developers 教程的纽约出租车样本数据，如 github.com/Microsoft/sql-server-samples/blob/master/samples/features/r-services/predictive-analytics/scripts/Lab.md 中所述。

您还可以从 Packt 代码文件存储库下载 nyctaxi_sample.csv 文件，并执行以下 bcp 命令：

bcp <database name>.dbo.nyctaxi_sample in <file path> -c -t, -T -S<server name>

其中：

  • <database name> 是数据库名称

  • <file path> 是 nyctaxi_sample.csv 文件的位置

  • <server name> 是您的服务器名称。

例如：

bcp NYCTaxi.dbo.nyctaxi_sample in c:\nyctaxi_sample.csv -c -t, -T -SMsSQLGirl

在这个场景中，目标是预测小费的可能性。作为这个过程的一部分，我们将创建一个逻辑回归模型，以及模型的接收者操作特征（ROC）曲线和其曲线下面积（AUC）。ROC 是针对诊断测试的各种阈值点的真阳性率与假阳性率的图表。曲线越接近 ROC 空间的对角线，测试的准确性就越低。

曲线越接近左上角边界，就越准确。AUC 以数值形式提供了准确性的测试。幸运的是，ROC 图和 AUC 值都可以很容易地在 R 中计算。

一旦我们确信模型足够准确，我们就可以分享它，并基于提供的输入重新使用它来预测出租车司机是否会收到小费。

这里是我们将用于训练的纽约出租车数据集的表定义：

CREATE TABLE [dbo].nyctaxi_sample NOT NULL,
[hack_license] varchar NOT NULL,
[vendor_id] char NULL,
[rate_code] char NULL,
[store_and_fwd_flag] char NULL,
[pickup_datetime] [datetime] NOT NULL,
[dropoff_datetime] [datetime] NULL,
[passenger_count] [int] NULL,
[trip_time_in_secs] [bigint] NULL,
[trip_distance] [float] NULL,
[pickup_longitude] varchar NULL,
[pickup_latitude] varchar NULL,
[dropoff_longitude] varchar NULL,
[dropoff_latitude] varchar NULL,
[payment_type] char NULL,
[fare_amount] [float] NULL,
[surcharge] [float] NULL,
[mta_tax] [float] NULL,
[tolls_amount] [float] NULL,
[total_amount] [float] NULL,
[tip_amount] [float] NULL,
[tipped] [int] NULL,
[tip_class] [int] NULL
) ON [PRIMARY]
GO

有一些变量我们可以开始使用来分析出租车司机收到小费的可能性。正如你在上一章中学到的，你可能需要尝试几个变量和算法来确定哪个更准确。这可能涉及几个迭代过程，这就是数据科学的魅力——你不断地进行实验。

首先，让我们使用以下变量：

变量类型列名
出租车司机是否收到小费（是/否）输出tipped
乘客数量输入passenger_count
行程时间（秒）输入trip_time_in_seconds
根据出租车计价器显示的行程距离输入trip_distance
基于两个位置之间的经度和纬度的直接距离输入pickup_longitudepickup_latitudedropoff_longitudedropoff_latitude

为了更容易计算直接距离，让我们定义以下函数：

CREATE FUNCTION [dbo].[fnCalculateDistance]
(@Lat1 FLOAT, @Long1 FLOAT, @Lat2 FLOAT, @Long2 FLOAT)
-- User-defined function calculate the direct distance
-- between two geographical coordinates.
RETURNS FLOAT
AS
BEGIN
DECLARE @distance DECIMAL(28, 10)
-- Convert to radians
SET @Lat1 = @Lat1 / 57.2958
SET @Long1 = @Long1 / 57.2958
SET @Lat2 = @Lat2 / 57.2958
SET @Long2 = @Long2 / 57.2958
-- Calculate distance
SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
--Convert to miles
IF @distance <> 0
BEGIN
SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
END
RETURN @distance
END

这里是我们想要存储在数据库中的训练预测模型（的）的表定义。将训练好的预测模型（的）存储在表中的一个优点是，我们可以轻松地稍后重用它，并且可以版本控制我们的实验。

请注意，有一个名为IsRealTimeScoring的列。SQL Server 2017 为实时评分添加了一个新功能，这将在集成 R 模型进行实时评分部分中讨论。如果你使用的是 SQL Server 2016，请忽略此值：

CREATE TABLE [dbo].NYCTaxiModel NOT NULL,
[AUC] FLOAT NULL,
[CreatedOn] DATETIME NOT NULL
CONSTRAINT DF_NYCTaxiModel_CreatedOn DEFAULT (GETDATE()),
[IsRealTimeScoring] BIT NOT NULL
CONSTRAINT DF_NYCTaxiModel_IsRealTimeScoring DEFAULT (0)
) ON [PRIMARY]

第 1 步 - 使用 T-SQL 训练和保存模型

在这一步，您可以通过存储过程将一个预测模型（以及可选的分数）创建到一个表中。这样做的原因是，我们不想每次智能应用需要做预测时都创建一个新的模型，而是希望保存模型以便重复使用：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00109.jpeg

图 7.2：创建预测模型并将其存储在 SQL Server 中

在图 7.2中，我们假设数据清洗部分已经完成，输入数据集已经准备好供 R 计算使用，以便训练和评分模型。

这里是一个示例存储过程，它基于纽约出租车样本数据集生成预测模型，并将其保存到表中。该模型预测小费的可能性。模型和模型的 AUC 都保存在dbo.nyc_taxi_models_v2表中：

CREATE PROCEDURE [dbo].[uspTrainTipPredictionModel]
AS
BEGIN
DECLARE @auc FLOAT;
DECLARE @model VARBINARY(MAX);
-- The data to be used for training
DECLARE @inquery NVARCHAR(MAX) = N'
SELECT
tipped,
fare_amount,
passenger_count,
trip_time_in_secs,
trip_distance,
pickup_datetime,
dropoff_datetime,
dbo.fnCalculateDistance(pickup_latitude,
pickup_longitude,
dropoff_latitude,
dropoff_longitude) as direct_distance
FROM dbo.nyctaxi_sample
TABLESAMPLE (10 PERCENT) REPEATABLE (98052)'
-- Calculate the model based on the trained data and the AUC.
EXEC sp_execute_external_script @language = N'R',
@script = N'
## Create model 
logitObj <- rxLogit(tipped ~ passenger_count +
trip_distance +
trip_time_in_secs +
direct_distance,
data = InputDataSet);
summary(logitObj)
## Serialize model             
model <- serialize(logitObj, NULL);
predOutput <- rxPredict(modelObject = logitObj,
data = InputDataSet, outData = NULL,
predVarNames = "Score", type = "response",
writeModelVars = FALSE, overwrite = TRUE);
library(''ROCR'');
predOutput <- cbind(InputDataSet, predOutput);
auc <- rxAuc(rxRoc("tipped", "Score", predOutput));
print(paste0("AUC of Logistic Regression Model:", auc));
',
@input_data_1 = @inquery,
@output_data_1_name = N'trained_model',
@params = N'@auc FLOAT OUTPUT, @model VARBINARY(MAX) OUTPUT',
@auc = @auc OUTPUT,
@model = @model OUTPUT;
-- Store the train model output and its AUC
INSERT INTO [dbo].[NYCTaxiModel] (Model, AUC)
SELECT @model, @auc;
END
GO

一旦定义了这个存储过程，你就可以执行它来生成模型和 AUC。例如：

EXEC [dbo].[uspTrainTipPredictionModel]

然后，通过执行以下语句查看NYCTaxiModel表的内容：

SELECT [Model], [AUC], [CreatedOn], [IsRealTimeScoring]
FROM [dbo].[NYCTaxiModel]

如果存储过程执行正确，你应该会看到以下类似的记录：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00110.jpeg

第 2 步 - 操作化模型

一旦模型创建并作为前一步骤的一部分存储在表中，我们现在就可以创建一个存储过程，智能应用程序可以调用它来进行小费预测：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00111.jpeg

图 7.3：在 SQL Server 中获取预测

图 7.3 展示了将预测模型操作化的存储过程的流程。

这里是一个示例，展示了我们如何使用一个已保存的模型以及我们想要预测的数据集。我们使用的是最新创建的模型：

CREATE PROCEDURE [dbo].[uspPredictTipSingleMode]
@passenger_count int = 0,
@trip_distance float = 0,
@trip_time_in_secs int = 0,
@pickup_latitude float = 0,
@pickup_longitude float = 0,
@dropoff_latitude float = 0,
@dropoff_longitude float = 0
AS
BEGIN
DECLARE @inquery nvarchar(max) = N'
SELECT
@passenger_count as passenger_count,
@trip_distance as trip_distance,
@trip_time_in_secs as trip_time_in_secs,
[dbo].[fnCalculateDistance] (
@pickup_latitude,
@pickup_longitude,
@dropoff_latitude,
@dropoff_longitude) as direct_distance';
DECLARE @lmodel2 varbinary(max);
-- Get the latest non-real-time scoring model
SET @lmodel2 = (SELECT TOP 1
[Model]
FROM [dbo].[NYCTaxiModel]
WHERE IsRealTimeScoring = 0
ORDER BY [CreatedOn] DESC);
EXEC sp_execute_external_script @language = N'R',
@script = N'
mod <- unserialize(as.raw(model));
print(summary(mod))
OutputDataSet<-rxPredict(modelObject = mod,
data = InputDataSet,
outData = NULL, predVarNames = "Score",
type = "response",
writeModelVars = FALSE,
overwrite = TRUE);
str(OutputDataSet)
print(OutputDataSet)',
@input_data_1 = @inquery,
@params = N'@model varbinary(max),
@passenger_count int,
@trip_distance float,
@trip_time_in_secs INT ,
@pickup_latitude FLOAT ,
@pickup_longitude FLOAT ,
@dropoff_latitude FLOAT ,
@dropoff_longitude FLOAT',
@model = @lmodel2,
@passenger_count =@passenger_count ,
@trip_distance=@trip_distance,
@trip_time_in_secs=@trip_time_in_secs,
@pickup_latitude=@pickup_latitude,
@pickup_longitude=@pickup_longitude,
@dropoff_latitude=@dropoff_latitude,
@dropoff_longitude=@dropoff_longitude
WITH RESULT SETS ((Score FLOAT));
END
GO

一旦创建了[dbo].[uspPredictTipSingleMode]，你的应用程序现在就可以使用这个存储过程来获取评分（小费概率）；例如：

EXEC [dbo].[uspPredictTipSingleMode]
@passenger_count = 2
,@trip_distance   = 10
,@trip_time_in_secs     = 1950
,@pickup_latitude = 47.643272
,@pickup_longitude      = -122.127235
,@dropoff_latitude      = 47.620529
,@dropoff_longitude     = -122.349297

输出应该类似于以下内容。在这种情况下，值 0.64 表示得到小费的概率——即 64%：

Score
----------------------
0.640058591034195

快速批量预测

如前所述，模型训练步骤和预测步骤都调用sp_execute_external_script，该脚本调用 R 进程。实时评分和本地评分允许你进行预测而不调用 R 进程。因此，这些评分方法提高了预测操作的性能。

此外，实时评分和本地评分允许你使用机器学习模型而无需安装 R。只要获得一个兼容格式的预训练模型并将其保存到 SQL Server 数据库中，就可以轻松调用预测操作。

先决条件

实时评分

SQL Server 2016 和 SQL Server 2017 都支持使用sp_rxPredict进行实时评分。

此存储过程是一个使用UNSAFE集合的 CLR 存储过程，需要您将数据库设置为TRUSTWORTHY。

这里是一个将PREDICT函数作为SELECT语句一部分调用的示例：

EXEC dbo.sp_rxPredict @model,
@inputData = @query;

在这种情况下：

  • @model: 包含之前准备好的实时评分模型

  • @query: 要评分的数据的查询定义

原生评分

SQL Server 2017 引入了一个新函数，PREDICT，允许您使用原生评分获取预测值。您不再需要使用带有 R 脚本的sp_execute_external_script进行预测，而是可以在SELECT语句的FROM子句中调用它，这使得预测分析的实用化变得更加容易。此外，使用PREDICT意味着您不再需要在每次进行预测时调用额外的 R 进程。

这个PREDICT函数是 T-SQL 的新功能，不要与现有的 DMX 的PREDICT函数混淆。

这里是一个将PREDICT函数作为SELECT语句一部分调用的示例：

SELECT  d.Input1, d.Input2, p.Output_Pred
FROM PREDICT( MODEL = @model,  DATA = d)
WITH (Output_Pred FLOAT) p;

在这种情况下：

  • d: 数据源，例如表、视图或公共表表达式。

  • Input1, Input2: 数据源中的列。

  • @model: 包含已之前准备好的实时评分模型。

  • Output_Pred: 正在预测的输出值。通常，列名由预测值的列名加上一个_Pred后缀组成；例如，Tipped_Pred，其中Tipped是正在预测的列的名称。

集成 R 模型以实现快速批量预测

在继续下一步之前，请遵循先决条件 - 准备数据部分。

第 1 步 – 使用 T-SQL 训练和保存实时评分模型

在这一步中，您可以通过存储过程将实时评分和原生评分的预测模型，以及可选的 AUC，创建到一个表中。目标是构建一个可重用的模型。如果您已经在 SQL Server 表中创建并存储了一个兼容的模型，则可以跳过此步骤。

以下存储过程使用rxSerializeModel，它允许您以原始格式序列化 R 模型。这然后允许您以VARBINARY格式保存模型，该格式可以加载到 SQL Server 中进行实时评分。要为在 R 中使用而反转序列化，您可以使用rxUnserializeModel：

CREATE PROCEDURE [dbo].[uspTrainTipPredictionModelWithRealTimeScoring]
AS
BEGIN
DECLARE @auc FLOAT;
DECLARE @model VARBINARY(MAX);
-- The data to be used for training
DECLARE @inquery NVARCHAR(MAX) = N'
SELECT
tipped,
fare_amount,
passenger_count,
trip_time_in_secs,
trip_distance,
pickup_datetime,
dropoff_datetime,
dbo.fnCalculateDistance(pickup_latitude,
pickup_longitude,
dropoff_latitude,
dropoff_longitude) as direct_distance
FROM dbo.nyctaxi_sample
TABLESAMPLE (10 PERCENT) REPEATABLE (98052)'
-- Calculate the model based on the trained data and the AUC.
EXEC sp_execute_external_script @language = N'R',
@script = N'
## Create model 
logitObj <- rxLogit(tipped ~ passenger_count +
trip_distance +
trip_time_in_secs +
direct_distance,
data = InputDataSet);
summary(logitObj)
## Serialize model             
## model <- serialize(logitObj, NULL); 
model <- rxSerializeModel(logitObj,
realtimeScoringOnly = TRUE);
predOutput <- rxPredict(modelObject = logitObj,
data = InputDataSet, outData = NULL,
predVarNames = "Score", type = "response",
writeModelVars = FALSE, overwrite = TRUE);
library(''ROCR'');
predOutput <- cbind(InputDataSet, predOutput);
auc <- rxAuc(rxRoc("tipped", "Score", predOutput));
print(paste0("AUC of Logistic Regression Model:", auc));
',
@input_data_1 = @inquery,
@output_data_1_name = N'trained_model',
@params = N'@auc FLOAT OUTPUT, @model VARBINARY(MAX) OUTPUT',
@auc = @auc OUTPUT,
@model = @model OUTPUT;
-- Store the train model output and its AUC
INSERT INTO [dbo].[NYCTaxiModel] (Model, AUC, IsRealTimeScoring)
SELECT @model, @auc, 1;
END
GO

要将 R 中创建的模型存储在 SQL Server 表中，您必须首先对其进行序列化。在 R 中，序列化的模型在使用它进行预测之前必须先反序列化。

第 2 步 a – 使用实时评分操作化模型

以下是一个示例脚本，其中我们使用实时评分模型通过sp_rxPredict预测翻盘的可能性：

DECLARE @logit_model VARBINARY(MAX) =
(SELECT TOP 1 [Model]
FROM [dbo].[NYCTaxiModel]
WHERE [IsRealTimeScoring] = 1
ORDER BY [CreatedOn] DESC);
EXEC dbo.sp_rxPredict @model = @logit_model,
@inputData = N'SELECT
2 AS passenger_count,
10 AS trip_distance,
1950 AS trip_time_in_secs,
dbo.fnCalculateDistance(47.643272,
-122.127235,
47.620529,
-122.349297) AS direct_distance';

输出结果应仅为您提供通过以下行推送的预测值：

tipped_Pred
----------------------
0.640058591034195
(1 row affected)

第 2 步 b – 使用原生评分操作化模型

以下是一个示例脚本，其中我们使用 PREDICT 函数和一个 R 实时评分模型来预测小费的可能性。SQL Server 2017 中的 PREDICT 函数可以从上一步读取存储的序列化模型以进行预测分析：

DECLARE @logit_model VARBINARY(MAX) =
(SELECT TOP 1 [Model]
FROM [dbo].[NYCTaxiModel]
WHERE [IsRealTimeScoring] = 1
ORDER BY [CreatedOn] DESC);
WITH d AS (
SELECT      2 AS passenger_count,
10 AS trip_distance,
1950 AS trip_time_in_secs,
dbo.fnCalculateDistance(47.643272,
-122.127235,
47.620529,
-122.349297) AS direct_distance)
SELECT  *
FROM PREDICT( MODEL = @logit_model, DATA = d)
WITH (tipped_Pred FLOAT) p;

输出应包括您在 SELECT 语句中指定的任何列，并且应如下所示：

tipped_Pred passenger_count trip_distance trip_time_in_secs direct_distance
----------- --------------- ------------- ----------------- ---------------
0.640058591 2               10            1950              10.4581575644
(1 row affected)

管理工作负载的角色和权限

将 R 脚本作为扩展性框架工作负载的一部分运行，以及使用实时评分和本地评分进行预测操作，需要首先设置一些角色和权限。

扩展性框架工作负载

本节概述了使用 sp_execute_external_script 从 SQL Server 运行 R 的典型安全要求。可以使用 SQL Server 登录或 Windows 用户账户直接从 SQL Server 使用存储过程运行 R 脚本。以下是为该账户设置足够权限的步骤：

  1. 允许访问 R 脚本将运行的数据库的权限。

  2. 允许读取受保护对象（如表）的数据的权限。这包括（但不限于）可能存储模型的表以及用于训练模型或作为预测输入的表/视图。

  3. 如果 R 脚本需要将新数据写入表，例如模型或评分结果，允许写入新数据的权限。

  4. 如果 R 脚本需要在运行时安装 R 包，允许安装新包的权限。

通常，创建角色来管理权限集更容易，然后将用户分配到这些角色，而不是单独设置用户权限。

以下是一个示例，说明如何创建一个角色并将其分配给名为 JulieGuest2 的登录，按照步骤 1、2 和 3 进行：

-- Create a new role
CREATE ROLE TutorialDBRUser AUTHORIZATION dbo
GO
-- Assign the role to a new member JulieGuest2 so that the login
-- can connect to the database Tutorial DB.
ALTER ROLE TutorialDBRUser ADD MEMBER JulieGuest2
GO
-- Allow members of TutorialDBRUser to read and write.
ALTER ROLE db_datareader ADD MEMBER TutorialDBRUser
GO
ALTER ROLE db_datareader ADD MEMBER TutorialDBRUser
GO
-- Allow members of TutorialDBRUser to run external script
GRANT EXECUTE ANY EXTERNAL SCRIPT TO [TutorialDBRUser]
GO
-- Allow members of TutorialDBRUser to run a specific
-- stored procedure.
GRANT EXECUTE ON [dbo].[predict_rentals] TO [TutorialDBRUser]
GO

快速批量预测工作负载

只有在使用实时评分或本地评分时，才遵循以下步骤：

  • 对于使用 sp_rxPredict 进行实时评分，您需要将执行此存储过程的用户添加到 rxpredict_users 中

  • 对于使用 SQL Server 2017 中新提供的 PREDICT 语法进行本地评分，您需要在数据库上授予 EXECUTE 权限

前面的步骤假设用户有权读取预测操作的真实时评分模型和输入数据集。

外部包

从 SQL Server 2017 开始，您也可以通过 CREATE EXTERNAL LIBRARY 添加外部库，只要您有 ALTER ANY EXTERNAL LIBRARY 权限：

GRANT ALTER ANY EXTERNAL LIBRARY TO [TutorialDBRUser]
GO

您必须首先从源下载包；例如，从 CRAN (ggplot2cran.r-project.org/web/packages/ggplot2/index.html) 下载到 SQL Server 可以访问的路径：

CREATE EXTERNAL LIBRARY ggplot2pkg
FROM
(CONTENT = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\ggplot2.zip')
WITH (LANGUAGE = 'R');

如果您正在使用 SQL Server 2016，要安装新的 R 包，您需要在机器上具有管理员访问权限。安装步骤在 SQL Server 之外，直接在关联到 SQL Server R 服务的 R 上执行。详细步骤在第三章中概述，管理 SQL Server 2017 和 R 的机器学习服务。

工具

在 T-SQL 中嵌入的 R 代码的操作化有三个主要选项。所有这些工具都是免费的：

  • SQL Server 管理工作室 (SSMS)

  • Visual Studio 的 R 工具 (RTVS)

  • SQL Server 数据工具 (SSDT)

本节概述了工具如何帮助您将 R 代码在 SQL Server 中作为工作流程的一部分进行操作化。

将 SSMS 作为操作化 R 脚本的一部分使用

SSMS 是一个强大的工具，它允许您将上一节中的预测分析操作化。SSMS 还提供了管理与在 SQL Server 中操作化 R 代码和维护相关的各种管理任务的能力，例如：

  • 管理权限，如本章前面所述。

  • 管理 R 包（在 SQL Server 2017 中），如本章前面所述。

  • 管理集成 R 代码的存储过程，如前文所述。

  • 管理 SQL Server R 服务的资源，如第三章中所述，管理 SQL Server 2017 和 R 的机器学习服务。

  • 使用内置自定义报告和 DMVs 监控 SQL Server R 服务，如使用 SQL Server R 服务的自定义报告中所述。

  • 创建和管理执行 R 脚本的工作。请参阅本章后面的安排训练和预测操作。

要获取最新版本的 SSMS 以帮助您使用 SQL Server R 服务开发和管理工作流程，请访问docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms。

使用 SQL Server R 服务的自定义报告

在 GitHub 上提供了针对 SQL Server R 服务的自定义报告：github.com/Microsoft/sql-server-samples/tree/master/samples/features/r-services/ssms-custom-reports

以下是一个自定义报告列表以及它们可以帮助您实现的内容：

报告用途
R 服务 - Configuration.rdl查看 R 服务的安装设置和 R 运行时的属性。安装后配置 R 服务。
R 服务 - Packages.rdl查看 SQL Server 实例上安装的 R 包及其属性，例如名称和版本。
R 服务 - Resource Usage.rdl查看 SQL Server 和 R 脚本执行的资源消耗。查看外部资源池的内存设置。
R Services - Extended Events.rdl查看扩展事件以了解 R 脚本执行情况。
R Services - Execution Statistics.rdl查看 R 服务的执行统计信息，包括但不限于 R 脚本执行次数、并行执行次数以及RevoScaleR函数。

首次添加自定义报告

从前面的 GitHub 位置下载自定义报告后，请按照以下步骤添加自定义报告：

  1. 前往 SSMS | 对象资源管理器。

  2. 在对象资源管理器中右键单击 SQL Server 实例的名称，然后选择报告 | 自定义报告…

  3. 从下载位置添加 RDL 文件。

添加后，您可能会看到一个如下警告对话框：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00112.jpeg

图 7.4：SSMS 中的自定义报告运行警告

点击运行表示您确认希望运行这些报告。

图 7.5展示了成功导入的 R 服务 - 执行统计报告。它表示有 38 次执行中的 24 次 R 脚本执行错误，最受欢迎的RevoScaleR函数是rxPredict_rxLogit：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00113.jpeg

图 7.5：SSMS 中的 SQL Server R 服务 - 执行统计报告

查看 R 服务自定义报告

第一次添加 R 服务自定义报告后，您可以再次访问它。以下是步骤：

  1. 前往 SSMS | 对象资源管理器。

  2. 右键单击 SQL Server 实例的名称。

  3. 选择报告 | 自定义报告。如果您已添加所有自定义报告，您应该看到类似以下内容：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00114.jpeg

图 7.6：在 SSMS 中查看自定义报告

使用 DMVs 管理 SQL Server 机器学习服务

有各种 DMVs 可供帮助您监控您已部署的 R 脚本。本节将 SQL Server 机器学习服务的 DMVs 分为以下两个类别，具体如下。

系统配置和系统资源

您可能熟悉sys.dm_exec_sessions和sys.dm_os_performance_counter，分别用于了解活动会话和系统性能计数器。以下是一个您应该了解更多以跟踪和监控 SQL Server 中 R 脚本执行性能和使用的 DMVs 列表：

  • sys.dm_exec_sessions: 查看用户会话和系统会话的详细信息，分别标识为with session_id >= 51和< 51。

  • sys.dm_os_performance_counters: 查看每个系统性能计数器的详细信息，包括与 R 脚本相关的计数器。以下是一个与 SQL Server R 服务相关的特定脚本的示例：

SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%External Scripts%'
  • sys.dm_external_script_requests: 查看当前实例上的活动外部脚本：
SELECT
[external_script_request_id]
, [language]
, [degree_of_parallelism]
, [external_user_name]
FROM sys.dm_external_script_requests;
  • sys.dm_external_script_execution_stats: 通过计数器查看新外部脚本的整体使用情况。

资源管理器

在 SQL Server 2016 中，新增了两个 DMV 来帮助监控外部资源池：sys.resource_governor_external_resource_pools 和 sys.dm_resource_governor_external_resource_pool_affinity。如果你熟悉一般性的资源管理器的跟踪和管理，你可能会知道以下列出的另外两个 DMV：

  • sys.resource_governor_resource_pools：查看当前资源池状态、资源池的当前配置及其统计信息。

  • sys.resource_governor_workload_groups：查看工作负载组的统计信息和当前配置。此 DMV 新增了一个列来显示与工作负载组关联的外部池的 ID。

  • sys.resource_governor_external_resource_pools：查看外部资源池的当前配置值。在撰写本文时，SQL Server 2016/2017 企业版允许你配置额外的资源池，使得在 SQL Server 中运行的 R 作业的资源将隔离于来自远程客户端的资源。

  • sys.dm_resource_governor_external_resource_pool_affinity：此 DMV 允许你查看与特定资源池亲和的处理器和资源。

使用 Visual Studio 实现 R 代码的运营化

使用R Tools for Visual Studio（RTVS）开发 R 脚本或使用 R 脚本的 T-SQL 现在变得简单。如果你已经使用 SQL Server Data Tools 作为你的 SQL Server 数据库项目的 IDE，你可以在解决方案中简单地添加一个新的 R 项目。这种集成在 Visual Studio 2017 中得到了改进。

如果你还没有安装 Visual Studio，请访问www.visualstudio.com/downloads/。

RTVS 作为数据科学和分析应用程序工作负载的一部分进行安装。

从 Visual Studio 安装程序中，你可以将数据科学和分析应用程序工作负载添加到你的 Visual Studio 2017 安装中，如图图 7.7所示：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00115.jpeg

图 7.7：在 Visual Studio 2017 安装程序中选择数据科学和分析应用程序选项

以下是一些使用 RTVS 开始使用的额外提示：

  1. 在 RTVS 中通过选择文件 | 新建 | 项目来创建一个新的 R 项目。项目名称和文件路径与以下类似：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00116.jpeg

图 7.8：创建一个新的 R 项目

  1. 在 RTVS 中，你可以选择运行 R 脚本的工空间。如果你已经按照第四章中提到的安装了 R 服务的 SQL Server，即数据探索和数据可视化，你会看到类似以下的内容：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00117.jpeg

图 7.9：显示 RTVS 可连接的所有工空间

前往 R Tools | Windows | Workspaces 或按Ctrl + 9显示工作空间窗口。

  1. 您可以从 R 交互式窗口运行 R 代码或在 R 项目中保存 R 文件。您可以参考docs.microsoft.com/en-us/visualstudio/rtvs/了解 RTVS 功能的更多信息。

  2. 您也可以通过在项目上右键单击并选择“添加新项”，然后选择 SQL 查询来在项目中添加一个 SQL 查询文件，如下所示：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00118.jpeg

图 7.10：选择要添加到 R 项目中的新项/文件

  1. RTVS 还允许您通过模板将 R 代码集成到 SQL Server 存储过程中。要访问此功能，只需像上一步一样点击“添加新项”，然后选择 SQL 存储过程。有关更多信息，请参阅docs.microsoft.com/en-us/visualstudio/rtvs/sql-server。

在 SQL Server 之外集成 R 工作负载和预测操作

在本节中，您将学习如何将您在上一节中创建的 R 工作负载和预测操作包含在 SQL Server 之外。我们将讨论如何在 PowerShell、SQL Agent 作业和SQL Server Integration Services (SSIS)中运行工作负载和操作。

请注意，您也可以使用 SQLCMD、C#在 SSIS、Azure 以及 Linux 上的 Bash 来执行这些工作负载/预测操作。这次讨论超出了本章的范围。

通过 PowerShell 执行 SQL Server 预测操作

假设您已经创建了从 SQL Server 执行 R 脚本的存储过程，例如前面示例中的[dbo].[uspTrainTipPredictionModel]，您可以轻松地将此命令作为 PowerShell 工作流的一部分执行。

这里是一个从 PowerShell 调用存储过程的简单示例：

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=.;Database=Taxi;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "EXEC [dbo].[uspPredictTipSingleMode]
@passenger_count    = 2
,@trip_distance   = 10
,@trip_time_in_secs     = 35
,@pickup_latitude = 47.643272
,@pickup_longitude      = -122.127235
,@dropoff_latitude      = 47.620529
,@dropoff_longitude     = -122.349297
"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

安排训练和预测操作

在 SSMS 中，您可以创建一个新的 SQL Server 作业，允许您一次性或按特定计划运行 R 代码。

例如，您可以执行一个计划离线预测分析工作负载。为此，只需通过 SSMS 创建一个作业：

  1. 要创建作业，您需要是 SQL Server Agent 固定数据库角色或 sysadmin 固定服务器角色的成员。只有作业所有者或 sysadmin 角色的成员才能更新作业的定义。

  2. 在 SSMS 的对象资源管理器中，展开您想要在其中创建 SQL Server Agent 作业的 SQL Server 实例。

  3. 展开 SQL Server Agent，然后在“作业”文件夹上右键单击，然后选择“新建作业…”：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00119.jpeg

图 7.11：使用 SSMS 创建新的 SQL Server Agent 作业

  1. 在“常规”页面上提供详细信息：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00120.jpeg

图 7.12：在新建作业窗口中添加更多详细信息

  1. 在新建作业窗口的左侧菜单中点击“步骤”，然后在新建作业窗口的底部点击“新建…”。

  2. 在新建作业步骤中提供要执行的详细信息。在这个例子中，我们想要更新纽约出租车训练模型。然后点击“确定”：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00121.jpeg

图 7.13：在 SQL Server 代理作业中调用 R 集成存储过程作为步骤

  1. 在新建作业窗口中，从左侧菜单选择“计划”。

  2. 从新建作业窗口的底部点击“新建…”。

  3. 提供你希望此作业受其约束的计划详情。

  4. 在新建计划窗口中点击“确定”，然后点击新建作业窗口以保存更改。

将 R 脚本作为 SSIS 的一部分进行操作

R 脚本可以轻松地作为 SSIS 工作流程的一部分进行集成。主要有两种方式：作为执行进程任务的一部分运行，以及作为执行 SQL 任务的一部分运行：

  1. 在执行进程任务中运行 R 代码（不是作为 SQL Server R 服务的一部分）可以通过调用 Rscript.exe 来简单地完成。如果你已经有一个准备执行的可执行 R 文件，那么只需在 SSIS 包中添加执行进程任务。你还可以将 SSIS 包中的执行进程任务的输入/输出编织到 R 文件中：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00122.jpeg

图 7.14：在 SSIS 执行进程任务中外部执行 R 脚本

  1. 在 SQL Server 中使用 SSIS 中的执行 SQL 任务运行预测操作：如果你已经有一个执行预测（或训练模型）的存储过程，那么只需从 SSIS 中的执行 SQL 任务调用此存储过程。还可能将 SSIS 包中的输入/输出编织到执行 SQL 任务中：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00123.jpeg

图 7.15：在 SSIS 中将 R 集成存储过程作为执行 SQL 任务步骤执行

摘要

在本章中，你学习了将现有的预测分析 R 代码集成到 SQL Server R 外部的步骤，使用了扩展性框架。你还看到了 SQL Server 2017 中新 PREDICT 函数的简单性和强大功能，它允许在不安装 R 的情况下进行本地评分。在预测操作中管理运行预测分析工作负载所需的安全性也很重要。你学习了如何使用 RTVS 将 SQL 查询添加到 R 项目中。最后，你发现了将 R 代码和预测操作集成到现有工作流程中的不同可能性，作为 SQL Server 存储过程、SQL Server 代理作业、PowerShell 脚本和 SSIS 项目。

带着这些新技能，我们准备好管理数据科学解决方案的下一个构建块，作为数据库生命周期的一部分：管理实践。在下一章中，你将学习如何在 持续集成/持续交付（CI/CD）和持续模型性能监控中管理数据科学解决方案。

第八章：部署、管理和监控包含 R 代码的数据库解决方案

在 SQL Server 数据库中运营 R 代码意味着数据科学家/数据库开发者也可以利用将数据科学解决方案作为数据库生命周期管理（DLM）的一部分进行生产化。这包括以下内容：

  • 将 R 代码作为 SQL Server 数据库项目的一部分提交到版本控制

  • 将数据科学解决方案的存储过程作为 SQL Server 单元测试的一部分添加

  • 将数据科学解决方案集成到 持续集成/持续交付（CI/CD）流程中

  • 定期监控生产中数据科学解决方案的性能

在本章中，我们将使用 Visual Studio 2017 和 Visual Studio Team Services 中的 SQL Server 数据工具（SSDT）来执行此 DLM 工作流程。然而，这个基本概念可以应用于您或您的团队可能已经使用的任何其他 CI/CD 平台。

将 R 集成到 SQL Server 数据库生命周期工作流程中

在第七章“将 R 预测模型投入运营”中，我们讨论了如何在 Visual Studio 2017 中创建 R 项目。我们还讨论了将 R 代码集成到 SQL Server 中的 sp_execute_external_script 作为一部分。在这里，我们将重新审视 Visual Studio 2017，特别是在将 R 代码集成到 sp_execute_external_script 作为 SQL Server 数据库项目的一部分，以及作为数据库生命周期工作流程的一部分。

为数据库生命周期工作流程准备您的环境

在本节中，我们将讨论数据库生命周期工作流程的阶段以及我们将使用的工具。对于工作流程中的每个阶段，还将有一些建议供您探索。

  1. 编码和管理 SQL Server 数据库项目/解决方案：管理构成 SQL Server 数据库项目的 SQL Server DML/DDL 脚本有几种不同的方法。Visual Studio 2017 (VS2017) 中的 SQL SSDT 是一个成熟的产品，它正式化了数据库模式和对象的创建和修改。在本节中，我们将使用 VS2017 中的 SSDT。

您可以使用 VS2017 Community、Professional 或 Enterprise 版本。请访问 www.visualstudio.com/vs/compare/ 获取这些版本如何比较的最新信息。在本节的演练和示例中，我们将使用 Visual Studio Enterprise Edition，但您可以使用任何版本。您可以从 www.visualstudio.com/vs/ 下载这些版本。

值得尝试的其他替代方案包括：

    • SQL Server Management Studio：RedGate 开发了一些插件，可以丰富 DevOps/数据库生命周期管理

    • SQL Operations Studio（预览版）：这个工具是基于 VS Code 构建的，这意味着它也有很高的潜力满足 DevOps 工作流程，包括源代码控制

  1. 单元测试：就像应用程序开发一样，数据库开发将从单元测试框架中受益，特别是如果它可以自动化的话。有两个广为人知的单元测试框架适用于 SQL Server 数据库，即 tSQLt 和集成在 Visual Studio 中的 SQL Server 单元测试。以下是链接：

在本节中，我们将使用 VS2017 中的 SQL Server 单元测试。

另一个值得尝试的工具是：

    • 基于 tSQLt 框架的 RedGate SQL Test，它是 SSMS 的一个扩展
  1. 版本控制：目前有许多流行的版本控制系统可供选择，例如 Git 和 团队基础版本控制（TFVC）。在本节中，我们将使用托管在 Visual Studio Team Services（VSTS）中的 TFVC。VS2017 可以连接到 VSTS 仓库。您可以在网上注册 VSTS 账户：www.visualstudio.com/team-services/

值得尝试的其他替代方案包括：

使用 Visual Studio，您可以连接到在线版本控制主机，如 GitHub 和 VSTS，以及私有的本地版本控制服务器，如 团队基础服务器（TFS）：

  1. CI/CD：VSTS 支持托管代理和私有代理。托管代理是一个基于云的代理，执行持续集成和持续交付。私有代理是一个基于本地的代理版本，可在 Visual Studio 2017 中使用。设置 CI 意味着当脚本被检查入时，代理将自动构建并可选择执行一系列测试。设置 CD 允许我们仅针对基线测试代码发布和/或模式更改。在本章中，我们将使用带有私有代理的 VSTS 来部署到本地 SQL Server 数据库。

值得尝试的其他替代方案包括：

    • VSTS 支持托管代理，允许您自动部署到 Azure VM

    • VSTS 支持托管代理，允许您将部署到 Azure SQL 数据库，自 2017 年 10 月起，它也支持 R

图 8.1 展示了使用 VSTS 的 CI/CD 工作流程，我们将在此章中使用它来构建我们的示例 SQL Server R 服务解决方案：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00124.jpeg

图 8.1 使用 VSTS 的 CI/CD 流程

来源：docs.microsoft.com/en-us/vsts/build-release/actions/ci-cd-part-1

本章的先决条件

工具URL备注
Visual Studio 2017下载：www.visualstudio.com/downloads/社区版是免费的。
VSTS注册/登录：www.visualstudio.com/team-services/免费注册个人账户。
PowerShell v2.0 或更高版本下载 PowerShell：www.microsoft.com/en-us/download/details.aspx?id=42554您需要此软件来本地设置私有代理。

创建 SQL Server 数据库项目

在本节中，我们将向您介绍如何在 VS2017 中创建数据库项目。

  1. 在 VS2017 中，单击文件 | 新建项目。

  2. 在左侧面板中选择已安装的 SQL Server，然后单击 SQL Server 数据库项目模板。

  3. 在“名称”字段中输入Ch08，在“解决方案名称”字段中输入SQL Server R Services Book，如图所示：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00125.jpeg

图 8.2 Visual Studio 中的新项目

  1. 选择保存解决方案的位置。

如果您已经有了用于版本控制的本地文件夹，您可以在此指定路径。

在这个例子中，我的 VSTS 项目名为 SQL Server R Services Book，它与我的本地文件夹C:\VSTS\SQL Server R Services Book相关联。

  1. 确保已选中“为解决方案创建目录”和“添加到源代码控制”。

  2. 在新建项目对话框中单击“确定”。解决方案资源管理器窗口应显示类似于以下截图的内容：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00126.jpeg

图 8.3 解决方案资源管理器中的数据库项目

从这里，您可以添加新的对象，例如表、存储过程以及许多其他对象。

将现有数据库导入到项目中

现在我们有一个空白的数据库，我们可以导入从第七章“实现 R 预测模型”中创建的现有数据库：

  1. 在 Ch08 上右键单击，选择导入 | 数据库：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00127.jpeg

图 8.4 将数据库导入到数据库项目中

  1. 在导入数据库对话框中，单击“选择连接”。然后，指定到您之前在第七章，“实现 R 预测模型”中创建的数据库的连接。

  2. 导入数据库对话框应看起来如下。单击“开始”：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00128.jpeg

图 8.5 导入数据库对话框

  1. 然后导入数据库对话框显示导入进度的摘要：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00129.jpeg

图 8.6 数据库项目导入摘要

  1. 解决方案应看起来如下：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00130.jpeg

图 8.7 导入数据库后解决方案资源管理器显示的数据库项目

  1. 在我们进行任何更多更改之前，让我们通过在根解决方案节点上右键单击并选择“生成解决方案”，或者您也可以单击 Ctrl + Shift + B 来生成解决方案。

注意，输出应包含针对每个存储过程引用sp_execute_external脚本的多个警告，如下所示：

C:\VSTS\SQL Server R Services Book\SQL Server R Services Book\Ch08\dbo\Stored Procedures\uspTrainTipPredictionModelWithRealTimeScoring.sql(27,8): Warning:  SQL71502: Procedure: [dbo].[uspTrainTipPredictionModelWithRealTimeScoring] has an unresolved reference to object [dbo].[sp_execute_external_script].

向存储过程添加新的存储过程对象

这里是向现有数据库项目添加新对象的一个示例：

  1. 要创建新过程，您可以右键单击“存储过程”文件夹，然后点击“添加”|“存储过程…”：

  2. 在名称字段中输入uspTrainTipPredictionModelWithRealTimeScoringDTree作为新的存储过程：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00131.jpeg

图 8.8 向数据库项目添加新项

  1. 将以下脚本添加到存储过程中：
CREATE PROCEDURE [dbo].[uspTrainTipPredictionModelWithRealTimeScoringDTree]
AS
BEGIN
DECLARE @auc FLOAT;
DECLARE @model VARBINARY(MAX);
-- The data to be used for training
DECLARE @inquery NVARCHAR(MAX)= N'
SELECT
tipped,
fare_amount,
passenger_count,
trip_time_in_secs,
trip_distance,
pickup_datetime,
dropoff_datetime,
dbo.fnCalculateDistance(pickup_latitude,
pickup_longitude,
dropoff_latitude,
dropoff_longitude) as direct_distance
FROM dbo.nyctaxi_sample
TABLESAMPLE (10 PERCENT) REPEATABLE (98052)'
-- Calculate the model based on the trained data and the AUC.
EXEC sys.sp_execute_external_script @language = N'R',
@script = N'
## Create model 
dTreeObj<- rxDTree(tipped ~ passenger_count +
trip_distance +
trip_time_in_secs +
direct_distance,
data = InputDataSet);
treeCp <- rxDTreeBestCp(dTreeObj);
dTreeObjChosen<- prune.rxDTree(dTreeObj, cp = treeCp);
## Serialize model             
model <- serialize(dTreeObjChosen, NULL);
predictTree <- rxPredict(dTreeObjChosen, data = InputDataSet, overwrite = TRUE)
library('ROCR');
predOutput <- cbind(InputDataSet, predictTree);
auc <- rxAuc(rxRoc("tipped", "tipped_Pred", predOutput));
print(paste0("AUC of Classification Model:", auc));
',
@input_data_1 = @inquery,
@output_data_1_name = N'trained_model',
@params= N'@auc FLOAT OUTPUT, @model VARBINARY(MAX) OUTPUT',
@auc= @auc OUTPUT,
@model = @model OUTPUT;
-- Store the train model output and its AUC
INSERT INTO [dbo].[NYCTaxiModel] (Model, AUC,IsRealTimeScoring)
SELECT @model, @auc, 1;
END

  1. 按Ctrl + S保存文件。

  2. 您现在可以使用Ctrl + Shift + B重新构建解决方案。

发布模式更改

有两种将更改发布到环境的方法：

  • 现有数据库

  • 新数据库

在此示例中，NYCTaxi 已经在数据库中存在。您可以识别模式更改并创建更新脚本：

  1. 右键单击“Ch08”并选择“模式比较”。

  2. 确保左侧的源指向数据库项目路径。

  3. 在“选择目标”下拉列表中，点击它以设置目标数据库。

  4. 选择“数据库”并点击“选择连接”。在这里，您可以提供对现有NYCTaxi数据库的连接。

  5. 点击“比较”，应该只显示一个文件：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00132.jpeg

图 8.9 Visual Studio 中的模式比较

  1. 在这里，您可以点击“更新”直接将更改应用到数据库中，或者点击“生成脚本”图标来生成更改的脚本。

作为最佳实践，尤其是如果您有一个正式的生产变更管理流程，您会选择生成脚本并将其包含在变更管理请求中。

向存储过程添加单元测试

向存储过程或函数等可编程对象添加单元测试是编程的良好实践的一部分：

  1. 通过右键单击存储过程或函数之一（如Ch08|dbo|存储过程|uspTrainTipPredictionModel）来创建单元测试套件。然后，选择“创建单元测试…”：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00133.jpeg

图 8.10 在 Visual Studio 中创建单元测试

  1. 选择连接：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00134.gif

图 8.11 SQL Server 测试配置

  1. 点击“确定”后，您将看到一个新创建的单元测试项目和创建的单元测试模板示例：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00135.jpeg

图 8.12 SQL Server 单元测试模板

在右上角面板中，您可以管理您的单元测试用例。由于dbo.uspTrianTipPredictionModel训练样本数据并将模型以及 AUC 存储到dbo.NYCTaxiModel中，我们将创建一个单元测试来确保：

    • 新记录已插入，

    • 创建的 AUC 达到一定的阈值

  1. 复制以下代码：
-- database unit test for dbo.uspTrainTipPredictionModel
DECLARE @RC AS INT;
DECLARE @RowCountBefore AS INT;
DECLARE @RowCountAfter AS INT;
DECLARE @AUC FLOAT;
SELECT @RC = 0;
SELECT @RowCountBefore = IS NULL((SELECT COUNT(1) ROWCOUNT
FROM [dbo].[NYCTaxiModel]
WHERE [AUC] ISNOTNULL), 0);
EXECUTE @RC = [dbo].[uspTrainTipPredictionModel];
-- Expected value: there should be a new record added to NYCTaxiModel
-- where AUC is known.
SELECT @RowCountAfter = ISNULL((SELECTCOUNT(1)ROWCOUNT
FROM [dbo].[NYCTaxiModel]
WHERE [AUC] ISNOTNULL), 0);
SELECT @AUC = (SELECTTOP 1 [AUC]
FROM [dbo].[NYCTaxiModel]
ORDER BY [CreatedOn] DESC);
SELECT
@RowCountAfter - @RowCountBeforeRowCountAdded,
IIF(@AUC > 0.5, 1, 0) AUCOfModel;

  1. 在测试条件面板中，点击inconclusiveCondition1并点击红色十字删除它。

  2. 现在，从测试条件中选择“标量值”并点击加号按钮。

  3. 然后，右键单击 scalarValueCondition1 并点击属性。

  4. 在属性窗口中更新以下值：

    1. 名称：TestNYCTaxiModelAdded

    2. 预期值：1

    3. 预期为空：False

  5. 重复步骤 6 到 8，并在属性窗口中更改以下值：

    1. 名称：TestNYCTaxiModelAdded

    2. 预期值：1

    3. 预期为空：False

一旦设置完毕，你的 Visual Studio 应该看起来像这样：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00136.jpeg

图 8.13 dbo.uspTrainTipPredictionModel 的 SQL Server 单元测试

  1. 删除 UnitTest.cs。

  2. 然后，右键单击 Ch08_Test 项目并点击构建。

  3. 导航到测试资源管理器并点击运行所有。

  4. 几秒钟后，dbo_uspTrainTipPredictionModelTest 出现在已通过测试下。点击它以查看执行摘要。

  5. 点击输出以查看更多详细信息，例如：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00137.jpeg

图 8.14 测试执行结果

现在，你已经学会了如何创建针对存储过程的单元测试，该测试针对现有的 NYC Taxi Model 上的现有存储过程执行。理想情况下，单元测试是在最近发布的 SQL Server 上运行的。

使用版本控制

从 Visual Studio，我们可以提交解决方案并管理版本控制中的更改。在这个特定实例中，我们正在使用 VSTS 进行提交。假设你已经在一个 VSTS 中创建了一个项目。

下面是本节其余部分的要求：

  1. VSTS 项目：要设置新的 VSTS 项目，只需访问：www.visualstudio.com/team-services/

VSTS 项目的 URL 应遵循以下格式：

https://<你的账户>.visualstudio.com/<VSTS 项目>

本章中提到的 VSTS 项目命名为 SQL Server R Services Book。因此，我的 URL 是 https://mssqlgirl.visualstudio.com/SQL%20Server%20R%20Services%20Book

  1. VSTS 项目映射到本地文件夹。

这里映射到项目的本地文件夹是 C:\VSTS\SQL Server R Services Book。在本章的早期部分，我们在该路径创建了 SQL Server 数据库解决方案。

按照以下步骤从 Visual Studio 提交你的解决方案：

  1. 在解决方案根节点上，右键单击并选择提交。

  2. 在团队资源管理器窗口中，在挂起的更改下，在注释文本框中输入 Initial check-in。

  3. 在点击提交之前，请先审查相关的工作项、包含的更改和排除的更改：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00138.jpeg

图 8.15 检查挂起的更改

  1. 在提交确认对话框中，点击是。

一旦所有文件都成功提交，你还可以在 VSTS 网站上查看它们。例如：

https://mssqlgirl.visualstudio.com/SQL%20Server%20R%20Services%20Book/_versionControl

设置持续集成

持续集成（CI）的主要思想是基于一个或多个触发器执行自动化的构建。执行构建的一个触发器是检查事件。另一个可能是计划构建。选择哪个触发器合适取决于各种因素，例如项目的复杂性和团队的文化。在本节中，因为项目较小，我们将自动化由检查触发的构建。我们还将测试作为构建的一部分添加。

VSTS 是一个自动化构建、测试部署和监控的好平台。在本节中，我们将配置构建定义并在 VSTS 中安排持续集成。

确保 Visual Studio 解决方案，包括 SQL Server 数据库项目和 SQL Server 单元测试项目，构建成功。

图 8.16显示了 VSTS 在线上的 SQL Server R Services Book 团队。在接下来的几个部分中，我们将使用浏览器上的 VSTS 来配置 CI：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00139.jpeg

图 8.16 检查挂起的更改

本节其余部分的一个先决条件是：

在 VSTS 中创建构建定义

按照以下步骤在 VSTS 中创建构建定义：

  1. 在 VSTS 项目网站上，点击顶部菜单中的构建和发布，然后选择构建。选择新定义。

  2. 从空流程开始。

  3. 在任务下，转到流程并从代理队列下拉列表中选择私有代理。在 mssqlgirl 账户中，私有代理命名为 Default：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00140.jpeg

图 8.17 选择构建任务中的私有代理（默认）

  1. 审查获取源中的选择。

在$(build.sourcesdirectory)下的本地路径是指私有代理的工作空间，用于执行构建和执行其他任务。

  1. 点击第一阶段，并将显示名称值替换为构建阶段。

  2. 在顶部菜单中，从保存和排队下拉列表中选择保存。

  3. 审查保存构建定义并添加注释。

  4. 通过点击加号在构建阶段添加一个任务。

  5. 在添加任务中，搜索 MS Build，然后点击添加。

  6. 将项目更改为$/SQL Server R Services Book/SQL Server R Services Book/SQL Server R Services Book.sln。

默认值是**/*.sln，它指的是 VSTS 项目中的所有解决方案文件。

  1. 在构建阶段，添加另一个任务，命名为发布构建工件。这允许我们获取以后可能很重要的文件，例如 DACPAC 文件。

  2. 在发布构建工件任务中，指定以下详细信息：

    1. 发布路径：$(Build.Repository.LocalPath)\SQL Server R Services Book\Ch08\bin\Debug

    2. 工件名称：DACPAC

    3. 工件发布位置：Visual Studio Team Services/TFS

在此步骤中，我们只发布 DACPAC 文件。在 Visual Studio Team Services 区域发布此特定文件允许我们在发布过程中（一个持续交付步骤）稍后引用此 DACPAC。

  1. 点击保存并排队以测试构建定义。

  2. 查看队列构建中 SQL Server R 服务 Book-CI 的选项，然后点击队列。

  3. 页面将显示正在排队构建，类似于以下内容：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00141.jpeg

图 8.18 添加发布工件任务

如果构建成功，您将看到类似以下内容。现在将是熟悉构建摘要页面和工件页面的好时机：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00142.jpeg

图 8.19 查看构建结果

当您导航到工件选项卡时，您应该能够看到 DACPAC 文件夹。通过点击探索，您可以看到解决方案内的文件，包括类似于通过 Visual Studio 本地构建的构建输出：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00143.jpeg

图 8.20 探索从前一个成功构建发布的工件

将构建部署到本地 SQL Server 实例

现在，通过 VSTS 在私有代理上的构建已成功，让我们尝试将数据库部署到 SQL Server 实例。此操作的前提是私有代理必须能够访问 SQL Server 实例。图 8.21 展示了如何使用带有本地（私有）代理的 VSTS 将部署到多个本地服务器/环境：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00144.jpeg

图 8.21 VSTS 和本地代理/环境的概要布局

来源：docs.microsoft.com/en-us/vsts/build-release/concepts/agents/agents

当 SQL Server 数据库项目构建时，它将生成一个 DACPAC 文件，该文件可以用来创建一个新的数据库。因此，在 SQL Server R 服务 Book-CI 构建定义的构建阶段，我们将添加一个新任务：

  1. 导航到 SQL Server R 服务 Book-CI 构建定义。

  2. 点击构建阶段并添加一个新任务。

  3. 搜索 WinRM - SQL Server DB 部署。然后，点击添加。

如果不存在，点击检查我们的市场。搜索 使用 WinRM 的 IIS Web 应用部署 并将其安装到您的 VSTS 账户。

  1. 在使用 DACPAC 部署时，输入以下详细信息：

    1. 机器：$(UATMachine)

    2. 管理员登录：$(UATAdminUser)

    3. 密码：$(UATAdminPwd)

    4. DACPAC 文件：$(Build.Repository.LocalPath)\SQL Server R 服务 Book\Ch08\bin\Debug\Ch08.dacpac

    5. 指定 SQL 使用：发布配置文件

    6. 发布配置文件：$(System.DefaultWorkingDirectory)$(UATPublishProfilePath)

  2. 添加以下新变量：

名称值秘密
UATMachine{输入您的机器名称（FQDN 或 IP 地址），例如：uatpc.mssqlgirl.com}否
UATAdminUser{输入可以登录 UAT 机器的管理员用户}否
UATAdminPwd{输入管理员密码}是
UATPublisProfilePath\SQL Server R Services Book\Ch08\Ch08-UAT.publish.xml否
  1. 点击保存并排队以测试构建。

将测试阶段添加到构建定义中

在本节中，你将学习如何将测试阶段添加到 SQL Server R Services Book-CI 构建定义。此测试阶段将执行我们之前所做的单元测试。

在我们可以开始单元测试之前，我们需要为测试做准备。这包括向dbo.nyctaxisample表填充数据：

  1. 要添加新的测试阶段，转到流程**，点击 …**，然后选择添加代理阶段。

  2. 在代理阶段，在显示名称中输入Test Phase。

  3. 在测试阶段，添加一个新任务。

  4. 搜索命令行。然后，点击添加。

  5. 在命令行任务中输入以下详细信息：

    1. 工具：bcp

    2. 参数：Ch08.dbo.nyctaxi_sample in "$(System.DefaultWorkingDirectory)$(UATSampleFilePath)" -c -t , -r \n -U $(UATDBUser) -P $(UATDBPwd)

  6. 点击保存。

现在，我们可以添加创建和执行单元测试的步骤：

  1. 在测试阶段，添加一个新任务。

  2. 搜索Visual Studio 测试。然后，点击添加。

  3. 在 Visual Studio 测试中输入以下详细信息：

    1. 显示名称：单元测试

    2. 使用以下方式选择测试：Test assemblies

    3. 测试程序集：**\Ch08_test*.dll

    4. 搜索文件夹：$(System.DefaultWorkingDirectory)

    5. 测试平台站：Visual Studio 2017

    6. 测试运行标题：Ch08 SQL Server Testing

  4. 点击保存 & 排队。

  5. 当你查看构建时，你应该能够看到如下内容：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00145.jpeg

图 8.22 自动化测试成功

自动化 CI 构建过程

现在我们已经定义了带有构建阶段和测试阶段的 SQL Server R Services Book-CI，我们准备自动化它：

  1. 在 VSTS 中编辑 SQL Server R Services Book-CI。

  2. 点击触发器选项卡。

  3. 确保已勾选启用持续集成。

  4. 可选地，点击+添加以添加计划：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00146.jpeg

图 8.23 配置 CI 和特定计划构建

  1. 点击选项卡。

  2. 在构建属性 | 构建号格式中，输入Build_$(Date:yyyyMMdd)$(Rev:.r)。

  3. 点击保存。

现在，为了测试自动化是否工作，让我们对解决方案进行一些更改，例如：

  1. 在 Visual Studio 中打开 SQL Server R Services Book 解决方案。

  2. 从 Ch08 项目中删除以下文件：

    1. nyc_taxi_models.sql

    2. PersistModel.sql

    3. PredictTipBatchMode.sql

    4. PredictTipSingleMode.sql

  3. 现在让我们检查挂起的更改。右键单击解决方案节点并选择签入。

  4. 可选地，在点击签入按钮之前添加注释。

在成功签入后，你应该能够看到更改集编号：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00147.jpeg

图 8.24 检查 Visual Studio 的更改集信息

在 VSTS 中，你应该能够访问最新的构建并看到匹配的源版本，如下所示：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00148.jpeg

图 8.25 通过 VSTS 中的更改集信息验证自动化 CI

设置持续交付

持续交付旨在确保我们可以将良好的构建部署到所需的环境。这可能意味着 UAT 环境，或者生产环境。在本节中，我们将使用 VSTS 实现持续交付：

  1. 在 VSTS 中，转到 SQL Server R Services Book 项目。

  2. 从顶部菜单导航到构建和发布 | 发布。

  3. 点击 + | 新定义。

  4. 查看选择模板窗格。从这里，您可以从许多选项中进行选择，包括从测试管理器运行自动化测试。此选项强烈推荐用于定期检查现有模型的准确性，这将在下一步中讨论。现在，让我们选择空并点击添加。

  5. 在顶部标题处，点击铅笔图标以编辑名称为 UAT 发布 的所有定义 | 新发布定义。

  6. 让我们继续到“管道”标签页。有两个框：工件和环境。

  7. 在“工件”框中，点击添加工件。

  8. 提供以下详细信息并点击添加：

    1. 项目：SQL Server R Services Book

    2. 源（构建定义）：SQL Server R Services Book-CI

  9. 在“环境”框中，点击环境 1 的 1 阶段，0 任务。

  10. 在“任务”标签页中，点击第一行显示为“环境 1”。将环境名称更改为 UAT。

  11. 在“任务”标签页中，点击代理阶段并提供以下详细信息：

    1. 显示名称：部署到 UAT

    2. 代理队列：默认

  12. 现在，添加一个新任务用于部署到 UAT。

  13. 搜索 WinRM - SQL Server DB Deployment 并点击添加。

  14. 在“使用 Dacpac 部署”中，填写以下详细信息：

    1. 机器：$(UATMachine)

    2. 管理员登录：$(UATAdminUser)

    3. 密码：`$(UATAdminPwd)`

    4. DACPAC 文件：$(System.ArtifactsDirectory)\$(Build.DefinitionName)\DACPAC\Ch08.dacpac

    5. 服务器名称：{指定服务器名称，例如：localhost}

    6. 数据库名称：NYCTaxiUAT

  15. 前往“变量”标签页并添加以下变量：

名称值秘密
UATMachine{输入您的机器名称（FQDN 或 IP 地址），例如：uatpc.mssqlgirl.com}否
UATAdminUser{输入可以登录到 UAT 机器的管理员用户}否
UATAdminPwd{输入管理员密码}是

  1. 然后，点击保存并接受默认值。

  2. 要测试此发布定义，在“新发布定义”下，点击 + 发布并选择创建 发布，然后选择 …

  3. 在“为新发布定义创建新发布”中，在发布描述中输入 Test UAT deployment。然后，点击创建，如图所示：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00149.jpeg

图 8.26 基于最新成功构建创建 UAT 环境的新发布

可以使用不同的数据库连接设置部署到多个环境。一个帮助你实现这一点的扩展是 XDT Transform：

marketplace.visualstudio.com/items?itemName=qetza.xdttransform

一旦发布完成，它将看起来如下：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00150.jpeg

图 8.27 成功发布的成果

要在发布上启用持续交付，你必须编辑定义：

  1. 前往发布视图，点击 UAT 发布的…，然后选择编辑。

  2. 在管道视图中，进入工件框中的 SQL Server R 服务 Book-CI。

  3. 点击此处所示的持续部署触发器：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00151.jpeg

图 8.28 修改持续部署触发器

  1. 在持续部署触发器窗口中，确保启用滑块处于开启状态。

  2. 点击保存。

要测试 UAT 发布的持续交付设置，你可以在 SQL Server R 服务 Book-CI 上调用一个新的构建。视图应如下所示：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00152.gif

图 8.29 通过持续开发成功发布的成果

在摘要中，详细信息应说明发布是由 SQL Server R 服务 Book-CI 构建 _20180101.1 触发的。因此，我们成功创建了一个基本的持续交付流程。现在可以添加高级步骤，如设置集成测试和负载测试，使用与前面显示的类似步骤。有关在 VSTS 中设置此信息的更多信息，请参阅以下 Microsoft 教程：docs.microsoft.com/en-us/vsts/build-release/test/example-continuous-testing#configure-cd。

监控生产化模型的准确性

在第六章“预测建模”中，我们讨论了许多预测建模示例。创建的模型是基于训练数据的。在现实世界中，新数据不断涌现，例如在线交易、出租车交易（记得之前提到的纽约市出租车示例）和航班延误预测。因此，应该定期检查数据模型，以确保它仍然令人满意，并且没有其他更好的模型可以生成。在这方面，优秀的数据科学家会持续至少提出以下四个问题：

  1. 由于数据的变化，是否需要考虑不同的算法？

例如，如果当前模型正在使用逻辑回归（rxLogit），那么决策树算法（rxDTree）是否会因为规模或预期结果的变化而更准确？

  1. 是否有来自新交易的其他特征变得越来越重要？

考虑以下场景：目前，出租车行程的小费预测正在使用乘客数量、行程距离、行程时间和直接距离。也许定期检查其他特征，如一天中的小时、一周中的日子、接单邮编和/或送单邮编、假日季节、出租车的清洁度或客户评分，是否会对小费预测有更大的贡献。

  1. 是否有变化的需求可以导致采取行动来改善业务或客户？

在出租车行程小费预测中，当前的预测是一个二进制值，即真或假。企业可能希望了解出租车清洁度或客户评分如何与无小费、小额小费、中等小费或大量小费相关联。出租车清洁度是司机可以用来提高服务质量的行为。

  1. 性能下降是否由模型执行或输入数据瓶颈引起？

可能随着输入数据集/数据源的增长且未优化，端到端的预测建模也会变慢。

为了捕捉模型的性能，应该记录实际预测或实际数据的合理表示的性能。以下是一个日志表应该看起来像的例子：

值数据类型注释
LogIDINT执行的顺序 ID。
创建时间DATETIME模型生成和测试的日期。
模型 IDINT每个模型的唯一标识符。
模型VARBINARY(MAX)这是模型的序列化表示。
RxFunctionVARCHAR(50)这是模型中使用的 rx 函数。
公式VARCHAR(1000)预测模型的公式。
训练输入查询VARCHAR(MAX)可重复生成的训练数据集
AUCFLOAT模型的 AUC 表示。这可以是任何其他可以用来比较模型质量的指标。
训练行数INT行数的数量。
CPU 时间INT生成模型所需的时间（秒数）。

一旦捕获了执行情况，就可以分析 AUC 值和 CPU 时间，如图 8.30 所示：

https://github.com/OpenDocCN/freelearn-ml-zh/raw/master/docs/sqlsvr17-ml-svc-r/img/00153.jpeg

图 8.30 监控模型在 AUC 和 CPU 时间上的比较

这些图表比较了以下模型的性能：

公式 B公式 C
rxDTree模型 ID 2模型 ID 3
rxLogit模型 ID 4模型 ID 5

描述如下：

  • 公式 B 是tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance + payment_type

  • 公式 C 是tipped ~ passenger_count + trip_distance + trip_time_in_secs + payment_type

每个模型都会与以下数据运行：

  • 最后 2 个月的数据

  • 随机选取的 5%数据

根据之前提到的比较，我们可以看到模型 ID 4，即使用公式 B 的rxLogit，具有最高的 AUC 范围和最低的 CPU 时间。因此，这个模型是两个中最好的。接下来需要决定这个模型是否应该替换生产中的模型。

现在你已经学会了比较模型的技术以及预测建模中一些重要的指标，你可以安排这种性能测试，类似于前面展示的。安排可以是 SQL 代理作业，如第七章中所述的“操作 R 代码”，在那里如果新的结果低于某个阈值，你可以收到警报。或者，你可以在 VSTS 中部署一个独立的 SQL Server 数据库单元项目，作为单独的部分来执行，以检查最新的交易数据。

有用参考资料

摘要

Visual Studio 2017 是一个强大的集成开发环境（IDE），数据科学家/开发者可以使用它来管理他们的代码、单元测试和版本控制。结合 Visual Studio Team Services，它们形成了一个完整的工具集，用于执行数据库生命周期管理，这也易于适应 DevOps 实践。本章详细介绍了如何在 SQL Server 数据库项目中、DevOps 实践中以及 CI/CD 工作流中集成 SQL Server 机器学习服务与 R 语言。最后，你也学习了如何监控预测模型随时间变化的准确性。

在下一章中，我们将讨论数据库管理员（DBAs）如何利用 R 语言的优势来利用机器学习服务。

posted on 2025-10-22 14:25  slgkaifa  阅读(7)  评论(0)    收藏  举报

导航