Große Datenmengen verarbeiten

root66 · 24.03.2011, 00:55

Hallo,

ich versuche gerade den CSV Export einer Datenbank in mein CMS-System (Drupal) zu importieren. Der Export besteht aus mehreren CSVs, die direkt aus einer Datenbank exportiert wurden. Jede Datenbanktabelle entspricht einer CSV Datei.

Das Problem ist, teilweise sind die Dateien über 1 GB groß. Mein Import-Skript ist so ausgelegt, daß es z.B. immer nur 100 Datensätze importieren soll, bis zum nächsten Cron-Aufruf. Um einen Datensatz aufzubereiten und dann zu importieren, muß ich mir allerdings aus mehreren CSV Dateien die Informationen (mit Fremdschlüsseln) zusammensammeln. Es gibt eine Hauptdatei, die ich nutze, um in 100er Schritten die Zeilen abzuarbeiten, aber ich kann nicht für jeden Datensatz jedesmal alle anderen CSVs nach den Zusatzdaten durchsuchen, das würde zu lange dauern. Und alle CSV Dateien kann ich auch nicht in den Speicher laden, weil die insgesamt 5 GB groß sind.

Ich hab's auch schon mit temporären Datenbank-Tabellen und LOAD DATA INFILE versucht, aber da scheitert es an der Codierung, weil noch eine 5.0.x im Einsatz ist und man bei LOAD DATA INFILE erst ab 5.1 die Kodierung angeben kann.

Ich möchte unbedingt erreichen, daß ein Datensatz samt aller Fremdschlüssel-Daten komplett aufbereitet ist, bevor ich ihn an das CMS zum Import gebe, weil mehrere Updates im CMS zu lange dauern.

Ich bräuchte einen schnellen Dateisystem-Zwischenspeicher, wo ich mit einem Index einen Datensatz anspringen und den dann nach und nach erweitern kann. Oder wäre eine serialisierte Datei pro Datensatz besser, auch wenn es dann insgesamt über 1 Mio. temporäre Dateien wären?
Wie würdet ihr das machen?

VG,
root66

tr0y · 24.03.2011, 01:11

Wie wärs wenn du eine XML aus allen CSV files bei dir lokal generierst und diese dann in die datenbank häppchenweise importierst ? Alternativ kannst du auch direkt SQL-Statements aus den Daten erzeugen und diese dann per SQL-Client ausführen, dabei ist dem SQL-Client egal ob er 2, 5, 10 oder 100 GB SQL-Statements verarbeitet. Dauert halt seine Zeit.

Die XML kann per stylesheet so evaluiert werden das sie bei egal welcher Anfrage die nötigen Daten als CSV-Format repräsentiert. ( XSLT + XML )

root66 · 24.03.2011, 01:36

Zitat:

Zitat von tr0y

Wie wärs wenn du eine XML aus allen CSV files bei dir lokal generierst und diese dann in die datenbank häppchenweise importierst ?

Um eine XML Datei zu erzeugen, muß ich genauso vorgehen. Die Haupt-CSV öffnen, einen Datensatz lesen und dann für diesen Datensatz über den Fremdschlüssel in den anderen CSVs die Zusatzdaten suchen. Das würde für jeden der ca. 1 Mio. Datensätze bis zu 5 Minuten dauern, je nachdem wo der Datensatz in der Datei steht.

Wenn ich diese Dateien einzeln sequentiell abarbeite, hab ich das Problem, daß ich in der generierten XML Datei den entsprechenden Datensatz suchen muß, um ihn zu erweitern. Würde wahrscheinlich noch länger dauern, weil ich die XML Datei nicht komplett in den Speicher lesen kann.

tr0y · 24.03.2011, 01:47

Wie lang etwas bei dir lokal dauert kann doch absolut egal sein oder ?

Und wie groß du die XML Datei werden lässt hängt immernoch von der Methode ab die du verwendest, schlussendlich muss die xml auf dem Zielsystem geöffnet und importiert werden -> Häppchen enstehen also by-File.

Alternativ kannst du deine CSVs auch lokal zu einer Datenbank zusammensetzen und einen DB-Dump erstellen ( der dann 5 GB + groß sein wird ), Welchen du dann in verkraftbare Häppchen aufteilst. Die Files in die Datenbank auf dem Zielsystem zu treten sollte ja dann wohl das kleinste Problem sein.

Ich hab auch noch nicht ganz verstanden was du beim DB-Import mit einem Cronjob willst, willst du die Imports öfter wie einmal durchführen ? Ein Import-Script das 10 Mio Datensätze in eine Datenbank drücken soll ruft alle 100 Datensätze per redirect sich selbst auf um die nächsten 100 zu importieren. Wie lange das dauert ist bei einzigartigen Import eines Datenbankinhaltes wohl absolut egal.

