TPT - Assigning default values for columns not present in a file via TPT to load a table

Tools & Utilities
Enthusiast

TPT - Assigning default values for columns not present in a file via TPT to load a table

Hi,

I am trying to use TPT utility to load data from a file into a table. I create INPUT, SELECT and INSERT schemas a part of this and it usually loads the file into the table without any issues.

Now, I have to load a couple of columns (audit fields) that are not in the file into the table with DEFAULT values. I would have to default columns CURRENT_DATE as column A and CURRENT_TIME as column B which are not there in the file but are present in the table.

I defined my input schema as per the source file metadata. I defined CURRENT_DATE as A and CURRENT_TIME as B in the SELECT schema in addition to the original columns. I mentioned the new fields in the INSERT schema as well.

But it still gives the below error. I cannot define column A in the input schema as its not there in the file.

Error Message :

Undefined derived column name: 'A' must be defined in a job schema.

 Specification is rejected.

Compilation failed due to errors. Execution Plan was not generated.

Job script compilation failed.

Any suggestions on how to assign default values not in file to a table via TPT would help.

3 REPLIES
Highlighted
Teradata Employee

Re: TPT - Assigning default values for columns not present in a file via TPT to load a table

Yes, it is possible. However, it depends on what you want to assign to these new columns.

I will give you the basic method and you can adjust as you need.

Let's assume for a minute that your target table has 4 columns, but your input file has 3 columns.

You need to have a schema object that defines your input file.

You then need a dummy schema for the column(s) you want to add, so that TPT will know the data type of the column.

DEFINE SCHEMA some_schema
(
F1 INTEGER,
F2 CHAR(10),
F3 VARCHAR(20)
);

DEFINE SCHEMA extra_column
(
F4 CHAR(5)
);

. . . .

APPLY
<some DML>
TO OPERATOR ( <some operator )
SELECT F1, F2, F3, 'abcd' AS F4
FROM OPERATOR ( <some file reader> );

You use the SELECT statement (in the APPLY-SELECT) to construct the new overall schema that will be passed to the operator that will be sending the data to Teradata.

If Teradata will be assigning the default value, then you just put the new columns in the list.

If you want to assign a hardcoded value to the column value, then my example above shows you how.

TPT does not currently have the ability to assign "current date" and "current time" type of values.

Hope this helps.

-- SteveF
Fan

Re: TPT - Assigning default values for columns not present in a file via TPT to load a table

Hello,

I´m not familiar with TPT, so I´m having some issues with it. Someone can help ?

I´m trying to make some transformations on data without success. This is my code:

DEFINE JOB CCONT_PSSOA
DESCRIPTION 'LOADS SAMPLE'
(

/**** Schema Definition ****/

DEFINE SCHEMA STG
DESCRIPTION 'STG TBL'
(
IN_ID VARCHAR(50),      
IN_ATR1 VARCHAR(50),   
IN_ATR2 VARCHAR(50),   
IN_BUSS_DT VARCHAR(50),
IN_VRSION_CD VARCHAR(50),    
IN_RTRNB_IND VARCHAR(50),
IN_MAP VARCHAR(50)
);

DEFINE SCHEMA WRK
DESCRIPTION 'WRK TBL'
(
ID INTEGER,      
ATR1 VARCHAR(50),   
ATR2 VARCHAR(50),   
BUSS_DT TIMESTAMP,    
ST_DT TIMESTAMP,  
END_DT TIMESTAMP,   
LD_DT TIMESTAMP,
VRSION_CD BYTEINT, 
RTRNB_IND CHAR(1),
MAP VARCHAR(50),
UPD_TYPE_CD CHAR(1)
);
  
DEFINE OPERATOR DDL_OPERATOR()
DESCRIPTION 'DDL'
TYPE DDL
ATTRIBUTES
(
VARCHAR TdpId = 'localhost',
VARCHAR UserName = 'dbc',
VARCHAR UserPassword = 'dbc',
VARCHAR PrivateLogName = 'ddl.log'
);

/**** Reader Operator Definition ****/

DEFINE OPERATOR FILE_READER()
DESCRIPTION 'FILE READER'
TYPE DATACONNECTOR PRODUCER
SCHEMA STG
ATTRIBUTES
(
VARCHAR PrivateLogName = 'log_stg1.log',
VARCHAR FileName = 'tst*',
VARCHAR OpenMode = 'Read',
VARCHAR DirectoryPath = '/home/project/processing',
VARCHAR ArchiveDirectoryPath = '/home/project/loaded',
VARCHAR IndicatorMode,
VARCHAR Format = 'Delimited',
VARCHAR TraceLevel = 'NONE',
INTEGER VigilMaxFiles,
INTEGER VigilStartTime,
INTEGER VigilStopTime,
INTEGER VigilWaitTime = 15, /* scan interval in seconds */
INTEGER VigilElapsedTime = 1 /* Duration of the job in minutes (or use VigilStartTime and VigilStopTime) */
);

DEFINE OPERATOR TO_STG()
DESCRIPTION 'Populates Stage from File'
TYPE LOAD
SCHEMA STG
ATTRIBUTES
(
INTEGER MaxSessions = 4,
INTEGER MinSessions = 1,
INTEGER ErrorLimit = 1,
VARCHAR PrivateLogName = 'log_stg2.log',
VARCHAR TdpId = 'localhost',
VARCHAR UserName = 'dbc',
VARCHAR UserPassword = 'dbc',
VARCHAR AccountID,
VARCHAR TargetTable = 'FINANCIAL.SAMPLE_STG',
VARCHAR ErrorTable1 = 'FINANCIAL.SAMPLE_STG_LET',
VARCHAR ErrorTable2 = 'FINANCIAL.SAMPLE_STG_LUV',
VARCHAR WorkTable = 'FINANCIAL.SAMPLE_STG_LWT',
VARCHAR LogTable = 'FINANCIAL.SAMPLE_STG_LLT',
VARCHAR WorkingDatabase = 'FINANCIAL',
VARCHAR TraceLevel = 'NONE'
);

DEFINE OPERATOR FROM_STG()
DESCRIPTION 'Transparent Work Tbl made from Stage'
TYPE EXPORT
SCHEMA WRK
ATTRIBUTES
(
VARCHAR SourceTdpId = 'localhost',
VARCHAR SourceUserName = 'dbc',
VARCHAR SourceUserPassword = 'dbc',
VARCHAR PrivateLogName = 'exp_wrk.log',
VARCHAR SelectStmt = '
LOCKING TABLE FINANCIAL.SAMPLE_STG FOR ACCESS
LOCKING TABLE FINANCIAL.SAMPLE_TGT FOR ACCESS
SELECT
   T1.ID
  ,T1.ATR1
  ,T1.ATR2
  ,T1.BUSS_DT
  ,CURRENT_TIMESTAMP AS ST_DT
  ,''9999-12-31'' AS END_DT
  ,CURRENT_TIMESTAMP AS LD_DT
  ,T1.VRSION_CD
  ,T1.RTRNB_IND
  ,T1.MAP 
  ,CASE WHEN T2.ID IS NULL
        THEN ''I''
        ELSE CASE WHEN T1.BUSS_DT > T2.BUSS_DT AND
                       HASHROW(T1.ATR1, T1.ATR2, T1.VRSION_CD) <>
                       HASHROW(T2.ATR1, T2.ATR2, T2.VRSION_CD)
                  THEN ''A''
                  ELSE ''N''
                  END
        END UPD_TYPE_CD
 FROM FINANCIAL.SAMPLE_STG T1
 LEFT JOIN FINANCIAL.SAMPLE_TGT T2
   ON T1.ID = T2.ID
  AND T1.VRSION_CD = T2.VRSION_CD
  AND T2.END_DT = ''9999-12-31''
WHERE UPD_TYPE_CD <> ''N'';'
);

DEFINE OPERATOR TO_WRK()
DESCRIPTION 'Populates Work from Modified data from Stage'
TYPE UPDATE
SCHEMA WRK
ATTRIBUTES
(
INTEGER MaxSessions = 4,
INTEGER MinSessions = 1,
INTEGER ErrorLimit = 1,
VARCHAR PrivateLogName = 'upd_wrk.log',
VARCHAR TdpId = 'localhost',
VARCHAR UserName = 'dbc',
VARCHAR UserPassword = 'dbc',
VARCHAR AccountID,
VARCHAR TargetTable = 'FINANCIAL.SAMPLE_WRK',
VARCHAR ErrorTable1 = 'FINANCIAL.SAMPLE_WRK_LET',
VARCHAR ErrorTable2 = 'FINANCIAL.SAMPLE_WRK_LUV',
VARCHAR WorkTable = 'FINANCIAL.SAMPLE_WRK_LWT',
VARCHAR LogTable = 'FINANCIAL.SAMPLE_WRK_LLT',
VARCHAR WorkingDatabase = 'FINANCIAL',
VARCHAR TraceLevel = 'NONE'
);

/**** The APPLY Statement ****/

STEP Setup_TBL (
      APPLY
       ('DROP TABLE FINANCIAL.SAMPLE_STG;'),
       ('DROP TABLE FINANCIAL.SAMPLE_WRK;'),
       ('CREATE TABLE FINANCIAL.SAMPLE_STG(
            ID INTEGER,
            ATR1 VARCHAR(50),
            ATR2 VARCHAR(50),
            BUSS_DT TIMESTAMP,
            VRSION_CD BYTEINT,
            RTRNB_IND CHAR(1),
            MAP VARCHAR(50))
           PRIMARY INDEX (ID);'),  
       ('CREATE TABLE FINANCIAL.SAMPLE_WRK(
            ID INTEGER,
            ATR1 VARCHAR(50),
            ATR2 VARCHAR(50),
            BUSS_DT TIMESTAMP,
            ST_DT TIMESTAMP,
            END_DT TIMESTAMP,
            LD_DT TIMESTAMP,
            VRSION_CD BYTEINT,
            RTRNB_IND CHAR(1),
            MAP VARCHAR(50),
            UPD_TYPE_CD CHAR(1))
           PRIMARY INDEX (ID);')
     TO OPERATOR ( DDL_OPERATOR()[1] );
   );

STEP Load_STG (
      APPLY (
      'INSERT INTO FINANCIAL.SAMPLE_STG 
       (:IN_ID,
        :IN_ATR1,
        :IN_ATR2,
        :IN_BUSS_DT,
        :IN_VRSION_CD,
        :IN_RTRNB_IND,
        :IN_MAP );'
       )
     TO OPERATOR ( TO_STG()[1] )
     SELECT * FROM OPERATOR ( FILE_READER[1] );
   );
 
STEP Ins_WRK (
      APPLY (
      'INSERT INTO FINANCIAL.SAMPLE_WRK
       (:ID,
        :ATR1,
        :ATR2,
        :BUSS_DT,
        :ST_DT,
        :END_DT,
        :LD_DT,
        :VRSION_CD,
        :RTRNB_IND,
        :MAP,
        :UPD_TYPE_CD);'
       )
     TO OPERATOR ( TO_WRK()[1] )
     SELECT * FROM OPERATOR ( FROM_STG()[1] );
    );
 );

