Datenauswertung von tonnenweise ASCII Text mittels Datenbank?

M

mzurhorst

Benutzer
Beiträge
10
Hallo zusammen.

Ich bin neu hier und bräuchte mal ein paar Ideen.
Und zwar habe ich hier eine Aufgabe vor mir, die ich vermutlich nur effizient mit einer Datenbank lösen kann. Allerdings möchte ich hier nicht mit dem Kopf durch die Wand, ohne dass ich vorher mal drüber gesprochen habe :)

Also, ich habe tatsächlich tonnenweise ASCII Files. Und zwar zwei in etwa gleich große Haufen in der Größenordnung von jeweils mehreren Gigabyte. Haufen 1 kenne ich bereits aus einer vorherigen Auswertung, das sind ca. 75-80 Millionen Zeilen Text. Haufen 2 wird ähnlich groß sein, diesen erhalte ich Anfang Januar.

Inhalt der Dateien aus Haufen 1:
  • Kosolenausgabe von du auf einem Enterprise Storage
  • Die Zeilen beinhalten Dateigröße in kB und die Pfadangabe zur Datei.
  • Der Dateiname einzigartig über einen unique alphanumerischen String. (z.B. something_a1b2c3d4e5f6g7.pdf)
  • Für mich interessant ist hier nur die Dateigröße (Integer) und dieser alphanum. String.
  • Für Haufen 1 habe ich bereits im Sommer einen Python Parser geschrieben, der mir diese Sachen in eine MariaDB importiert. Dieser läuft etliche Stunden und befüllt eine Tabelle der Datenbank.
Inhalt der Dateien von Haufen 2:
  • Das ist ein Report aus einer Enterprise PLM Applikation. (Teamcenter).
  • Die Zeilen beinhalten:
    • interner Datenbank Identifier
    • externer Identifier (das ist der alphanum. String von oben!)
    • Information ob diese Datei noch referenziert wird aus der Datenbank oder nicht.

Warum das Ganze?
Ich möchte nun im Grunde die Haufen übereinander legen und wissen, wie viel Speicherplatz auf dem Storage alloziert wird durch Dateien, welche nicht mehr in der Datenbank referenziert sind.



Ich denke es macht wenig Sinn, wenn ich mir nun im zweiten Haufen Zeile 1 anschaue und diese dann mit 75 Millionen Zeilen aus Haufen 1 vergleiche und bei einem Treffer aufsummiere.
Sprich, nur in Python, komplett ohne Datenbank.

Also würde ich nun die beiden Datenberge jeweils parsen und in zwei Tabellen rein schreiben, welche ich dann hoffentlich hinterher "einfach" joinen kann über diesen alphanum. Code.

Gibt es hier etwas zu beachten, damit das ganze innerhalb von einer vertretbaren Zeit (z.B. Wochenende) durch läuft? Oder werde ich hier zwangsläufig in größere Performance-Probleme rein laufen?
Sollte ich die Daten irgendwie sortieren in den Tabellen?

Ich habe einen i7 Workstation-Laptop mit 16 GB RAM und SSD. Falls das aussichtslos ist, könnte ich das evt. auch auf anderer Hardware laufen lassen, würde es aber gerne erst mal lokal probieren.

#
Anregungen sind herzlichen Willkommen.
Viele Grüße und einen guten Rutsch,
Marcus


Disclaimer: Ich bin kein ausgebildeter ITler
 
Werbung:
Danke.
Hast du eine Idee, mit welcher Laufzeit ich rechnen müsste? - Muss ich außer dem PRIMARY KEY noch etwas beachten?

Für meinen Python-Parser hatte ich damals ursprünglich eine Laufzeit von ~26 Stunden, um 71M Zeilen nach MariaDB zu importieren.
Das war sehr langsam. Ich habe dann das Script umgebaut, so dass ich ~200-300 INSERTs gemacht habe, und dann erst ein COMMIT. Hatte mit der Zahl der Inserts gespielt, bis sich irgendwann keine weitere Beschleunigung mehr einstellt hatte. Aber immerhin Faktor 7-8 war durch diese Änderung noch drin.

Ich hatte damals gelesen, dass die Datenbank wohl irgendwie prüft, ob diese Zeile nicht schon vorhanden sei.
Lässt sich so etwas komplett unterbinden für Performance-Verbesserungen?
 
mzurhorst schrieb:
Danke.
Hast du eine Idee, mit welcher Laufzeit ich rechnen müsste?
Zum Vergrößern anklicken....

hängt von ganz vielen Faktoren ab ...

- Muss ich außer dem PRIMARY KEY noch etwas beachten?

Für meinen Python-Parser hatte ich damals ursprünglich eine Laufzeit von ~26 Stunden, um 71M Zeilen nach MariaDB zu importieren.
Das war sehr langsam. Ich habe dann das Script umgebaut, so dass ich ~200-300 INSERTs gemacht habe, und dann erst ein COMMIT. Hatte mit der Zahl der Inserts gespielt, bis sich irgendwann
Zum Vergrößern anklicken....

