ETL: extract transform e load su Sql Server

Con ETL si intende l’insieme delle tre operazioni necessarie per popolare una base di dati: estrazione, trasformazione e caricamento, dall’inglese extract, load e transform. In questo articolo vedremo un esempio completo di ETL sul database relazionale SQL Server che effettua nell’ordine le seguenti operazioni:

  • estrazione dei dati da un file con estensione .csv
  • trasformazione dei dati per adattarli allo schema del database
  • caricamento sulla tabella target

Oggi è possibile trovare i dati in molteplici formati e tipologie di archivi: csv, xlsx, json, xml, database relazionali e non relazionali, pagine web, archivi cloud, eccetera. In questo articolo vedremo uno dei casi più comuni: i file csv, come questo mostrato in figura che potete scaricare dal mio repository Github.

esempio file csv

I file possono provenire dalle fonti più disparate: dall’esportazione dell’output generato da un particolare software, da un archivio web come Github, oppure dalla creazione manuale di un utente. Ritengo importante osservare che in generale la struttura del file non coincide con la struttura della tabella del database dove le informazioni devono essere salvate. Potrebbero esserci ad esempio dei formati differenti per le date o per i numeri, dei campi in più, oppure più informazioni condensate in un solo campo.

Estrazione dei dati

L’approccio comunemente usato consiste nel dividere la procedura ETL in più fasi. La prima si occuperà semplicemente di trasferire le informazioni dal file a un database “di parcheggio“, comunemente chiamato Staging Area, all’interno del quale i dati saranno trattati con l’SQL.

Per effettuare questa prima fase di caricamento si hanno a disposizione vari strumenti. In questo articolo vedremo un particolare utilizzo del linguaggio T-SQL, il dialetto dell’SQL dell’RDBMS Microsoft Sql Server. La creazione della tabella è necessaria solo nel caso della prima importazione. Per il file mostrato nella figura precedente, scriveremo questo codice:

CREATE TABLE StagingClienti(
 CodiceCliente INT NOT NULL PRIMARY KEY,
 Denominazione VARCHAR(200),
 DataNascita VARCHAR(50),
 RegioneResidenza VARCHAR(50) );

Le tabelle nella Staging Area sono generalmente create senza nessun vincolo, ad eccezione della presenza di una chiave primaria. L’unico obiettivo di questa fase è trasferire i dati all’interno del database, demandando tutti i controlli alle fasi successive.

Vediamo ora il codice T-SQL per eseguire il caricamento tramite una BULK INSERT

BULK INSERT StagingClienti
FROM 'C:\Desktop\Clienti.csv'
WITH (
 FIRSTROW = 2,
 FIELDTERMINATOR = ';',
 ROWTERMINATOR = '\n');

A meno che non si abbiano particolari necessità sull’archiviazione dei dati di origine, ogni successiva importazione dovrà essere proceduta dall’eliminazione dei dati caricati in precedenza. Ciò è eseguibile tramite l’istruzione SQL Truncate Table.

Trasformazione dei dati

Come anticipato nel paragrafo precedente, la struttura del file potrebbe essere differente da quella della tabella. Il database infatti è progettato e realizzato in ottica di normalizzazione della base dati. Osserviamo a tal proposito la prima riga del file:

CodiceClienteDenominazioneDataNascitaRegioneResidenza
1Nicola, Iantomasi17/10/1980Lombardia

La colonna Denominazione contiene due informazioni che su un database normalizzato dovranno essere divise in due colonne Nome e Cognome. Inoltre la data non è nel formato ISO (a questo link trovi un approfondimento https://www.ionos.it/digitalguide/siti-web/programmazione-del-sito-web/iso-8601/), ma in quello italiano. La trasformazione può avvenire tramite questo codice T-SQL:

SELECT CodiceCliente,
 TRIM(SUBSTRING(
        Denominazione, 
        1, 
        CHARINDEX(',', Denominazione)-1)
       ) AS Nome,
 TRIM(SUBSTRING(
        Denominazione, 
        CHARINDEX(',', Denominazione) + 1, 
        1000)
       ) AS Cognome,
 CONVERT(DATE, 
       DataNascita, 
       103) AS DataNascita,
 RegioneResidenza
FROM StagingClienti;

In particolare sono utilizzate le funzioni:

  • TRIM per eliminare gli spazi bianchi a destra e a sinistra del testo;
  • SUBSTRING per estrarre due porzioni distinte a partire dall’unica colonna Denominazione;
  • CHARINDEX per individuare dinamicamente la presenza del carattere virgola “,” all’interno della Denominazione;
  • CONVERT per convertire il testo in un campo di tipo Date.

In questo articolo trovi ulteriori approfondimenti sulle funzioni SQL.

Vediamo come appare una riga dell’output della query di trasformazione:

CodiceClienteNome
Cognome		DataNascitaRegioneResidenza
1NicolaIantomasi1980-10-17Lombardia

ETL, l’ultimo step: caricamento dei dati

Il caricamento dei dati all’interno della tabella target avviene solitamente in due step: l’aggiornamento dei record già esistenti nella tabella e l’inserimento dei nuovi. Questa operazione deve essere eseguita all’interno di una stessa transazione. La tipologia di record da aggiornare o da inserire sarà discriminata tramite l’utilizzo delle chiavi primarie.

L’aggiornamento utilizzerà come subquery il codice di trasformazione che abbiamo scritto in precedenza, eventualmente materializzato in una tabella temporanea #StagingClientiTrasformata se le dimensioni lo permettono.

UPDATE db
SET db.Nome = Fl.Nome,
    db.Cognome = Fl.Cognome,
    db.DataNascita = Fl.DataNascita,
    db.RegioneResidenza = Fl.RegioneResidenza
FROM CorsoSql.Dbo.Clienti AS Db
INNER JOIN #StagingClientiTrasformata AS Fl
     ON Db.NumeroCliente = Fl.CodiceCliente;

In questo modo vengono aggiornate sempre tutte le righe, anche se in molti casi si tratterà di sovrascrivere lo stesso valore già presente in precedenza. Questa query può essere comunque affinata in modo da aggiornare soltanto le righe oggetto di variazione.

L’inserimento invece avverrà combinando le clausole insert e not exists.

INSERT INTO CorsoSql.Dbo.Clienti
         (NumeroCliente, 
          Nome,
          Cognome, 
          DataNascita,
          RegioneResidenza,
          CodiceFiscale)
SELECT CodiceCliente, 
       Nome, 
       Cognome, 
       DataNascita, 
       RegioneResidenza
FROM   #StagingClientiTrasformata AS s
WHERE  
 NOT EXISTS (SELECT *
             FROM   CorsoSql.Dbo.Clienti AS c
             WHERE  s.CodiceCliente = c.NumeroCliente);

Su Sql Server le transazioni non sono implicite, occorrerà dunque crearne e chiuderne una per gestire l’aggiornamento e l’inserimento come un unico blocco atomico.

Continua a imparare

Torna in alto
Torna su