SQL Server to BigQuery 模板

SQL Server to BigQuery 模板是一种批处理流水线，可将 SQL Server 表中的数据复制到现有 BigQuery 表。此流水线使用 JDBC 连接到 SQL Server。为了增加一项保护措施，您还可以在传入使用 Cloud KMS 密钥加密的 Base64 编码用户名、密码和连接字符串参数的同时，传入该 Cloud KMS 密钥。如需详细了解如何对用户名、密码和连接字符串参数进行加密，请参阅 Cloud KMS API 加密端点。

流水线要求

• 在运行此流水线之前，BigQuery 表必须已存在。
• BigQuery 表必须具有兼容的架构。
• 必须能够从运行 Dataflow 的子网访问关系型数据库。

模板参数

必需参数

• driverJars：以英文逗号分隔的驱动程序 JAR 文件列表。（示例：gs://your-bucket/driver_jar1.jar,gs://your-bucket/driver_jar2.jar）。
• driverClassName：JDBC 驱动程序类名称。（示例：com.mysql.jdbc.Driver）。
• connectionURL：JDBC 连接网址字符串。例如 jdbc:mysql://some-host:3306/sampledb。您可以将此值作为使用 Cloud KMS 密钥加密，然后进行 Base64 编码的字符串传入。 从 Base64 编码的字符串中移除空白字符。 请注意 Oracle 非 RAC 数据库连接字符串 (jdbc:oracle:thin:@some-host:<port>:<sid>) 和 Oracle RAC 数据库连接字符串 (jdbc:oracle:thin:@//some-host[:<port>]/<service_name>) 之间的差异。（示例：jdbc:mysql://some-host:3306/sampledb）。
• outputTable：BigQuery 输出表位置。（示例：<PROJECT_ID>:<DATASET_NAME>.<TABLE_NAME>）。
• bigQueryLoadingTemporaryDirectory：BigQuery 加载进程的临时目录。（示例：gs://your-bucket/your-files/temp_dir）。

可选参数

• connectionProperties：要用于 JDBC 连接的属性字符串。字符串的格式必须为 [propertyName=property;]*。如需了解详情，请参阅 MySQL 文档中的“配置属性”部分 (https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html)。（示例：unicode=true;characterEncoding=UTF-8）。
• username：要用于 JDBC 连接的用户名。您可以将此值作为使用 Cloud KMS 密钥加密，然后进行 Base64 编码的字符串传入。 从 Base64 编码的字符串中移除空白字符。
• password：要用于 JDBC 连接的密码。您可以将此值作为使用 Cloud KMS 密钥加密，然后进行 Base64 编码的字符串传入。 从 Base64 编码的字符串中移除空白字符。
• query：要在提取数据的来源上运行的查询。请注意，某些 JDBC SQL 类型和 BigQuery 类型虽然名称相同，但存在一些差异。需要注意的一些重要的 SQL -> BigQuery 类型映射如下：DATETIME --> TIMESTAMP

如果您的架构不匹配，可能需要进行类型转换。（示例：select * from sampledb.sample_table）。