Ich würde PG nehmen, und dann mit COPY arbeiten. Wenn ein Ausfall während des Importes verkraftbar wäre (klingt so in Deinem Fall) fsync ausschalten. Constraints/Indexe erst danach erstellen.

keine weitere Beschleunigung mehr einstellt hatte. Aber immerhin Faktor 7-8 war durch diese Änderung noch drin.

Ich hatte damals gelesen, dass die Datenbank wohl irgendwie prüft, ob diese Zeile nicht schon vorhanden sei.
Lässt sich so etwas komplett unterbinden für Performance-Verbesserungen?
Zum Vergrößern anklicken....

siehe oben.


Andreas
 
Das heißt, mein Parser sollte nicht direkt in die Datenbank committen, sondern erst mal die Inputs in einem Output File so ablegen, dass sie möglichst effizient in die Datenbank rein importiert werden können.
PG kenne ich nicht. Vermutlich ist die Syntax für so rudimentäre Sachen aber sehr ähnlich zwischen den Datenbanken, hmm?

Danke, schaue ich mir alles bis zum Wochenende an. Werde mal was basteln und versuche dass alles zu berücksichtigen.
 
Ok, ich habe es nun etwas anders gemacht mangels besserer Skills:
1) Bin bei MySQL geblieben, da ich dazu schon installiert habe und Skript 1 ja quasi fertig war.
2) Haufen 1 wurde importiert binnen 6h. Allerdings hatte ich eine sehr miese Performance, wenn ich die UID als Primary Key verwendet habe. die Datenbank wird vermutlich die Eindeutigkeit nochmal prüfen, oder? Darüber hinaus habe ich tatsächlich irgendwann mal Duplikate bei der UID gehabt. Das muss ich nun erst mal genauer erforschen, wie das sein kann. Erwartet hätte ich das nicht.
3) Das Haufen 2 Sample ist auch importiert in eine zweite Tabelle. Das sind nur 100.000 Zeilen etwa.


Aber wenn ich nun eine einfache Stichprobe mache, dann sucht das bereits knapp anderthalb Minuten:
Code: 
SELECT FILESIZE FROM cpd_storage2 WHERE UID = "k270qhl6rdgmx";
/* Betroffene Zeilen: 0  Gefundene Zeilen: 1  Warnungen: 0  Dauer von 1 Abfrage: 00:01:25.3 */

Ist das normal? -- Wie soll das dann erst werden, wenn ich da Millionen Daten abgleiche aus den beiden Tabellen?
 
erscheint mit etwas lahm. Ich hab mal mit PG eine Tabelle erstellt und mit 500.000 Rows befüllt:

Code: 
test=# create table mzurhost(uid uuid primary key, val text);
CREATE TABLE
Time: 24,282 ms
test=*# insert into mzurhost select uuid_generate_v4(), repeat(md5(s::text),3) from generate_series(1,500000) s;
INSERT 0 500000
Time: 6300,490 ms (00:06,300)

Nun suche ich nach einem Wert:

Code: 
test=*# explain analyse select * from mzurhost where uid = 'a26fcc4b-3bfa-4037-b468-fd889ed144ef';
                                                       QUERY PLAN                                                       
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using mzurhost_pkey on mzurhost  (cost=0.42..8.44 rows=1 width=48) (actual time=0.015..0.015 rows=0 loops=1)
   Index Cond: (uid = 'a26fcc4b-3bfa-4037-b468-fd889ed144ef'::uuid)
 Planning Time: 0.216 ms
 Execution Time: 0.040 ms
(4 rows)

Time: 5,112 ms
test=*#


Also, 5 Millisekunden. So in etwa sollte das gehen.
 
Ok. Ich bin auf PostgresSQL umgestiegen.
Allerdings bin ich gerade als Laie geschockt über die Komplexität von "pgAdmin4". Das "HeidiSQL" Frontend war erheblich intuitiver zu bedienen.

Anyways, ich kämpfe gerade mit einem JOIN statement und bin mir unsicher, ob das zurück gelieferte Ergebnis korrekt ist:

Ich habe drei Tabellen:
  1. cpd_findings
  2. cpd_dbreport
  3. cpd_storage

Tabelle 1 (findings) hat zwei Spalten:
  • Category (Integer)
  • Description (Text)

Tabelle 2 (dbreport) hat zwei Spalten (13.5 Millionen Zeilen)
  • UID (Text)
  • Category (Integer)
Tabelle 3 (storage) hat zwei (relevante) Spalten: (71 Millionen Zeilen)
  • Filesize (Integer)
  • UID (Text)

Was ich nun gerne möchte:
  • Zwei Spalten (Description, Sum(Filesize))
  • Die UID aus der Tabelle 2 soll nur 1x gezählt werden

Ich habe sowohl in der Tabelle 1 als auch in Tabelle 2 bei UID möglicherweise doppelte Einträge. Ich möchte es vermeiden, dass ich hier etwas doppelt abfrage.

