Top

SQL Server Machine Learning Services & Wine Quality

 

English Version : https://www.linkedin.com/pulse/sql-server-machine-learning-services-wine-quality-romain-casteres/

 

Dans cet article je vais présenter SQL Server Machine Learning Services, nous verrons les avantages que celui-ci apporte au sein des bases de données. Nous verrons ensuite à travers un problème de classification les différentes façons d’exécuter du code R ou Python. Enfin, j’ajouterai quelques bonnes pratiques quant à l’utilisation de cette fonctionnalité.

Remarque : N’étant pas Data Scientist, cet article n’est en rien un tutoriel sur le langage R mais plutôt une présentation de ce qu’il est possible de faire avec SQL Server ML Services.

 

1 – Introduction

Dans SQL Server 2016, il est possible d’installer R Services, un module complémentaire à une instance de moteur de base de données utilisé pour l’exécution de code R et des fonctions sur SQL Server.

Dans SQL Server 2017, le R Services a été renommé en SQL Server Machine Learning Services, reflétant l’ajout du langage Python.

Dans SQL Server 2019 (actuellement en Preview), le langage Java est ajouté aux langages R et Python ainsi que le support sur Linux et de la haute disponibilité (FCI), …

Et ce n’est surement pas au hasard que ces trois langages ont été choisis :

MostPopularLanguages

Source : https://insights.stackoverflow.com/survey/2017#technologies-and-occupations

Une tendance dans les architectures actuelles est d’apporter l’intelligence au plus près des données (IoT Edge, …) :

BringingIntelligenceToWhereDataLives

En exécutant un langage de script approuvé au sein d’une infrastructure sécurisée gérée par SQL Server, les administrateurs de base de données peuvent maintenir la sécurité tout en permettant aux scientifiques d’accéder aux données d’entreprise et d’utiliser les ressources des serveurs plutôt que celles de leurs postes de travail.

AI_In_Database

Le diagramme suivant décrit visuellement les opportunités et les avantages d’une telle architecture :

AI_Database

Exemple de retour client : https://customers.microsoft.com/en-us/story/acxiom-corporation

 

2 – Démonstrations

Afin de présenter SQL Server Machine Learning Services, prenons une problématique bien réelle : Comment prédire la qualité d’un vin sans le goûter !

Pour cela, je vais reprendre les sources de données d’un article que j’avais rédigé en 2014 sur le service Azure Machine Learning : « Predict Wine Quality With Azure ML ». Contrairement à Azure ML qui propose un outil visuel dans le Cloud pour entrainer, créer et déployer un modèle, nous travaillerons dans cet article On Premise dans SQL Server et en mode « Code First ».

MS_AI_Portofolio

Rappel du jeu de données :

  • Il a été recueilli par Paulo Cortez, Antonio Cerdeira, Fernando Almeida, Telmo Matos et Jose Reis en 2009 pour la rédaction du livre : Modeling wine preferences by data mining from physicochemical properties.
  • Les échantillons proviennent de vins blancs et rouges du Portugal : Vinho Verde.
  • Les données sont publiques, vous pourrez donc reproduire mes expérimentations : http://www3.dsi.uminho.pt/pcortez/wine/winequality.zip.

Voici à quoi ressemblent les données :

WineData

L’output dans notre exemple sera la qualité. Cette dernière est basée sur des données sensorielles médianes d’au moins 3 évaluations faites par des experts en vin. Chaque expert a classé la qualité du vin entre 0 (très mauvais) et 10 (excellent). Le nombre de vins testés est de 1599 pour les rouges et de 4898 pour les blancs.

Avant de commencer mon expérimentation, revenons sur ce que nous souhaitons faire et obtenir :

WineClassification

2.1 – Architecture 1 : R CRAN

Les données sur les vins blancs ayant été chargées en base de données, je peux commencer à les analyser. Voici un exemple de code R exécuté depuis Visual Studio à partir de la version Open Source de R CRAN (3.5.1) :

library(RODBC)   # install.packages("RODBC")
library(ggplot2) # install.packages("ggplot2")

