etl-estrazione-trasformazione-caricamento

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.

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 creato manualmente da 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 ad 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 che ne ricalca l’intestazione

CREATE TABLE StagingClienti(
    CodiceCliente INT Not Null Primary Key,
    Denominazione VARCHAR(200),
    DataNascita VARCHAR(200),
    RegioneResidenza VARCHAR(200));

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

BULK INSERT StagingClienti
FROM ‘C:\Users\ianto\Desktop\Clienti.csv’
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ‘;’,
ROWTERMINATOR = ‘\n’,
TABLOCK );

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.

 

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:

CodiceCliente Denominazione DataNascita RegioneResidenza
1 Nicola, Iantomasi 17/10/1980 Lombardia

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 il seguente 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 porzioni distinte della stringa 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:

CodiceCliente Nome Cognome DataNascita RegioneResidenza
1 Nicola Iantomasi 17/10/1980 Lombardia

 

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 presente in precedenza. La query precedente può essere affinata per 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.

 

Articoli correlati al processo di ETL

In questa pagina trovi un articolo su come scrivere query di inserimento, aggiornamento e cancellazione su un database.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.

Torna su