Gagnasafnsfræði, haust 2011

Verkefni 11 - Vöruhús gagna

Lausnum skal skilað í hólf viðkomandi dæmakennara (sjá lista yfir dæmatíma).

Skiladagur: þriðjudaginn 22. nóvember fyrir kl 16:00

Í þessu verkefni þá eigið þið að hanna vöruhús gagna fyrir netmælingargögn sem innihalda upplýsingar um niðurhal (e. download).

Gefið ykkur eftirfarandi forsendur við úrlausn á verkefninu.

Lausn:

Hönnun á töflum:
Skilgreinum nýtt ENUM tag sem inniheldur annaðhvort gildið 'innlent' eða 'erlent'.
```
CREATE TYPE srctag AS ENUM ('innlent', 'erlent');
```
Skilgreinum gagnatöfluna okkar sem mun innihalda samantekt á netmælingargögnum. Taflan hefur flatan strúktúr til að koma í veg fyrir vandamál þegar víddir breytast (t.d. IP bókhaldið eða íslensk IP net). Þá eru tilheyrandi gögn felld inn í gagnatöfluna og þeim er bætt við í forvinnslu.
```
CREATE TABLE summary
(
	date DATE NOT NULL,
	month CHAR(6) NOT NULL,
	dst_ip INET NOT NULL,
	src_tag SRCTAG NOT NULL,
	dst_tag VARCHAR(32) NOT NULL,
	bytes BIGINT NOT NULL,
	PRIMARY KEY (date,dst_ip,src_tag,dst_tag)
);
```
Aðallykill: Primary key fyrir töfluna er (date,dst_ip,src_tag,dst_tag) þeas. taflan inniheldur magntölu fyrir tiltekinn dag, destination IP tölu, uppruna (src_tag) og bókhaldslykil (dst_tag). Ef tvær eða fleiri færslur innihalda sömu gildi í dálkum aðallykilsins þá er ein lína fyrir þær í töflunni og bytes inniheldur summuna.
Aukadálkur: month dálkurinn er til þæginda, en hann er háður date dálknum og það á alltaf að gilda að to_char(date,'YYYYMM')=month.
Skipting: Skiptum töflunni samkvæmt mánuðum (ein tafla per mánuð).
Flýtivísar: Höfum amk. trévísi á dst_ip og hakkavísi á dst_tag. Auk þess mun skiptingin sjá til þess að fyrirspurnir fyrir tiltekinn mánuð rati í rétta töflu.
Hreinsun:
Við hendum út færslum um upphal í úrvinnslunni. Við notum ekkert gildin um pakkafjölda, tímasetningu þegar sending endaði og samskiptastaðal.
Forvinnsla:
Í forvinnslu þá reiknum við út eða sækjum eftirfarandi statískar upplýsingum miðað við gefnar forsendur þegar forvinnslan á sér stað (IP bókhaldið og lista yfir íslensk IP net á þeim tíma):
1. date: Reiknum dagsetningu út frá tímasetningu þegar sending hófst (start time)
2. month: Reiknum mánuð út frá tímasetningu þegar sending hófst (start time)
3. src_tag: Flettum Source IP upp í núverandi lista af íslenskum IP tölum. src_tag='innlent' eða src_tag='erlent' eftir því hver niðurstaðan er.
4. dst_tag: Flettum upp bókhaldslykli fyrir Destination IP í IP bókhaldinu.

Fyrir hverja skrá þá er reiknuð summa af gagnamagni fyrir allar færslur sem hafa sameiginleg gildi í dálkunum (date,dst_ip,src_tag,dst_tag). Samantektin er notuð í skráningu í vöruhúsið.

