tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

Tools & Utilities
Enthusiast

tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

I am having difficulty using tpt and bulk loading dates into a table. My define schema looks like:

 DEFINE SCHEMA FILE_SOURCE_SCHEMA
(
Main_Id VARCHAR(25),
Parent_Id VARCHAR(25),
Channel_Cd VARCHAR(25),
Split_Pct VARCHAR(25),
Update_Ts VARCHAR(25),
Create_Ts VARCHAR(25)
);

after setting up the ddl operator, file reader and load operator and table my insert statement looks like:

                STEP LOAD_DATA
(
APPLY (
'INSERT INTO XBI_RADBI_BIZ_APP.AMR_SCPTv3_f_Channels (
:Main_Id,
:Parent_Id,
:Channel_Cd,
:Split_Pct,
:Update_Ts,
:Create_Ts
);'
)
TO OPERATOR (LOAD_OPERATOR[1])
SELECT * FROM OPERATOR (FILE_READER[1]);
);

The table itself looks like:

 CREATE MULTISET TABLE XBI_RADBI_BIZ_APP.AMR_SCPTv3_f_Channels ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
Main_Id NUMERIC NOT NULL,
Parent_Id NUMERIC NOT NULL,
Channel_Cd VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Split_Pct DECIMAL(15,3) NOT NULL,
Update_Ts VARCHAR(25) NOT NULL,
Create_Ts VARCHAR(25) NOT NULL)
UNIQUE PRIMARY INDEX ( Parent_Id ,Channel_Cd );

The problem is the Update_Ts and Create_Ts fields should be TimeStamp(6) fields. However, if I try to dictate them to be such, every record crashes when  it tries to load and I end up with an emtpy table. The numeric fields convert successfully as does the decimal, but the time fields will only load as strings.

I am very new to TeraData and this buik loading process. Any help will be greatly appreciated.

17 REPLIES
Teradata Employee

Re: tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

Please provide the whole script.

In what format is your data?

-- SteveF
Enthusiast

Re: tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

format of the data? It is in the format of the table I am trying to build. I am sucking data from one 
database, writing to flat file, sending it to teradata and then trying to suck it in.

This is the whole script for tpt:

DEFINE JOB AMR_SCPTv3_f_Channels DESCRIPTION 'Loads f_channels_pub data into a XBI_RADBI_BIZ_APP AMR_SCPTv3_f_Channels Teradata table' ( DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCHAR(25), Parent_Id VARCHAR(25), Channel_Cd VARCHAR(25), Split_Pct VARCHAR(25), Update_Ts VARCHAR(25), Create_Ts VARCHAR(25) ); DEFINE OPERATOR DDL_OPERATOR TYPE DDL ATTRIBUTES ( VARCHAR PrivateLogName = 'AMR_SCPTv3_f_Channels_log', VARCHAR TdpId = @jobvar_tdpid_tgt, VARCHAR UserName = @jobvar_username_tgt, VARCHAR UserPassword = @jobvar_password_tgt, VARCHAR AccountID, VARCHAR DataEncryption, VARCHAR LogonMech, VARCHAR LogonMechData, VARCHAR QueryBandSessInfo, VARCHAR Errorlist = '3807' ); DEFINE OPERATOR FILE_READER TYPE DATACONNECTOR PRODUCER SCHEMA FILE_SOURCE_SCHEMA ATTRIBUTES ( VARCHAR PrivateLogName = 'AMR_SCPTv3_f_Channels_File_Log', VARCHAR DirectoryPath = '/home/devo/e0111518/scpt3/data/', VARCHAR FileName = 'AMR_SCPTv3_f_Channels.csv', VARCHAR FORMAT = 'Delimited', VARCHAR OpenMode = 'Read', VARCHAR TextDelimiter = 'TAB', INTEGER SkipRows = 0 ); DEFINE OPERATOR LOAD_OPERATOR TYPE LOAD SCHEMA * ATTRIBUTES ( VARCHAR PrivateLogName = 'AMR_SCPTv3_f_Channels_Load_Log', INTEGER MaxSessions = 32, INTEGER MinSessions = 1, VARCHAR TdpId = @jobvar_tdpid_tgt, VARCHAR UserName = @jobvar_username_tgt, VARCHAR UserPassword = @jobvar_password_tgt, VARCHAR TargetTable = @jobvar_tgt_dbname || '.AMR_SCPTv3_f_Channels', VARCHAR LogTable = @jobvar_wrk_dbname || '.AMR_SCPTv3_f_Channels_LG', VARCHAR ErrorTable1 = @jobvar_wrk_dbname || '.AMR_SCPTv3_f_Channels_ET', VARCHAR ErrorTable2 = @jobvar_wrk_dbname || '.AMR_SCPTv3_f_Channels_UV', VARCHAR WorkTable = @jobvar_wrk_dbname || '.AMR_SCPTv3_f_Channels_WT' ); STEP SETUP_TABLE ( APPLY( 'DELETE FROM XBI_RADBI_BIZ_APP.AMR_SCPTv3_f_Channels;' ) TO OPERATOR (DDL_OPERATOR); ); STEP LOAD_DATA ( APPLY ( 'INSERT INTO XBI_RADBI_BIZ_APP.AMR_SCPTv3_f_Channels ( :Main_Id, :Parent_Id, :Channel_Cd, :Split_Pct, :Update_Ts, :Create_Ts );' ) TO OPERATOR (LOAD_OPERATOR[1]) SELECT * FROM OPERATOR (FILE_READER[1]); ); );

Enthusiast

Re: tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

