SnowConvert AI – Teradata – BTEQ

Referências de tradução para converter arquivos Teradata BTEQ para o Snowflake SQL

Descrição

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

O Basic Teradata Query (BTEQ) é um programa de uso geral, baseado em comandos, que permite que os usuários de uma estação de trabalho se comuniquem com um ou mais sistemas de banco de dados do Teradata e formatem relatórios para impressão e saída na tela.

For more information, see the Teradata BTEQ Reference.

Amostra de padrões da origem

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.

Todas as instruções DML e DDL dentro de scripts BTEQ são compatíveis com SnowConvert AI e traduzidas com sucesso para Snowflake SQL. Os comandos que ainda não têm suporte ou que não têm suporte nenhum estão sendo marcados com uma mensagem de aviso e comentado.

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

O SnowConvert AI oferece suporte à migração de código BTEQ com espaços reservados de variável Bash usado para scripts de shell. Esses espaços reservados serão migrados para seu equivalente SnowSQL e SSC-FDM-TD0003 será adicionado ao código. Considere o seguinte ao migrar o código com esses espaços reservados:

  • O SnowConvert AI não oferece suporte à migração de scripts de shell, para migrar o código BTEQ. Isole-o em um arquivo BTEQ e forneça-o como entrada para a ferramenta.

  • O SnowSQL com substituição de variável ativada é necessário para executar o código migrado. Para obter mais informações sobre como usar o SnowSQL verifique SSC-FDM-TD0003 e a documentação oficial para 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)

Nota

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.

Problemas conhecidos

  1. Pode haver comandos do BTEQ que não tenham um equivalente no Snowflake SQL

Como o BTEQ é um programa baseado em comandos, pode haver alguns comandos em seu código de entrada que não tenham uma equivalência funcional de cem por cento no Snowflake SQL. Esses casos específicos são identificados, marcados com avisos no código de saída e documentados nas páginas seguintes.