TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

Tools & Utilities
Enthusiast

TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

In the example code for the dataconnector producer, I noticed you can have a schema that is 'delimited' and send it to a table that is formatted to non VARCHAR values:

/*
Appendix A: Job Script Examples
Job Script Example Library
Teradata Parallel Transporter User Guide 439
*/

DEFINE JOB MINI_BATCH
DESCRIPTION 'Mini-Batch Loading'
(
DEFINE SCHEMA PRODUCT_SOURCE_SCHEMA
(
Associate_Id integer,
Associate_Name char(25),
Salary float,
DOJ ansidate,
Designation varchar(25),
Loan_Amount decimal(5,2),
Martial_Status char(1),
No_Of_Dependents byteint
);
DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ddl_log',
VARCHAR TdpId = @jobvar_tdpid,
VARCHAR UserName = @jobvar_username,
VARCHAR UserPassword = @jobvar_password,
VARCHAR ARRAY ErrorList = ['3807','3803','5980']
);
DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA PRODUCT_SOURCE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = @jobvar_datafiles_path,
VARCHAR FileName = 'accounts.txt',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = '|'
);
DEFINE OPERATOR LOAD_OPERATOR
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'load_log',
VARCHAR TdpId = @jobvar_tdpid,
VARCHAR UserName = @jobvar_username,
VARCHAR UserPassword = @jobvar_password,
VARCHAR TargetTable = 'DDL046_stg',
VARCHAR LogTable = 'DDL046_stg_log',
VARCHAR ErrorTable1 = 'DDL046_stg_e1',
VARCHAR ErrorTable2 = 'DDL046_stg_e2'
);

STEP STEP1
(
APPLY
('DROP TABLE DDL046_stg;'),
('CREATE TABLE DDL046_stg (Associate_Id integer,
Associate_Name char(25),
Salary float,
DOJ date,
Designation varchar(25),
Loan_Amount decimal(5,2),
Martial_Status char(1),
No_Of_Dependents byteint);')
TO OPERATOR (DDL_OPERATOR);
);
STEP STEP2
(
APPLY ('INS INTO DDL046_stg (:Associate_Id,
:Associate_Name,
:Salary,
:DOJ,
:Designation,
:Loan_Amount,
:Martial_Status,
:No_Of_Dependents);')
TO OPERATOR (LOAD_OPERATOR[2])
SELECT * FROM OPERATOR (FILE_READER[2]);
);
);

Noting the highlighted lines, I tried it and got the following error:

MyProducer: TPT19108 Data Format 'DELIMITED' requires all 'VARCHAR' schema.

this is my code:

USING CHAR SET ASCII

DEFINE JOB MyJob
(
DEFINE SCHEMA MySchema
(
Status_Id INTEGER
,Status_Cd2 VARCHAR(120)
,counts INTEGER
,valid VARCHAR(1)
,first_used INTDATE
,last_used INTDATE
,last_updated INTDATE
);

DEFINE OPERATOR MyProducer()
TYPE DATACONNECTOR PRODUCER
SCHEMA MySchema
ATTRIBUTES
(
PrivateLogName = 'dataconnector_log',
DirectoryPath = 'D:\Users\d355869\Documents\tpt\',
FileName = 'test4_dataconnector_edw_2_file.txt',
Format = 'Delimited',
OpenMode = 'Read',
TextDelimiter = '~'
);

DEFINE OPERATOR DDLOperator()
TYPE DDL
ATTRIBUTES
(
PrivateLogName = 'ddl_log'
,TdpId = @MyDstTdpId
,LogonMech = @MyDstLogonMech
,UserName = @MyDstUserName
,UserPassword = @MyDstUserPassword
,WorkingDatabase = @MyDstUserDatabase
,ErrorList = '3807'
);

DEFINE OPERATOR MyConsumer()
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR TdpId = @MyDstTdpId
,VARCHAR LogonMech = @MyDstLogonMech
,VARCHAR UserName = @MyDstUserName
,VARCHAR UserPassword = @MyDstUserPassword
,VARCHAR WorkingDatabase = @MyDstUserDatabase
,VARCHAR TargetTable = @MyDstUserDatabase || '.' || @MyDstTable
,VARCHAR LogTable = @MyDstUserDatabase || '.' || @MyDstTable || '_LOG'
,VARCHAR ErrorTable1 = @MyDstTable || '_E1'
,VARCHAR ErrorTable2 = @MyDstTable || '_E2'
,VARCHAR PrivateLogName = 'consumer_log'
);

