BEGIN LOADING

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 NONE LEADING 'p';
SET RECORD VARTEXT 'l' TRIM NONE TRAILING 'p';
SET RECORD VARTEXT 'l' TRIM NONE BOTH 'p';
SET RECORD FORMATTED TRIM NONE BOTH;
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)

DEFINE
FILE=/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,Error2
   CHECKPOINT 10000;

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.

Teradata (FastLoad)

INSERT INTO TESTS.EmpLoad4 (col2, col3, col1, col4)	
VALUES
(
    :_col2,
    :_col3,
    :_col1,
    CURRENT_DATE
);

Snowflake (Python)

#** 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:

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

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:

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,
  input_data_place_holder="s3://mybucket/data/files")

END LOADING

The END LOADING command is commented out since is not necessary for the transformation of the BEGIN LOADING.

Teradata (FastLoad)

END LOADING;

Snowflake (Python)

#** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. END LOADING **
#END LOADING

Known Issues

1. BINARY and TEXT keyword specification not supported

The BINARY and TEXT keyword specification for the SET RECORD command are not yet supported.

2. Only base specification for VARTEXT is supported

Extra specifications for the SET RECORD VARTEXT such as TRIM or QUOTE are not yet supported.

MSCEWI1002: Removed next statement, not applicable in SnowFlake.
MSCEWI1021: Not Supported

PreviousFLOAD NextMLOAD

Last updated 2 years ago

Was this helpful?

SET RECORD

DEFINE

BEGIN LOADING

INSERT

END LOADING

Known Issues

Related EWIs