So, I have a few questions like:

1- What is the difference between transforming data on SelectStmt Clause (on export operator) and transforminf data on SELECT (inside APPLY) ? Which one is better/more efficient ?

2- How should I write fixed Values (like 9999-12-31) inside SelectStmt ? Cause it´s a single quotes inside another single quotes. Should I replace it by double quotes ( "value" ) or double single quotes ( '' value '' ), like I wrote ?

3- Is there any parameter that make me able to produce a log for producer operators (like " -o " parameter does to consumer operators ) ? I´m getting a return error, and it doesnt tell me anything :(

Teradata Parallel Transporter Export Operator Version 14.10.00.01

FROM_STG: private log specified: exp_wrk.log

Teradata Parallel Transporter Update Operator Version 14.10.00.01

TO_WRK: private log specified: upd_wrk.log

TO_WRK: connecting sessions

FROM_STG: TPT10551: CLI '215' occurred while connecting to the RDBMS

FROM_STG: TPT10507: CLI Error 215: MTDP: EM_CONNECT(215): Error found in local machine during connect.

FROM_STG: TPT10507: CLI Error 215: CLI error 215 (no message text available)

FROM_STG: Total processor time used = '0 Second(s)'

FROM_STG: Start : Tue Apr 22 14:32:20 2014

FROM_STG: End   : Tue Apr 22 14:32:44 2014

TO_WRK: TPT10507: CLI Error 220: MTDP: EM_DBC_CRASH_A(220): Network connection to the DBC was lost.

TO_WRK: disconnecting sessions

TO_WRK: Total processor time used = '0.1 Second(s)'

TO_WRK: Start : Tue Apr 22 14:32:20 2014

TO_WRK: End   : Tue Apr 22 14:32:46 2014

(I´m running it on Teradata VM)

Thank you !

Teradata Employee

Re: TPT - Assigning default values for columns not present in a file via TPT to load a table

1. The SELECT statement on the APPLY-SELECT is not a SELECT statement that is sent to Teradata. It is an internal command that allows TPT to filter out columns from the input record. The SELECT statement for the SelectStmt attribute is sent to Teradata and you should always do as much transformation in the database as possible. TPT does not support transformations on the client side.

2. You need to use double single-quotes.

3. The -o command was created so that users of our older utilities would get the information to stdout like they are used to seeing (and that would really only include ths consumer operator output). If you want to see the producer operator output, you will need to look in the job log (the .out file) using tlogview.

The output you see is about as much as you will get. The Update operator could not connect to Teradata due to CLI220 which means there is no network connection to that machine. I am unfamiliar with a CLI215, you may have to talk tot he GSC about that one.

All good questions!

-- SteveF