Mit dem ndwriter werden per SQL relationale Datenbanken im portablen NDF Format erstellt. Diese NDF (nadas data files) können dann per SQL im ndreader, bzw. im ndwriter selbst, ausgewertet werden.

In diesem Tutorial lernen Sie

- Eine Tabellenstruktur einzurichten.

- Ein NDF mit einem Erstbestand zu erzeugen.

- Eine Tabelle zu kopieren.

- Daten zwischen NDF zu kopieren.

- Änderungen in einer Tabelle zu verarbeiten (Insert/Delete).

- Eine Datenstruktur zu ändern (Alter Table).

- Massen-Updates zu verarbeiten.

SQL-Grundkenntnisse werden bei diesem Tutorial vorausgesetzt.

Grundlegende Einführungen in SQL finden Sie leicht im Internet (Suche nach "SQL Tutorial" 1,8 Mio Treffer).

Wirklich lesenswert: C.J. Date "An Introduction to Database Systems", erste Ausgabe 1974, 8. Ausgabe 2003 und seine bis heute gern zitierte "city, supplier, parts" Database.

Die Daten: Die Testdaten für dieses Tutorial werden durch Scannen von Dateiverzeichnissen gewonnen.

Wir verwenden hier die /usr Verzeichnisse zweier Linux Rechner, mit unterschiedlichen Linux Versionen.

Insgesamt sind das ca. 340.000 Datensätze.

Im NDF sieht das dann folgendermaßen aus, die Bedeutung der angezeigten Werte wird im weiteren Fortgang des Tutorials erläutert.

SQL:

describe stat

UALIFIED_COLUMNNAME REC_COUNT NULL_COUNT DUPL_COUNT

------------------------------------------------------------------

MYSCANS.OPENSUSE10.FNAME : 238912 0 81868

MYSCANS.OPENSUSE10.MD5 : 238912 0 25283

MYSCANS.OPENSUSE10.MYGROUP : 238912 0 238896

MYSCANS.OPENSUSE10.PATHNAME : 238912 0 222721

MYSCANS.OPENSUSE10.SIZE : 238912 0 198033

MYSCANS.SUSE8.FNAME : 92502 0 24994

MYSCANS.SUSE8.MD5 : 92502 0 6889

MYSCANS.SUSE8.MYGROUP : 92502 0 92493

MYSCANS.SUSE8.PATHNAME : 92502 0 88563

MYSCANS.SUSE8.SIZE : 92502 0 70125

Dies ist der Anfangsdatenbestand, der fortlaufend verändert wird.

2 Import der Daten

Die Import-Dateien hierzu wurden mit folgenden Kommandos erzeugt:

openSuse10

find -P /usr -type f -printf " %g; %f;%h; %s;" -exec md5sum \{\} \;

Suse8

find /usr -type f -printf " %g; %f;%h; %s;" -exec md5sum \{\} \;

Die Kommandos erzeugen rekursiv eine Dateiliste mit folgenden Werten:
Gruppe, Dateiname, Verzeichnisname, Größe in Byte und dem MD5- Hashcode der Datei. Der MD5 ist mit sehr hoher Wahrscheinlichkeit eindeutig.

Sie können die hierzu erzeugten Datei-Scanns und ein NDF von unserer Seite herunterladen oder eigene erzeugen.

Tipp: Wenn Sie mit eigenen Scanns arbeiten, verwenden Sie am besten statt dem /usr Verzeichnis, ein Verzeichnis, das Sie schon immer analysieren oder bereinigen wollten. Das Auffinden von irgendwie/irgendwann einmal erzeugten und vergessenen Dateikopien ist, wie wir zeigen werden, auf diese Art sehr einfach.

Dieses Beispiel lässt sich im Übrigen per "find" Kommando sehr gut variieren, z.B. könnte man auch das Änderungsdatum, den Owner, die Rechte, etc. einsammeln.

Hinweis: Bemerkenswerterweise wurde das find-Kommando zwischen den Linux-Versionen geändert, der Suse8-Aufruf funktioniert (noch) unter Version 10.3, da -P der Default ist.

2.1 Handhabung des ndwriters

Der ndwriter ist auf den Massenimport von Daten ausgelegt. Gesteuert wird dieses durch ein Import-Skript mit SQL-Anweisungen.

Es ist zwar möglich, den ndwriter ohne Skript zu starten und dann SQL-Befehle direkt über die Tastatur quasi am "command prompt" einzugeben. Sinnvoll ist das nicht.

Wir erläutern hier zunächst die einzelnen Schritte und geben dann weiter unten das komplette SQL-Skript und den ndwriter Programmaufruf im Abschnitt "2.4" an.

Für ein aktives arbeiten mit dem Tutorial empfehlen wir Ihnen nach der gleichen Methode vorzugehen. Erstellen Sie sich ein eigenes Skript mit ihrem gewohnten Texteditor und führen Sie dieses dann jeweils zum Test mit dem ndwriter aus.

2.2 Einrichten einer Tabelle

Zum Anlegen einer Tabelle benötigen wir zunächst ein logisches Schema, dem wir diese Tabelle zuordnen können.

Schritt (1): Einrichten eines Schemas

SQL:

create schema myscans;

Schritt (2): Anlegen der Tabelle.

Die folgende Anweisung legt im Default-Schema eine Tabelle mit Namen suse8 an, vorausgesetzt als Default ist ebenfalls myscans angegeben, können wir schreiben:

create table suse8

( mygroup varchar(20),

fname varchar(256),

pathname varchar (2048),

size dec(11), -- no Terabyte Files

md5 char(32) );

bzw. in der Langform

create table myscans.suse8

( mygroup�

�);

2.3 Laden der Daten (sql load)

Im ndwriter gibt es als SQL-Erweiterung einen load- Befehl, mit dem eine Datei in eine Tabelle importiert werden kann.

