Daten aus Datenbanken, SQL, JSON, XML und Flat Files extrahieren und umwandeln

(Last Updated On: 13. Februar 2016)

Als Systemadministrator oder Entwickler kommen Sie irgendwann an die Herausforderung, mit Daten umzugehen, die in verschiedenen Formaten vorliegen. Teilweise als Flat File, also beispielsweise als .csv-Datei, die über Microsoft Excel gepflegt wurde, teilweise als .xml-Datei, und teilweise müssen Sie Daten aus einer datenbank extrahieren, um sie in eine andere Datenbank zu laden oder in eine .csv- bzw. xml-Datei zu exportieren. Und einige Datenbanken bieten die Funktion zum Exportieren der Daten in eine Datei mit SQL Statements, aus der Quelltabelle in einer neuen Datenbank wieder aufgebaut werden kann.

In diesem Beitrag, der regelmäßig aktualisiert wird, zeige ich Ihnen, wie Sie daten von verschiedenen Datenbankmanagementsystemen in SQL Statements Files, Flat Files oder .xml-Files exportieren und umgekehrt aus diesen Dateien auch wieder in die Datnebank importieren. Desweiteren zeige ich IHnen, wie Sie .xml-Files in Flat Files und umgekehrt Flat Files in .xml-Files konvertieren.

Das extrahieren von Daten aus einer Quelle, das Umwandeln in ein Zwischenformat und das Laden der Daten in ein Ziel wird auch als Extract, Transform and Load (ETL) bezeichnet.

Datenbanken

In diesem Abschnitt befassen wir uns damit, wie wir Daten aus einzelnen Tabellen einer Datenbank exportieren können. Ich zeige Ihnen hier, wie Sie die Daten aus einer oder mehreren Tabellen der Datenbank in ein Flat File und in SQL-Statement-File bekommen und wie Sie die Tabelle in einer anderen Datenbank aus dem SQL Statement File wieder aufbauen können. Desweiteren zeige ich Ihenn, wie Sie eine bstehende Tabelle aus einem beliebiegn Flat File  befüllen und aufbauen.

SAP HANA

Daten von der Datenbank in ein Flat File oder SQL Statement File exportieren

die einfachste Möglihckeit, DAten aus einer SAP HANA datenbank zu exportieren, ist es,  die gewünschte Tabelle im HANA Studio unter Catalog -> <Schema Name> – Tables auszuwählen und damm im kontextmenü auf Export zu gehen.

2016-01-23_23h47_44

 

Im nächstne FEnster können Sie mehere Tabellen hinzufügen. Haben Sie alle gewünschten objekte selektiert, wählen Sie die Schaltfläche NExt.

2016-01-23_23h50_22

Alternativ können Sie auch bei TAbles im kontextmenü Export wählen und dort dann die Tabelle suchen und hinzufügen

2016-01-23_23h57_34

2016-01-23_23h58_33

Die Daten können Sie nun entweder als BINARY oder als CSV-DAtei exportieren. Egal was Sie hier wählen, Sie exportieren auf diesem Weg nur die MEtadaten über die Tabelle. Das heißt Sie bekommen eine sogenannte create.sql, mit deren Hilfe Sie die Tabelle in einer anderen Datenabnke wieder erstellen können (jedoch ohne daten darin) sowe eine .xml-Datei, welche die Metadaten nochmal extra ausschildert.

Wenn Sie die Daten einer Tabelle (und nicht nur die TAbelle selbst) exportieren wollen, müssen Sie folgendermaßen vorgehen. Suchen Sie die Tabelle, indem Sie im kontextmenü Find Tables wählen und dann den NAmen der tabelle iengeben. Wenn Sie die tabelle dann gefunden haben, setzen Sie einen Haken bei Show Content und Show definition, um sowohol den Inahtl als auch die Definition der tabelle anzuzeigen

2016-01-23_23h55_17

Nun wird Ihnen der inhalt der Tabelle angezeigt. Markieren Sie alle Zeilen, die Sie exportieren wollen, meistens also alle. Ihre erste Möglihckeit ist nun im Kontextmenü zu wählen Export Result. Wenn Sie das tun, erhalten Sie ein Flat File, welches mit Semika getrennt ist. Dieses Ausgabeformat ist super, wenn Sie die Daten anderweitig laden und analysieren wollen, beispielsweise in einer Excel Datei o. Ä.

