Modifier du code R/Python à exécuter dans des instances SQL Server (en base de données)

Article
05/30/2023

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Managed Instance

Cet article fournit des instructions générales sur la façon de modifier du code R ou Python pour qu’il s’exécute sous la forme d’une procédure stockée SQL Server afin d’améliorer les performances lors de l’accès à des données SQL.

Quand vous déplacez du code R/Python d’un environnement de développement intégré (IDE) local ou d’un autre environnement vers SQL Server, le code fonctionne généralement sans autre modification. C’est particulièrement vrai pour du code simple, comme une fonction qui accepte des entrées et retourne une valeur. Il est également plus facile de déplacer des solutions qui utilisent les packages RevoScaleR/revoscalepy qui prennent en charge l’exécution dans différents contextes d’exécution avec de légers changements. Notez que MicrosoftML s’applique à SQL Server 2016 (13.x), SQL Server 2017 (14.x) et SQL Server 2019 (15.x), et n’apparaît pas dans SQL Server 2022 (16.x).

Toutefois, votre code peut nécessiter des modifications substantielles si l’une des conditions suivantes s’applique :

Vous utilisez des bibliothèques qui accèdent au réseau ou qui ne peuvent pas être installées sur SQL Server.
Le code effectue des appels distincts à des sources de données en dehors de SQL Server, telles que des feuilles de calcul Excel, des fichiers sur des partages et d’autres bases de données.
Vous voulez paramétrer la procédure stockée et exécuter le code dans le paramètre @script de sp_execute_external_script.
Votre solution d’origine comprend plusieurs étapes qui peuvent être plus efficaces dans un environnement de production si elles sont exécutées indépendamment, par exemple la préparation des données ou l’ingénierie des fonctionnalités, ainsi que la formation du modèle, le scoring ou la création de rapports.
Vous souhaitez optimiser les performances en modifiant les bibliothèques, grâce à une exécution parallèle ou en déléguant une partie du traitement à SQL Server.

Étape 1. Planifiez les spécifications et les ressources

.

Déterminez les packages nécessaires et assurez-vous qu’ils fonctionnent sur SQL Server.
Installez les packages à l’avance, dans la bibliothèque de packages par défaut utilisée par Machine Learning Services. Les bibliothèques utilisateur ne sont pas prises en charge.

Sources de données

Si vous envisagez d’incorporer votre code dans sp_execute_external_script, identifiez les sources de données principales et secondaires.
- Les sources de données principales sont des jeux de données volumineux, tels que des données d’apprentissage de modèle ou des données d’entrée pour les prédictions. Prévoyez de mapper votre plus grand jeu de données au paramètre d’entrée de sp_execute_external_script.
- Les sources de données secondaires sont généralement des jeux de données plus petits, tels que des listes de facteurs ou des variables de regroupement supplémentaires.
Actuellement, sp_execute_external_script ne prend en charge qu’un seul jeu de données comme entrée de la procédure stockée. Toutefois, vous pouvez ajouter plusieurs entrées scalaires ou binaires.

Vous ne pouvez pas utiliser des appels de procédure stockée précédés de l’instruction EXECUTE comme entrée de sp_execute_external_script. Vous pouvez utiliser des requêtes, des affichages ou toute autre instruction SELECT valide.
Déterminez les sorties dont vous avez besoin. Si vous exécutez du code à l’aide de sp_execute_external_script, la procédure stockée peut générer une seule trame de données comme résultat. Toutefois, vous pouvez également générer plusieurs sorties scalaires, notamment des tracés et des modèles au format binaire, ainsi que d’autres valeurs scalaires dérivées du code ou des paramètres SQL.

Types de données

Pour explorer plus en détail les mappages de types de données entre R/Python et SQL Server, consultez les articles suivants :

Examinez les types de données utilisés dans votre code R/Python, puis effectuez les opérations suivantes :

Dressez une liste des problèmes possibles liés aux types des données.

Tous les types de données R/Python sont pris en charge par SQL Server Machine Learning Services. Toutefois, SQL Server prend en charge un plus grand nombre de types de données que R ou Python. Par conséquent, certaines conversions de types de données implicites sont effectuées lors du déplacement de données SQL Server vers et à partir de votre code. Vous devrez peut-être effectuer explicitement des opérations de cast ou de conversion sur certaines données.