Das Ergebnis des Datei-Scans befindet sich im aktuellen Verzeichnis in der Datei suse8-scan.dat.

Wir laden die Daten mit dem load-Befehl:

load data infile 'suse8-scan.dat'

into table suse8

fields terminated by ';'

lines terminated by '\n';

Tatsächlich hätte genügt zu schreiben:

load data infile 'suse8-scan.dat'

into table suse8;

Die Zeichen ';' und '\n' sind die Default-Trennzeichen für Felder (fields) und Zeilen (lines).

Wir starten danach den ndreader mit

ndreader -s myscans

und führen die erste Abfrage aus. Die Abfrage listet die Anzahl Dateien pro "group".

SQL:

select mygroup, count(*)
from myscans.suse8

group by mygroup

order by count(*);

Antwort

bin!1

lp!1

mail!1

tty!3

uucp!3

shadow!8

dialout!28

news!205

root!92253

Das Ergebnis, entspricht nicht ganz dem Gewünschten. Die Gruppennamen beginnen mit einem Leerzeichen. Das liegt an unserem "find Kommando", das diese fehlerhaften führenden Leerzeichen bei 'group' und 'filename' einfügt.

Anstatt das Linux Find-Kommando anzupassen, verwenden wir beim nächsten Versuch das TRIM Feature aus der SQL Load-Anweisung und schalten zusätzlich mit 'set feedback on' die Protokollierung ein.

2.4 Import per Skript

Das Ganze schreiben wir jetzt in die Skript-Datei my.sql, die wir dann beim Starten des ndwriters mit der Option -f angeben.

Skript-Datei my.sql

set feedback on

create schema myscans;

create table suse8

( mygroup varchar(20),

fname varchar(256),

pathname varchar (2048),

size dec(11),

md5 char(32) );

load data infile 'suse8-scan.dat'

into table suse8

trim left;

Wir starten den ndwriter mit
unserem Zertifikat "bin/nd.cxx"
unserer Userid "xtest"
und dem Password "xyz".

-s gibt das Defaultschema (myscans) an

-f die SQL-Skriptdatei my.sql

-a W (Write) überschreibt das evtl. vorhandene NDF

time bin/ndwriter -smyscans -aW -f my.sql xtest xyz bin/nd.cxx

[V:1.0] (LINUX AMD/INTEL)code_set: UTF-8

number of input line(s) processed 92503

table MYSCANS.SUSE8 : number of record(s) inserted 92503

real 0m0.572s

user 0m0.520s

sys 0m0.048s

Da wir keinen NDF Namen angegeben haben, finden wir das Ergebnis in der Default-Datei "nadas.ndf" im aktuellen Verzeichnis.

Die Ausführungszeit liegt bei ca. einer halben Sekunde für 92.000 Datensätzen.
Diese Verarbeitungszeit erreichen wir nicht auf einen High-End-Server sondern auf einer DELLPowerEdge SC440, das ist die Preisklasse unterhalb von € 500,- (Euro fünfhundert).

Das erzeugte NDF hat ca. 58% der Größe der importierten Rohdaten.

du -h suse8-scan.dat nadas.ndf

13M suse8-scan.dat

7.5M nadas.ndf

Wir starten den ndreader im aktuellen Verzeichnis, dieser lädt automatisch das NDF nadas.ndf.

bin/ndreader -s myscans

Mit den Kommandos describe table und describe stat Datenstruktur und Statistik anzeigen.

SQL:

describe table

UALIFIED_TABNAME COL_NAME PRIMARY_KEY DATA_TYPE LENGTH PRECISION SCALE

-------------------------------------------------------------------------------

MYSCANS.SUSE8 MYGROUP -- 5 20 -- 1

MYSCANS.SUSE8 FNAME -- 5 256 -- 1

MYSCANS.SUSE8 PATHNAME -- 5 2048 -- 1

MYSCANS.SUSE8 SIZE -- 4 6 11 0

MYSCANS.SUSE8 MD5 -- 5 32 -- --

SQL:

describe stat

UALIFIED_COLUMNNAME REC_COUNT NULL_COUNT DUPL_COUNT

-------------------------------------------------------------

MYSCANS.SUSE8.FNAME : 92503 0 24994

MYSCANS.SUSE8.MD5 : 92503 0 6889

MYSCANS.SUSE8.MYGROUP : 92503 0 92494

MYSCANS.SUSE8.PATHNAME : 92503 0 88564

MYSCANS.SUSE8.SIZE : 92503 0 70125

Durch Verwendung der TRIM LEFT Anweisung beim Import erhalten wir jetzt das gewünschte Ergebnis, ohne die führenden Leerzeichen.

SQL:

select mygroup, count(*)

from myscans.suse8

group by mygroup

order by count(*);

Antwort

bin!1

lp!1

mail!1

tty!3

uucp!3

shadow!8

dialout!28

news!205

root!92253

2.5 Normalisierung des Datenmodells

nadas führt eine Art automatisch Normalisierung durch, die sollten Sie kennen, um ihre Zeit nicht für unnötige Normalisierungen zu verschwenden.

Unser obiges Datenmodell ist zumindest auf den ersten Blick denkbar schlecht, da wir bei jeder Datei den vollen Verzeichnisnamen speichern.

SQL:

select count (distinct pathname) from suse8;

3939

Es gibt nur 3939 unterschiedliche Pfade, die Information hierzu redundant dargestellt belegt (theoretisch) mehr als 3 MB.

SQL:

select sum(length(pathname)) from suse8;

3044280

Solche redundanten Einträge beseitigt man im Allgemeinen durch Normalisierung.

Was können wir durch Normalisierung erreichen? Wenn wir z.B. eine neue Tabelle mit den Verzeichnisnamen und einer eindeutigen, möglichst kurzen ID anlegen und dann auf diese verweisen?

