구성 YAML 파일을 사용하여 SQL 변환

이 문서에서는 구성 YAML 파일을 사용하여 SQL 코드를 변환하고 BigQuery로 마이그레이션하는 방법을 설명합니다. 자체 구성 YAML 파일을 만들기 위한 가이드라인과 이 기능에서 지원되는 다양한 변환에 대한 예시를 제공합니다.

BigQuery 대화형 SQL 변환기를 사용하거나 일괄 SQL 변환을 수행할 때 SQL 쿼리 변환을 수정하기 위한 구성 YAML 파일을 제공할 수 있습니다. 구성 YAML 파일을 사용하면 소스 데이터베이스에서 SQL 쿼리를 변환할 때 추가로 맞춤설정할 수 있습니다.

다음 방법으로 SQL 변환에 사용할 구성 YAML 파일을 지정할 수 있습니다.

대화형 SQL 변환기, 일괄 SQL 변환기, 일괄 변환 Python 클라이언트는 단일 변환 작업에 여러 구성 YAML 파일 사용을 지원합니다. 자세한 내용은 여러 YAML 구성 적용을 참조하세요.

구성 YAML 파일 요구사항

구성 YAML 파일을 만들기 전에 다음 정보를 검토하여 YAML 파일이 BigQuery Migration Service와 호환되는지 확인합니다.

  • 구성 YAML 파일을 SQL 변환 입력 파일이 포함된 Cloud Storage 버킷의 루트 디렉터리에 업로드해야 합니다. 버킷을 만들고 Cloud Storage에 파일을 업로드하는 방법에 대한 자세한 내용은 버킷 만들기 및 파일 시스템에서 객체 업로드를 참조하세요.
  • 단일 구성 YAML 파일의 파일 크기는 1MB를 초과하면 안 됩니다.
  • 단일 SQL 변환 작업에 사용되는 모든 구성 YAML 파일의 총 파일 크기는 4MB를 초과할 수 없습니다.
  • 이름 일치에 regex 구문을 사용하는 경우 RE2/J를 사용합니다.
  • 모든 구성 YAML 파일 이름에는 .config.yaml 확장 프로그램이 포함되어야 합니다(예: change-case.config.yaml).
    • config.yaml만으로는 구성 파일에 유효한 이름이 아닙니다.

구성 YAML 파일 만들기 가이드라인

이 섹션에서는 구성 YAML 파일을 만들기 위한 몇 가지 일반 가이드라인을 제공합니다.

각 구성 파일에는 구성 유형을 지정하는 헤더가 있어야 합니다. object_rewriter 유형은 구성 YAML 파일에서 SQL 변환을 지정하는 데 사용됩니다. 다음 예시에서는 object_rewriter 유형을 사용하여 이름 대소문자를 변환합니다.

type: object_rewriter
global:
  case:
    all: UPPERCASE

항목 선택

항목별 변환을 수행하려면 구성 파일에 항목을 지정합니다. 모든 match 속성은 선택사항입니다. 변환에 필요한 match 속성만 사용하세요. 다음 구성 YAML은 특정 항목을 선택하는 데 도움이 되는 속성을 보여줍니다.

match:
  db: <literal_name>
  schema: <literal_name>
  relation: <literal_name>
  attribute: <literal_name>
  dbRegex: <regex>
  schemaRegex: <regex>
  relationRegex: <regex>
  attributeRegex: <regex>

각 match 속성에 대한 설명은 다음과 같습니다.

  • db: project_id 구성요소입니다.
  • schema: 데이터 세트 구성요소입니다.
  • relation: 테이블 구성요소입니다.
  • attribute: 열 구성요소입니다. 속성 선택에만 유효합니다.
  • dbRegex: 정규 표현식을 사용해서 일치하는 db 속성을 찾습니다(미리보기).
  • schemaRegex: 정규 표현식을 사용해서 일치하는 schema 속성을 찾습니다(미리보기).
  • relationRegex: 정규 표현식을 사용해서 일치하는 relation 속성을 찾습니다(미리보기).
  • attributeRegex: 정규 표현식을 사용해서 일치하는 attribute 속성을 찾습니다. 속성 선택에만 유효합니다(미리보기).

