Applicare la trasformazione SQL

Esegue una query SQLite su set di dati di input per trasformare i dati

Categoria: Trasformazione/Manipolazione dei dati

Panoramica del modulo

Questo articolo descrive come usare il modulo Applica trasformazione SQL in Machine Learning Studio (versione classica) per specificare una query SQL su un set di dati o set di dati di input.

SQL è utile quando è necessario modificare i dati in modi complessi o rendere persistenti i dati per l'uso in altri ambienti. Ad esempio, usando il modulo Applica SQL trasformazione, è possibile:

• Creare tabelle per i risultati e salvare i set di dati in un database portabile.

• Eseguire trasformazioni personalizzate sui tipi di dati oppure creare aggregazioni.

• Eseguire le istruzioni di query SQL per filtrare o modificare i dati e restituire i risultati della query come tabella dati.

Che cos'è SQLite?

SQLite è un sistema di gestione di database relazionali di dominio pubblico contenuto in una libreria di programmazione C. SQLite è spesso scelto come database incorporato per l'archiviazione locale nei browser Web.

SQLite è stato progettato nel 2000 per la Marina degli Stati Uniti, per supportare le transazioni senza server. Si tratta di un motore di database indipendente che non presenta un sistema di gestione e pertanto non richiede interventi di configurazione o amministrazione.

Come configurare apply SQL Transformation

Il modulo può accettare come input fino a tre set di dati. Quando si fa riferimento ai set di dati connessi a ogni porta di input, è necessario usare i nomi t1, t2e t3. Il numero di tabella indica l'indice della porta di input.

Il parametro rimanente è una query SQL che usa la sintassi SQLite. Il modulo supporta tutte le istruzioni standard della sintassi SQLite. Per un elenco di istruzioni non supportate, vedere la sezione Note tecniche.

Sintassi e utilizzo generali

• Quando si digitano più righe nella SQL di testo Script, usare un punto e virgola per terminare ogni istruzione. In caso contrario, le interruzioni di riga vengono convertite in spazi.

  Ad esempio, le istruzioni seguenti sono equivalenti:

  SELECT   
  *   
  from   
  t1;  
  

  SELECT * from t1;  
  

• È possibile aggiungere commenti usando all'inizio -- di ogni riga o racchiudendo il testo usando /* */.

  Ad esempio, questa istruzione è valida:

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

• Se un nome di colonna duplica il nome di una parola chiave riservata, l'evidenziazione della sintassi viene applicata al testo all'interno della SQL di testo Script. Per evitare confusione, è necessario racchiudere i nomi di colonna tra parentesi quadre (per seguire la convenzione Transact-SQL) o backticks o virgolette doppie (convenzione di SQL ANSI).

  Ad esempio, nella query seguente sul set di dati Blood Donation, Time è un nome di colonna valido, ma è anche una parola chiave riservata.

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

  Se si esegue la query così come è, la query potrebbe restituire i risultati corretti, ma a seconda del set di dati potrebbe restituire un errore. Ecco alcuni esempi di come evitare il problema:

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

  Nota

  L'evidenziazione della sintassi rimane sulla parola chiave anche dopo essere stata racchiusa tra virgolette o parentesi quadre.

• SQLite non fa distinzione tra maiuscole e minuscole, ad eccezione di alcuni comandi con varianti con distinzione tra maiuscole e minuscole con significati diversi (GLOB e glob).

Istruzione SELECT

Nell'istruzione SELECT i nomi di colonna che includono spazi o altri caratteri non consentiti negli identificatori devono essere racchiusi tra virgolette doppie, parentesi quadre o caratteri backtick (').

Ad esempio, questa query fa riferimento Two-Class set t1di dati Iris in , ma un nome di colonna contiene un carattere non consentito, quindi il nome della colonna è racchiuso tra virgolette.

SELECT class, "sepal-length" FROM t1;  

È possibile aggiungere una clausola WHERE per filtrare i valori nel set di dati.

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

La sintassi SQLite non supporta la parola TOP chiave , usata in Transact-SQL. È invece possibile usare la parola chiave LIMIT o un'istruzione FETCH .

Ad esempio, confrontare queste query sul set di dati 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;  

Join

Gli esempi seguenti usano il set di dati relativo alle valutazioni dei ristoranti sulla porta di input corrispondente a t1 e il set di dati relativo alle caratteristiche dei ristoranti sulla porta di input corrispondente a t2.

L'istruzione seguente unisce due tabelle per creare un set di dati che combina le caratteristiche dei ristoranti specificate con le valutazioni medie per ogni ristorante.

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;  

