Translation specification for the BEGIN LOADING command.
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 the following subsections.
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. This value is stored in the ssc_set_record variable.
As of now only SET RECORD VARTEXT, SET RECORD FORMATTED and SET RECORD UNFORMATTED are supported. For the BINARY and TEXT keyword specification an error EWI is placed instead.
Teradata (FastLoad)
SET RECORD VARTEXT DELIMITER 'c' DISPLAY ERRORS 'efilename';SET RECORD VARTEXT 'l''c' NOSTOP;SET RECORD VARTEXT 'l' TRIM NONELEADING'p';SET RECORD VARTEXT 'l' TRIM NONETRAILING'p';SET RECORD VARTEXT 'l' TRIM NONEBOTH'p';SET RECORD FORMATTED TRIM NONEBOTH;SET RECORD UNFORMATTED QUOTE NO OPTIONAL;SET RECORD BINARY QUOTE NO YES 'q';SET RECORD TEXT QUOTE OPTIONAL;
Snowflake (Python)
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS AN ASSIGNMENT STATEMENT **
#SET RECORD VARTEXT DELIMITER 'c' DISPLAY ERRORS 'efilename'ssc_set_record =""#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS AN ASSIGNMENT STATEMENT **
#SET RECORD VARTEXT 'l' 'c' NOSTOP ssc_set_record ="'l'"#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS AN ASSIGNMENT STATEMENT **
#SET RECORD VARTEXT 'l' TRIM NONE LEADING 'p' ssc_set_record ="'l'"#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS AN ASSIGNMENT STATEMENT **
#SET RECORD VARTEXT 'l' TRIM NONE TRAILING 'p' ssc_set_record ="'l'"#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS AN ASSIGNMENT STATEMENT **
#SET RECORD VARTEXT 'l' TRIM NONE BOTH 'p' ssc_set_record ="'l'"#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS AN ASSIGNMENT STATEMENT **
#SET RECORD FORMATTED TRIM NONE BOTH ssc_set_record =","#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS AN ASSIGNMENT STATEMENT **
#SET RECORD UNFORMATTED QUOTE NO OPTIONAL ssc_set_record ="UNFORMATTED"#** MSC-ERROR - MSCEWI1021 - 'BINARY' KEYWORD SPECIFICATION FOR SET RECORD NOT SUPPORTED **#SET RECORD BINARY QUOTE NO YES 'q'#** MSC-ERROR - MSCEWI1021 - 'TEXT' KEYWORD SPECIFICATION FOR SET RECORD NOT SUPPORTED **#SET RECORD TEXT QUOTE OPTIONAL
DEFINE
The transformation for the DEFINE command sets the ssc_define_columns and ssc_define_file variables with the value of the columns definition and the file path to be used in the BEGIN LOADING transformation respectively.
Teradata (FastLoad)
DEFINE id (INTEGER), first_name (VARCHAR(50)), last_name (VARCHAR(50)), salary (FLOAT)FILE=/tmp/inputData.txt;DEFINE id (INTEGER), first_name (VARCHAR(50)), last_name (VARCHAR(50)), salary (FLOAT)DEFINEFILE=/tmp/inputData.txt;DEFINE;
Snowflake (Python)
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS ASSIGNMENT STATEMENTS **
#DEFINE id (INTEGER), first_name (VARCHAR(50)), last_name (VARCHAR(50)), salary (FLOAT) FILE = /tmp/inputData.txt ssc_define_columns ="id (INTEGER), first_name (VARCHAR(50)), last_name (VARCHAR(50)), salary (FLOAT)"'''Set file name manually if empty'''ssc_define_file =f"""/tmp/inputData.txt"""#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS ASSIGNMENT STATEMENTS **
#DEFINE id (INTEGER), first_name (VARCHAR(50)), last_name (VARCHAR(50)), salary (FLOAT) ssc_define_columns ="id (INTEGER), first_name (VARCHAR(50)), last_name (VARCHAR(50)), salary (FLOAT)"'''Set file name manually if empty'''ssc_define_file =f""""""#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS ASSIGNMENT STATEMENTS **
#DEFINE FILE = /tmp/inputData.txt ssc_define_columns =""'''Set file name manually if empty'''ssc_define_file =f"""/tmp/inputData.txt"""#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS ASSIGNMENT STATEMENTS **
#DEFINE ssc_define_columns =""'''Set file name manually if empty'''ssc_define_file =f""""""
BEGIN LOADING
The BEGIN LOADING command is commented out since the relevant information for the transformation is found in the associated INSERT statement instead.
ERRORFILES, NODROP, CHECKPOINT, INDICATORS and DATAENCRYPTION specifications are not necessary for the transformation and thus commented out.
Teradata (FastLoad)
BEGIN LOADING FastTable ERRORFILES Error1,Error2CHECKPOINT10000;
Snowflake (Python)
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW **#BEGIN LOADING FastTable ERRORFILES Error1, Error2 CHECKPOINT 10000
INSERT
The transformation for the associated INSERT statement sets the value for the ssc_begin_loading_columns and ssc_begin_loading_values variables, used to determine the order in which to insert the values to be loaded.
Finally, these variables and the ones described in the above sections are used to call the the BeginLoading.import_file_to_table function part of the SnowConvert.Helpers module. This function simulates the behavior of the whole FastLoad BEGIN LOADING process. To learn more about this function check here.
#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELLOW AS PART OF THE BEGIN LOADING TRANSLATION **
#INSERT INTO TESTS.EmpLoad4 (col2, col3, col1, col4) VALUES (:_col2, :_col3, :_col1, CURRENT_DATE)ssc_begin_loading_columns ="(col2, col3, col1, col4)"ssc_begin_loading_values = [":_col2",":_col3",":_col1","CURRENT_DATE"]BeginLoading.import_file_to_table(f"""TESTS.EmpLoad4""", ssc_define_columns, ssc_define_file, ssc_begin_loading_columns, ssc_begin_loading_values, ssc_set_record)
Internally, the import_file_to_table function creates a temporary stage and puts the local file in the stage to load into the specified table. However, the file might be already stored in one the supported cloud provider by Snowflake:
If this is the case, please manually add the additional parameter input_data_place_holder="<cloud_provider_path>" in the import_file_to_table function. For example: