Appliquer une transformation SQL

Article
05/06/2019

Important

Le support de Machine Learning Studio (classique) prend fin le 31 août 2024. Nous vous recommandons de passer à Azure Machine Learning avant cette date.

À partir du 1er décembre 2021, vous ne pourrez plus créer de nouvelles ressources Machine Learning Studio (classique). Jusqu’au 31 août 2024, vous pouvez continuer à utiliser les ressources Machine Learning Studio (classique) existantes.

Consultez les informations sur le déplacement des projets de machine learning de ML Studio (classique) à Azure Machine Learning.
En savoir plus sur Azure Machine Learning.

La documentation ML Studio (classique) est en cours de retrait et ne sera probablement plus mise à jour.

Exécute une requête SQLite sur des jeux de données d'entrée pour transformer les données

Catégorie : transformation/manipulation des données

Notes

s’applique à: Machine Learning Studio (classic) uniquement

Des modules par glisser-déposer similaires sont disponibles dans Concepteur Azure Machine Learning.

Vue d’ensemble du module

cet article explique comment utiliser le module appliquer SQL Transformation dans Machine Learning Studio (classic) pour spécifier une requête SQL sur un jeu de données d’entrée ou des jeux de données.

SQL est pratique lorsque vous devez modifier vos données de manière complexe ou conserver les données pour les utiliser dans d’autres environnements. par exemple, à l’aide du module appliquer SQL Transformation , vous pouvez effectuer les opérations suivantes :

Créer des tables pour les résultats et enregistrer les jeux de données dans une base de données portable.
Effectuer des transformations personnalisées sur des types de données ou créer des agrégats.
Exécuter des instructions de requête SQL pour filtrer ou modifier des données, et retourner les résultats de requête dans une table de données.

Important

Le moteur SQL utilisé dans ce module est SQLite. Si vous n’êtes pas familiarisé avec la syntaxe SQLite, veillez à lire la section syntaxe et utilisation de cet article pour obtenir des exemples.

Qu’est-ce que SQLite ?

SQLite est un système de gestion de base de données relationnelle de domaine public qui est contenu dans une bibliothèque de programmation en C. SQLite est couramment utilisé en tant que base de données incorporée pour le stockage local dans les navigateurs web.

SQLite a été conçu initialement en 2000 pour la U.S. Navy, afin de prendre en charge des transactions sans serveur. Il s'agit d'un moteur de base de données autonome dépourvu de tout système de gestion, qui ne nécessite donc aucune configuration ou administration.

Comment configurer le module Appliquer une transformation SQL

Le module peut utiliser jusqu’à trois jeux de données comme entrées. Quand vous référencez les jeux de données connectés à chaque port d’entrée, vous devez utiliser les noms t1, t2 et t3. Le numéro de table indique l’index du port d’entrée.

Le paramètre restant est une requête SQL, qui utilise la syntaxe SQLite. Ce module prend en charge toutes les instructions standard de la syntaxe SQLite. Pour obtenir la liste des instructions non prises en charge, consultez la section Notes techniques.

Syntaxe générale et utilisation

Quand vous tapez plusieurs lignes dans la zone de texte Script SQL, utilisez un point-virgule pour terminer chaque instruction. Sinon, les sauts de ligne sont convertis en espaces.

Par exemple, les instructions suivantes sont équivalentes :
```
SELECT   
*   
from   
t1;  
```
```
SELECT * from t1;  
```
Vous pouvez ajouter des commentaires à l’aide d’un -- au début de chaque ligne, ou en plaçant du texte à l’aide /* */ de.

Par exemple, l'instruction suivante est valide :
```
SELECT * from t1  
/*WHERE ItemID BETWEEN 1 AND 100*/;  
```
si un nom de colonne duplique le nom d’un mot clé réservé, la mise en surbrillance de la syntaxe est appliquée au texte figurant dans la zone de texte SQL Script . pour éviter toute confusion, vous devez placer les noms de colonnes entre crochets (pour respecter la convention de SQL) ou les repères ou guillemets doubles (convention de SQL ANSI).

Par exemple, dans la requête suivante sur le jeu de données sang don, Time est un nom de colonne valide, mais est également un mot clé réservé.
```
SELECT Recency, Frequency, Monetary, Time, Class  
FROM t1  
WHERE Time between 3 and 20;  
```
Si vous exécutez la requête telle quelle, la requête peut retourner les résultats corrects, mais selon le DataSet, elle peut retourner une erreur. Voici quelques exemples de la façon d’éviter ce problème :
```
-- Transact-SQL  
SELECT [Recency], [Frequency], [Monetary], [Time], [Class]  
FROM t1  
WHERE [Time] between 3 and 20;  
-- ANSI SQL  
SELECT "Recency", "Frequency", "Monetary", "Time", "Class"  
FROM t1  
WHERE `Time` between 3 and 20;  
```
Notes