Antwort: Was die Größe des NDF betrifft, nichts!

In nadas werden alle identischen Strings in einem VARCHAR-Spalte automatisch zu einem Eintrag zusammengefasst.

Es genügt, wenn wir dieses bei unserer Datenmodellierung berücksichtigen.

Also wie hier, der Zufall will es, durch die Trennung von Dateiname und Pfad, bei einem Telefonverzeichnis Trennung von Name und Vorname, bei einem Adressbuch Trennung von Ort und PLZ, von Straße und Hausnummer, etc..

Mehr ist nicht notwendig.

2.6 Weitere Load-Optionen

Das oben verwendete Load-Kommando bietet diverse zusätzliche Optionen.

Häufig benötigt wird das direkte Laden von Dateien im MS-Windows Format, z.B. direkt von einer Samba-Partition oder nach dem (binären / unkonvertiertem) Kopieren.

Verarbeitung windows Zeilenende in load

load data infile 'test.csv'

into table test

lines terminated by '\r\n';

CSV-Exportierte Daten (comma separated values) enthalten häufig eine oder mehrere Überschriftzeilen, die nicht mit importiert werden dürfen.

Überlese Zeilen am Dateianfang

load data infile 'test.csv'

into table test

lines terminated by '\r\n'

ignore 2 lines;

Schließlich besteht die Möglichkeit, die Daten statt von einer Datei direkt aus einem anderen Prozess zu verarbeiten:

load data command statt infile

load data

command 'find /usr -type f -printf " %g; %f;%h; %s"'

into table myscans.suse8;

Dieses ist auch eine gute Variante um sehr große Importdateien zu verarbeiten, die nach Möglichkeit nur komprimierte auf den Laufwerken gespeichert werden.

Wir verwenden dieses Feature z.B. bei den Geo-Daten, indem wir direkt aus den gepackten Dateien mit dem zcat Kommando lesen:

create table geos.tags

(

id int,

tag varchar(80),

val varchar(80)

);

load data command 'zcat waytags.csv.gz'

into table geos.tags

fields terminated by ','

lines terminated by '\n'

ignore 1 lines;

3 Eine neue Tabelle hinzufügen

Wir möchten jetzt, wie angekündigt, zusätzlich zu den SUSE-Linux8- Daten den Scann zu openSUSE10 importieren.

Wie nicht anders zu erwarten, hat dieser bereits mehr als doppelt soviele Einträge.

Wir können jetzt wahlweise die Daten dem bestehenden NDF hinzufügen oder nochmal von vorne beginnen oder ein zusätzliches NDF anlegen.

Wir werden im Folgenden mehrere Alternativen ausführen.

3.1 Import in ein neues NDF

Wir führen einen komplett neuen Import für beide Dateien aus.

Wir ergänzen hierzu unser SQL-Skript my.sql um die zweite Tabelle und den zweiten LOAD-Befehl und erhalten das folgende Skript.

SQL-Script my.sql

set feedback on

create schema myscans;

create table suse8

( mygroup varchar(20),

fname varchar(256),

pathname varchar (2048),

size dec(11),

md5 char(32));

create table opensuse10

( mygroup varchar(20),

fname varchar(256),

pathname varchar (2048),

size dec(11),

md5 char(32));

load data infile 'suse8-scan.dat'

into table suse8

trim left;

load data infile 'opensuse10-scan.dat'

into table opensuse10

trim left;

commit work;

Die letzte Zeile "commit work" ist neu.

Der ndwriter führt vor dem Speichern automatisch ein commit auf allen Tabellen aus. Nur deshalb hat auch unser erstes Skript funktioniert.

Es ist guter Stil, commits explizit zu schreiben.

Wir können jetzt den ndwriter mit dem neuen SQL-Skript aufrufen.

time bin/ndwriter -dmyscan.ndf -smyscans -aW -f my.sql xtest xyz bin/nd.cxx

[V:1.0] (LINUX AMD/INTEL)code_set: UTF-8

number of input line(s) processed 92503

table MYSCANS.SUSE8 : number of record(s) inserted 92503

number of input line(s) processed 238913

table MYSCANS.OPENSUSE10 : number of record(s) inserted 238913

commit all tables

real 0m2.153s

user 0m1.964s

sys 0m0.188s

Die Importzeit beträgt knapp über 2 Sekunden für 340.000 Datensätze.

Das Ergebnis steht dieses Mal nicht in nadas.ndf sondern in myscan.ndf, das wir oben mit der -d Option angegeben haben!

3.2 Hinzufügen einer neuen Tabelle

Alternativ hätte wir die Tabelle auch zusätzlich in unserem nadas.ndf aus dem ersten Import anlegen können.

Das SQL-Skript hierzu nennen wir my2.sql.

set feedback on

create table opensuse10

( mygroup varchar(20),

fname varchar(256),

pathname varchar (2048),

size dec(11),

md5 char(32));

load data infile 'opensuse10-scan.dat'

into table opensuse10

trim left;

commit work;

Der Programmaufruf (ohne -a Option!)

time bin/ndwriter -smyscans -f my2.sql xtest xyz bin/nd.cxx

[V:1.0] (LINUX AMD/INTEL)code_set: UTF-8

Data loaded

number of input line(s) processed 238913

table MYSCANS.OPENSUSE10 : number of record(s) inserted 238913

commit all tables

real 0m1.666s

user 0m1.488s

sys 0m0.176s

Das Ergebnis ist das gleiche. Auch hier haben wir nach der Ausführung ein NDF, das beide Tabellen enthält.

Achten Sie bei diesem Aufruf auf die Zeile 'Data loaded'.

Der ndwriter hat nadas.ndf aus dem aktuellen Verzeichnis gelesen, das ist der Default-Modus.

