Data Analytics
April 25, 2019

BigQuery: Migration von On-Prem PostgreSQL

How to move your data from an on-premises solution to Google Cloud Platform without harming your current setup and production performance? Find out here:

arrow downarrow down

Interview multiple candidates

Lorem ipsum dolor sit amet, consectetur adipiscing elit proin mi pellentesque  lorem turpis feugiat non sed sed sed aliquam lectus sodales gravida turpis maassa odio faucibus accumsan turpis nulla tellus purus ut   cursus lorem  in pellentesque risus turpis eget quam eu nunc sed diam.

Search for the right experience

Lorem ipsum dolor sit amet, consectetur adipiscing elit proin mi pellentesque  lorem turpis feugiat non sed sed sed aliquam lectus sodales gravida turpis maassa odio.

  1. Lorem ipsum dolor sit amet, consectetur adipiscing elit.
  2. Porttitor nibh est vulputate vitae sem vitae.
  3. Netus vestibulum dignissim scelerisque vitae.
  4. Amet tellus nisl risus lorem vulputate velit eget.

Ask for past work examples & results

Lorem ipsum dolor sit amet, consectetur adipiscing elit consectetur in proin mattis enim posuere maecenas non magna mauris, feugiat montes, porttitor eget nulla id id.

  • Lorem ipsum dolor sit amet, consectetur adipiscing elit.
  • Netus vestibulum dignissim scelerisque vitae.
  • Porttitor nibh est vulputate vitae sem vitae.
  • Amet tellus nisl risus lorem vulputate velit eget.
Vet candidates & ask for past references before hiring

Lorem ipsum dolor sit amet, consectetur adipiscing elit ut suspendisse convallis enim tincidunt nunc condimentum facilisi accumsan tempor donec dolor malesuada vestibulum in sed sed morbi accumsan tristique turpis vivamus non velit euismod.

“Lorem ipsum dolor sit amet, consectetur adipiscing elit nunc gravida purus urna, ipsum eu morbi in enim”
Once you hire them, give them access for all tools & resources for success

Lorem ipsum dolor sit amet, consectetur adipiscing elit ut suspendisse convallis enim tincidunt nunc condimentum facilisi accumsan tempor donec dolor malesuada vestibulum in sed sed morbi accumsan tristique turpis vivamus non velit euismod.

Verlagerung von Daten von On-Prem zu BigQuery

Heutzutage nutzen immer mehr Unternehmen Google Cloud BigQuery, um ihre Anforderungen an ein Cloud-basiertes Enterprise Data Warehouse zu erfüllen. Wenn Sie zu diesen Unternehmen gehören, stehen Sie möglicherweise vor dem Problem, wie Sie Ihre Daten von einer On-Premises-Lösung auf die Google Cloud Platform verschieben können, ohne Ihre aktuelle Einrichtung und Produktionsleistung zu beeinträchtigen. In diesem Beitrag werfen wir einen Blick auf ein sehr spezifisches Problem, bei dem Sie (der Kunde)

  • in die Cloud umziehen möchten, weil Ihre Geschäftsberichte sehr langsam sind, selbst bei einem Datensatz von 500 GB.
  • Sie haben derzeit einen kleinen Produktionsserver, auf dem PostgreSQL mit einer Größe von ~500GB-1TB Datenbank, zwei Kernen, 16GB Speicher und einer hohen Last läuft.
  • Sie möchten Ihre Daten auch in PostgreSQL behalten, daher ist die beste Option Streaming (oder Mini-Bach-Transfers), zusätzlich zum ursprünglichen Datenexport.
  • nicht alle Ihre Daten exportieren wollen (aber Sie haben es als eine mögliche Anforderung für die Zukunft definiert).
  • vielleicht die Ergebnisse bestimmter Abfragen von PostgreSQL exportieren möchten, anstatt alles zu dumpen.
  • Daten haben, die sehr komplex sind.
  • VPN verwenden.

Im Grunde wollen Sie im Moment nichts ändern, aber Sie möchten BigQuery parallel nutzen. Unsere Herausforderung besteht darin, eine Pipeline zu erstellen, die in der Lage ist, die Daten aus PostgreSQL zu BigQuery zu streamen. Der schwierige Teil ist, dass wir die Einschränkungen des Servers berücksichtigen müssen. Er muss auch die anfänglichen Datenlasten und das Streaming gleichzeitig bewältigen, wobei eine maximale Tabellengröße von ~200 GB zu berücksichtigen ist.

Architektur

Composer: Cloud Composer ist ein vollständig verwalteter Workflow-Orchestrierungsdienst, mit dem Sie Pipelines erstellen, planen und überwachen können, die sich über Clouds und On-Premise-Rechenzentren erstrecken. Cloud Composer basiert auf dem beliebten Open-Source-Projekt Apache Airflow und wird mit der Programmiersprache Python betrieben. Er ist frei von Abhängigkeiten und einfach zu bedienen.

Pub/Sub: Cloud Pub/Sub ist eine einfache, zuverlässige und skalierbare Grundlage für Stream-Analytics/Event-Driven-Computing-Systeme und ist ein globaler Messaging- und Event-Ingestion-Service.

Google Cloud Storage: Google Cloud Storage (GCS) ist eine flexible, skalierbare und dauerhafte Speicheroption. Sie können Dateien in GCS-Buckets von fast überall aus lesen und schreiben, sodass Sie Buckets als gemeinsamen Speicher zwischen Ihren Instanzen, Google App Engine, Ihren On-Premise-Systemen und anderen Cloud-Diensten verwenden können.

