TPT - Can you load from a file containing multiple record types?

Tools

TPT - Can you load from a file containing multiple record types?

If we have a file which contains multiple record types, each with a different number of fields and record length but identified by the value in the first field, is it possible to load this using TPT?

A simple example would be a file with a header and trailer, eg:

01|20140523

10|ID01|JOHN

10|ID02|PETER

10|ID03|FRANK

10|ID04|MARY

10|ID05|ELLEN

10|ID06|MICHAEL

10|ID07|SAM

10|ID08|JONATHAN

10|ID09|MICHELLE

10|ID10|ALICE

99|10

Could we load the date from record type 01 to STG_HEADER, the ID and username in record type 10 to STG_USER_DETAIL, and the record count in record type 99 to STG_TRAILER?

Our files are more complex than this, but I want to know whether this is possible before I look at splitting the file using operating system commands.

Tags (1)
3 REPLIES
Teradata Employee

Re: TPT - Can you load from a file containing multiple record types?

Yes.

First of all, every record must adhere to a single schema. That is the most important rule to remember.

If the data is in delimited format, then we have a feature whereby short rows (rows with less fields than columns in the schema definition) will have NULL columns added to the end. And if a row has too many fields, then we can truncate the extra ones.

In the APPLY-SELECT statement, in the APPLY section, you can add CASE-WHEN logic so basically do this:

APPLY

CASE WHEN F1 = 10 THEN

INSERT INTO TABLE_A( col-list );

ELSE WHEN F1 = 99 THEN

INSERT INTO TABLE_B( col-list );

END;

You will have to check the documentation for the exact syntax, but it should work.

-- SteveF

Re: TPT - Can you load from a file containing multiple record types?

In this file each record type will have it's own schema.  In the above example record type 01 has 2 fields:

F1 - record_type  - byteint

F2 - created date - date

Record type 10:

F1 - record_type - byteint

F2 - person_id - char(4)

F3 - person_name - varchar(99)

Record type 99:

F1 - record_type - byteint

F2 - record_count - integer

Previously I would have used unix to split the file into 3, one for each record type, then used fastload to load to staging tables, then SQL to insert/update the final tables.

I'm now working on a site that is completely new to teradata so would like to use TPT where possible, rather than using fastload or multiload.  Ideally we would use TPT to run all the processing rather than use operating system specific pre-processing.  Is there any way of doing this in TPT?

I was wondering if you could do this using a filter operator.  i.e. take the file, define it as one single varchar field, split it into 3 data streams based on the first 2 characters of the record, then define each of these streams using a different schema as above.  I'm trying to work out from the manuals (TPT user guide and TPT reference) if this is possible, but there aren't any examples of anything like this.  Has anyone ever done this, or have any examples of anything similar?

Teradata Employee

Re: TPT - Can you load from a file containing multiple record types?

TPT does not support multiple schemas in a single input file.

-- SteveF