Dieses Default-Verhalten hatten wir bei den vorherigen Aufrufen mit Option -aW (für Schreiben/Write) explizit ausgeschaltet

Am Ende haben wir das gewünschte Ergebnis, ein NDF mit beiden Tabellen.

Trotzdem passiert hier etwas anderes als man vermuten könnte.

Das NDF nadas.ndf wird hier nicht geändert sondern mit beiden Tabellen neu angelegt.

Die erste Tabelle wird aus dem NDF kopiert, die zweite wird per SQL-LOAD Befehl importiert.

Das neue NDF (unter dem gleichen Namen) enthält unsere Signatur und den aktuellen Zeitstempel.

D.h. Daten aus einem NDF können, siehe oben, einfach kopiert werden, das Original ist nicht änderbar.

3.3 Variante mit explizite NDF I/O

Der Name des NDF wurde bei allen bisherigen Beispielen beim Start des ndwriters angegeben.

Im letzten Beispiel wird das Original NDF am Ende überschrieben, weil das neue NDF zwangsläufig den gleichen Namen hat.

Für komplexere Fälle, bei denen mehrere NDF gelesen und geschrieben werden sollen, verfügt der ndwriter über explizite I/O Befehle.

Als Variante zum obigen Beispiel wollen wir das Ergebnis auf das neue NDF neu.ndf schreiben.

Wir verwenden dafür das wird das gleiche Skript my2.sql, das wir mit @my2.sql in unser neues Skript einschließen.

Skript my3.sql mit expliziter NDF I/O

open input 'nadas.ndf';

read all;

close input;

@my2.sql

open output 'neu.ndf';

write all;

-- alternativ: write schema myscans;

close output;

Der ndwriter Aufruf hierzu

bin/ndwriter -aO -smyscans -fmy3.sql xtest xyz bin/nd.cxx

-aO steht für automatisch I/O aus (off)

Das Ergebnis steht in neu.ndf im aktuellen Verzeichnis, nadas.ndf wird durch den Aufruf nicht verändert.

3.4 Zusätzliches NDF anlegen

Angenommen, wir dürften für die erste Tabelle nur das Original NDF verwenden.

Dieses könnte in einem konkreten Anwendungsfall eine Preisliste, ein Stückeverzeichnis, etc. enthalten, das mit einer bestimmten Signatur autorisiert sein muss.

In diesem Fall würde man ein neues NDF nur mit der zweiten Tabelle anlegen und für Auswertungen den ndreader mit beiden NDF aufrufen!

Auch hierfür können wir im Prinzip das obere Skript my2.sql verwenden, allerdings fehlt dort die Anweisung zum Einrichten des Schemas, dieses ist noch zu ergänzen:

create schema myscans

@my2.sql

4 Inserts und Deletes

Wir setzen unser Beispiel jetzt unter der Annahme fort, dass unser Verzeichnis /usr auf SUSE8 täglich überwacht werden soll um befürchtete Änderungen durch einen Angreifer zu entdecken.

Hierzu werden wir täglich aktualisierte Datei-Scanns zu SUSE8 verarbeiten.

4.1 Ein NDF pro Scann

Die Lösung ist denkbar einfach, da wir unsere Skripte unverändert beibehalten können und nur den Aufruf des ndwriters anpassen müssen.

Wir verwenden die -d Option und benennen die NDF einfach fortlaufend durch anhängen des Datums an den Dateinamen mit

myscan<JJJJMMTT>.ndf

also z.B.

myscan20080301.ndf

myscan20080302.ndf

, etc.

bin/ndwriter -smyscans -dmyscan20080301.ndf -aW -f my.sql xtest xyz bin/nd.cxx

Wenn wir diese Daten nach dem Import miteinander vergleichen wollen, haben wir zunächst das Problem, dass wir hier NDF mit gleichbenanntem Schema und gleichnamigen Tabellen haben.

Wir zeigen hier eine von sehr vielen Möglichkeiten dieses zu lösen, als Anregung zum weiteren experimentieren mit dem ndwriter.

Wir starten den ndwriter mit

bin/ndwriter -smyscans -aO xtest xyz bin/nd.cxx

öffnen das erste NDF und lesen es ein.

open input 'myscan2000301;'

read all;

Wir benennen die suse8 Tabelle einfach um mit

alter table suse8 rename suse8old;

Als nächstes lesen wir die Daten aus dem zweiten NDF.

open input 'myscan20080302.ndf';

read all;

Jetzt ist es einfach, wir bilden die Differenz der beiden Mengen:

Die (manipulierten) neuen Werte

SQL:

select * from suse8

minus

select * from suse8old;

MYGROUP FNAME PATHNAME SIZE MD5

-----------------------------------------------------------------------------

root usb.h /usr/include 6488 6e5a78c0562eaed5d601a67da4c32a8e

root memory.h /usr/include 1029 b9d9db7be433ccfa0dca9452151f6a9a

number of record(s) selected: 2

Die alten Werte

SQL:

select * from suse8old

minus

select * from suse8;

MYGROUP FNAME PATHNAME SIZE MD5

---------------------------------------------------------------------------

root usb.h /usr/include 6457 7bdde83fc25dbc205106a9f1f420d9dd

root memory.h /usr/include 1029 334be0a5af452bff0c8e13d2810f538d

number of record(s) selected: 2

Die Dateien "usb.h" haben unterschiedliche Größe, bei "memory.h" unterscheidet sich nur der MD5-Hashwert, der allerdings deutlich. Dahinter steckt als Änderung, dass in einer Kommentarzeile von "memory.h" die Buchstabenfolge 'GNU' in 'KUH' geändert wurde.

4.2 Aufbau einer Änderungshistorie

