[SQL Server] R : introduction et utilisation

par Mohamed A. Cherif

Ce billet présente R, et tout particulièrement SQL Server R Services, une des nouvelles fonctionnalités apparues à partir de SQL Server 2016.

Avant de commencer

Depuis SQL Server 2016, et suite à l'acquisition en 2015 de Revolution Analytics, Microsoft a intégré une nouvelle 
fonctionnalité permettant à SQL Server de supporter nativement R et ses nombreux packages open source destinés 
à créer des modèles statistiques et générer des prévisions : SQL Server R Services, qui fait partie de la famille 
Microsoft R.
Nous allons d'abord présenter rapidement Microsoft R avant d'entrer dans le vif du sujet.

Présentation de R

R est un environnement et langage de programmation statistique. Très populaire dans le monde des 
statisticiens et data miners, il est supporté par la R Foundation for Statistical Computing, sous licence open-source 
GNU GPL.

Le schéma ci-dessous présente de manière simple le fonctionnement de Microsoft R :



L'architecture de Microsoft R est principalement constituée des composants suivants supportés par SQL Server :
  • Microsoft R Open : il s’agit d’une version évoluée et open-source de la distribution R de Microsoft. Elle inclut des améliorations liées au multithreading qui permettent de doper les performances de calculs statistiques (via la bibliothèque Math Kernel Library (MKL)). Utilisable aussi bien en environnement Windows que Linux, Microsoft R Open supporte également les packages type CRAN (Comprehensive R Archive Network), Github, Bioconductor.      
  • Microsoft R Server : il s’agit de la version serveur de la distribution R de Microsoft, principalement utilisée pour les analyses d’entreprise. Elle supporte toutes les analyses statistiques, l’exploration de données et l’analyse prédictive avec le Machine Learning. R Server est également entièrement compatible avec les référentiels de bibliothèques CRAN, Github ou Bioconductor, ainsi que les algorithmes ScaleR avec ses fonctions capables de réaliser des traitements parallèles de gros volumes de données (DistributedR). Grâce à R Server, toute application basée sur R pourra utiliser plusieurs plateformes (Windows, Linux, Hadoop, Spark,…) à l’aide de ConnectR.      
  • Microsoft R Database (SQL Server R Services) : il s’agit de la version base de données de la distribution R de Microsoft basée sur SQL Server. Elle couvre la plupart des algorithmes ScaleR, et la mémoire et le disque sont gérés SQL Server. Depuis SQL Server 2016, et suite à l’acquisition de Revolution Analytics, Microsoft a intégré une nouvelle fonctionnalité permettant à SQL Server de supporter nativement R et ses nombreux packages open source destinés à créer des modèles statistiques et générer des prévisions : SQL Server R Services. Il s’agit d’une installation de R qui fonctionne comme un service SQL Server et communique en toute sécurité avec SQL Server. Au cours du processus d’installation, les bibliothèques Microsoft Open et ScaleR sont installées sur SQL Server afin de favoriser l’intégration de données statistiques avec SQL Server et les outils de BI de Microsoft. Pour prendre en charge l’exécution R, un service SQL Server supplémentaire est installé localement : SQL Server Trusted Launchpad.       
  • Microsoft R Client : il s’agit d’une version gratuite de R construite sur Microsoft R Open. Supportant des packages CRAN, Github, Bioconductor et la puissante bibliothèque RevoScaleR qui assure la parallélisation et multithreading, elle permet à l’utilisateur d’exécuter des traitements haute performance sans avoir à installer localement un serveur Microsoft R. Sachant que la mémoire est limitée à celle de la machine locale, tout utilisateur a également la possibilité de lancer ses opérations de calculs statistiques vers Microsoft R Server ou toute autre plateforme surdimensionnée (Hadoop, SQL Server,…) pour obtenir de meilleures performances. 

Installation et configuration



Après clic sur New R Server (standalone) installation, il nous faudra procéder à la :

  1. Sélection les fonctionnalités associées.


  2. Confirmation de l’installation de Microsoft R Open.


Notez que :

Après installation de R, on peut jeter un coup d’oeil dans la liste des services Windows, et s’assurer de la présence (et du démarrage) de SQL Server Launchpad, qui permet de créer, coordonner et executer des plans parallèles.


En outre, afin de permettre l’exécution de script externes comme R, les instructions suivantes doivent être lancées sur SQL Server Management Studio (SSMS) :

EXEC sp_configure 'external scripts enabled',1
GO
RECONFIGURE
GO

Sitôt la commande lancée, SQL Server doit être redémarré.

Cela va permettre l’utilisation de la procédure stockée système sp_execute_external_script qui va permettre le lancement de scripts R.

Pour des informations supplémentaires relatives à l’upgrade et la post-installation de R, vous pouvez aller ici : https://msdn.microsoft.com/library/mt653951.aspx.

Utilisation de R

