Använda SQL-transformering

Viktigt

Stödet för Machine Learning Studio (klassisk) upphör den 31 augusti 2024. Vi rekommenderar att du byter till Azure Machine Learning innan dess.

Från och med den 1 december 2021 kan du inte längre skapa nya Machine Learning Studio-resurser (klassisk). Du kan fortsätta att använda befintliga Machine Learning Studio-resurser (klassisk) till och med den 31 augusti 2024.

• Se information om hur du flyttar maskininlärningsprojekt från ML Studio (klassisk) till Azure Machine Learning.
• Läs mer om Azure Machine Learning.

Dokumentationen om ML Studio (klassisk) håller på att dras tillbaka och kanske inte uppdateras i framtiden.

Kör en SQLite-fråga på indatauppsättningar för att transformera data

Kategori: Datatransformering/manipulering

Anteckning

Gäller endast för: Machine Learning Studio (klassisk)

Liknande dra och släpp-moduler är tillgängliga i Azure Machine Learning designer.

Modulöversikt

Den här artikeln beskriver hur du använder modulen Apply SQL Transformation i Machine Learning Studio (klassisk) för att ange en SQL-fråga på en indatauppsättning eller datauppsättningar.

SQL är praktiskt när du behöver ändra dina data på komplexa sätt eller spara data för användning i andra miljöer. Med hjälp av modulen Tillämpa SQL transformation kan du till exempel:

• Skapa tabeller för resultat och spara datauppsättningarna i en portabel databas.

• Utföra anpassade transformningar på datatyper eller skapa aggregeringar.

• Kör SQL för att filtrera eller ändra data och returnera frågeresultatet som en datatabell.

Viktigt

Den SQL som används i den här modulen är SQLite. Om du inte är bekant med SQLite-syntaxen läser du avsnittet syntax och användning i den här artikeln för exempel.

Vad är SQLite?

SQLite är ett hanteringssystem för relationsdatabaser i offentlig domän som finns i ett C-programmeringsbibliotek. SQLite är ett populärt val som en inbäddad databas för lokal lagring i webbläsare.

SQLite utformades ursprungligen år 2000 för USA:s dåd för att stödja serverlösa transaktioner. Det är en fristående databasmotor som inte har något hanteringssystem och därför inte kräver någon konfiguration eller administration.

Så här konfigurerar du Apply SQL Transformation (Tillämpa SQL transformering)

Modulen kan ta upp till tre datauppsättningar som indata. När du refererar till de datauppsättningar som är anslutna till varje indataport måste du använda namnen t1, t2och t3. Tabellnumret anger indexet för indataporten.

Den återstående parametern är SQL fråga, som använder SQLite-syntaxen. Den här modulen stöder alla standardsatser för SQLite-syntaxen. En lista över instruktioner som inte stöds finns i avsnittet Tekniska anteckningar.

Allmän syntax och användning

• När du skriver flera rader SQL textrutan Skript använder du ett semikolon för att avsluta varje instruktion. Annars konverteras radbrytningar till blanksteg.

  Följande instruktioner är till exempel likvärdiga:

  SELECT   
  *   
  from   
  t1;  
  

  SELECT * from t1;  
  

• Du kan lägga till kommentarer antingen -- i början av varje rad eller genom att omsluta text med hjälp av /* */.

  Den här instruktionen är till exempel giltig:

  SELECT * from t1  
  /*WHERE ItemID BETWEEN 1 AND 100*/;  
  

• Om ett kolumnnamn duplicerar namnet på ett reserverat nyckelord, tillämpas syntaxmarkering på texten i textrutan SQL skript. För att undvika förvirring bör du omge kolumnnamn med hakparenteser (för att följa Transact-SQL-konventionen) eller grava färger eller dubbla citattecken (ANSI-konventionen SQL).

  I följande fråga om datamängden Blood Dataset är Time ett giltigt kolumnnamn men är också ett reserverat nyckelord.

  SELECT Recency, Frequency, Monetary, Time, Class  
  FROM t1  
  WHERE Time between 3 and 20;  
  

  Om du kör frågan som den är kan frågan returnera rätt resultat, men beroende på datauppsättningen kan den returnera ett fel. Här följer några exempel på hur du undviker problemet:

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

  Anteckning

  Syntaxmarkeringen finns kvar på nyckelordet även efter att den omges av citattecken eller hakparenteser.

• SQLite är icke-känslig, förutom för några kommandon som har fallkänsliga varianter med olika betydelser (GLOB kontra glob).

SELECT-instruktion

