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
Transformed
The node is commented out since the transformation occurs in the related INSERT statement instead.
CLEAR
Commented
DATEFORM
Commented
Transformed
Commented out since is not necessary for the transformation of the BEGIN LOADING.
ERRLIMIT
Commented
HELP
Commented
HELP TABLE
Commented
LOGDATA
Commented
LOGMECH
Commented
LOGOFF
Commented
LOGON
Commented
NOTIFY
Commented
OS
Commented
QUIT
Commented
RECORD
Commented
RUN
Commented
SESSIONS
Commented
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)
SESSIONS 4;
ERRLIMIT 25;
Snowflake (Python)
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. SESSIONS 4 **
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. ERRLIMIT 25 **
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)
BEGIN LOADING FastTable ERRORFILES Error1,Error2
CHECKPOINT 10000;
Snowflake (Python)
exec(f"""COPY INTO FastTable FROM {inputDataPlaceholder} ON_ERROR = CONTINUE""")
exec("""CREATE TABLE CTE_FastTable AS SELECT DISTINCT * FROM FastTable""")
exec("""DROP TABLE FastTable""")
exec("""ALTER TABLE CTE_FastTable RENAME TO FastTable""")
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)
DROP TABLE Error1;
DROP TABLE Error2;
Snowflake (Python)
#** MSC-WARNING - MSCEWI2022 - COMMENTED OUT CODE RELATED WITH DROPPING WORK OR ERROR TABLE.
#DROP TABLE Error1 **
#** MSC-WARNING - MSCEWI2022 - COMMENTED OUT CODE RELATED WITH DROPPING WORK OR ERROR TABLE.
#DROP TABLE Error2 **
Large Example
Given the transformations shown above for a variety of commands, consider the following example.
Teradata (FastLoad)
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)
#*** Generated code is based on the SnowConvert Python Helpers version 2.0.* ***
import os
import sys
import snowconvert.helpers
from snowconvert.helpers import Export
from snowconvert.helpers import exec
con = None
def main():
snowconvert.helpers.configure_log()
con = snowconvert.helpers.log_on()
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. SESSIONS 4 **
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. ERRLIMIT 25 **
exec("""DROP TABLE PUBLIC.FastTable""")
#** MSC-WARNING - MSCEWI2022 - COMMENTED OUT CODE RELATED WITH DROPPING WORK OR ERROR TABLE.
#DROP TABLE Error1 **
#** MSC-WARNING - MSCEWI2022 - COMMENTED OUT CODE RELATED WITH DROPPING WORK OR ERROR TABLE.
#DROP TABLE Error2 **
exec("""CREATE TABLE PUBLIC.FastTable
(
ID INTEGER,
UFACTOR INTEGER,
MISC CHAR(42))""")
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. DEFINE ID (INTEGER), UFACTOR (INTEGER), MISC (CHAR(42))
# FILE=FileName **
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. SHOW **
exec(f"""COPY INTO FastTable FROM {inputDataPlaceholder} ON_ERROR = CONTINUE""")
exec("""CREATE TABLE CTE_FastTable AS SELECT DISTINCT * FROM FastTable""")
exec("""DROP TABLE FastTable""")
exec("""ALTER TABLE CTE_FastTable RENAME TO FastTable""")
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. 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].
Last updated
Was this helpful?