ETL

Hva det er

Bokstavene i ETL står for extract, transform, load, på norsk uttrekk, transformering og lasting. Sammen beskriver de arbeidet med å kopiere data ut av systemene som skaper dem, gi dem den formen og betydningen et annet system forventer, og skrive dem inn der, som oftest i et datavarehus. En nær variant, ELT, beholder de tre stegene, men laster rådataene først og transformerer dem inne i målsystemet, og mange team skriver ETL/ELT for å dekke begge.

Hver bokstav er en egen jobb. Uttrekket leser fra en kilde: en hel tabell, en eksportfil, et API, eller bare radene som er endret siden forrige kjøring, funnet gjennom et tidsstempel som updated_at eller gjennom change data capture (CDC), som leser databasens egen oversikt over hver endring. Transformeringen er der dataene renses og avstemmes: tekst blir til datoer og tall, enheter og tidssoner gjøres ensartede, duplikater fjernes, koder slås opp mot referansetabeller, og resultatet formes om til modellen målsystemet bruker, f.eks. fakta- og dimensjonstabeller. Lastingen skriver resultatet, enten som en masseinnsetting i en tom tabell eller som en sammenslåing (merge) som oppdaterer radene som allerede finnes, og legger til de nye.

Rekkefølgen på de to siste stegene avgjør hvor arbeidet gjøres. Klassisk ETL transformerer på en egen server før noe når målsystemet, og det ga mening den gang datalagring og regnekraft i et datavarehus var knappe og dyre. ELT legger rådataene i mellomtabeller (staging-tabeller) inne i målsystemet og kjører transformeringen der, i SQL. Råkopien blir værende, så når en regel endres, kan transformeringen kjøres på nytt over hele historikken uten et nytt uttrekk. Prisen er at alt kilden sendte, også kolonner ingen ba om, nå ligger i datavarehuset og må forvaltes der.

PostgreSQL dekker hvert steg med egne verktøy. COPY flytter rader mellom en tabell og en fil i tekst-, CSV- eller binærformat og er den raskeste veien inn. For å lese en fil på selve serveren må du være superbruker eller ha rollen pg_read_server_files, mens psql-kommandoen \copy i stedet leser filen på klienten. INSERT ... ON CONFLICT og MERGE fletter nye rader inn i dem som allerede finnes. Logisk dekoding gjør write-ahead-loggen (WAL) om til en strøm av radendringer for CDC, og en prosedyre i PL/pgSQL kan holde transformeringen tett ved dataene den endrer.

Hvorfor det er viktig

De fleste spørsmål som er verdt å stille om en virksomhet, krever data fra mer enn ett system. Ordrene ligger i én database, betalingene i en eksport fra en leverandør, kundeopplysningene i et kundesystem (CRM). Inntil noe flytter dem til ett sted under ett sett regler, blir hvert svar satt sammen for hånd, og hånden skifter med personen som gjør jobben. ETL er det som flytter dem, og et datavarehus er bare så riktig som lastingene som fyller det.

Transformeringssteget er også der en virksomhet skriver ned hva dataene betyr. Hvilke rader som teller som et salg, hvilken dag en betaling ved midnatt hører til, hvilken post som vinner når to kunder ser ut til å være den samme: avgjort én gang i lastingen gjelder svaret for hver rapport lenger ut i kjeden. Overlatt til hver enkelt rapport glir svarene fra hverandre, og to dashbord viser to forskjellige summer.

Måten en lasting henter ut data på, avgjør hvordan den vokser. En full omlasting kopierer alt hver gang. Den er enkel og retter seg selv, siden gårsdagens feil blir overskrevet i natt, men kostnaden stiger med den samlede datamengden. En inkrementell lasting flytter bare det som er endret, så der stiger kostnaden i stedet med endringstakten. Den må huske hvor forrige kjøring stoppet, merke seg rader som er slettet i kilden, og ta imot rader som kommer sent med gamle tidsstempler. De fleste systemer begynner med fulle omlastinger og bytter over, tabell for tabell, når natten ikke lenger er lang nok.

Den løpende kostnaden er at lastinger er kode som kjører uten tilsyn, som regel om natten, mot kilder som endres uten forvarsel. De trenger logging som forteller hva som kjørte og hvor mange rader som ble flyttet, et driftsvarsel når en kjøring stopper, og framfor alt én egenskap: en kjøring som gjentas med de samme inndataene, skal etterlate målet nøyaktig slik én kjøring ville gjort. En lasting med den egenskapen er idempotent, og den kan kjøres på nytt etter hvilken som helst feil uten at noen må sjekke noe først.

Slik ser det ut i praksis

I flere utviklings- og produksjonsmiljøer i PostgreSQL ble over 600 ETL/ELT-pipeliner skrevet i PL/pgSQL for å erstatte fragmenterte pipeliner som forsinket forretningskritisk rapportering. Primær- og fremmednøkkelbegrensninger voktet referanseintegriteten under transformasjonene, unike og sammensatte indekser gjorde JOIN-operasjonene raskere, og unntakshåndtering med transaksjonskontroll rullet tilbake en kjøring som gikk galt. Inkrementelle lastinger gjennom CDC og tidsstempelbaserte deltaer kuttet behandlingstiden med over 60 %, og hver kjøring ble logget og revidert. Gjennomsnittlig kjøretid falt med 35 %, gjennomstrømningen økte med over 50 %, og over 90 % av transformasjonsfeilene forsvant takket være bedre håndheving av begrensninger og bedre valideringslogikk.

