0

I have a table with example data as follows:

Num      JobId

 1      12345678

where Num is of Integer datatype, and JobId is of Varbyte datatype.

I need to extract it, and the TPT script I have used for this is:

USING CHARACTER SET UTF8
DEFINE JOB EXPORT_DELIMITED_FILE
DESCRIPTION 'Export' ( DEFINE SCHEMA FILE_SCHEMA (
Num INTEGER,
JobID Varbyte(16)
);

DEFINE OPERATOR SQL_SELECTOR
TYPE SELECTOR
SCHEMA FILE_SCHEMA ATTRIBUTES
(
VARCHAR PrivateLogName = 'selector_log',
VARCHAR TdpId = '192.168.xx.xxx',
VARCHAR UserName = 'dbc', VARCHAR UserPassword = 'dbc',
VARCHAR SelectStmt = 'SELECT * FROM AdventureWorksDW.DB1_TB1;',

DEFINE OPERATOR FILE_WRITER
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = 'filepath',
VARCHAR FileName = 'DB1_TB1.csv',
VARCHAR FORMAT= 'DELIMITED', //delimited doesn't work
VARCHAR TextDelimiter= '|',
VARCHAR OpenMode = 'Write'
);


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

I need the csv file in the format:

   1 | 12345678

How to achieve this?

linux
  • 157
  • 11

2 Answers2

1

Binary data is not supported for delimited format. But you can transform it to a string using from_bytes in your select:

USING CHARACTER SET UTF8
DEFINE JOB EXPORT_DELIMITED_FILE
DESCRIPTION 'Export' 
 (
   APPLY TO OPERATOR
    ( $FILE_WRITER[1]
      ATTR ( VARCHAR PrivateLogName = 'dataconnector_log',
             VARCHAR DirectoryPath = 'filepath',
             VARCHAR FileName = 'DB1_TB1.csv',
             VARCHAR FORMAT= 'DELIMITED',
             VARCHAR TextDelimiter= '|',
             VARCHAR OpenMode = 'Write'
           )
    )
   SELECT *
   FROM OPERATOR
    ( $SELECTOR[1]
      ATTR
      ( PrivateLogName = 'selector_log',
        TdpId = '192.168.xx.xxx',
        UserName = 'dbc',
        UserPassword = 'dbc',
        SelectStmt = 'SELECT num, from_bytes(jobid, ''base16'') FROM td01.testtab;'
      )
    );
);
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Using `base16` changes the output i.e. `~  Ça`4· ÞÂ( ³ ¦Ã t 4 H `ã G ã` becomes `7EC28CC28AC7616034B70EDEC2289EB308A6C39374993409480C60E3964704E3` . Is there any way to retain the original value? I tried with other variants of `base` and `ASCII `as well but still same issue. – linux Feb 21 '22 at 07:06
  • Bytes are just bytes, what is the *original value* supposed to be? – dnoeth Feb 21 '22 at 07:27
0

The below TD query will work to export the data from VARBYTE datatype column.

select from_bytes(column_name, 'base16')
from table_name
Sivakumar Sundaram
  • 1
  • 1