2016-01-24_00h04_39

Ihre nächste Möglichekti ist im kontextmenü Insert Statement auszuwählen. Sie bekommen einen SQL Editor mit den Insert into Statements eines jeden Datensatzes angezeigt. Diese Insert into-Statements können Sie sich nun rauskopieren und in einer .sql-Datei, beispielsweise data.sql,  abspeichern.

Wenn Sie nun, wie im ersten Schritt beschrieben, die create.sql-Datei der Tabelle exportiert haben, können Sie die Tabelle erst mit Hilfe der create.sql bauen und dann die Daten mithilfe der data.sql, die wir jetzt gerade exportiert haben, wieder befüllen.

Flat File in eine Tabelle laden

Zwar können Sie unter SAP HANA auch über das HANA Studio .csv-Dateien in eine Tabelle laden, das ist jedoch ab einer bestimmten Größe des Flat Files nicht mehr zumutbar, da der importporzess hier sehr langsam ist. Stattdessen empfiehlt es sich, ein Flat File ganz normal mit Hilfe einre sogenannten Kontrolldatei zu importieren.

Erstellen Sie eine Datei namens data.ctl. Wir gehen davon aus, dass ihr Flat File data.csv heißt, die einzlenen Spaltenwerte / ATtribute mit KOmmas und die Datensätze durch Zeilenumbrüche getrennt sind. Dann schrieben Sie also in die data.ctl folgenden Inhalt:

IMPORT FROM CSV FILE '/data/data.csv' INTO "MYTABLE"
WITH
RECORD DELIMITED BY '\n'
FIELD DELIMITED BY ','
DATE FORMAT 'MM/DD/YYYY'
BATCH 1000
FAIL ON INVALID DATA
ERROR LOG /data/error.log;

Damit das später funktioniert, müssen Sie sicherstellen, dass der Betriebssystembenutzer <dbsid>adm Leserechte sowohl auf die data.ctl als auch auf die data.csv hat. MYTABLE ist der Name der Tabelle, in die später die Daten geladen werdne sollen. Diese Tabelle muss vor dem Importvorgang bereits existieren. Sie können die Tabelle dazu entweder vorher über ein CREATE TABLE-SQL-Statement erstellen oder alternativ eine kleine Version der .csv-Datei (mit biespielsweise den ersten 20 Datensätzen) über das HANA Studio laden und dadurch die Tabelle mit diesen 20 Datensätzen erstellen lassen. Genauere Einstellungen wie Primärschlüssel und Indizes können Sie dann über ALTER TABLE oder über das HANA STudio einstellen.DATE FORMAT legt fest, in wlechem Format Datumswerte im Flat File abgelegt sind, falls vorhanden.  BATCH 1000 legt fest, dass die Datensätze in der .csv-Datei in Blöcken von 1000 Datensätzen abgearbeitet werden. FAIL ON INVALID DATA stellt sicher, dass der Importvorgang abgebrochen (und nicht fortgesetzt) wird, wenn es Probleme gibt. Dann geben wir noch eine Datei an, in der Fehlermeldungen beim importvorgang geloggt werdne sollen. Die Datie muss vorher icht existieren, der Betriebssystembenutzer <dbsid>adm muss aber Schreibrechte auf dem Verzeichnis haben, in welchem die Datei erstellt werden soll. Die erzeugte error.log-Datei ist standardmäßig read only, das heißt wenn man den Importvorgang nach einem Fehler ein zweites mal ausführt, wir ddie Datei nicht aktualisiert. Daher vorher die .log-Datei löschen oder auf overwriteable stellen.

Bevor wir den Import starten, stellen wir die beim SQL Editor die Konfigurationseinstellung AUTOCOMMIT aus. REchtslickt dazu im HANA Studio auf den SQL Editor und wählt Show In Properties. Dann setzt ihr AUTOCOMMIT auf false.

Bevor ihr den Import statet, prüft nochmal euer Flat File, ob darin kritische Sonderzeichen wie beispielsweise “ oder ‚ vorkommen. Falls ja, müsst ihr die Sonderzeichen entweder mit beispielsweise dem Linux-Kommanodzeilentool sed entfernen oder euch informieren, wie ihr die Sonderzeichen maskieren müsst, damit SAP HANA sie sauber übernimmt.

Der Import wird dann über den SQL Editor gestartet mittels

IMPORT FROM CONTROL FILE '/data/data.ctl';

 