Pour lancer un script R, il faut faire usage de la procédure stockée système sp_execute_external_script dont la syntaxe est la suivante :

sp_execute_external_script   
    @language = N'language' ,   
    @script = N'script',  
  
    @input_data_1 = ] 'input_data_1'   
    [ , @input_data_1_name = ] N'input_data_1_name' ]   
    [ , @output_data_1_name = 'output_data_1_name' ]  
    [ , @parallel = 0 | 1 ]  
    [ , @params = ] N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]'  
    [ , @parameter1 = ] 'value1' [ OUT | OUTPUT ] [ ,...n ]  
    [ WITH <execute_option> ]  
[;]  
  
<execute_option>::=  
{  
      { RESULT SETS UNDEFINED }   
    | { RESULT SETS NONE }   
    | { RESULT SETS ( &lt;result_sets_definition&gt; ) }  
}  
  
<result_sets_definition> ::=   
{  
    (  
         { column_name   
           data_type   
         [ COLLATE collation_name ]   
         [ NULL | NOT NULL ] }  
         [,...n ]  
    )  
    | AS OBJECT   
        [ db_name . [ schema_name ] . | schema_name . ]   
        {table_name | view_name | table_valued_function_name }  
    | AS TYPE [ schema_name.]table_type_name  
}  

La procédure stockée sp_execute_external_script utilise les arguments suivants :
  • @language : permet de définir le nom du langage de programmation à executer (ici, le langage R).
  • @script : permet de déclarer le script R qui sera utilisé pour les operations de calculs et d’analyse.
  • @input_data_1 : permet de declarer le script T-SQL qui va retourner les données à traiter. L’argument @script utilisera ces données retournées en entrée pour les calculer et analyser.
  • @input_data_1_name : permet de définir le nom du jeu de résultats produit. La valeur par défaut est InputDataSet.
  • @output_data_1_name : permet de définir le nom de la variable qui contiendra les données en sortie. La valeur par défaut est OutputDataSet.
  • @parallel : permet d’activer (valeur 1) ou non (valeur 0, par défaut) l’exécution en mode parallèle. L’activation de ce paramètre est particulièrement utile lorsque la quantité de données à traiter est conséquente, et que RevoScaleR n’est pas utilisé.
  • @params : permet de déclarer les paramètres d’entrée.
  • @parameter1 : permet de spécifier une liste de valeurs pour les paramètres d’entrée.

En guise de test, nous allons procéder au calcul du coût réel médian d’un ensemble de transactions commerciales effectuées par une organisation, via l’usage de la fonction median de R. Les données sont récupérées à partir de la table TransactionHistory de la base de données AdventureWorks. Voici ce que cela donnerait :

 EXECUTE sp_execute_external_script
        @language = N'R'
       ,@script = N'	MedianCost <-median(InputCosts$ActualCost);
                        OutputCost <- data.frame(MedianCost);'

       ,@input_data_1=N'SELECT ActualCost
                        FROM AdventureWorks.Production.TransactionHistory;'

       ,@input_data_1_name=N'InputCosts'
	   ,@output_data_1_name=N'OutputCost'

WITH RESULT SETS ((
                   MedianCost FLOAT(20) NOT NULL
                  ));
GO

Sachant que la fonction data_frame permet de convertir le résultat en un data frame,
cela donnera le résultat suivant :


Pour de plus amples informations : https://msdn.microsoft.com/en-us/library/mt604368.aspx.

Pour aller plus loin…

Nous avons vu dans ce billet que R permettait de réaliser des traitements statistiques avec SQL Server. Avec le temps, et les évolutions futures de cette technologie, d’autres billets viendront enrichir la section dédiée au sein du blog.

Vous pouvez également jeter un coup d’œil au portail officiel de Microsoft R ici : https://mran.microsoft.com/. Et à la page officielle de SQL Server R Services là : https://msdn.microsoft.com/en-us/library/mt604845.aspx.

4 commentaires sur “[SQL Server] R : introduction et utilisation”

  1. Javier Recasens dit :
    octobre 24, 2017 à 17:23

    Given that Micrososoft R Client is free, can we use it in production?

    Réponse
    • Mohamed A. Cherif dit :
      octobre 25, 2017 à 21:45

      Hi Javier,

      Yes, we can use the client to work with Microsoft R Server in production.

      Cheers,
      M.

      Réponse
      • Javier Recasens dit :
        octobre 26, 2017 à 14:28

        I mean not with Microsoft R Server. I know there is a limit of 2 cores in R Client but I wonder if we can still use it in production with other open source software.

      • Mohamed A. Cherif dit :
        octobre 27, 2017 à 00:08

        Hi Javier,

        Yes, you can use Microsoft R Client in production without Microsoft R Server. You will just have some restrictions (including the thread limitations you mentioned).

        Because the client is simply based on R Open, you can even use this with other open source tools, such as RStudio.

        Cheers,
        M.

Laisser un commentaire