• KMSEncryptionKey：要用于对用户名、密码和连接字符串进行解密的 Cloud KMS 加密密钥。如果您传入 Cloud KMS 密钥，则还必须对用户名、密码和连接字符串进行加密。（示例：projects/your-project/locations/global/keyRings/your-keyring/cryptoKeys/your-key）。
• useColumnAlias：如果设置为 true，则流水线会使用列别名 (AS) 而不是列名称将行映射到 BigQuery。默认值为 false。
• isTruncate：如果设置为 true，则流水线会在将数据加载到 BigQuery 之前截断。默认为 false，这会导致流水线附加数据。
• partitionColumn：如果此参数提供 table 的名称（定义为可选参数），则 JdbcIO 使用范围对同一个表（子查询）执行多个查询实例来并行读取表。目前仅支持 Long 分区列。
• table：使用分区时要读取的表。此参数还接受用英文括号括起的子查询。 （示例：(select id, name from Person) as subq）。
• numPartitions：分区的数量。使用下限和上限，此值会为生成的 WHERE 子句表达式形成分区步长，这些表达式用于均匀拆分分区列。当输入小于 1 时，数字设置为 1。
• lowerBound：要在分区方案中使用的下限。如果未提供，Apache Beam 会针对受支持的类型自动推断此值。
• upperBound：要在分区方案中使用的上限。如果未提供，Apache Beam 会针对受支持的类型自动推断此值。
• fetchSize：一次从数据库中提取的行数。不用于分区读取。默认值为 50000。
• createDisposition：要使用的 BigQuery CreateDisposition。例如 CREATE_IF_NEEDED 或 CREATE_NEVER。默认值为：CREATE_NEVER。
• bigQuerySchemaPath：BigQuery JSON 架构的 Cloud Storage 路径。如果将 createDisposition 设置为 CREATE_IF_NEEDED，则必须指定此参数。（例如：gs://your-bucket/your-schema.json）。
• disabledAlgorithms：要停用的算法（以英文逗号分隔）。如果此值设置为 none，则系统不会停用任何算法。请谨慎使用此参数，因为默认停用的算法可能存在漏洞或性能问题。 （示例：SSLv3、RC4）。
• extraFilesToStage：用于将文件暂存在工作器中的 Cloud Storage 路径或 Secret Manager 密文（以英文逗号分隔）。这些文件保存在每个工作器的 /extra_files 目录中。（示例：gs://）。
• defaultLogLevel：在工作器中设置日志级别。支持的选项包括 OFF、ERROR、WARN、INFO、DEBUG、TRACE。默认为 INFO。
• useStorageWriteApi：如果为 true，则流水线使用 BigQuery Storage Write API (https://cloud.google.com/bigquery/docs/write-api)。默认值为 false。如需了解详情，请参阅使用 Storage Write API (https://beam.apache.org/documentation/io/built-in/google-bigquery/#storage-write-api)。
• useStorageWriteApiAtLeastOnce：使用 Storage Write API 时，指定写入语义。如需使用“至少一次”语义 (https://beam.apache.org/documentation/io/built-in/google-bigquery/#at-least-once-semantics)，请将此参数设置为 true。如需使用“正好一次”语义，请将参数设置为 false。仅当 useStorageWriteApi 为 true 时，此参数才适用。默认值为 false。

运行模板

控制台

1. 转到 Dataflow 基于模板创建作业页面。
转到“基于模板创建作业”
2. 在作业名称字段中，输入唯一的作业名称。
3. 可选：对于区域性端点，从下拉菜单中选择一个值。默认区域为 us-central1。

   如需查看可以在其中运行 Dataflow 作业的区域列表，请参阅 Dataflow 位置。

4. 从 Dataflow 模板下拉菜单中，选择 the SQL Server to BigQuery template。
5. 在提供的参数字段中，输入您的参数值。
6. 点击运行作业。

gcloud

注意：如需使用 Google Cloud CLI 运行 flex模板，您必须具有 Google Cloud CLI 284.0.0 或更高版本。

在 shell 或终端中，运行模板：

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

替换以下内容：

• JOB_NAME：您选择的唯一性作业名称
• VERSION：您要使用的模板的版本

  您可使用以下值：

  • latest，以使用模板的最新版本，该模板在存储桶的未标示日期的父文件夹 (gs://dataflow-templates-REGION_NAME/latest/) 中可用
  • 版本名称（如 2023-09-12-00_RC00），以使用模板的特定版本，该版本嵌套在存储桶的相应日期父文件夹 (gs://dataflow-templates-REGION_NAME/) 中
  注意：最新版模板可能会随着重大更改而更新。为了防止这些重大更改影响您的生产工作流程，生产环境应使用有最近标示日期的父文件夹中保存的模板。
• REGION_NAME：要在其中部署 Dataflow 作业的区域，例如 us-central1
• JDBC_CONNECTION_URL：JDBC 连接网址
• SOURCE_SQL_QUERY：需要在源数据库上运行的 SQL 查询
• DATASET：您的 BigQuery 数据集
• TABLE_NAME：您的 BigQuery 表名称
• PATH_TO_TEMP_DIR_ON_GCS：临时目录的 Cloud Storage 路径
• CONNECTION_PROPERTIES：JDBC 连接属性（如有需要）
• CONNECTION_USERNAME：JDBC 连接用户名
• CONNECTION_PASSWORD：JDBC 连接密码
• KMS_ENCRYPTION_KEY：Cloud KMS 加密密钥

API

如需使用 REST API 来运行模板，请发送 HTTP POST 请求。如需详细了解 API 及其授权范围，请参阅 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" }
  }
}

替换以下内容：

• PROJECT_ID：您要在其中运行 Dataflow 作业的 Google Cloud 项目的 ID
• JOB_NAME：您选择的唯一性作业名称
• VERSION：您要使用的模板的版本

  您可使用以下值：

  • latest，以使用模板的最新版本，该模板在存储桶的未标示日期的父文件夹 (gs://dataflow-templates-REGION_NAME/latest/) 中可用
  • 版本名称（如 2023-09-12-00_RC00），以使用模板的特定版本，该版本嵌套在存储桶的相应日期父文件夹 (gs://dataflow-templates-REGION_NAME/) 中
  注意：最新版模板可能会随着重大更改而更新。为了防止这些重大更改影响您的生产工作流程，生产环境应使用有最近标示日期的父文件夹中保存的模板。
• LOCATION：要在其中部署 Dataflow 作业的区域，例如 us-central1
• JDBC_CONNECTION_URL：JDBC 连接网址
• SOURCE_SQL_QUERY：需要在源数据库上运行的 SQL 查询
• DATASET：您的 BigQuery 数据集
• TABLE_NAME：您的 BigQuery 表名称
• PATH_TO_TEMP_DIR_ON_GCS：临时目录的 Cloud Storage 路径
• CONNECTION_PROPERTIES：JDBC 连接属性（如有需要）
• CONNECTION_USERNAME：JDBC 连接用户名
• CONNECTION_PASSWORD：JDBC 连接密码
• KMS_ENCRYPTION_KEY：Cloud KMS 加密密钥

模板源代码

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.GCSAwareValueProvider;
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(new GCSAwareValueProvider(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;
  }
}

后续步骤

• 了解 Dataflow 模板。
• 参阅 Google 提供的模板列表。