talend_TOS_MDM spago_logo

Dette innlegget viser hvordan man kan gå fra noen Excel-regneark til en online interaktiv presentasjon med bruk av open source BI-verktøy, nærmere bestemt Talend Open Studio for Data Integration (TOS/DI) og SpagoBI Open Studio og online business intelligence server.

Utgangspunktet er et Excel-regneark som Avinor publiserer en gang i måneden med oversikt over landets flytrafikk. Excel-arket som inneholder Avinors Månedstatistikk ble forøvrig utviklet av undertegnede for en del år tilbake, noe som gjør det både naturlig og en smule nostalgisk å bruke tallmaterialet i dette eksemplet.  Dette blogginnlegget omfatter del 1 av eksperimentet: Overføring av data fra Avinors Excel-filer til en MySql database via en ETL-jobb (ETL = Extract, Transform, Load) som vi lager i TOS/DI.

1. Forberedelser

Først laster vi ned Månedsstatistikken  fra Avinors nettsted, via denne lenken:

http://www.avinor.no/avinor/trafikk/10_Flytrafikkstatistikk

For å gjøre det enklere for oss selv, lagrer vi Excel-filene i en egen mappe, som vi f.eks. kaller Avinor.  Og siden filnavnene til Avinor varierer litt tilfeldig, endrer vi dem til følgende format: MånedÅr.xls – f.eks: Jan2012.xls, Feb2012.xls , Mar2012.xls osv.

Excel-filene er arbeidsbøker med samme oppsett: En fane med Hovedtall for måneden, en fane med oversikt over flypassasjerer, en fane med oversikt over flybevegelser, og en fane med oversikt over frakt og post.

Det første vi skal gjøre i  dette eksemplet er å gå gjennom alle filene, trekke ut oversiktstallene for flypassasjerer fra fane 2  og lagre disse i en MySql-tabell.  Som en del av forberedelsene  trenger vi derfor å opprette en MySql database til dette formålet, og en bruker med rettigheter til databasen.  Gitt at MySql er installert på testmaskinen vår, kan vi opprette dette lokalt med et par enkle kommandoer i et terminalvindu:

$ mysql -u root -pRotPassord

$ mysql > create database Avinor;

$ mysql > grant all on Avinor.* to avinorbruker@localhost identified by ‘BrukerPassord’;

Databasen har ingen tabeller ennå – de skal vi generere dynamisk med Talend TOS/DI. Neste forberedelse er derfor å laste ned og starte opp TOS/DI. Programmet lan lastes ned fra denne lenken:

http://www.talend.com/download.php

2. Definere jobben i TOS/DI

(Klikk på bildene for å vise full størrelse)

Neste skritt er å åpne TOS/DI og definere en ny ETL-jobb. Åpningsbildet i TOS/DI gir et overblikk over prosessen:

welcome

 

Så vi høyreklikker i venstre panel og velger «Create Job». Jobben gis et navn, f.eks: Luftfartsstatistikk. Bildet under viser den ferdig designede jobben:

jobbdesign1

Vi skal nå gå igjennom de ulike trinnene i ETL-prosessen i detalj. De ulike komponentene i prosessen finner vi i palett-oversikten i høyrepanelet. Vi trenger 4 komponenter for å utføre jobben:

  1.  tFileList (ligger under Files->Management og også under Orchestration)
    Denne komponenten blar igjennom en mappe og sender filnavnene til neste komponent.
  2.  tFileInputExcel (ligger under Files -> Input)
    Denne komponenten leser Excel-filen (hvis filnavn den får fra forrige komponent), og trekker ut de data vi definerer.
  3.  tMap (ligger under Processing)
    Denne komponenten «mapper» Excel-feltene til MySql-felter og legger til ekstra felter for måned og år, som den tar fra filnavnet.
  4.  tMysqlOutput (ligger under Databases -> MySQL)
    Denne komponenten lagrer  data i en spesifisert tabell i en MySql-database, og kan også opprette tabellen hvis den ikke finnes fra før.

For lettere å finne komponentene kan vi bruke søkefeltet øverst i palett-kolonnen:

tFileList

Vi drar hver komponent over til midt-feltet i TOS/DI, som så blir seende slik ut:

komponenter

Ved å klikke på teksten under hvert ikon, kan vi endre navnet på komponentene til noe mer meningsfullt, slik jeg har gjort i bildet med  den ferdige prosessen ovenfor.

