Anwenden der SQL-Transformation

Artikel
05/06/2019

Wichtig

Der Support für Machine Learning Studio (klassisch) endet am 31. August 2024. Es wird empfohlen, bis zu diesem Datum auf Azure Machine Learning umzustellen.

Ab dem 1. Dezember 2021 können Sie keine neuen Ressourcen in Machine Learning Studio (klassisch) mehr erstellen. Bis zum 31. August 2024 können Sie die vorhandenen Ressourcen in Machine Learning Studio (klassisch) weiterhin verwenden.

Siehe Migrieren zu Azure Machine Learning
Weitere Informationen zu Azure Machine Learning.

Die Dokumentation zu ML Studio (klassisch) wird nicht mehr fortgeführt und kann künftig nicht mehr aktualisiert werden.

Führt eine SQLite-Abfrage für Eingabedatasets aus, um die Daten zu transformieren

Kategorie: Datentransformation/-bearbeitung

Hinweis

Gilt nur für: Machine Learning Studio (klassisch)

Ähnliche Drag & Drop-Module sind im Azure Machine Learning-Designer verfügbar.

Modulübersicht

In diesem Artikel wird beschrieben, wie Sie das Modul Apply SQL Transformation in Machine Learning Studio (klassisch) verwenden, um eine SQL Abfrage für ein Eingabedataset oder datasets anzugeben.

SQL ist praktisch, wenn Sie Ihre Daten auf komplexe Weise ändern oder die Daten für die Verwendung in anderen Umgebungen beibehalten müssen. Mit dem Modul Apply SQL Transformation haben Sie beispielsweise folgende Möglichkeiten:

Erstellen von Tabellen für Ergebnisse und Speichern der Datasets in einer portierbaren Datenbank
Ausführen von benutzerdefinierten Transformationen für Datentypen oder Erstellen von Aggregaten
Ausführen von SQL-Abfrageanweisungen, um Daten zu filtern oder zu ändern und die Abfrageergebnisse als Datentabelle zurückzugeben

Wichtig

Die in diesem Modul verwendete SQL-Engine ist SQLite. Wenn Sie mit der SQLite-Syntax nicht vertraut sind, lesen Sie unbedingt den Abschnitt syntax and usage dieses Artikels, um Beispiele zu erhalten.

Was ist SQLite?

SQLite ist ein lizenzfreies relationales Datenbankmanagementsystem, das in einer C-Programmierungsbibliothek enthalten ist. SQLite ist eine beliebte Wahl für eine eingebettete Datenbank zur lokalen Speicherung in Webbrowsern.

SQLite wurde ursprünglich im Jahre 2000 für die U.S. Navy entwickelt, um serverlose Transaktionen zu unterstützen. Es handelt sich um eine eigenständige Datenbank-Engine, das über kein Verwaltungssystem verfügt und somit keine Konfiguration oder Administration erfordert.

Konfigurieren von „Apply SQL Transformation“

Das Modul kann bis zu drei Datasets als Eingaben übernehmen. Wenn Sie auf die Datasets verweisen, die mit den einzelnen Eingabeports verbunden sind, müssen Sie die Namen t1, t2 und t3 verwenden. Die Tabellennummer gibt den Index des Eingabeports an.

Der verbleibende Parameter ist eine SQL-Abfrage, für die die SQLite-Syntax verwendet wird. In diesem Modul werden alle Standardanweisungen der SQLite-Syntax unterstützt. Eine Liste der nicht unterstützten-Anweisungen finden Sie im Abschnitt Technische Hinweise.

Allgemeine Syntax und Verwendung

Wenn Sie mehrere Zeilen in das Textfeld SQL-Skript eingeben, verwenden Sie ein Semikolon, um jede Anweisung zu beenden. Andernfalls werden Zeilenumbrüche in Leerzeichen konvertiert.

Beispielsweise sind die folgenden Anweisungen äquivalent:
```
SELECT   
*   
from   
t1;  
```
```
SELECT * from t1;  
```
Sie können Kommentare hinzufügen, indem Sie entweder -- am Anfang jeder Zeile oder durch Einschließen von Text mithilfe /* */von verwenden.

Folgende Anweisung ist beispielsweise zulässig:
```
SELECT * from t1  
/*WHERE ItemID BETWEEN 1 AND 100*/;  
```
Wenn ein Spaltenname den Namen eines reservierten Schlüsselworts dupliziert, wird die Syntaxhervorhebung auf den Text im Textfeld SQL Skript angewendet. Um Verwechslungen zu vermeiden, sollten Sie Spaltennamen in eckige Klammern (gemäß der Transact-SQL-Konvention) oder Backticks oder doppelte Anführungszeichen (ANSI SQL Konvention) einschließen.

