TPT load NULL AND empty string

Tools
Supporter

TPT load NULL AND empty string

Hi, 

 

 

I am a bit confused and might have been overseen something.

I need to load a data from delimited file. The char fields can contain empty stings and nulls. I tried to use the 

QuotedData = 'Optional' / 'Yes' feature but didn't succeed - all details below (why the hack can't we attache files???)

Any idea what I missed? 

 

Ulirch

 

P.S. I used also the YES option with all data quoted - result the same...

 

here is my test case:

Target Table:

create table tpt_load_test
(a integer,
 b varchar(10),
c varchar(10)
) primary index (a)

Script:

DEFINE JOB stream_LOAD
DESCRIPTION 'Load a Teradata table using Stream'
(
DEFINE SCHEMA tb_SCHEMA
(
a varchar(100),
b varchar(100),
c VARCHAR(30)
);


DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA tb_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = @file_read_log,
VARCHAR FileName = @file_name,
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = ',',
VARCHAR QuotedData = 'Optional',
VARCHAR OpenQuoteMark = '"'
);

DEFINE OPERATOR LOAD_OPERATOR
TYPE UPDATE
SCHEMA *
ATTRIBUTES
(
VARCHAR TargetTable = 'tpt_load_test',
VARCHAR TdpId = '**bleep**',
VARCHAR UserName = 'yyy',
VARCHAR UserPassword ='zzz'
);

APPLY
('INSERT INTO test_db_uli.tpt_load_test VALUES (:a,:b,:c);')
TO OPERATOR (LOAD_OPERATOR[2])
SELECT * FROM OPERATOR (FILE_READER[1]);
);

Varfile

file_read_log     = 'file_read_log.log'
,file_name        = 'tpt_test.csv'

Data File

1,"",j
2,"",y
3,"",c
4,,z
5,sak,f
6,,g
7,kskas,g
8,,s
9,,m
10,,y

Run Log

Teradata Parallel Transporter Version 15.00.00.00
TPT_INFRA: TPT03624: Warning: tbuild -s option argument specifies the first job step;
  no job steps will be skipped (unless this is a restarted job).
Job log: /opt/teradata/client/15.00/tbuild/logs/uli-70.out
Job id is uli-70, running on trinity.data2knowledge.bitz
Teradata Parallel Transporter Update Operator Version 15.00.00.00
LOAD_OPERATOR: private log not specified
Teradata Parallel Transporter FILE_READER[1]: TPT19006 Version 15.00.00.00
FILE_READER[1]: TPT19010 Instance 1 directing private log report to 'file_read_log.log-1'.
FILE_READER[1]: TPT19003 NotifyMethod: 'None (default)'
FILE_READER[1]: TPT19008 DataConnector Producer operator Instances: 1
FILE_READER[1]: TPT19003 ECI operator ID: 'FILE_READER-6718'
FILE_READER[1]: TPT19222 Operator instance 1 processing file 'tpt_test.csv'.
LOAD_OPERATOR: connecting sessions
LOAD_OPERATOR: preparing target table(s)
LOAD_OPERATOR: entering DML Phase
LOAD_OPERATOR: entering Acquisition Phase
LOAD_OPERATOR: entering Application Phase
LOAD_OPERATOR: Statistics for Target Table:  'tpt_load_test'
LOAD_OPERATOR: Rows Inserted: 10
LOAD_OPERATOR: Rows Updated:  0
LOAD_OPERATOR: Rows Deleted:  0
LOAD_OPERATOR: entering Cleanup Phase
LOAD_OPERATOR: Error Table Statistics for Target Table :   'tpt_load_test'
LOAD_OPERATOR: Total Rows in Error Table 1:   0
LOAD_OPERATOR: Total Rows in Error Table 2:   0
FILE_READER[1]: TPT19221 Total files processed: 1.
LOAD_OPERATOR: disconnecting sessions
LOAD_OPERATOR: Total processor time used = '0,36 Second(s)'
LOAD_OPERATOR: Start : Wed Oct 19 20:02:36 2016
LOAD_OPERATOR: End   : Wed Oct 19 20:02:47 2016
Job step MAIN_STEP completed successfully
Job uli completed successfully, but with warning(s).
Job start: Wed Oct 19 20:02:31 2016
Job end:   Wed Oct 19 20:02:47 2016

Table Result - All empty strings are converted to Null...

a	b	c
1	Null	j
2	Null	y
3	Null	c
4	Null	z
5	sak	f
6	Null	g
7	kskas	g
8	Null	s
9	Null	m
10	Null	y
  • tpt null empty string

Accepted Solutions
Teradata Employee

Re: TPT load NULL AND empty string

This issue you are running into is fixed in 15.00.00.003.

-- SteveF
1 ACCEPTED SOLUTION
7 REPLIES
Enthusiast

Re: TPT load NULL AND empty string

Looks like you may also need to specify "NullColumns='No'".

From the reference manual:

NullColumns= ‘option’

Determines whether zero length columns are to be NULLed.
Valid values are:
• ‘Y[es]' = zero length columns are to be treated as NULL columns
(default).
• ‘N[o]’ = zero length columns are to be treated as zero length columns.

Supporter

Re: TPT load NULL AND empty string

Was promissing but not yet - it now turns everything into empty strings...

a	b	c
1		j
2		y
3		c
4		z
5	sak	f
6		g
7	kskas	g
8		s
9		m
10		y
Supporter

Re: TPT load NULL AND empty string

I used as data:

"1","","j"
"2","","y"
"3","","c"
"4",,"z"
"5","sak","f"
"6",,"g"
"7","kskas","g"
"8",,"s"
"9",,"m"
"10",,"y"

and as script:

DEFINE JOB stream_LOAD
DESCRIPTION 'Load a Teradata table using Stream'
(
DEFINE SCHEMA tb_SCHEMA
(
a varchar(100),
b varchar(100),
c VARCHAR(30)
);


DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA tb_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = @file_read_log,
VARCHAR FileName = @file_name,
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = ',',
VARCHAR QuotedData = 'Y',
VARCHAR OpenQuoteMark = '"',
VARCHAR NullColumns='No'
);

DEFINE OPERATOR LOAD_OPERATOR
TYPE UPDATE
SCHEMA *
ATTRIBUTES
(
VARCHAR TargetTable = 'tpt_load_test',
VARCHAR TdpId = '**bleep**',
VARCHAR UserName = 'yyy',
VARCHAR UserPassword ='zzz'
);

APPLY
('INSERT INTO test_db_uli.tpt_load_test VALUES (:a,:b,:c);')
TO OPERATOR (LOAD_OPERATOR[2])
SELECT * FROM OPERATOR (FILE_READER[1]);
);

 

Enthusiast

Re: TPT load NULL AND empty string

I know that in the past (prior to some of the newer options), I've had to specify a special character (I used a non-typeable character like a hex '1F') to indicate an empty string. Then, on the insert, I would use a CASE statement to translate that character back to an empty string. It's a pain to do, but it worked.

 

 

 

Supporter

Re: TPT load NULL AND empty string

juup - this is how it is currently implemented

but due to ETL tool usage the case statement is done in SQL after loading

and this is affecting some hundred cols

and this results in long parsing times for a short query...

 

And given all these parameters I was really sure that it should be possible to do this directly in TPT but doesn’t look too promising…

 

Teradata Employee

Re: TPT load NULL AND empty string

This issue you are running into is fixed in 15.00.00.003.

-- SteveF
Supporter

Re: TPT load NULL AND empty string

Upgraded to latest version - now WITHOUT NullColumns='No' option - now let's check if this was also fixed for the 14.10 release the customer is currently running ;)...

a	b	c
5	sak	f
9	Null	m
7	kskas	g
6	Null	g
3		c
10	Null	y
1		j
4	Null	z
8	Null	s
2		y