STEP create_the_table
(
APPLY
('DROP table ' || @MyDstUserDatabase || '.' || @MyDstTable || ';'),
('DROP table ' || @MyDstUserDatabase || '.' || @MyDstTable || '_LOG;'),
('DROP table ' || @MyDstUserDatabase || '.' || @MyDstTable || '_E1;'),
('DROP table ' || @MyDstUserDatabase || '.' || @MyDstTable || '_E2;'),
(
'CREATE SET TABLE ' || @MyDstUserDatabase || '.' || @MyDstTable || '
,NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO(
Status_Id INTEGER NOT NULL,
Status_Cd2 VARCHAR(120) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
counts INTEGER,
valid VARCHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC,
first_used DATE FORMAT ''YYYY-MM-DD'',
last_used DATE FORMAT ''YYYY-MM-DD'',
last_updated DATE FORMAT ''YYYY-MM-DD''
)PRIMARY INDEX ( Status_Id );'
)
TO OPERATOR (DDLOperator);
);

STEP export_the_data
(
APPLY
('INSERT INTO ' || @MyDstUserDatabase || '.' || @MyDstTable || '
(:Status_Id
,:Status_Cd2
,:counts
,:valid
,:first_used
,:last_used
,:last_updated
);'
)
TO OPERATOR (MyConsumer)

SELECT * FROM OPERATOR (MyProducer);
);
);

Can somebody explain what I might be doing wrong?

10 REPLIES
Enthusiast

Re: TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

Addendum, to check myself I rewrote the example code for my system and got the following log error:

D:\Users\d355869\Documents\tpt>tbuild -v MyAttrs.tpt -f test7.tpt
Teradata Parallel Transporter Version 13.10.00.04
Job log: C:\Program Files\Teradata\client\13.10\Teradata Parallel Transporter/logs/d355869-70.out
Job id is d355869-70, running on W0000004000214
Found CheckPoint file: C:\Program Files\Teradata\client\13.10\Teradata Parallel Transporter/checkpoint\d355869LVCP
This is a restart job; it restarts at step STEP2.
Teradata Parallel Transporter Load Operator Version 13.10.00.03
LOAD_OPERATOR: private log specified: load_log
FILE_READER Instance 2 directing private log report to 'dataconnector_log-2'.
Teradata Parallel Transporter DataConnector Version 13.10.00.04
FILE_READER Instance 1 directing private log report to 'dataconnector_log-1'.
FILE_READER: TPT19108 Data Format 'DELIMITED' requires all 'VARCHAR' schema.
FILE_READER: TPT19008 DataConnector Producer operator Instances: 2
FILE_READER: TPT19108 Data Format 'DELIMITED' requires all 'VARCHAR' schema.
FILE_READER: TPT19221 Total files processed: 0.
LOAD_OPERATOR: connecting sessions
LOAD_OPERATOR: preparing target table
LOAD_OPERATOR: entering Acquisition Phase
LOAD_OPERATOR: disconnecting sessions
LOAD_OPERATOR: Total processor time used = '1.17001 Second(s)'
LOAD_OPERATOR: Start : Sun Apr 22 08:13:39 2012
LOAD_OPERATOR: End : Sun Apr 22 08:13:47 2012
Job step STEP2 terminated (status 12)
Job d355869 terminated (status 12)

Here is my code rewrite:

SET MyDstUserDatabase = 'MYDB';

DEFINE JOB MINI_BATCH
DESCRIPTION 'Mini-Batch Loading'
(
DEFINE SCHEMA PRODUCT_SOURCE_SCHEMA
(
Associate_Id INTEGER,
Associate_Name CHAR(25),
Salary FLOAT,
DOJ ANSIDATE,
Designation VARCHAR(25),
Loan_Amount DECIMAL(5,2),
Martial_Status CHAR(1),
No_Of_Dependents BYTEINT
);

DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ddl_log',
VARCHAR TdpId = @MyDstTdpId,
VARCHAR UserName = @MyDstUserName,
VARCHAR UserPassword = @MyDstUserPassword,
VARCHAR LogonMech = @MyDstLogonMech,
VARCHAR WorkingDatabase = @MyDstUserDatabase,
VARCHAR ARRAY ErrorList = ['3807','3803','5980']
);

DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA PRODUCT_SOURCE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = 'D:\Users\d355869\Documents\tpt\',
VARCHAR FileName = 'accounts.txt',
VARCHAR FORMAT = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = '|'
);