La mise en surbrillance de la syntaxe reste sur le mot clé même après qu’elle est placée entre guillemets ou crochets.
SQLite ne respecte pas la casse, à l’exception de quelques commandes qui ont des variantes sensibles à la casse avec des significations différentes (glob et glob).

Instruction SELECT

Dans l’instruction, les noms de colonnes qui incluent des espaces ou d’autres caractères interdits dans les SELECT identificateurs doivent être placés entre guillemets doubles, crochets ou caractères de soulignement (').

Par exemple, cette requête fait référence au jeu de données Two-Class Iris sur t1 , mais un nom de colonne contient un caractère interdit, donc le nom de la colonne est placé entre guillemets.

SELECT class, "sepal-length" FROM t1;

Vous pouvez ajouter une WHERE clause pour filtrer les valeurs dans le jeu de données.

SELECT class, "sepal-length" FROM t1 WHERE "sepal-length" >5.0;

La syntaxe SQLite ne prend pas en charge le TOP mot clé, qui est utilisé dans Transact-SQL. Au lieu de cela, vous pouvez utiliser le LIMIT mot clé ou une FETCH instruction.

Par exemple, Comparez ces requêtes sur le jeu de données de location de vélos.

-- unsupported in SQLite  
SELECT  TOP 100 [dteday] FROM t1 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100   
SELECT  [dteday] FROM t1 LIMIT 100 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100. Note that FETCH is on a new line.  
SELECT  [dteday] FROM t1 - ;  
FETCH FIRST 100 rows ONLY;  
ORDER BY [dteday] DESC;

Jointures

Les exemples suivants utilisent le jeu de données Restaurant Ratings sur le port d'entrée correspondant à t1, et le jeu de données Restaurant Features sur le port d'entrée correspondant à t2.

L'instruction suivante joint les deux tables afin de créer un jeu de données combinant les caractéristiques spécifiées des restaurants avec des évaluations moyennes pour chacun d'eux.

SELECT DISTINCT(t2.placeid),    
t2.name, t2.city, t2.state, t2.price, t2.alcohol,  
AVG(rating)  AS 'AvgRating'   
FROM t1   
JOIN t2  
ON t1.placeID = t2.placeID  
GROUP BY t2.placeid;

Fonctions d'agrégation

cette section fournit des exemples de base de certaines fonctions d’agrégation de SQL courantes, à l’aide de SQLite.

Les fonctions d’agrégation actuellement prises en charge sont : AVG , COUNT , MINMAX ,, SUM , TOTAL .

La requête suivante renvoie un jeu de données contenant l'ID du restaurant, ainsi que l'évaluation moyenne de celui-ci.

SELECT DISTINCT placeid,  
AVG(rating) AS ‘AvgRating’,  
FROM t1  
GROUP BY placeid

Utilisation des chaînes

SQLite prend en charge l'opérateur de canal double pour la concaténation des chaînes.

L'instruction suivante crée une colonne en concaténant deux colonnes de texte.

SELECT placeID, name,   
(city || '-' || state) AS 'Target Region',   
FROM t1

Avertissement

l’opérateur de concaténation de chaînes Transact-SQL n’est pas pris en charge : + (concaténation de chaînes). Par exemple, l'expression ('city + '-' + state) AS 'Target Region' dans l'exemple de requête renvoie 0 pour toutes les valeurs.

Toutefois, même si l’opérateur n’est pas pris en charge pour ce type de données, aucune erreur n’est générée dans Machine Learning. Veillez à vérifier les résultats du module Apply SQL Transformation avant d'utiliser le jeu de données résultant d'une expérience.

COALESCE et CASE

COALESCE évalue plusieurs arguments, dans l’ordre, et retourne la valeur de la première expression qui ne prend pas la valeur NULL.

Par exemple, cette requête sur le jeu de données Steel Annealing Multi-Class renvoie le premier indicateur non null d'une liste de colonnes supposées avoir des valeurs s'excluant mutuellement. Si aucun indicateur n'est trouvé, la chaîne « none » est renvoyée.

SELECT classes, family, [product-type],  
COALESCE(bt,bc,bf,[bw/me],bl, "none") AS TemperType  
FROM t1;

L' CASE instruction est utile pour tester des valeurs et retourner une nouvelle valeur en fonction des résultats évalués. SQLite prend en charge la syntaxe suivante pour CASE les instructions :