I - SELECT instruktionen måste kolumnnamn som innehåller blanksteg eller andra tecken som är förbjudna i identifierare omges av dubbla citattecken, hakparenteser eller bakåtklickstecken (').

Den här frågan refererar till exempel till Two-Class Iris-datauppsättningen t1på , men ett kolumnnamn innehåller ett förbjudet tecken, så kolumnnamnet omges av citattecken.

SELECT class, "sepal-length" FROM t1;  

Du kan lägga till en - WHERE sats för att filtrera värden i datauppsättningen.

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

SQLite-syntaxen stöder inte nyckelordet TOP , som används i Transact-SQL. I stället kan du använda nyckelordet LIMIT eller en - FETCH instruktion.

Du kan till exempel jämföra dessa frågor i datamängden Cykeluthyrning.

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

Kopplingar

I följande exempel används datamängden Restaurant Ratings på den indataport t1som motsvarar och datamängden Restaurangfunktioner på den indataport som motsvarar t2.

Följande instruktion sammanfogar de två tabellerna för att skapa en datamängd som kombinerar de angivna restaurangfunktionerna med genomsnittliga omdömen för varje restaurang.

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;  

Mängdfunktioner

Det här avsnittet innehåller grundläggande exempel på några vanliga SQL mängdfunktioner med SQLite.

Mängdfunktioner som för närvarande stöds är: AVG, COUNT, MAX, MIN, SUM, TOTAL.

Följande fråga returnerar en datamängd som innehåller restaurang-ID:t, tillsammans med det genomsnittliga omdömet för restaurangerna.

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

Arbeta med strängar

SQLite stöder den dubbla pipe-operatorn för att sammanfoga strängar.

Följande instruktion skapar en ny kolumn genom att sammanfoga två textkolumner.

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

Varning

Operatorn Transact-SQL för sammanfogning av strängar stöds inte: + (sammanfogning av strängar). Till exempel skulle uttrycket ('city + '-' + state) AS 'Target Region'i exempelfrågan returnera 0 för alla värden.

Även om operatorn inte stöds för den här datatypen utlöses dock inget fel i Machine Learning. Se till att kontrollera resultatet av Apply SQL Transformation innan du använder den resulterande datauppsättningen i ett experiment.

COALESCE och CASE

COALESCE utvärderar flera argument i ordning och returnerar värdet för det första uttrycket som inte utvärderas till NULL.

Till exempel returnerar den här frågan i Datauppsättningen För flerklassig annealering den första icke-null-flaggan från en lista med kolumner som antas ha ömsesidigt uteslutande värden. Om ingen flagga hittas returneras strängen "none".

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

- CASE instruktionen är användbar för att testa värden och returnera ett nytt värde baserat på de utvärderade resultaten. SQLite stöder följande syntax för - CASE instruktioner:

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

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

Anta till exempel att du tidigare har använt modulen Konvertera till indikatorvärden för att skapa en uppsättning funktionskolumner som innehåller true-false-värden. Följande fråga minimerar värdena i flera egenskapskolumner till en enda flervärdeskolumn.

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;  

Exempel

Ett exempel på hur den här modulen kan användas i maskininlärningsexperiment finns i det här exemplet i Azure AI Gallery:

• Använd SQL Transformation: Använder datamängden Restaurangklassificeringar, Restaurangfunktioner och Restaurangkunder för att illustrera enkla kopplingar, välja instruktioner och mängdfunktioner.

Teknisk information

Det här avsnittet innehåller implementeringsinformation, tips och svar på vanliga frågor.

• Indata krävs alltid på port 1.

• Om indatauppsättningen har kolumnnamn använder kolumnerna i utdatauppsättningen kolumnnamnen från indatauppsättningen.

  Om indatauppsättningen inte har kolumnnamn skapas kolumnnamnen i tabellen automatiskt med hjälp av följande namngivningskonvention: T1COL1, T1COL2, T1COL3 och så vidare, där siffrorna visar indexet för varje kolumn i indatauppsättningen.

• För kolumnidentifierare som innehåller ett blanksteg eller andra specialtecken omsluter SELECT du alltid kolumnidentifieraren inom hakparenteser eller dubbla citattecken när du refererar till kolumnen i - WHERE eller -satserna.

Instruktioner som inte stöds

Även om SQLite stöder en stor del av ANSI SQL standarden innehåller den inte många funktioner som stöds av kommersiella relationsdatabassystem. Mer information finns i SQL förstådd av SQLite. Tänk också på följande begränsningar när du skapar SQL instruktioner:

• SQLite använder dynamisk skrivning för värden i stället för att tilldela en typ till en kolumn som i de flesta relationsdatabassystem. Den är svag typad och tillåter implicit typkonvertering.

• LEFT OUTER JOIN implementeras, men inte RIGHT OUTER JOIN eller FULL OUTER JOIN.

• Du kan använda RENAME TABLE - och ADD COLUMN -instruktioner med ALTER TABLE kommandot, men andra satser stöds inte, inklusive DROP COLUMN, ALTER COLUMNoch ADD CONSTRAINT.

• Du kan skapa en VY inom SQLite, men därefter är vyerna skrivskyddade. Du kan inte köra en DELETE- INSERT, - eller UPDATE -instruktion i en vy. Du kan dock skapa en utlösare som utlöses DELETEvid ett försök till , INSERTeller UPDATE i en vy och utföra andra åtgärder i utlösartexten.

Förutom listan över funktioner som inte stöds på den officiella SQLite-webbplatsen innehåller följande wiki en lista över andra funktioner som inte stöds: SQLite - Unsupported SQL

Förväntade indata

Namn	Typ	Description
Tabell1	Datatabell	Indatauppsättning1
Tabell2	Datatabell	Indatauppsättning2
Tabell3	Datatabell	Indatauppsättning3

Modulparametrar

Name	Intervall	Typ	Standardvärde	Description
SQL frågeskript	valfri	StreamReader		SQL frågeutdrag

Utdata

Namn	Typ	Description
Resultatdatauppsättning	Datatabell	Utdatauppsättning

Undantag

Undantag	Description
Fel 0001	Ett undantag inträffar om det inte går att hitta en eller flera angivna kolumner i datauppsättningen.
Fel 0003	Ett undantag inträffar om en eller flera av indatauppsättningarna är null eller tomma.
Fel 0069	SQL logikfel eller databas som saknas

En lista över fel som är specifika för Studio-moduler (klassisk) finns i Machine Learning felkoder.

En lista över API-undantag finns i Machine Learning REST API felkoder.

Se även

Manipulation
Datatransformering
A-Z-modullista