Passing data stored in Teradata to SCRIPT

So far, we have been using SCRIPT operator to run standalone scripts. But the main purpose to run scripts on Teradata is to process data that is in Teradata. Let's see how we can retrieve data from Teradata and pass it to SCRIPT.

We will start by creating a table with a few rows.

-- Switch to STO database.
DATABASE STO;

-- Create a table with a few urls
CREATE TABLE urls(url varchar(10000));

Now insert the data. Insert each row in a separate statement:

INSERT INTO urls VALUES ('https://www.google.com/finance?q=NYSE:TDC');
INSERT INTO urls VALUES ('http://www.ebay.com/sch/i.html?_trksid=p2050601.m570.l1313.TR0.TRC0.H0.Xteradata+merchandise&_nkw=teradata+merchandise&_sacat=0&_from=R40');
INSERT INTO urls VALUES ('https://www.youtube.com/results?search_query=teradata%20commercial&sm=3');
INSERT INTO urls VALUES ('https://www.contrivedexample.com/example?mylist=1&mylist=2&mylist=...testing');

We will use the following script to parse out query parameters:

from urllib.parse import urlparse
from urllib.parse import parse_qsl
import sys

for line in sys.stdin:
    # remove leading and trailing whitespace
    url = line.strip()
    parsed_url = urlparse(url)
    query_params = parse_qsl(parsed_url.query)

    for element in query_params:
        print("\t".join(element))

Note how the script assumes that urls will be fed into stdin one by one, line by line. Also, note how it prints results line by line, using the tab character as a delimiter between values.

Let's install the script.

Linux/macOS example:

CALL SYSUIF.install_file('urlparser', 'urlparser.py', 'cz!/tmp/urlparser.py');

Windows example:

CALL SYSUIF.install_file('urlparser', 'urlparser.py', 'cz!C:/Temp/urlparser.py');

With the script installed, we will now retrieve data from urls table and feed it into the script to retrieve query parameters:

-- We inform Teradata to create a symbolic link from the UIF directory to ./sto/
SET SESSION SEARCHUIFDBPATH = sto;

SELECT *
  FROM SCRIPT(
    ON(SELECT url FROM urls)
    SCRIPT_COMMAND('python3 ./sto/urlparser.py')
    RETURNS ('param_key varchar(512)', 'param_value varchar(512)'));

As a result, we get query params and their values. There are as many rows as key/value pairs. Also, since we inserted a tab between the key and the value output in the script, we get 2 columns from STO.

param_key   |param_value
-----------------------------------------------------------------
q           |NYSE:TDC
_trksid     |p2050601.m570.l1313.TR0.TRC0.H0.Xteradata merchandise
search_query|teradata commercial
_nkw        |teradata merchandise
sm          |3
_sacat      |0
mylist      |1
_from       |R40
mylist      |2
mylist      |...testing