Import

Translation reference to convert import MLoad to Snowflake SQL

Description

The IMPORT command specifies a source for data input.

For more information regarding Import MLoad, check here

Sample Source Patterns

As BTEQ content also MLoad is relocated in an EXECUTE IMMEDIATE block. Import transformation with take each layout field an added to a select. Inserts in dml label will be transform to COPY INTO and upserts (Update and insert) will be transform to MERGE INTO.

1. DML label with insert

Teradata MLoad

.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;

BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;

.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);

.dml label insert_into_my_table
  IGNORE DUPLICATE INSERT ROWS ;
INSERT INTO my_table(col1, col2) VALUES (:col1, :col2);

.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
  FORMAT UNFORMAT 
  LAYOUT my_layout APPLY insert_into_my_table;

.END MLOAD;
.LOGOFF;

Snowflake SQL

EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT;
  BEGIN
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGTABLE my_table_log;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGON my_teradata_system/username,password;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*BEGIN IMPORT MLOAD TABLES my_table
    WORKTABLES my_table_work*/
     
    /*** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 6 OF THE SOURCE CODE. ***/
    /*--ERRORTABLES my_table_err*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE ***/
    /*.LAYOUT my_layout;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE ***/
    /*.dml label insert_into_my_table
      IGNORE DUPLICATE INSERT ROWS ;*/
     
    BEGIN
      CREATE OR REPLACE STAGE &{stagename};
      PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
      /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
      /*.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
        FORMAT UNFORMAT
        LAYOUT my_layout APPLY insert_into_my_table;*/
       
      COPY INTO my_table (
        col1,
        col2
      )
      FROM
      (
        SELECT DISTINCT
          SUBSTRING($1, 1, 2) col1,
          SUBSTRING($1, 3, 5) col2
        FROM
          @&{stagename}/my_tr_file_1.tr
      );
    END;
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.END MLOAD;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGOFF;*/
     
  END
$$

2. DML label with upsert

Teradata MLoad

.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;

BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;

.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);

.dml label upsert_into_my_table;
UPDATE my_table  
 SET     
  col1 = :col1,     
  col2 = :col2  
 WHERE col2 = :col2 
INSERT INTO my_table (
col1, col2)  
VALUES (:col1, :col2);

.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
  FORMAT UNFORMAT 
  LAYOUT my_layout APPLY upsert_into_my_table;

.END MLOAD;
.LOGOFF;

Snowflake SQL

EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT;
  BEGIN
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGTABLE my_table_log;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGON my_teradata_system/username,password;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*BEGIN IMPORT MLOAD TABLES my_table
    WORKTABLES my_table_work*/
     
    /*** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 6 OF THE SOURCE CODE. ***/
    /*--ERRORTABLES my_table_err*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE ***/
    /*.LAYOUT my_layout;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE ***/
    /*.dml label upsert_into_my_table;*/
     
    BEGIN
      CREATE OR REPLACE STAGE &{stagename};
      PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
      /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
      /*.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
        FORMAT UNFORMAT
        LAYOUT my_layout APPLY upsert_into_my_table;*/
       
      MERGE INTO my_table merge_table
      USING (
        SELECT
          SUBSTRING($1, 1, 2) col1,
          SUBSTRING($1, 3, 5) col2
        FROM
          @&{stagename}/my_tr_file_1.tr
      ) load_temp ON merge_table.col2 = load_temp.col2
      WHEN MATCHED THEN
        UPDATE SET
          merge_table.col1 = load_temp.col1,
          merge_table.col2 = load_temp.col2
      WHEN NOT MATCHED THEN
        INSERT (col1, col2)
        VALUES (load_temp.col1, load_temp.col2);
    END;
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.END MLOAD;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGOFF;*/
     
  END
$$

3. Layout or DML label not found

Teradata MLoad

.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;

BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;

.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);

.dml label insert_into_my_table
  IGNORE DUPLICATE INSERT ROWS ;
INSERT INTO my_table(col1, col2)VALUES (:col1, :col2);

.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
  FORMAT UNFORMAT 
  LAYOUT not_layout APPLY insert_into_my_table;

.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
  FORMAT UNFORMAT 
  LAYOUT my_layout APPLY insert_not_my_table;

.END MLOAD;
.LOGOFF;pl

Snowflake SQL

EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT;
  BEGIN
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGTABLE my_table_log;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGON my_teradata_system/username,password;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*BEGIN IMPORT MLOAD TABLES my_table
    WORKTABLES my_table_work*/
     
    /*** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 6 OF THE SOURCE CODE. ***/
    /*--ERRORTABLES my_table_err*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE ***/
    /*.LAYOUT my_layout;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE ***/
    /*.dml label insert_into_my_table
      IGNORE DUPLICATE INSERT ROWS ;*/
     
     
    BEGIN
      CREATE OR REPLACE STAGE &{stagename};
      PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
      /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
      /*.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
        FORMAT UNFORMAT
        LAYOUT not_layout APPLY insert_into_my_table*/
       
      /*** MSC-WARNING - MSCEWI2088 - LAYOUT not_layout WAS NOT FOUND ***/
       
    END;
    BEGIN
      CREATE OR REPLACE STAGE &{stagename};
      PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
      /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
      /*.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
        FORMAT UNFORMAT
        LAYOUT my_layout APPLY insert_not_my_table*/
       
      /*** MSC-WARNING - MSCEWI2089 - DML LABEL insert_not_my_table WAS NOT FOUND ***/
       
    END;
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.END MLOAD;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGOFF;*/
     
  END
$$

4. Conditions not found in update statement

Teradata MLoad

.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;

BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;

.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);

.dml label upsert_into_my_table;
UPDATE my_table  
 SET     
  col1 = :col1,     
  col2 = :col2
INSERT INTO my_table (
col1, col2)  
VALUES (:col1, :col2);

.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
  FORMAT UNFORMAT 
  LAYOUT my_layout APPLY upsert_into_my_table;

.END MLOAD;
.LOGOFF;

Snowflake SQL

EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT;
  BEGIN
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGTABLE my_table_log;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGON my_teradata_system/username,password;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*BEGIN IMPORT MLOAD TABLES my_table
    WORKTABLES my_table_work*/
     
    /*** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 6 OF THE SOURCE CODE. ***/
    /*--ERRORTABLES my_table_err*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE ***/
    /*.LAYOUT my_layout;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE ***/
    /*.dml label upsert_into_my_table;*/
     
    BEGIN
      CREATE OR REPLACE STAGE &{stagename};
      PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
      /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
      /*.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
        FORMAT UNFORMAT
        LAYOUT my_layout APPLY upsert_into_my_table;*/
        
      /*** MSC-WARNING - MSCEWI2090 - CONDITIONS NOT FOUND IN UPDATE STATEMENT FOR TRANSFORMATION ***/     
        
    END;
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.END MLOAD;*/
     
    /*** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  ***/
    /*.LOGOFF;*/
     
  END
$$

Known Issues

Some statements from import clause where not supported yet:

• AXSMOD statement

• INMODE statement

• FORMAT (only FORMAT UNFORMAT is supported)

• WHERE condition of APPLY label

Related EWIS

1. MSCEWI1002: Removed next statement, not applicable in snowflake.

2. MSCEWI2088: Layout was not found.

3. MSCEWI2089: DML label was not found.

4. MSCEWI2090: Conditions not found in update statement for transformation.