Etwas anspruchsvoller als der erste Ansatz, wäre eine Lösung, die neben dem aktuellen Scann zusätzlich eine Änderungshistorie im NDF führt.

Hierzu definieren wir zunächst zwei neu Tabellen:

diffnew: enthält Datensätze, die nur im neuen Scann sind.

diffold: enthält Datensätze, die nur im alten Scan sind.

Durch Auswertung diffold gegen diffnew lassen sich so die verschiedenen Änderungen wie insert, delete oder update auflisten. Selbst ein 'rename' wäre mittels des MD5 leicht aufzufinden.

diffnew und diffold sind ähnlich aufgebaut wie die suse8 Tabelle, enthalten aber als zusätzliche Spalte das Tagesdatum. Hierfür verwenden wir den neuen Datentyp DATE.

Am Anfang sind beide Tabellen leer, das heißt Dateien die nie in diffold/diffnew erscheinen sind "seit immer" unverändert im System.

Die gesamte Lösung besteht aus zwei Skripten,

einem für den Initialbestand (intial load) und einem für die täglichen Updates.

Skript iniload.sql

-- nadas writer tutorial

-- die Tabellen anlegen

-- und den ersten scan laden

create schema myscans;

create table suse8new

( mygroup varchar(20),

fname varchar(256),

pathname varchar (2048),

size dec(9),

md5 char(32));

create table suse8

( mygroup varchar(20),

fname varchar(256),

pathname varchar (2048),

size dec(9),

md5 char(32));

create table diffold

( datum date,

mygroup varchar(20),

fname varchar(256),

pathname varchar (2048),

size dec(9),

md5 char(32)

primary key (datum,fname,pathname));

create table diffnew

( datum date,

mygroup varchar(20),

fname varchar(256),

pathname varchar (2048),

size dec(9),

md5 char(32)

primary key (datum,fname,pathname));

load data infile 'suse8-scan.dat'

into table suse8;

commit work;

Die oben vereinbarten "primary keys" sind nicht notwendig. Andererseits wird so gewährleistet, dass keine mehrfach Eintragungen zu einer Datei zu einem Tagesdatum möglich sind.

Wir führen zunächst das ini-Skript mit unserem ersten Scann aus, um die Datenstrukturen einzurichten.

time bin/ndwriter -smyscans -d hist.ndf -aW -fsql/iniload.sql xtest xyz bin/nd.cxx

[V:1.0] (LINUX AMD/INTEL)code_set: UTF-8

real 0m0.571s

user 0m0.508s

sys 0m0.060s

Für die täglichen updates wird das folgende Skript diffload.sql verwendet.

Das Skript verwendet am Ende ein paar spezielle SQL-Features, um die Ausgabe zu verschönern.

Inserts in die Diff-Tabellen werden per SQL INSERT, nicht per LOAD erzeugt.

Zum Löschen nicht mehr benötigter Datensätze verwenden wir SQL DELETE.

Das Tagesdatum ermitteln wir hier mit Hilfe der nadas function sysdate(), die das aktuelle Tagesdatum zurückgibt.

Skript diffload.sql

-- lade den neuen scan

set feedback on

set heading on

set format auto

set pagesize 30

set colsep ' '

load data infile 'suse8-scan.dat'

into table suse8new

trim left ;

commit work;

-- ermittle alle records, die nur im neuen Scann sind

insert into diffnew

select sysdate(),*

from suse8new

where (mygroup,fname,pathname,size,md5)

not in (select mygroup,fname,pathname,size,md5

from suse8);

commit work enable keys;

-- ermittle alle records, die nur im alten Scann sind

insert into diffold

select sysdate(),*

from suse8

where (mygroup,fname,pathname,size,md5)

not in (select mygroup,fname,pathname,size,md5

from suse8new);

commit work enable keys;

-- swap tables

alter table suse8 rename hpswap;

commit work;

alter table suse8new rename suse8;

-- Loesche alle alten Datensaetze

-- eine Alternative hierzu

-- waere drop /create table

alter table hpswap rename suse8new;

delete from suse8new;

commit work;

-- das war alles

-- der Rest dient nur der besseren Protokollierung

set feedback off

set heading off

select 'in diffnew gesamt: ' || cast(count(*), char)

from diffnew;

select 'in diffold gesamt: ' || cast(count(*), char)

from diffold;

select 'neueste in diffnew (' || cast(sysdate(),char ) ||') : ' || cast (count(*) , char)

from diffnew

where datum = sysdate();

select 'neueste in diffold (' || cast(sysdate(),char ) ||') : ' || cast (count(*) , char)

from diffold

where datum = sysdate();

Wir rufen jetzt das Skript diffload.sql mit dem neuesten Scann-Ergebnis in Datei suse8-scan.dat auf.

Bitte achten Sie auf die Aufrufoptionen. Warum darf hier, anders als beim ersten Load, nicht die Option -aW stehen?

time bin/ndwriter -smyscans -d hist.ndf -fsql/diffload.sql xtest xyz bin/nd.cxx

[V:1.0] (LINUX AMD/INTEL)code_set: UTF-8

Data loaded

number of input line(s) processed 92503

table MYSCANS.SUSE8NEW : number of record(s) inserted 92503

commit all tables

table MYSCANS.DIFFNEW : number of record(s) inserted 2

commit all tables

table MYSCANS.DIFFOLD : number of record(s) inserted 2

commit all tables

record(s) deleted 92503

commit all tables

in diffnew gesamt: 2

in diffold gesamt: 2

neueste in diffnew (2008-03-11) : 2

neueste in diffold (2008-03-11) : 2

real 0m1.839s

user 0m1.760s

sys 0m0.076s

zur Kontrolle Laden wir jetzt das NDF in den ndreader und selektieren aus den beiden diff-Tabellen, dafür erstellen wir uns das folgende Skript:

