SnowConvert AI - Teradata - BTEQ¶

Teradata BTEQ 파일을 Snowflake SQL로 변환하기 위한 변환 참조

설명¶

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

기본 Teradata 쿼리(BTEQ)는 워크스테이션의 사용자가 1개 이상의 Teradata 데이터베이스 시스템과 통신하고 인쇄 및 화면 출력을 위한 보고서 형식을 지정할 수 있는 범용 명령 기반 프로그램입니다.

For more information, see the Teradata BTEQ Reference.

샘플 소스 패턴¶

1. Basic BTEQ Example¶

The BTEQ content is relocated within an EXECUTE IMMEDIATE block to transfer the BTEQ script functionality to Snowflake SQL executable code.

BTEQ 스크립트 내부의 모든 DML 및 DDL 문은 SnowConvert AI에서 지원되며 Snowflake SQL로 성공적으로 변환되었습니다. 아직 지원되지 않거나 전혀 지원되지 않는 명령은 경고 메시지와 함께 주석 처리됩니다.

Teradata BTEQ¶

 -- Additional Params: -q SnowScript
.LOGON 0/dbc,dbc;
   DATABASE tduser;

   CREATE TABLE employee_bkup ( 
      EmployeeNo INTEGER, 
      FirstName CHAR(30), 
      LastName CHAR(30), 
      DepartmentNo SMALLINT, 
      NetPay INTEGER 
   )
   Unique Primary Index(EmployeeNo);

   DROP TABLE employee_bkup;

   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; 
.LOGOFF; 

Snowflake SQL¶

 EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN
    -- Additional Params: -q SnowScript
    --.LOGON 0/dbc,dbc
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'BTLogOn' NODE ***/!!!
    null;
    BEGIN
      USE DATABASE tduser;
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    BEGIN
      CREATE OR REPLACE TABLE employee_bkup (
        EmployeeNo INTEGER,
        FirstName CHAR(30),
        LastName CHAR(30),
        DepartmentNo SMALLINT,
        NetPay INTEGER,
        UNIQUE (EmployeeNo)
      );
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    BEGIN
      DROP TABLE employee_bkup;
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    IF (STATUS_OBJECT['SQLCODE'] /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/ != 0) THEN
      RETURN STATUS_OBJECT['SQLCODE'] /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/;
    END IF;
    --.LOGOFF
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'LogOff' NODE ***/!!!
    null;
  END
$$

2. Bash Variable Placeholders Example¶

SnowConvert AI는 셸 스크립트에 사용되는 Bash 변수 자리 표시자를 사용하여 BTEQ 코드의 마이그레이션을 지원합니다. 이러한 자리 표시자는 SnowSQL의 동등한 코드로 마이그레이션되며 SSC-FDM-TD0003이 코드에 추가됩니다. 이러한 자리 표시자가 있는 코드를 마이그레이션할 때는 다음 사항을 고려하세요.

SnowConvert AI는 셸 스크립트의 마이그레이션을 지원하지 않습니다. BTEQ 코드를 마이그레이션하려면 BTEQ 파일로 분리하여 도구의 입력으로 제공하세요.
마이그레이션된 코드를 실행하려면 변수 대체가 활성화된 SnowSQL이 필요합니다. SnowSQL을 사용하는 방법에 대한 자세한 내용은 SSC-FDM-TD0003 및 SnowSQL에 대한 공식 설명서를 참조하세요.

Teradata BTEQ¶

 -- Additional Params: -q SnowScript
.LOGON dbc, dbc;

DATABASE testing;

SELECT $columnVar FROM $tableVar WHERE col2 = $nameExprVar;
INSERT INTO $tableName values ('$myString', $numValue);
UPDATE $dbName.$tableName SET col1 = $myValue;
DELETE FROM $tableName;

.LOGOFF;

Snowflake SQL¶

 EXECUTE IMMEDIATE