Code: 
SELECT "Category"."Category" AS "Category", count(distinct "public"."cpd_dbreport"."UID") AS "count"
FROM "public"."cpd_dbreport"
LEFT JOIN "public"."cpd_findingcategories" "Category" ON "public"."cpd_dbreport"."Category" = "Category"."Category" LEFT JOIN "public"."cpd_storage" "Cpd Storage" ON "public"."cpd_dbreport"."UID" = "Cpd Storage"."UID"
GROUP BY "Category"."Category"
ORDER BY "Category"."Category" ASC

Ich bekomme nun die Zeilen gezählt, aber er zeigt mir die Integer-Zahl für den Error an, und nicht die Beschreibung.
 
Ah, das mit Heidi wusste ich nicht.
Diese Query habe ich nicht selbst gemacht, da ich das nicht hinbekommen habe. Die wurde mit Metabase zusammengeklickt.

Ich habe tatsächlich enorme Schwierigkeiten, unmittelbar in pgAdmin4 eine Query zu schreiben.
Select * from Tabelle Where Spalte = 3 ---> wird dauernd angemeckert. (3 ist ein Integer). Habe es mit ' und " versucht, und ohne.
Habe es probiert mit Tabelle.Spalte.

Nur wenn ich "public"."tabelle"."spalte" schreibe, dann klappt es.
 
Du hast die Tabellen mit den doppelten Anführungszeichen angelegt, und damit sind sie jetzt case-sensitiv - "Tabelle" ist ein anderer Name als "tabelle" - am besten schon beim Anlegen der Tabellen darauf verzichten. Wenn Du SQL schreibst, vergiss einfach, dass es die Taste mit dem " gibt ;)
 
Naja, ich nutze PGAdmin auch nicht. Wenn es bunt sein muß, probiere mal OmniDB. Ansonsten empfehle ich gern schlicht und ein psql, also den Text-Client.

Code: 
andreas@[local]:5434/test# create table findings(category int, description text);
CREATE TABLE
andreas@[local]:5434/test# create table dbreport(uid text, category int);
CREATE TABLE
andreas@[local]:5434/test# create table storage(filesize int, uid text);
CREATE TABLE

Kannst Du je Tabelle mal ein paar testdatensätze zeigen, und was Du daraus ermitteln willst?
 
Tabelle cpd_storage
Nur zwei Spalten sind relevant: UID und die zugehörige Dateigröße.
upload_2020-1-10_9-31-12.png

Tabelle cpd_findingcategories
upload_2020-1-10_9-32-58.png


Tabelle cpd_dbreport
upload_2020-1-10_9-34-59.png

Was ich schon fertig habe:
upload_2020-1-10_9-40-26.png
Code: 
SELECT count(distinct "public"."cpd_dbreport"."UID") AS "count"
FROM "public"."cpd_dbreport"
GROUP BY "public"."cpd_dbreport"."Category"

Was ich aber gerne hätte:
upload_2020-1-10_9-41-38.png


Die Formatierung der Zahlen spielt keine Rolle. Mir ist es nur wichtig, dass ich die Zahlen korrekt zähle bzw. aufsummiere.
Es kann in den beiden Listen theoretisch Duplikate bei der UID geben (technisch begründet, weil ein File auf zwei Volumes abgelegt sein kann).

Vielen Dank.
 
Werbung:
Ich hatte mir das so vorgestellt :D
Code: 
SELECT
   "public"."cpd_findingcategories"."Description" AS "Defect",
   COUNT(DISTINCT("public"."cpd_dbreport"."UID")) AS "Quantity",
   SUM("public"."cpd_storage"."FILESIZE") AS "Disc_Space"
FROM "public"."cpd_dbreport"
   LEFT JOIN "public"."cpd_storage" ON "public"."cpd_dbreport"."UID" = "public"."cpd_storage"."UID"
   LEFT JOIN "public"."cpd_findingcategories" ON "public"."cpd_dbreport"."Category" = "public"."cpd_findingcategories"."Category"
GROUP BY "public"."cpd_dbreport"."Category"
ORDER BY "public"."cpd_findingcategories"."Category" ASC;

Das klappt aber nicht leider.


Frage: Was muss ich tun, um diese Anführungszeichen los zu werden? - Lässt sich das noch nachträglich reparieren?



Edit:
So klappt es. Er kann nicht sortieren nach Sachen, die nicht selektiert sind. Das war mir nicht klar.
Code: 
SELECT
   "public"."cpd_findingcategories"."Description" AS "Defect",
   COUNT(DISTINCT("public"."cpd_dbreport"."UID")) AS "Quantity",
   SUM("public"."cpd_storage"."FILESIZE") AS "Disc_Space"
FROM "public"."cpd_dbreport"
   LEFT JOIN "public"."cpd_storage" ON "public"."cpd_dbreport"."UID" = "public"."cpd_storage"."UID"
   LEFT JOIN "public"."cpd_findingcategories" ON "public"."cpd_dbreport"."Category" = "public"."cpd_findingcategories"."Category"
GROUP BY "public"."cpd_findingcategories"."Description"
ORDER BY "public"."cpd_findingcategories"."Description" ASC;
upload_2020-1-10_12-35-39.png

Runtime: 3min 42sec
 
Zuletzt bearbeitet:
Zurück
Oben