Skráning í vöruhús:
Við viljum framkvæma svokallað MERGE eða UPSERT (UPDATE but on failure INSERT) fyrir ný gögn, því ef við erum að skrá færslu og það er engin lína í töflunni sem passar við nýju færsluna, þá viljum við gera INSERT, en ef það er þegar til lína þá viljum við gera UPDATE og uppfæra bytes gildið.
MySQL og Oracle hafa góðan stuðning við UPSERT, en PostgreSQL hefur því miður mjög takmarkaðan stuðning. Það er á TODO listanum hjá þeim að útfæra UPSERT, en þangað til er hægt að finna sig fram hjá því með krókaleiðum, t.d. triggers eða reglum, en það verður ekki farið út í það hér.
Við skulum gera ráð fyrir að það sé UPSERT virkni á töflunni þ.a. INSERT skipun fyrir nýja færslu gerir UPDATE ef það er primary key violation (árekstur við línu í töflunni sem hefur sama primary key.)
Bókhald um raðnúmer:
Við skulum halda bókhald um raðnúmer í sérstakri töflu í vöruhúsinu:
```
CREATE TABLE files
(
	source VARCHAR(16) NOT NULL,
	nextseq INTEGER NOT NULL,
	PRIMARY KEY (source)
);
```
Innsetning á gögnum:
Þegar við vinnum úr skrá þá tökum við saman allar færslur með sameiginleg gildi í (date,dst_ip,src_tag,dst_tag) og reiknum summu fyrir gagnamagn.
Við tökum síðan allar samantektirnar og skráum þær í vöruhúsið í einni hreyfingu þ.a. annaðhvort er allt eða ekkert skráð.
Dæmi um færslur í skrá:
```
#Source IP;Destination IP;Packets;Bytes;Start;End;Protocol;Type
74.125.230.140;130.208.143.42;18350;14680064;2011-11-14 13:47:12;2011-11-14 13:49:52;TCP;Niðurhal
74.125.230.140;130.208.143.42;1734;1734183;2011-11-14 13:49:50;2011-11-14 13:52:40;TCP;Niðurhal
173.194.65.105;130.208.143.20;41832;51731473;2011-11-14 13:45:10;2011-11-14 13:55:29;TCP;Niðurhal
```
Dæmi um innsetningu á ofangreindum færslum:
```
BEGIN;
INSERT INTO summary (date,month,dst_ip,src_tag,dst_tag,bytes)
     VALUES ('2011-11-14', '201111', '130.208.143.42', 'erlent', 'A', 16414247);
INSERT INTO summary (date,month,dst_ip,src_tag,dst_tag,bytes)
     VALUES ('2011-11-14', '201111', '130.208.143.20', 'erlent', 'B', 51731473);
COMMIT;
```
Til að viðhalda bókhaldinu um raðnúmer sem segir til um hvaða skrár er búið að klára þá skulum við líka uppfæra töfluna yfir raðnúmer í sömu hreyfingu.
Grf. að við séum að vinna úr skránni MULI-100.gz og þá er (MULI,100) í töflunni files.
```
BEGIN;
INSERT INTO summary (date,month,dst_ip,src_tag,dst_tag,bytes)
     VALUES ('2011-11-14', '201111', '130.208.143.42', 'erlent', 'A', 16414247);
INSERT INTO summary (date,month,dst_ip,src_tag,dst_tag,bytes)
     VALUES ('2011-11-14', '201111', '130.208.143.20', 'erlent', 'B', 51731473);

UPDATE files SET nextseq=101 WHERE source='MULI';
COMMIT;
```
Þetta tryggir samræmi í bókhaldinu yfir næsta raðnúmer og raunverulega skráningu á gögnum. Þá getum við aldrei lent í því að vinna óvart tvisvar úr sömu skrá (og tvískrá gögnin í henni).
Gögn sótt af FTP svæði:
Látum nextseq(x) vera nextseq gildið í files töflunni fyrir gefið forskeyti.
Við notum eftirfarandi ferli þegar við sækjum skrár af FTP svæðinu.
Skoðum reglulega FTP svæðið (t.d. 15 mínútna fresti).
Fyrir hverja skrá FORSKEYTI-RADNR þá:
- Ef RADNR < nextseq(FORSKEYTI) þá eyðum við skránni (búið að vinna úr henni áður.)
- Ef RADNR > nextseq(FORSKEYTI) þá hoppum við yfir skrána og gerum ekkert við hana.
- Ef RADNR = nextseq(FORSKEYTI) þá sækjum við skrána, vinnum úr henni og eyðum henni síðan.
Ef engin skrá með rétt raðnúmer hefur borist í meira en X tíma þá sendum við tilkynningu á umsjónarmenn kerfisins um að skrár séu ekki að berast.
Gagnamagn í vöruhúsi:
Áætlið gróflega fjölda nýrra raða í vöruhúsinu per dag miðað við ykkar hönnun.
Við höfum eina skráningu per dagsetningu/destination IP/src_tag/dst_tag.
dst_tag ræðst af destination, svo þetta er í raun ein skráning per dagsetningu/destination IP/src_tag.
src_tag hefur tvo möguleika ('innlent' eða 'erlent') og því að hámarki tvær færslur per dagsetningu/destination IP.
Hámarksfjöldi færslna per dag er því jafn hámarksfjölda mismunandi destination IP talna.

Samtals erlent niðurhal per bókhaldslykil fyrir tiltekinn mánuð

SELECT dst_tag,SUM(bytes)
FROM summary
WHERE src_tag='erlent'
  AND month='201105'
GROUP BY dst_tag;

Uppfletting á erlendu niðurhali per mánuð fyrir tiltekna IP tölu

SELECT month,SUM(bytes)
FROM summary
WHERE src_tag='erlent'
  AND dst_ip='1.2.3.4'
GROUP BY month;

Uppfletting á erlendu niðurhali per mánuð fyrir tiltekinn bókhaldslykil

SELECT month,SUM(bytes)
FROM summary
WHERE src_tag='erlent'
  AND dst_tag='X'
GROUP BY month;