Translation specification for the BEGIN MLOAD and related commands.
The transformation for the command .BEGIN MLOAD is a multi-part transformation that requires the .LAYOUT, .FIELD, .FILLER,.DML LABEL, and .IMPORT commands to simulate its behavior correctly.
This transformation is fully explained in the following subsections.
.LAYOUT, .FIELD and .FILLER
The transformation for the commands .LAYOUT, .FIELD, and .FILLER will create variable definitions to be used in a future function call of the IMPORT of this layout.
#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***import osimport sysimport snowconvert.helpersfrom snowconvert.helpers import Exportfrom snowconvert.helpers importexecfrom snowconvert.helpers import BeginLoadingcon =Nonedefmain(): snowconvert.helpers.configure_log() con = snowconvert.helpers.log_on() INFILE_LAYOUT_TableName ="INFILE_LAYOUT_TEMP_TABLE" INFILE_LAYOUT_Columns ="""TABLE_ID INTEGER, TABLE_DESCR CHAR(8), COL1 CHAR(1), TABLE_NBR SMALLINT, TABLE_SOMEFIELD SMALLINT""" INFILE_LAYOUT_Conditions = """TABLE_ID AS TABLE_ID, TABLE_DESCR AS TABLE_DESCR, COL1 AS COL1, TABLE_NBR AS TABLE_NBR, TABLE_SOMEFIELD AS TABLE_SOMEFIELD"""
snowconvert.helpers.quit_application()if__name__=="__main__":main()
.DML LABEL
The transformation for the .DML LABELcommand will create a function containing the statements after the label definition. Note that after the .DML LABEL command there is usually an Insert, Update or Delete.
Teradata (MultiLoad)
IN -> Teradata_02.mload
-- Example of .DML LABEL with INSERT:.DML LABEL INSERT_TABLE; INSERT INTO mydb.mytable( TABLE_ID,TABLE_DESCR,TABLE_NBR ) VALUES( :TABLE_ID,:TABLE_DESCR,:TABLE_NBR );-- Example of .DML LABEL with DELETE:.DML LABEL DELETE_TABLE; DELETEFROM Employee WHERE EmpNo = :EmpNo;-- Example of .DML LABEL with an UPDATE, followed by an INSERT:.DML LABEL UPSERT_TABLE DO INSERTFOR MISSING UPDATEROWS;UPDATE mydb.mytable SET TABLE_ID = :TABLE_ID WHERE TABLE_DESCR = :somedescriptionINSERT INTO mydb.mytable(TABLE_ID, TABLE_DESCR, TABLE_NBR) VALUES(:TABLE_ID, :TABLE_DESCR, :TABLE_NBR );
Snowflake (Python)
OUT -> Teradata_02.py
#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***import osimport sysimport snowconvert.helpersfrom snowconvert.helpers import Exportfrom snowconvert.helpers importexecfrom snowconvert.helpers import BeginLoadingcon =Nonedefmain(): snowconvert.helpers.configure_log() con = snowconvert.helpers.log_on()defINSERT_TABLE(tempTableName,queryConditions=""):exec(f"""INSERT INTO mydb.mytable (TABLE_ID, TABLE_DESCR, TABLE_NBR)SELECT :TABLE_ID, :TABLE_DESCR, :TABLE_NBRFROM {tempTableName} SRC {queryConditions}""")exec(""" DELETE FROM Employee WHERE EmpNo = :EmpNo """)defUPSERT_TABLE(tempTableName,queryConditions=""): exec(f"""MERGE INTO mydb.mytable TGT USING (SELECT * FROM {tempTableName} {queryConditions}) SRC ON TABLE_DESCR = :somedescription
WHEN MATCHED THEN UPDATE SET TABLE_ID =:TABLE_IDWHEN NOT MATCHED THEN INSERT (TABLE_ID, TABLE_DESCR, TABLE_NBR)VALUES (:TABLE_ID, :TABLE_DESCR, :TABLE_NBR)""") snowconvert.helpers.quit_application()if __name__ == "__main__": main()
.IMPORT
The transformation of the .IMPORT command will create a call to theimport_file_to_temptablehelper to load the data from the file to a temporary table. Then, the calls to all theAPPLYlabels used in the original import will be created. Finally, the calls for anINSERTlabel will be transformed to a query parameter and optionally can have a query condition.