|
ETL-Tools.Info
|
Business Intelligence - Data warehousing - ETL |
|
Loading extract using Teradata Multiload
Goal
Load customer extract file using FTP command line script and Teradata MultiLoad tool. 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:
MultiLoad is a Teradata utility with a purpose to do fast, high-volume loading on multiple tables and views of a Teradata RDBMS. .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 Comments2009-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 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 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 Add a commentAs a result, all the customers records are loaded into the data warehouse. Read more about Teradata loading at:
|
|
All Rights Reserved |
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.