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 -> Teradata_01.bteq

.LABEL FIRSTLABEL
SELECT * FROM MyTable1;
.LABEL SECONDLABEL
SELECT * FROM MyTable2;
SELECT * FROM MyTable3;

In the example above, there were five commands. Two of them were.Labelcommands. The commandFIRSTLABELwas transformed into a function with the statement(s) that follow it below until another.LABELcommand 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:

OUT -> Teradata_01.py

#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***
 
import os
import sys
import snowconvert.helpers
from snowconvert.helpers import Export
from snowconvert.helpers import exec
from snowconvert.helpers import BeginLoading
con = None
def main():
  snowconvert.helpers.configure_log()
  con = snowconvert.helpers.log_on()
  FIRSTLABEL()
  snowconvert.helpers.quit_application()
def FIRSTLABEL():
  exec("""
    SELECT
      *
    FROM
      MyTable1
    """)
  SECONDLABEL()
def SECONDLABEL():
  exec("""
    SELECT
      *
    FROM
      MyTable2
    """)
  exec("""
    SELECT
      *
    FROM
      MyTable3
    """)

if __name__ == "__main__":
  main()

Notice there is a call to the functionFIRSTLABEL, this function has only one statement, which would be the only non-label command that followsFIRSTLABELin the original code. Before theFIRSTLABELfunction ends, it calls SECONDLABEL, with the statements that followed it.

• Notes:

  • Creating a connector variable con = None, and populating it in the main() 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 theexecfunction provided by the snowconvert.helpers. Take for example the following code:

IN -> Teradata_02.bteq

CREATE TABLE aTable (aColumn BYTEINT);

This is converted to:

OUT -> Teradata_02.py

#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***
 
import os
import sys
import snowconvert.helpers
from snowconvert.helpers import Export
from snowconvert.helpers import exec
from snowconvert.helpers import BeginLoading
con = None
def main():
  snowconvert.helpers.configure_log()
  con = snowconvert.helpers.log_on()
  exec("""
    CREATE OR REPLACE TABLE aTable (
      aColumn BYTEINT
    )
    """)
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

Execute Script Files

Files that contain a user’s BTEQ commands and Teradata SQL statements are called scripts, run files, macros, or stored procedures. For example, create a file called SAMPFILE, and enter the following BTEQ script:

   .LOGON tdpid/userid,password 
   SELECT * FROM department;
   .LOGOFF

To execute the run file, enter either form of the BTEQ RUN command:

IN -> Teradata_03.bteq

.RUN FILE=sampfile

If you convert the second code, the result is the following:

OUT -> Teradata_03.py

#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***
 
import os
import sys
import snowconvert.helpers
from snowconvert.helpers import Export
from snowconvert.helpers import exec
from snowconvert.helpers import BeginLoading
con = None
def main():
  snowconvert.helpers.configure_log()
  con = snowconvert.helpers.log_on()

  for statement in snowconvert.helpers.readrun(fr"sampfile"):
    eval(statement)
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

The snowconvert.helpers.readrun("sampfile") will return each line from the SAMPFILE and in theFORstatement, each one of the lines will be passed to the eval function, a method that parses the expression passed to it and runs python expression (the SAMPFILE should be converted to work) within the program.

Execute SQL Files

In some instances during the execution of a BTEQ file a SQL file can be found, take for example the SQL file called NEWSQL:

IN -> Teradata_04.bteq

CREATE TABLE aTable (aColumn BYTEINT);

This can be executed during a script with the following line:

$(<$NEWSQL)

And after the conversion of the script the result is:

OUT -> Teradata_04.py

#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***
 
import os
import sys
import snowconvert.helpers
from snowconvert.helpers import Export
from snowconvert.helpers import exec
from snowconvert.helpers import BeginLoading
con = None
def main():
  snowconvert.helpers.configure_log()
  con = snowconvert.helpers.log_on()
  exec("""
    CREATE OR REPLACE TABLE aTable (
      aColumn BYTEINT
    )
    """)
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

The exec_file helper function will read each line from the NEWSQL file and then use the exec function as explained in the section Execute query statement.

Known Issues

No issues were found.

Related EWIs

No issues were found.