Les valeurs NULL sont prises en charge. Néanmoins, R utilise la construction de données na pour représenter une valeur manquante, comme pour une valeur NULL.
Envisagez d’éliminer la dépendance vis-à-vis des données qui ne peuvent pas être utilisées par R : par exemple, les types de données rowid et GUID de SQL Server ne peuvent pas être consommés par R et génèrent des erreurs.

Étape 2. Convertir ou reconditionner du code

L’étendue de la modification de votre code varie selon que vous envisagez de soumettre le code à partir d’un client distant pour qu’il s’exécute dans le contexte de calcul SQL Server, ou de déployer le code dans le cadre d’une procédure stockée. Même si cette dernière opération demande plus, elle peut améliorer les performances et la sécurité des données.

Dans la mesure du possible, définissez vos données d’entrée principales en tant que requête SQL afin d’éviter le déplacement des données.

Lors de l’exécution de code dans une procédure stockée, vous pouvez passer par plusieurs entrées scalaires. Pour tous les paramètres que vous souhaitez utiliser dans la sortie, ajoutez le mot clé OUTPUT.

Par exemple, l’entrée scalaire suivante @model_name contient le nom du modèle, qui est également modifié ultérieurement par le script R et généré dans sa propre colonne dans les résultats :

-- declare a local scalar variable which will be passed into the R script
DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';

-- The below defines an OUTPUT variable in the scope of the R script, called model_name
-- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
-- of these parameters is very important. Mandatory parameters to sp_execute_external_script
-- must appear first, followed by the additional parameter definitions like @params, etc.
EXECUTE sp_execute_external_script @language = N'R', @script = N'
  model_name <- "Model name from R script"
  OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
  , @input_data_1 = N'SELECT 1 AS c1'
  , @params = N'@model_name nvarchar(50) OUTPUT'
  , @model_name = @local_model_name OUTPUT;

-- optionally, examine the new value for the local variable:
SELECT @local_model_name;

Toutes les variables que vous passez comme paramètres de la procédure stockée sp_execute_external_script doivent être mappées à des variables dans le code. Par défaut, les variables sont mappées par nom. Toutes les colonnes du jeu de données d’entrée doivent également être mappées à des variables dans le script.

Par exemple, supposez que votre script R contient une formule comme celle-ci :
```
formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
```
Une erreur se produit si le jeu de données d’entrée ne contient pas de colonnes avec les noms correspondants ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour et DayOfWeek.
Dans certains cas, un schéma de sortie doit être défini à l’avance pour les résultats.

Par exemple, pour insérer les données dans une table, vous devez utiliser la clause WITH RESULT SET pour spécifier le schéma.

Le schéma de sortie est également nécessaire si le script utilise l’argument @parallel=1. Cela s’explique par le fait que plusieurs processus peuvent être créés par SQL Server pour exécuter la requête en parallèle et collecter les résultats à la fin. Le schéma de sortie doit donc être préparé avant la création des processus parallèles.

Dans d’autres cas, vous pouvez omettre le schéma de résultat en utilisant l’option WITH RESULT SETS UNDEFINED. Cette instruction retourne le jeu de données à partir du script sans nommer les colonnes ni spécifier les types de données SQL.
Envisagez de générer des données de temporisation ou de suivi à l’aide de T-SQL plutôt que de R/Python.

Par exemple, vous pouvez passer l’heure système ou d’autres informations utilisées pour l’audit et le stockage en ajoutant un appel T-SQL qui est transmis aux résultats, plutôt que de générer des données similaires dans le script.

Améliorer les performances et la sécurité

Évitez d’écrire des prédictions ou des résultats intermédiaires dans un fichier. Écrivez plutôt les prédictions dans une table, afin d’éviter le déplacement de données.

Exécutez toutes les requêtes à l’avance, et passez en revue les plans de requête SQL Server pour identifier les tâches qui peuvent être effectuées en parallèle.

Si la requête d’entrée peut être parallélisée, définissez @parallel=1 dans les arguments de sp_execute_external_script.