Funzioni di aggregazione

Questa sezione fornisce esempi di base di alcune funzioni di aggregazione SQL comuni, usando SQLite.

Le funzioni di aggregazione attualmente supportate sono: AVG, COUNT, MAX, MIN, SUM, TOTAL.

La query seguente restituisce un set di dati contenente l'ID del ristorante e la valutazione media per tale ristorante.

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

Uso delle stringhe

SQLite supporta la doppia barra verticale come operatore per la concatenazione delle stringhe.

L'istruzione seguente crea una nuova colonna concatenando due colonne di testo.

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

COALESCE e CASE

COALESCE valuta più argomenti, in ordine, e restituisce il valore della prima espressione che non restituisce NULL.

Ad esempio, questa query sul set di dati Steel Annealing Multi-Class restituisce il primo flag non Null da un elenco di colonne che si presuppone abbiano valori che si escludono a vicenda. Se non viene trovato alcun flag, viene restituita la stringa "none".

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

L'istruzione CASE è utile per testare i valori e restituire un nuovo valore in base ai risultati valutati. SQLite supporta la sintassi seguente per CASE le istruzioni :

• CASE WHEN [condizione] THEN [espressione] ELSE [espressione] END

• CASE [espressione] WHEN [valore] THEN [espressione] ELSE [espressione] END

Si supponga, ad esempio, di aver usato in precedenza il modulo Converti in valori indicatore per creare colonne di funzionalità impostate contenenti valori true-false. La query seguente comprime i valori in più colonne di funzionalità in una singola colonna multivalore.

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;  

Esempio

Per un esempio di come questo modulo può essere usato negli esperimenti di Machine Learning, vedere questo esempio nel Azure AI Gallery:

• Applica SQL trasformazione: usa il set di dati Restaurant Ratings, Restaurant Features e Restaurant Customers per illustrare join semplici, istruzioni select e funzioni di aggregazione.

Note tecniche

Questa sezione contiene informazioni dettagliate sull'implementazione, suggerimenti e risposte alle domande frequenti.

• Un input è sempre necessario sulla porta 1.

• Se il set di dati di input include nomi di colonne, questi ultimi verranno usati nelle colonne del set di dati di output.

  Se il set di dati di input non ha nomi di colonna, i nomi delle colonne nella tabella vengono creati automaticamente usando la convenzione di denominazione seguente: T1COL1, T1COL2, T1COL3 e così via, dove i numeri indicano l'indice di ogni colonna nel set di dati di input.

• Per gli identificatori di colonna che contengono uno spazio o altri caratteri speciali, SELECT racchiudere sempre l'identificatore di colonna tra parentesi quadre o virgolette doppie quando si fa riferimento alla colonna nelle clausole o WHERE .

Istruzioni non supportate

Anche se supporta gran parte dello standard ANSI SQL, SQLite non include molte funzionalità supportate dai sistemi di database relazionale in commercio. Per altre informazioni, vedere SQL come comprensibile da SQLite. Tenere inoltre presenti le restrizioni seguenti quando si creano SQL istruzioni:

• SQLite usa la tipizzazione dinamica dei valori anziché assegnare un tipo a una colonna, come avviene nella maggior parte dei sistemi di database relazionali. È scarsamente tipizzato e consente la conversione implicita del tipo.

• LEFT OUTER JOIN viene implementato, ma non o RIGHT OUTER JOINFULL OUTER JOIN.

• È possibile usare istruzioni RENAME TABLE e ADD COLUMN con il comando ALTER TABLE, ma altre clausole non sono supportate, tra cui DROP COLUMN, ALTER COLUMN e ADD CONSTRAINT.

• È possibile creare una vista in SQLite, ma successivamente le viste sono di sola lettura. Non è possibile eseguire un'istruzione DELETE, INSERT o UPDATE su una vista. È tuttavia possibile creare un trigger che viene attivato al tentativo di eseguire operazioni DELETE, INSERT o UPDATE su una vista ed eseguire altre operazioni nel corpo del trigger.

Oltre all'elenco delle funzioni non supportate disponibili nel sito ufficiale di SQLite, il wiki seguente fornisce un elenco di altre funzionalità non supportate: SQLite - Non supportato SQL

Input previsti

Parametri del modulo

Output

Eccezioni

Per un elenco degli errori specifici dei moduli di Studio (versione classica), vedere Machine Learning codici di errore.

Per un elenco delle eccezioni API, vedere Machine Learning di errore dell'API REST.

Vedi anche

Manipolazione
Trasformazioni dei dati
Elenco moduli A-Z