BigQuery: BigQuery ist Googles serverloses, hoch skalierbares Data Warehouse für Unternehmen, das entwickelt wurde, um alle Ihre Datenanalysten zu einem unübertroffenen Preis-Leistungs-Verhältnis produktiv zu machen. Da keine Infrastruktur verwaltet werden muss, können Sie sich auf die Analyse von Daten konzentrieren, um aussagekräftige Erkenntnisse zu gewinnen, indem Sie vertrautes SQL verwenden, ohne einen Datenbankadministrator zu benötigen.

Docker: Docker ist ein Tool, das die Erstellung, Bereitstellung und Ausführung von Anwendungen mithilfe von Containern erleichtert. Mit Containern kann ein Entwickler eine Anwendung mit allen benötigten Bestandteilen, wie Bibliotheken und anderen Abhängigkeiten, in einem Paket zusammenfassen und ausliefern.

Erläuterung

  1. Composer veröffentlicht regelmäßig eine Anfrage über Pub/Sub, die die inkrementelle Abfrage enthält (basierend auf Zeitstempeln).
  2. Die Java-Docker-Anwendung empfängt sie und startet den Export.
  3. Nachdem der Export abgeschlossen ist, lädt die Java-Docker-Anwendung die JSON-Datei mit gsutil in das GCS hoch.
  4. Anschließend veröffentlicht die Java-Docker-Anwendung eine Nachricht an Pub/Sub, dass die Aufgabe abgeschlossen ist.
  5. Composer empfängt diese Nachricht, übernimmt die JSON-Datei von GCS und startet einen Ladeauftrag für BigQuery

Effizienter JSON-Export aus PostgreSQL

Hier wird nur der interessanteste Teil des effizienten Exports von JSON aus PostgreSQL behandelt: der Befehl psql der Java-Docker-Anwendung, der den Export durchführt. Das ist eine Herausforderung. Manchmal müssen nur ein paar Megabyte exportiert werden, aber wenn eine neue Tabelle hinzugefügt wird, kann es sein, dass mehr als 200 GB exportiert werden müssen. Und während des Exports wird die Datenbank noch in der Produktion verwendet.

Wir müssen also so speicher- und IO-effizient wie möglich sein, um Ihre Anwendung nicht zu beeinträchtigen. Die einzige Möglichkeit, dies zu erreichen, ohne viel Speicher zu verbrauchen, ist die Verwendung von COPY von PostgreSQL. COPY unterscheidet sich von anderen Lösungen (wie z.B. der Umleitung von Abfrageergebnissen in eine Datei), weil es die Daten auf die Standardausgabe oder in eine Datei streamen kann, so dass eine kleine Menge Speicher ausreicht. Das Problem ist, dass es nur in die CSV-Datei exportieren kann. Und das Problem mit CSV ist, dass die Daten in der Datenbank so komplex sind, dass das CSV-Format sie nicht verarbeiten kann. Daher wählen wir ein anderes Ausgabeformat, JSON.

Exportieren nach JSON mit COPY

Um dieses Problem zu lösen, muss die Abfrage selbst ein JSON zurückgeben. Dann müssen wir es nur noch über COPY in eine Datei oder stdout streamen. Es ist eigentlich sehr einfach.

Das Problem dabei ist jedoch, dass COPY beim Export über COPY das JSON-Format ruiniert, weil COPY standardmäßig doppelte Anführungszeichen (") als CSV-Spaltentrennzeichen verwendet. Die Lösung ist ein wenig umständlich, funktioniert aber sehr gut:

Damit werden die standardmäßigen doppelten Anführungszeichen durch ein Backspace-Zeichen während der Escape-Phase ersetzt. Auf diese Weise wird COPY die JSON-Struktur nicht zerstören, da es nach Backspace-Zeichen als Spaltentrennzeichen suchen wird. Mit dieser Lösung haben wir eine Datei, die JSON-Daten in einer CSV-Spalte enthält, die mit Backspace-Zeichen getrennt sind. Wir müssen diese Backspace-Zeichen in der Datei einfach in jeder Zeile ersetzen:

Hinweis: Je nach Daten können wir ein Backspace-Zeichen durch ein beliebiges anderes Zeichen ersetzen, das die Anforderungen des QUOTE-Parameters erfüllt. Jetzt sind die Daten bereit, mit gsutil in den Cloud-Speicher hochgeladen zu werden.

Die Guten und die Schlechten

Wie immer gibt es gute und schlechte Dinge an diesem Vorgang.

Das Gute

Auf der On-Prem-Seite ist diese Lösung eine effiziente Möglichkeit, Daten zu streamen. Durch die Verwendung von Pub/Sub lassen sich außerdem VPN-/Firewall-Probleme leicht vermeiden, da wir keine Netzwerk-Hacks benötigen. Es ist eine skalierbare, robuste Lösung.

Das Schlechte

In dieser Lösung bietet Composer die Orchestrierung, die für den Einsatz in komplexeren Szenarien entwickelt wurde. Hier ist sie ein wenig übertrieben, da sie mindestens drei Maschinen in Gang setzt.

Zusammenfassung

Am Ende haben wir eine robuste, skalierbare Lösung. Sie ist in der Lage, eine gültige JSON-Datei aus einer 1 TB großen Datenbank zu exportieren, ohne viel Arbeitsspeicher zu verbrauchen und die Datenbank zu beschädigen. Diese JSON-Dateien können dann einfach in BigQuery von GCS importiert werden, damit Sie sie verwenden können.

Related articles