Datenbankreplikation mit Change Data Capture

Last reviewed 2020-10-20 UTC

In diesem Dokument werden verschiedene Ansätze erläutert, wie Sie mithilfe von CDC (Change Data Capture) eine Vielzahl von Datenquellen in BigQuery einbinden. Das Dokument enthält dazu eine Analyse des Verhältnisses von Datenkonsistenz, Nutzerfreundlichkeit und Kosten bei den einzelnen Ansätzen. Es bietet eine Unterstützung für vorhandene Lösungen, erläutert die verschiedenen Ansätze zur Nutzung von Daten, die von CDC repliziert werden, und zeigt, wie Sie eine Kosten-Nutzen-Analyse für die einzelnen Ansätze erstellen.

Es ist für Data Architects, Data Engineers und Business-Analysten konzipiert und soll diese bei der Entwicklung des optimalen Ansatzes für den Zugriff auf replizierte Daten in BigQuery unterstützen. Dabei wird davon ausgegangen, dass Sie mit BigQuery, SQL und Befehlszeilentools vertraut sind.

Übersicht über die CDC-Datenreplikation

Meist werden Datenbanken wie MySQL, Oracle und SAP als CDC-Datenquellen betrachtet. Es kann aber jedes System als Datenquelle angesehen werden, das Änderungen an Datenelementen erfasst und bereitstellt, die durch Primärschlüssel identifiziert werden. Wenn ein System keinen eigenen CDC-Prozess enthält, wie etwa ein Transaktionslog, können Sie Änderungen mit einem inkrementellen Batch-Leser abrufen.

In diesem Dokument werden CDC-Prozesse erläutert, die folgende Kriterien erfüllen:

1. Die Datenreplikation erfasst Änderungen für jede Tabelle separat.
2. Jede Tabelle hat einen Primärschlüssel oder einen zusammengesetzten Primärschlüssel.
3. Jedem ausgegebenen CDC-Ereignis wird eine inkrementell ansteigende Änderungs-ID zugewiesen. Dies ist in der Regel ein numerischer Wert wie z. B. eine Transaktions-ID oder ein Zeitstempel.
4. Jedes CDC-Ereignis enthält den vollständigen Status der geänderten Zeile.

Das folgende Diagramm zeigt eine allgemeine Architektur mit CDC für das Replizieren von Datenquellen nach BigQuery:

Im obigen Diagramm wird für jede Quelldatentabelle eine Haupttabelle sowie eine Tabelle mit den Änderungen (im Folgenden als „Deltatabelle“ bezeichnet) in BigQuery erstellt. Die Haupttabelle enthält alle Spalten der Quelltabelle sowie eine Spalte für den neuesten Wert der Änderungs-ID. Der neueste Wert der Änderungs-ID ist praktisch die Versions-ID der Entität, die durch den Primärschlüssel des Eintrags identifiziert wird und zur Ermittlung der neuesten Version verwendet werden kann.

Die Deltatabelle enthält alle Spalten der Quelltabelle sowie eine Spalte für den jeweiligen Vorgangstyp, also für das Aktualisieren, Einfügen oder Löschen, sowie den Wert der Änderungs-ID.

Im Folgenden sind alle Schritte zum Replizieren von Daten nach BigQuery mithilfe von CDC zusammenfassend dargestellt:

1. Ein erster Datendump der Quelltabelle wird extrahiert.
2. Die extrahierten Daten werden optional transformiert und dann in die entsprechende Haupttabelle geladen. Wenn die Tabelle keine Spalte für die Änderungs-ID hat, z. B. für einen Zeitstempel der letzten Aktualisierung, wird die Änderungs-ID auf den niedrigsten für den Datentyp dieser Spalte möglichen Wert gesetzt. Damit können durch nachfolgende Verarbeitung die Haupttabelleneinträge identifiziert werden, die nach dem ersten Datendump aktualisiert werden.
3. Die Zeilen, die sich nach dem ersten Datendump ändern, werden vom CDC-Erfassungsprozess erfasst.
4. Bei Bedarf wird durch die CDC-Verarbeitungsebene eine zusätzliche Datentransformation ausgeführt. Beispielsweise können auf der CDC-Verarbeitungsebene der Zeitstempel für die Verwendung durch BigQuery neu formatiert, Spalten vertikal aufgeteilt oder Spalten entfernt werden.
5. Die Daten werden mithilfe von Mikro-Batch-Ladevorgängen oder Streaming-Insert-Anweisungen in die entsprechende Deltatabelle in BigQuery eingefügt.

