SnowConvert AI - Teradata - MLOAD

Teradata MLOAD ファイルを Snowflake SQL に変換するための変換リファレンス

インポート

説明

IMPORT コマンドはデータ入力のソースを指定します。

MLoad のインポートに関する詳しい情報は こちら をご覧ください

サンプルソースパターン

BTEQ のコンテンツとして、 MLoad も EXECUTE IMMEDIATE ブロックに再配置されます。各レイアウトフィールドをselectに追加するインポート変換。dmlラベルへの挿入は COPY INTO に変換され、upsert（更新と挿入）は MERGE INTO に変換されます。

1.挿入を含む DML ラベル

Teradata MLoad
-- Additional Params: -q SnowScript
.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;
Copy
Snowflake SQL
EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN
    -- Additional Params: -q SnowScript
    /*.LOGTABLE my_table_log;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGON my_teradata_system/username,password;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*BEGIN IMPORT MLOAD TABLES my_table
    WORKTABLES my_table_work*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '7' COLUMN '1' OF THE SOURCE CODE STARTING AT 'ERRORTABLES'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'my_table_err' ON LINE '7' COLUMN '13'. CODE '81'. ***/
    /*--ERRORTABLES my_table_err*/
     
    /*.LAYOUT my_layout;*/
    --** SSC-FDM-0027 - 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 ;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
     
     
    BEGIN
      CREATE OR REPLACE STAGE &{stagename};
      PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
       
      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;
    /*.END MLOAD;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGOFF;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
  END
$$
Copy
2.upsertを含む DML ラベル
Teradata MLoad
-- Additional Params: -q SnowScript
.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;
Copy
Snowflake SQL
EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN
    -- Additional Params: -q SnowScript
    /*.LOGTABLE my_table_log;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGON my_teradata_system/username,password;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*BEGIN IMPORT MLOAD TABLES my_table
    WORKTABLES my_table_work*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '7' COLUMN '1' OF THE SOURCE CODE STARTING AT 'ERRORTABLES'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'my_table_err' ON LINE '7' COLUMN '13'. CODE '81'. ***/
    /*--ERRORTABLES my_table_err*/
     
    /*.LAYOUT my_layout;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
     
    /*.dml label upsert_into_my_table;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
     
     
     
    BEGIN
      CREATE OR REPLACE STAGE &{stagename};
      PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
       
      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;
    /*.END MLOAD;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGOFF;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
  END
$$
Copy
3.レイアウトまたは DML ラベルが見つかりません
Teradata MLoad
-- Additional Params: -q SnowScript
.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
Copy
Snowflake SQL
EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN
    -- Additional Params: -q SnowScript
    /*.LOGTABLE my_table_log;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGON my_teradata_system/username,password;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*BEGIN IMPORT MLOAD TABLES my_table
    WORKTABLES my_table_work*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '7' COLUMN '1' OF THE SOURCE CODE STARTING AT 'ERRORTABLES'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'my_table_err' ON LINE '7' COLUMN '13'. CODE '81'. ***/
    /*--ERRORTABLES my_table_err*/
     
    /*.LAYOUT my_layout;*/
    --** SSC-FDM-0027 - 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 ;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
     
     
     
--      .IMPORT INFILE C:\USER\user\my_tr_file_1.tr FORMAT UNFORMAT LAYOUT not_layout APPLY insert_into_my_table
                                                                                                              ;
    BEGIN
      CREATE OR REPLACE STAGE &{stagename};
      PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
       
    END;
    /*.END MLOAD;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGOFF;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '26' COLUMN '9' OF THE SOURCE CODE STARTING AT 'pl'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'pl' ON LINE '26' COLUMN '9'. CODE '81'. ***/
    /*--pl*/
     
  END
$$
Copy
4.更新ステートメントにない条件
Teradata MLoad
-- Additional Params: -q SnowScript
.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;
Copy
Snowflake SQL
EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN
    -- Additional Params: -q SnowScript
    /*.LOGTABLE my_table_log;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGON my_teradata_system/username,password;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*BEGIN IMPORT MLOAD TABLES my_table
    WORKTABLES my_table_work*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '7' COLUMN '1' OF THE SOURCE CODE STARTING AT 'ERRORTABLES'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'my_table_err' ON LINE '7' COLUMN '13'. CODE '81'. ***/
    /*--ERRORTABLES my_table_err*/
     
    /*.LAYOUT my_layout;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
     
    /*.dml label upsert_into_my_table;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
     
     
     
     
--      .IMPORT INFILE C:\USER\user\my_tr_file_1.tr FORMAT UNFORMAT LAYOUT my_layout APPLY upsert_into_my_table
                                                                                                             ;
    /*.END MLOAD;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGOFF;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
  END
$$
Copy

既知の問題

import句のいくつかのステートメントはまだサポートされていません。

  • AXSMOD ステートメント

  • INMODE ステートメント

  • FORMAT （FORMAT UNFORMAT のみがサポートされています）

  • APPLY ラベルの WHERE 条件

言語: 日本語