Oder Missversteh ich dich ?

root66 · 24.03.2011, 01:58

Der Import soll regelmäßig und vollautomatisch laufen, deshalb Cron und weil PHP bzw. das CMS viel Ressourcen benötigt, soll das Skript nicht ununterbrochen laufen.

Ich werde wohl die CSVs erstmal in MySQL importieren und dort zusammensetzen und dann häppchenweise ins CMS laden.

tr0y · 24.03.2011, 02:05

Okay, dann würde ich:

- mehrere XMLs erstellen die den zu importierenden Datenbestand beinhalten, aufgeteilt in je 100 Datensätze für alle betroffenen Tables. ( Vielleicht auch nur 50 wenn die Tableanzahl sehr hoch ist ). Also: Je XML 100 Rows / Table.
- Jede XML als Job in ein Job-Table für den Cronjob injizieren.
- Einen Cronjob entwickeln der permanent das Job-Table nach Jobs scannt. Ist was drin im Table einen Eintrag als "InProgress" marken und die XML abarbeiten. Ist der Import fertig, XML Löschen und Job als "Done" markieren.
- Den Cronjob je nach serverlast entweder als Worker-Manager realisieren so das mehrere Instanzen von ihm parallel laufen können.

Sobald das Job-Table leer ist ist der Datenbank-Import den du automatisiert fertigstellen willst beendet.

Auf der Datenbank-Export-"Kiste" wo du die Daten herholst würde ich statt zu exportieren direkt die Datenbank per Script zu XML-Daten verarbeiten, sie nach jedem generieren der XML direkt per FTP / cURL-POST auf den Zielserver schieben. Ist der Export dort fertig gestellt -> Jobs per Mutliquery-Inserts ins Job-Table drücken. Done.

serious-cool · 24.03.2011, 12:25

Ich würde das über ein ,,systemnahes" Programm lösen, php erscheint mir für solche Operationen etwas zu langsam.

Schreib dir ein kleines C/C++/JAVA Programm welches den Import vornimmt, dies spart Ressourcen und kann problemlos mit jeder Datenmenge umgehen. Dann feuert das Programm auch genau so schnell wie die DB zuhört... (eigener Server vorausgesetzt, dem Thread eine niedrige Priorität zuweisen und Task-gesteuert morgens um 3 loslassen. Zusätzlich einen eigenen Datenbankbenutzer mit Leistungseinschränkungen verwenden, dann ist das Livesystem auch nicht gefährdet.)

(Falls keine Konvertierung nötig ist, kannst Du das auch direkt mit dem MySQL Workbench machen. )

tr0y · 24.03.2011, 12:30

php ist für diese Zwecke schnell genug, warum erst das gaspedal frisieren wenn du dann eh die bremse drückst ( per mysql user restrictions + low priority ) ?

serious-cool · 24.03.2011, 12:51

weil periodische Arbeitsvorgängen die im ,,Hintergrund" ablaufen sollen, im Hintergrund bleiben sollen

Klar, bei einmaligen Vorgängen - Vollgas...
Warum mit PHP auf die Systemebene losgehen, für die es nicht(wirklich) erfunden wurde.

tr0y · 24.03.2011, 12:56

C(++) Importer -> unnötige Mehrarbeit

One PHP script per host to rule them all, no compile, no threat acrobatics, just run.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
Mehrere Werte in einem script verarbeiten	ernich	PHP Tipps 2010	7	09.04.2010 20:24
[Erledigt] [SFTP] Große Datenmengen - Queue?	jGeee	PHP-Fortgeschrittene	3	08.03.2010 10:55
Wie Größe einer IMAP/NNTP Message ohne kompl. Download rausfinden?	cider	PHP-Fortgeschrittene	6	22.02.2010 23:44
Größe in PX nach rechts flexibel	Rubio	PHP Tipps 2010	30	22.02.2010 23:12
Große Datenmenge (Bilder) verarbeiten	Logan1980	PHP Tipps 2009	7	01.01.2010 18:28
[Erledigt] Tabellenlayout: Große Felder auslagern?	boernard	Datenbanken	7	27.07.2009 14:03
[Erledigt] Bild mit wasserzeichen in vari größe	Kay654	PHP Tipps 2009	10	14.04.2009 14:25
große Datenmengen und die Same Origin Policy	R4v3r	JavaScript, Ajax und mehr	4	30.03.2009 12:42
mit jQuery grosse Formulare verarbeiten	phpbeginner	HTML, Usability und Barrierefreiheit	5	13.06.2008 22:58
Größe von Memory (Heap) Tabellen	R4v3r	Datenbanken	1	22.08.2007 17:11
DIV dynamischer Größe mit Schatten	TheIfrit	HTML, Usability und Barrierefreiheit	9	15.03.2006 18:21
Größe von Button ändern		HTML, Usability und Barrierefreiheit	2	29.09.2005 15:29
große datenmengen aus mysql auslesen	bratwurstschorsch	PHP Tipps 2005-2	6	04.08.2005 16:05
[Erledigt] sehr große Dateien mit PHP verarbeiten		PHP-Fortgeschrittene	6	16.03.2005 22:06
[Erledigt] Größe des Fenster durch array		PHP Tipps 2004	12	01.09.2004 12:03

