16.20 - Example: Creating JSON Output by Using the SCRIPT Table Operator to Invoke Python Modules - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

The following example uses Python built-in modules to create a JSON object from a website URL query string. This is useful in converting web URL data to JSON so it can be queried using the JSON data type.

The script requires that Python version 2.6 or higher is installed on the system.

The ‘urltojson.py’ Python script:

#!/usr/bin/python
import sys
import json
import urlparse
for line in sys.stdin:
    print json.dumps(urlparse.parse_qs(urlparse.urlparse(line.rstrip('
	\n')).query))

SQL to install and run the script on sample data:

DATABASE mytestdb;
create table sourcetab(url varchar(10000));
ins sourcetab('https://www.google.com/finance?q=NYSE:TDC');
ins sourcetab('http://www.ebay.com/sch/i.html?_trksid=p2050601.m570.l1313.TR0.TRC0.H0.Xteradata+merchandise&_nkw=teradata+merchandise&_sacat=0&_from=R40');
ins sourcetab('https://www.youtube.com/results?search_query=teradata%20commercial&sm=3');
ins sourcetab('https://www.contrivedexample.com/example?mylist=1&mylist=2&mylist=...testing');
-- This assumes that urltojson.py is in the current directory.
call sysuif.replace_file('urltojson', 'urltojson.py', 'cz!urltojson.py', 0);
set session searchuifdbpath=mytestdb;
select cast(JSONresult as JSON)
from SCRIPT(
    ON(select url from sourcetab)
    SCRIPT_COMMAND('./mytestdb/urltojson.py')
    RETURNS(' JSONresult VARCHAR(10000)')
);

The result:

JSONresult
----------------------------------------------------------------------------------------
{"q": ["NYSE:TDC"]}
{"mylist": ["1", "2", "...testing"]}
{"_from": ["R40"], "_trksid": ["p2050601.m570.l1313.TR0.TRC0.H0.Xteradata merchandise"], "_nkw": ["teradata merchandise"], "_sacat": ["0"]}
{"search_query": ["teradata commercial"], "sm": ["3"]}