$$
  --** SSC-FDM-TD0003 - BASH VARIABLES FOUND, USING SNOWSQL WITH VARIABLE SUBSTITUTION ENABLED IS REQUIRED TO RUN THIS SCRIPT **
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN
    -- Additional Params: -q SnowScript
    --.LOGON dbc, dbc
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'BTLogOn' NODE ***/!!!
    null;
    BEGIN
      USE DATABASE testing;
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    BEGIN
      SELECT
        &columnVar
      FROM
        &tableVar
      WHERE
        col2 = &nameExprVar;
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    BEGIN
      INSERT INTO &tableName
      VALUES ('&myString', &numValue);
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    BEGIN
      UPDATE &dbName.&tableName
        SET
          col1 = &myValue
        ;
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    BEGIN
      DELETE FROM
        &tableName;
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    --.LOGOFF
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'LogOff' NODE ***/!!!
    null;
  END
$$

3. `.IF`, `.GOTO`, and `.LABEL` (Snowflake Scripting)¶

When the BTEQ script target is Snowflake Scripting (SnowScript), SnowConvert AI translates .IF, .GOTO, and .LABEL by modeling jumps as nested procedure calls and early returns inside a single EXECUTE IMMEDIATE $$ … $$ block. Snowflake does not provide BTEQ-style goto/label semantics.


BTEQ construct	Snowflake Scripting approach
Script body with labels	Wrapped in `EXECUTE IMMEDIATE $$ … $$` with `DECLARE`, nested procedures, and a top-level `BEGIN … END`
`.LABEL name`	Section `name` becomes a nested procedure; handoffs use `CALL name();`
`.GOTO name`	`CALL name();` followed by `RETURN 'PROCESS FINISHED';`
`.IF ERRORCODE …`	`IF (STATUS_OBJECT['SQLCODE'] …)` using a status object updated in `EXCEPTION` handlers
`.IF ACTIVITYCOUNT …`	`IF` on row count from `TABLE(RESULT_SCAN(LAST_QUERY_ID()))`
Error/status tracking	`STATUS_OBJECT` holds status fields; generated SQL may include SSC-FDM-TD0013 (Snowflake `SQLCODE` is not the same as Teradata `ERRORCODE`)

참고

To produce output in this form, set the Teradata/BTEQ script output to Snowflake Scripting (for example -- Additional Params: -q SnowScript in the samples below). Option names vary by interface; see the SnowConvert AI user guide for your product version.

The following example shows .IF ERRORCODE with .GOTO and .LABEL. Teradata BTEQ often branches on ERRORCODE after DDL and jumps to a labeled cleanup or next step.

Teradata BTEQ¶

 -- Additional Params: -q SnowScript
DROP TABLE DP_DWEDW.TF035_PCOM_PROD_TRAT_SEL;
.IF ERRORCODE <> 0 THEN .GOTO CRIA_EXPRESS_1;
.LABEL CRIA_EXPRESS_1
DROP TABLE DP_DWEDW.TF035_DESCTO_EXPRESS;
.IF ERRORCODE <> 0 THEN .GOTO CRIA_EXPRESS_2;
.LABEL CRIA_EXPRESS_2
DROP TABLE DP_DWEDW.TF035_DEVOL_EXPRESS;

Snowflake SQL¶

 EXECUTE IMMEDIATE
$$
  DECLARE
    SC_EXIT_CODE VARCHAR := 0;
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0) /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/;
    SC_PROCESS PROCEDURE ()
    RETURNS VARCHAR
    AS
      BEGIN
        DROP TABLE DP_DWEDW.TF035_PCOM_PROD_TRAT_SEL;
        IF (STATUS_OBJECT['SQLCODE'] /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/ != 0) THEN
          CALL CRIA_EXPRESS_1();
          RETURN 'PROCESS FINISHED';
        END IF;
        CALL CRIA_EXPRESS_1();
      EXCEPTION
        WHEN OTHER CONTINUE THEN
          STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
      END;
    CRIA_EXPRESS_1 PROCEDURE ()
    RETURNS VARCHAR
    AS
      BEGIN
        DROP TABLE DP_DWEDW.TF035_DESCTO_EXPRESS;
        IF (STATUS_OBJECT['SQLCODE'] /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/ != 0) THEN
          CALL CRIA_EXPRESS_2();
          RETURN 'PROCESS FINISHED';
        END IF;
        CALL CRIA_EXPRESS_2();
      EXCEPTION
        WHEN OTHER CONTINUE THEN
          STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
      END;
    CRIA_EXPRESS_2 PROCEDURE ()
    RETURNS VARCHAR
    AS
      BEGIN
        DROP TABLE DP_DWEDW.TF035_DEVOL_EXPRESS;
      EXCEPTION
        WHEN OTHER CONTINUE THEN
          STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
      END;
  BEGIN
    CALL SC_PROCESS();
    RETURN SC_EXIT_CODE;
  END