Neste skritt er å definere hver komponent, og så lenke dem sammen. Vi begynner med tFileList, som jeg har omdøpt til Hent alle excel-filene i en mappe. Et dobbeltklikk på komponentens ikon gjør at fanen Component blir synlig i det nederste feltet. Her setter vi inn adressen til mappen med Excel-filene. Jeg har også satt opp en Filemask: «*.xls», slik at kun Excel-filer tas med.

del1_component

Dette er alt vi trenger for denne komponenten, og vi kan linke den til neste komponent ved å høyreklikke på ikonet og velge Row->Iterate, og så dra/peke den til neste komponent og slippe musen.  Dette gjør at for hver fil tFileList finner i mappen sender den avgårde noen variabler til neste komponent – bl.a. Filnavn og -adresse. Disse kan vi enkelt plukke opp ved å bruke TOS/DIs enkle funksjon: Ctrl+Space. La oss først dobbeltklikke på ikonet til komponenten, som nå heter Plukk ut riktig data fra hver fil. Vi får opp detalj-vindet til komponenten. Ved å klikke i feltet File name/Strem og klikke Ctrl+Space får vi opp en liste med variabler, og kan velge denne: Hent alle filene i en mappe.CURRENT_FILEPATH.

del2_component2

Dette setter inn denne variablen i feltet for filnavnet, som i bildet under. Som vist på bildet har jeg også klikket på det grønne +-ikonet og lagt til en Sheet i en Sheet list, dvs et regneark i Excel-boken. Og det er regneark 2 jeg skal bruke – som inneholder detaljer over passasjertrafikken til hver lufthavn i Norge. Siden regnearkene er null-indeksert, er dette ark nummer 1 i boken (første ark er ark null). Jeg ser i regnearket at det er 5 rader før dataene begynner, så jeg setter en Header på 5, og siden det er 67 rader med data ialt, setter vil en Limit på 67. Videre skal vi ha med data fra kolonne 1 til og med kolonne 16. (Dette ser vi i regnearket).

del2_component1

Nederst på bildet over, ser vi at det er valgt et Built-in Schema, og vi klikker de to prikkene i ikonet etter Edit Schema, slik at får opp en editor hvor vi kan definere hva de ulike feltene (dvs kolonnene i Excel-arket) skal hete. Basert på regnearket, lager vi dette Skjemaet:

schema

Siden regnearket er lagret med %-tegnet som en del av verdien for feltene som viser prosent-endringer,  har jeg satt disse feltene til å være type String, altså tekstfelt, for å unngå feilmeldinger. Som vi skal se, lagres ikke prosenttegnet i MySql-tabellen til slutt.

Merk: Det kan lønne seg å lagre skjemaet ved å klikke på mappe-ikonet med en blå pil under seg. Gi den et navn du kjenner igjen, og programmet tilføyer en xlm-endelse. Vi kan da hente opp igjen skjemaet for bruk i neste komponent.

Under Advanced Settings i fanene på venstresiden i Component-vinduet må vi også stille inn Advanced separator (for numbers) slik at komma separerer tusener og ingenting separerer desimaler. Etter litt prøving og feiling var det disse verdiene som ga riktig resultat.

del2_component3

Neste trinn er å konfigurere mappingen til MySql-tabellen i tMap-komponenten, som nå heter Legg til felter for år og måned. Først kobler vi den sammen med foregående komponent ved å høyreklikke på Excel-komponenten og velge Row->Iterate, og slippe musen over tMap-komponenten. Komponentene er nå forbundet med en linje, og under denne står det Row 1 (Main).  Når vi dobbeltklikker på tMap-komponenten kommer det opp et editor-vindu, hvor vi kan definere forholdet mellom variablene, og  legge til to nye for År og måned.  Slik ser editoren ut, ferdig konfigurert:

tMap1

Funksjonen Auto map øverst i høyre hjørne i editoren mapper elegant feltene slik vi vil ha dem. Dersom skjemaene ikke er riktige, kan vi laste inn det lagrede skjemaet fra tidligere ved å klikke på mappe-ikonene nederst i skjermbildet.

Nå trenger vi å legge til felter for År og Måned, så vi vet  hvilken tidsperiode tallene gjelder for. Til dette har TOS/DI en utmerket expression-builder som forenkler oppgaven betraktelig. Med editor-vinduet åpent klikker vi på det grønne + -ikonet nederst til høyre for å legge til et nytt felt, og kaller feltet Maned (greit å unngå norske bokstaver).  Vi lager den som type String og gir den en lengde på 3.  Med piltastene som ligger ved siden av det grønne +-tegnet kant vi flytte feltet opp til toppen av tabellen. I øverste høyre tabell kan vi nå klikke i expression-feltet, på et ikon med to prikker,  og opp kommer en expression builder:

expression_builder1