DEFINE OPERATOR LOAD_OPERATOR
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'load_log',
VARCHAR TdpId = @MyDstTdpId,
VARCHAR UserName = @MyDstUserName,
VARCHAR UserPassword = @MyDstUserPassword,
VARCHAR LogonMech = @MyDstLogonMech,
VARCHAR WorkingDatabase = @MyDstUserDatabase,
VARCHAR TargetTable = 'MYDB.DDL046_stg',
VARCHAR LogTable = 'MYDB.DDL046_stg_log',
VARCHAR ErrorTable1 = 'MYDB.DDL046_stg_e1',
VARCHAR ErrorTable2 = 'MYDB.DDL046_stg_e2'
);

STEP STEP1
(
APPLY
('DROP TABLE DDL046_stg;'),
(
'CREATE TABLE MYDB.DDL046_stg (
Associate_Id integer,
Associate_Name char(25),
Salary float,
DOJ date,
Designation varchar(25),
Loan_Amount decimal(5,2),
Martial_Status char(1),
No_Of_Dependents byteint
);'
)
TO OPERATOR (DDL_OPERATOR);
);
STEP STEP2
(
APPLY (
'INS INTO MYDB.DDL046_stg (
:Associate_Id,
:Associate_Name,
:Salary,
:DOJ,
:Designation,
:Loan_Amount,
:Martial_Status,
:No_Of_Dependents
);'
)
TO OPERATOR (LOAD_OPERATOR[2])
SELECT * FROM OPERATOR (FILE_READER[2]);
);
);

Teradata Employee

Re: TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

The answer is quite simple.

The Schema object defines the format of the data as it is being provided to the DataConnector operator. It has nothing to do with the data types of columns in the target table.

"Delimited" data is just a series of varying length character strings. Thus, the schema object for delimited data must be made up of VARCHAR fields.

This is documented in the TPT manuals.

The loading operator will send a USING clause with the DML statements and the USING clause will contain the VARCHAR data types, telling Teradata we are sending the data as VARCHAR, and Teradata will convert to the data types of the target table.

The error message you are getting is self-explanatory.

-- SteveF
Enthusiast

Re: TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

Thanks for responding feinholz,

I Agree. Hence please note: ref_2445.pdf_p438_V13.10 of the documentation includes the code snippet above which, I think we both agree, is not legal code. 

        DEFINE SCHEMA PRODUCT_SOURCE_SCHEMA
(
Associate_Id INTEGER,
Associate_Name CHAR(25),
Salary FLOAT,
DOJ ANSIDATE,
Designation VARCHAR(25),
Loan_Amount DECIMAL(5,2),
Martial_Status CHAR(1),
No_Of_Dependents BYTEINT
);

if being used in a 'delimited' charater set.

Can we have a rulling on this page of the documentation? I think it is in error. below is a cut down version of the code on p438:

        DEFINE SCHEMA PRODUCT_SOURCE_SCHEMA
(
Associate_Id INTEGER,
Associate_Name CHAR(25),
Salary FLOAT,
DOJ ANSIDATE,
Designation VARCHAR(25),
Loan_Amount DECIMAL(5,2),
Martial_Status CHAR(1),
No_Of_Dependents BYTEINT
);

DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA PRODUCT_SOURCE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = 'D:\Users\d355869\Documents\tpt\',
VARCHAR FileName = 'accounts.txt',
VARCHAR FORMAT = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = '|'
);

DEFINE OPERATOR LOAD_OPERATOR
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'load_log',
VARCHAR TdpId = @MyDstTdpId,
VARCHAR UserName = @MyDstUserName,
VARCHAR UserPassword = @MyDstUserPassword,
VARCHAR LogonMech = @MyDstLogonMech,
VARCHAR WorkingDatabase = @MyDstUserDatabase,
VARCHAR TargetTable = 'MYDB.DDL046_stg',
VARCHAR LogTable = 'MYDB.DDL046_stg_log',
VARCHAR ErrorTable1 = 'MYDB.DDL046_stg_e1',
VARCHAR ErrorTable2 = 'MYDB.DDL046_stg_e2'
);

APPLY (
'INS INTO MYDB.DDL046_stg (
:Associate_Id,
:Associate_Name,
:Salary,
:DOJ,
:Designation,
:Loan_Amount,
:Martial_Status,
:No_Of_Dependents
);'
TO OPERATOR (LOAD_OPERATOR[2])
SELECT * FROM OPERATOR (FILE_READER[2]);

Teradata Employee

Re: TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

Documentation is incorrect.

I questioned engineering about it; I am not sure why or how they could have an example script that does not work.