Test-Skript t.sql (myformat .sql siehe: 6.1)

@sqlread/myformat.sql

select * from diffnew;

select * from diffold;

und erhalten als Ergebnis:

bin/ndreader -s myscans -f t.sql hist.ndf

[V:1.0] (LINUX AMD/INTEL)

code_set: UTF-8

DATUM MYGROUP FNAME PATHNAME SIZE MD5

-------------------------------------------------------------------------------------

2008-03-11 root usb.h /usr/include 6488 6e5a78c0562eaed5d601a67da4c32a8e

2008-03-11 root memory.h /usr/include 1029 b9d9db7be433ccfa0dca9452151f6a9a

number of record(s) selected: 2

DATUM MYGROUP FNAME PATHNAME SIZE MD5

-------------------------------------------------------------------------------------

2008-03-11 root usb.h /usr/include 6457 7bdde83fc25dbc205106a9f1f420d9dd

2008-03-11 root memory.h /usr/include 1029 334be0a5af452bff0c8e13d2810f538d

number of record(s) selected: 2

5 Erweiterung des Datenmodells

5.1 Anpassung der Tabellen

Wir erweitern jetzt für das SUSE8/openSUS10 Beispiel das Datenmodell um zwei Spalten.

Die Spalten inode, für die inode-Id im Linux-Dateisystem und hlcnt für die Hardlink-Counts.

Die Erweiterung wollen wir nachträglich in die Datenbank myscan.ndf aus " 3.1" einfügen.

Die Daten könnten wir über das neue Scann-Kommando besorgen:

find /usr -type f -printf "%g;%f;%h;%s;%i;%n;" -exec md5sum \{\}

Um zu zeigen wie in den zusätzlichen Spalten die Werte auch nachträglich ergänzt werden können verwenden wir hier:

find /usr -type f -printf "%f;%h;%i;%n"

Wir modifizieren die Tabellen aus 'myscan.ndf' und schreiben das Ergebnis in das neue NDF 'newmyscan.ndf'.

Skript altertabs.sql

-- wir brauchen 2 neue Spalten

-- per Default werden diese "rechts" angefuegt

alter table suse8 add column

( inode dec(9),

hlcnt int );

-- md5 soll die letzte Spalte in der Tabelle sein

-- wir verschieben diese

alter table suse8 move md5 after hlcnt;

-- definiere nachtraeglich einen Primary Key

-- aus Dateiname/Verzeichnisname

alter table suse8 add to primary key fname;

alter table suse8 add to primary key pathname;

alter table opensuse10 add column

( inode dec(9),

hlcnt int );

alter table opensuse10 move md5 after hlcnt;

alter table opensuse10 add to primary key fname;

alter table opensuse10 add to primary key pathname;

commit work;

open output 'newmyscan.ndf';

write all;

close output;

Das neue NDF hat jetzt zusätzliche Spalten, die komplett mit NULL Werten belegt sind.

SQL:

describe stat

UALIFIED_COLUMNNAME REC_COUNT NULL_COUNT DUPL_COUNT

------------------------------------------------------------------

MYSCANS.OPENSUSE10.FNAME : 238913 0 81869

MYSCANS.OPENSUSE10.HLCNT : 0 238913 0

MYSCANS.OPENSUSE10.INODE : 0 238913 0

MYSCANS.OPENSUSE10.MD5 : 238913 0 25283

MYSCANS.OPENSUSE10.MYGROUP : 238913 0 238897

MYSCANS.OPENSUSE10.PATHNAME : 238913 0 222722

MYSCANS.OPENSUSE10.SIZE : 238913 0 198034

MYSCANS.SUSE8.FNAME : 92503 0 24994

MYSCANS.SUSE8.HLCNT : 0 92503 0

MYSCANS.SUSE8.INODE : 0 92503 0

MYSCANS.SUSE8.MD5 : 92503 0 6889

MYSCANS.SUSE8.MYGROUP : 92503 0 92494

MYSCANS.SUSE8.PATHNAME : 92503 0 88564

MYSCANS.SUSE8.SIZE : 92503 0 70125

Anmerkung: 'decribe stat' listet die Spalten in alphabetischer Sortierung auf.

Die Tabellendefinition mit der definierten Spaltenreihenfolge erhalten wir mit 'describe table'.

SQL:

describe table

UALIFIED_TABNAME COL_NAME PRIMARY_KEY DATA_TYPE LENGTH PRECISION SCALE

-------------------------------------------------------------------------------

MYSCANS.OPENSUSE10 MYGROUP -- 5 20 -- 1

MYSCANS.OPENSUSE10 FNAME 1 5 256 -- 1

MYSCANS.OPENSUSE10 PATHNAME 1 5 2048 -- 1

MYSCANS.OPENSUSE10 SIZE -- 4 6 11 0

MYSCANS.OPENSUSE10 INODE -- 4 5 9 0

MYSCANS.OPENSUSE10 HLCNT -- 0 4 -- --

MYSCANS.OPENSUSE10 MD5 -- 5 32 -- --

MYSCANS.SUSE8 MYGROUP -- 5 20 -- 1

MYSCANS.SUSE8 FNAME 1 5 256 -- 1

MYSCANS.SUSE8 PATHNAME 1 5 2048 -- 1

MYSCANS.SUSE8 SIZE -- 4 6 11 0

MYSCANS.SUSE8 INODE -- 4 5 9 0

MYSCANS.SUSE8 HLCNT -- 0 4 -- --

MYSCANS.SUSE8 MD5 -- 5 32 -- --

Frage: Welche Einfluss haben die NULL Spalten auf die (externe) Größen des NDF? Stellen Sie bitte fest, wie viel Plattenplatz das NDF vor /nach der Erweiterung belegt.

5.2 update per sql-load