Oracle

Daten von Der Datenbank in ein Flat File exportieren und wieder importieren

Um Daten aus einer Oracle Datenbank in ein Flat File exportieren wollen, nehmen Sie am besten den SQL Loader. Loggen Sie sich dazu auf der kommandozeile über sqlplus als der User ein, dem das Schema gehört, aus dem Sie DAten exportieren wollen, und geben Sie ein

sqlplus> set heading off
sqlplus> set pagesize 0
sqlplus> spool C:\flat_file.txt
sqlplus>SELECT * from <Schema>.Tabelle;
sqlplus>spool off

Nun haben Sie ein Flat File (in unserem Beispiel gespeichert als C:\flat_file.txt) erhalten mit den Daten der Tabelle, abgetrennt mit Kommata.

Wir erstellen jetzt noch ein sogenanntes discard file als C:\discard_file.dis. diese Datei enthält später alle Datensätze, die nicht geladen wurden. Das kläre ich gleich auf

Die selbe Tabelle können Sie nun mit einem solchen Flat File wieder füllen mit Hilfe von SQL Loader. Dazu bruachen Sie zuerst ein sogenanntes Control File, welches wir einfach mla als C:\control_file.ctl erstellen. Dort legen wir fest

LOAD DATA
INFILE 'C:\flat_file.txt'
DISCARDFILE 'C:\discard_file.dis'
INTO TABLE <tabellenname> TRUNCATE
WHEN <Spaltenname> <> '<Wert>'
FIELDS TERMINATED BY ","
(
<Spaltenname1>,
<Spaltenname2>,
... "TRIM (:<letzter_spaltenname>)"
)

Die Datie sit beinahe selbsterklärend. Das infile ist das Flat File, von dem die Daten geladen werden, das Discard File ist die DAtei mit den WErten, die überspringen werden. Wir laden das in die Tabelle, die wir in der Zeile INTO TABLE angeben, und die Zeile mit TRUNCATE bedeutet, dass wir den aktuellen Inhalt der tbaelle löschen, bevor wir die neuen DAten reinladen. die Zeile WHEN  <Spaltennanem> <> ‚<Wert>‘ ist nur ein biespiel dafür, dass man bestimmte Datensätze auslassen kann, das heißt man kann hier beispielsweise solche Sachen festlegen wie „wenn Herkunftsland = ‚Österreich‘, dann den Datensatz nicht in die Tabelle laden“. Die DAtensätze, die aufgrund dieser Regel ausgelassen werden, werden dann nacher in der discard_file.dis gelistet. FIELDS TERMINATED heißt, dass die einzelnen Werte im Flat File von einem Komma getrennt werden, und dann geben wir die Spaltenüberschriften ein, die unsere Spalten in der Tbaelle bekommen sollen. Am Ende der letzten Spaltenüberschrift schreiben wir diese TRIM-Direktive. Diese ignoriert alle Whitespaces (TAbstopps und Leerzeichen) im Flat File und verhindert somit beispielsweise, dass in einer Tabelle, in der es nur zwie Spalten mit Vorname und Name von Personen gibt, der Name als ‚Huber              ‚ (sau viele Leerezciehn danach) gespeihcert wird.

Jetzt laden wir die Daten in die Tabelle über die kommandozeile

C:\User\Public>sqlldr control=C:\control_file.ct log=C:\log_file.log

Nachdem ihr das kommadno absetzt müsst ihr euch mit einem ORacle-User einloggen. Das flat_file müssen wir nicht angeben, da der Pfad dorthin bereits im control_file.ctl steht. Nun werdne die Daten importiert und Fehlermeldungen oder sonstige Warnnugen werdne in die log_file.log geschrieben.

SAP MaxDB

In MaxDB kann man  über das Kommandozeilenprogramm loadercli exporiterne. Aebr auch über das grafische Database Studio geht es. Sie können im Databse Studio die fragliche Datnebankinstanz auswählen, darauf rechtsklicekn und dann im Kontextmenü den Eintrag EXPORT/IMPORT wählen.  Beim Exportierne können Sie ein Format wähhlen.  Eine einzelne Tabelle wird standardmäßig in das .sv-Format epxoriter.t Datenabnken, Schemata oder User werden entweder im Fromat PAGES oder RECORDS exporitert, diewiederum nur von MaxDB-Instanzen selbst geladen werdne können.

Beim LAden der Daten müssen Sie erst im Databsae Studio über WINDOWS / PREFERENCES / LOADER das Format angeben, in welchem die Daten vorliegnt. Wählen Sie pakacge files für PAGES oder RECORDS-Daten, oder Flat Files für .sv-Daten.

XML DAteien

 

Infos aus XML Dateien unter Linux extrahieren

Nehmen wir an, wir haben eine .xml-Datei wie die folgende

<buch>
 <titel>
 A Game of Thrones
 </titel>
 <autor>
 George R. R. Martin
 </autor>
</buch> 

<buch>
 <titel>
 Der Schatz im Silbersee
 </titel>
 <autor>
 Karl May
 </autor>
</buch>

und nehmen wir an, wir wollen aus der Datei alle Titel der Bücher haben. DAs würde unter Linux beispielsweise über das folgende sed-Kommando gehen.

sed "/titel/,/\/titel/" datei.xml

sed sucht hierbei zuerst nach der Zeichenkette „titel“ und gibtdann von dort aus alles aus, bis es au fdie Ziehcenkette „/titel“ trifft. Somit haben wir alle Titel in der Ausgabe drin. In Verbindung mit dem kommando grep können wir dann nach einem bestimmten Titel suchen, also beispielswiese

sed "/titel/,/\/titel/" datei.xml | grep Schatz im Silbersee

XML-Datei in Flat File umwandeln

Was ist nun, wenn wir eine XML-Datei in ein Flat File umwandeln wollen? Beispielsiwese in eine .csv-Datei, wo die Spaltenwerte durch kommas getrennt sind. Wir wollen also aus

<buch>
 <titel>
 A Game of Thrones
 </titel>
 <autor>
 George R. R. Martin
 </autor>
</buch> 

<buch>
 <titel>
 Der Schatz im Silbersee
 </titel>
 <autor>
 Karl May
 </autor>
</buch>

das hier machen

A Game of Thrones, George R. R. Martin
Der Schatz im Silbersee, Karl May

Denn wie wir weiter oben schon aus dem kapitel der Datenbanken gelernt haben, können wir solche Flat Files sehr leicht in eine Datenbank laden.

als erstes müssen wir es scahffen, dass alle Datensätze zusammen in einer Zeile stehen. Zuerst bringen wir mal alle Zeichen in der xml in eine einzige Zeile, indem wir alle Zeilenumbrüche entfernen.

tr -d '\n' < datei.xml

Jetzt stehen alle Daten in einer Zeile. Da wir ohnehin die Container <buch> und </buch> nicht mehr brauchen können, ersetzen wir <buch> durch eine leere Zeichenkette

tr -d '\n' < datei.xml | sed "s/buch//g"

und </buch> durch einen Zeilenumbruch.

tr -d '\n' < datei.xml | sed "s/<buch>//g" |sed "s/<\/buch>/\\n/g"

Jetzt stehen alle Bücher in einer extra Zeile.

Jetzt bekommen wir den die anderen Container weg, indem wir den öffnenden Container wieder durch eine leere Zeichenkette ersetzen.

tr -d '\n' < datei.xml | sed "s/<buch>//g" |sed "s/<\/buch>/\\n/g" | sed "s/<titel>//g"

und den schließenden Container durch ein Komma

tr -d '\n' < datei.xml | sed "s/<buch>//g" |sed "s/<\/buch>/\\n/g" | sed "s/<titel>//g" | sed "s/<\/titel>|,/g"

Wenn wir jetzt die Schritte für den Titel-Container für den Autor wiederholen

tr -d '\n' < datei.xml | sed "s/<buch>//g" |sed "s/<\/buch>/\\n/g" | sed "s/<titel>//g" | sed "s/<\/titel>|,/g" | sed "s/<autor>//g" | sed "s/<\/autor>|,/g"

haben wir nur noch das PRoblem, dass hinter dem Autor noch ein komma zu viel steht, dass wir nicht mehr brauchen. Also suchen wir zum Schluss noch nach Kommas, auf die direkt ein Zeilenumbruch folgt, und ersetzen diese durch einen leeren String, dazu pipen wir zu guter letzt einfach nur noch dran:

sed "s/,$//g"

Schon haben wir ein Flat File.

Flat Files in .xml-Dateien umwandeln