I have been told that we made a lot of corrections to the documentation in this area for the 14.0 books.

-- SteveF
Enthusiast

Re: TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

Thankyou for your confirmation. Kind regards, R

Enthusiast

Re: TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

14.0 books have the examples removed from the user guide manual and added onto

/opt/teradata/client/14.00/tbuild/sample/userguide/ folder. However they still contain at least a non working example in my opinion.

For example "/opt/teradata/client/14.00/tbuild/sample/userguide/uguide14.txt still uses a non-all varchar schema and invokes a Dataconnector consumer operator in Delimited format.

  DEFINE SCHEMA TEST_SCHEMA
  (
    COL1_INT   INTEGER,
    COL2_INT   INTEGER,
    COL3_CLOB  CLOB(25000) AS DEFERRED BY NAME,
    COL4_BLOB  BLOB(500)   AS DEFERRED BY NAME,
    COL5_BLOB  BLOB(29000) AS DEFERRED BY NAME
  );

...

 TYPE DATACONNECTOR CONSUMER
  SCHEMA *
  ATTRIBUTES
  (
    VARCHAR PrivateLogName = 'dataconnector_log',
    VARCHAR DirectoryPath  = @jobvar_datafiles_path,
    VARCHAR FileName       = 'test_file.dat',
    VARCHAR Format         = 'Delimited',
    VARCHAR OpenMode       = 'Write',
    VARCHAR TextDelimiter  = '~'
  );

 APPLY TO OPERATOR (FILE_WRITER)
  SELECT * FROM OPERATOR (SQL_SELECTOR);

 What is the correct way of exporting to flat files using delimited format in case the table exported is not made out all of varchars ?  Does the SelectStmt need to be adjusted to select only varchars ?

Unfortunately,  the examples provided for the delimited flat file export use a table that contains only VARCHARS ( uguide10.txt)

Teradata Employee

Re: TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

In 14.0 we added the capability of exporting the data from Teradata and writing out the data in delimited format, and the DC operator will convert the data from binary to character format.

So, you will have to know that there is a high CPU cost for this, but you can scale the output to multiple files if you want; and you do not have to adjust the SELECT statement or have an all-VARCHAR schema.

-- SteveF
Enthusiast

Re: TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

Hm, i am seeing something different in the TPT 14 Reference manual . page 126 / revision June 2012

Format = 'Delimited'

....<text removed here>

With this file format, all of the data types in the DEFINE SCHEMA must be VARCHARs.

Additionally, when running in my TD express 14 instance the uguide14.txt job i get

 

TDExpress14.0_Sles10:/opt/teradata/client/14.00/tbuild/sample/userguide # tbuild -v jobvars.txt -f uguide14.txt

Teradata Parallel Transporter Version 14.00.00.04

Job log: /opt/teradata/client/14.00/tbuild/logs/root-13.out

Job id is root-13, running on (none)

Found CheckPoint file: /opt/teradata/client/14.00/tbuild/checkpoint/rootLVCP

This is a restart job; it restarts at step MAIN_STEP.

Teradata Parallel Transporter FILE_WRITER: TPT19006 Version 14.00.00.04

FILE_WRITER Instance 1 directing private log report to 'dataconnector_log-1'.

FILE_WRITER: TPT19007 DataConnector Consumer operator Instances: 1

FILE_WRITER: TPT19108 Data Format 'DELIMITED' requires all 'VARCHAR' schema.

Teradata Parallel Transporter SQL Selector Operator Version 14.00.00.04

SQL_SELECTOR: private log specified: selector_log

FILE_WRITER: TPT19221 Total files processed: 0.

SQL_SELECTOR: connecting sessions

SQL_SELECTOR: RDBMS Warning: 5675 Keep Response must be set to return Locators to LOB values.

SQL_SELECTOR: disconnecting sessions

SQL_SELECTOR: Total processor time used = '0.59 Second(s)'

SQL_SELECTOR: Start : Wed Jan 16 10:36:10 2013

SQL_SELECTOR: End   : Wed Jan 16 10:37:11 2013

Job step MAIN_STEP terminated (status 12)

Job root terminated (status 12)

Am i using the example incorrectly?

Note: I did not get email notification from forum , so it has been a while before i manually checked the forum for any response. I have my profile set to email for all comments.

Thanks.

Enthusiast

Re: TPT Dataconnector Producer for 'Delimited' files to schema's other than VARCHAR?

Hi,

I am facing simillar issue.

could someone help me how to adjust select statement to export decimal columns using DELIMITER

we are on TPT 13.10

Thanks,