En migrering mellom databaser er ETL som kjører én gang. Å flytte en kartapplikasjon fra Microsoft SQL Server til PostgreSQL med PostGIS betydde å føre over 50 millioner romlige poster gjennom ETL-pipeliner med transformasjonssteg som gjorde om hver eneste koordinat til referansesystemet EPSG:4326. Romlige spørringer fikk ti ganger bedre ytelse av migreringen som helhet, der ETL-jobben virket sammen med GiST- og SP-GiST-indekser og en justert konfigurasjon på den nye siden: svartiden, i snitt 2,5 sekunder før, havnet under 250 millisekunder, og CPU-belastningen i backend falt med 65 %.

Over en nettverksgrense viser seks ETL/ELT-pipeliner alle bokstavene i navnet i arbeid. Uttrekket besto av planlagte nedlastinger av komprimerte arkiver fra en server som bare kunne nås gjennom et IPsec-VPN, med Bash- og Python-skript som sorterte Parquet-, CSV- og .bak-filene i dem etter type og skjema, og .bak-sikkerhetskopiene ble gjenopprettet i en lokal MSSQL Server-instans. Dataene ble lastet inn i staging-skjemaer i MSSQL og PostgreSQL med bcp, psql eller SSIS, alt etter formatet, så renset, normalisert og beriket av prosedyrer i PL/pgSQL og T-SQL, og til slutt komprimert med gzip og fraktet gjennom en Google Cloud Storage-bucket inn i Google BigQuery. Arbeid som hadde tatt mer enn tre timer for hånd, kjørte på under 20 minutter fra ende til ende, og synkroniseringen gikk fra ukentlig til daglig.

Hvor det går galt

En kjøring som startes på nytt, laster de samme radene to ganger. En jobb setter inn med vanlig INSERT, stopper halvveis på en nettverksfeil, og jobbplanleggeren kjører den på nytt fra starten. Radene som kom inn første gang, ligger nå der to ganger, og hver sum som bygger på dem, blir for høy. Gi målet en nøkkel som identifiserer en rad i forretningstermer, og flett inn partiet med INSERT ... ON CONFLICT DO UPDATE, som PostgreSQL dokumenterer som en atomisk innsetting eller oppdatering også ved høy samtidighet, eller med MERGE. Én hake finnes: en enkelt ON CONFLICT DO UPDATE-setning nekter å endre den samme raden to ganger og gir et kardinalitetsbrudd (cardinality violation), så et parti med gjentatte nøkler må først reduseres til én rad per nøkkel.

INSERT INTO meter_reading (meter_id, read_at, kwh)
SELECT DISTINCT ON (meter_id, read_at) meter_id, read_at, kwh
FROM staging_meter_reading
ORDER BY meter_id, read_at, received_at DESC
ON CONFLICT (meter_id, read_at)
DO UPDATE SET kwh = EXCLUDED.kwh;

En masselasting stopper nær slutten og etterlater disken fullere enn før. COPY FROM skriver rader inn i tabellen etter hvert som den leser dem. Når den stopper på en dårlig rad, blir radene den allerede har skrevet, liggende igjen i slettet tilstand, usynlige for spørringer, men fortsatt på disken til VACUUM tar plassen tilbake, og en feil sent i en stor fil kan sløse bort mye plass. PostgreSQL 17 la til ON_ERROR ignore, som hopper over rader i tekst- eller CSV-inndata der verdiene ikke lar seg konvertere til kolonnens type, og PostgreSQL 18 la til REJECT_LIMIT for å sette et tak på hvor mange rader den kan hoppe over. Det dekker bare typekonvertering; en rad som bryter en begrensning, stopper fortsatt kommandoen. Last inn i en mellomtabell først, sjekk dataene der, og flytt de gode radene inn i den virkelige tabellen i én transaksjon, så holdes en dårlig fil unna dataene folk leser.

Change data capture fyller disken. Logisk dekoding leser endringer gjennom et replikeringsspor (replication slot), og et slikt spor holder på hver bit av write-ahead-loggen som mottakeren fortsatt trenger, enten en mottaker er koblet til eller ikke. Når leseren stopper, hoper loggen seg opp til disken er full, og i ytterste konsekvens slår serveren seg av for å hindre omløp av transaksjons-ID-ene (transaction ID wraparound). PostgreSQL-dokumentasjonen sier det rett ut: et spor som ikke lenger trengs, skal fjernes. Etter et krasj kan et spor også sende nylige endringer én gang til, så mottakeren må tåle duplikater, og det er den samme egenskapen ved nye kjøringer igjen.

Morgenens første rapport er treg etter en stor lasting. Spørringsplanleggeren velger hvordan en spørring skal kjøres, ut fra statistikk om hver tabell, og en masselasting endrer dataene under den statistikken. Autovacuum kan oppdatere statistikken på egen hånd, men ikke alltid før den første rapporten kjører. Å kjøre ANALYZE som siste steg i lastingen løser det. For svært store lastinger inn i en eksisterende tabell legger PostgreSQL-veiledningen om å fylle en database til at det kan lønne seg å fjerne indekser og fremmednøkler og opprette dem på nytt etterpå, fordi det går raskere å bygge dem samlet enn å oppdatere dem rad for rad. Da gir du fra deg feilsjekkingen mens de er borte, så en unik indeks fortjener en ekstra tanke før den fjernes.

Les mer

• COPY — kommandoen for masselasting, formatene og opsjonene, og hva en mislykket lasting etterlater seg.
• Populating a Database — rådene fra PostgreSQL for store lastinger, fra én transaksjon og COPY til å bygge indekser på nytt og kjøre ANALYZE etterpå.
• MERGE — innsetting, oppdatering og sletting i én setning, styrt av hvordan hver kilderad passer med målet.
• INSERT — leddet ON CONFLICT, den atomiske upserten og regelen mot å røre den samme raden to ganger.
• Logical Decoding Concepts — hvordan endringer leses fra write-ahead-loggen, og hvorfor et replikeringsspor som ikke brukes, må fjernes.