SQL-transformatie toepassen

Artikel
05/06/2019

Belangrijk

De ondersteuning voor Azure Machine Learning-studio (klassiek) eindigt op 31 augustus 2024. U wordt aangeraden om vóór die datum over te stappen naar Azure Machine Learning.

Vanaf 1 december 2021 kunt u geen nieuwe resources voor Azure Machine Learning-studio (klassiek) meer maken. Tot en met 31 augustus 2024 kunt u de bestaande resources van Azure Machine Learning-studio (klassiek) blijven gebruiken.

Zie informatie over het verplaatsen machine learning van ML Studio (klassiek) naar Azure Machine Learning.
Meer informatie over Azure Machine Learning.

De documentatie van ML-studio (klassiek) wordt buiten gebruik gesteld en wordt in de toekomst mogelijk niet meer bijgewerkt.

Voert een SQLite-query uit op invoergegevenssets om de gegevens te transformeren

Categorie: Gegevenstransformatie/manipulatie

Notitie

Van toepassing op: Machine Learning Studio (klassiek)

Vergelijkbare modules voor slepen en neerzetten zijn beschikbaar in Azure Machine Learning designer.

Moduleoverzicht

In dit artikel wordt beschreven hoe u de module Apply SQL Transformation in Machine Learning Studio (klassiek) gebruikt om een SQL query op een invoerset of gegevenssets op te geven.

SQL is handig wanneer u uw gegevens op complexe manieren moet wijzigen of de gegevens wilt persistent maken voor gebruik in andere omgevingen. Met de module Apply SQL Transformation kunt u bijvoorbeeld het volgende doen:

Maak tabellen voor resultaten en sla de gegevenssets op in een draagbare database.
Voer aangepaste transformaties uit op gegevenstypen of maak statistische gegevens.
Voer SQL query-instructies uit om gegevens te filteren of te wijzigen en de queryresultaten te retourneren als een gegevenstabel.

Belangrijk

De SQL-engine die in deze module wordt gebruikt, is SQLite. Als u niet bekend bent met de SQLite-syntaxis, lees dan de sectie syntaxis en gebruik van dit artikel voor voorbeelden.

Wat is SQLite?

SQLite is een relationeel databasebeheersysteem voor openbare domeinen dat is opgenomen in een C-programmeerbibliotheek. SQLite is een populaire keuze als ingesloten database voor lokale opslag in webbrowsers.

SQLite is oorspronkelijk ontworpen in 2000 voor de AmerikaanseLetters ter ondersteuning van serverloze transacties. Het is een zelfstandige database-engine die geen beheersysteem heeft en daarom geen configuratie of beheer vereist.

Een transformatie toepassen SQL configureren

De module kan maximaal drie gegevenssets als invoer gebruiken. Wanneer u verwijst naar de gegevenssets die zijn verbonden met elke invoerpoort, moet u de namen t1, t2en gebruiken t3. Het tabelnummer geeft de index van de invoerpoort aan.

De resterende parameter is een SQL query, die gebruikmaakt van de SQLite-syntaxis. Deze module ondersteunt alle standaard-instructies van de SQLite-syntaxis. Zie de sectie Technische notities voor een lijst met niet-ondersteunde instructies.

Algemene syntaxis en gebruik

Wanneer u meerdere regels typt in SQL tekstvak Script, gebruikt u een puntpunt om elke instructie te beëindigen. Anders worden regelbreaks geconverteerd naar spaties.

De volgende instructies zijn bijvoorbeeld gelijkwaardig:
```
SELECT   
*   
from   
t1;  
```
```
SELECT * from t1;  
```
U kunt opmerkingen toevoegen met behulp van -- aan het begin van elke regel of door tekst in te voegen met behulp van /* */.

Deze instructie is bijvoorbeeld geldig:
```
SELECT * from t1  
/*WHERE ItemID BETWEEN 1 AND 100*/;  
```
Als een kolomnaam de naam van een gereserveerd trefwoord dupliceert, wordt syntaxis markeren toegepast op de tekst in SQL tekstvak Script. Om verwarring te voorkomen, moet u kolomnamen tussen vierkante haken (om de Transact-SQL-conventie te volgen) of backticks of dubbele aanhalingstekens (de ANSI-SQL conventie).

