Analiza podatkov z odprtokodnim sistemom za upravljanje s podatkovnimi bazami PostgreSQL
PostgreSQL je odprtokodna aplikacija za upravljanje s podatkovnimi bazami, ki se v zadnjih različicah lahko kosa tudi z najbolj zmogljivimi komercialnimi rešitvami. Zlasti slovi po svoji zanesljivosti delovanja, hitrosti in zmožnosti obdelave zelo velikih količin podatkov (govorimo o več milijonih zapisov). Z nekaterimi dodatki je mogoče obdelovati tudi prostorske podatke (PostGIS), slike (PostPic), indeksirati besedila (OpenFTS), itd. V nadaljevanju si bomo na kratko pogledali kako je s pomočjo PostgreSQL mogoče analizirati velike količine podatkov iz področja finančnih transakcij.
Namestitev aplikacije
PostgreSQL je na voljo za številne operacijske sisteme. Poleg Linux, Windows in Mac OS je na voljo tudi za BSD sisteme in Solaris. V Ubuntu Linuxu ga namestimo s pomočjo programskega paketa postgresql. Trenutno je za Ubuntu na voljo različica 9.1. Naj opozorimo, da je po namestitvi smiselno nekaj časa posvetiti nastavitvi ustreznih pravic dostopa do baz podatkov oz. zavarovanju dostopa do podatkov, zlasti, če bo podatkovni strežnik odprt na internet.
Uvoz podatkov
Najpomembnejši in pogosto tudi najbolj zamuden del analize podatkov je priprava in uvoz podatkov v bazo. PostgreSQL omogoča različne načine uvoza oziroma ima na voljo različne uvozne filtre (nekateri so na voljo tudi kot posebne aplikacije), pogosto pa je najbolj enostavno, če podatke uvažamo in tekstovnih datotek, kjer so podatki v stolpcih ločeni s tabulatorji (tim. tab delimited datoteka).
Takšno datoteko najbolj enostavno pripravimo tako, da v aplikaciji za delo s podatkovnimi preglednicami (npr. LibreOffice, OpenOffice ali Microsoft Excel) podatke shranimo v CSV obliko, pri čemer kot ločilni znak (ang. delimiter) uporabimo tabulator. Pomembno je tudi kakšno kodiranje znakov izberemo (gre za izbiro tim. kodne tabele znakov, npr. CP-1250, ISO-8859-2, UFT-8,…). Če bomo podatkovno bazo poganjali v okolju Linux (v nadaljevanju bomo prikazali uporabo PostgreSQL v okolju Linux) je najbolj smiselno, da je izhodna datoteka shranjena v UTF-8 formatu.
Včasih so vhodni podatki v razmeroma slabem stanju, zato je pred izvozom datoteko smiselno pregledati in preveriti ali ni morda kje prišlo do zamika stolpcev, preveriti kako so oblikovane številke (ali je uporabljena decimalna vejica ali decimalna pika, ali je uporabljeno ločilo tisočic,…). Podatke je pred izvozom v tekstovno datoteko zato potrebno pregledati in urediti. Pri urejanju si lahko pomagamo tudi z orodjem Google Refine, v programih za delo s preglednicami pa je podatke v celicah najbolj smiselno pretvoriti v besedilo, saj se s tem najlažje izognemo kakšnim čudnim samodejnim pretvorbam v različne oblike.
Ko podatke prečistimo, uredimo in izvozimo, bo torej rezultat (CSV) tekstovna datoteka, ki jo bomo v naslednjem koraku uvozili v bazo podatkov. Mimogrede naj omenimo, da pri uvozu v PostgreSQL bazo pogosto pride do težav zaradi praznih vrstic, ki se pojavljajo v datoteki s podatki, ki jih želimo uvoziti. Le-te v Linuxu najbolj enostavno odstranimo z ukazom:
grep -v '^$' podatki.txt > podatki_brez_praznih_vrstic.txt
V naslednjem koraku je potrebno ustvariti podatkovno bazo. Recimo, da jo želimo poimenovati “finance“. V Linux ukazni vrstici uporabimo naslednji ukaz:
createdb finance
Nato s pomočjo PostgreSQL “vstopimo” v novo – zaenkrat še prazno – bazo podatkov:
psql finance
Znajdemo se v ukazni vrstici Postgres podatkovnega strežnika. Ker je podatkovna baza na novo ustvarjena, je prazna, zato je potrebno v njen najprej ustvariti ustrezne tabele.
Tabele so entitete (posamezna tabela je podobna delovnemu listu v aplikaciji za delo s preglednicami), v katerih se nahajajo konkretni podatki. Baza podatkov torej vsebuje različne tabele (v katerih se nahajajo različni nabori podatkov), posamezna tabela pa vsebuje stolpce (spremenljivke) in vrstice (enote) v katerih se nahajajo podatki. Podatke iz posameznih tabel je mogoče povezovati oz. kombinirati med seboj (če se tabele nahajajo znotraj posamezne baze podatkov).
Novo tabelo ustvarimo s pomočjo ukaza create table (ukazi se v PostgreSQL zaključijo s podpičjem). Primer ustvarjanja tabele, kjer bodo shranjena imena podjetij in njihove matične številke:
create table podjetja (ime text, maticna_stevilka text);
Ustvarimo še nekaj dodatnih tabel, ki jih bomo kasneje med sebboj lahko povezovali:
create table trr (maticna_stevilka text, transakcijski_racun text);
create table placila (imetnik text, prejemnik text, prejemnik_trr text, znesek text, valuta text, datum text, namen text);
Če sedaj želimo pregledati katere tabele se nahajajo v naši bazi podatkov (finance), v ukazno vrstico vpišemo ukaz:
\d
Z ukazom \d ime_tabele pa lahko pogledamo kateri stolpci se nahajajo v dani tabeli. Primer:
\d placila
finance=# \d List of relations Schema | Name | Type | Owner -------+----------+-------+------- public | placila | table | root public | podjetja | table | root public | trr | table | root (3 rows)
finance=# \d placila Table "public.placila" Column | Type | Modifiers --------------+------+----------- imetnik | text | prejemnik | text | prejemnik_trr | text | znesek | text | valuta | text | datum | text | namen | text |
Uporabna ukaza sta še \d+ ime_tabele, ki pokaže podrobnejši opis tabele in \l, ki prikaže vse baze podatkov v sistemu.
Sedaj je baza podatkov pripravljena na uvoz podatkov iz tekstovne (tab-delimited) datoteke. Za uvoz uporabimo ukaz copy. Seveda lahko v PostgreSQL uvažamo iz različnih vrst in oblik datotek, tudi iz datoteke s fiksnimi pozicijami stolpcev, je pa kot rečeno verjetno najbolj enostavno podatke iz Excela ali Libre/OpenOffice Calca uvažati s pomočjo CSV datotek.
Primer ukaza za uvoz podatkov s tabulatorji (\t) ločenih stolpcev:
copy placila (imetnik, prejemnik, prejemnik_trr, znesek, valuta, datum, namen) from '/home/matej/dataimport/placila.txt' with csv header delimiter E'\t'; copy podjetja (ime, maticna_stevilka) from '/home/matej/dataimport/podjetja.txt' with csv header delimiter E'\t'; copy trr (maticna_stevilka, transakcijski_racun) from '/home/matej/dataimport/trr.txt' with csv header delimiter E'\t';
Če je uvoz uspešen, bo PostgreSQL izpisal koliko vrstic podatkov je bilo uvoženih. Npr. v primeru tabele podjetja: COPY 18.
Mimogrede, včasih želimo v tabelo dodati samo manjšo količino podatkov. To lahko storimo “ročno” z ukazom insert. Primer:
insert into placila (imetnik, prejemnik, prejemnik_trr, znesek, valuta, datum, namen) values ('podjetje 1', 'Janez s.p.', 'SI56251009709467164', '100,12', 'EUR', '1.1.2012', 'plačilo računa');
Na zgoraj opisani način smo tako v bazo podatkov uvozili podatke v osnovni (tekstovni) obliki. Da pa bi bila njihova obdelava čim bolj enostavna in zmogljiva pa je smiselno, da podatke v tabeli pretvorimo v ustrezne podatkovne tipe. PostgreSQL pozna številne podatkovne tipe, med najbolj pogosto uporabljanimi pa so verjetno tekstovni podatkovni tip (text), številski podatkovni tip (decimal) in časovni podatkovni tip (timestamp). V nadaljevanju si bomo zato pogledali kako uvožene tekstovne podatke pretvorimo v druge oblike (kadar je to smiselno), saj je s podatki v drugih podatkovnih tipih mogoče opravljati številne, tudi statistične analize.
Priprava podatkov v tabeli
Pri pretvorbi podatkov v novo obliko je smiselno poleg nove ohraniti tudi staro obliko podatkov. Zato v tabeli podatkov ustvarimo nov stolpec, kamor zapišemo podatke v pretvorjeni obliki. V nadaljevanju si bomo pogledali primer, ko bomo stolpec znesek, ki se nahaja v tekstovni obliki pretvorili v decimalno obliko, hkrati pa bomo vse valute pretvorili v evre. Stolpec bomo poimenovali znesek_v_eur. V drugem primeru bomo polje datum pretvorili v časovni podatkovni tip in ga preimenovali v datum_transakcije.
Prvi primer – uporabimo ukaz update, kjer je prvi parameter ime tabele, sledi pa nastavitev vrednosti s parametrom set:
alter table placila add column znesek_v_eur decimal(16,4);
update placila set znesek_v_eur = replace(znesek,',','.')::decimal(16,4) where valuta = 'EUR'; update placila set znesek_v_eur = cast(replace(znesek,',','.') as numeric(16,4))/239.64 where valuta = 'SIT';
Včasih je smiselno določene vrednosti nastaviti na tim. ničelno vrednost – vrednost NULL. Npr. v našem primeru bi lahko vse zneske, kjer se nahaja presledek nastavili na ničelno vrednost:
update placila set znesek_v_eur = NULL where znesek = ' ';
Drugi primer, kjer bomo datume v obliki zapisa s piko z regex zamenjavo pretvorili v PostgreSQL fomat zapisa datuma:
alter table placila add column datum_nakazila date;
update placila set datum_nakazila = regexp_replace(datum, E'^(\\d?\\d)\.(\\d?\\d)\.(\\d\\d\\d\\d)', E'\\3-\\2-\\1')::timestamp;
Če sedaj pogledamo vsebino tabele placila, lahko opazimo, da sta se na koncu tabele pojavila še dva dodatna stolpca in sicer eden v številskem, drugi pa v časovnem podatkovnem tipu. Podatki so sedaj pripravljeni na osnovno analizo.
finance=# \d placila Table "public.placila" Column | Type | Modifiers ---------------+---------------+----------- imetnik | text | prejemnik | text | prejemnik_trr | text | znesek | text | valuta | text | datum | text | namen | text | znesek_v_eur | numeric(16,4) | datum_nakazila | date |
Osnovna analiza podatkov
Osnovni ukaz s katerim pripravljamo različne vrste izpisov obdelav podatkov je ukaz select, pomagamo pa si tudi s parametri, ki omogočajo izbiro podatkov glede na ustrezne pogoje. Najpogosteje uporabljen tovrsten parameter bo where.
Za začetek si oglejmo kako pregledamo vse podatke oziroma samo prvih 10 vrstic podatkov:
select * from placila; select * from placila limit 10;
Če je stolpcev več kot za en zaslon, in so vrstice “razlomljene” na zaslonu, v PostgreSQL ukazni vrstici pritisnemo “-S”. Po izpisu se premikamo s smernimi tipkami.
Prikažemo lahko samo določene stolpce:
select datum_nakazila, valuta from placila;
Pri posameznih tipih stolpcev (spremenljivk) lahko uporabimo tudi različne operatorje. Primer pretvorbe vseh namenov nakazil v male ali velike črke:
select lower(namen) from placila; select upper(namen) from placila;
S funkcijo trim pa lahko odstranimo presledke ali kakšen drug znak iz tekstovnega niza (naj omenimo, da funkcija trim lahko odstranjuje poljubne znake (privzeto presledke) na začetku ali na koncu tekstovnega niza):
select trim(namen) from placila;
Na posamezni spremenljivki v tabeli (stolpcu), lahko uporabimo tudi kakšno dodatno obdelavo. V spodnjem primeru bomo prikazali seznam nakazil, pri vsakem nakazilu pa leto v katerem je bilo nakazilo izvedeno:
select extract(year from datum_nakazila) as leto, znesek_v_eur from placila;
Podatke pa lahko tudi preštejemo:
select count(*) from placila;
Rezultat:
count ------- 56 (1 row)
Grupiranje podatkov
Posebej uporabno je tudi grupiranje podatkov, torej obdelava, ki omogoča združevanje podatkov glede na določene kategorije. Za primer si poglejmo kako prikazati vsoto vseh plačil v posameznem letu, pri čemer bomo uporabili parameter group by, podatke pa bomo na koncu sortirali po letu (od najvišjega do najnižjega) z ukazom order by:
select extract(year from datum_nakazila) as leto, sum(znesek_v_eur) as znesek from placila group by leto order by leto desc;
Rezultat:
leto | znesek -----+------------- 2013 | 860.4300 2012 | 136410.3800 2011 | 170671.3800 2010 | 191976.5100 2009 | 68122.5800 2008 | 45039.0000 2007 | 234.0000 2006 | 4727.0091 (8 rows)
Na podoben način lahko preštejemo tudi število nakazil v posamezni valuti:
select valuta, count(*) as stevilo from placila group by valuta;
Rezultat:
valuta | stevilo -------+--------- SIT | 2 EUR | 54 (2 rows)
Izbori podatkov in uporaba pogojev
V nadaljevanju si bomo ogledali le nekaj najpogostejših oz. v praksi najpogosteje uporabnih primerov uporabe pogojev. Za izbor podatkov, ki ustrezajo enemu ali več pogojem pri ukazu select uporabimo parameter where, ki mu sledi eden ali več pogojev. Primer prikaza samo tistih podatkov, kjer je valuta enaka “SIT”:
select * from placila where valuta = 'SIT';
Rezultat:
imetnik | prejemnik | prejemnik_trr | znesek | valuta | datum | namen | znesek_v_eur | datum_nakazila -------------+------------+---------------------+-----------+--------+------------+-------------------------+--------------+---------------- TEST d.o.o. | Podjetje C | SI56251009709467164 | 600123,25 | SIT | 25.10.2006 | OPREMA,NAKUP IN GRAD.OS | 2504.2699 | 2006-10-25 TEST d.o.o. | podjetje D | SI56044800110599295 | 532657,22 | SIT | 4.12.2006 | MATERIAL | 2222.7392 | 2006-12-04 (2 rows)
Mogoči so tudi bolj kompleksni pogoji oz. uporaba več pogojev. Za primer si oglejmo prikaz skupne vsote nakazil po letih 2008, 2010 in 2012, pri čemer je tabela sortirana glede na leto (padajoče):
select extract(year from datum_nakazila) as leto, sum(znesek_v_eur) as skupni_znesek from placila where (extract(year from datum_nakazila) = '2008') or (extract(year from datum_nakazila) = '2010') or (extract(year from datum_nakazila) = '2012') group by leto order by leto desc;
Rezultat:
leto | skupni_znesek -----+--------------- 2012 | 136410.3800 2010 | 191976.5100 2008 | 45039.0000 (3 rows)
Isti ukaz bi lahko nekoliko krajše zapisali tudi drugače:
select extract(year from datum_nakazila) as leto, sum(znesek_v_eur) as skupni_znesek from placila where (extract(year from datum_nakazila) in ('2008', '2010', '2012')) group by leto order by leto desc;
Še primer prikaza zneskov in valut podatkov, kjer je namena nakazila ni (kar ni enako kot da je v namenu zapisan presledek, pač pa, da je polje prazno oziroma ima tim. ničelno vrednost):
select znesek, valuta from placila where namen IS NULL;
Pri pripravi različnih izpisov je zelo uporabna tudi funkcija coalesce, ki vrne prvo neničelno vrednost iz seznama spremenljivk. Primer:
select coalesce(prejemnik, prejemnik_trr, 'ni podatka') as prejemnik_placila from placila;
Izpis bo vrnil naziv prejemnika, če pa tega podatka ni, bo vrnil njegov transakcijski račun. Če tudi tega podatka ni v bazi, bo izpisal “ni podatka“. Še nekoliko lepši izpis pa bi dobili z naslednjim ukazom:
select coalesce(prejemnik, 'TRR prejemnika:' || prejemnik_trr, 'ni podatka') as prejemnik_placila from placila;
Za iskanje po tekstovnih nizih sta zelo uporabni funkciji like in regex iskanje. Primer iskanja nakazil, ki v namenu vsebujejo besedilo “NAJEM“:
select * from placila where namen like '%NAJEM%';
Rezultat:
imetnik | prejemnik | prejemnik_trr | znesek | valuta | datum | namen | znesek_v_eur | datum_nakazila -------------+------------+---------------------+--------+--------+----------+-----------+--------------+---------------- TEST d.o.o. | PODJETJE E | SI56060001922635456 | 850,21 | EUR | 4.2.2010 | NAJEM | 850.2100 | 2010-02-04 TEST d.o.o. | PODJETJE E | SI56060001922635456 | 820,41 | EUR | 3.2.2011 | NAJEMNINA | 820.4100 | 2011-02-03 (2 rows)
Še bolj zmogljivo pa je tim. regex iskanje, zlasti regex iskanje, ki ne upošteva velikosti znakov (tim. case insesitive regex – ~*) ter regex iskanje, ki upošteva velikost znakov (tim. case sensitive regex – ~). Primer:
select * from placila where namen ~* 'najem';
Dodatno prilagajanje izpisov
Izpise PostgreSQL strežnika lahko še nekoliko bolj prilagodimo za naše potrebe. Za primer si bomo ogledali izpis seznama poslovnih partnerjev z njihovimi tekočimi računi:
select prejemnik || ' (TRR: ' || prejemnik_trr || ')' from placila;
Podatke v celicah pa lahko na izpisu tudi spreminjamo, npr. takole:
select replace(prejemnik,'d.o.o.','(druzba z omejeno odgovornostjo)') from placila;
ali takole:
select regexp_replace(prejemnik, 'd.o.o.','(druzba z omejeno odgovornostjo)','g') from placila;
Povezovanje podatkov
Podatke iz različnih tabel znotraj posamezne baze podatkov med seboj lahko tudi povezujemo, kar da možnostim analize povsem nove dimenzije. Predpostavimo, da imamo v tabeli placila stolpec prejemnik, ki vsebuje ročno vpisane podatke o prejemniku. Ker gre za ročen vpis, pogosto prihaja do napak. Tako je npr. podjetje ABC vpisano kot “ABC”, “abc”, “A B C”, itd. Vendar pa tabela plačila vsebuje tudi stolpec prejemnik_trr, ki pa vsebuje točne podatke, saj gre za številko tekočega računa, kamor je bilo izvedeno dejansko nakazilo. Hkrati pa imamo v bazi podatkov še dve tabeli. Prva tabela z imenom podjetja vsebuje imena in matične številke podjetij, druga tabela z imenom trr pa številke transakcijskih računov in matične številke podjetij.
V nekoliko kompleksnejšem primeru, ki pa dobro ponazarja zmogljivosti povezovanja različnih tabel, podatke iz različnih tabel povežemo tako, da iz tabele placila preberemo številko TRR, z njo vpogledamo v tabelo trr iz katere preberemo matično številko podjetja, s to številko pa nato vpogledamo v tabelo podjetja, kjer preberemo ime. To ime nato izpišemo poleg ostalih podatkov iz tabele placila. To storimo takole:
select placila.*, podjetja.ime from placila, podjetja, trr where (placila.prejemnik_trr = trr.transakcijski_racun) and (trr.maticna_stevilka = podjetja.maticna_stevilka);
Nekateri drugi uporabni triki
Za konec si poglejmo še nekaj uporabnih ukazov v PostgreSQL.
Če želimo podatke iz izpisa shraniti v tekstovno datoteko (ki jo lahko kasneje uvozimo v program za delo s preglednicami, npr. Open/LibreOffice ali MS Excel), to storimo z ukazom copy. Pri tem je v ciljnem imeniku potrebno imeti ustrezne pravice zapisovanja, včasih je zato najenostavneje, da podatke shranimo v začasno datoteko na podimenik /tmp. Primer:
copy (...psql ukaz...) to '/tmp/izpis.txt' with csv header delimiter E'\t';
Npr.:
copy (select podjetja.ime, placila.znesek, placila.valuta from placila, podjetja, trr where (placila.prejemnik_trr = trr.transakcijski_racun) and (trr.maticna_stevilka = podjetja.maticna_stevilka)) to '/tmp/podjetja-zneski.txt' with csv header delimiter E'\t';
Naj omenimo, da podatke lahko shranjujemo tudi v novo tabelo in sicer z ukazom select … into ime_tabele. Npr.:
select podjetja.ime as ime_podjetja, placila.znesek as znesek_placila, placila.valuta as valuta into podjetjazneski from placila, podjetja, trr where (placila.prejemnik_trr = trr.transakcijski_racun) and (trr.maticna_stevilka = podjetja.maticna_stevilka);
Nova tabela z imenom podjetjazneski vsebuje tri stolpce: ime_podjetja, znesek_placila in valuta.
finance=# \d podjetjazneski Table "public.podjetjazneski" Column | Type | Modifiers ----------------+------+----------- ime_podjetja | text | znesek_placila | text | valuta | text |
Če želimo iz te nove tabele (podjetjazneski) izbrisati stolpec valuta, to storimo z ukazom:
alter table podjetjazneski drop column valuta;
Če želimo pobrisati samo določene podatke (podatke, ki ustrezajo pogoju, pa namesto ukaza select uporabimo ukaz delete. Npr. izbris vseh plačil delniškim družbam (ime prejemnika vsebuje d.d.):
delete from podjetjazneski where ime_podjetja ~* 'd.d.';
Če pa želimo izbrisati celotno tabelo, to storimo z ukazom:
drop table podjetjazneski;
Seveda ni odveč poudariti, da je pri brisanju potreba previdnost. Podatke namenjene izbrisu je vedno priporočljivo predhodno pregledati z ukazom select. Celotno bazo sicer lahko izbrišemo iz (Linux) ukazne vrstice z ukazom dropdb (npr. dropdb finance).
Risanje z GraphWiz
Za konec si bomo pogledali nekoliko bolj kompleksen, vendar v praksi zelo uporaben primer risanja grafov s pomočjo PostgreSQL in odprtokodnega orodja Graphviz. Graphviz (oz. Graph Visualization Software) je zbirka odprtokodnih orodij za risanje grafov s pomočjo ukaznega jezika DOT. Poganjamo ga iz ukazne vrstice, zato je to orodje zelo uporabno za poganjanje v skriptah. Na voljo pa je tudi grafično orodje Gephi, ki ravno tako razume ukazni jezik DOT. Graphviz ima sicer precej zmogljivosti, zato se v njegove številne možnosti ne bomo spuščali, pogledali si bomo le enostaven primer kako s pomočjo PostgreSQL pripravimo tekstovni izpis podatkov, ki jih nato z Graphvizovim orodjem fdp vizualiziramo.
Najprej pripravimo izpis podatkov o plačilih iz baze v obliki:
"podjetje" -> "prejemnik" [label="XXXX,XX EUR", fontsize=10]
Podatki bodo pripravljeni na tak način, da bo za vsak unikaten par (podjetje – prejemnik) izrisana ena sama povezava, s skupnim zneskom vseh izplačil v celotnem obdobju. PostgreSQL ukaz je nekoliko bolj kompleksen:
select '"' || placila.imetnik || '"->"' || podjetja.ime || '" [label="' || sum(placila.znesek_v_eur)::numeric(16,2) || ' EUR", fontsize=10]' from placila, podjetja, trr where (placila.prejemnik_trr = trr.transakcijski_racun) and (trr.maticna_stevilka = podjetja.maticna_stevilka) group by placila.imetnik, podjetja.ime;
Rezultat:
?column? -------------------------------------------------------------------------- "TEST 1 d.o.o."->"podjetje B" [label="6231.86 EUR", fontsize=10] "TEST 1 d.o.o."->"Čiščenje d.o.o." [label="8740.45 EUR", fontsize=10] "TEST d.o.o."->"Avtomobili d.d." [label="18500.00 EUR", fontsize=10] "TEST 1 d.o.o."->"Web.de" [label="30.12 EUR", fontsize=10] "TEST 1 d.o.o."->"Računalništvo Janez" [label="860.43 EUR", fontsize=10] "TEST 1 d.o.o."->"podjetje D" [label="4800.01 EUR", fontsize=10] "TEST d.o.o."->"Janez Novak" [label="28083.00 EUR", fontsize=10] "TEST d.o.o."->"Elektro Center" [label="4012.44 EUR", fontsize=10] "TEST d.o.o."->"Podjetje Biro" [label="6971.11 EUR", fontsize=10] "TEST d.o.o."->"GRADNJE d.d." [label="75889.72 EUR", fontsize=10] "TEST d.o.o."->"Vrtnarstvo s.p." [label="6971.11 EUR", fontsize=10] "TEST d.o.o."->"Storitveni servis" [label="12342.00 EUR", fontsize=10] "TEST d.o.o."->"PODJETJE E" [label="1670.62 EUR", fontsize=10] "TEST d.o.o."->"podjetje B" [label="89893.90 EUR", fontsize=10] "TEST d.o.o."->"Čiščenje d.o.o." [label="34951.81 EUR", fontsize=10] "TEST d.o.o."->"Internet d.o.o." [label="45.60 EUR", fontsize=10] "TEST d.o.o."->"Računalništvo Janez" [label="79801.31 EUR", fontsize=10] "TEST d.o.o."->"Podjetje C" [label="66460.36 EUR", fontsize=10] "TEST d.o.o."->"Izposoja d.o.o." [label="45002.80 EUR", fontsize=10] "TEST 1 d.o.o."->"podjetje A" [label="83410.64 EUR", fontsize=10] "TEST d.o.o."->"podjetje D" [label="23484.60 EUR", fontsize=10] "TEST d.o.o."->"Miha novak" [label="21358.51 EUR", fontsize=10] (22 rows)
Z ukazom copy sedaj vsebino izpisa shranimo v datoteko /tmp/graf.txt (brez CSV vzglavja in ločilnih znakov):
copy(select '"' || placila.imetnik || '"->"' || podjetja.ime || '" [label="' || sum(placila.znesek_v_eur)::numeric(16,2) || ' EUR", fontsize=10]' from placila, podjetja, trr where (placila.prejemnik_trr = trr.transakcijski_racun) and (trr.maticna_stevilka = podjetja.maticna_stevilka) group by placila.imetnik, podjetja.ime) to '/tmp/graf.txt';
Sedaj se odjavimo iz PostgreSQL ukazne vrstice s pritiskom na Ctrl-d. V Linux ukazni vrstici vpišemo naslednje ukaze, ki nam datoteko graf.txt opremijo z ustreznimi DOT ukazi:
echo "digraph {" >> graf.dot
cat /tmp/graf.txt >> graf.dot
echo "}" >> graf.dot
Nato pa DOT datoteko narišemo v graf – v danem primeru bomo graf narisali v PNG datoteko (podprti so številni formati, tudi PDF):
fdp -Tpng -GK=1 -o graf.png graf.dot
Rezultat je PNG slika z grafom transakcij.
Graf transakcij.
Zaključek
Kot smo torej videli, je mogoče z odprtokodnimi orodji izvajati enostavne, pa tudi bolj kompleksne analize velikih količin podatkov. PostgreSQL zmore obdelati zares ogromne količine podatkov – ena tabela lahko vsebuje do 32 TB podatkov, posamezen stolpec pa do 1,6 TB. Poleg tega lahko PostgreSQL ukaze “skriptamo”, torej jih zapišemo v skripte, ki se izvajajo iz ukazne vrstice oziroma jih lahko s pomočjo Crona izvajamo periodično in samodejno. To storimo s parametrom -c:
psql finance -c "...psql ukaz...;"
Ker se ukaz izvaja iz ukazne vrstice znotraj narekovajev (“…psql ukaz…;”), lahko nastopijo težave pri uporabi narekovajev v samem PostgreSQL ukazu. Zato je narekovaje potrebno zapisati v tim. ubežni obliki (ang. escape): “\”. Primer:
psql finance -c "copy(select '\"' || placila.imetnik || '\"->\"' || podjetja.ime || '\" [label=\"' || sum(placila.znesek_v_eur)::numeric(16,2) || ' EUR\", fontsize=15]' from placila, podjetja, trr where (placila.prejemnik_trr = trr.transakcijski_racun) and (trr.maticna_stevilka = podjetja.maticna_stevilka) group by placila.imetnik, podjetja.ime) to '/tmp/graf.txt';"
V tem prispevku smo si ogledali le nekaj bolj enostavnih primerov analiz in nekaj najbolj pogosto uporabljanih PostgreSQL ukazov, za kaj več pa je priporočljivo prebrati dokumentacijo, ki je prosto dostopna na spletni strani postgresql.org. Vseeno pa je iz pričujočega opisa podatkovnega strežnika PostgreSQL razvidno, da gre za zmogljivo orodje, ki ga je mogoče koristno uporabiti pri analizi najrazličnejših vrst podatkov, in ki se kosa tudi z dragimi komercialnimi orodji.
Pri pripravi tega prispevka so bili uporabljeni (izmišljeni) testni podatki, ki se nahajajo v datoteki testni_podatki.ods.
Ključne besede: baze podatkov, Odprta koda, PostgreSQL, statistika