Modèle SQL Server vers BigQuery

Le modèle SQL Server vers BigQuery est un pipeline de traitement par lot qui copie les données d'une table SQL Server vers une table BigQuery existante. Ce pipeline utilise JDBC pour se connecter à SQL Server. Pour obtenir une couche supplémentaire de protection, vous pouvez également 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

  • 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. Par exemple, jdbc:mysql://some-host:3306/sampledb. Vous pouvez transmettre cette valeur sous forme de chaîne chiffrée avec une clé Cloud KMS, puis encodée en base64. Supprimez les espaces blancs de la chaîne encodée en base64. 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 la table de sortie BigQuery. (Exemple : <PROJECT_ID>:<DATASET_NAME>.<TABLE_NAME>).
  • 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;]*. Pour en savoir plus, consultez la section "Propriétés de configuration" (https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html) dans la documentation MySQL. (Exemple : unicode=true;characterEncoding=UTF-8).
  • username : nom d'utilisateur à utiliser pour la connexion JDBC. Vous pouvez transmettre cette valeur sous forme de chaîne chiffrée avec une clé Cloud KMS, puis encodée en base64. Supprimez les espaces blancs de la chaîne encodée en base64.
  • password : mot de passe à utiliser pour la connexion JDBC. Vous pouvez transmettre cette valeur sous forme de chaîne chiffrée avec une clé Cloud KMS, puis encodée en base64. Supprimez les espaces blancs de la chaîne encodée en base64.
  • query : requête à exécuter sur la source pour extraire les données. Veuillez noter que certains types JDBC SQL et BigQuery, même s'ils partagent le même nom, présentent quelques différences. Voici quelques mappages de types SQL -> BigQuery importants à retenir : DATETIME --> TIMESTAMP

Une conversion de type peut être nécessaire si vos schémas ne correspondent pas. (Exemple : sélectionnez * dans sampledb.sample_table).

  • KMSEncryptionKey : clé de chiffrement Cloud KMS à utiliser pour déchiffrer le nom d'utilisateur, le mot de passe et la chaîne de connexion. Si vous transmettez une clé Cloud KMS, vous devez également chiffrer le nom d'utilisateur, le mot de passe et la chaîne de connexion. (Exemple : projects/your-project/locations/global/keyRings/your-keyring/cryptoKeys/your-key).
  • useColumnAlias : si la valeur est true, le pipeline utilise l'alias de colonne (AS) au lieu du nom de colonne pour mapper les lignes sur BigQuery. La valeur par défaut est false.
  • isTruncate : si la valeur est true, le pipeline est tronqué avant de charger les données dans BigQuery. La valeur par défaut est false, ce qui amène le pipeline à ajouter des données.
  • partitionColumn : si ce paramètre est fourni avec le nom de table défini en tant que paramètre facultatif, 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, ne prend en charge que les colonnes de partition Long.
  • table : table à lire lors de l'utilisation de partitions. Ce paramètre accepte également une sous-requête entre parenthèses. (Exemple : (select id, name from Person) as subq).
  • numPartitions : Nombre de partitions. Avec les limites inférieure et supérieure, cette valeur 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 à utiliser dans le schéma de partition. Si cette valeur n'est pas fournie, elle est automatiquement déduite par Apache Beam pour les types compatibles.
  • upperBound : limite supérieure à utiliser dans le schéma de partition. Si cette valeur n'est pas fournie, elle est automatiquement déduite par Apache 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 : la propriété CreateDisposition de BigQuery à utiliser. Par exemple, CREATE_IF_NEEDED ou 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 aucun, aucun algorithme n'est désactivé. Utilisez ce paramètre avec prudence, car les algorithmes désactivés par défaut peuvent présenter des failles ou des problèmes de performances. (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 sont enregistrés dans le répertoire "/extra_files" de chaque nœud de calcul. (Exemple : gs://)
  • defaultLogLevel : définit le niveau de journalisation dans les nœuds de calcul. Les options acceptées sont OFF, ERROR, WARN, INFO, DEBUG et TRACE. La valeur par défaut est INFO.
  • useStorageWriteApi : si la valeur est true, le pipeline utilise l'API BigQuery Storage Write (https://cloud.google.com/bigquery/docs/write-api). La valeur par défaut est false. Pour en savoir plus, consultez la page "Utiliser l'API Storage Write" (https://beam.apache.org/documentation/io/built-in/google-bigquery/#storage-write-api).
  • useStorageWriteApiAtLeastOnce : spécifie la sémantique d'écriture, lorsque vous utilisez l'API Storage Write. Pour utiliser la sémantique de type "au moins une fois" (https://beam.apache.org/documentation/io/built-in/google-bigquery/#at-least-once-semantics), définissez ce paramètre sur true. Pour utiliser la sémantique de type "exactement une fois", définissez le paramètre sur false. Ce paramètre ne s'applique que lorsque la valeur de useStorageWriteApi est définie sur true. La valeur par défaut est false.

Exécuter le modèle

Console

  1. Accédez à la page Dataflow Créer un job à partir d'un modèle.
    2. Accéder à la page Créer un job à partir d'un modèle
  2. Dans le champ Nom du job, saisissez un nom de job unique.
  3. 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.

  4. Dans le menu déroulant Modèle Dataflow, sélectionnez the SQL Server to BigQuery template.
  5. Dans les champs fournis, saisissez vos valeurs de paramètres.
  6. 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 \
    --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

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 :

  • REGION_NAME : région dans laquelle vous souhaitez déployer votre job Dataflow, par exemple us-central1
  • JDBC_CONNECTION_URL : URL de connexion JDBC
  • SOURCE_SQL_QUERY : requête SQL à exécuter sur la base de données source.
  • DATASET : votre ensemble de données BigQuery.
  • TABLE_NAME : nom de votre table BigQuery.
  • PATH_TO_TEMP_DIR_ON_GCS : chemin d'accès Cloud Storage au répertoire temporaire
  • CONNECTION_PROPERTIES : propriétés de connexion JDBC, le cas échéant.
  • CONNECTION_USERNAME : nom d'utilisateur de la connexion JDBC
  • CONNECTION_PASSWORD : mot de passe de la connexion JDBC
  • KMS_ENCRYPTION_KEY : clé de chiffrement Cloud KMS.

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",
    "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" }
  }
}

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 :

  • LOCATION : région dans laquelle vous souhaitez déployer votre job Dataflow, par exemple us-central1
  • JDBC_CONNECTION_URL : URL de connexion JDBC
  • SOURCE_SQL_QUERY : requête SQL à exécuter sur la base de données source.
  • DATASET : votre ensemble de données BigQuery.
  • TABLE_NAME : nom de votre table BigQuery.
  • PATH_TO_TEMP_DIR_ON_GCS : chemin d'accès Cloud Storage au répertoire temporaire
  • CONNECTION_PROPERTIES : propriétés de connexion JDBC, le cas échéant.
  • CONNECTION_USERNAME : nom d'utilisateur de la connexion JDBC
  • CONNECTION_PASSWORD : mot de passe de la connexion JDBC
  • KMS_ENCRYPTION_KEY : clé de chiffrement Cloud KMS.

Étapes suivantes