Examples | SCRIPT Calls Python for Data Type Conversion | Vantage - Example: SCRIPT Calls Python for Data Type Conversion - Advanced SQL Engine - Teradata Database

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
fsi1592016213432.ditamap
dita:ditavalPath
fsi1592016213432.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™

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;