Flat Files, das haben Sie mittlerweile schon kennengelernt, sind Dateien, die wie Tabellen aufgebaut sind. Nur sind die Spalten der Tabelle eben durch Delimiters voneinander abgegrenzt. In einer .csv-Datei beispielsweise werden die Daten durch Kommata getrennt.

Wie Sie Flat Files in verschiedene Datenbanken laden, habe ich Ihnen bereits im Kapitel zur jeweiligen Datenbank gezeigt.

Wir wollen im Beispiel aus einem Flat File im Format

Johann,Johanson,45,Rechnungswesen
Anne,Anderson,55,Personalwesen
Mike,Michaelson,31,Programmierung

eine XML-Datei machen im Format

<angestellter>
 <vorname>
  Johann
 </vorname>
 <name>
  JOhnason
 </name>
 <alter>
  45
 </alter>
 <abteilung>
  Rechnungswesen
 </abteilung>
</angestellter>
...

machen.

Als erstes wandeln wir jeden Zeilenumbruch so um, dass ein XML-Container geschlossen und ein neuer begonnen wird.

sed "s/$/<\/angestellter>$<angestellter>/g"

damit erhalten wir

Johann,Johanson,45,Rechnungswesen</angestellter>
<angestellter>Anne,Anderson,55,Personalwesen</angestellter>
<angestellter>Mike,Michaelson,31,Programmierung</angestellter>

Dann schließen wir Vornamen und Nachnamen ein

sed "s/[A-Za-z]+,[A-Za-z]+/<vorname>[a-Z]+<\/vorname><name>[A-Za-z]<\/name>/g"

damit erhalten wird

<vorname>Johann</vorname><name>Johanson</name>,45,Rechnungswesen</angestellter>
<angestellter><vorname>Anne</vorname><name>Anderson</name>,55,Personalwesen</angestellter>
<angestellter><vorname>Mike</vorname><name>Michaelson</name>,31,Programmierung</angestellter>

dann ersetzen wir das Alter

sed "s/,[0-9]{2},/<alter>[0-9]{2}<\/alter>/g"

und erhalten

<vorname>Johann</vorname><name>Johanson</name><alter>45</alter>Rechnungswesen</angestellter>
<angestellter><vorname>Anne</vorname><name>Anderson</name><alter>55</alter>Personalwesen</angestellter>
<angestellter><vorname>Mike</vorname><name>Michaelson</name><alter>31</alter>Programmierung</angestellter>

und zu guter letzt schließ en wir noch die Programmierung ein

sed "s/<\/alter>[a-zA-Z]+/<\alter><abteilung>[a-zA-Z]+<\/abteilung>/g"

und schon ist unsere XML fertig, bis auf ein Detail

<vorname>Johann</vorname><name>Johanson</name><alter>45</alter><abteilung>Rechnungswesen</abteilung></angestellter>
<angestellter><vorname>Anne</vorname><name>Anderson</name><alter>55</alter><abteilung>Personalwesen</abteilung></angestellter>
<angestellter><vorname>Mike</vorname><name>Michaelson</name><alter>31</alter><abteilung>Programmierung</abteilung></angestellter>

und zwar müssen wir noch an den Anfang der Datei ein <angestellter> hinkriegen

sed "1s/^/<angestellter>/"

Die ganzen sed-Befehle hintereinander gepiped liefern uns dann den richtigen Output

<angestellter><vorname>Johann</vorname><name>Johanson</name><alter>45</alter><abteilung>Rechnungswesen</abteilung></angestellter>
<angestellter><vorname>Anne</vorname><name>Anderson</name><alter>55</alter><abteilung>Personalwesen</abteilung></angestellter>
<angestellter><vorname>Mike</vorname><name>Michaelson</name><alter>31</alter><abteilung>Programmierung</abteilung></angestellter>

 

Andreas Loibl ist SAP-Berater, Ethical Hacker und Online Marketing Manager und schreibt auf seinem Blog DaFRK Blog über verschiedene Themen in den Sektoren Projektmanagement, Informationstechnik, Persönlichkeitsentwicklung, Finanzen und Zeitmanagement.

DaFRK

Andreas Loibl ist SAP-Berater, Ethical Hacker und Online Marketing Manager und schreibt auf seinem Blog DaFRK Blog über verschiedene Themen in den Sektoren Projektmanagement, Informationstechnik, Persönlichkeitsentwicklung, Finanzen und Zeitmanagement.

Das könnte Dich auch interessieren …

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.