예를 들어 다음 구성 YAML은 임시 테이블 변환을 위해 testdb.acme.employee 테이블을 선택하도록 match 속성을 지정합니다.

type: object_rewriter
relation:
-
  match:
    db: testdb
    schema: acme
    relation: employee
  temporary: true

dbRegex, schemaRegex, relationRegex, attributeRegex 속성을 사용하여 항목의 하위 집합을 선택하기 위한 정규 표현식을 지정할 수 있습니다. 다음 예시에서는 이름이 tmp_로 시작하는 한 testdb의 tmp_schema 스키마에서 모든 관계를 임시로 변경합니다.

type: object_rewriter
relation:
-
  match:
    schema: tmp_schema
    relationRegex: "tmp_.*"
  temporary: true

리터럴과 regex 속성은 모두 대소문자를 구분하지 않는 방식으로 일치합니다. 다음 예시와 같이 사용 중지된 i 플래그와 함께 regex를 사용하여 대소문자를 구분하는 일치를 적용할 수 있습니다.

match:
  relationRegex: "(?-i:<actual_regex>)"

또한 해당하는 짧은 문자열 구문을 사용하여 정규화된 항목을 지정할 수도 있습니다. 짧은 문자열 구문은 점으로 구분된 3(관계 선택의 경우) 또는 4(속성 선택의 경우) 이름 세그먼트를 필요로 합니다(예: testdb.acme.employee). 그런 다음 세그먼트는 각각 db, schema, relation, attribute로 전달된 것처럼 해석됩니다. 즉, 이름이 문자 그대로 일치하므로 짧은 구문에서 정규 표현식이 허용되지 않습니다. 다음 예시에서는 구성 문자열 파일에서 정규화된 항목을 지정하기 위해 짧은 문자열 구문을 사용하는 방법을 보여줍니다.

type: object_rewriter
relation:
-
  match : "testdb.acme.employee"
  temporary: true

테이블에 이름에 점이 포함된 경우 짧은 구문을 사용하여 이름을 지정할 수 없습니다. 이 경우 객체 일치를 사용해야 합니다. 다음 예시에서는 testdb.acme.stg.employee 테이블을 임시로 변경합니다.

type: object_rewriter
relation:
-
  match:
    db: testdb
    schema: acme
    relation: stg.employee
  temporary: true

구성 YAML은 key을 match의 별칭으로 허용합니다.

기본 데이터베이스

일부 입력 SQL 언어, 특히 Teradata는 정규화된 이름에서 database-name을 지원하지 않습니다. 이 경우 항목을 일치시키는 가장 쉬운 방법은 match에서 db 속성을 생략하는 것입니다.

하지만 BigQuery Migration Service의 default_database 속성을 설정하고 match에서 해당 기본 데이터베이스를 사용할 수 있습니다.

지원되는 대상 속성 유형

구성 YAML 파일을 사용하여 속성 유형 변환을 수행할 수 있습니다. 여기서 열의 데이터 유형을 소스 유형에서 대상 유형으로 변환합니다. 구성 YAML 파일은 다음과 같은 대상 유형을 지원합니다.

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • NUMERIC(NUMERIC(18, 2)과 같은 선택적 정밀도 및 규모 지원)
  • TIME
  • TIMETZ
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIMESTAMPTZ
  • CHAR(CHAR(42)와 같은 선택적 정밀도 지원)
  • VARCHAR(VARCHAR(42)와 같은 선택적 정밀도 지원)

구성 YAML 예시

이 섹션에서는 SQL 변환에 사용할 여러 구성 YAML 파일을 만들기 위한 예시를 제공합니다. 각 예시에서는 SQL 변환을 특정 방식으로 변환하는 YAML 구문을 간단히 설명합니다. 각 예시는 BigQuery SQL 쿼리 변환에서 구성 YAML의 효과를 비교할 수 있도록 teradata-input.sql 또는 hive-input.sql 파일과 bq-output.sql 파일의 콘텐츠도 제공합니다.

다음 예시에서는 Teradata 또는 Hive를 입력 SQL 언어로, BigQuery SQL을 출력 언어로 사용합니다. 다음 예시에서는 testdb를 기본 데이터베이스로, testschema를 스키마 검색 경로로 사용합니다.

