Loading a teradata table using multiple files sample script

Tools
Enthusiast

Loading a teradata table using multiple files sample script

Hi All,

 Can anyone please provide me sample  a tpt script or mload script  which can load data from multiple flat files into a teradata table and a sample script to upsert,insert a teradata table from a staging table.

6 REPLIES
Enthusiast

Re: Loading a teradata table using multiple files sample script

Hi,

Refer tpt installation folder, it has sample scripts.

Thanks

Teradata Employee

Re: Loading a teradata table using multiple files sample script

The User Guide will also have the information you need.

-- SteveF
Enthusiast

Re: Loading a teradata table using multiple files sample script

Thanks Harpreetand Feinholz,

I have duplicate rows in my data and the table is not a multiset table. Can we insert duplicate rows into the table.

Enthusiast

Re: Loading a teradata table using multiple files sample script

Hi ,

Below is my script for loading table from 4 flat files.The records are getting inserted into the table but the output says the job terminated with status 4.

Can anyone please help what is the issue.

It takes one and half hour to load 232664603. Can we optimize the script to work faster

DEFINE JOB FILE_LOAD

DESCRIPTION 'Load a Teradata table from a file'

(

DEFINE SCHEMA TABLE

(

 col1  VARCHAR(50),

 col2  VARCHAR(50),

 col3  VARCHAR(10),

 col4  VARCHAR(50),

 col5  VARCHAR(1),

 col6  VARCHAR(3),

 col7  VARCHAR(50),

 col8  VARCHAR(10),

 col9  VARCHAR(2),

 col10  VARCHAR(50),

 col11  VARCHAR(50),

 col12  VARCHAR(19),

 col13  VARCHAR(50),

 col14  VARCHAR(80),

 col15 VARCHAR(3),

 col16  VARCHAR(3),

 col17  VARCHAR(50),

 col18  VARCHAR(10),

 col19  VARCHAR(1)

);

DEFINE OPERATOR DDL_OPERATOR

TYPE DDL

ATTRIBUTES

(

VARCHAR PrivateLogName = 'log',

VARCHAR TdpId = 'tdpid',

VARCHAR UserName = 'user',

VARCHAR UserPassword = 'password',

VARCHAR ErrorList = '3807'

);

DEFINE OPERATOR FILE_READER

TYPE DATACONNECTOR PRODUCER

SCHEMA TABLE

ATTRIBUTES

(

VARCHAR PrivateLogName = 'dataconnector.log',

VARCHAR DirectoryPath = 'DIR',

VARCHAR FileName = '*',

VARCHAR Format = 'Delimited',

VARCHAR OpenMode = 'Read',

VARCHAR TextDelimiter = '^A'

);

DEFINE OPERATOR LOAD_OPERATOR

TYPE LOAD

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'load.log',

VARCHAR TdpId = 'tdpid',

VARCHAR UserName = 'user',

VARCHAR UserPassword = 'password',

VARCHAR TargetTable = 'SCHEMA.TABLE',

VARCHAR LogTable = 'SCHEMA.TABLE_LG'

 );

STEP TABLE

(

 APPLY

(

'INSERT INTO  ' || 'SCHEMA' || '.TABLE(

 col1, 

 col2,

 col3 ,

 col4 ,

 col5 ,

 col6 ,

 col7 ,

 col8  ,

 col9  ,

 col10 ,

 col11 ,

 col12  ,

 col13  ,

 col14  ,

 col15 ,

 col16 ,

 col17 ,

 col18 ,

 col19

)

VALUES

(

:col1, 

: col2,

:col3 ,

:col4 ,

:col5 ,

:col6 ,

:col7 ,

:col8  ,

:col9  ,

:col10 ,

:col11 ,

:col12  ,

:col13  ,

:col14  ,

:col15 ,

:col16 ,

:col17 ,

:col18 ,

:col19

);')

TO OPERATOR (LOAD_OPERATOR())

SELECT * FROM OPERATOR(FILE_READER());

);

);

Output

 LOAD_OPERATOR: Total Rows Sent To RDBMS:      232664603

 LOAD_OPERATOR: Total Rows Applied:            232664603

LOAD_OPERATOR: disconnecting sessions

 FILE_READER: TPT19221 Total files processed: 4.

 LOAD_OPERATOR: Total processor time used = 'could not be determined from o.s.'

 LOAD_OPERATOR: Start : Tue Jul 16 06:48:12 2013

 LOAD_OPERATOR: End   : Tue Jul 16 08:04:31 2013

Job step TABLE terminated (status 4)

Job  completed successfully, but with warning(s).

Enthusiast

Re: Loading a teradata table using multiple files sample script

Duplicate rows can be inserted into NOPI table.

For second question: Try increasing instances for data connector.

Thanks

Harpreet

Enthusiast

Re: Loading a teradata table using multiple files sample script

hi,

can we write generic mload script to load different files sequentially.

Filename are like this: table1_timestamp,table2_timestamp,table3_timestamp,table4_timestamp etc...

i have to use single multiload to load these files into table1,table2 etc.. respectively.

as per my knowledge we can not load through mload. can you please advice me.