That was ugly - lets do it in code version:

DEFINE JOB AMR_SCPTv3_f_Channels
DESCRIPTION 'Loads f_channels_pub data into a XBI_RADBI_BIZ_APP AMR_SCPTv3_f_Channels Teradata table'
(
DEFINE SCHEMA FILE_SOURCE_SCHEMA
(
Main_Id VARCHAR(25),
Parent_Id VARCHAR(25),
Channel_Cd VARCHAR(25),
Split_Pct VARCHAR(25),
Update_Ts VARCHAR(25),
Create_Ts VARCHAR(25)
);

DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'AMR_SCPTv3_f_Channels_log',
VARCHAR TdpId = @jobvar_tdpid_tgt,
VARCHAR UserName = @jobvar_username_tgt,
VARCHAR UserPassword = @jobvar_password_tgt,
VARCHAR AccountID,
VARCHAR DataEncryption,
VARCHAR LogonMech,
VARCHAR LogonMechData,
VARCHAR QueryBandSessInfo,
VARCHAR Errorlist = '3807'
);

DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA FILE_SOURCE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'AMR_SCPTv3_f_Channels_File_Log',
VARCHAR DirectoryPath = '/home/devo/e0111518/scpt3/data/',
VARCHAR FileName = 'AMR_SCPTv3_f_Channels.csv',
VARCHAR FORMAT = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = 'TAB',
INTEGER SkipRows = 0
);

DEFINE OPERATOR LOAD_OPERATOR
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'AMR_SCPTv3_f_Channels_Load_Log',
INTEGER MaxSessions = 32,
INTEGER MinSessions = 1,
VARCHAR TdpId = @jobvar_tdpid_tgt,
VARCHAR UserName = @jobvar_username_tgt,
VARCHAR UserPassword = @jobvar_password_tgt,
VARCHAR TargetTable = @jobvar_tgt_dbname || '.AMR_SCPTv3_f_Channels',
VARCHAR LogTable = @jobvar_wrk_dbname || '.AMR_SCPTv3_f_Channels_LG',
VARCHAR ErrorTable1 = @jobvar_wrk_dbname || '.AMR_SCPTv3_f_Channels_ET',
VARCHAR ErrorTable2 = @jobvar_wrk_dbname || '.AMR_SCPTv3_f_Channels_UV',
VARCHAR WorkTable = @jobvar_wrk_dbname || '.AMR_SCPTv3_f_Channels_WT'
);

STEP SETUP_TABLE
(
APPLY( 'DELETE FROM XBI_RADBI_BIZ_APP.AMR_SCPTv3_f_Channels;' )
TO OPERATOR (DDL_OPERATOR);
);

STEP LOAD_DATA
(
APPLY (
'INSERT INTO XBI_RADBI_BIZ_APP.AMR_SCPTv3_f_Channels (
:Main_Id,
:Parent_Id,
:Channel_Cd,
:Split_Pct,
:Update_Ts,
:Create_Ts
);'
)
TO OPERATOR (LOAD_OPERATOR[1])
SELECT * FROM OPERATOR (FILE_READER[1]);
);
);
Enthusiast

Re: tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

This whole thread has gotten a little convoluted. I need the last part of that last post (the load_data step) to send Update_Ts and Create_Ts as timestamp objects. (datetime, timestamp(0), timestamp(6)... i don't care). Teradata will not convert no matter what I do with the database (up till now).

Enthusiast

Re: tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

btw- it got convoluted because I did not describe the problem correctly.

my bad.

My issue is that I need to upload a file with a string representing a datetime or timestamp field and import it into teradata using some form of bulkload (see above tpt file). Is this possible?

Teradata Employee

Re: tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

Ok, so you have a TAB-delimited text file.

Can you provide a sample row so that I can see the format of the data that is destined for the timestamp columns?

Teradata is very picky about the format.

Also, if the text in the data file does not adhere (strictly) to the format expected by Teradata, TPT can do text conversions to get it right (we can discuss that later, if needed).

Also, what version of TPT are you running?

-- SteveF
Enthusiast

Re: tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

example row:

121234 21653 Retail 40.15 "2016-05-27 13:37:08" "2016-05-27 13:37:08"

 

tab delimited (not that it looks like it on a cust and paste) last two fields are timestamp(6)

 

How do i check the tpt version. (As I said, I am new to this company and these sytems and teradata)

 

Mark

Enthusiast

Re: tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

Also Steve - Don't get tied up on the quotes wrapping the datetime fields. That was just my last pull/attempt. No matter what I wrap them in (singles, nothing etc...) they will not convert.

I think there must be some form of a direct conversion or cast that can be done in the final INSERT statemement for the data load? I have tried demanding the fields be a timestamp... (e.g.)

 STEP LOAD_DATA
(
APPLY (
'INSERT INTO XBI_RADBI_BIZ_APP.AMR_SCPTv3_f_Channels (
:Main_Id,
:Parent_Id,
:Channel_Cd,
:Split_Pct,
:Update_Ts,
:Create_Ts (timestamp(6) format...)
);'
)
TO OPERATOR (LOAD_OPERATOR[1])
SELECT * FROM OPERATOR (FILE_READER[1]);
);

But that only crashed everything.

Shutting down for the night. But if you have any ideas please send them for an AM wakeup call.

Thanks for your efforts.

Mark

Enthusiast

Re: tpt conversion from varchar to timestamp(6) DEFINE SCHEMA FILE_SOURCE_SCHEMA ( Main_Id VARCH

Steven -

Any further thoughts on this issue? 

Regards,

Mark