Conclusa la fase progettuale e ottenuti gli schemi logici delle due basi di dati è stato possibile procedere con l’implementazione vera e propria.
A questo scopo è stato scelto il database manager system open source MySql. Le ragioni di questa scelta sono sostanzialmente due: la licenza gra- tuita del software e la facilità del suo utilizzo. La licenza gratuita permette di usufruire di questo software a chiunque, garantendo la standardizzazione del metodo di risoluzione della nostra problematica. In altre parole, ci permette di realizzare basi di dati di appoggio o di secondo livello a prescindere dal DBMS utilizzato dall’azienda. MySql potrebbe infatti essere utilizzato sia che l’azienda utilizzi sistemi Oracle sia che utilizzi sistemi Microsoft, basta porre attenzione alla creazione di un processo etl valido e coerente. Avrem- mo potuto utilizzare Oracle, database manager system utilizzato da Asa, ma avremmo dato un’accezione troppo specialistica al nostro lavoro.
Seconda ragione per l’adozione di MySql è da ricercarsi nella semplicità del suo utilizzo. Essendo uno dei DBMS più utilizzati al mondo vari team di sviluppatori hanno creato nel tempo una comoda interfaccia ricca di funzioni e strumenti per l’amministrazione delle basi di dati. Tale interfaccia prende il nome di MySqlWorkbench ed è facilmente e gratuitamente scaricabile dal sito di MySql. Questo strumento ci ha garantito la creazione fisica delle basi di dati semplicemente utilizzando un tool grafico. È bastato creare graficamente
uno schema (Fig. 4.12), seguendo gli elementi prodotti dalla progettazione logica, e utilizzare un’apposito comando per generare il codice SQL di cui avevamo bisogno.
Per non appesantire la documentazione del lavoro svolto il codice SQL non è stato incluso. Vale però la pena di dire che è stato generato velocemente e senza difficoltà.
Capitolo 5
PROGETTAZIONE E
REALIZZAZIONE DEI
PROCESSI DI ETL
Iniziamo con il capire cosa significa l’acronimo ETL. Le tre lettere sono le iniziali dei tre termini in lingua inglese Extract, Transform, Load. Con questi tre termini si fa riferimento alle tre funzioni che caratterizzano le procedure ETL, vale a dire: l’estrazione, la trasformazione e il caricamento di dati tra due o più basi di dati differenti, siano essi operazionali o decisionali (Data Mart, Data Warehouse).
Lo scopo principale di un’applicazione ETL è rendere disponibili i da- ti raccolti in azienda, provenienti dalle fonti più disparate, sia a Database transazionali, sia a soggetti incaricati di assumere le decisioni, nella forma e secondo le tempistiche più idonee a supportare il processo decisionale. I software di ETL permettono, infatti, di leggere i dati dalla loro fonte, ripulirli
e formattarli in modo uniforme, per poi caricarli nel database di destinazione per essere utilizzati [Pentaho 14].
Il processo ETL come suggerito dal nome è formato da più fasi.
1. Extract: questo elemento è responsabile dell’estrazione dei dati dalla sorgente. I dati vengono estratti da delle basi di dati più o meno strut- turate, da piattaforme informatiche gestionali, da fogli elettronici o flat file.
2. Transform: I dati estratti vengono elaborati per essere trasformati in dati consolidati e rispondenti alle specifiche dettate dalla struttura della base di dati di destinazione. E’ il più complicato degli elementi ETL. Questa fase consiste, ad esempio, nel:
• Selezionare solo quei dati che sono di interesse per il sistema. • Normalizzare i dati (per esempio eliminando i duplicati). • Tradurre dati codificati.
• Derivare nuovi dati calcolati.
• Eseguire accoppiamenti (join) tra dati recuperati da differenti tabelle.
• Raggruppare i dati.
3. Load: Al termine dell’operazione di trasformazione, i dati appena ela- borati vengono caricati nel database di destinazione.
Definita la struttura di un processo ETL, è poi possibile programmar- ne la tempistica. Tale processo infatti può essere eseguito una tantum, o
ciclicamente rispettando specifici intervalli temporali (mensilmente, settima- nalmente o quotidianamente).
La scelta sullo strumento da utilizzare per la fase realizzativa è ricaduta su Kettle, un applicativo open source della suite Pentaho per il Data Inte- gration. Data la nostra scelta di implementare tutto il sistema utilizzando software a licenza gratuita, questo strumento si adatta perfettamente alle no- stre esigenze. Il programma mette a disposizione un’interfaccia grafica molto curata ed intuitiva con la quale è possibile creare sia job che trasformazioni sui dati. Selezionato il tipo di processo (Job o Trasformation) che vogliamo costruire, si sceglie tra un’ampia gamma di elementi disponibili che costitui- ranno gli step del flusso di dati. Questi elementi effettuano operazioni sui dati senza che si debba scrivere codice. Kettle è uno strumento User-friendly e facilita notevolmente il nostro lavoro.
Nelle sezioni seguenti parleremo dei processi che sono necessari alla riso- luzione del nostro particolare caso. Le fasi di Etl, seppur obbligatorie nel risolvere problematiche di gestione dei flussi di dati, possono differire anche di molto tra loro a seconda dei casi da trattare. Ogni scenario presenta osta- coli diversi e di conseguenza necessità di soluzioni diverse. Non è dunque possibile effettuare una standardizzazione su questa componente, ma il no- stro obiettivo si limita a voler presentare una metodologia di approccio al problema.
5.1
Processo di caricamento delle basi di dati
dell’infrastruttura idrica
Trattiamo prima il passaggio di dati tra il sistema informativo territoria- le(SIT) di Asa e la base di dati sull’infrastruttura dell’acquedotto. Vedremo in seguito che data la natura molto simile delle due basi di dati, i processi rea- lizzati per il caricamento della base di dati sulla fognatura avranno una strut- tura simile. La progettazione e la realizzazione di ogni singola trasformazione saranno affrontate parallelamente per comodità espositiva.
L’obiettivo del processo che andiamo a realizzare è il caricamento all’inter- no della base di dati dei dati disponibili nel sistema informativo di Asa, anche se, come vedremo, alcuni attributi richiesti rimarranno nulli per mancanza di informazione sul sistema di origine.
Ogni tabella, per essere caricata, richiede un processo apposito ed ognuno di questi processi ha un elemento che specifica la fonte dalla quale prelevare il dato, un elemento con cui si indicano le trasformazioni da effettuare ed infine un elemento che indica la destinazione dei dati appena trasformati.
Presentiamo di seguito i tre passaggi da rispettare per concludere un basilare processo ETL di caricamento con Kettle.
1. Definizione dell’elemento di input per selezionare un file o una tabella sorgente. L’elemento di input determina l’inizio del flusso di dati. E’ sufficiente scegliere tra i vari elementi di input quello adatto alla nostra fonte e configuralo in modo tale da selezionare solo i dati che vogliamo prelevare.
2. Inserimento durante il flusso degli elementi preposti ad eseguire trasfor- mazioni sui dati, conformandoli alle specifiche della tabella di destina- zione. Per effettuare tali trasformazioni abbiamo a disposizione un set molto ampio di elementi, sia per effettuare conversioni tra tipi diversi di dato sia per effettuare modifiche complesse ed eventuali aggregazioni. 3. Definizione dell’elemento di output con cui specificare la destinazione
del flusso di dati A questo scopo l’elemento che utilizzeremo più spesso sarà l’“Insert-Update”. Permette sia di inserire record non ancora pre- senti sia eventualmente di aggiornare quelli già caricati [Pentaho 14]. Questa funzionalità è molto utile per mantenere la nostra base di dati di destinazione aggiornata in seguito a variazioni avvenute nelle fon- ti e permette di eseguire ciclicamente la trasformazione senza incap- pare in errori di duplicazione della Primary Key. Le nostre tabelle di destinazione sono gestite dal servizio MySql, è pertanto necessario configurare quest’ultimo elemento inserendo i parametri per la connes- sione al Database. La connessione è possibile tramite il driver JDBC “mySqlConnector” disponibile sul sito di MySql.
Confrontando lo schema del SIT Asa con quello del nostro database obiet- tivo si comprende che solo alcune informazioni possono essere sfruttate. In- fatti il SIT Asa, essendo stato progettato per uno scopo diverso da quello che noi ora ci proponiamo, contiene dati a livello di dettaglio molto inferiore rispetto a quello richiesto dall’AiT.
Seppur questo limite non permetta di completare il caricamento non ci impedisce di estrarre le informazioni che individuano tutti gli impianti, tutte
le reti e tutti i loro collegamenti. Quindi ci limitiamo a importare i codici identificativi (Primary Key) e alcuni attributi descrittivi lasciando ad ela- borazioni successive la raccolta di tutti gli altri attributi richiesti. Asa sta lavorando strenuamente per raccogliere i dati mancanti, e quando saranno disponibili basterà effettuare delle semplici integrazioni.
Per chiarire la struttura della fonte dei dati Inseriamo nella Figura 5.2 un estratto dello schema logico del SIT di Asa. Le tabelle evidenziate in blu sono quelle che costituiranno la fonte principale del processo ETL di popolamento. Analizziamo tabella per tabella tutti i processi coinvolti nel caricamento dei dati.
1. Caricamento dei dati relativi agli Impianti (Fig. 5.2)
Si tratta del processo più complesso poichè le grane tra il sistema informativo di origine e quello di destinazione sono diverse.
Nel SIT è presente un’unica tabella “A_IMPIANTI” che raccoglie tutte le tipologie di impianto, tale tabella ha un attributo che ne descrive il tipo e che permette di distinguerle tra loro. La base di dati sull’ acquedotto, al contrario, ha sia una tabella “Impianto” sia una tabella per ogni tipologia di impianto. Per riuscire ad effettuare il caricamento siamo obbligati ad inserisce un controllo sul flusso di dati. In Kettle l’elemento da utilizzare per queste forme di controllo è lo “Switch-case”. Si estrae i record dalla tabella “A_IMPIANTI” e si importano nella tabella Impianti della base di dati . Successivamente l’elemento “Swith- Case” analizzando l’attributo “TIPO” della tabella di origine, individua il tipo di impianto descritto dal record e divide il flusso unico di input
Figura 5.1: Estratto dello schema logico del sistema informativo territoriale di Asa
Figura 5.2: Processo ETL per la tabella Impianti
in piu flussi di output tanti quanti sono le diverse tipologie di impianto. Ognuno di questi flussi ha una distinta tabella di destinazione.
2. Caricamento dati sulle tratte della rete (Fig. 5.3)
Il processo per la tabella tratte è formato da più elementi, le semplici operazioni di estrazione e caricamento non sono sufficienti.
Questo passaggio deve prevedere anche una trasformazione, infatti l’at- tributo “materiale” è espresso in modi diversi nelle due basi di dati. Nel SIT si utilizza una stringa di caratteri con il nome del materiale men- tre nella base di dati dell’acquedotto, in linea con le esigenze di AiT, è richiesto l’utilizzo di un indice. Nella documentazione di NetSIC viene indicato per ogni tipo di materiale il codice da utilizzare. Il codice, chiamato “id_Materiale” deve essere un intero di quattro byte. Per trasformare l’informazione nel formato richiesto utilizziamo l’elemento “Value Mapper” che permette di cambiare il valore di un attributo a seconda del valore di origine [Pentaho 14].
Ad esempio, se il “Value Mapper” trova la stringa con valore “ferro” nell’attributo materiale della riga di input, la sostituisce con il codice
Figura 5.3: Processo ETL per la tabella Tratte e tabella Reti
relativo che abbiamo impostato nel pannello di controllo dell’elemento, nel caso del ferro sarà “13”.
Inseriamo anche un’altra trasformazione per limitare i valori del campo “TIPORETE” da quattro, presenti nel SIT, a tre, come previsto dalla documentazione di AiT.
3. Caricamento dati sulle reti (Fig. 5.3)
Per caricare le Reti non abbiamo a disposizione una tabella dedicata nel SIT. Anzitutto estraiamo tutti i codici rete presenti sull’attributo “NUMRET” dalla tabella “A_TRATTE”. Poichè più tratte apparten- gono ad una rete il risultato sarà una tabella con i codici reti ripetuti tante volte quante solo le tratte che ne fanno parte. Risolviamo inse- rendo l’elemento “MEMORY GROUP BY” che, una volta impostato, raggruppa i record su codice rete permettendoci di ottenere una riga per ogni rete. Abbiamo impostato il Group by affinchè ci mostri per ogni rete sia la tipologia sia la somma delle lunghezze delle tratte che ne fanno parte, un informazione utile richiesta da AiT.
Una volta identificate le reti distinguiamo le adduttrici dalle distribu- zioni con lo “Switch-Case” sfruttando l’attributo Tipologia mantenuto dal Group by e inseriamole nalla tabella dedicata.
Figura 5.4: Processo ETL per la tabella Nodi
Figura 5.5: Processo ETL per la tabella Comuni 4. Caricamento dati sui nodi della rete (Fig. 5.4)
Il processo per i nodi è molto semplice. Estraiamo il numero identifica- tivo del nodo dalla tabella “A_NODI” del SIT e carichiamolo nella ta- bella “nodo” della nostra base di dati sull’acquedotto. Questo processo non necessita di alcuna trasformazione.
5. Caricamento dati sui comuni serviti (Fig. 5.5)
Ultimo processo di ETL eseguibile riguarda il caricamento dei codici istat dei comuni serviti da Asa e delle relazioni di servizio tra questi e le reti. Estraiamo quindi dalla tabella “RETI_ATO” del SIT sia tutti i codici dei comuni sia la rete che li serve. Per rispettare il vincolo di integrita carichiamo i dati prima nella tabella “comune” della base di dati e successivamente nella tabella “comune_servito”.
Gli elementi contraddistinti dall’immagine di un cartello di stop servono per controllare l’ordine di esecuzione delle trasformazioni. Il controllo sull’or- dine è necessario per rispettare i vincoli di integrità referenziale già impostati all’interno dela base di dati. Se caricassimo prima le tratte e successivamente
i nodi che ne definiscono il punto di inizio e di fine violeremmo il vincolo di integrità tra le due tabelle e l’intera trasformazione non verrebbe eseguita.
Concludiamo il processo ETL relativo alla base di dati dell’acquedotto inserendo tutte le singole trasformazioni create all’interno di un job. L’u- tilizzo del job ci garantisce la possibilità di effettuare ciclicamente tutte le trasformazioni con un intervallo di tempo che piu si adatta alle esigenze dell’utilizzatore [Pentaho 14].
Abbiamo caricato tutti i dati del SIT Asa compatibili con la struttura della base di dati obiettivo. Ci siamo limitati a mostrare come caricare quelli più rappresentativi. Per i dati mancanti lasciamo il compito ad elaborazioni successive. Tali elaborazioni saranno fattibili solo quando Asa avrà concluso il censimento di tutta l’infrastruttura di acquedotto seguendo le linee guida dell’AiT. Quando infatti Asa disporrà di ulteriori fonti contenenti le integra- zioni informative richieste sarà sufficiente creare altri semplici processi sulla falsa riga di quelli mostrati per portare a termine un’esaustiva fase di ETL.
I processi ETL utilizzati per il caricamento della base di dati sull’infra- struttura fognaria sono molto simili a quelli utilizzati per l’acquedotto. Es- sendo le strutture delle due basi di dati molto simili tra loro non si richiede l’utilizzo di nuovi elementi.
Analizziamo ognuno dei quattro processi coinvolti.
1. Caricamento dati degli Impianti dell’infrastruttura fognaria (Fig. 5.6)
Impostiamo l’elemento iniziale di estrazione dei dati dal SIT Asa. Es- sendo presente un vincolo di integrità referenziale tra Impianti e nodi,
Figura 5.6: Processo ETL per la tabella Impianti
dopo l’estrazione inseriamo un elemento di blocco che subordina l’ese- cuzione dei successivi elementi al caricamento della tabella nodi. La presenza del vincolo di integrità ci tutela nei confronti di inserimenti di dati non coerenti, infatti se nel file di Input fossero presenti im- pianti localizzati in un nodo non ancora caricato il DBMS rifiuterebbe l’esecuzione dell’intero processo.
Sempre per soddisfare i vincoli di integrità imposti tra la tabella “padre” impianti e le sue tabelle “figlie” dobbiamo prima inserire tutti i codici identificativi degli impianti nella tabella Impianti e successivamente ag- giungere gli stessi codici nelle tabelle dedicate (depuratori, sollevamenti e scaricatori di piena) effettuando un controllo sull’attributo tipologia. Per il caricamento utilizziamo sempre l’elemento ”Insert-Update” che valuta autonomamente tra i record provenienti dalla sorgente cosa è ne- cessario inserire e cosa invece è gia stato inserito evitando duplicazioni di chiavi primarie.
2. Caricamento dati su tratte e reti fognarie (Fig. 5.7)
Figura 5.7: Processo ETL per la tabella Tratte e la tabella Reti
Figura 5.8: Processo ETL per la tabella Nodi
stessi elementi posizionati nello stesso ordine, cambiano ovviamente le impostazioni delle sorgenti e delle destinazioni e i parametri per l’elemento “switch-case”, ma a livello concettuale non ci sono differenze. 3. Caricamento dati sui nodi (Fig. 5.8)
Stesso discorso per il caricamento dei nodi. Ricordiamo che questo processo deve essere il primo ad essere eseguito a causa dei tre vincoli di integrità costituiti proprio tra la tabella nodi e le tabelle Impianti e Tratte. Il campo “numNodo” di Impianti e i campi “numNodoIniziale” e “numNodoFinale” di Tratte riferiscono al codice del nodo che costituisce chiave primaria della tabella Nodi.
4. Caricamento dati sui comuni serviti (Fig. 5.9)
Concludiamo il processo aggiungendo la trasformazione per il carica- mento della tabella comuni e della tabella comuni_serviti che indica
Figura 5.9: Processo ETL per la tabella Comuni per ogni rete quali comuni vengono serviti.
Anche in questo caso possiamo inserire tutto in un Job per stabilire l’intervallo di tempo che ne regolerà l’esecuzione.