Bei weiteren Transformationen vor dem Einfügen der Daten in BigQuery können Anzahl und Typ der Spalten von der Quelltabelle abweichen. In der Haupt- und Deltatabelle ist aber jeweils der gleiche Satz von Spalten vorhanden.

Deltatabellen enthalten alle Änderungsereignisse für eine bestimmte Tabelle seit dem ersten Laden. Auf Grundlage der gesamten Änderungsereignisse lassen sich mögliche Trends erkennen, der Status der Entitäten, die eine Tabelle zu einem bestimmten Zeitpunkt darstellt, ermitteln und die Häufigkeit von Änderungen feststellen.

Zum Abrufen des aktuellen Status einer Entität, die durch einen bestimmten Primärschlüssel dargestellt wird, können Sie die Haupttabelle und die Deltatabelle nach dem Eintrag mit der neuesten Änderungs-ID abfragen. Diese Abfrage kann kostspielig sein, da Sie möglicherweise einen Join zwischen der Haupt- und der Deltatabelle sowie einen vollständigen Tabellenscan von einer Tabelle oder von beiden Tabellen ausführen müssen, um den neuesten Eintrag für einen bestimmten Primärschlüssel zu ermitteln. Sie können diesen vollständigen Tabellenscan vermeiden, wenn Sie die Tabellen anhand des Primärschlüssels clustern oder partitionieren. Das ist jedoch nicht immer möglich.

In diesem Dokument werden die folgenden grundlegenden Ansätze verglichen, mit denen Sie den aktuellen Status einer Entität ermitteln können, wenn sich die Tabellen nicht partitionieren oder gruppieren lassen:

• Ansatz der sofortigen Konsistenz: Die Abfragen geben den aktuellen Status replizierter Daten wieder. Für die sofortige Konsistenz ist eine Abfrage erforderlich, die die Haupt- und die Deltatabelle verknüpft und die neueste Zeile für jeden Primärschlüssel auswählt.
• Kostenoptimierter Ansatz: Dabei werden schnellere und kostengünstigere Abfragen auf Kosten der Datenverfügbarkeit ausgeführt. Sie können die Daten regelmäßig in der Haupttabelle zusammenführen.
• Hybrider Ansatz: Sie verwenden je nach Anforderungen und Budget den Ansatz der sofortigen Konsistenz oder den kostenoptimierten Ansatz.

Weiter unten in diesem Dokument werden zusätzliche Möglichkeiten zur Verbesserung der Leistung erläutert.

Hinweise

In diesem Dokument wird gezeigt, wie Sie BigQuery-Daten mit dem bq-Befehlszeilentoo und mit SQL-Anweisungen aufrufen sowie abfragen. Weiter unten in diesem Dokument werden außerdem Beispieltabellenlayouts und -abfragen beschrieben. Um mit den Beispieldaten zu experimentieren, führen Sie die folgenden Schritte aus:

1. Wählen Sie ein Projekt aus oder erstellen Sie ein Projekt und aktivieren Sie die Abrechnung für das Projekt.
   • Wenn Sie ein Projekt erstellen, wird BigQuery automatisch aktiviert.
   • Wenn Sie ein vorhandenes Projekt auswählen, müssen Sie die BigQuery API aktivieren.
2. Öffnen Sie Cloud Shell in der Google Cloud Console.

3. Öffnen Sie zum Aktualisieren der BigQuery-Konfigurationsdatei die Datei ~/.bigqueryrc in einem Texteditor. Fügen Sie der Datei an einer beliebigen Stelle die folgenden Zeilen hinzu oder aktualisieren diese:

   [query]
   --use_legacy_sql=false
   
   [mk]
   --use_legacy_sql=false
   

4. Klonen Sie das GitHub-Repository, das die Skripts zum Einrichten der BigQuery-Umgebung enthält:

   git clone https://github.com/GoogleCloudPlatform/bq-mirroring-cdc.git
   

5. Erstellen Sie die Dataset-, Haupt- und Deltatabelle:

   cd bq-mirroring-cdc/tutorial
   chmod +x *.sh
   ./create-tables.sh
   

Um Kosten nach Abschluss der Experimentierphase zu vermeiden, fahren Sie das Projekt herunter oder löschen Sie das Dataset.

