Anfängerfrage; Daten analysieren

[Peter_Frankfurt]

Hallo zusammen,
ich probiere mich gerade in mysql aus und würde gerne eine große Datenmenge analysieren. (ca35.000 Einträge)

Leider liegen mir die Rohdaten nur als txt Datei, und in der folgenden Struktur vor:

0104441Name1
010450020200101
..
..(bis zu 30 Dateninfos)
..
0104441 Name2
0104600TEXT
..
..
...
Meine Frage: Wie bekomme ich die Daten am besten in mysql? Oder macht es Sinn, die Daten erst in einer Tabellenkalkulation aufzubereiten um sie Daten dann in mysql zu importieren?

VG Peter

 

[ukulele]

SQL-Server arbeiten sehr strukturiert mit Daten, immer ausgehend von einem normalisierten Datenbankmodell. Das was du hast ist genau das Gegenteil, einfach nur Informationen in einer Textdatei, mehrere Zeilen bilden einen Datensatz. Du wirst das erst aufbereiten und in eine normalisierte Form bringen müssen um damit vernünftig arbeiten zu können. Wie du das tust ist eigentlich egal aber mit SQL ist das schon sehr schwierig, besser erstmal aus mehreren Zeilen vernünftige Datensätze bilden bevor du das importierst.

 

[akretschmer]

aber mit SQL ist das schon sehr schwierig,

Depends. Ich hatte das ja schon gezeigt, fiel aber dem Server-Crash zum Opfer, daher noch einmal:

Das hier steht in /tmp/data.txt:

Max
Mustermann
10
Susi
Sorglos
20
Paul
Muster
66

SQL-Seite:

test=*# create table import(t text);
CREATE TABLE
test=*# copy import from '/tmp/data.txt' ;
COPY 9
test=*# with x as (select *, (row_number() over() - 1) % 3 as col, (row_number() over() - 1) / 3  as row from import) select string_agg(t,'') filter (where col = 0) as vname, string_agg(t,'') filter (where col = 1) as nname, string_agg(t,'') filter (where col = 2) as zahl  from x group by row;
 vname |   nname    | zahl
-------+------------+------
 Max   | Mustermann | 10
 Paul  | Muster     | 66
 Susi  | Sorglos    | 20
(3 rows)

test=*#

 

[ukulele]

Man kann tolle Sachen mit SQL machen, keine Frage. Aber das ist auf jedenfall sehr mühselig. Wir kennen überhaupt keine Rahmenbedingungen, z.B. ob die Anzahl der Zeilen zu einem Datensatz fix ist. "bis zu" deutet eher nicht darauf hin.

 

[dabadepdu]

Man kann tolle Sachen mit SQL machen, keine Frage. Aber das ist auf jedenfall sehr mühselig.

Ansichtssache, ich mache statt ETL lieber LTE. Es gibt für mich nichts besseres als Datentransformation mit SQL. Also rein mit dem Zeug, notfalls eine ganze Zeile als Spalte und Rosinenpicken dann in SQL.
Mühselig wäre es, wenn ich es in Excel oder handschriftlich machen müsste. Solange die DB die Arbeit macht und sich nicht weigert, ist es mir egal.
Ach und apropos weigert, um diesen Teil der Frage zu beantworten, in mySQL würde ich es nicht machen.

 

[Peter_Frankfurt]

