Stosowanie przekształcenia SQL

Ważne

Obsługa programu Machine Learning Studio (wersja klasyczna) zakończy się 31 sierpnia 2024 r. Zalecamy przejście do usługi Azure Machine Learning przed tym terminem.

Od 1 grudnia 2021 r. nie będzie można tworzyć nowych zasobów programu Machine Learning Studio (wersja klasyczna). Do 31 sierpnia 2024 r. można będzie nadal korzystać z istniejących zasobów programu Machine Learning Studio (wersja klasyczna).

• Zobacz informacje na temat przenoszenia projektów uczenia maszynowego z programu ML Studio (wersja klasyczna) do Azure Machine Learning.
• Dowiedz się więcej o Azure Machine Learning.

Dokumentacja programu ML Studio (wersja klasyczna) jest wycofywana i może nie być aktualizowana w przyszłości.

Uruchamia zapytanie SQLite w wejściowych zestawach danych, aby przekształcić dane

Kategoria: Przekształcanie/manipulowanie danymi

Uwaga

Dotyczy: tylko Machine Learning Studio (klasyczne)

Podobne moduły przeciągania i upuszczania są dostępne w Azure Machine Learning projektanta.

Omówienie modułu

W tym artykule opisano sposób użycia modułu Zastosuj SQL Transformation w programie Machine Learning Studio (wersja klasyczna) w celu określenia zapytania SQL wejściowego zestawu danych lub zestawów danych.

SQL jest przydatna, gdy konieczne jest modyfikowanie danych w złożony sposób lub utrwalanie danych do użycia w innych środowiskach. Na przykład za pomocą modułu Apply SQL Transformation (Stosowanie SQL przekształcenia) można:

• Utwórz tabele dla wyników i zapisz zestawy danych w przenośnej bazie danych.

• Wykonywanie niestandardowych przekształceń typów danych lub tworzenie agregacji.

• Wykonaj SQL zapytania, aby filtrować lub zmieniać dane i zwracać wyniki zapytania jako tabelę danych.

Ważne

Aparatem SQL używanym w tym module jest SQLite. Jeśli nie masz informacji o składni języka SQLite, zapoznaj się z sekcją składni i użycia tego artykułu, aby zapoznać się z przykładami.

Co to jest SQLite?

SQLite to system zarządzania relacyjną bazą danych domeny publicznej, który znajduje się w bibliotece programowania języka C. SQLite to popularny wybór jako osadzona baza danych dla magazynu lokalnego w przeglądarkach internetowych.

SqLite został pierwotnie zaprojektowany w 2000 r. dla Stanów Zjednoczonych Do obsługi transakcji bez serwera. Jest to samodzielny aparat bazy danych, który nie ma systemu zarządzania i dlatego nie wymaga konfiguracji ani administracji.

Jak skonfigurować stosowanie SQL przekształcania

Moduł może przyjmować do trzech zestawów danych jako dane wejściowe. W przypadku odwołania do zestawów danych połączonych z każdym portem wejściowym należy użyć nazw t1, t2i t3. Numer tabeli wskazuje indeks portu wejściowego.

Pozostały parametr jest zapytaniem SQL, które używa składni SQLite. Ten moduł obsługuje wszystkie standardowe instrukcje składni SQLite. Aby uzyskać listę nieobsługiwanych instrukcji, zobacz sekcję Uwagi techniczne.

Ogólna składnia i użycie

• Podczas wpisywania wielu wierszy w SQL tekstowym Skryptu użyj średnika, aby zakończyć każdą z instrukcji. W przeciwnym razie podziały wierszy są konwertowane na spacje.

  Na przykład następujące instrukcje są równoważne:

  SELECT   
  *   
  from   
  t1;  
  

  SELECT * from t1;  
  

• Komentarze można dodawać przy użyciu polecenia -- na początku każdego wiersza lub przez dołączenie tekstu przy użyciu polecenia /* */.

  Na przykład ta instrukcja jest prawidłowa:

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