BigQuery-Daten einrichten

Für die Darstellung unterschiedlicher Lösungen zur CDC-Datenreplikation nach BigQuery verwenden Sie ein Paar aus Haupt- und Deltatabelle, dem Beispieldaten wie aus den folgenden einfachen Beispieltabellen hinzugefügt werden.

Für eine komplexere Einrichtung als in diesem Dokument beschrieben, können Sie die Demo zur Einbindung in BigQuery mithilfe von CDC verwenden. In der Demo ist das Einfügen von Daten in die Tabellen automatisiert. Außerdem enthält es Skripts zur Überwachung des Replikationsvorgangs. Zum Ausführen der Demo folgen Sie der Anleitung in der README-Datei im Stammverzeichnis des GitHub-Repositorys, das Sie im Abschnitt Vorbereitung dieses Dokuments geklont haben.

Für die Beispieldaten wird ein einfaches Datenmodell verwendet: eine Websitzung mit einer erforderlichen systemgenerierten Sitzungs-ID und einem optionalen Nutzernamen. Beim Start der Sitzung ist der Nutzername null. Nach Anmeldung des Nutzers wird dessen Nutzername eingefügt.

Zum Laden von Daten aus den BigQuery-Umgebungsskripts in die Haupttabelle führen Sie einen Befehl wie den folgenden aus:

bq load cdc_tutorial.session_main init.csv

Um den Inhalt der Haupttabelle abzurufen, führen Sie eine Abfrage wie die folgende aus:

bq query "select * from cdc_tutorial.session_main limit 1000"

Die Ausgabe sieht in etwa so aus:

+-----+----------+-----------+
| id  | username | change_id |
+-----+----------+-----------+
| 100 | NULL     |         1 |
| 101 | Sam      |         2 |
| 102 | Jamie    |         3 |
+-----+----------+-----------+

Als Nächstes laden Sie den ersten Batch an CDC-Änderungen in die Deltatabelle. Um den ersten Batch an CDC-Änderungen aus den BigQuery-Umgebungsskripts in die Deltatabelle zu laden, führen Sie einen Befehl wie den folgenden aus:

bq load cdc_tutorial.session_delta first-batch.csv

Um den Inhalt der Deltatabelle abzurufen, führen Sie eine Abfrage wie die folgende aus:

bq query "select * from cdc_tutorial.session_delta limit 1000"

Die Ausgabe sieht in etwa so aus:

+-----+----------+-----------+-------------+
| id  | username | change_id | change_type |
+-----+----------+-----------+-------------+
| 100 | Cory     |         4 | U           |
| 101 | Sam      |         5 | D           |
| 103 | NULL     |         6 | I           |
| 104 | Jamie    |         7 | I           |
+-----+----------+-----------+-------------+

In der obigen Ausgabe ist der Wert für change_id die eindeutige ID einer Änderung der Tabellenzeile. Die Werte in der Spalte change_type stehen für Folgendes:

• U: Vorgänge aktualisieren
• D: Vorgänge löschen
• I: Vorgänge einfügen

Die Haupttabelle enthält Informationen zu den Sitzungen 100, 101 und 102. Die Deltatabelle beinhaltet die folgenden Änderungen:

• Sitzung 100 wurde mit dem Nutzernamen „Cryry“ aktualisiert.
• Sitzung 101 wurde gelöscht.
• Es wurden die neuen Sitzungen 103 und 104 erstellt.

Der aktuelle Status der Sitzungen im Quellsystem lautet so:

+-----+----------+
| id  | username |
+-----+----------+
| 100 | Cory     |
| 102 | Jamie    |
| 103 | NULL     |
| 104 | Jamie    |
+-----+----------+

Der aktuelle Status zwar als Tabelle angezeigt, diese Tabelle ist aber nicht als Datei vorhanden. Die Tabelle ist eine Kombination der Haupt- und der Deltatabelle.

Daten abfragen

Zum Ermitteln des Gesamtstatus der Sitzungen gibt es mehrere Möglichkeiten. In den folgenden Abschnitten werden die Vor- und Nachteile der einzelnen Ansätze erläutert.

Ansatz der sofortigen Konsistenz

Wenn die sofortige Datenkonsistenz Ihr primäres Ziel ist und sich die Quelldaten häufig ändern, können Sie eine einzelne Abfrage verwenden, die die Haupt- und Deltatabellen zusammenführt und die neueste Zeile auswählt, also die Zeile mit dem neuesten Zeitstempel oder dem höchsten Zahlenwert.