$$
;

After each statement that can set SQL state, the generated IF checks STATUS_OBJECT['SQLCODE']. On error, the script calls the target label procedure and returns from the current procedure so later statements in that section do not run. On success, it calls the next section’s procedure to continue the original linear flow.

The next example shows .IF ActivityCount = 0 THEN .GOTO …, expressed in Snowflake Scripting using the last query id and RESULT_SCAN.

Teradata BTEQ¶

 -- Additional Params: -q SnowScript
.IF ActivityCount = 0 THEN .GOTO Continue_No_Rejects_00
DROP TABLE DROPTEDTABLE1;
.LABEL Continue_No_Rejects_00
SELECT A FROM AUDITORIA;

Snowflake SQL¶

 EXECUTE IMMEDIATE
$$
  DECLARE
    SC_EXIT_CODE VARCHAR := 0;
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0) /*** SSC-FDM-TD0013 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/;
    SC_PROCESS PROCEDURE ()
    RETURNS VARCHAR
    AS
      BEGIN
        IF ((SELECT
          COUNT(*)
        FROM
          TABLE(RESULT_SCAN(LAST_QUERY_ID()))) = 0) THEN
          CALL Continue_No_Rejects_00();
          RETURN 'PROCESS FINISHED';
        END IF;
        DROP TABLE DROPTEDTABLE1;
        CALL Continue_No_Rejects_00();
      EXCEPTION
        WHEN OTHER CONTINUE THEN
          STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
      END;
    Continue_No_Rejects_00 PROCEDURE ()
    RETURNS VARCHAR
    AS
      BEGIN
        SELECT
          A
        FROM
          AUDITORIA;
      EXCEPTION
        WHEN OTHER CONTINUE THEN
          STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
      END;
  BEGIN
    CALL SC_PROCESS();
    RETURN SC_EXIT_CODE;
  END
$$
;

If activity count is zero, the generated script jumps to the label section with CALL Continue_No_Rejects_00() and RETURN 'PROCESS FINISHED', skipping DROP TABLE DROPTEDTABLE1. Otherwise it runs DROP TABLE, then calls the label procedure to run SELECT A FROM AUDITORIA.

Known Issues¶

Snowflake SQL 에 해당하는 명령이 없는 BTEQ 명령이 있을 수 있습니다

BTEQ 는 명령어 기반 프로그램이기 때문에 입력 코드에 Snowflake SQL 에서 100% 기능적으로 동등하지 않은 명령이 있을 수 있습니다. 이러한 특정 케이스는 식별자가 표시되고 출력 코드에 경고가 표시되며 추가 페이지에 설명되어 있습니다.

SnowConvert AI - Teradata - BTEQ¶

설명¶

샘플 소스 패턴¶

1. Basic BTEQ Example¶

Teradata BTEQ¶

Snowflake SQL¶

2. Bash Variable Placeholders Example¶

Teradata BTEQ¶

Snowflake SQL¶

3. .IF, .GOTO, and .LABEL (Snowflake Scripting)¶

Teradata BTEQ¶

Snowflake SQL¶

Teradata BTEQ¶

Snowflake SQL¶

Known Issues¶

관련 EWIs ¶

3. `.IF`, `.GOTO`, and `.LABEL` (Snowflake Scripting)¶