24.03.2011, 00:55
root66 Benutzer Registriert seit: 13.10.2008 Beiträge: 70	Große Datenmengen verarbeiten Hallo, ich versuche gerade den CSV Export einer Datenbank in mein CMS-System (Drupal) zu importieren. Der Export besteht aus mehreren CSVs, die direkt aus einer Datenbank exportiert wurden. Jede Datenbanktabelle entspricht einer CSV Datei. Das Problem ist, teilweise sind die Dateien über 1 GB groß. Mein Import-Skript ist so ausgelegt, daß es z.B. immer nur 100 Datensätze importieren soll, bis zum nächsten Cron-Aufruf. Um einen Datensatz aufzubereiten und dann zu importieren, muß ich mir allerdings aus mehreren CSV Dateien die Informationen (mit Fremdschlüsseln) zusammensammeln. Es gibt eine Hauptdatei, die ich nutze, um in 100er Schritten die Zeilen abzuarbeiten, aber ich kann nicht für jeden Datensatz jedesmal alle anderen CSVs nach den Zusatzdaten durchsuchen, das würde zu lange dauern. Und alle CSV Dateien kann ich auch nicht in den Speicher laden, weil die insgesamt 5 GB groß sind. Ich hab's auch schon mit temporären Datenbank-Tabellen und LOAD DATA INFILE versucht, aber da scheitert es an der Codierung, weil noch eine 5.0.x im Einsatz ist und man bei LOAD DATA INFILE erst ab 5.1 die Kodierung angeben kann. Ich möchte unbedingt erreichen, daß ein Datensatz samt aller Fremdschlüssel-Daten komplett aufbereitet ist, bevor ich ihn an das CMS zum Import gebe, weil mehrere Updates im CMS zu lange dauern. Ich bräuchte einen schnellen Dateisystem-Zwischenspeicher, wo ich mit einem Index einen Datensatz anspringen und den dann nach und nach erweitern kann. Oder wäre eine serialisierte Datei pro Datensatz besser, auch wenn es dann insgesamt über 1 Mio. temporäre Dateien wären? Wie würdet ihr das machen? VG, root66


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

24.03.2011, 01:11
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 4.874 PHP-Kenntnisse: Fortgeschritten	Wie wärs wenn du eine XML aus allen CSV files bei dir lokal generierst und diese dann in die datenbank häppchenweise importierst ? Alternativ kannst du auch direkt SQL-Statements aus den Daten erzeugen und diese dann per SQL-Client ausführen, dabei ist dem SQL-Client egal ob er 2, 5, 10 oder 100 GB SQL-Statements verarbeitet. Dauert halt seine Zeit. Die XML kann per stylesheet so evaluiert werden das sie bei egal welcher Anfrage die nötigen Daten als CSV-Format repräsentiert. ( XSLT + XML ) __________________ Lasse mir ohne Anwendung von Gewalt Dinge schenken, Amazon weiß darüber bald mehr.

24.03.2011, 01:47
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 4.874 PHP-Kenntnisse: Fortgeschritten	Wie lang etwas bei dir lokal dauert kann doch absolut egal sein oder ? Und wie groß du die XML Datei werden lässt hängt immernoch von der Methode ab die du verwendest, schlussendlich muss die xml auf dem Zielsystem geöffnet und importiert werden -> Häppchen enstehen also by-File. Alternativ kannst du deine CSVs auch lokal zu einer Datenbank zusammensetzen und einen DB-Dump erstellen ( der dann 5 GB + groß sein wird ), Welchen du dann in verkraftbare Häppchen aufteilst. Die Files in die Datenbank auf dem Zielsystem zu treten sollte ja dann wohl das kleinste Problem sein. Ich hab auch noch nicht ganz verstanden was du beim DB-Import mit einem Cronjob willst, willst du die Imports öfter wie einmal durchführen ? Ein Import-Script das 10 Mio Datensätze in eine Datenbank drücken soll ruft alle 100 Datensätze per redirect sich selbst auf um die nächsten 100 zu importieren. Wie lange das dauert ist bei einzigartigen Import eines Datenbankinhaltes wohl absolut egal. Oder Missversteh ich dich ? __________________ Lasse mir ohne Anwendung von Gewalt Dinge schenken, Amazon weiß darüber bald mehr.

