Translation references to convert Teradata BTEQ files to Snowflake SQL
Description
Basic Teradata Query (BTEQ) is a general-purpose, command-based program that enables users on a workstation to communicate with one or more Teradata Database systems and to format reports for both print and screen output.
The BTEQ content is relocated within an EXECUTE IMMEDIATE block of to transfer the BTEQ script functionality to Snowflake SQL executable code.
All the DML and DDL statements inside BTEQ scripts are supported by SnowConvert and successfully translated to Snowflake SQL. The commands that do not have support yet, or do not have support at all, are being marked with a warning message and commented out.
EXECUTE IMMEDIATE$$DECLARE STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);BEGIN/*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SCRIPT STATEMENTS' NODE ***//*.LOGON 0/dbc,dbc;*//*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SCRIPT STATEMENTS' NODE ***//* DATABASE tduser;*/BEGINCREATETABLE employee_bkup ( EmployeeNo INTEGER, FirstName CHAR(30), LastName CHAR(30), DepartmentNo SMALLINT, NetPay INTEGER,UNIQUE (EmployeeNo) ); STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);EXCEPTIONWHEN OTHER THEN STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);END;BEGINDROPTABLE employee_bkup; STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);EXCEPTIONWHEN OTHER THEN STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);END; IF (STATUS_OBJECT['SQLCODE'] /*** MSC-WARNING - MSCEWI2075 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/ != 0) THEN
RETURN STATUS_OBJECT['SQLCODE'] /*** MSC-WARNING - MSCEWI2075 - THE SNOWFLAKE ERROR CODE MISMATCH THE ORIGINAL TERADATA ERROR CODE ***/;
END IF;/*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SCRIPT STATEMENTS' NODE ***//*.LOGOFF;*/END$$
2. Bash Variable Placeholders Example
SnowConvert supports the migration of BTEQ code with Bash Variable Placeholders used for shell scripts, these placeholders will be migrated to its SnowSQL equivalent and MSCEWI2073 will be added to the code. Please consider the following when migrating code with these placeholders:
SnowConvert does not support the migration of shell scripts, to migrate the BTEQ code please isolate it in a BTEQ file and supply it as input for the tool.
SnowSQL with variable substitution enabled is required to execute the migrated code, for more information on how to use SnowSQL please check MSCEWI2073 and the official documentation for SnowSQL.
.LOGON dbc, dbc;DATABASE testing;SELECT $columnVar FROM $tableVar WHERE col2 = $nameExprVar;INSERTINTO $tableName values ('$myString', $numValue);UPDATE $dbName.$tableName SET col1 = $myValue;DELETEFROM $tableName;.LOGOFF;
EXECUTE IMMEDIATE$$ --** MSC-WARNING - MSCEWI2073 - BASH VARIABLES FOUND, USING SNOWSQL WITH VARIABLE SUBSTITUTION ENABLED IS REQUIRED TO RUN THIS SCRIPT **
DECLARE STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);BEGIN/*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SCRIPT STATEMENTS' NODE ***//*.LOGON dbc, dbc;*//*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SCRIPT STATEMENTS' NODE ***//*DATABASE testing;*/BEGINSELECT &columnVarFROM &tableVarWHERE col2 = &nameExprVar; STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);EXCEPTIONWHEN OTHER THEN STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);END;BEGININSERTINTO &tableNameVALUES ('&myString', &numValue); STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);EXCEPTIONWHEN OTHER THEN STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);END;BEGINUPDATE &dbName.&tableNameSET col1 = &myValue ; STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);EXCEPTIONWHEN OTHER THEN STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);END;BEGINDELETEFROM &tableName; STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);EXCEPTIONWHEN OTHER THEN STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);END;/*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SCRIPT STATEMENTS' NODE ***//*.LOGOFF;*/END$$
Known Issues
There may be BTEQ commands that do not have an equivalent in Snowflake SQL
Since BTEQ is a command-based program, there may be some commands in your input code that do not have a hundred percent functional equivalence in Snowflake SQL. Those particular cases are identified, marked with warnings in the output code, and documented in the further pages.