CASE WHEN [condition] THEN [expression] ELSE [expression] END
CASE [expression] WHEN [valeur] THEN [expression] ELSE [expression] END

Par exemple, supposons que vous ayez utilisé précédemment le module convertir en valeurs d’indicateur pour créer des colonnes de fonctionnalités définies contenant des valeurs true-false. La requête suivante réduit les valeurs de plusieurs colonnes de fonctionnalité en une seule colonne à valeurs multiples.

SELECT userID, [smoker-0], [smoker-1],  
CASE  
WHEN [smoker-0]= '1' THEN 'smoker'   
WHEN [smoker-1]= '1' THEN 'nonsmoker'   
ELSE 'unknown'  
END AS newLabel  
FROM t1;

Exemples

Pour obtenir un exemple de la façon dont ce module peut être utilisé dans Machine Learning expériences, consultez cet exemple dans le Azure ai Gallery:

appliquer SQL Transformation: utilise les jeux de données des évaluations de restaurant, du restaurant et des clients de restaurant pour illustrer des jointures simples, des instructions select et des fonctions d’agrégation.

Notes techniques

Cette section contient des détails, des conseils et des réponses aux questions fréquentes concernant l’implémentation.

Une entrée est toujours obligatoire sur le port 1.
Si le jeu de données d'entrée comprend des noms de colonne, les colonnes du jeu de données de sortie utilisent les noms de colonne du jeu de données d'entrée.

Si le jeu de données d’entrée n’a pas de noms de colonnes, les noms de colonnes de la table sont créés automatiquement à l’aide de la Convention d’affectation de noms suivante : T1COL1, T1COL2, T1COL3, etc., où les nombres indiquent l’index de chaque colonne dans le jeu de données d’entrée.
Placez toujours les identificateurs de colonne qui contiennent un espace ou un autre caractère spécial entre crochets ou guillemets doubles quand vous référencez la colonne dans les clauses SELECT ou WHERE.

Instructions non prises en charge

Bien que SQLite prenne en charge la plupart des normes ANSI SQL, il n’inclut pas de nombreuses fonctionnalités prises en charge par les systèmes de base de données relationnelle du commerce. Pour plus d’informations, consultez SQL as Understood by SQLite. Par ailleurs, gardez à l’esprit les restrictions suivantes quand vous créez des instructions SQL :

SQLite utilise le typage dynamique des valeurs, au lieu d’attribuer un type à une colonne comme dans la plupart des systèmes de base de données relationnelle. Il est faiblement typé et autorise la conversion de type implicite.
LEFT OUTER JOIN est implémenté, mais pas RIGHT OUTER JOIN ni FULL OUTER JOIN.
Vous pouvez utiliser les instructions RENAME TABLE et ADD COLUMN avec la commande ALTER TABLE, mais les autres clauses ne sont pas prises en charge, notamment DROP COLUMN, ALTER COLUMN et ADD CONSTRAINT.
Vous pouvez créer une vue dans SQLite, mais les vues suivantes sont en lecture seule. Vous ne pouvez pas exécuter d’instruction DELETE, INSERTou UPDATE sur une vue. Toutefois, vous pouvez créer un déclencheur qui se déclenche à chaque tentative d’exécution de DELETE, INSERT ou UPDATE sur une vue, et exécuter d’autres opérations dans le corps du déclencheur.

En plus de la liste des fonctions non prises en charge fournie sur le site officiel de SQLite, le wiki suivant donne une liste d’autres fonctionnalités non prises en charge : SQLite - Unsupported SQL

Entrées attendues

Nom	Type	Description
Table1	Table de données	Dataset1 d'entrée
Tableau2	Table de données	Dataset2 d'entrée
Table3	Table de données	Dataset3 d'entrée

Paramètres du module

Nom	Plage	Type	Default	Description
Script de requête SQL	n'importe laquelle	StreamReader		Instruction de requête SQL

Sorties

Nom	Type	Description
Jeu de données de résultats	Table de données	Jeu de données de sortie

Exceptions

Exception	Description
Erreur 0001	Une exception se produit si une ou plusieurs colonnes spécifiées du jeu de données sont introuvables.
Erreur 0003	Une exception se produit si un ou plusieurs jeux de données d'entrée ont la valeur Null ou sont vides.
Erreur 0069	SQL une erreur logique ou une base de données manquante

pour obtenir la liste des erreurs spécifiques aux modules Studio (classiques), consultez Machine Learning codes d’erreur.

pour obtenir la liste des exceptions d’api, consultez Machine Learning les Codes d’erreur de l' api REST.

Voir aussi

Manipulation
Transformation des données
Liste alphabétique des modules

Partager via