In de volgende query op de gegevensset Bloeddonatie is Tijd bijvoorbeeld een geldige kolomnaam, maar ook een gereserveerd trefwoord.
```
SELECT Recency, Frequency, Monetary, Time, Class  
FROM t1  
WHERE Time between 3 and 20;  
```
Als u de query uitvoert zoals deze is, kan de query de juiste resultaten retourneren, maar afhankelijk van de gegevensset kan er een fout worden weergegeven. Hier zijn enkele voorbeelden van hoe u het probleem kunt voorkomen:
```
-- 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;  
```
Notitie

Syntaxis markering blijft op het trefwoord zelfs nadat deze tussen aanhalingstekens of haakjes.
SQLite is niet-casegevoelig, met uitzondering van enkele opdrachten met casegevoelige varianten met verschillende betekenis (GLOB versus glob).

SELECT-instructie

In de SELECT instructie moeten kolomnamen die spaties of andere tekens bevatten die niet zijn toegestaan in id's, tussen dubbele aanhalingstekens, vierkante haken of backticktekens (') worden ingesloten.

Deze query verwijst bijvoorbeeld naar de Two-Class Iris-gegevensset t1op , maar één kolomnaam bevat een niet-toegestaan teken, waardoor de kolomnaam tussen aanhalingstekens staat.

SELECT class, "sepal-length" FROM t1;

U kunt een -component toevoegen WHERE om waarden in de gegevensset te filteren.

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

De SQLite-syntaxis biedt geen ondersteuning voor het trefwoord TOP , dat wordt gebruikt in Transact-SQL. In plaats daarvan kunt u het trefwoord LIMIT of een instructie FETCH gebruiken.

Vergelijk deze query's bijvoorbeeld op de gegevensset 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;

Samenvoegingen

In de volgende voorbeelden wordt de gegevensset Restaurantbeoordelingen gebruikt op de invoerpoort die overeenkomt t1met en de gegevensset Restaurantfuncties op de invoerpoort die overeenkomt met t2.

Met de volgende instructie worden de twee tabellen toegevoegd om een gegevensset te maken waarin de opgegeven restaurantfuncties worden gecombineerd met gemiddelde beoordelingen voor elk restaurant.

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;

Statistische functies

Deze sectie bevat basisvoorbeelden van een aantal algemene SQL statistische functies, met behulp van SQLite.

Statistische functies die momenteel worden ondersteund, zijn: AVG, COUNT, MINMAX, , SUM, TOTAL.

De volgende query retourneert een gegevensset met de restaurant-id, samen met de gemiddelde waardering voor het restaurant.

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

Werken met tekenreeksen

SQLite ondersteunt de dubbele pijpoperator voor het samenvoegen van tekenreeksen.

Met de volgende instructie wordt een nieuwe kolom gemaakt door twee tekstkolommen samen tevoegen.

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

Waarschuwing

