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 연결 URL 문자열입니다. 예를 들면 jdbc:mysql://some-host:3306/sampledb입니다. Base64로 인코딩된 문자열로 전달한 후 Cloud KMS 키로 암호화할 수 있습니다. 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>:<dataset>.<table_name> 형식이어야 합니다. 테이블 스키마가 입력 객체와 일치해야 합니다. (예:
• bigQueryLoadingTemporaryDirectory: BigQuery 로드 프로세스를 위한 임시 디렉터리입니다(예: gs://your-bucket/your-files/temp_dir).

선택적 매개변수

• connectionProperties: JDBC 연결에 사용할 속성 문자열입니다. 문자열 형식은 [propertyName=property;]*여야 합니다. 예를 들면 unicode=true;characterEncoding=UTF-8입니다.
• username: JDBC 연결에 사용할 사용자 이름입니다. Cloud KMS 키로 암호화된 Base64 인코딩 문자열로 전달할 수 있습니다.
• password: JDBC 연결에 사용할 비밀번호입니다. Cloud KMS 키로 암호화된 Base64 인코딩 문자열로 전달할 수 있습니다.
• query: 데이터를 추출하기 위해 소스에서 실행될 쿼리입니다. 쿼리 또는 테이블과 PartitionColumn을 모두 지정해야 합니다. (예: sampledb.sample_table에서 * 선택).
• KMSEncryptionKey: 사용자 이름, 비밀번호, 연결 문자열을 복호화하는 Cloud KMS 암호화 키입니다. Cloud KMS 키가 전달되면 사용자 이름, 비밀번호, 연결 문자열이 모두 암호화되어 전달되어야 합니다. (예: projects/your-project/locations/global/keyRings/your-keyring/cryptoKeys/your-key).
• useColumnAlias: 사용 설정(true로 설정)되면 파이프라인은 BigQuery에 행을 매핑하기 위해 열 이름 대신 열 별칭('AS')을 고려합니다. 기본값은 false입니다.
• isTruncate: 사용 설정(true로 설정)되면 데이터를 BigQuery에 로드하기 전에 파이프라인이 잘립니다. 기본값은 false이며, 데이터 추가에만 사용됩니다.
• partitionColumn: 이 매개변수가 table과 함께 제공되면 JdbcIO는 범위를 사용해서 동일한 테이블(서브 쿼리)의 쿼리 인스턴스 여러 개를 실행하여 테이블을 병렬로 읽습니다. 현재 긴 파티션 열만 지원됩니다. 쿼리 또는 테이블과 PartitionColumn을 모두 지정해야 합니다.
• table: 파티션을 사용하여 읽을 테이블입니다. 쿼리 또는 테이블과 PartitionColumn을 모두 지정해야 합니다. 이 매개변수는 괄호로 묶인 서브 쿼리도 허용합니다. (예: (사람에서 ID, 이름 선택) subq로))
• numPartitions : 파티션 수입니다. 이 값은 하한 및 상한과 함께 파티션 열을 균등하게 분할하기 위해 사용되는 생성된 WHERE 절 표현식에 대한 파티션 스트라이드를 형성합니다. 입력이 1보다 작으면 숫자가 1로 설정됩니다.
• lowerBound: 파티션 스키마에서 사용되는 하한값입니다. 제공하지 않으면 Beam에서 자동으로 추론됩니다(지원되는 유형에 대해).
• upperBound: 파티션 스키마에 사용되는 상한값입니다. 제공하지 않으면 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://your-bucket/file.txt,projects/project-id/secrets/secret-id/versions/version-id).
• useStorageWriteApi: 사용 설정(true로 설정)되면 파이프라인은 BigQuery에 데이터를 쓸 때 Storage Write API를 사용합니다(https://cloud.google.com/blog/products/data-analytics/streaming-data-into-bigquery-using-storage-write-api 참조). 기본값은 false입니다.
• useStorageWriteApiAtLeastOnce: 이 매개변수는 'BigQuery Storage Write API 사용'이 설정된 경우에만 적용됩니다. 사용 설정하면 Storage Write API에 최소 1회의 시맨틱스가 사용되고 그렇지 않은 경우 정확히 한 번의 시맨틱스가 사용됩니다. 기본값은 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 이상이 필요합니다.

셸 또는 터미널에서 템플릿을 실행합니다.

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 연결 URL
• 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 연결 URL
• 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.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;
  }
}

다음 단계

• Dataflow 템플릿 알아보기
• Google 제공 템플릿 목록 참조