Modèle Java Database Connectivity (JDBC) vers BigQuery

Le modèle JDBC vers BigQuery est un pipeline par lots qui copie les données d'une table de base de données relationnelle vers une table BigQuery existante. Ce pipeline utilise JDBC pour se connecter à la base de données relationnelle. Utilisez ce modèle pour copier des données de toute base de données relationnelle contenant les pilotes JDBC disponibles dans BigQuery.

Pour obtenir une couche supplémentaire de protection, vous pouvez transmettre une clé Cloud KMS avec des paramètres de nom d'utilisateur, de mot de passe et de chaîne de connexion encodés en base64 et chiffrés avec la clé Cloud KMS. Pour en savoir plus sur le chiffrement des paramètres de nom d'utilisateur, de mot de passe et de chaîne de connexion, consultez la page Point de terminaison de chiffrement de l'API Cloud KMS.

Conditions requises pour ce pipeline

Les pilotes JDBC de la base de données relationnelle doivent être disponibles.
La table BigQuery doit exister avant l'exécution du pipeline.
La table BigQuery doit avoir un schéma compatible.
La base de données relationnelle doit être accessible à partir du sous-réseau dans lequel Dataflow est exécuté.

Paramètres de modèle

Paramètres obligatoires

driverJars : Liste des fichiers JAR du pilote, séparés par une virgule. (Exemple : gs://your-bucket/driver_jar1.jar,gs://your-bucket/driver_jar2.jar).
driverClassName : Nom de classe du pilote JDBC. (Exemple : com.mysql.jdbc.Driver).
connectionURL : Chaîne d'URL de connexion JDBC. Exemple :jdbc:mysql://some-host:3306/sampledb Peut être transmis en tant que chaîne encodée en base64 et chiffrée avec une clé Cloud KMS. Notez la différence entre une chaîne de connexion à une base de données Oracle non-RAC (jdbc:oracle:thin:@some-host:<port>:<sid>) et une chaîne de connexion à une base de données Oracle RAC (jdbc:oracle:thin:@//some-host[:<port>]/<service_name>). (Exemple : jdbc:mysql://some-host:3306/sampleb).
outputTable : Emplacement de table BigQuery dans lequel écrire la sortie. Le nom doit être au format <project>:<dataset>.<table_name>. Le schéma de la table doit correspondre aux objets d'entrée. (Exemple :
bigQueryLoadingTemporaryDirectory : Répertoire temporaire pour le processus de chargement BigQuery (exemple : gs://your-bucket/your-files/temp_dir).

Paramètres facultatifs

connectionProperties : Chaîne de propriétés à utiliser pour la connexion JDBC. Le format de la chaîne doit être [propertyName=property;]*. (Exemple : unicode=true;characterEncoding=UTF-8).
username : Nom d'utilisateur à utiliser pour la connexion JDBC. Peut être transmis en tant que chaîne encodée en base64 et chiffrée avec une clé Cloud KMS.
password : Mot de passe à utiliser pour la connexion JDBC. Peut être transmis en tant que chaîne encodée en base64 et chiffrée avec une clé Cloud KMS.
query : Requête à exécuter sur la source pour extraire les données. Vous devez spécifier "query" OU spécifier "table" ET "PartitionColumn". (Exemple : select * from sampledb.sample_table).
KMSEncryptionKey : Clé de chiffrement Cloud KMS permettant de déchiffrer le nom d'utilisateur, le mot de passe et la chaîne de connexion. Si la clé Cloud KMS est transmise, le nom d'utilisateur, le mot de passe et la chaîne de connexion doivent tous être transmis de manière chiffrée. (Exemple : projects/your-project/locations/global/keyRings/your-keyring/cryptoKeys/your-key).
useColumnAlias : Si cette option est activée (définie sur "true"), le pipeline considère les alias de colonne ("AS") au lieu du nom de colonne pour mapper les lignes sur BigQuery. Valeur par défaut : "false".
isTruncate : Si cette option est activée (définie sur "true"), le pipeline est tronqué avant de charger les données dans BigQuery. La valeur par défaut est "false", qui permet uniquement d'ajouter des données.
partitionColumn : Si ce paramètre est fourni (avec table), JdbcIO lit la table en parallèle en exécutant plusieurs instances de la requête sur la même table (sous-requête) à l'aide de plages. Actuellement, seules les colonnes de partition Long sont acceptées. Vous devez spécifier "query" OU spécifier "table" ET "PartitionColumn".
table : Table à partir de laquelle lire en utilisant des partitions. Vous devez spécifier "query" OU spécifier "table" ET "PartitionColumn". Ce paramètre accepte également une sous-requête entre parenthèses. (Exemple : (select id, name from Person) as subq).
numPartitions : Nombre de partitions. Ce paramètre, avec les limites inférieure et supérieure, forme des pas de partition pour les expressions de clause WHERE générées, qui sont utilisées pour diviser la colonne de partition de manière uniforme. Lorsque l'entrée est inférieure à 1, le nombre est défini sur 1.
lowerBound : Limite inférieure utilisée dans le schéma de partition. Si aucune valeur n'est fournie, elle est automatiquement déduite par Beam (pour les types compatibles).
upperBound : Limite supérieure utilisée dans le schéma de partition. Si aucune valeur n'est fournie, elle est automatiquement déduite par Beam (pour les types compatibles).
fetchSize : Nombre de lignes à extraire simultanément de la base de données. Non utilisé pour les lectures partitionnées. La valeur par défaut est 50 000.
createDisposition : CreateDisposition BigQuery. Par exemple, CREATE_IF_NEEDED, CREATE_NEVER. La valeur par défaut est CREATE_NEVER.
bigQuerySchemaPath : Chemin d'accès Cloud Storage pour le schéma JSON BigQuery. Si createDisposition est défini sur CREATE_IF_NEEDED, ce paramètre doit être spécifié. (Exemple : gs://your-bucket/your-schema.json).
disabledAlgorithms : Algorithmes à désactiver, séparés par une virgule. Si cette valeur est définie sur none, aucun algorithme n'est désactivé. Soyez prudent, car les algorithmes désactivés par défaut souffrent de failles ou de problèmes de performances connus. (Exemple : SSLv3, RC4).
extraFilesToStage : Chemins d'accès Cloud Storage ou secrets Secret Manager séparés par une virgule afin que les fichiers soient traités dans le nœud de calcul. Ces fichiers seront enregistrés dans le répertoire /extra_files de chaque nœud de calcul (exemple : gs://your-bucket/file.txt,projects/project-id/secrets/secret-id/versions/version-id).
useStorageWriteApi : Si cette option est activée (définie sur "true"), le pipeline utilise l'API Storage Write lors de l'écriture des données dans BigQuery (voir https://cloud.google.com/blog/products/data-analytics/streaming-data-into-bigquery-using-storage-write-api). La valeur par défaut est "false".
useStorageWriteApiAtLeastOnce : Ce paramètre ne prend effet que si l'option "Utiliser l'API BigQuery Storage Write" est activée. Si cette option est activée, la sémantique de type "au moins une fois" est utilisée pour l'API Storage Write. Sinon, la sémantique de type "exactement une fois" est utilisée. La valeur par défaut est "false".

Exécuter le modèle

Console

Accédez à la page Dataflow Créer un job à partir d'un modèle.

Accéder à la page Créer un job à partir d'un modèle

Dans le champ Nom du job, saisissez un nom de job unique.
Facultatif : pour Point de terminaison régional, sélectionnez une valeur dans le menu déroulant. La région par défaut est us-central1.
Pour obtenir la liste des régions dans lesquelles vous pouvez exécuter un job Dataflow, consultez la page Emplacements Dataflow.
Dans le menu déroulant Modèle Dataflow, sélectionnez the JDBC to BigQuery template.
Dans les champs fournis, saisissez vos valeurs de paramètres.
Cliquez sur Run Job (Exécuter la tâche).

gcloud

Dans le shell ou le terminal, exécutez le modèle :

gcloud dataflow flex-template run JOB_NAME \
    --template-file-gcs-location=gs://dataflow-templates-REGION_NAME/VERSION/flex/Jdbc_to_BigQuery_Flex \
    --project=PROJECT_ID \
    --region=REGION_NAME \
    --parameters \
       driverJars=DRIVER_JARS,\
       driverClassName=DRIVER_CLASS_NAME,\
       connectionURL=CONNECTION_URL,\
       outputTable=OUTPUT_TABLE,\
       bigQueryLoadingTemporaryDirectory=BIG_QUERY_LOADING_TEMPORARY_DIRECTORY,\

Remplacez les éléments suivants :

JOB_NAME : nom de job unique de votre choix
VERSION : version du modèle que vous souhaitez utiliser
Vous pouvez utiliser les valeurs suivantes :
- latest pour utiliser la dernière version du modèle, disponible dans le dossier parent non daté du bucket gs://dataflow-templates-REGION_NAME/latest/
- Le nom de la version, par exemple 2023-09-12-00_RC00, pour utiliser une version spécifique du modèle, qui est imbriqué dans le dossier parent daté respectif dans le bucket : gs://dataflow-templates-REGION_NAME/
Attention : La dernière version des modèles peut être mise à jour avec des modifications destructives. Vos environnements de production devraient utiliser des modèles conservés dans le dernier dossier parent daté afin d'empêcher que ces modifications n'affectent vos workflows.
REGION_NAME : région dans laquelle vous souhaitez déployer votre job Dataflow, par exemple us-central1
DRIVER_JARS : chemin(s) d'accès Cloud Storage séparé(s) par des virgules vers le(s) pilote(s) JDBC
DRIVER_CLASS_NAME : nom de la classe du pilote JDBC
CONNECTION_URL : chaîne d'URL de connexion JDBC
OUTPUT_TABLE : table de sortie BigQuery
BIG_QUERY_LOADING_TEMPORARY_DIRECTORY : répertoire temporaire pour le processus de chargement de BigQuery

API

Pour exécuter le modèle à l'aide de l'API REST, envoyez une requête HTTP POST. Pour en savoir plus sur l'API, ses autorisations et leurs champs d'application, consultez la section projects.templates.launch.

POST https://dataflow.googleapis.com/v1b3/projects/PROJECT_ID/locations/LOCATION/flexTemplates:launch
{
   "launchParameter": {
     "jobName": "JOB_NAME",
     "parameters": {
       "driverJars": "DRIVER_JARS",
       "driverClassName": "DRIVER_CLASS_NAME",
       "connectionURL": "CONNECTION_URL",
       "outputTable": "OUTPUT_TABLE",
       "bigQueryLoadingTemporaryDirectory": "BIG_QUERY_LOADING_TEMPORARY_DIRECTORY",
     },
     "containerSpecGcsPath": "gs://dataflow-templates-LOCATION/VERSION/flex/Jdbc_to_BigQuery_Flex",
     "environment": { "maxWorkers": "10" }
  }
}

Remplacez les éléments suivants :

PROJECT_ID : ID du projet Google Cloud dans lequel vous souhaitez exécuter le job Dataflow
JOB_NAME : nom de job unique de votre choix
VERSION : version du modèle que vous souhaitez utiliser
Vous pouvez utiliser les valeurs suivantes :
- latest pour utiliser la dernière version du modèle, disponible dans le dossier parent non daté du bucket gs://dataflow-templates-REGION_NAME/latest/
- Le nom de la version, par exemple 2023-09-12-00_RC00, pour utiliser une version spécifique du modèle, qui est imbriqué dans le dossier parent daté respectif dans le bucket : gs://dataflow-templates-REGION_NAME/
Attention : La dernière version des modèles peut être mise à jour avec des modifications destructives. Vos environnements de production devraient utiliser des modèles conservés dans le dernier dossier parent daté afin d'empêcher que ces modifications n'affectent vos workflows.
LOCATION : région dans laquelle vous souhaitez déployer votre job Dataflow, par exemple us-central1
DRIVER_JARS : chemin(s) d'accès Cloud Storage séparé(s) par des virgules vers le(s) pilote(s) JDBC
DRIVER_CLASS_NAME : nom de la classe du pilote JDBC
CONNECTION_URL : chaîne d'URL de connexion JDBC
OUTPUT_TABLE : table de sortie BigQuery
BIG_QUERY_LOADING_TEMPORARY_DIRECTORY : répertoire temporaire pour le processus de chargement de BigQuery

Code source du modèle

Java

/*
 * Copyright (C) 2018 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */
package com.google.cloud.teleport.v2.templates;

import static com.google.cloud.teleport.v2.utils.GCSUtils.getGcsFileAsString;
import static com.google.cloud.teleport.v2.utils.KMSUtils.maybeDecrypt;

import com.google.api.services.bigquery.model.TableRow;
import com.google.cloud.teleport.metadata.Template;
import com.google.cloud.teleport.metadata.TemplateCategory;
import com.google.cloud.teleport.v2.common.UncaughtExceptionLogger;
import com.google.cloud.teleport.v2.options.JdbcToBigQueryOptions;
import com.google.cloud.teleport.v2.utils.BigQueryIOUtils;
import com.google.cloud.teleport.v2.utils.JdbcConverters;
import com.google.common.annotations.VisibleForTesting;
import org.apache.beam.sdk.Pipeline;
import org.apache.beam.sdk.PipelineResult;
import org.apache.beam.sdk.io.FileSystems;
import org.apache.beam.sdk.io.gcp.bigquery.BigQueryIO;
import org.apache.beam.sdk.io.gcp.bigquery.BigQueryIO.Write;
import org.apache.beam.sdk.io.gcp.bigquery.TableRowJsonCoder;
import org.apache.beam.sdk.io.jdbc.JdbcIO;
import org.apache.beam.sdk.options.PipelineOptionsFactory;
import org.apache.beam.sdk.options.ValueProvider.StaticValueProvider;
import org.apache.beam.sdk.values.PCollection;

/**
 * A template that copies data from a relational database using JDBC to an existing BigQuery table.
 *
 * <p>Check out <a
 * href="https://github.com/GoogleCloudPlatform/DataflowTemplates/blob/main/v2/jdbc-to-googlecloud/README_Jdbc_to_BigQuery_Flex.md">README</a>
 * for instructions on how to use or modify this template.
 */
@Template(
    name = "Jdbc_to_BigQuery_Flex",
    category = TemplateCategory.BATCH,
    displayName = "JDBC to BigQuery with BigQuery Storage API support",
    description = {
      "The JDBC to BigQuery template is a batch pipeline that copies data from a relational database table into an existing BigQuery table. "
          + "This pipeline uses JDBC to connect to the relational database. You can use this template to copy data from any relational database with available JDBC drivers into BigQuery.",
      "For an extra layer of protection, you can also pass in a Cloud KMS key along with a Base64-encoded username, password, and connection string parameters encrypted with the Cloud KMS key. "
          + "See the <a href=\"https://cloud.google.com/kms/docs/reference/rest/v1/projects.locations.keyRings.cryptoKeys/encrypt\">Cloud KMS API encryption endpoint</a> for additional details on encrypting your username, password, and connection string parameters."
    },
    optionsClass = JdbcToBigQueryOptions.class,
    flexContainerName = "jdbc-to-bigquery",
    documentation =
        "https://cloud.google.com/dataflow/docs/guides/templates/provided/jdbc-to-bigquery",
    contactInformation = "https://cloud.google.com/support",
    preview = true,
    requirements = {
      "The JDBC drivers for the relational database must be available.",
      "The BigQuery table must exist before pipeline execution.",
      "The BigQuery table must have a compatible schema.",
      "The relational database must be accessible from the subnet where Dataflow runs."
    })
public class JdbcToBigQuery {

  /**
   * Main entry point for executing the pipeline. This will run the pipeline asynchronously. If
   * blocking execution is required, use the {@link JdbcToBigQuery#run} method to start the pipeline
   * and invoke {@code result.waitUntilFinish()} on the {@link PipelineResult}.
   *
   * @param args The command-line arguments to the pipeline.
   */
  public static void main(String[] args) {
    UncaughtExceptionLogger.register();

    // Parse the user options passed from the command-line
    JdbcToBigQueryOptions options =
        PipelineOptionsFactory.fromArgs(args).withValidation().as(JdbcToBigQueryOptions.class);

    run(options, writeToBQTransform(options));
  }

  /**
   * Create the pipeline with the supplied options.
   *
   * @param options The execution parameters to the pipeline.
   * @param writeToBQ the transform that outputs {@link TableRow}s to BigQuery.
   * @return The result of the pipeline execution.
   */
  @VisibleForTesting
  static PipelineResult run(JdbcToBigQueryOptions options, Write<TableRow> writeToBQ) {
    // Validate BQ STORAGE_WRITE_API options
    BigQueryIOUtils.validateBQStorageApiOptionsBatch(options);

    // Create the pipeline
    Pipeline pipeline = Pipeline.create(options);

    /*
     * Steps: 1) Read records via JDBC and convert to TableRow via RowMapper
     *        2) Append TableRow to BigQuery via BigQueryIO
     */
    JdbcIO.DataSourceConfiguration dataSourceConfiguration =
        JdbcIO.DataSourceConfiguration.create(
                StaticValueProvider.of(options.getDriverClassName()),
                maybeDecrypt(options.getConnectionURL(), options.getKMSEncryptionKey()))
            .withUsername(maybeDecrypt(options.getUsername(), options.getKMSEncryptionKey()))
            .withPassword(maybeDecrypt(options.getPassword(), options.getKMSEncryptionKey()));

    if (options.getDriverJars() != null) {
      dataSourceConfiguration = dataSourceConfiguration.withDriverJars(options.getDriverJars());
    }

    if (options.getConnectionProperties() != null) {
      dataSourceConfiguration =
          dataSourceConfiguration.withConnectionProperties(options.getConnectionProperties());
    }

    /*
     * Step 1: Read records via JDBC and convert to TableRow
     *         via {@link org.apache.beam.sdk.io.jdbc.JdbcIO.RowMapper}
     */
    PCollection<TableRow> rows;
    if (options.getPartitionColumn() != null && options.getTable() != null) {
      // Read with Partitions
      // TODO(pranavbhandari): Support readWithPartitions for other data types.
      JdbcIO.ReadWithPartitions<TableRow, Long> readIO =
          JdbcIO.<TableRow>readWithPartitions()
              .withDataSourceConfiguration(dataSourceConfiguration)
              .withTable(options.getTable())
              .withPartitionColumn(options.getPartitionColumn())
              .withRowMapper(JdbcConverters.getResultSetToTableRow(options.getUseColumnAlias()));
      if (options.getNumPartitions() != null) {
        readIO = readIO.withNumPartitions(options.getNumPartitions());
      }
      if (options.getLowerBound() != null && options.getUpperBound() != null) {
        readIO =
            readIO.withLowerBound(options.getLowerBound()).withUpperBound(options.getUpperBound());
      }

      if (options.getFetchSize() != null && options.getFetchSize() > 0) {
        readIO = readIO.withFetchSize(options.getFetchSize());
      }

      rows = pipeline.apply("Read from JDBC with Partitions", readIO);
    } else {
      if (options.getQuery() == null) {
        throw new IllegalArgumentException(
            "Either 'query' or both 'table' AND 'PartitionColumn' must be specified to read from JDBC");
      }
      JdbcIO.Read<TableRow> readIO =
          JdbcIO.<TableRow>read()
              .withDataSourceConfiguration(dataSourceConfiguration)
              .withQuery(options.getQuery())
              .withCoder(TableRowJsonCoder.of())
              .withRowMapper(JdbcConverters.getResultSetToTableRow(options.getUseColumnAlias()));

      if (options.getFetchSize() != null && options.getFetchSize() > 0) {
        readIO = readIO.withFetchSize(options.getFetchSize());
      }

      rows = pipeline.apply("Read from JdbcIO", readIO);
    }

    /*
     * Step 2: Append TableRow to an existing BigQuery table
     */
    rows.apply("Write to BigQuery", writeToBQ);

    // Execute the pipeline and return the result.
    return pipeline.run();
  }

  /**
   * Create the {@link Write} transform that outputs the collection to BigQuery as per input option.
   */
  @VisibleForTesting
  static Write<TableRow> writeToBQTransform(JdbcToBigQueryOptions options) {
    // Needed for loading GCS filesystem before Pipeline.Create call
    FileSystems.setDefaultPipelineOptions(options);
    Write<TableRow> write =
        BigQueryIO.writeTableRows()
            .withoutValidation()
            .withCreateDisposition(Write.CreateDisposition.valueOf(options.getCreateDisposition()))
            .withWriteDisposition(
                options.getIsTruncate()
                    ? Write.WriteDisposition.WRITE_TRUNCATE
                    : Write.WriteDisposition.WRITE_APPEND)
            .withCustomGcsTempLocation(
                StaticValueProvider.of(options.getBigQueryLoadingTemporaryDirectory()))
            .to(options.getOutputTable());

    if (Write.CreateDisposition.valueOf(options.getCreateDisposition())
        != Write.CreateDisposition.CREATE_NEVER) {
      write = write.withJsonSchema(getGcsFileAsString(options.getBigQuerySchemaPath()));
    }

    return write;
  }
}

Étapes suivantes

Apprenez-en plus sur les modèles Dataflow.
Consultez la liste des modèles fournis par Google.