Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Datenauswertung von tonnenweise ASCII Text mittels Datenbank?

Dieses Thema im Forum "Datenmodellierung, Datenbank-Design" wurde erstellt von mzurhorst, 31 Dezember 2019.

  1. mzurhorst

    mzurhorst Benutzer

    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
     
  2. akretschmer

    akretschmer Datenbank-Guru

    klingt machbar. Offenbar ist Dein Code da je Tabelle UNIQUE, nutze ihn gleich als PRIMARY KEY.
     
  3. mzurhorst

    mzurhorst Benutzer

    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?
     
  4. akretschmer

    akretschmer Datenbank-Guru

    hängt von ganz vielen Faktoren ab ...

    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.

    siehe oben.


    Andreas
     
  5. mzurhorst

    mzurhorst Benutzer

    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.
     
  6. mzurhorst

    mzurhorst Benutzer

    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?
     
  7. akretschmer

    akretschmer Datenbank-Guru

    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.
     
  8. mzurhorst

    mzurhorst Benutzer

    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.
     
  9. castorp

    castorp Datenbank-Guru

    HeidiSQL funktioniert doch auch mit Postgres

    Du solltest Dir auch diese dämlichen Anführungszeichen abgewöhnen.
    Don't Do This - PostgreSQL wiki
     
    Zuletzt bearbeitet: 9 Januar 2020
  10. mzurhorst

    mzurhorst Benutzer

    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.
     
  11. castorp

    castorp Datenbank-Guru

    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 ;)
     
  12. akretschmer

    akretschmer Datenbank-Guru

    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?
     
  13. mzurhorst

    mzurhorst Benutzer

    Klar, kann ich machen. Einfach hier ins Forum rein kopieren, oder in welchem Format?
     
  14. mzurhorst

    mzurhorst Benutzer

    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.
     
  15. mzurhorst

    mzurhorst Benutzer

    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: 10 Januar 2020

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden