The following example uses some of 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 urltojson.py Python script:
import sys import json from urllib.parse import urlparse from urllib.parse import parse_qs for line in sys.stdin: print(json.dumps(parse_qs(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.INSTALL_FILE('urltojson', 'urltojson.py', 'cz!urltojson.py'); SET SESSION SEARCHUIFDBPATH = mytestdb; SELECT CAST(JSONresult as JSON) FROM SCRIPT ( ON (SELECT url FROM sourcetab) SCRIPT_COMMAND('/opt/teradata/languages/Python/bin/python3 ./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"]}