Le traitement parallèle avec cet indicateur est généralement possible quand SQL Server peut utiliser des tables partitionnées ou distribuer une requête entre plusieurs processus et agréger les résultats à la fin. En revanche, il n’est généralement pas possible si vous entraînez des modèles à l’aide d’algorithmes qui nécessitent la lecture de toutes les données, ou si vous avez besoin de créer des agrégats.
Examinez votre code pour déterminer si certaines étapes peuvent être effectuées séparément, ou de manière plus efficace, à l’aide d’un appel de procédure stockée distinct. Par exemple, vous pouvez atteindre de meilleures performances en effectuant l’ingénierie de caractéristiques ou l’extraction de fonctionnalités séparément, et en enregistrant les valeurs dans une table.
Recherchez des moyens d’utiliser T-SQL plutôt que du code R/Python pour les calculs basés sur un jeu.

Par exemple, cette solution R montre comment les fonctions T-SQL définies par l’utilisateur et R peuvent effectuer la même tâche d’ingénierie des fonctionnalités : Procédure pas à pas pour une solution complète de science des données.
Consultez un développeur de base de données pour déterminer les moyens d’améliorer les performances à l’aide de fonctionnalités SQL Server comme les tables à mémoire optimisée ou, si vous disposez de l’édition Enterprise, Resource Governor.
Si vous utilisez R, remplacez, si possible, les fonctions R classiques par des fonctions RevoScaleR qui prennent en charge l’exécution distribuée. Pour plus d’informations, consultez Comparaison des fonctions R de base et RevoScaleR.

Étape 3. Préparer le déploiement

Prévenez l’administrateur afin que vous puissiez installer et tester les packages avant le déploiement de votre code.

Dans un environnement de développement, vous pouvez éventuellement installer les packages dans votre code, mais ce n’est pas une bonne pratique dans un environnement de production.

Les bibliothèques utilisateur ne sont pas prises en charge, que vous utilisiez une procédure stockée ou que vous exécutiez du code R/Python dans le contexte de calcul SQL Server.

Créer un package pour votre code R/Python dans une procédure stockée

Créez une fonction T-SQL définie par l’utilisateur, en incorporant votre code à l’aide de l’instruction sp-execute-external-script.
Si vous avez du code R complexe, utilisez le package R sqlrutils pour le convertir. Ce package est conçu pour aider les utilisateurs R expérimentés à écrire du code de procédure stockée efficace. Vous réécrivez votre code R sous la forme d’une fonction unique avec des entrées et des sorties clairement définies, puis vous utilisez le package sqlrutils pour générer l’entrée et les sorties au format approprié. Le package sqlrutils génère l’ensemble du code de la procédure stockée et peut également inscrire la procédure stockée dans la base de données.

Pour plus d’informations et d’exemples, consultez sqlrutils (SQL).

Intégrer à d’autres workflows

Tirez parti des outils T-SQL et des processus ETL. Procédez à l’ingénierie des fonctionnalités, à l’extraction des fonctionnalités et au nettoyage des données à l’avance dans le cadre des workflows de données.

Quand vous travaillez dans un environnement de développement dédié, vous pouvez extraire des données sur votre ordinateur, les analysez de manière itérative, puis écrivez ou affichez les résultats. Toutefois, quand du code autonome est migré vers SQL Server, vous pouvez en grande partie simplifier ce processus ou le déléguer à d’autres outils SQL Server.
Utilisez des stratégies de visualisation asynchrones et sécurisées.

Souvent, les utilisateurs de SQL Server ne peuvent pas accéder aux fichiers sur le serveur, et les outils clients SQL ne prennent généralement pas en charge les appareils graphiques R/Python. Si vous générez des tracés ou d’autres graphiques dans le cadre de la solution, envisagez d’exporter les tracés sous forme de données binaires et de les enregistrer dans une table, ou de les écrire.
Incluez les fonctions de prédiction et de scoring dans un wrapper dans des procédures stockées pour un accès direct par les applications.

Étapes suivantes

Pour voir des exemples de déploiement de solutions R et Python dans SQL Server, consultez les tutoriels suivants :

Tutoriels sur R

Développement SQL pour les scientifiques des données R