객체 이름 대소문자 변경

다음 구성 YAML은 객체 이름의 대문자 또는 소문자를 변경합니다.

type: object_rewriter
global:
  case:
    all: UPPERCASE
    database: LOWERCASE
    attribute: LOWERCASE

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

      create table x(a int);
      select * from x;
bq-output.sql 

      CREATE TABLE testdb.TESTSCHEMA.X
      (
        a INT64
      )
      ;
      SELECT
          X.a
        FROM
          testdb.TESTSCHEMA.X
      ;

임시적인 테이블 만들기

다음 구성 YAML은 일반 테이블을 임시 테이블로 변경합니다.

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    temporary: true

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

    create table x(a int);
bq-output.sql 

    CREATE TEMPORARY TABLE x
    (
      a INT64
    )
    ;

일시적인 테이블 만들기

다음 구성 YAML은 일반 테이블을 만료 시간이 60초인 일시적인 테이블로 변경합니다.

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    ephemeral:
      expireAfterSeconds: 60

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

    create table x(a int);
bq-output.sql 

    CREATE TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      expiration_timestamp=timestamp_add(current_timestamp(), interval 60 SECOND)
    );

파티션 만료 시간 설정

다음 구성 YAML은 파티션을 나눈 테이블의 만료 시간을 1일로 변경합니다.

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    partitionLifetime:
      expireAfterSeconds: 86400

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

    create table x(a int, b int) partition by (a);
bq-output.sql 

    CREATE TABLE testdb.testschema.x
    (
      a INT64,
      b INT64
    )
    CLUSTER BY a
    OPTIONS(
      partition_expiration_days=1
    );

테이블의 외부 위치 또는 형식 변경

다음 구성 YAML은 테이블의 외부 위치 및 구성을 변경합니다.

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    external:
      locations: "gs://path/to/department/files"
      format: ORC

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

    create table x(a int);
bq-output.sql 

    CREATE EXTERNAL TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      format='ORC',
      uris=[
        'gs://path/to/department/files'
      ]
    );

테이블 설명 설정 또는 변경

다음 구성 YAML은 테이블 설명을 설정합니다.

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    description:
      text: "Example description."

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

    create table x(a int);
bq-output.sql 

    CREATE TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      description='Example description.'
    );

테이블 파티션 나누기 설정 또는 변경

다음 구성 YAML은 테이블의 파티션 나누기 스키마를 변경합니다.

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    partition:
      simple:
        add: [a]
  -
    match: "testdb.testschema.y"
    partition:
      simple:
        remove: [a]

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

    create table x(a date, b int);
    create table y(a date, b int) partition by (a);
bq-output.sql 

    CREATE TABLE testdb.testschema.x
    (
      a DATE,
      b INT64
    )
    PARTITION BY a;
    CREATE TABLE testdb.testschema.y
    (
      a DATE,
      b INT64
    )
    ;

테이블 클러스터링 설정 또는 변경

다음 구성 YAML은 테이블의 클러스터링 스키마를 변경합니다.

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    clustering:
      add: [a]
  -
    match: "testdb.testschema.y"
    clustering:
      remove: [b]

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

hive-input.sql 

    create table x(a int, b int);
    create table y(a int, b int) clustered by (b) into 16 buckets;
bq-output.sql 

    CREATE TABLE testdb.testschema.x
    (
      a INT64,
      b INT64
    )
    CLUSTER BY a;
    CREATE TABLE testdb.testschema.y
    (
      a INT64,
      b INT64
    )
    ;

열 속성의 유형 변경

다음 구성 YAML은 열의 속성에 대한 데이터 유형을 변경합니다.

type: object_rewriter
attribute:
  -
    match:
      db: testdb
      schema: testschema
      attributeRegex: "a+"
    type:
      target: NUMERIC(10,2)

소스 데이터 유형을 지원되는 대상 속성 유형으로 변환할 수 있습니다.

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

    create table x(a int, b int, aa int);
bq-output.sql 

    CREATE TABLE testdb.testschema.x
    (
      a NUMERIC(31, 2),
      b INT64,
      aa NUMERIC(31, 2)
    )
    ;