# Connection
con <- "Driver=SQL Server;Server=MININT-BHOAV8H;Database=WineQuality;Trusted_Connection=yes"
ch <- odbcDriverConnect(connection = con)

# Requete SQL
source.query <- paste("SELECT fixed_acidity, volatile_acidity, citric_acid, residual_sugar, chlorides, free_sulfur_dioxide, total_sulfur_dioxide, density, pH, sulphates, alcohol, quality FROM [dbo].[white];")
wine <- sqlQuery(ch, source.query)

# Affichage des premières lignes 
head(wine)

# Description des données
str(wine)

# Graphiques
ggplot(wine, aes(wine$alcohol, wine$pH)) + geom_point(color = "red", size = 0.8) + xlab("Alcohol") + ylab("pH") + ggtitle("Wine Quality")
ggplot(wine, aes(x = quality, fill = quality)) + geom_bar(stat = "count") + geom_text(position = "stack", stat = 'count', aes(label = ..count..), vjust = -0.5) + labs(y = "Num of Observations", x = "Wine Quality") + theme(legend.position = "none")

Architecture1 - RCRAN

 

Architecture 1 :

Architecture1

Voyez-vous le problème ?

Depuis mon poste de développeur où j’ai installé R CRAN Open Source, je viens d’exécuter une requête sur ma base de données et je viens de rapatrier toutes les colonnes et lignes de ma table « white ».

RCRAN_RServer

Avec cette architecture, toutes les données sont transférées au client et traitées par un seul Thread. Voyons donc comment analyser autrement via SQL Server ML Service.

 

2.2 – Architecture 2 : SQL Server Compute Context

Toujours depuis votre éditeur préféré, en se connectant non plus au R CRAN mais au Microsoft ML Server installé lors de l’installation de SQL Server :

VisualStudio_R

Exécution du script R suivant :

library(RODBC) # install.packages("RODBC")
library(RevoTreeView) # install.packages("RevoTreeView")

con <- "Driver=SQL Server;Server=MININT-BHOAV8H;Database=WineQuality;Trusted_Connection=yes"

sqlCompute <- RxInSqlServer(connectionString = con, wait = T, numTasks = 5, consoleOutput = F)

rxSetComputeContext(sqlCompute)
# rxSetComputeContext("local")  # if debugging in local !

train <- RxSqlServerData(
  connectionString = con,
  databaseName = "WineQuality",
  sqlQuery = "SELECT * FROM [dbo].[white]",
  rowsPerRead = 10000,
  stringsAsFactors = T)

# Récupération et affichage des données en local
test <- rxGetInfo(data = train, numRows = 10)
test$data

model <- rxDTree(
  quality ~ density + pH + sulphates + alcohol,
  data = train,
  cp = 0.01)

# Analyse de l'arbre de décision
plot(createTreeView(model))

Résultat :

RevoTreeView

 

Remarque : Ici les données ne sont pas transférées via le réseau et le code R est exécuté en parallèle côté serveur (au plus proche des données stockées dans SQL Server).

 

Architecture 2 :

Architecture2

 

2.3 – Architecture 3 : Procédures stockées

Afin d’exécuter du script R en T-SQL, il faut au préalable activer l’exécution de script externe et vérifier que le service SQL Server Launchpad est démarré (Prérequis) :

EXEC sp_configure  'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

Maintenant rappelons les bases, les principales étapes quant à la construction d’un modèle prédictif :

AI_Project

 

0. Définition de l’objectif : À partir des caractéristiques d’un vin, nous souhaitons savoir s’il est bon ou mauvais -> Classification binaire.

1. Accéder et comprendre la donnée :

CREATE OR ALTER PROC dbo.[1_Data]
AS BEGIN
SET NOCOUNT ON;
	SELECT id, fixed_acidity, volatile_acidity, citric_acid, residual_sugar, chlorides, free_sulfur_dioxide,
       total_sulfur_dioxide, density, pH, sulphates, alcohol, quality 
	FROM dbo.[white];
END;

EXEC dbo.[1_Data];

Résultat :

SQLWineData

2. Prétraiter et enrichir l’information :

