FLOAD

Translation references to convert Teradata FLOAD files to Python

Teradata FastLoad is a command‑driven utility for quickly loading large amounts of data in an empty table on a Teradata Database.

In order to simulate the FastLoad functionality for Teradata in Snowflake, FastLoad files and commands are transformed to Python code, similar to the transformations performed for BTEQ and MultiLoad scripts. The generated code uses the Snowflake Python project called snowconvert.helpers which contains the required functions to simulate the FastLoad statements in Snowflake.

FastLoad Commands Translation

Most of the FastLoad commands are considered not relevant in Snowflake, these commands are commented out. Below is the summary list of FastLoad commands and their transformation status into Snowflake:

Teradata FastLoad Command

Transformation Status

Note

AXSMOD

Commented

​

BEGIN LOADING

Transformed

​The node is commented out since the transformation occurs in the related INSERT statement instead.

CLEAR

Commented

​

DATEFORM

Commented

​

DEFINE

Transformed

​

END LOADING

Transformed

​Commented out since is not necessary for the transformation of the BEGIN LOADING.

ERRLIMIT

Commented

​

HELP

Commented

​

HELP TABLE

Commented

​

INSERT

Transformed

Transformed as part of the BEGIN LOADING.

LOGDATA

Commented

​

LOGMECH

Commented

​

LOGOFF

Commented

​

LOGON

Commented

​

NOTIFY

Commented

​

OS

Commented

​

QUIT

Commented

​

RECORD

Commented

​

RUN

Commented

​

SESSIONS

Commented

​

SET RECORD

Transformed

​

SET SESSION CHARSET

Commented

​

SHOW

Commented

​

SHOW VERSIONS

Commented

​

SLEEP

Commented

​

TENACITY

Commented

​

Default Transformation

The default behavior of the ConversionTool for these statements is to comment them out. For example:

Teradata (FastLoad)

IN -> Teradata_01.fload
SESSIONS 4;
ERRLIMIT 25;

Snowflake (Python)

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()
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
  #SESSIONS 4
   
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
  #ERRLIMIT 25
   
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

Nonetheless, there are some exceptions that must be converted to specific Python statements in order to work as intended in Snowflake.

BEGIN LOADING (And related commands)

The transformation for the command BEGIN LOADING is a multi-part transformation that requires the DEFINE, INSERT and (optionally) SET RECORD commands to simulate its behavior correctly.

This transformation is fully explained in this section.

SET RECORD

As stated above, this command is not required for the transformation of the BEGIN LOADING. If not found, the default delimiter will be set to ',' (comma). Else, the defined delimiter will be used.

Teradata (FastLoad)

IN -> Teradata_02.fload
BEGIN LOADING FastTable ERRORFILES Error1,Error2
   CHECKPOINT 10000;

Snowflake (Python)

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()
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELOW **
  #BEGIN LOADING FastTable ERRORFILES Error1, Error2 CHECKPOINT 10000
   
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

In the example above, FastTable is the name of the table associated to the BEGIN LOADING command. Note the use of the python variableinputDataPlaceholder, that must be defined by the user in a previous step. The value represents the Snowflake stage that could be internal or external as shown in the following table or as explained here.

Stage

Input Data Place Holder

Internal stage

@my_int_stage

External stage

@my_int_stage/path/file.csv

Amazon S3 bucket

s3://mybucket/data/files

Google Cloud Storage

gcs://mybucket/data/files

Microsoft Azure

azure://myaccount.blob.core.windows.net/mycontainer/data/files

Embedded SQL

FastLoad scripts support Teradata statements inside the same file. The majority of these statements are converted just as if they were inside a BTEQ file, with some exceptions.

Dropping an error table is commented out if inside a FastLoad file.

Teradata (FastLoad)

IN -> Teradata_03.fload
DROP TABLE Error1;
DROP TABLE Error2;

Snowflake (Python)

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()
  exec("""
    DROP TABLE Error1
    """)
  exec("""
    DROP TABLE Error2
    """)
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

Large Example

Given the transformations shown above for a variety of commands, consider the following example.

Teradata (FastLoad)

IN -> Teradata_04.fload
SESSIONS 4;
ERRLIMIT 25;
DROP TABLE FastTable;
DROP TABLE Error1;
DROP TABLE Error2;
CREATE TABLE FastTable, NO FALLBACK
   ( ID INTEGER, UFACTOR INTEGER, MISC CHAR(42))
   PRIMARY INDEX(ID);
DEFINE ID (INTEGER), UFACTOR (INTEGER), MISC (CHAR(42))
   FILE=FileName;
SHOW;
BEGIN LOADING FastTable ERRORFILES Error1,Error2
   CHECKPOINT 10000;
INSERT INTO FastTable (ID, UFACTOR, MISC) VALUES
   (:ID, :MISC);
END LOADING;

Snowflake (Python)

OUT -> Teradata_04.py
#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***
#** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "Error1", "Error2" **
 
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()
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
  #SESSIONS 4
   
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
  #ERRLIMIT 25
   
  exec("""
    DROP TABLE FastTable
    """)
  exec("""
    CREATE TABLE FastTable (
      ID INTEGER,
      UFACTOR INTEGER,
      MISC CHAR(42)
    )
    """)
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELOW AS ASSIGNMENT STATEMENTS **
  #DEFINE ID (INTEGER), UFACTOR (INTEGER), MISC (CHAR(42)) FILE = FileName
   
  ssc_define_columns = "ID (INTEGER), UFACTOR (INTEGER), MISC (CHAR(42))"
  #Set file name manually if empty
  ssc_define_file = f"""FileName"""
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
  #SHOW
   
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELOW **
  #BEGIN LOADING FastTable ERRORFILES Error1, Error2 CHECKPOINT 10000
   
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELOW AS PART OF THE BEGIN LOADING TRANSLATION **
  #INSERT INTO FastTable (ID, UFACTOR, MISC) VALUES (:ID, :MISC)
   
  ssc_begin_loading_columns = "(ID, UFACTOR, MISC)"
  ssc_begin_loading_values = [":ID", ":MISC"]
  BeginLoading.import_file_to_table(f"""FastTable""", ssc_define_columns, ssc_define_file, ssc_begin_loading_columns, ssc_begin_loading_values, ",")
  #** SSC-EWI-0002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. END LOADING **
  #END LOADING
   
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

If you have any additional questions regarding this documentation, you can email us at [email protected].

Related EWIs

  • SSC-EWI-TD0022: Commented out code related with dropping work or error table.

  • SSC-EWI-0002: Removed next statement, not applicable in SnowFlake.

PreviousUSING REQUEST MODIFIERNextBEGIN LOADING

Last updated