The previous example demonstrated how to load a complete document as a large object into Teradata and use built in analytic functions to parse it into a usable dataset.
This is convenient but limited: we need to parse this document every time we need to use it, as the original document is not directly usable for analytics, JSON documents are currently limited to 16MB in Teradata and it may be inconvenient to fix data quality or formatting issues within the document stored as a CLOB.
In this example, we will parse our JSON document using the Python json package and load it as a table that can be used directly and efficiently for analysis.
Python json and list manipulation functions, along with the Teradata SQL driver for Python make this process really simple and efficient.
For this example, we will use the boundaries of the world countries available on https://datahub.io.
Let's get into it.
Open you favourite Python 3 interpreter and make sure you have the following packages installed:
- wget
- teradatasql
- getpass