24.03.2011, 01:58
root66 Benutzer Registriert seit: 13.10.2008 Beiträge: 70	Der Import soll regelmäßig und vollautomatisch laufen, deshalb Cron und weil PHP bzw. das CMS viel Ressourcen benötigt, soll das Skript nicht ununterbrochen laufen. Ich werde wohl die CSVs erstmal in MySQL importieren und dort zusammensetzen und dann häppchenweise ins CMS laden.

24.03.2011, 02:05
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 4.874 PHP-Kenntnisse: Fortgeschritten	Okay, dann würde ich: - mehrere XMLs erstellen die den zu importierenden Datenbestand beinhalten, aufgeteilt in je 100 Datensätze für alle betroffenen Tables. ( Vielleicht auch nur 50 wenn die Tableanzahl sehr hoch ist ). Also: Je XML 100 Rows / Table. - Jede XML als Job in ein Job-Table für den Cronjob injizieren. - Einen Cronjob entwickeln der permanent das Job-Table nach Jobs scannt. Ist was drin im Table einen Eintrag als "InProgress" marken und die XML abarbeiten. Ist der Import fertig, XML Löschen und Job als "Done" markieren. - Den Cronjob je nach serverlast entweder als Worker-Manager realisieren so das mehrere Instanzen von ihm parallel laufen können. Sobald das Job-Table leer ist ist der Datenbank-Import den du automatisiert fertigstellen willst beendet. Auf der Datenbank-Export-"Kiste" wo du die Daten herholst würde ich statt zu exportieren direkt die Datenbank per Script zu XML-Daten verarbeiten, sie nach jedem generieren der XML direkt per FTP / cURL-POST auf den Zielserver schieben. Ist der Export dort fertig gestellt -> Jobs per Mutliquery-Inserts ins Job-Table drücken. Done. __________________ Lasse mir ohne Anwendung von Gewalt Dinge schenken, Amazon weiß darüber bald mehr.

24.03.2011, 12:25
serious-cool Erfahrener Benutzer Registriert seit: 12.09.2008 Beiträge: 154 PHP-Kenntnisse: Fortgeschritten	Ich würde das über ein ,,systemnahes" Programm lösen, php erscheint mir für solche Operationen etwas zu langsam. Schreib dir ein kleines C/C++/JAVA Programm welches den Import vornimmt, dies spart Ressourcen und kann problemlos mit jeder Datenmenge umgehen. Dann feuert das Programm auch genau so schnell wie die DB zuhört... (eigener Server vorausgesetzt, dem Thread eine niedrige Priorität zuweisen und Task-gesteuert morgens um 3 loslassen. Zusätzlich einen eigenen Datenbankbenutzer mit Leistungseinschränkungen verwenden, dann ist das Livesystem auch nicht gefährdet.) (Falls keine Konvertierung nötig ist, kannst Du das auch direkt mit dem MySQL Workbench machen. ) __________________ Ein Klauenhammer ist kein Schraubendreher, auch wenn beide das Problem ,,Schraube-Wand" bidirektional lösen können.

24.03.2011, 12:30
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 4.874 PHP-Kenntnisse: Fortgeschritten	php ist für diese Zwecke schnell genug, warum erst das gaspedal frisieren wenn du dann eh die bremse drückst ( per mysql user restrictions + low priority ) ? __________________ Lasse mir ohne Anwendung von Gewalt Dinge schenken, Amazon weiß darüber bald mehr.

24.03.2011, 12:51
serious-cool Erfahrener Benutzer Registriert seit: 12.09.2008 Beiträge: 154 PHP-Kenntnisse: Fortgeschritten	weil periodische Arbeitsvorgängen die im ,,Hintergrund" ablaufen sollen, im Hintergrund bleiben sollen Klar, bei einmaligen Vorgängen - Vollgas... Warum mit PHP auf die Systemebene losgehen, für die es nicht(wirklich) erfunden wurde. __________________ Ein Klauenhammer ist kein Schraubendreher, auch wenn beide das Problem ,,Schraube-Wand" bidirektional lösen können.

24.03.2011, 12:56
tr0y Erfahrener Benutzer Registriert seit: 26.07.2010 Beiträge: 4.874 PHP-Kenntnisse: Fortgeschritten	C(++) Importer -> unnötige Mehrarbeit One PHP script per host to rule them all, no compile, no threat acrobatics, just run. __________________ Lasse mir ohne Anwendung von Gewalt Dinge schenken, Amazon weiß darüber bald mehr.