Hva er DuckDB?¶
DuckDB er en lettvekts, relasjonell analytisk database som er optimalisert for spørringer av store datamengder. Den er designet med fokus på ytelse, enkelhet og fleksibilitet, noe som gjør den svært velegnet for datajournalistisk analyse av store flytrafikkdatasett.
DuckDB kjører lokalt på datamaskinen din, noe som betyr at du ikke trenger å sette opp en egen kompleks server for å håndtere dataene.
DuckDB er optimalisert for å jobbe med store datasett og komplekse spørringer, noe som passer vårt bruksområde perfekt. Spørrespråket er basert på SQL, som både er veletablert og intuitivt å lære. I tillegg kan du løse flere oppgaver i spørrespråket uten å måtte ty til mer kompliserte programmeringsspråk.
Samtidig kan DuckDB integreres direkte med populære verktøy som Python og R, noe som gir oss muligheten til å kombinere SQL-spørringer med andre dataanalyseverktøy og biblioteker. Dette er svært nyttig for å lage datadrevne historier som involverer statistisk analyse eller visualiseringer.
DuckDB, Parquet og historiske data¶
Medieklyngens ADS-B-server lagrer alle data fra radarene den er koblet til. Hver natt genereres optimaliserte databasefiler med siste døgns bevegelser. Filene lagres i det standardiserte Parquet-formatet, som er utviklet for å håndtere store datamengder effektivt gjennom optimalisering og komprimering.
I motsetning til radbaserte formater som CSV og Excel, lagrer Parquet data kolonnevis. Dette gir flere fordeler ved analyse av store datasett, siden det blir raskere å hente ut og komprimere spesifikke kolonner. For flydata, som ofte inneholder mange attributter som posisjon, hastighet og høyde, gjør Parquet det enkelt å trekke ut og behandle relevante felter, noe som gir betydelige ytelsesforbedringer sammenlignet med radbaserte formater.
Parquet-filene er hive-partisjonert, noe som betyr at de er delt inn i mapper basert på år, måned og dag. Dette gjør det enklere å filtrere og hente ut spesifikke data, og forbedrer spørringsytelsen ved at man kun leser de nødvendige partisjonene i stedet for hele datasettet.
Parquet-filer kan leses av mange ulike verktøy og programmeringsspråk, noe som gir stor fleksibilitet. For eksempel kan Parquet-filer behandles i verktøy som Apache Spark, Hadoop, Presto, Amazon Athena og DuckDB. Dette gir en robust og skalerbar løsning som er klargjort for fremtidig vekst i datamengdene.
Spørrespråket SQL¶
DuckDB benytter det veletablerte spørrespråket SQL. SQL har røtter tilbake til 1970-tallet, da det ble utviklet av IBM for å håndtere data i relasjonsdatabaser. Spørrespråket er intuitivt oppbygget, og relativt enkelt å lære seg på et grunnleggende nivå.
I DuckDBs SQL-dialekt bruker du enkle kommandoer for å hente, manipulere og analysere data fra tabeller. Den grunnleggende strukturen består av kommandoer som SELECT, som brukes for å hente spesifikke data fra en tabell, og FROM, som angir hvilken tabell du henter data fra. Du kan også bruke WHERE for å filtrere dataene basert på bestemte kriterier.
For eksempel kan en spørring se slik ut:
SELECT * FROM flights WHERE altitude > 10000;
Praktisk øvelse¶
Oppstart av DuckDB¶
Lag en tom mappe på valgfri plassering på datamaskinen din.
Start opp Terminal (Mac) eller cmd.exe (Windows), og naviger til mappen du akkurat opprettet.
Start DuckDB med følgende kommando:
duckdb workshop_22_10_24.db
Dette skal gi et prompt som ser slik ut:
v1.0.0 1f98600c2cEnter ".help" for usage hints.D
Installer og last inn utvidelser og innstillinger¶
Installer følgende utvidelser ved å skrive inn følgende kommandorer i prompten (dette gjøres kun ved første gangs kjøring):
INSTALL httpfs;
INSTALL spatial;
INSTALL h3 FROM community;Last inn utvidelsene du akkurat installerte ved hjelp av følgende kommandoer:
LOAD httpfs;
LOAD spatial;
LOAD h3;Legg inn tilgangsnøkler til serveren som lagrer de historiske dataene (bruk dine egne nøkler hvis du har fått dette, eller demonøklene under):
CREATE SECRET (
TYPE R2,
KEY_ID '9030e0f90a86af08b08b6e2a1222a778',
SECRET '2fe64ae1c22869400f577bb9421602f0f81a83a2f658cea6bdd556f4fc65064b',
ACCOUNT_ID 'bca3475a0f4afeb0640daafc17ec2b18'
);Last inn data¶
Vi begynner med å laste ned data fra en spesifikk dag, som vi deretter skal undersøke nærmere. I dette eksempelet benytter vi 11. september 2024.
CREATE TABLE sep11_2024 AS
SELECT * FROM read_parquet('r2://medieklyngen-radar-data/adsb/history/*/*/*/*.parquet', hive_partitioning = true)
WHERE year = 2024 AND month = 09 AND day = 11;Her ber vi DuckDB om å opprette en tabell (CREATE TABLE) med navn sep11_2024 og fylle den med data fra alle feltene (SELECT *) fra alle Parquet-filer (FROM read_parquet) som ligger lagret i mappen 2024/09/11 (WHERE year = 2024 AND month = 09 AND day = 11;).
Tell antall posisjoner i datasettet¶
Når vi nå har lastet ned data for én dag, kan det være nyttig å telle antallet datapunkter det inneholder. Det gjør vi ved å kjøre følgende kommando:
SELECT count(*) FROM sep11_2024;Det gir oss følgende svar:
2380429
2.380.429 millioner posisjoner! Det gir oss litt å jobbe med.
Bli kjent med datastrukturen¶
La oss ta en nærmere titt på dataene og den underliggende strukturen ved å kjøre en enkel spørring. LIMIT 1 begrenser oss til ett treff – det første:
SELECT * FROM sep11_2024 LIMIT 1;Her var det mye informasjon! Vi ser en flytype i dataene, et kallesignal, og land. Men det blir dessverre ikke plass til all informasjonen på skjermen. La oss kjøre den ut i en tekstfil:
COPY (
SELECT * FROM sep11_2024 LIMIT 1
) TO 'sample.json';Her kjører vi spørringen, og kopierer resultatet direkte til filen sample.json. Hvis vi åpner denne filen i en teksteditor, får vi ut noe mer skapelig:
{
"dbFlags":0,
"desc":"AIRBUS A-321",
"icao":"471eff",
"ownOp":null,
"r":"HA-LTF",
"reg_details":{
"description":"general",
"iso2":"HU",
"iso3":"HUN",
"nation":"Hungary"
},
"t":"A321",
"timestamp":"2024-09-11 00:00:00",
"trace":{
"aircraft":{
"alert":0,
"alt_geom":null,
"baro_rate":null,
"category":null,
"emergency":null,
"flight":null,
"geom_rate":null,
"gva":null,
"ias":null,
"mach":null,
"mag_heading":null,
"nac_p":8,
"nac_v":null,
"nav_altitude_fms":null,
"nav_altitude_mcp":null,
"nav_heading":null,
"nav_modes":null,
"nav_qnh":null,
"nic":8,
"nic_baro":null,
"oat":null,
"rc":186,
"roll":null,
"sda":null,
"sil":2,
"sil_type":"perhour",
"spi":0,
"squawk":null,
"tas":null,
"tat":null,
"track":null,
"track_rate":null,
"true_heading":null,
"type":"adsb_icao",
"version":0,
"wd":null,
"ws":null
},
"altitude":35000,
"flags":1,
"geometric_altitude":null,
"geometric_vertical_rate":null,
"ground_speed":null,
"h3_15":"8f09941952db2c1",
"indicated_airspeed":null,
"lat":56.987108,
"lon":4.373002,
"on_ground":false,
"roll_angle":null,
"source":"adsb_icao",
"timestamp":"2024-09-11 04:32:49.670000",
"track_degrees":null,
"vertical_rate":null
},
"year":2024,
"day":"11",
"month":"09"
}Dette viser oss den fullstendige strukturen på dataene som er lagret i Parquet-formatet. Vi ser en rekke datafelter med innhold, men vi ser også at det er en mange nullverdier her, og det er ganske vanlig. Blant feltene vi kan merke oss i første omgang, er:
desc: En beskrivelse av fartøyet.
icao: Fartøyets ICAO-adresse.
r: Kjennetegn
nation: Registreringsland
t: Fartøytype
altitude: Høyde
lat og lon: GPS-posisjon
En god start, men la oss se om vi finner et fartøy med færre nullverdier:
COPY (
SELECT * FROM sep11_2024 WHERE r = 'LN-ORA' LIMIT 1
) TO 'sample2.json';Dette gir oss følgende resultat:
{
"dbFlags":0,
"desc":"AGUSTA AW-169",
"icao":"479c1e",
"ownOp":null,
"r":"LN-ORA",
"reg_details":{
"description":"helicopters",
"iso2":"NO",
"iso3":"NOR",
"nation":"Norway"
},
"t":"A169",
"timestamp":"2024-09-11 00:00:00",
"trace":{
"aircraft":{
"alert":null,
"alt_geom":null,
"baro_rate":null,
"category":null,
"emergency":null,
"flight":null,
"geom_rate":null,
"gva":null,
"ias":null,
"mach":null,
"mag_heading":null,
"nac_p":null,
"nac_v":null,
"nav_altitude_fms":null,
"nav_altitude_mcp":null,
"nav_heading":null,
"nav_modes":null,
"nav_qnh":null,
"nic":null,
"nic_baro":null,
"oat":null,
"rc":null,
"roll":null,
"sda":null,
"sil":null,
"sil_type":null,
"spi":null,
"squawk":null,
"tas":null,
"tat":null,
"track":null,
"track_rate":null,
"true_heading":null,
"type":null,
"version":null,
"wd":null,
"ws":null
},
"altitude":3325,
"flags":1,
"geometric_altitude":null,
"geometric_vertical_rate":null,
"ground_speed":null,
"h3_15":"8f09993851ac430",
"indicated_airspeed":null,
"lat":59.940948,
"lon":10.729706,
"on_ground":false,
"roll_angle":null,
"source":"adsb_icao",
"timestamp":"2024-09-11 11:48:30.880000",
"track_degrees":null,
"vertical_rate":null
},
"year":2024,
"day":"11",
"month":"09"
}Her har vi kjørt samme spørring, men filtrert på et spesifikt fartøy, i dette tilfellet et av Kongeriket Norges politihelikopter: LN-ORA. Vi ser at dette er et helikopter av typen Augusta AW-169, og at det har ICAO-adressen 479c1e. Men også dette datapunktet har mange nullverdier. La oss prøve med noen ekstra filtreringer:
COPY (
SELECT * FROM sep11_2024 WHERE r = 'LN-ORA' AND trace.ground_speed > 0 AND trace.aircraft.squawk IS NOT NULL LIMIT 1
) TO 'sample3.json';Her har vi lagt inn to ekstra filtre. Vi vil kun ha treff der ground_speed er større enn 0, og hvor squawk er satt til en verdi. Dette gir oss følgende resultat:
{
"dbFlags":0,
"desc":"AGUSTA AW-169",
"icao":"479c1e",
"ownOp":null,
"r":"LN-ORA",
"reg_details":{
"description":"helicopters",
"iso2":"NO",
"iso3":"NOR",
"nation":"Norway"
},
"t":"A169",
"timestamp":"2024-09-11 00:00:00",
"trace":{
"aircraft":{
"alert":0,
"alt_geom":2525,
"baro_rate":null,
"category":"A7",
"emergency":"none",
"flight":"HEP01",
"geom_rate":-64,
"gva":2,
"ias":null,
"mach":null,
"mag_heading":null,
"nac_p":11,
"nac_v":2,
"nav_altitude_fms":null,
"nav_altitude_mcp":null,
"nav_heading":null,
"nav_modes":null,
"nav_qnh":null,
"nic":8,
"nic_baro":1,
"oat":null,
"rc":186,
"roll":null,
"sda":2,
"sil":3,
"sil_type":"perhour",
"spi":0,
"squawk":"3317",
"tas":null,
"tat":null,
"track":9.84,
"track_rate":null,
"true_heading":null,
"type":"adsb_icao",
"version":2,
"wd":null,
"ws":null
},
"altitude":2925,
"flags":4,
"geometric_altitude":2525,
"geometric_vertical_rate":-64,
"ground_speed":99.5,
"h3_15":"8f0999398b2604d",
"indicated_airspeed":null,
"lat":59.92772,
"lon":10.845116,
"on_ground":false,
"roll_angle":null,
"source":"adsb_icao",
"timestamp":"2024-09-11 15:23:57.180000",
"track_degrees":9.8,
"vertical_rate":-64
},
"year":2024,
"day":"11",
"month":"09"
}Her ser vi at flere felter har fått verdier. Som forventet finner vi nå verdier for squawk (3317) og ground_speed (99.5), samt data i feltene flight (HEP01), vertical_rate (-64), og flere andre. Nå begynner det å bli interessant!
Vis fartøy pr. land¶
I datastrukturen ser vi at nation er et felt som angir registreringslandet til de sporede fartøyene. La oss se hvilke land som er representert i datasettet, og hvor mange fly som er registrert per land:
.mode duckbox
SELECT reg_details.nation,
COUNT(DISTINCT r)
FROM sep11_2024
WHERE LENGTH(reg_details.nation)
GROUP BY 1
ORDER BY 2 DESC
LIMIT 100;Vi ser at det er 47 land representert, og Norge ligger på toppen med 103 unike observerte fly.
Nødkoder¶
Vi husker fra tidligere at piloter kan melde fra om nødsituasjoner ved å sette spesielle squawk-koder i transponderen. La oss se om vi finner noen slike koder i dataene.
Først laster vi inn et nytt datasett fra en (nesten) tilfeldig valgt dag i oktober:
CREATE TABLE okt15_2024 AS
SELECT * FROM read_parquet('r2://medieklyngen-radar-data/adsb/history/*/*/*/*.parquet', hive_partitioning = true)
WHERE year = 2024 AND month = 10 AND day = 15;Deretter kjører vi følgende spørring for å se etter posisjoner med
squawksatt til 7700, som indikerer en nødsituasjon:
SELECT r, trace.timestamp
FROM okt15_2024 WHERE trace.aircraft.squawk::INT = 7700
GROUP BY 1, 2
ORDER BY 2 DESC
LIMIT 100;Interessant! Her er det noe. La oss utvide spørringen for å få mer informasjon:
SELECT r, trace.timestamp, trace.lat, trace.lon, trace.altitude
FROM okt15_2024 WHERE trace.aircraft.squawk::INT = 7700
GROUP BY 1, 2, 3, 4, 5
ORDER BY 2 DESC
LIMIT 100;Hvis vi ser nærmere på kolonnen
altitude, ser vi at nødsignalet er aktivert på lav høyde, kun 275 fot. Når vi undersøker koordinatene, ser vi at flyet befinner seg rett ved Schiphol-lufthavnen i Amsterdam. Dette er så interessant at vi kan utvide søket ytterligere:
SELECT r, trace.timestamp, trace.lat, trace.lon, trace.altitude, "desc", t, trace.aircraft.category, trace.aircraft.flight
FROM okt15_2024 WHERE trace.aircraft.squawk::INT = 7700
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
ORDER BY 2 DESC
LIMIT 100;Her kan vi visualisere flyets posisjon på kart, undersøke det i andre kilder, og prøve å finne ut hva som har skjedd.
Interessante fartøy¶
Mens mange av sporingsdataene er offentlig tilgjengelig i tjenester som Flightradar24 og Radarbox, er det selvsagt ekstra spennende med informasjon som er vanskeligere tilgjengelig. Her kommer feltet dbFlags til unnsetning. Dataene her stammer fra en database som oppdateres periodisk på serveren. Hvis vi kjører følgende spørring:
SELECT r, dbFlags AS flag, COUNT(DISTINCT dbFlags)
FROM okt15_2024 WHERE dbFlags = 1
GROUP BY ALL
ORDER BY 2 DESC
LIMIT 1000;...får vi opp en liste over fartøy som er klassifisert som militære.
dbFlags = 2 angir at fartøyet er klassifisert som interessant, mens dbFlags = 8 står for LADD: Limiting Aircraft Data Displayed.
Dette kan være verdt en nærmere titt:
SELECT r, dbFlags AS flag, COUNT(DISTINCT dbFlags)
FROM okt15_2024 WHERE dbFlags = 8
GROUP BY ALL
ORDER BY 2 DESC
LIMIT 1000;Her finner vi nok av spennende informasjon å dykke ned i.
Søk i geografiske sektorer¶
DuckDB har en utvidelse kalt spatial som lar oss gjøre geografiske spørringer. Kort fortalt gjør denne modulen DuckDB i stand til å håndtere koordinater. Flytrafikkdataene er i tillegg beriket med en såkalt H3-heksagon.
H3 er et kraftig verktøy utviklet av Uber som gjør det enkelt å dele opp jorden i små sekskantede ruter, kalt “heksagoner.” Dette systemet gjør det lettere å jobbe med geografiske data på en presis og fleksibel måte. Hver av disse heksagonene har en unik ID og kan brukes til å analysere alt fra trafikkmønstre til miljødata.
H3 er spesielt nyttig fordi det løser noen av utfordringene med mer tradisjonelle metoder for å dele opp geografiske områder, som ofte bruker firkanter. Heksagoner gir en mer jevn fordeling og unngår noen av forvrengningene som kan oppstå med firkanter, spesielt når man jobber over store områder på jordkloden.
Vi installerte H3-utvidelsen i starten av denne gjennomgangen, så nå er det bare å sette i gang:
Det første vi må gjøre er å finne H3-sektoren for området vi vil søke i. Til dette finnes det flere åpne løsninger, for eksempel denne: https://
wolf -h3 -viewer .glitch .me/. Zoom inn på området du ønsker å granske nærmere, for eksempel Bergen. Etter hvert som du zoomer inn, vil du se at rutenettet av heksagoner endrer størrelse. Finn et passende utsnitt, og noter deg “Current H3 resolution” oppe til høyre i skjermbildet – for eksempel 3.
Klikk på heksagonen du ønsker å filtrere på. Denne kopieres da til utklippsboken din. Eksempel på H3-kode:
830981fffffffff.Kjør følgende spørring, eventuelt tilpasset med heksagonen og oppløsningen du har valgt. Oppløsningen setter du der det nå står 3 (bak
trace.h3_15), mens heksagon-ID-en settes til slutt på samme linje.
SELECT r, COUNT(trace.altitude) AS count
FROM okt15_2024
WHERE h3_cell_to_parent(trace.h3_15, 3) = '830981fffffffff'
GROUP BY 1
ORDER BY count DESC;Denne spørringen gir oss ut lufttrafikken i den aktuelle sektoren for hele datasettet vårt, som i dette tilfellet er 15. oktober 2024. Den er sortert på antall posisjoner registrert per fartøy. Nå kan vi gå dypere ved å filtrere på fartøyet med flest posisjoner. Samtidig legger vi til noen interessante datafelt:
SELECT r, trace.altitude,
trace.ground_speed,
ST_POINT(trace.lon, trace.lat) geom,
trace.timestamp,
trace.h3_15
FROM okt15_2024
WHERE h3_cell_to_parent(trace.h3_15, 3) = '830981fffffffff' AND r = 'ES-ACP'
ORDER BY trace.timestamp;Her har vi kombinert koordinatene lat og lon i et såkalt “point”, og vi har i tillegg hentet ut høyde, bakkefart og kjennetegn. Spørringen returnerer hele 8685 posisjoner. Dette er interessant, men kanskje ikke nyttig i seg selv. I neste steg skal vi derfor eksportere dataene til en fil.
Eksport av data¶
Med forrige steg friskt i minne, går vi rett på sak:
COPY (
SELECT r, trace.altitude,
trace.ground_speed,
ST_POINT(trace.lon, trace.lat) geom,
trace.timestamp,
trace.h3_15
FROM okt15_2024
WHERE h3_cell_to_parent(trace.h3_15, 3) = '830981fffffffff' AND r = 'ES-ACP'
ORDER BY trace.timestamp
) TO 'ES-ACP.csv' WITH (HEADER);Nå kan vi åpne dataene i Excel eller et annet valgfritt verktøy for videre behandling. Godt jobbet!
Oppgaver¶
Nå har du fått en grunnleggende innføring i noen av mulighetene som ligger i DuckDB. Når man kombinerer disse funksjonene, kan man utføre kraftfulle søk der bare fantasien setter grenser. La oss prøve oss på noen oppgaver nedenfor: