Teradata Parallel Transporter Supports Quoted VARTEXT in TTU14.00

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

Re: Teradata Parallel Transporter Supports Quoted VARTEXT in TTU14.00

Exact version is 14.10.00.05.

Table DDL and record that failed are as below:

CREATE MULTISET TABLE SAMPLE_TEST ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      PI_ID VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      FIELD1 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      FIELD1 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      FIELD1 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      FIELD1 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      FIELD1 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      FIELD1 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,

      FIELD1 VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC

 )

PRIMARY INDEX ( PI_ID );

PT123456789|E001234567|01-01-2015|spitting up||"lot"most^who=mother|concern|XYZ

-----

In the mean time, I tried creating a tpt script to load this data. I used the below attributes for the data connector.

DEFINE OPERATOR FILE_READER_OPERATOR

          TYPE   DATACONNECTOR PRODUCER

          SCHEMA Load_File_TD_TEST

     ATTRIBUTES

     (

          VARCHAR FileName            = 'samplefile.txt',

          VARCHAR OpenMode            = 'Read',

          VARCHAR Format              = 'Delimited',

          VARCHAR IndicatorMode       = 'N',

          VARCHAR AcceptExcessColumns = 'Y',

          VARCHAR TextDelimiter       = '|',

          VARCHAR QuotedData       = 'Optional'

     );

But still got the failure. Am I missing any attribute/used a wrong one?

Enthusiast

Re: Teradata Parallel Transporter Supports Quoted VARTEXT in TTU14.00

Hi Steve,

I changed the attribute list to below, where I changed the character used for OpenQuoteMark and CloseQuoteMark from the default double quotes (") to single quote ('). 

Then the TPT script was successful.

 ATTRIBUTES

     (

          VARCHAR FileName            = 'samplefile.txt',

          VARCHAR OpenMode            = 'Read',

          VARCHAR Format              = 'Delimited',

          VARCHAR IndicatorMode       = 'N',

          VARCHAR AcceptExcessColumns = 'Y',

          VARCHAR TextDelimiter       = '|',

          /*VARCHAR QuotedData       = 'Optional',

          VARCHAR EscapeQuoteDelimiter       = '"',*/

          VARCHAR OpenQuoteMark       = ''',

          VARCHAR CloseQuoteMark  = '''

     );

But probably this is more of a work around. Do you have any suggestions.
Teradata Employee

Re: Teradata Parallel Transporter Supports Quoted VARTEXT in TTU14.00

In 13.10, we did not support quoted delimited data.

Thus, the field in question:

|"lot"most^who=mother|concern|XYZ

would have loaded with the quote characters included in the data.

Starting in 14.0, we added the feature and when you enable it, your data has to adhere to the parsing rules.

And the fied in question does not follow the rules and that is why the error is generated.

-- SteveF
Enthusiast

Re: Teradata Parallel Transporter Supports Quoted VARTEXT in TTU14.00

A direct link to the documentation and a few examples would really help here. Specifically, I was looking to how to export into a format MS Excel would understand and failed to find it here or in Google. Fortunately somebody else in my company has put the solution on our internal wiki. Not sharing myself it as unclear about copyright etc. :-(

Enthusiast

Re: Teradata Parallel Transporter Supports Quoted VARTEXT in TTU14.00

i am using TPT 15.10 to export data directly export data to HDFS, but i am having issue with EscapeQuoteDelimiter and EscapeTextDelimiter  both are ' \ ' this is giveng me problem when escape char itself is part of data

ex:

If Teradata value :- "abc\","sad","def" 

Expected output :- "abc\","sad","def"

This is coming as expected on my dev Teradata and Dev cluster, but on my production Teradata and production cluster same script giving me the output as 

"abc\","sad","def"

 this is giving me problem while parsing the data, is this something related to database settings

TPT  Version on my Dev

    TDICU................................... 15.10.01.00

     PXICU................................... 15.10.01.00

     PMPROCS................................. 15.10.00.05

     PMRWFMT................................. 15.00.00.02

     PMHADOOP................................ 15.10.01.00

     PMTRCE.................................. 13.00.00.02

     PMMM.................................... 15.10.00.03

     DCUDDI.................................. 15.10.00.12

     PMHEXDMP................................ 15.10.00.02

     PMHDFSDSK............................... 15.10.00.02

     PMUNXDSK................................ 15.10.00.02

TPT Version on Prod

     TDICU................................... 15.10.00.00

     PXICU................................... 15.10.00.00

     PMPROCS................................. 15.10.00.05

     PMRWFMT................................. 15.00.00.02

     PMHADOOP................................ 15.10.01.00

     PMTRCE.................................. 13.00.00.02

     PMMM.................................... 15.10.00.03

     DCUDDI.................................. 15.10.00.12

     PMHEXDMP................................ 14.10.00.02

     PMHDFSDSK............................... 15.10.00.02

     PMUNXDSK................................ 15.10.00.02

Only big diffrence is between version PMHEXDMP, what is it is that the root cause for the problem i am facing