Vi velger StringHandling -> LEFT, og setter at vi skal velge de 3 første tegnene i en streng. Strengen skal være filnavnet i den Excel-filen vi prosesserer, og det er tilgjengelig gjennom en variabel som kommer til syne når vi setter markøren der hvor variablen skal sette inn og klikker Ctrl+space. Ved å velge tFileList_1_CURRENT_FILE, ser uttrykket ut som i bildet ovenfor, og vi kan klikke OK og har laget et flet som inneholder de tre første bokstavene i filnavnet, som er den aktuelle måneden skrevet med tre bokstaver.  Vi gjentar prosessen for å lage et nytt felt for År, kaller det Aar, flytter det til toppen av tabellen og åpner en ny expression builder. Denne gangen velger vi en uttrykk som gir de fire første tegnene av de siste 8 tegnene, slik:

StringHandling.LEFT(
StringHandling.RIGHT(((String)globalMap.get(«tFileList_1_CURRENT_FILE»)),8)
,4)

(Merk: skrives på én linje)

Dette er fordi de siste 4 tegnene i filnavnet er «.xls». Så ved å velge de 8 siste tegnene (med en RIGHT-funksjon), har vi fjernet måneds-bokstavene, og kan velge de 4 første tegnene av det vi har igjen (med en LEFT-funksjon) for å få årstallet. Vi klikke OK og Ok igjen for å lagre endringene i tMap-komponenten. Så kan vi høyreklikke denne og lenke den sammen med neste komponent ved å velge Row->NewOutput (Main).  Da gjenstår det bare å konfigurere siste komponent, som er en tMysqlOutput-komponent, som nå heter Lagre i Mysql. Vi dobbeltklikker på denne slik at detaljvinduet vises:

tMysqlOutput1

Her setter vi inn parameterne for databasen vi opprettet tidligere, og velger Create table if not exists, slik at vi oppretter en ny tabell med de navnet vi setter inn i feltet Table (her PaxPaned). Ved å klikke Sync columns tar vi med oss feltene fra forrige komponent. Hvis dette ikke fungerer, eller vi ønsker å gjøre endringer, kan vi åpne skjema-editoren og f.eks. laste inn det lagrede skjemaet vårt her.

Nå er jobben definert, og klar til å testes. Tast F6 eller klikk Run  i nederste panel, så kjøres jobben. Går alt bra ser det ca. slik ut:

jobbdesign

Her ser vi at 10 filer er prosessert. Sjekker vi Mysql-databsen, ser vi at det er opprettet en tabell PaxManed, som inneholder data over passasjerer til og fra norske lufthavner organisert per år og måned.

Siden dette tar mål av seg til å være et realistisk eksempel, sjekker vi databasen for å se at data er riktig lagret. Et søk på et par felter for Oslo Lufthavn gir følgende:

mysql_prosent

Som vi ser er det noe galt med November 2011. Sjekker vi Excel-filen, ser vi at regnearket er fullt av feil, siden Avinor har lagret det med absolutte lenker til mapper på egen filserver.  Siden dette ikke er noe vi kan rette opp selv, må vi slette November 2011 fra mappen og leve med et hull i tidsrekken, til evt. Avinor får rettet opp filen. Vi sletter likegodt hele databasen og kjører jobben på nytt. Men da kan vi også konvertere de feltene i databasen som viser prosentvis endring fra tekst til desimal – dvs. fra Java type String til Double. Dette gjør vi enkelt ved å legge til komponenten tConvertType, slik:

jobbdesign_med_konvertering Her har jeg endret navnet på komponenten til Konverter tekst til tall. Vi konfigurerer tConvertType ved å dobbeltklikke på ikonet, noe som bringer opp en skjema-editor:

konverter_shcema

Vi redefinerer feltene som inneholder prosentvise endringer (som SumInnlandProsent) fra String til Double, og klikker OK. Det siste vi må gjøre er å huke av for Auto Cast  i detaljvinduet til komponenten, og så kan vi kjøre jobben på nytt. Denne gangen blir data lagret slik de skal.

Big Data Business Intelligence
Axenna kan Hadoop, Talend, MongoDB, Cassandra og SpagoBI - og hjelper gjerne til for at din satsning på Big Data skal bli en suksess. Les mer
Profesjonell konsulenthjelp
Våre eksperter omdanner dine data til tilgjengelig kunnskap med effektiv og åpen teknologi. Les mer
Kurs og opplæring
Axenna tilbyr opplæring gjennom ITFakultetet.no med sentrale lokaler i Oslo sentrum og online e-læringsportal. Gå til ITFakultetet.no