Zum Erstellen einer BigQuery-Ansicht, in der die Haupt- und Deltatabelle verknüpft und die neueste Zeile ermittelt werden, führen Sie einen bq-Tool-Befehl wie den folgenden aus:

bq mk --view \
"SELECT * EXCEPT(change_type, row_num)
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY change_id DESC) AS row_num
  FROM (
    SELECT * EXCEPT(change_type), change_type
    FROM \`$(gcloud config get-value project).cdc_tutorial.session_delta\` UNION ALL
    SELECT *, 'I'
    FROM \`$(gcloud config get-value project).cdc_tutorial.session_main\`))
WHERE
  row_num = 1
  AND change_type <> 'D'" \
 cdc_tutorial.session_latest_v

Mit der SQL-Anweisung in der vorherigen BigQuery-Ansicht werden folgende Schritte ausgeführt:

• Die innerste UNION ALL-Anweisung generiert die Zeilen sowohl aus der Haupt- als auch aus der Deltatabelle:
  • SELECT * EXCEPT(change_type), change_type FROM session_delta führt dazu, dass die Spalte change_type zur letzten Spalte in der Liste wird.
  • SELECT *, 'I' FROM session_main wählt die Zeile aus der Haupttabelle so aus, als ob es eine Einfügezeile wäre.
  • Mit dem Operator * kann das Beispiel einfach gehalten werden. Wenn weitere Spalten oder eine andere Spaltenreihenfolge vorhanden sind, ersetzen Sie die Verknüpfung durch explizite Spaltenlisten.
• SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY change_id DESC) AS row_num verwendet eine Fensterfunktion in BigQuery, um jeder Zeilengruppe mit dem gleichen Wert für id, der durch PARTITION BY definiert wird, fortlaufende Zeilennummern beginnend mit 1 zuzuweisen. Die Zeilen sind nach change_id in absteigender Reihenfolge innerhalb jeder Gruppe sortiert. Da sich der Wert für change_id auf jeden Fall erhöht, entspricht der neuesten Änderung eine Spalte row_num mit dem Wert 1.
• Mit WHERE row_num = 1 AND change_type <> 'D' wird nur die neueste Zeile aus jeder Gruppe ausgewählt. Dies ist eine gängige Deduplizierungsmethode in BigQuery. Mit dieser Klausel wird auch die Zeile aus dem Ergebnis entfernt, wenn der zugehörige Änderungstyp „Löschen“ lautet.
• Mit der obersten SELECT * EXCEPT(change_type, row_num)-Anweisung werden die zusätzlichen Spalten entfernt, die für die Verarbeitung eingefügt wurden, aber ansonsten nicht relevant sind.

Im vorherigen Beispiel werden die Änderungstypen „Einfügen“ und „Aktualisieren“ nicht in der Ansicht verwendet, da mit dem Verweis auf den höchsten change_id-Wert die ursprüngliche Einfüge-Anweisung oder die letzte Aktualisierung ausgewählt wird. In diesem Fall enthält jede Zeile die vollständigen Daten für alle Spalten.

Nachdem Sie die Ansicht erstellt haben, können Sie Abfragen dafür ausführen. Um die neuesten Änderungen abzurufen, führen Sie eine Abfrage wie die folgende aus:

bq query 'select * from cdc_tutorial.session_latest_v order by id limit 10'

Die Ausgabe sieht in etwa so aus:

+-----+----------+-----------+
| id  | username | change_id |
+-----+----------+-----------+
| 100 | Cory     |         4 |
| 102 | Jamie    |         3 |
| 103 | NULL     |         6 |
| 104 | Jamie    |         7 |
+-----+----------+-----------+

Wenn Sie die Ansicht abfragen, sind die Daten in der Deltatabelle sofort sichtbar, wenn Sie die Daten in der Deltatabelle mit einer DML-Anweisung (Data Manipulation Language) aktualisiert haben, oder zeitnah sichtbar, wenn Sie Daten darin gestreamt haben.

Kostenoptimierter Ansatz

Der Ansatz zur sofortigen Konsistenz ist sehr einfach in der Anwendung. Er kann jedoch ineffizient werden, da damit BigQuery alle Verlaufsdaten lesen, nach Primärschlüssel sortieren und die anderen Vorgänge in der Abfrage ausführen muss, um die Ansicht zu implementieren. Wenn Sie den Sitzungsstatus häufig abfragen, reduziert der Ansatz der sofortigen Konsistenz eventuell die Leistung und erhöht die Kosten für die Speicherung und Verarbeitung von Daten in BigQuery.

Um die Kosten zu minimieren, können Sie Änderungen der Deltatabelle mit der Haupttabelle zusammenführen und zusammengeführte Zeilen regelmäßig dauerhaft aus der Deltatabelle entfernen. Für das Zusammenführen und Löschen entstehen zusätzliche Kosten. Wenn Sie die Haupttabelle aber häufig abfragen, sind diese Kosten im Vergleich zu den Kosten für die kontinuierliche Suche nach dem neuesten Eintrag für einen Schlüssel in der Deltatabelle vernachlässigbar.

Zum Zusammenführen von Daten aus der Deltatabelle in die Haupttabelle führen Sie eine MERGE-Anweisung wie die folgende aus:

bq query \
'MERGE `cdc_tutorial.session_main` m
USING
  (
  SELECT * EXCEPT(row_num)
  FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY delta.id ORDER BY delta.change_id DESC) AS row_num
    FROM `cdc_tutorial.session_delta` delta )
  WHERE row_num = 1) d
ON  m.id = d.id
  WHEN NOT MATCHED
AND change_type IN ("I", "U") THEN
INSERT (id, username, change_id)
VALUES (d.id, d.username, d.change_id)
  WHEN MATCHED
  AND d.change_type = "D" THEN
DELETE
  WHEN MATCHED
  AND d.change_type = "U"
  AND (m.change_id < d.change_id) THEN
UPDATE
SET username = d.username, change_id = d.change_id'

Die obige MERGE-Anweisung hat sich auf vier Zeilen ausgewirkt. Die Haupttabelle enthält den aktuellen Status der Sitzungen. Um die Haupttabelle in dieser Ansicht abzufragen, führen Sie eine Abfrage wie die folgende aus:

  bq query 'select * from cdc_tutorial.session_main order by id limit 10'

Die Ausgabe sieht in etwa so aus:

+-----+----------+-----------+
| id  | username | change_id |
+-----+----------+-----------+
| 100 | Cory     |         4 |
| 102 | Jamie    |         3 |
| 103 | NULL     |         6 |
| 104 | Jamie    |         7 |
+-----+----------+-----------+

Die Daten in der Haupttabelle geben den Status der letzten Sitzungen wieder.

Die beste Methode, um Daten häufig und einheitlich zusammenzuführen, ist die Verwendung einer MERGE-Anweisung, mit der Sie mehrere INSERT-, UPDATE- und DELETE-Anweisungen in einem einzigen Vorgang kombiniert ausführen können. Im Folgenden werden einige Details der obigen MERGE-Anweisung erläutert:

• Die Tabelle session_main wird mit der Datenquelle zusammengeführt, die in der USING-Klausel angegeben ist. In diesem Fall ist es eine Unterabfrage.
• Die Unterabfrage verwendet die gleiche Methode wie die Ansicht im Ansatz der sofortigen Konsistenz. Es wird dabei die neueste Zeile in der Eintragsgruppe ausgewählt, die den gleichen id-Wert hat. Dieser ist eine Kombination aus ROW_NUMBER() OVER(PARTITION BY id ORDER BY change_id DESC) row_num und WHERE row_num = 1.
• Die Zusammenführung wird für die id-Spalten beider Tabellen ausgeführt. Dies ist der Primärschlüssel.
• Die Klausel WHEN NOT MATCHED prüft auf Übereinstimmung. Wenn keine Übereinstimmung vorhanden ist, prüft die Abfrage, ob der neueste Eintrag eine Einfügung oder eine Aktualisierung ist, und fügt dann den Eintrag ein.
  • Bei übereinstimmendem Eintrag und mit dem Änderungstyp „Löschen“ wird der Eintrag in der Haupttabelle gelöscht.
  • Wenn der Eintrag übereinstimmt, der Änderungstyp „Aktualisieren“ ist und der change_id-Wert der Deltatabelle größer ist als der change_id-Wert des Haupteintrags, werden die Daten aktualisiert, einschließlich des neuesten change_id-Werts.

Die obige MERGE-Anweisung funktioniert für alle Kombinationen der folgenden Änderungen ordnungsgemäß:

• Mehrere Aktualisierungszeilen für den gleichen Primärschlüssel: Es wird nur die neueste Aktualisierung angewendet.

• Nicht übereinstimmende Aktualisierungen in der Haupttabelle: Wenn die Haupttabelle keinen Eintrag unter dem Primärschlüssel enthält, wird ein neuer Eintrag eingefügt.

  Bei diesem Ansatz wird die Extraktion der Haupttabelle übersprungen und mit der Deltatabelle gestartet. Die Haupttabelle wird automatisch ausgefüllt.

• Fügen Sie Zeilen in den nicht verarbeiteten Delta-Batch ein und aktualisieren Sie diese. Die Zeile mit der neuesten Aktualisierung wird verwendet und ein neuer Eintrag in die Haupttabelle eingefügt.

• Fügen Sie Zeilen in den nicht verarbeiteten Batch ein und löschen Sie diese. Der Eintrag wird nicht eingefügt.

Die obige MERGE-Anweisung ist idempotent, d. h., die mehrfache Ausführung führt zum identischen Status der Haupttabelle ohne Nebeneffekte. Wenn Sie die MERGE-Anweisung noch einmal ausführen, ohne neue Zeilen zur Deltatabelle hinzuzufügen, sieht die Ausgabe in etwa so aus:

Number of affected rows: 0

Sie können die MERGE-Anweisung regelmäßig ausführen lassen, um die Haupttabelle nach jeder Zusammenführung auf den neuesten Stand zu bringen. Die Aktualität der Daten in der Haupttabelle hängt von der Häufigkeit der Zusammenführung ab. Informationen zur automatischen Ausführung der MERGE-Anweisung finden Sie im Abschnitt „Zusammenführung planen“ der Demo-README-Datei, die Sie zuvor heruntergeladen haben.

Hybridansatz

Der Ansatz der sofortigen Konsistenz und der kostenoptimierte Ansatz schließen sich nicht gegenseitig aus. Wenn Sie Abfragen für die Ansicht session_latest_v und die Tabelle session_main ausführen, geben diese die gleichen Ergebnisse zurück. Sie können den Ansatz auswählen, der Ihren Anforderungen und Ihrem Budget am besten entspricht: höhere Kosten und nahezu sofortige Konsistenz oder geringere Kosten, aber möglicherweise veraltete Daten. In den folgenden Abschnitten wird gezeigt, wie Sie die Ansätze und mögliche Alternativen vergleichen.

Ansätze vergleichen

In diesem Abschnitt wird gezeigt, wie Sie Ansätze vergleichen und dafür die Kosten sowie die Leistung der einzelnen Lösungen und das jeweilige Verhältnis von akzeptabler Datenlatenz und Kosten für das Zusammenführen berücksichtigen.

Kosten für Abfragen

Das folgende Beispiel bietet eine Bewertung von Kosten und Leistung jeder Lösung. Es enthält eine Analyse von etwa 500.000 Sitzungen, die von der Demo zur Einbindung in BigQuery mithilfe von CDC generiert wurden. Das Sitzungsmodell in der Demo ist etwas komplexer als das zuvor in diesem Dokument beschriebene Modell und wird in einem anderen Dataset bereitgestellt. Die Konzepte sind jedoch identisch.

Mithilfe einer einfachen Aggregationsabfrage können Sie die Kosten für Abfragen vergleichen. Die folgende Beispielabfrage testet den Ansatz der sofortigen Konsistenz für die Ansicht, die die Deltadaten mit der Haupttabelle kombiniert:

SELECT status, count(*) FROM `cdc_demo.session_latest_v`
GROUP BY status ORDER BY status

Die Abfrage führt zu folgenden Kosten:

Slot time consumed: 15.115 sec, Bytes shuffled 80.66 MB

Mit der folgenden Beispielabfrage wird der kostenoptimierte Ansatz für die Haupttabelle getestet:

SELECT status, count(*) FROM `cdc_demo.session_main`
GROUP BY status ORDER BY status

Die Abfrage führt zu folgenden, niedrigeren Kosten:

Slot time consumed: 1.118 sec, Bytes shuffled 609 B

Die genutzte Slot-Zeit kann variieren, wenn Sie die gleichen Abfragen mehrmals ausführen. Die Durchschnittswerte sind jedoch weitgehend einheitlich. Der Wert von Bytes shuffled bleibt bei den verschiedenen Ausführungen gleich.

Die Ergebnisse des Leistungstests variieren je nach Art der Abfragen und des Tabellenlayouts. In der vorherigen Demo werden weder Daten-Clustering noch Partitionierung genutzt.

Datenlatenz

Beim kostenoptimierten Ansatz wird die Datenlatenz durch Folgendes beeinflusst:

• Verzögerung des Triggers für die Datenreplikation. Dies ist die Zeitdauer zwischen dem Zeitpunkt, an dem die Daten beim Quellereignis konstant sind, und dem Zeitpunkt, an dem das Replikationssystem den Replikationsprozess auslöst.
• Zeitdauer für das Einfügen der Daten in BigQuery (variiert je nach Replikationslösung).
• Zeitdauer bis zur Anzeige der Daten des BigQuery-Streamingpuffers in der Deltatabelle. Wenn Sie Streaming-Insert-Anweisungen verwenden, beträgt diese in der Regel nur einige Sekunden.
• Verzögerung zwischen einzelnen Ausführungen der Zusammenführung.
• Zeitdauer der Zusammenführung.

Beim Ansatz der sofortigen Konsistenz wird die Datenlatenz durch Folgendes beeinflusst:

• Verzögerung des Triggers für die Datenreplikation.
• Zeitdauer für das Einfügen der Daten in BigQuery.
• Zeitdauer bis zur Anzeige der Daten des BigQuery-Streamingpuffers in der Deltatabelle.

Sie können die Verzögerung zwischen den Zusammenführungen je nach dem gewünschten Verhältnis von für die Ausführung anfallenden Kosten und konsistenten Daten selbst konfigurieren. Bei Bedarf haben Sie die Möglichkeit, ein komplexeres Schema zu verwenden, z. B. häufige Zusammenführungen, die während der Geschäftszeiten, und stündliche Zusammenführungen, die außerhalb der Geschäftszeiten ausgeführt werden.

Alternativen berücksichtigen

Der Ansatz der sofortigen Konsistenz und der kostenoptimierte Ansatz sind die gängigsten CDC-Ansätze für die Einbindung verschiedener Datenquellen in BigQuery. In diesem Abschnitt werden einfachere und kostengünstigere Möglichkeiten für die Dateneinbindung beschrieben.

Deltatabelle als „Single Source Of Truth“

Wenn die Deltatabelle den gesamten Änderungsverlauf enthält, können Sie eine Ansicht nur für die Deltatabelle erstellen und müssen nicht die Haupttabelle verwenden. Die Verwendung einer Deltatabelle als „Single Source Of Truth“ ist ein Beispiel für eine Ereignisdatenbank. Dieser Ansatz bietet eine sofortige Konsistenz bei niedrigen Kosten und geringen Leistungseinbußen. Der Ansatz ist hilfreich, wenn Sie eine selten geänderte Dimensionstabelle mit einer nur kleinen Anzahl an Einträgen nutzen.

Vollständiger Datendump ohne CDC

Wenn Sie mit Tabellen handhabbarer Größe (z. B. mit weniger als 1 GB) arbeiten, kann es einfacher sein, einen vollständigen Datendump mit folgenden Schritten auszuführen:

1. Importieren Sie den ersten Datendump in eine Tabelle mit einem eindeutigen Namen.
2. Erstellen Sie eine Ansicht, die nur auf die neue Tabelle verweist.
3. Führen Sie Abfragen für die Ansicht aus, nicht für die zugrunde liegende Tabelle.
4. Importieren Sie den nächsten Datendump in eine andere Tabelle.
5. Ändern Sie die Ansicht, damit sie auf die neu hochgeladenen Daten verweist.
6. Löschen Sie optional die ursprüngliche Tabelle.
7. Wiederholen Sie regelmäßig die vorherigen Schritte zum Importieren, Neuerstellen und Löschen.

Änderungsverlauf in der Haupttabelle beibehalten

Beim kostenoptimierten Ansatz wird kein Änderungsverlauf gespeichert. Die letzte Änderung überschreibt die vorherigen Daten. Wenn Sie den Verlauf beibehalten möchten, können Sie ihn mit einem Array der Änderungen speichern. Dabei müssen Sie darauf achten, dass das Limit für die maximale Zeilengröße nicht überschritten wird. Wenn Sie den Änderungsverlauf in der Haupttabelle beibehalten, ist die Zusammenführungs-DML-Datei komplexer. Mit einem einzelnen MERGE-Vorgang können mehrere Zeilen der Deltatabelle in einer einzigen Zeile der Haupttabelle zusammengeführt werden.

Föderierte Datenquellen verwenden

In einigen Fällen können Sie Daten in eine andere Datenquelle als BigQuery replizieren und diese über eine föderierte Abfrage verfügbar machen. BigQuery unterstützt eine Reihe von externen Datenquellen. Wenn Sie beispielsweise ein Sternschema aus einer MySQL-Datenbank replizieren, können Sie selten geänderte Dimensionen mithilfe der nativen MySQL-Replikation in eine schreibgeschützte Version von MySQL replizieren. Bei dieser Methode replizieren Sie nur die sich häufig ändernde Faktentabelle nach BigQuery. Wenn Sie föderierte Datenquellen verwenden, müssen Sie die Einschränkungen für die Abfrage von föderierten Quellen beachten.

Leistung weiter verbessern

In diesem Abschnitt wird erläutert, wie Sie die Leistung weiter steigern können und dafür Tabellen clustern und partitionieren sowie zusammengeführte Daten bereinigen.

BigQuery-Tabellen clustern und partitionieren

Bei einem häufig abgefragten Dataset können Sie die Nutzung jeder Tabelle analysieren und dann das Tabellendesign durch Clustern und Partitionieren optimieren. Das Clustern der Haupt- oder Deltatabelle oder von beiden nach Primärschlüssel kann zu einer besseren Leistung verglichen mit den anderen Ansätzen führen. Führen Sie zur Prüfung der Leistung testweise Abfragen für ein Dataset mit mindestens 10 GB aus.

Zusammengeführte Daten bereinigen

Die Deltatabelle wird mit der Zeit immer größer. Außerdem verbraucht jede Zusammenführungsanfrage Ressourcen durch Lesen einer Anzahl an Zeilen, die Sie für das letztliche Ergebnis nicht benötigen. Wenn Sie den neuesten Status nur mit den Deltatabellendaten berechnen, können Sie durch das Bereinigen von zusammengeführten Einträgen die Kosten für die Zusammenführung reduzieren und auch die Gesamtkosten senken, da die in BigQuery gespeicherte Datenmenge verringert wird.

So können Sie zusammengeführte Daten bereinigen:

• Fragen Sie die Haupttabelle regelmäßig nach dem höchsten change_id-Wert ab und löschen Sie alle Deltaeinträge, deren change_id-Wert unter diesem Höchstwert liegt. Wenn Sie Insert-Anweisungen in die Deltatabelle streamen, werden möglicherweise die Einfügungen für einen bestimmten Zeitraum nicht gelöscht.
• Verwenden Sie eine aufnahmebasierte Partitionierung der Deltatabellen und führen Sie täglich ein Skript aus, um die bereits verarbeiteten Partitionen zu löschen. Wenn eine detailliertere BigQuery-Partitionierung verfügbar ist, können Sie die Häufigkeit der Bereinigung erhöhen. Informationen zur Implementierung finden Sie im Abschnitt „Verarbeitete Daten bereinigen“ der Demo-README-Datei, die Sie zuvor heruntergeladen haben.

Zusammenfassung

Für die Auswahl des oder der richtigen Ansätze sind Ihre jeweiligen Anwendungsfälle maßgeblich. Sie können die Anforderungen für die Datenreplikation unter Umständen mit den vorhandenen Methoden der Datenbankmigration erfüllen. Bei komplexen Anforderungen, z. B. wenn es um einen Echtzeit-Anwendungsfall geht und der Rest des Datenzugriffsmusters kostenoptimiert werden soll, müssen Sie möglicherweise die Häufigkeit der benutzerdefinierten Datenbankmigration anhand anderer Produkte oder Open-Source-Lösungen entsprechend konfigurieren. Die in diesem Dokument erläuterten Ansätze und Methoden sollen Ihnen dabei helfen, eine solche Lösung erfolgreich zu implementieren.

Nächste Schritte

• Datenpipelines migrieren
• Mehr erfahren zum Entwerfen der ETL-Architektur für ein cloudnatives Data Warehouse in Google Cloud und zum Ausführen von ETL von einer relationalen Datenbank nach BigQuery mithilfe von Dataflow
• Referenzarchitekturen, Diagramme und Best Practices zu Google Cloud kennenlernen. Weitere Informationen zu Cloud Architecture Center