Run scripts on Teradata
Overview
Sometimes, you need to apply complex logic to your data that can't be easily expressed in SQL. One option is to wrap your logic in a User Defined Function (UDF). What if you already have this logic coded in a language that is not supported by UDF? Script Table Operator is a Teradata feature that allows you to bring your logic to the data and run it on Teradata. The advantage of this approach is that you don't have to retrieve data from Teradata to operate on it. Also, by running your data applications on Teradata, you leverage its parallel nature. You don't have to think how your applications will scale. You can let Teradata take care of it.
Prerequisites
You need access to a Teradata instance.
If you need a test instance of Teradata, you can provision one for free at https://www.teradata.com/try
Hello World
Let's start with something simple. What if we wanted the database to print "Hello World"?
Here is what we've got:
Let's analyze what just happened here. The SQL includes echo Hello World!, which is a Bash command. The script runs once on each AMP, so the number of returned rows depends on the number of AMPs in your Teradata system.
Returns:
This simple script demonstrates the idea behind the Script Table Operator (STO). You provide your script and the database runs it in parallel, once for each AMP. This is an attractive model in case you have transformation logic in a script and a lot of data to process. Normally, you would need to build concurrency into your application. By letting STO do it, you let Teradata select the right concurrency level for your data.
Supported languages
We used echo in Bash, but Bash is hardly a productive environment for expressing complex logic. What other languages are supported? The good news is that any binary that can run on Teradata nodes can be used in STO. Remember that the binary and all its dependencies must be installed on all your Teradata nodes. In practice, the available options depend on what your administrator is willing and able to maintain on the servers. Python is a popular choice.
Uploading scripts
Hello World is useful for testing, but what if you have existing logic in a larger file? You likely do not want to paste the entire script and escape quotes in an SQL query. The User Installed Files (UIF) feature solves the script upload issue.
Say you have helloworld.py script with the following content:
Create this file on your local machine. On Linux/macOS, place it at /tmp/helloworld.py. On Windows, use a path like C:\\Temp\\helloworld.py.
First, we need to setup permissions in Teradata. We are going to do this using a new database to keep it clean.
You can upload the script to Teradata using the following procedure call.
Adjust the path to match your client OS and where the local file is located.
Linux/macOS example:
Windows example:
Now that the script has been uploaded, you can call it like this. Note: Run this as a separate statement from the CALL above, or issue an ET/NULL if required by your client.
The last call should return:
That was a lot of work and we are still at Hello World. Let's try to pass some data into SCRIPT.
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.
Now insert the data. Insert each row in a separate statement:
We will use the following script to parse out query parameters:
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:
Windows example:
With the script installed, we will now retrieve data from urls table and feed it into the script to retrieve query parameters:
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.
Inserting SCRIPT output into a table
We have learned how to take data from Teradata, pass it to a script and get output. Is there an easy way to store this output in a table? Sure, there is. We can use the following pattern:
Now, let's inspect the contents of url_params table:
You should see the following output:
Summary
In this quick start, we learned how to run scripts against data in Teradata. We ran scripts using Script Table Operator (STO). The operator allows us to bring logic to the data. It offloads concurrency considerations to the database by running our scripts in parallel, one per AMP. All you need to do is provide a script and the database will execute it in parallel.