De Transact-SQL operator voor tekenreeksen samenvoegen wordt niet ondersteund: + (Tekenreeks samenvoegen). De expressie in de ('city + '-' + state) AS 'Target Region'voorbeeldquery retourneert bijvoorbeeld 0 voor alle waarden.

Hoewel de operator echter niet wordt ondersteund voor dit gegevenstype, wordt er geen fout weergegeven in Machine Learning. Controleer de resultaten van Apply SQL Transformation voordat u de resulterende gegevensset in een experiment gebruikt.

COALESCE en CASE

COALESCE evalueert meerdere argumenten op volgorde en retourneert de waarde van de eerste expressie die niet wordt geëvalueerd als NULL.

Met deze query op de gegevensset Steel Annealing Multi-Class wordt bijvoorbeeld de eerste niet-null-vlag geretourneerd uit een lijst met kolommen die elkaar wederzijds uitsluiten. Als er geen vlag wordt gevonden, wordt de tekenreeks 'none' geretourneerd.

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

De CASE instructie is handig voor het testen van waarden en het retourneren van een nieuwe waarde op basis van de geëvalueerde resultaten. SQLite ondersteunt de volgende syntaxis voor - CASE instructies:

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

Stel bijvoorbeeld dat u eerder de module Converteren naar indicatorwaarden hebt gebruikt om een set functiekolommen te maken die waar-onwaar-waarden bevatten. Met de volgende query worden de waarden in meerdere functiekolommen samengevouwen in één kolom met meerdere waarden.

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;

Voorbeelden

Voor een voorbeeld van hoe deze module kan worden gebruikt in machine learning experimenten, zie dit voorbeeld in de Azure AI Gallery:

Transformatie SQL toepassen: maakt gebruik van de gegevensset Restaurantbeoordelingen, Restaurantfuncties en Restaurantklanten om eenvoudige joins, select-instructies en statistische functies te illustreren.

Technische opmerkingen

Deze sectie bevat implementatiedetails, tips en antwoorden op veelgestelde vragen.

Invoer is altijd vereist op poort 1.
Als de invoerset kolomnamen bevat, gebruiken de kolommen in de uitvoerset de kolomnamen uit de invoerset.

Als de invoerset geen kolomnamen heeft, worden de kolomnamen in de tabel automatisch gemaakt met behulp van de volgende naamconventie: T1COL1, T1COL2, T1COL3, en meer, waarbij de getallen de index van elke kolom in de invoerset aangeven.
Voor kolom-id's die een spatie of andere speciale tekens bevatten, sluit u de kolom-id altijd tussen vierkante haken of dubbele aanhalingstekens wanneer u verwijst naar de kolom in SELECTWHERE de - of -component.

Niet-ondersteunde instructies

Hoewel SQLite veel van de ANSI-SQL ondersteunt, bevat het niet veel functies die worden ondersteund door commerciële relationele databasesystemen. Zie voor meer informatie SQL zoals Begrepen door SQLite. Let ook op de volgende beperkingen bij het maken van SQL-instructies:

SQLite maakt gebruik van dynamisch typen voor waarden, in plaats van een type toe te wijzen aan een kolom zoals in de meeste relationele databasesystemen. Deze is zwak getypt en maakt impliciete typeconversie mogelijk.
LEFT OUTER JOIN is geïmplementeerd, maar niet RIGHT OUTER JOIN of FULL OUTER JOIN.
U kunt - RENAME TABLE en - ADD COLUMN instructies gebruiken met de ALTER TABLE opdracht , maar andere -componenten worden niet ondersteund, zoals DROP COLUMN, ALTER COLUMNen ADD CONSTRAINT.
U kunt een WEERGAVE maken in SQLite, maar daarna zijn weergaven alleen-lezen. U kunt geen instructie DELETE, INSERTof uitvoeren UPDATE voor een weergave. U kunt echter een trigger maken die DELETEwordt uitgevoerd bij een poging tot , INSERTUPDATE of in een weergave en andere bewerkingen in de body van de trigger uit te voeren.

Naast de lijst met niet-ondersteunde functies op de officiële SQLite-site, bevat de volgende wiki een lijst met andere niet-ondersteunde functies: SQLite - Niet-ondersteunde SQL

Verwachte invoer

Naam	Type	Description
Table1	Gegevenstabel	Invoer-gegevensset1
Table2	Gegevenstabel	Invoer gegevensset2
Tabel3	Gegevenstabel	Invoerset3

Moduleparameters

Name	Bereik	Type	Standaard	Beschrijving
SQL Queryscript	alle	StreamReader		SQL query-instructie

Uitvoerwaarden

Naam	Type	Description
Gegevensset met resultaten	Gegevenstabel	Uitvoerset

Uitzonderingen

Uitzondering	Description
Fout 0001	Er treedt een uitzondering op als een of meer opgegeven kolommen van de gegevensset niet kunnen worden gevonden.
Fout 0003	Er treedt een uitzondering op als een of meer invoersets null of leeg zijn.
Fout 0069	SQL of ontbrekende database

Zie Foutcodes voor een lijst met fouten die specifiek zijn Machine Learning voor Studio-modules (klassiek).

Zie Foutcodes voor een lijst Machine Learning REST API API-uitzonderingen.

Zie ook

Manipulatie
Gegevenstransformatie
Lijst met A-Z-modules

Delen via