Vielen Dank für die Infos!
Ich bin ein wenig erschlagen von den Antworten.....
Aber der Reihe nach:
Ich nutze mysql, weil ich mir das Buch Einstieg in SQL von Michael Laube gekauft habe. Ich habe das Buch nun zu 1/3 durch gelesen und weiß nun nach euren Ausführungen, dass ich nichts über SQL weiß. :-(
Ich habe den Code mal in Workbench kopiert und meine Daten von einem Datensatz auf C: test/data.txt abgelegt, aber leider nur eine Fehlermeldung erhalten.

Meine Quelldaten sehen wie folgt aus:

0459409Audi
0488802V6
048020820499884
04894490E5454242445888474
0254407M+M
048740924 C
04484429996
0498444X
0488448Frankfurt
049844685
04544089644545445S
0464440Combi
044540442049
04 8460409684
048440999
047446920490604
044848820444204
0844444084244879
0224484Mannheim
044658444
074448244
06044249
084442208
024428980
047500020200948
094500482006
0275098060478800
0805099454654504
049500408004
0425888060478700
0489699047454504
08850844920
0484498025478780
0484899448654600
048400944
04990984
048967852
04846842
08880409904
08880448244
0499006559555
07440402020424565454545
040804484

Es befinden sich also immer 7 Ziffern und dann die Information im Anschluss. Eigentlich stelle ich mir vor, dass ich den Spaltenname immer nach den ersten 7 Ziffern erstelle und dann mit AS umbenenne /*Aber noch fehlt mir jede Menge "Know-How" */

test=*# create table import(t text);
CREATE TABLE
test=*# copy import from 'C:\Test/data.txt' ;
COPY 9
test=*# with x as (select *, (row_number() over() - 1) % 3 as col, (row_number() over() - 1) / 3 as row from import) select string_agg(t,'') filter (where col = 0) as vname, string_agg(t,'') filter (where col = 1) as nname, string_agg(t,'') filter (where col = 2) as zahl from x group by row;

Error Code 1064

Viele Grüße Peter

 

[akretschmer]

Vielen Dank für die Infos!
Ich bin ein wenig erschlagen von den Antworten.....
Aber der Reihe nach:
Ich nutze mysql, weil ich mir das Buch Einstieg in SQL von Michael Laube gekauft habe. Ich habe das Buch nun zu 1/3 durch gelesen und weiß nun nach euren Ausführungen, dass ich nichts über SQL weiß. :-(

SQL mit MySQL lernen zu wollen ist keine gute Idee. Ist etwa so wie Fahrschule machen mit einem BobbyCar. Das hat 4 Räder wie ein PKW, immerhin...

Ich habe den Code mal in Workbench kopiert und meine Daten von einem Datensatz auf C: test/data.txt abgelegt,

Du hast das jetzt nicht als ganzen Block kopiert, oder? Dir ist das Konzept einer interaktiven Eingabe noch nie vorgekommen?

Die DB sagt:

test=*#

und signalisiert damit, Eingaben entgegen zu nehmen.
Ich tippe:

create table import(t text);

Die verarbeitet die DB und sagt:

CREATE TABLE

Damit bestätigt siemir die Ausführung. Dann sagt sie wieder:

test=*#

und wartet auf meine nächste Eingabe.

Du erkennst das Prinzip?

aber leider nur eine Fehlermeldung erhalten.

Interessant. Interessanter wäre die Fehlermeldung selbst.

Meine Quelldaten sehen wie folgt aus:

0459409Audi
0488802V6
048020820499884
04894490E5454242445888474
0254407M+M
048740924 C
04484429996
0498444X
0488448Frankfurt
049844685
04544089644545445S
0464440Combi
044540442049
04 8460409684
048440999
047446920490604
044848820444204
0844444084244879
0224484Mannheim
044658444
074448244
06044249
084442208
024428980
047500020200948
094500482006
0275098060478800
0805099454654504
049500408004
0425888060478700
0489699047454504
08850844920
0484498025478780
0484899448654600
048400944
04990984
048967852
04846842
08880409904
08880448244
0499006559555
07440402020424565454545
040804484

Es befinden sich also immer 7 Ziffern und dann die Information im Anschluss. Eigentlich stelle ich mir vor, dass ich den Spaltenname immer nach den ersten 7 Ziffern erstelle und dann mit AS umbenenne /*Aber noch fehlt mir jede Menge "Know-How" */

ich sehe u.a Audi, V6 (Motor?), Frankfur, Mannheim. Jede Menge Ziffern, oft mehr als 7. Ist da nebenbei eine Katze über den Nummernblock gelaufen? Sind die 7 führenden Ziffern nach einem festen Schema? Bei Frankfurt ist es 0488448, bei Mannheim dann 0224484. Sind es je Datensatz immer gleich viele Zeilen?

Sorry, aber das kann hier keiner wissen... und ohne zu wissen, wie das zu interpretieren ist, kann es keiner interpretieren.

 

[Peter_Frankfurt]

Hallo akretscher,
das ist ein so zu sagen "1 Datensatz" , und der nächste fängt wieder mit der 0459409 an und es folgt dann ein anderes Fahrzeug, das allerdings auch noch andere bzw. noch weitere Zeilen haben kann. Identisch sind aber immer die ersten 7 Ziffern.

Was für ein Buch könnt ihr mir denn empfehlen? Oder wie habt Ihr SQL gelernt? Ist PostgreSQL für Einsteiger das richtige Programm?

VG Peter

 

[akretschmer]

Hallo akretscher,
das ist ein so zu sagen "1 Datensatz" , und der nächste fängt wieder mit der 0459409 an und es folgt dann ein anderes Fahrzeug, das allerdings auch noch andere bzw. noch weitere Zeilen haben kann. Identisch sind aber immer die ersten 7 Ziffern.

Okay, damit kann man das auch auflösen, etwas kniffliger, aber machbar.

Was für ein Buch könnt ihr mir denn empfehlen? Oder wie habt Ihr SQL gelernt? Ist PostgreSQL für Einsteiger das richtige Programm?

VG Peter

Kann ich aus dem Handgelenk jetzt nichts empfehlen. Es gibt ein Buch von Andreas Scherbaum, da steht mein Name in der Danksagung - aber das ist schon 11 Jahre alt...
Aber PostgreSQL ist eine wirklich gute Datenbank unter der besten OpenSource-Lizenz der Welt. Unsere Kunden (z.B. ein deutscher Autobauer mit 300.000 Mitarbeitern weltweit) würden es sonst nicht nutzen...

 

[dabadepdu]

Nachdem die Struktur nun realativ klar scheint:

Ich kann Dir nur Postgres empfehlen, gerade wenn es um derartige Datenstrukuren und Transformationen geht, aber auch für die weitere Analyse, die Du machen willst.
Trau Dich, installiere es und es kann losgehen. Was Du als erstes brauchst, ist eine vollständige Liste aller 7 Ziffern, die auftauchen können, am besten mit einem Namen dazu (fürs Auge und alles weitere), sonst hast Du halt Nummern als Spaltennamen (und vielleicht Funktionslücken, aber nein, man kann die Nummernliste auch aus dem Bestand auslesen). Das kann man dann zum Transformieren in eine klassische Tabelle nehmen. Danach kann man nach Herzenslust mit Standard SQL operieren. Das kannst Du wunderbar mit Postgres lernen.

Bücher:
Ich habe noch einen Stapel, den ich gerade bei Ebay Kleinanzeigen hatte. Will keiner mehr, vielleicht ist was dabei.

Aber ehrlich:
Das Netz ist voller Tutorials, auch Grundlagen.

 

[Peter_Frankfurt]

Vielen Dank! Ich werde es installieren und mal sehen wie weit ich komme ;-)

VG Peter

 

[akretschmer]

mal so als Spielerei:

test=*# select * from peter ;
      t       
--------------
 123Dresden
 234Elbe
 345Zwinger
 123Meißen
 234Elbe
 123Berlin
 234Spree
 789Regierung
(8 rows)

test=*# select * from mapping ;
 id  |       name       
-----+------------------
 123 | Stadt
 234 | Fluß
 345 | Sehenswürdigkeit
 789 | Sitz
(4 rows)

test=*# with x as (select *, case when substring(t,1,3) = '123' then true else null end as new_record from peter order by ctid), y as (select *, sum(case when new_record then 1 else 0 end) over(rows between unbounded preceding  and current row) as record from x) select y.record, m.name, substring(y.t,4,20) as wert from y left join mapping m on substring(t,1,3)=m.id order by record, t;
 record |       name       |   wert   
--------+------------------+-----------
      1 | Stadt            | Dresden
      1 | Fluß             | Elbe
      1 | Sehenswürdigkeit | Zwinger
      2 | Stadt            | Meißen
      2 | Fluß             | Elbe
      3 | Stadt            | Berlin
      3 | Fluß             | Spree
      3 | Sitz             | Regierung
(8 rows)

test=*#

Hier halt mit nur 3 Stellen. In der Tabelle mapping trägst halt ein, welcher Zahlencode was zu bedeuten hat. Damit bleibt die Abfrage konstant, auch wenn neue 7-stellige codes dazukommen. Man könnte daraus jetzt auch recht leicht eine JSONB-Datenstruktur bauen, je nachdem, was weiter passieren soll.

 

[dabadepdu]

Ich werde es installieren und mal sehen wie weit ich komme ;-)

Prima, nach der Installation am besten eine eigene DB anlegen plus eigner user mit Rechten (grant all privileges)
Dann darin eine Tabelle für Deinen Import, einfach eine Textspalte
Dann laden mit copy in die neue Tabelle
..
dann kannst Du Dir das Beispiel von akretschmer ansehen

 

[Dukel]

Wenn man das nicht andersrum haben möchte? Das was bei dir Name ist sollte die Spalte sein.

 

[akretschmer]

Wenn man das nicht andersrum haben möchte? Das was bei dir Name ist sollte die Spalte sein.

Ich weiß, was Du meinst. Dafür hatte ich in #3 eine Lösung gezeigt. Diese geht aber davon aus, daß es eine feste Anzahl von Spalten gibt - dies ist nicht der Fall bei den Daten. Man könnte das nötige SQL dynamisch erstellen, aber so eine Lösung mit EAV (wie es jetzt ist) oder als JSONB (oder auch als HSTORE) hat auch seine Vorteile...