CREATE OR ALTER PROC dbo.[2_Data_Normalized]
AS BEGIN
SET NOCOUNT ON;
	DROP TABLE IF EXISTS dbo.[White_Normalized];
	
	SELECT id, fixed_acidity, volatile_acidity, citric_acid, residual_sugar, chlorides, free_sulfur_dioxide,
       total_sulfur_dioxide, density, pH, sulphates, alcohol, IIF(quality<=5,0,1) AS quality
	INTO dbo.[White_Normalized]
	FROM dbo.[White];
	
	SELECT id, fixed_acidity, volatile_acidity, citric_acid, residual_sugar, chlorides, free_sulfur_dioxide,
       total_sulfur_dioxide, density, pH, sulphates, alcohol, quality 
	FROM dbo.[White_Normalized];
END;

EXEC dbo.[2_Data_Normalized];

Résultat : La colonne « quality » est dorénavant binaire, 0 si le vin est de mauvaise qualité et 1 si le vin est bon.

SQLWineData2

3. Modéliser les données :

CREATE OR ALTER PROCEDURE dbo.[3_Data_Features] AS  
BEGIN  
	SET NOCOUNT ON;
	EXECUTE sp_execute_external_script  
	@language = N'R',
	@script = N'  
	library("reshape2")
	library("ggplot2")

	mainDir <- ''C:\\temp''  
	dir.create(mainDir, recursive = TRUE, showWarnings = FALSE)  
	setwd(mainDir);  

	dest_filename = file.path(mainDir, ''wine_features.jpg'')  
	jpeg(filename=dest_filename, height = 768, width = 1024, res=150); 

	numeric_cols <- sapply(wine, is.numeric)
	wine.lng <- melt(wine[,numeric_cols], id="quality")

	print(ggplot(aes(x=value, group=quality, fill=factor(quality)), data=wine.lng) + geom_density(alpha = 0.2) + facet_wrap(~variable, scales="free")) + theme_classic()

	dev.off()',  
	@input_data_1 = N'SELECT * FROM dbo.[White_Normalized]',
	@input_data_1_name = N'wine'; 
END;

EXEC dbo.[3_Data_Features];

Résultat : la procédure stockée exporte dans un fichier image l’importance des Features pour prédire la qualité du vin. Une courbe superposée ne nous aidera pas à qualifier un vin, il en ressort donc les Features suivantes : citric_acid + total_sulfur_dioxide + density + pH + sulphates + alcohol que j’utiliserai donc pour entrainer mon modèle.

FeaturesAnalysis

4. Diviser les données (Split) :

CREATE OR ALTER PROCEDURE dbo.[4_Split]
AS  
BEGIN  
  SET NOCOUNT ON;  
  -- 75 % Training 
  DROP TABLE IF EXISTS [dbo].[White_Train];
  SELECT * INTO [dbo].[White_Train] 
  FROM (SELECT * FROM [dbo].[White_Normalized] WHERE (ABS(CAST((BINARY_CHECKSUM(id, NEWID())) as int)) % 100) < 75) a;
  -- 25 % Test 
  DROP TABLE IF EXISTS [dbo].[White_Test];
  SELECT * INTO [dbo].[White_Test] 
  FROM (SELECT * FROM [dbo].[White_Normalized] WHERE id NOT IN (SELECT id FROM [dbo].[White_Train])) a;
  SELECT * FROM dbo.White_Train;
  SELECT * FROM dbo.White_Test;
END;

EXEC dbo.[4_Split]

Résultat : Sur les 4898 lignes initiales, la table « White_Train » en a 3675 (environ 75 %) et la table « White_Test » en a 1223 (environ 25 %).

5. Entrainement :

