Loading extract using Teradata Multiload

Goal

Load customer extract file using FTP command line script and Teradata MultiLoad tool.
The ftp command line tool will fetch the extract from a source system into an ETL server.
The MultiLoad Job Script will load the script into a Teradata database.

FTP script

An FTP command line script will be used to get the extract file. The get_cust_extract.txt control script has the following contents:

open 10.150.193.128
etltoolsinfouser
dwpwd1
prompt
lcd /dw/input/Dwh_cust_extract.txt
mget Dwh_cust_extract*.txt
quit

Invoke FTP commands in command prompt (Windows or Unix) by issuing the following command: ftp -s:get_cust_extract.txt

Teradata MultiLoad (Mload)

The goal is to create a Teradata MultiLoad job script to import data into an empty table and select data from the table to verify the import task.

The datawarehouse customers extract contains the following records:
The datawarehouse customers extract contains the following records

MultiLoad is a Teradata utility with a purpose to do fast, high-volume loading on multiple tables and views of a Teradata RDBMS.
MultiLoad processes a series of MultiLoad commands and Teradata SQL statements as a batch job script which are used for session control and data transfers.

Among others, the MultiLoad script will use the following statements to load the data:

  • LAYOUT command and the series of FIELD commands specify each field of the data records that are sent to the Teradata database.
  • The DML LABEL command introduces the INSERT statement.
  • IMPORT command starts the import task, by specifying the VARtext format with a field delimiter. It uses the input file parameter and the custdml insert statement. The multiload script is composed of the following statements:
    .LOGTABLE dwlogtable;
    .LOGON tdp1/etltoolsinfo,dwpwd1;
     
    .begin import mload tables customers;
    .layout custlayout;
    .field ID			1 INTEGER;
    .field CUST_ID		* VARCHAR(6);
    .field CUST_NAME 		* VARCHAR(30);
    .field CUST_GROUP		* VARCHAR(30);
    .field CUST_SEGMENT		* VARCHAR(10);
    .field CUST_COUNTRY_ID	* VARCHAR(3);
     
    .dml label custdml;
    insert into customers.*;
     
    .import infile /dw/input/Dwh_cust_extract.txt
    format VARtext ';'
    layout custlayout
    apply custdml;
     
    .end mload;
    .logoff;
    
    To load the extract, the following mload statement needs to be issued:
    mload < load_cust_extract.mload
    

    And the following Teradata BTEQ script is invoked (Basic Teradata Query) to verify the MultiLoad loading process:
     
    bteq
    .logon tdp1/etltoolsinfo,dwpwd1;
    select count(1) CustCnt from customers;
    .quit
    
    The query result will show the number of records in the data warehouse customer table:
    *** Query completed. 1 row found. 1 columns returned.
    *** Total elapsed time was 1 seconds.
    CustCnt
    -----
    22


    Comments

    2009-01-29 08:57:15 by keerthan:
    I would like to know if in a MLOAD script we can do the following in a single script:
    1. Create a table
    2. Load the table from a file.
    3. Insert from that table into another table.
    4. Only do the above insert if records do not exist, if present then do an update.

    Can the above four steps be written in a single MLOAD script.

    2009-02-02 17:40:18 by Data warehouser:
    keerthan,
    you can use SQL statements in the mload script to manipulate the data.
    The task nr 4 in your script will reqiure an UPSERT.
    Teradata uses the following UPSERT syntax: UPDATE SET ... ELSE INSERT ...

    2010-01-19 19:50:42 by pranjali:
    I would like to know one thing with regard to mload script. Can I insert derived fields (calculated) columns? so in insert i would write col names under valus what will be the syntax?

    say insert db.table,
    (col1,
    col2,
    col3,
    col4)
    values (:col1,:col1+col2,
    -----what syntax do i need here.
    :col1+col2-col3);

    Thanks
    2010-09-02 12:53:13 by VADIVU:
    I would like to know one thing about MLOAD. I am trying to run the MLOAD with datatype varchar and
    .IMPORT INFILE X
    AXSMOD LIBMQS '-PARMFILE MQPARM'
    FORMAT VARTEXT ','
    LAYOUT INPUT_LAYOUT
    APPLY INSERT_NEW_ROWS;

    When i try to access with VARTEXT i am getting UTY4015 access Module error 35.
    Inputs are coming from MQ series.

    Can anyone give suggestions?
    2010-11-04 17:08:02 by guruvulu:
    .logon 127.0.0.1/user id,pwd;
    .logtable .tab;
    database ;
    .begin import mload tables ,;
    .layout ;
    .field i_partyid * varchar(5);
    .field i_pname * varchar(10);
    .field i_pincome * varchar(10);
    .field i_mailid * varchar(10);
    .dml label ins_lbl;
    insert into custm1
    (:i_partyid,:i_pname,:i_pincome,:i_mailid);
    .dml label ups_lbl;
    update custm2 set
    custm_name=:i_pname,custm_pay=:i_pincome,custm_mailid=:i_pmailid where
    custm_id=:i_partyid;
    .import infile :\filename.txt
    format vartext ',' layout
    apply ins_lbl apply ups_lbl;
    .end mload;
    .logoff;

    2011-03-09 19:00:47 by malli:
    what is difference?
    sel * from tab A where eid in(sel * from tab B)(or)
    sel* from tab A
    intersect
    sel * from tab B


    2012-01-11 06:00:14 by raam:
    your first query is wrong. That should've been:
    sel * from tab A where eid in(sel eid from tab B)
    -- The query compares only the primary key (eid)

    Your 2nd query just lists the common rows between tab A and tab B (by doing a full comparison (each element-wise) comparison)


    As a result, all the customers records are loaded into the data warehouse.

    Read more about Teradata loading at:
  • Teradata loading tools examples - FastLoad, TPump and Multiload.

  • Loading customers - back to the data extract loading tutorial lesson
    Back to the Data Warehousing tutorial home