The following example shows the script table operator returning a BYTE and other data types in the return value list:
SELECT * FROM SCRIPT( ON script_syn_res_tab001 SCRIPT_COMMAND('cat') DELIMITER(',') RETURNS ('uid int', 'helen BYTE', 'word varchar(10)', 'id int') ) as srpt;
In the following Python script, the print command returns HEX numbers for a BYTE (20) return value.
The mapper2.py script:
import sys print('040A1b202EC4a256406F7D7Ffffcf3 33 22.4 abc')
Save the mapper2.py script in /home/tdatuser or modify the directory location in the following SQL.
Test the script:
CALL SYSUIF.INSTALL_FILE('mapper2', 'mapper2.py', 'cz!/root/mapper2.py'); SET SESSION SEARCHUIFDBPATH = mytestdb; SELECT * FROM SCRIPT ( SCRIPT_COMMAND ('/opt/teradata/languages/Python/bin/python3 ./mytestdb/mapper2.py') RETURNS ('v byte(50)', 'v2 int', 'v3 float', 'v4 varchar(10)') ) as d1;
Result:
v | v2 | v3 | v4 |
---|---|---|---|
040A1B202EC4A256406F7D7FFFFCF30000000000 | 33 | 2.24000000000000E 001 | abc |
040A1B202EC4A256406F7D7FFFFCF30000000000 | 33 | 2.24000000000000E 001 | abc |
040A1B202EC4A256406F7D7FFFFCF30000000000 | 33 | 2.24000000000000E 001 | abc |
040A1B202EC4A256406F7D7FFFFCF30000000000 | 33 | 2.24000000000000E 001 | abc |
In the example, the delimiter is a tab. You can specify another character as the delimiter. For example, to specify a comma as the delimiter:
import sys print('040A1b202EC4a256406F7D7Ffffcf3,33,22.4,abc')
Change the SELECT statement accordingly to include the DELIMITER clause :
CALL SYSUIF.INSTALL_FILE('mapper3', 'mapper3.py', 'cz!/root/mapper3.py'); SET SESSION SEARCHUIFDBPATH = mytestdb; SELECT * FROM SCRIPT ( SCRIPT_COMMAND ('/opt/teradata/languages/Python/bin/python3 ./mytestdb/mapper3.py') DELIMITER(',') RETURNS ('v byte(50)', 'v2 int', 'v3 float', 'v4 varchar(10)') ) as d1;