BEGIN MLOAD
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.
Teradata (MultiLoad)
.LAYOUT INFILE_LAYOUT;
.FIELD TABLE_ID * INTEGER;
.FIELD TABLE_DESCR * CHAR(8);
.FILLER COL1 * CHAR(1);
.FIELD TABLE_NBR * SMALLINT;
.FIELD TABLE_SOMEFIELD * SMALLINT;
Snowflake (Python)
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"""
.DML LABEL
The transformation for the .DML LABEL
command 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)
-- 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;
DELETE FROM Employee WHERE EmpNo = :EmpNo;
-- Example of .DML LABEL with an UPDATE, followed by an INSERT:
.DML LABEL UPSERT_TABLE DO INSERT FOR MISSING UPDATE ROWS;
UPDATE mydb.mytable SET TABLE_ID = :TABLE_ID WHERE TABLE_DESCR = :somedescription
INSERT INTO mydb.mytable(TABLE_ID, TABLE_DESCR, TABLE_NBR) VALUES(:TABLE_ID, :TABLE_DESCR, :TABLE_NBR );
Snowflake (Python)
def INSERT_TABLE(tempTableName, queryConditions = ""):
exec(f"""INSERT INTO mydb.mytable (TABLE_ID, TABLE_DESCR, TABLE_NBR)
SELECT
SRC.TABLE_ID,
SRC.TABLE_DESCR,
SRC.TABLE_NBR
FROM {tempTableName} SRC {queryConditions}""")
exec("""
DELETE FROM
Employee
WHERE
EmpNo = :EmpNo
""")
def UPSERT_TABLE(tempTableName, queryConditions = ""):
exec(f"""MERGE INTO mydb.mytable TGT USING (SELECT * FROM {tempTableName} {queryConditions}) SRC ON TGT.TABLE_DESCR = :somedescription
WHEN MATCHED THEN UPDATE SET
TGT.TABLE_ID = SRC.TABLE_ID
WHEN NOT MATCHED THEN INSERT (TGT.TABLE_ID, TGT.TABLE_DESCR, TGT.TABLE_NBR)
VALUES (SRC.TABLE_ID, SRC.TABLE_DESCR, SRC.TABLE_NBR)""")
.IMPORT
The transformation of the .IMPORT
command will create a call to theimport_file_to_temptable
helper to load the data from the file to a temporary table. Then, the calls to all theAPPLY
labels used in the original import will be created. Finally, the calls for anINSERT
label will be transformed to a query parameter and optionally can have a query condition.
Teradata (MultiLoad)
.IMPORT INFILE INFILE_FILENAME
LAYOUT INFILE_LAYOUT
APPLY INSERT_TABLE
APPLY UPSERT_TABLE
Apply DELETE_TABLE;
Snowflake (Python)
snowconvert.helpers.import_file_to_temptable(fr"INFILE_FILENAME", INFILE_LAYOUT_TableName, INFILE_LAYOUT_Columns, INFILE_LAYOUT_Conditions, ',')
INSERT_TABLE(INFILE_LAYOUT_TableName)
UPSERT_TABLE(INFILE_LAYOUT_TableName)
DELETE_TABLE(INFILE_LAYOUT_TableName)
exec(f"""DROP TABLE {INFILE_LAYOUT_TableName}""")
Large Example
Given the transformations shown above for a variety of commands, consider the following example.
With this input data:
id,name,age
1,John,25
2,Maria,29
3,Carlos,31
4,Mike,40
5,Laura,27
Teradata (MultiLoad)
.begin import mload
tables
mySampleTable1
sessions 20
ampcheck none;
.layout myLayOut;
.field ID * VARCHAR(2) NULLIF ID = '1';
.field NAME * VARCHAR(25);
.field AGE * VARCHAR(10);
.dml label insert_data;
INSERT INTO mySampleTable1
(
ID,
NAME,
AGE
)
VALUES
(
:ID,
SUBSTRING(:NAME FROM 2),
:AGE
);
.import infile sampleData.txt
layout myLayOut
apply insert_data
.end mload;
.logoff;
Snowflake (Python)
#*** 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()
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. **
#.begin import mload tables mySampleTable1 sessions 20 ampcheck none
myLayOut_TableName = "myLayOut_TEMP_TABLE"
myLayOut_Columns = """ID VARCHAR(2),
NAME VARCHAR(25),
AGE VARCHAR(10)"""
myLayOut_Conditions = """CASE
WHEN ID = '1'
THEN NULL
ELSE ID
END AS ID, NAME AS NAME, AGE AS AGE"""
def insert_data(tempTableName, queryConditions = ""):
exec(f"""INSERT INTO mySampleTable1 (ID, NAME, AGE)
SELECT
SRC.ID,
SUBSTRING(SRC.NAME, 2),
SRC.AGE
FROM {tempTableName} SRC {queryConditions}""")
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELOW **
#.import infile sampleData.txt layout myLayOut apply insert_data
snowconvert.helpers.import_file_to_temptable(fr"sampleData.txt", myLayOut_TableName, myLayOut_Columns, myLayOut_Conditions, ',')
insert_data(myLayOut_TableName)
exec(f"""DROP TABLE {myLayOut_TableName}""")
if con is not None:
con.close()
con = None
snowconvert.helpers.quit_application()
if __name__ == "__main__":
main()
Known Issues
1. Delete statement is partially supported
The DELETE
statement is partially supported since the where conditions, when found, are not being converted correctly if pointing to a LAYOUT
defined column.
In the example below, :EmpNo
is pointing to a LAYOUT
defined column. However, the transformation does not take this into account and thus the code will be referencing a column that does not exists.
exec("""
DELETE FROM
Employee
WHERE
EmpNo = :EmpNo
""")
If you have any additional questions regarding this documentation, you can email us at [email protected].
Last updated
Was this helpful?