• Jeśli nazwa kolumny duplikuje nazwę zastrzeżonego słowa kluczowego, wyróżnianie składni jest stosowane do tekstu w SQL tekstowym Skrypt. Aby uniknąć nieporozumień, należy ująć nazwy kolumn w nawiasy kwadratowe (zgodnie z konwencją Transact-SQL) lub znakami backtics lub podwójnymi cudzysłowami (konwencja ANSI SQL).

  Na przykład w poniższym zapytaniu dla zestawu danych dodawania do krwi czas jest prawidłową nazwą kolumny, ale jest również zastrzeżonym słowem kluczowym.

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

  Jeśli zapytanie zostanie uruchomione bez tego typu, zapytanie może zwrócić poprawne wyniki, ale w zależności od zestawu danych może zwrócić błąd. Oto kilka przykładów, jak uniknąć problemu:

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

  Uwaga

  Wyróżnianie składni pozostaje w słowie kluczowym nawet wtedy, gdy jest ujęte w cudzysłów lub nawiasy.

• W programie SQLite nie jest uwzględniania wielkość liter, z wyjątkiem kilku poleceń, które mają warianty z uwzględnianiem wielkości liter o różnych znaczeniach (GLOB a glob).

Instrukcja SELECT

W instrukcji SELECT nazwy kolumn, które zawierają spacje lub inne znaki zabronione w identyfikatorach, muszą być ujęte w znaki podwójnego cudzysłowu, nawiasy kwadratowe lub znaki znakowe (').

Na przykład to zapytanie odwołuje się do Two-Class zestawu danych irysów t1w pliku , ale jedna nazwa kolumny zawiera niedozwolony znak, więc nazwa kolumny jest ujęta w cudzysłów.

SELECT class, "sepal-length" FROM t1;  

Możesz dodać klauzulę, WHERE aby filtrować wartości w zestawie danych.

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

Składnia SQLite nie obsługuje słowa kluczowego TOP , które jest używane w transact-SQL. Zamiast tego można użyć słowa kluczowego LIMIT lub instrukcji FETCH .

Na przykład porównaj te zapytania w zestawie danych Bike Rental (Wypożyczanie rowerów).

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

Sprzężenia

W poniższych przykładach na porcie wejściowym odpowiadającym elementowi użyjemy zestawu danych Klasyfikacje t1restauracji oraz zestawu danych Funkcji restauracji na porcie wejściowym odpowiadającym .t2

Następująca instrukcja łączy dwie tabele w celu utworzenia zestawu danych, który łączy określone funkcje restauracji z średnimi ocenami dla każdej restauracji.

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;  

Funkcje agregujące

Ta sekcja zawiera podstawowe przykłady niektórych typowych funkcji SQL funkcji agregowania przy użyciu sqlite.

Obecnie obsługiwane funkcje agregowania to: AVG, COUNT, MAX, MIN, SUM, . TOTAL

Poniższe zapytanie zwraca zestaw danych zawierający identyfikator restauracji wraz ze średnią oceną dla restauracji.

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

Praca z ciągami

SqLite obsługuje operator podwójnej potoku do połączenia ciągów.

Następująca instrukcja tworzy nową kolumnę przez utworzenie dwóch kolumn tekstowych.

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

Ostrzeżenie

Operator konkasowania SQL transact-SQL nie jest obsługiwany: + (konkatacja ciągów). Na przykład wyrażenie w przykładzie ('city + '-' + state) AS 'Target Region'zapytania zwróci wartość 0 dla wszystkich wartości.

Jednak mimo że operator nie jest obsługiwany dla tego typu danych, w Machine Learning. Przed użyciem wynikowego zestawu danych w eksperymencie upewnij się, że SQL zastosuj przekształcenie.

COALESCE i CASE

COALESCE oblicza wiele argumentów w kolejności i zwraca wartość pierwszego wyrażenia, które nie daje wartości NULL.

Na przykład to zapytanie dla zestawu danych z wieloma klasami do odplatania ze sobą zwraca pierwszą flagę o wartości innych niż null z listy kolumn, w których założono, że mają wzajemnie wykluczające się wartości. Jeśli flaga nie zostanie znaleziona, zostanie zwrócony ciąg "none".

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

Instrukcja CASE jest przydatna do testowania wartości i zwracania nowej wartości na podstawie ocenianych wyników. SqLite obsługuje następującą składnię instrukcji CASE :

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

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

Załóżmy na przykład, że wcześniej używano modułu Konwertuj na wartości wskaźnika do utworzenia zestawu kolumn cech zawierających wartości true-false. Poniższe zapytanie zwija wartości w wielu kolumnach cech w jedną kolumnę wielowartościową.

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;  

Przykłady

Aby uzyskać przykład sposobu, w jaki ten moduł może być używany w eksperymentach uczenia maszynowego, zobacz ten przykład w Azure AI Gallery:

• Zastosuj SQL: używa zestawu danych Restaurant Ratings, Restaurant Features i Restaurant Customers, aby zilustrować proste sprzężenia, wybór instrukcji i funkcje agregujące.

Uwagi techniczne

Ta sekcja zawiera szczegóły implementacji, porady i odpowiedzi na często zadawane pytania.

• Dane wejściowe są zawsze wymagane na porcie 1.

• Jeśli wejściowy zestaw danych ma nazwy kolumn, kolumny w wyjściowym zestawie danych będą używać nazw kolumn z wejściowego zestawu danych.

  Jeśli wejściowy zestaw danych nie ma nazw kolumn, nazwy kolumn w tabeli są tworzone automatycznie przy użyciu następującej konwencji nazewnictwa: T1COL1, T1COL2, T1COL3 i tak dalej, gdzie liczby wskazują indeks każdej kolumny w wejściowym zestawie danych.

• W przypadku identyfikatorów kolumn zawierających spację lub inne znaki specjalne należy zawsze ująć identyfikator kolumny w nawiasy kwadratowe lub podwójne cudzysłowy SELECT podczas odwoływania się do kolumny w klauzulach lub WHERE .

Nieobsługiwane instrukcje

Mimo że sqlite obsługuje większość standardu ANSI SQL, nie zawiera wielu funkcji obsługiwanych przez komercyjne systemy relacyjnych baz danych. Aby uzyskać więcej informacji, zobacz SQL as Understood by SQLite (Zrozumiały dla sqlite). Należy również pamiętać o następujących ograniczeniach podczas tworzenia SQL instrukcji:

• SqLite używa dynamicznego wpisywania wartości zamiast przypisywania typu do kolumny, jak w większości systemów relacyjnych baz danych. Jest słabie wpisany i umożliwia niejawną konwersję typu.

• LEFT OUTER JOIN jest zaimplementowany, ale nie lub RIGHT OUTER JOINFULL OUTER JOIN.

• Z poleceniem RENAME TABLE można używać ALTER TABLE instrukcji i ADD COLUMN , ale inne klauzule nie są obsługiwane, w tym DROP COLUMN, ALTER COLUMNi ADD CONSTRAINT.

• Widok można utworzyć w ramach sqlite, ale później widoki są tylko do odczytu. Nie można wykonać instrukcji DELETE, INSERTlub UPDATE w widoku. Można jednak utworzyć wyzwalaczDELETE, który jest wyzwalany przy próbie wykonania operacji , INSERTUPDATE lub w widoku i wykonywać inne operacje w treści wyzwalacza.

Oprócz listy nieobsługiwanych funkcji dostępnych w oficjalnej witrynie SQLite, następująca witryna typu wiki zawiera listę innych nieobsługiwanych funkcji: SQLite — nieobsługiwane SQL

Oczekiwane dane wejściowe

Nazwa	Typ	Opis
Table1	Tabela danych	Wejściowy zestaw danych1
Tabela 2	Tabela danych	Wejściowy zestaw danych2
Tabela 3	Tabela danych	Wejściowy zestaw danych3

Parametry modułu

Nazwa	Zakres	Typ	Domyślny	Opis
SQL skryptu zapytania	dowolny	Streamreader		SQL query

Dane wyjściowe

Nazwa	Typ	Opis
Zestaw danych wyników	Tabela danych	Wyjściowy zestaw danych

Wyjątki

Wyjątek	Opis
Błąd 0001	Wyjątek występuje, jeśli nie można odnaleźć co najmniej jednej określonej kolumny zestawu danych.
Błąd 0003	Wyjątek występuje, jeśli co najmniej jeden wejściowy zestaw danych ma wartość null lub jest pusty.
Błąd 0069	SQL logiki lub brakująca baza danych

Aby uzyskać listę błędów specyficznych dla modułów programu Studio (wersja klasyczna), zobacz Machine Learning Kodów błędów.

Aby uzyskać listę wyjątków interfejsu API, zobacz Machine Learning API REST Error Codes (Kody błędów interfejsu API REST).

Zobacz też

Manipulacji
Przekształcanie danych
Lista modułów A–Z