CREATE OR ALTER PROCEDURE dbo.[5_Build_Model]
AS BEGIN  
	DECLARE @inquery nvarchar(max) = N'SELECT * FROM [dbo].[White_Train]';
 
	IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'White_Models') AND type = N'U')
	BEGIN
		CREATE TABLE [dbo].[White_Models] ([Model] [varbinary](max) NOT NULL, [Date] [DATETIME2](7) NULL);
		ALTER TABLE [dbo].[White_Models] ADD CONSTRAINT [DF_Wine_Models_Date] DEFAULT (GETDATE()) FOR [Date];
	END 
	-- DROP TABLE IF EXISTS [dbo].[White_Models];
  
	INSERT INTO [dbo].[White_Models] (Model)
	EXEC sp_execute_external_script 
	@language = N'R',  
	@script = N'  
	randomForestObj <- rxDForest(quality ~  citric_acid + total_sulfur_dioxide + density + pH + sulphates + alcohol, InputDataSet)
	model <- data.frame(payload = as.raw(serialize(randomForestObj, connection=NULL)))
	',
	@input_data_1 = @inquery,  
	@output_data_1_name = N'model'; 
  
	SELECT * FROM [dbo].[White_Models];
END;

EXEC dbo.[5_Build_Model];

Résultat :
– Entrainement d’arbres de décisions pour prédire la qualité en fonction des Features choisies à partir du jeu de données d’entrainement « White_Train ».
– Sauvegarde du modèle dans une nouvelle table « White_Models » avec l’heure de génération de celui-ci.

WineBuildModel

6. Noter le modèle (Scoring) :

CREATE OR ALTER PROCEDURE dbo.[6_Score_Model] AS 
BEGIN  
	DECLARE @inquery nvarchar(max) = N'SELECT * FROM [dbo].[White_Test]'; 
	DECLARE @model varbinary(max) = (SELECT TOP(1) Model FROM dbo.White_Models ORDER BY DATE DESC);
  
	DROP TABLE IF EXISTS [dbo].[White_Predictions];
	CREATE TABLE [dbo].[White_Predictions] ([Prediction] [float] NULL, [id] [int] NULL);

	INSERT INTO [dbo].[White_Predictions]
	EXEC sp_execute_external_script 
	@language = N'R',
	@script = N'  
		rfModel <- unserialize(as.raw(model));  
		OutputDataSet<-rxPredict(rfModel, data = InputDataSet, extraVarsToWrite = c("id"))
	',
	@input_data_1 = @inquery,
	@params = N'@model varbinary(max)',
	@model = @model;
  
	SELECT * FROM [dbo].[White_Predictions];
END;

EXEC dbo.[6_Score_Model];

Résultat : sauvegarde des prédictions dans la table « White_Predictions » sur le jeu de données de test « White_Test » (1223 lignes).

WineScoring

7. Évaluer le modèle :

CREATE OR ALTER PROCEDURE dbo.[7_Evaluate_Model] AS  
BEGIN  
	EXEC sp_execute_external_script
	@language = N'R',
	@script = N'  
	suppressMessages(library("ROCR"))
  
	mainDir <- ''C:\\temp''  
	dir.create(mainDir, recursive = TRUE, showWarnings = FALSE)  
	setwd(mainDir);  
  
	dest_filename = file.path(mainDir, ''ROC.jpg'')  
	jpeg(filename=dest_filename, height=1800, width = 1800, res = 300); 
  
	pred <- prediction(White_Predictions$Prediction, White_Predictions$Quality)
	perf <- performance(pred,"tpr","fpr")
	plot(perf)
	abline(a=0,b=1)
	dev.off()
  
	auc <- performance(pred, "auc")
	print(paste0("Area under ROC Curve : ", as.numeric(auc@y.values)))
	',  
	@input_data_1 = N'SELECT b.Prediction, a.Quality FROM [dbo].[White_Test] a INNER JOIN [dbo].[White_Predictions] b ON a.id = b.id',  
	@input_data_1_name = N'White_Predictions';

	SELECT 
		b.Prediction,
		a.Quality
	FROM 
		[dbo].[White_Test] a 
		INNER JOIN [dbo].[White_Predictions] b ON a.id = b.id
	ORDER BY b.Prediction DESC;
END;

EXEC dbo.[7_Evaluate_Model];

Résultat : Export d’une courbe de ROC :

WineROC

8. Prédiction à la volée :

CREATE OR ALTER PROCEDURE dbo.[8_Prediction]
  @citric_acid float, @total_sulfur_dioxide float, @density money, @pH float, @sulphates float, @alcohol float