In der folgenden Abfrage des Datasets "Blood Username " ist Time beispielsweise ein gültiger Spaltenname, aber auch ein reserviertes Schlüsselwort.
```
SELECT Recency, Frequency, Monetary, Time, Class  
FROM t1  
WHERE Time between 3 and 20;  
```
Wenn Sie die Abfrage so ausführen, wie sie ist, gibt die Abfrage möglicherweise die richtigen Ergebnisse zurück, aber je nach Dataset wird möglicherweise ein Fehler zurückgegeben. Im Folgenden finden Sie einige Beispiele zur Vermeidung des Problems:
```
-- 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;  
```
Hinweis

Die Syntaxhervorhebung bleibt für das Schlüsselwort auch dann erhalten, wenn es in Anführungszeichen oder Klammern eingeschlossen wurde.
Bei SQLite wird die Groß-/Kleinschreibung nicht beachtet, mit Ausnahme einiger Befehle, die Varianten mit unterschiedlichen Bedeutungen (GLOB im Vergleich zu Glob) aufweisen, bei denen die Groß-/Kleinschreibung beachtet wird.

SELECT-Anweisung

In der SELECT -Anweisung müssen Spaltennamen, die Leerzeichen oder andere Zeichen enthalten, die in Bezeichnern unzulässig sind, in doppelte Anführungszeichen, eckige Klammern oder Hintergrundzeichen (') eingeschlossen werden.

Diese Abfrage verweist beispielsweise auf t1das Two-Class Iris-Dataset für , aber ein Spaltenname enthält ein unzulässiges Zeichen, sodass der Spaltenname in Anführungszeichen eingeschlossen ist.

SELECT class, "sepal-length" FROM t1;

Sie können eine WHERE -Klausel hinzufügen, um Werte im Dataset zu filtern.

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

Die SQLite-Syntax unterstützt nicht das TOP Schlüsselwort, das in Transact-SQL verwendet wird. Stattdessen können Sie das LIMIT Schlüsselwort oder eine FETCH -Anweisung verwenden.

Vergleichen Sie diese Abfragen beispielsweise mit dem Dataset Bike Rental.

-- 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;

Joins

Die folgenden Beispiele verwenden das "Restaurant Ratings"-Dataset für den Eingabeport, der t1 entspricht, und das "Restaurant Features"-Dataset für den Eingabeport, der t2 entspricht.

Die folgende Anweisung verknüpft die beiden Tabellen, um ein Dataset zu erstellen, dass die angegebenen Restaurantfunktionen mit Durchschnittsbewertungen für die einzelnen Restaurants kombiniert.

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;

Aggregatfunktionen

Dieser Abschnitt enthält grundlegende Beispiele für einige allgemeine SQL Aggregatfunktionen, die SQLite verwenden.

Derzeit unterstützte Aggregatfunktionen sind: AVG, COUNT, MAX, MIN, SUM, . TOTAL

Die folgende Abfrage gibt ein Dataset zurück, das die Restaurant-ID zusammen mit der Durchschnittsbewertung für das Restaurant enthält.

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

Arbeiten mit Zeichenfolgen

SQLite unterstützt den Operator mit doppeltem senkrechten Strich zur Verkettung von Zeichenfolgen.

Die folgende Anweisung erstellt eine neue Spalte durch Verkettung zweier Textspalten.

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

Warnung

Der Transact-SQL Zeichenfolgenverkettungsoperator wird nicht unterstützt: + (Zeichenfolgenverkettung). Der Ausdruck ('city + '-' + state) AS 'Target Region' in der Beispielabfrage würde z. B. für alle Werte 0 zurückgeben.

Obwohl der Operator für diesen Datentyp nicht unterstützt wird, wird in Machine Learning kein Fehler ausgelöst. Stellen Sie sicher, dass Sie die Ergebnisse von Apply SQL Transformation überprüfen, bevor Sie das sich ergebende Dataset in einem Experiment verwenden.

COALESCE und CASE

COALESCE wertet mehrere Argumente in der Reihenfolge aus und gibt den Wert des ersten Ausdrucks zurück, der nicht zu NULL ausgewertet wird.

Die folgende Abfrage zum mehrklassigen Dataset zur Stahlverarbeitung gibt das erste Kennzeichen, das nicht NULL ist, aus einer Liste von Spalten zurück, die sich gegenseitig ausschließende Werte aufweisen. Wenn kein Kennzeichen gefunden wird, wird die Zeichenfolge "None" zurückgegeben.

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

Die CASE -Anweisung ist nützlich, um Werte zu testen und einen neuen Wert basierend auf den ausgewerteten Ergebnissen zurückzugeben. SQLite unterstützt die folgende Syntax für CASE -Anweisungen:

CASE WHEN [Bedingung] THEN [Ausdruck] ELSE [Ausdruck] END
CASE [Bedingung] WHEN [Wert] THEN [Ausdruck] ELSE [Ausdruck] END

Angenommen, Sie haben zuvor das Modul Convert to Indicator Values (In Indikatorwerte konvertieren ) verwendet, um eine Gruppe von Featurespalten zu erstellen, die true-false-Werte enthalten. Die folgende Abfrage reduziert die Werte in mehreren Featurespalten in eine einzelne mehrwertige Spalte.

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;

Beispiele

Ein Beispiel für die Verwendung dieses Moduls in Machine Learning-Experimenten finden Sie in diesem Beispiel im Azure KI-Katalog:

Anwenden SQL Transformation: Verwendet das Dataset Restaurantbewertungen, Restaurantfunktionen und Restaurantkunden, um einfache Joins, Select-Anweisungen und Aggregatfunktionen zu veranschaulichen.

Technische Hinweise

Dieser Abschnitt enthält Implementierungsdetails, Tipps und Antworten auf häufig gestellte Fragen.

An Port 1 ist immer eine Eingabe erforderlich.
Wenn das Eingabedataset Spaltennamen aufweist, werden die Spalten im Ausgabedataset die Spaltennamen aus dem Eingabedataset verwenden.

Wenn das Eingabedataset keine Spaltennamen hat, werden die Spaltennamen in der Tabelle automatisch mithilfe der folgenden Benennungskonvention erstellt: T1COL1, T1COL2, T1COL3 usw., wobei die Zahlen den Index jeder Spalte im Eingabedataset angeben.
Enthält ein Spaltenbezeichner (Spalten-ID) ein Leerzeichen oder andere Sonderzeichen, müssen Sie den Bezeichner in eckige Klammern oder doppelte Anführungszeichen einschließen, wenn Sie in einer SELECT- oder WHERE-Klausel auf die Spalte verweisen.

Nicht unterstützte Anweisungen

SQLite unterstützt zwar einen Großteil des ANSI SQL-Standards, umfasst aber nicht viele Funktionen, die von kommerziellen relationalen Datenbanksystemen unterstützt werden. Weitere Informationen finden Sie unter SQL as Understood by SQLite. Beachten Sie beim Erstellen von SQL-Anweisungen außerdem die folgenden Einschränkungen:

SQLite verwendet dynamische Typzuweisung für Werte, anstatt jeder Spalte einen Typ zuzuweisen, wie dies in den meisten relationalen Datenbanksystemen geschieht. SQLite ist schwach typisiert und ermöglicht implizite Typkonvertierung.
LEFT OUTER JOIN ist implementiert, RIGHT OUTER JOIN und FULL OUTER JOIN sind dagegen nicht implementiert.
Sie können RENAME TABLE- und ADD COLUMN-Anweisungen mit dem ALTER TABLE-Befehl verwenden, andere Klauseln werden jedoch nicht unterstützt, einschließlich DROP COLUMN, ALTER COLUMN und ADD CONSTRAINT.
Sie können eine SICHT in SQLite erstellen, aber danach sind Sichten schreibgeschützt. Sie können keine DELETE-, INSERT- oder UPDATE-Anweisung für eine Sicht ausführen. Sie können jedoch einen Trigger erstellen, der beim Ausführen einer DELETE-, INSERT- oder UPDATE-Anweisung für eine Sicht ausgelöst wird, und Sie können in dem Trigger weitere Vorgänge ausführen.

Zusätzlich zur Liste der nicht unterstützten Funktionen, die auf der offiziellen SQLite-Website zu finden ist, wird im folgenden Wiki eine Liste weiterer nicht unterstützter Features bereitgestellt: SQLite - Unsupported SQL

Erwartete Eingaben

Name	Type	Beschreibung
Table1	Datentabelle	Eingabedataset1
Table2	Datentabelle	Eingabedataset2
Table3	Datentabelle	Eingabedataset3

Modulparameter

Name	Range	type	Standard	Beschreibung
SQL Query Script	any	StreamReader		SQL-Abfrageanweisung

Ausgaben

Name	Type	Beschreibung
Ergebnisdataset	Datentabelle	Ausgabedataset

Ausnahmen

Ausnahme	Beschreibung
Fehler 0001	Eine Ausnahme tritt auf, wenn mindestens eine angegebene Spalte des Datasets nicht gefunden werden konnte.
Fehler 0003	Eine Ausnahme tritt auf, wenn mindestens ein Eingabedataset NULL oder leer ist.
Fehler 0069	SQL logikfehler oder fehlende Datenbank

Eine Liste der Fehler, die für Studio-Module (klassisch) spezifisch sind, finden Sie unter Machine Learning Fehlercodes.

Eine Liste der API-Ausnahmen finden Sie unter Machine Learning REST-API-Fehlercodes.

Siehe auch

Manipulation
Datentransformation
Modulliste von A bis Z