Wir werden jetzt die beiden neuen Spalten per SQL-LOAD belegen.

Die neue Importdatei enthält die Primärschlüsselspalten (FNAME, PATHNAME) und die beiden neuen Spalten (INODE, HLCNT).

Der ndwriter ist auf Massen-Inserts, spezielle per Load-Befehl ausgelegt, um in gleicher Art Updates ausführen zu können, ist über den Primary Key (Primärschlüssel) eine spezielle Update-Logik realisiert:

Sätze mit gleichem Primärschlüssel werden beim Commit zusammengefasst. Wobei bei mehreren Werten für eine Spalte zu einem Primärschlüssel der (zeitlich) zuletzt Eingefügte gilt.

Anschaulich gesprochen werden die Werte über den Hauptschlüssel (primary key) zusammengemischt.

In unserem Fall ist der der vollständige Pfadname Primärschlüssel.

SQL-Skript loaddiff.sql

set feedback on

load data infile 'opensuse10-diff.dat'

into table opensuse10

(fname,pathname,inode,hlcnt);

load data infile 'suse8-diff.dat'

into table suse8

(fname,pathname,inode,hlcnt);

commit work enable keys;

Auszug aus der Datei 'opensuse10-diff.dat'

libstdc++.so.6.0.9;/usr/lib;966048;1

liblcms.so.1.0.16;/usr/lib;965893;1

libxcb.so.1.0.0;/usr/lib;966143;1

Notify.pm;/usr/lib/perl5/vendor_perl/Immunix;1768059;1

Severity.pm;/usr/lib/perl5/vendor_perl/Immunix;1766155;1

SubDomain.pm;/usr/lib/perl5/vendor_perl/Immunix;1766156;1

Reports.pm;/usr/lib/perl5/vendor_perl/Immunix;1766154;1

Import der Daten

time bin/ndwriter -smyscans -d newmyscan.ndf -fsql/loaddiff.sql xtest xyz bin/nd.cxx

[V:1.0] (LINUX AMD/INTEL)code_set: UTF-8

Data loaded

number of input line(s) processed 238913

table MYSCANS.OPENSUSE10 : number of record(s) inserted 238913

number of input line(s) processed 92503

table MYSCANS.SUSE8 : number of record(s) inserted 92503

commit all tables

real 0m3.656s

user 0m3.368s

sys 0m0.284s

Wir prüfen das NDF wieder im ndreader und erhalten:

SQL:

describe stat

UALIFIED_COLUMNNAME REC_COUNT NULL_COUNT DUPL_COUNT

------------------------------------------------------------------

MYSCANS.OPENSUSE10.FNAME : 238913 0 81869

MYSCANS.OPENSUSE10.HLCNT : 238913 0 238870

MYSCANS.OPENSUSE10.INODE : 238913 0 8539

MYSCANS.OPENSUSE10.MD5 : 238913 0 25283

MYSCANS.OPENSUSE10.MYGROUP : 238913 0 238897

MYSCANS.OPENSUSE10.PATHNAME : 238913 0 222722

MYSCANS.OPENSUSE10.SIZE : 238913 0 198034

MYSCANS.SUSE8.FNAME : 92503 0 24994

MYSCANS.SUSE8.HLCNT : 92503 0 92468

MYSCANS.SUSE8.INODE : 92503 0 2168

MYSCANS.SUSE8.MD5 : 92503 0 6889

MYSCANS.SUSE8.MYGROUP : 92503 0 92494

MYSCANS.SUSE8.PATHNAME : 92503 0 88564

MYSCANS.SUSE8.SIZE : 92503 0 70125

Aufgabe (nur für die Misstrauischen): Erzeugen Sie zur Kontrolle zwei weitere Tabellen, in die Sie die Daten nicht wie oben, sondern direkt aus einem Datei-Scann mit allen Spalten einspielen.

Verwenden Sie dafür die gleichen Tabellennamen in einem neuen Schema.

Der finale Vergleich könnte dann z.B. so aussehen.

set feedback on

select * from myscans.suse8

minus

select * from fullscan.suse8;

select * from fullscan.suse8

minus

select * from myscans.suse8;

select * from myscans.opensuse10

minus

select * from fullscan.opensuse10;

select * from fullscan.opensuse10

minus

select * from myscans.opensuse10;

6 Beispiel Auswertungen

6.1 Formatierung der Ausgabe

ndreader und ndwriter geben die Ergebnisse von SQL-Abfragen in einem einfach weiterzuverarbeitenden Format aus.

Ohne Überschrift ohne Endezeile, ohne irgendeine Ausrichtung der Spalten, sondern einfach fortlaufen mit '!' als Spaltentrennzeichen und newline nach jedem Datensatz.

Aus historischen Gründen ist in nadas '!' Default-Spaltentrennzeichen, statt den vielleicht naheliegender erscheinenden Zeichen ',' ';' oder '|' .

Dieses Default-Format ist einfach weiterzuverarbeiten, als Druckausgabe brauchen wir ein lesbares Format.

Das Format definieren wir uns in einem Skript und schalten es dann einfach durch Ausführung dieses Skripts ein.

Skript myfomat.sql

set format auto

set heading on

set feedback on

set colsep ' '

set pagesize 20

6.2 Suche nach Duplikaten

Bei der folgenden Suche gehen wir davon aus, dass Dateien mit unterschiedlichem Inhalt auch einen unterschiedlichen MD5- Hash haben.

Insbesondere für Dateien gleicher Größe sollte das (fast) sicher sein.

Wir suchen zunächst zu Sicherheit nach einem MD5-Wert, zu dem es unterschiedlich große Dateien gibt.

Der Test ergibt erwartungsgemäß Null Treffer.

Die weiteren Tests bauen hierauf auf und sind im folgenden inline kommentiert.