AS BEGIN
	DECLARE @model varbinary(max) = (SELECT TOP 1 Model FROM [dbo].[White_Models]);

	EXEC sp_execute_external_script
		@language = N'R',
		@script = N'df <- InputDataSet
					model <- unserialize(model)
					df$Prediction <- rxPredict(model, data = df)
					df$Prediction <- as.numeric(df$Prediction)
					OutputDataSet <- df',
		@input_data_1 = N'SELECT @citric_acid AS citric_acid, @total_sulfur_dioxide AS total_sulfur_dioxide, @density AS density, @pH AS pH, @sulphates AS sulphates, @alcohol AS alcohol;',
		@params = N'@model varbinary(max), @citric_acid float, @total_sulfur_dioxide float, @density money, @pH float, @sulphates float, @alcohol float',
		@model = @model,
		@citric_acid = @citric_acid,
		@total_sulfur_dioxide = @total_sulfur_dioxide,
		@density = @density,
		@pH = @pH,
		@sulphates = @sulphates,
		@alcohol = @alcohol
	WITH RESULT SETS ((citric_acid float, total_sulfur_dioxide float, density money, pH float, sulphates float, alcohol float, PredictedWhiteProb float));
END;

DECLARE	@return_value int;
EXEC @return_value = dbo.[8_Prediction]
  @citric_acid = 0.45,
  @total_sulfur_dioxide = 124,
  @density = 0.997,
  @pH = 3.2,
  @sulphates = 0.46,
  @alcohol = 12;

Résultat : Test de deux vins, le premier semble meilleur (l’alcool en serait-il pour quelque chose :-p)

WinePredict

 

Architecture 3 :

Architecture3

 

3 – Conclusion

Nous avons vu dans cet article différents moyens d’exécuter du R via SQL Server ML Services :
– Depuis un IDE R en changeant de contexte.
– Depuis des procédures T-SQL.

Bien entendu mon modèle pourrait être amélioré (Choix des Features, de l’algorithme d’entrainement … ) mais le but de l’article est de présenter l’usage de SQL Server ML Services et les gains que celui-ci peut offrir dans vos architectures. Vous l’aurez surement remarqué, mon modèle étant sauvegardé dans une table, je peux garder différentes versions de celui-ci, il sera sauvegardé lors de la sauvegarde des mes bases de données …

Pour exécuter du Python ça n’est pas plus compliqué : https://docs.microsoft.com/fr-fr/sql/advanced-analytics/tutorials/demo-data-iris-in-sql?view=sql-server-2017

Voici quelques bonnes pratiques quant à l’utilisation de SQL Server ML Services :
– Effectuer les transformations de données en T-SQL si cela est réalisable plutôt qu’en R ou Python.
– Sauvegarder les modèles en base de données.
– Utiliser des fonctionnalités comme le In Memory, Columnstore Index afin d’améliorer les performances.
– Configurer un pool de ressources pour gérer les ressources externes :
o Create an external resource pool.
o How to create a resource pool for machine learning in SQL Server : https://docs.microsoft.com/en-us/sql/advanced-analytics/administration/how-to-create-a-resource-pool?view=sql-server-2017
– Adapter le montant de la mémoire alloué à l’instance : Server memory configuration options.
– Changer le nombre de compte R créé lors de l’installation (20 par défaut) : Modify the user account pool for machine learning.
– Monitorer l’utilisation via des rapports SSRS : https://docs.microsoft.com/en-us/sql/advanced-analytics/r/monitor-r-services-using-custom-reports-in-management-studio?view=sql-server-2017
– Installer de nouvelles librairies R, depuis RGui.exe en mode admin (en adaptant le Path) :

lib.SQL <- "C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\R_SERVICES\\library"
install.packages("rjson", lib = lib.SQL)

 

Vous trouverez à l’adresse suivant un backup de la base de données ainsi que le projet Visual Studio et les scripts TSQL : https://1drv.ms/f/s!AuHo6XX5MiYSlNojjLWmZGhxBKhEsg

 

Ajouter un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *


Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

Suivre

Recevez par courrier les nouveaux articles.

Joignez-vous aux followers:

%d blogueurs aiment cette page :