BTEQ
Translation references to convert Teradata BTEQ files to Python
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.
In order to simulate the BTEQ functionality for Teradata in Snowflake, BTEQ files and commands are transformed to Python code, similar to the transformations performed for MultiLoad and FastLoad scripts. The generated code uses the Snowflake Python project called snowconvert.helpers which contains the required functions to simulate the BTEQ statements in Snowflake.
BTEQ Commands Translation
The following table presents the conversion for the BTEQ commands to Snowflake.
Teradata | Snowflake | Notes |
---|---|---|
ERRORCODE != 0 | snowconvert.helpers.error_code != 0 | |
.EXPORT DATA FILE=fileName | Export.report("fileName", ",") | The function has no functionality |
.EXPORT INDICDATA FILE=fileName | Export.report("fileName", ",") | The function has no functionality |
.EXPORT REPORT FILE=fileName | Export.report("fileName", ",") | The function has no functionality |
.EXPORT DIF FILE=fileName | Export.report("fileName", ",") | The function has no functionality |
.EXPORT RESET | Export.reset() | The function has no functionality |
.IF ERRORCODE != 0 THEN .QUIT ERRORCODE | If snowconvert.helpers.error_code != 0: snowconvert.helpers.quit_application (snowconvert.helpers.error_code) | |
.IMPORT RESET | snowconvert.helpers.import_reset() | The function has no functionality |
.LABEL newLabel | def NEWLABEL(): snowconvert.helpers.quit_application() | |
.LOGOFF | The statement is commented | |
.LOGON | The statement is commented | |
.LOGMECH | The statement is commented | |
.OS /fs/fs01/bin/filename.sh 'load' | snowconvert.helpers.os(""/fs/fs01/bin/filename.sh 'load' "") | |
.RUN FILE=newFile | for statement in snowconvert.helpers.readrun("newFile"): eval(statement) | |
.SET DEFAULTS | Export.defaults() | The function has no functionality |
.SET ERRORLEVEL 3807 SEVERITY 0 | snowconvert.helpers.set_error_level(3807, 0) | |
.SET RECORMODE OFF | Export.record_mode(False) | |
.SET RECORMODE ON | Export.record_mode(True) | |
.SET SEPARATOR '|' | Export.separator_string('|') | The function has no functionality |
.SET WIDTH 120 | Export.width(120) | The function has no functionality |
.Remark ""Hello world!""" | snowconvert.helpers.remark(r""""""Hello world!"""""") | |
.QUIT ERRORCODE | snowconvert.helpers.quit_application( snowconvert.helpers.error_code ) | |
.QUIT | snowconvert.helpers.quit_application() | |
SQL statements | exec(statement) | |
$(<$INPUT_SQL_FILE) | exec_file("$INPUT_SQL_FILE") | |
= (Repeat previous command) | snowconvert.helpers.repeat_previous_sql_statement(con) |
For more complicated statements presented in the previous table, subsections with more elaborated examples are explained.
.GOTO Conversion
Since we are converting BTEQ scripts to Python, certain structures that are valid in BTEQ are not inherently supported in Python. This is the case for the .GOTO
command using the .Label
commands.
For this reason, an alternative has been developed so that the functionality of these commands can be emulated, turning the .Label
commands into functions with subsequent call statements.
Check the following code:
In the example above, there were five commands. Two of them were.Label
commands. The commandFIRSTLABEL
was transformed into a function with the statement(s) that follow it below until another.LABEL
command is found. When another label is called (in this case, SECONDLABEL
), that call ends the first function and starts a new one.
If we were to migrate the previous example, the result would be:
Notice there is a call to the functionFIRSTLABEL
, this function has only one statement, which would be the only non-label command that followsFIRSTLABEL
in the original code. Before theFIRSTLABEL
function ends, it calls SECONDLABEL
, with the statements that followed it.
Notes:
Creating a connector variable
con = None
, and populating it in themain()
function:con = snowconvert.helpers.log_on()
.Setting up a log:
snowconvert.helpers.configure_log()
.
Execute Query Statements
Every SQL statement found in a BTEQ file will be executed though to theexec
function provided by the snowconvert.helpers. Take for example the following code:
This is converted to: