Trailing spaces in Teradata Fastexport

Tools
Enthusiast

Trailing spaces in Teradata Fastexport

We are using a Fastexport script to export the records from a Teradata table into a pipe delimited output file.

 

.LOGTABLE dummy.ERR_LOG;

.LOGON serv/dummy1,dummy2;

DATABASE dummy; .

BEGIN EXPORT SESSIONS 4;

.EXPORT OUTFILE Dummy_Out_Path MODE RECORD FORMAT TEXT;

select CAST('"'||COALESCE(TRIM(cast(id as CHAR(14))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime1 as CHAR(12))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime2 as CHAR(18))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime3 as CHAR(30))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime4 as CHAR(36))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime5 as CHAR(25))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset1 as CHAR(10))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset2 as CHAR(5))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset3 as CHAR(5))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset4 as CHAR(5))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset5 as CHAR(12))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(enrolled_period as CHAR(30))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(work_period as CHAR(65))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast((Numeric1 (FORMAT '-Z(3)')) as CHAR(5))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast((Numeric2 (FORMAT '-Z(5)')) as CHAR(6))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Numeric3 as CHAR(14))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Numeric4 as CHAR(25))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast((Numeric5 (FORMAT '-Z(34)9.99')) as CHAR(40))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast((Numeric6 (FORMAT '-Z(34)9.99')) as CHAR(40))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(Character1),'test')||'"'||'|'
||'"'||COALESCE(TRIM(Character2),'test')||'"'
||'|'||'"'||TDUSER.hash_md5(CAST('"'||COALESCE(TRIM(cast(id as CHAR(14))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime1 as CHAR(12))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime2 as CHAR(18))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime3 as CHAR(30))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime4 as CHAR(36))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(DateTime5 as CHAR(25))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset1 as CHAR(10))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset2 as CHAR(5))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset3 as CHAR(5))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset4 as CHAR(5))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Offset5 as CHAR(12))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(enrolled_period as CHAR(30))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(work_period as CHAR(65))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast((Numeric1 (FORMAT '-Z(3)')) as CHAR(5))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast((Numeric2 (FORMAT '-Z(5)')) as CHAR(6))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Numeric3 as CHAR(14))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Numeric4 as CHAR(25))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast((Numeric5 (FORMAT '-Z(34)9.99')) as CHAR(40))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(cast((Numeric6 (FORMAT '-Z(34)9.99')) as CHAR(40))),'test')||'"'||'|'
||'"'||COALESCE(TRIM(Character1),'test')||'"'||'|'
||'"'||COALESCE(TRIM(Character2),'test')||'"' AS CHAR(520)))||'"' AS CHAR(555)) from dummy.table;

 .END EXPORT;

.LOGOFF;

 

Since we are using an outer cast to remove the extra binary characters that we get if we do not cast the Fastexport output into character.For every row the length of the data is way less than the casting length i.e 555 in this case leading to trailing spaces at the end of every record.We are deciding the casting length(outer) based on the sum of the  casting length of the datatypes we are using in the select statement.I was wondering what would be the best approach to remove the space from thev end of the record? 

 

Tags (4)
11 REPLIES
Enthusiast

Re: Trailing spaces in Teradata Fastexport

If you don't want trailing spaces, then you'll want to CAST it as VARCHAR not CHAR. The CHAR data type will pad it with spaces up to the size that you specify.

Enthusiast

Re: Trailing spaces in Teradata Fastexport

But casting it as VARCHAR will lead to extra binary text at the start of each record.I wanted to avoid that situation so I cast it as CHAR.Any other workarounds?

 

 

 

 

Enthusiast

Re: Trailing spaces in Teradata Fastexport

Have you tried using "UNFORMATTED" as the format? You would need to concatenate in your end of line character (x'0A' for Linux or x'0D0A') for DOS, but I would think that would work with VARCHAR and would avoid the length bytes at the beginning of the record.

Enthusiast

Re: Trailing spaces in Teradata Fastexport

You mean using the word "UNFORMATTED" instead of TEXT .Replacing 'TEXT' as 'UNFORMATTED' in the FORMAT is throwing an error.Also I did not get the expression "You would need to concatenate in your end of line character (x'0A' for Linux or x'0D0A') for DOS"

Could you please explain where do I need to make changes to the code?

Enthusiast

Re: Trailing spaces in Teradata Fastexport

I was suggesting that you try it with "UNFORMAT" (I put "UNFORMATTED" instead, which was probably why you got a syntax error). However, I have tried "UNFORMAT" myself and it looks like it still puts the length at the beginnng of the record when using that format as well.

 

What I have done in the past is to use VARCHAR, but then follow the export up with a process (such as an 'awk' in Linux) to remove the length characters at the beginning of the records.

Enthusiast

Re: Trailing spaces in Teradata Fastexport

The output file generated by FastExport is a huge file in TB's.I was wondering whether using awk or similar process in Linux will be right approach in term of performance overhead it will cause.Are there any other options which will yield better result in terms of performance impact?

Senior Apprentice

Re: Trailing spaces in Teradata Fastexport

The best "workaround" would be switching to TPT & DELIMITED format. 

Enthusiast

Re: Trailing spaces in Teradata Fastexport

Since it will take some time to implement the entire thing in TPT.We have already identified that need in the long run but as of now we were seeking for some quick workaround.Will writing an outmod script to trim the spaces seem a viable option.Will be it better in terms of performance compared to 'sed' or 'awk' process in Unix.Any other suggestions are also welcome.

 

 

 

 

 

Regards,

Indranil Roy

Senior Apprentice

Re: Trailing spaces in Teradata Fastexport

Writing & testing an OutMod might need more time than switching to TPT.

 

There's no need to trim the spaces, simply concatenate the trimmed columns (VarChars) instead of Chars, no final cast. The result will be a single VarChar column with the two length bytes, but no trailing spaces. Now your OutMod simply strips off the first two bytes.