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




    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