Vorlage „SQL Server für BigQuery“

Die Vorlage „SQL Server für BigQuery“ ist eine Batchpipeline, die Daten aus einer SQL Server-Tabelle in eine vorhandene BigQuery-Tabelle kopiert. Diese Pipeline verwendet JDBC, um eine Verbindung zu SQL Server herzustellen. Als zusätzliche Schutzmaßnahme können Sie auch einen Cloud KMS-Schlüssel zusammen mit einem Base64-codierten Nutzernamen, Passwort und Verbindungsstring-Parametern übergeben, die mit dem Cloud KMS-Schlüssel verschlüsselt sind. Weitere Informationen zum Verschlüsseln von Nutzernamen, Passwörtern und Verbindungsstring-Parametern finden Sie unter Cloud KMS API-Verschlüsselungsendpunkt.

Pipelineanforderungen

  • Die BigQuery-Tabelle muss vor der Pipelineausführung vorhanden sein.
  • Die BigQuery-Tabelle muss ein kompatibles Schema haben.
  • Die relationale Datenbank muss über das Subnetz zugänglich sein, in dem Dataflow ausgeführt wird.

Vorlagenparameter

Erforderliche Parameter

  • driverJars : Die durch kommagetrennte Liste der JAR-Dateien des Treibers. (Beispiel: gs://your-bucket/driver_jar1.jar,gs://your-bucket/driver_jar2.jar).
  • driverClassName : Der Name der JDBC-Treiberklasse. (Beispiel: com.mysql.jdbc.Driver).
  • connectionURL : Der URL-String für die JDBC-Verbindung. Beispiel: jdbc:mysql://some-host:3306/sampledb. Kann als String übergeben werden, der Base64-codiert und dann mit einem Cloud KMS-Schlüssel verschlüsselt wird. Beachten Sie den Unterschied zwischen einem Oracle-Nicht-RAC-Datenbankverbindungsstring (jdbc:oracle:thin:@some-host:<port>:<sid>) und einem Oracle-RAC-Datenbankverbindungsstring (jdbc:oracle:thin:@//some-host[:<port>]/<service_name>). (Beispiel: jdbc:mysql://some-host:3306/sampledb).
  • outputTable : Der Speicherort der BigQuery-Tabelle, in die die Ausgabe geschrieben werden soll. Der Name muss das Format <project>:<dataset>.<table_name> haben. Das Schema der Tabelle muss mit Eingabeobjekten übereinstimmen. (Beispiel:
  • bigQueryLoadingTemporaryDirectory : Das temporäre Verzeichnis für den BigQuery-Ladevorgang (Beispiel: gs://your-bucket/your-files/temp_dir).

Optionale Parameter

  • connectionProperties : Attributstring für die JDBC-Verbindung. Format des Strings muss [propertyName=property;]* sein. Beispiel: unicode=true;characterEncoding=UTF-8).
  • Nutzername : Der Nutzername, der für die JDBC-Verbindung verwendet werden soll. Kann als Base64-codierter String übergeben werden, der mit einem Cloud KMS-Schlüssel verschlüsselt ist.
  • Passwort : Das Passwort für die JDBC-Verbindung. Kann als Base64-codierter String übergeben werden, der mit einem Cloud KMS-Schlüssel verschlüsselt ist.
  • Abfrage : Die Abfrage, die in der Quelle zur Extraktion der Daten ausgeführt wird. Es müssen entweder die Abfrage ODER sowohl die Tabelle UND PartitionColumn angegeben werden. (Beispiel: Wählen Sie "*" aus sampledb.sample_table aus).
  • KMSEncryptionKey : Cloud KMS-Verschlüsselungsschlüssel zur Entschlüsselung des Nutzernamens, Passworts und Verbindungsstrings. Wenn der Cloud KMS-Schlüssel übergeben wird, müssen der Nutzername, das Passwort und der Verbindungsstring verschlüsselt übergeben werden. (Beispiel: projects/your-project/locations/global/keyRings/your-keyring/cryptoKeys/your-key).
  • useColumnAlias : Wenn diese Option aktiviert (true) ist, berücksichtigt die Pipeline den Spaltenalias ("AS") anstelle des Spaltennamens, um die Zeilen BigQuery zuzuordnen. Die Standardeinstellung ist "false".
  • isTruncate : Wenn diese Option aktiviert (auf "true") gesetzt ist, wird die Pipeline vor dem Laden von Daten in BigQuery gekürzt. Die Standardeinstellung ist "false". Damit werden nur Daten angefügt.
  • partitionColumn : Wenn dieser Parameter (zusammen mit table) angegeben wird, liest JdbcIO die Tabelle parallel, indem mehrere Instanzen der Abfrage in derselben Tabelle (Unterabfrage) mithilfe von Bereichen ausgeführt werden. Derzeit werden nur lange Partitionsspalten unterstützt. Es müssen entweder die Abfrage ODER sowohl die Tabelle UND PartitionColumn angegeben werden.
  • Tabelle : Tabelle, aus der mithilfe von Partitionen gelesen werden soll. Es müssen entweder die Abfrage ODER sowohl die Tabelle UND PartitionColumn angegeben werden. Dieser Parameter akzeptiert auch eine Unterabfrage in Klammern. (Beispiel: (ID auswählen, Name von Person) als subq).
  • numPartitions : Die Anzahl der Partitionen. Damit bilden zusammen mit der Unter- und Obergrenze die Partitionsschritte für generierte WHERE-Klauselausdrücke, die zum gleichmäßigen Aufteilen der Partitionsspalte verwendet werden. Wenn die Eingabe kleiner als ist, wird die Zahl auf gesetzt.
  • lowerBound : Untergrenze, die im Partitionsschema verwendet wird. Wenn nicht angegeben, wird sie automatisch von Beam abgeleitet (für die unterstützten Typen).
  • upperBound : Obergrenze, die im Partitionsschema verwendet wird. Wenn nicht angegeben, wird sie automatisch von Beam abgeleitet (für die unterstützten Typen).
  • fetchSize : Die Anzahl der Zeilen, die jeweils aus der Datenbank abgerufen werden sollen. Wird nicht für partitionierte Lesevorgänge verwendet. Die Standardeinstellung ist 50000.
  • createDisposition : BigQuery CreateDisposition. Beispiel: CREATE_IF_NEEDED, CREATE_NEVER. Die Standardeinstellung ist CREATE_NEVER.
  • bigQuerySchemaPath : Der Cloud Storage-Pfad für das BigQuery-JSON-Schema. Wenn createDisposition auf CREATE_IF_NEEDED gesetzt ist, muss dieser Parameter angegeben werden. (Beispiel: gs://your-bucket/your-schema.json).
  • disabledAlgorithms : Durch Kommas getrennte Algorithmen zum Deaktivieren. Wenn dieser Wert auf none gesetzt ist, wird kein Algorithmus deaktiviert. Seien Sie bei der Verwendung vorsichtig, da die standardmäßig deaktivierten Algorithmen entweder Sicherheitslücken oder Leistungsprobleme aufweisen. (Beispiel: SSLv3, RC4).
  • extraFilesToStage : Durch Kommas getrennte Cloud Storage-Pfade oder Secret Manager-Secrets für Dateien, die im Worker bereitgestellt werden sollen. Diese Dateien werden im Verzeichnis /extra_files in jedem Worker gespeichert (Beispiel: gs://your-bucket/file.txt,projects/project-id/secrets/secret-id/versions/version-id).
  • useStorageWriteApi : Wenn diese Option aktiviert (auf "true") gesetzt ist, verwendet die Pipeline beim Schreiben der Daten in BigQuery die Storage Write API (siehe https://cloud.google.com/blog/products/data-analytics/streaming-data-into-bigquery-using-storage-write-api). Die Standardeinstellung ist "false".
  • useStorageWriteApiAtLeastOnce : Dieser Parameter wird nur wirksam, wenn "BigQuery Storage Write API verwenden" aktiviert ist. Wenn diese Option aktiviert ist, wird für die Storage Write API die "Mindestens einmal"-Semantik verwendet. Andernfalls wird die "Genau einmal"-Semantik verwendet. Die Standardeinstellung ist "false".

Führen Sie die Vorlage aus.

Console

  1. Rufen Sie die Dataflow-Seite Job aus Vorlage erstellen auf.
    2. Zur Seite "Job aus Vorlage erstellen“
  2. Geben Sie im Feld Jobname einen eindeutigen Jobnamen ein.
  3. Optional: Wählen Sie für Regionaler Endpunkt einen Wert aus dem Drop-down-Menü aus. Die Standardregion ist us-central1.

    Eine Liste der Regionen, in denen Sie einen Dataflow-Job ausführen können, finden Sie unter Dataflow-Standorte.

  4. Wählen Sie im Drop-down-Menü Dataflow-Vorlage die Option the SQL Server to BigQuery templateaus.
  5. Geben Sie Ihre Parameterwerte in die Parameterfelder ein.
  6. Klicken Sie auf Job ausführen.

gcloud

Führen Sie die Vorlage in der Shell oder im Terminal aus:

gcloud dataflow flex-template run JOB_NAME \
    --project=PROJECT_ID \
    --region=REGION_NAME \
    --template-file-gcs-location=gs://dataflow-templates-REGION_NAME/VERSION/flex/SQLServer_to_BigQuery \
    --parameters \
connectionURL=JDBC_CONNECTION_URL,\
query=SOURCE_SQL_QUERY,\
outputTable=PROJECT_ID:DATASET.TABLE_NAME,
bigQueryLoadingTemporaryDirectory=PATH_TO_TEMP_DIR_ON_GCS,\
connectionProperties=CONNECTION_PROPERTIES,\
username=CONNECTION_USERNAME,\
password=CONNECTION_PASSWORD,\
KMSEncryptionKey=KMS_ENCRYPTION_KEY

Ersetzen Sie Folgendes:

  • JOB_NAME: ein eindeutiger Jobname Ihrer Wahl
  • VERSION: Die Version der Vorlage, die Sie verwenden möchten

    Sie können die folgenden Werte verwenden:

    • latest zur Verwendung der neuesten Version der Vorlage, die im nicht datierten übergeordneten Ordner im Bucket verfügbar ist: gs://dataflow-templates-REGION_NAME/latest/
    • Den Versionsnamen wie 2023-09-12-00_RC00, um eine bestimmte Version der Vorlage zu verwenden. Diese ist verschachtelt im jeweiligen datierten übergeordneten Ordner im Bucket enthalten: gs://dataflow-templates-REGION_NAME/.
  • REGION_NAME: die Region, in der Sie Ihren Dataflow-Job bereitstellen möchten, z. B. us-central1
  • JDBC_CONNECTION_URL: Die JDBC-Verbindungs-URL
  • SOURCE_SQL_QUERY: die SQL-Abfrage, die in der Quelldatenbank ausgeführt werden soll
  • DATASET: Ihr BigQuery-Dataset
  • TABLE_NAME: Ihr BigQuery-Tabellenname
  • PATH_TO_TEMP_DIR_ON_GCS: Der Cloud Storage-Pfad zum temporären Verzeichnis
  • CONNECTION_PROPERTIES: die JDBC-Verbindungsattribute, falls erforderlich
  • CONNECTION_USERNAME: Der Nutzername der JDBC-Verbindung
  • CONNECTION_PASSWORD: Das JDBC-Verbindungspasswort
  • KMS_ENCRYPTION_KEY: der Cloud KMS-Verschlüsselungsschlüssel

API

Senden Sie eine HTTP-POST-Anfrage, um die Vorlage mithilfe der REST API auszuführen. Weitere Informationen zur API und ihren Autorisierungsbereichen finden Sie unter projects.templates.launch.

POST https://dataflow.googleapis.com/v1b3/projects/PROJECT_ID/locations/LOCATION/flexTemplates:launch
{
  "launchParameter": {
    "jobName": "JOB_NAME",
    "containerSpecGcsPath": "gs://dataflow-templates-LOCATION/VERSION/flex/SQLServer_to_BigQuery"
    "parameters": {
      "connectionURL": "JDBC_CONNECTION_URL",
      "query": "SOURCE_SQL_QUERY",
      "outputTable": "PROJECT_ID:DATASET.TABLE_NAME",
      "bigQueryLoadingTemporaryDirectory": "PATH_TO_TEMP_DIR_ON_GCS",
      "connectionProperties": "CONNECTION_PROPERTIES",
      "username": "CONNECTION_USERNAME",
      "password": "CONNECTION_PASSWORD",
      "KMSEncryptionKey":"KMS_ENCRYPTION_KEY"
    },
    "environment": { "zone": "us-central1-f" }
  }
}

Ersetzen Sie Folgendes:

  • PROJECT_ID: die ID des Google Cloud-Projekts, in dem Sie den Dataflow-Job ausführen möchten
  • JOB_NAME: ein eindeutiger Jobname Ihrer Wahl
  • VERSION: Die Version der Vorlage, die Sie verwenden möchten

    Sie können die folgenden Werte verwenden:

    • latest zur Verwendung der neuesten Version der Vorlage, die im nicht datierten übergeordneten Ordner im Bucket verfügbar ist: gs://dataflow-templates-REGION_NAME/latest/
    • Den Versionsnamen wie 2023-09-12-00_RC00, um eine bestimmte Version der Vorlage zu verwenden. Diese ist verschachtelt im jeweiligen datierten übergeordneten Ordner im Bucket enthalten: gs://dataflow-templates-REGION_NAME/.
  • LOCATION: die Region, in der Sie Ihren Dataflow-Job bereitstellen möchten, z. B. us-central1
  • JDBC_CONNECTION_URL: Die JDBC-Verbindungs-URL
  • SOURCE_SQL_QUERY: die SQL-Abfrage, die in der Quelldatenbank ausgeführt werden soll
  • DATASET: Ihr BigQuery-Dataset
  • TABLE_NAME: Ihr BigQuery-Tabellenname
  • PATH_TO_TEMP_DIR_ON_GCS: Der Cloud Storage-Pfad zum temporären Verzeichnis
  • CONNECTION_PROPERTIES: die JDBC-Verbindungsattribute, falls erforderlich
  • CONNECTION_USERNAME: Der Nutzername der JDBC-Verbindung
  • CONNECTION_PASSWORD: Das JDBC-Verbindungspasswort
  • KMS_ENCRYPTION_KEY: der Cloud KMS-Verschlüsselungsschlüssel

Nächste Schritte