외부 데이터 레이크에 연결 추가

다음 구성 YAML은 소스 테이블을 데이터 레이크 연결로 지정된 외부 데이터 레이크에 저장된 데이터를 가리키는 외부 테이블로 표시합니다.

type: object_rewriter
relation:
-
  key: "testdb.acme.employee"
  external:
    connection_id: "connection_test"

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

hive-input.sql 

    CREATE TABLE x
    (
      a VARCHAR(150),
      b INT
    );
bq-output.sql 

    CREATE EXTERNAL TABLE x
    (
      a STRING,
      b INT64
    )
    WITH CONNECTION `connection_test`
    OPTIONS(
    );

입력 파일의 문자 인코딩 변경

기본적으로 BigQuery Migration Service는 입력 파일의 문자 인코딩을 자동으로 감지하려고 시도합니다. BigQuery Migration Service가 파일 인코딩을 잘못 식별할 수 있는 경우 구성 YAML을 사용해서 문자 인코딩을 명시적으로 지정할 수 있습니다.

다음 구성 YAML은 입력 파일의 명시적 문자 인코딩을 ISO-8859-1로 지정합니다.

type: experimental_input_formats
formats:
- source:
    pathGlob: "*.sql"
  contents:
    raw:
      charset: iso-8859-1

전역 유형 변환

다음 구성 YAML은 데이터 유형을 모든 스크립트에서 다른 유형으로 변경하고 트랜스파일 스크립트에서 방지할 소스 데이터 유형을 지정합니다. 이는 단일 속성의 데이터 유형만 변경되는 열 속성의 변경 유형 구성과 다릅니다.

BigQuery는 다음과 같은 데이터 유형 변환을 지원합니다.

  • DATETIME에서 TIMESTAMP로 변환
  • TIMESTAMP에서 DATETIME으로 변환
  • TIMESTAMP WITH TIME ZONE에서 DATETIME으로 변환
  • CHAR에서 VARCHAR로 변환

다음 예시에서 구성 YAML은 DATETIME 데이터 유형을 TIMESTAMP로 변환합니다.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

      create table x(a timestamp);
bq-output.sql 

      CREATE TABLE x
      (
        a TIMESTAMP
      )
      ;

Select 문 수정

다음 구성 YAML은 SELECT 문에서 star 프로젝션, GROUP BY, ORDER BY 절을 변경합니다.

starProjection은 다음 구성을 지원합니다.

  • ALLOW
  • PRESERVE(기본)
  • EXPAND

groupBy 및 orderBy는 다음 구성을 지원합니다.

  • EXPRESSION
  • ALIAS
  • INDEX

다음 예시에서 구성 YAML은 star 프로젝션을 EXPAND로 구성합니다.

type: experimental_statement_rewriter
select:
  starProjection: EXPAND

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

      create table x(a int, b TIMESTAMP);
      select * from x;
bq-output.sql 

      CREATE TABLE x
      (
        a INT64,
        b DATETIME
      )
      ;
      SELECT
          x.a
          x.b
        FROM
          x
      ;

UDF 사양

다음 구성 YAML은 소스 스크립트에 사용된 사용자 정의 함수(UDF)의 서명을 지정합니다. 메타데이터 zip 파일과 같이 UDF 정의는 보다 정확한 입력 스크립트 변환을 생성하도록 도와줄 수 있습니다.

type: metadata
udfs:
  - "date parse_short_date(dt int)"

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

      create table x(dt int);
      select parse_short_date(dt) + 1 from x;
bq-output.sql 

      CREATE TABLE x
      (
        dt INT64
      )
      ;
      SELECT
          date_add(parse_short_date(x.dt), interval 1 DAY)
        FROM
          x
      ;

엄격한 십진수 정밀도 설정

기본적으로 BigQuery Migration Service는 숫자 정밀도를 지정된 크기에 사용 가능한 최고 정밀도로 늘립니다. 다음 구성 YAML은 소스 문의 십진수 정밀도를 보존하도록 엄격한 정밀도를 구성하여 이러한 동작을 재정의합니다.

type: experimental_statement_rewriter
common:
  decimalPrecision: STRICT

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

      create table x(a decimal(3,0));
bq-output.sql 

      CREATE TABLE x
      (
        a NUMERIC(3)
      )
      ;

출력 이름 매핑

구성 YAML을 사용해서 SQL 객체 이름을 매핑할 수 있습니다. 매핑되는 객체에 따라 이름의 여러 부분을 변경할 수 있습니다.

정적 이름 매핑

항목 이름을 매핑하려면 정적 이름 매핑을 사용합니다. 이름의 특정 부분만 변경하고 이름의 다른 부분은 동일하게 유지하려면 변경할 부분만 포함합니다.

다음 구성 YAML은 테이블 이름을 my_db.my_schema.my_table에서 my_new_db.my_schema.my_new_table로 변경합니다.

type: experimental_object_rewriter
relation:
-
  match: "my_db.my_schema.my_table"
  outputName:
    database: "my_new_db"
    relation: "my_new_table"

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

      create table my_db.my_schema.my_table(a int);
bq-output.sql 

      CREATE TABLE my_new_db.my_schema.my_new_table
      (
        a INT64
      )

동적 이름 매핑

여러 객체를 동시에 변경하고 매핑된 객체를 기준으로 새 이름을 생성하려면 동적 이름 매핑을 사용합니다.

다음 구성 YAML은 staging 스키마에 속하는 항목에 stg_ 프리픽스를 추가하여 모든 테이블의 이름을 변경하고 이러한 테이블을 production 스키마로 이동합니다.

type: experimental_object_rewriter
relation:
-
  match:
    schema: staging
  outputName:
    schema: production
    relation: "stg_${relation}"

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

      create table staging.my_table(a int);
bq-output.sql 

      CREATE TABLE production.stg_my_table
      (
        a INT64
      )
      ;

기본 데이터베이스 및 스키마 검색 경로 지정

다음 구성 YAML은 기본 데이터베이스 및 스키마 검색 경로를 지정합니다.

type: environment
session:
  defaultDatabase: myproject
  schemaSearchPath: [myschema1, myschema2]

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

      SELECT * FROM database.table
      SELECT * FROM table1
bq-output.sql 

      SELECT * FROM myproject.database.table.
      SELECT * FROM myproject.myschema1.table1

전역 출력 이름 재작성

다음 구성 YAML은 구성된 규칙에 따라 스크립트에서 모든 객체(데이터베이스, 스키마, 관계, 속성)의 출력 이름을 변경합니다.

type: experimental_object_rewriter
global:
  outputName:
    regex:
      - match: '\s'
        replaceWith: '_'
      - match: '>='
        replaceWith: 'gte'
      - match: '^[^a-zA-Z_].*'
        replaceWith: '_$0'

이 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같이 표시될 수 있습니다.

teradata-input.sql 

      create table "test special chars >= 12"("42eid" int, "custom column" varchar(10));
bq-output.sql 

      CREATE TABLE test_special_chars_employees_gte_12
      (
        _42eid INT64,
        custom_column STRING
      )
      ;

여러 YAML 구성 적용

일괄 또는 대화형 SQL 변환에 구성 YAML 파일을 지정할 때 여러 변환을 반영하도록 단일 변환 작업에 여러 구성 YAML 파일을 선택할 수 있습니다. 여러 구성이 충돌하면 하나의 변환이 다른 구성을 재정의할 수 있습니다. 동일한 변환 작업에서 변환이 충돌하지 않도록 각 파일에 다른 유형의 구성 설정을 사용하는 것이 좋습니다.

다음은 단일 SQL 변환 작업에 제공된 2개의 개별 구성 YAML 파일을 보여줍니다. 하나는 열 속성을 변경하고, 다른 하나는 테이블을 임시로 설정합니다.

change-type-example.config.yaml:

type: object_rewriter
attribute:
  -
    match: "testdb.testschema.x.a"
    type:
      target: NUMERIC(10,2)

make-temp-example.config.yaml:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    temporary: true

이러한 두 구성 YAML 파일을 사용하는 SQL 변환은 다음과 같습니다.

teradata-input.sql 

    create table x(a int);
bq-output.sql 

    CREATE TEMPORARY TABLE x
    (
      a NUMERIC(31, 2)
    )
    ;