Duplikate sind unter Linux nicht einfach über das Zählen der doppelten MD5 ermittelbar.

Eine Datei kann in mehreren Verzeichnissen eingetragen sein über sogenannte Hardlinks. Die folgenden Auswertungen berücksichtigen dieses.

SQL-Skript simple.sql

-- Beispiel Auswertungen Datei-Scanns auf Linux File-System

-- nadas ndwriter Tutorial

-- Die Formatierung der Ausgabe

set format auto

set heading on

set colsep ' '

set pagesize 20

set feedback on

-- Test1 gibt es eine MD5 Kollision in opensuse10?

select md5, count(distinct(size)) cnt, count(distinct(inode)) cnt2

from opensuse10

group by md5

having cnt > 1

order by cnt;

-- ermittle den Plattenplatz, Hardlinks nicht doppelt zaehlen!

select 'suse 8' scann, count(*) cnt,cast(sum(size/hlcnt),int) size

from suse8;

-- dito opensuse10

select 'opensuse 10' scann, count(*) cnt,cast(sum(size/hlcnt),int) size

from opensuse10;

-- gibt es Dateien mit gleichem Inhalt in suse8 und opensuse10

select 'Gemeinsam:', count(*) cnt, cast(sum(size / hlcnt),int) size from opensuse10

where md5 in (select md5 from suse8);

-- dito, aber nur die nicht leeren!

select 'Gemeinsam (ohne triviale):', count(*) cnt, cast(sum(size/hlcnt),int) size from opensuse10

where md5 in (select md5 from suse8 where size > 0);

-- die groessten Dateien

select fname,size

from opensuse10

where size >= ALL (

select max(size) / 2 tot_size

from opensuse10) ;

-- Vergleiche die (FilesystemLinkcounts), gegen die

-- gefundenen Dateien

-- sollte uebereinstimmen, bzw. kleiner sein

select inode,count(*) cnt ,max(hlcnt) Hardlink_Count from suse8

group by inode

having cnt > 20

order by cnt;

-- Suche nach (echten) Duplikaten in opensuse10

-- d.h. gleicher MD5-Hash aber andere Inode-ID !

-- durch silent on wird nur die Summenzeile ausgeben

set silent on

select md5, count(distinct(inode)) inode_cnt, min (hlcnt) min_cnt, max(hlcnt) max_cnt, min(size) filesize

from opensuse10

where size > 0

group by md5

having inode_cnt > 1;

set silent off

-- dito aber mindestens 30 echte Duplikate!

select md5, count(distinct(inode)) inode_cnt, min (hlcnt) min_cnt, max(hlcnt) max_cnt, min(size) filesize

from opensuse10

where size > 0

group by md5

having inode_cnt >= 30

order by inode_cnt;

Wir führen das Skript aus und leiten das Ergebnis in die Datei test.out um. :

time bin/ndreader newmyscan.ndf -smyscans -otest.out -f sqlread/simple.sql

[V:1.0] (LINUX AMD/INTEL)

code_set: UTF-8

real 1m6.380s

user 0m25.502s

sys 0m40.879s

Die komplette Testausgabe:

karis@dragonfly:~/nadas/nadas_scan> cat test.out

MD5 CNT CNT2

----------------

number of record(s) selected: 0

SCANN CNT SIZE

------------------------

suse 8 92503 1383830137

number of record(s) selected: 1

SCANN CNT SIZE

------------------------------

opensuse 10 238913 4801518385

number of record(s) selected: 1

column[1] CNT SIZE

--------------------------

Gemeinsam: 14906 50771471

number of record(s) selected: 1

column[1] CNT SIZE

-----------------------------------------

Gemeinsam (ohne triviale): 7321 50771471

number of record(s) selected: 1

FNAME SIZE

-----------------------------------

libgeos.a 46218614

_x1l.cfs 42879522

icon-theme.cache 49300108

rt.jar 39999770

libwireshark.so.0.0.1 31259160

libgcj.so.8.0.0 43762136

number of record(s) selected: 6

INODE CNT HARDLINK_COUNT

--------------------------------

15521 21 21

15852 22 22

15527 22 22

15998 23 23

16334 24 24

16288 29 29

15982 37 37

16326 54 54

16201 61 61

16065 63 63

16071 64 64

16209 69 69

16194 69 69

16188 69 69

16203 71 71

16289 87 87

16335 104 104

16329 106 106

number of record(s) selected: 18

number of record(s) selected: 4986

MD5 INODE_CNT MIN_CNT MAX_CNT FILESIZE

---------------------------------------------------------------------------

9ee5254cc9152fb944edc9692a658c6e 31 1 1 39

aed1d9f022f7989f89df34e9bb303895 32 1 1 41

d1950f8e7864a725c048bde326e1e1f8 32 1 1 360

ae2ae301fa9705daf4437f8b17758a07 32 1 1 52

ebd7e6d206d73850d77d305ab548f957 32 1 3 472

f6192f4292d744423df690e785d60fdf 32 1 3 654

f771308152ab24a69682daed70ce136d 32 1 3 459

2f07d9272deb4ae870ca6821117877fa 32 1 3 406

460d62cd215de207abd09fc6c31fbc82 32 1 1 44

59530bdf33659b29e73d4adb9f9f6552 35 1 1 18002

5328424d6163a91273450aaee76e0135 54 1 1 2100

87f199af11331495f1b7b1b9c538e797 63 1 1 87

94d55d512a9ba36caa9b7df079bae19f 286 1 2 17992

number of record(s) selected: 13

Achten Sie bitte auf die letzte Zeile, eine Datei gibt es tatsächlich 286 mal (redundant) kopiert und einmal wird der Linux Hardlink-Mechanismus verwendet!

[Seitenanfang]

kontakt

impressum

nutzungsbedingungen

datenschutz

lizenz