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:

print("Hello World!")

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.

-- Create a new database called STO
CREATE DATABASE STO
AS PERMANENT = 60e6, -- 60MB
    SPOOL = 120e6; -- 120MB

-- Check the current Teradata user
SELECT USER;

-- Replace <CURRENT_USER_VALUE> with the value returned by SELECT USER
GRANT CREATE EXTERNAL PROCEDURE ON STO TO <CURRENT_USER_VALUE>;

You can upload the script to Teradata using the following procedure call.

Note

Adjust the path to match your client OS and where the local file is located.

Linux/macOS example:

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

Windows example:

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

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.

-- We switch to STO database
DATABASE STO; 

-- We tell Teradata where to look for the script. This can be
-- any string and it will create a symbolic link to the directory
-- where our script got uploaded. By convention, we use the
-- database name.
SET SESSION SEARCHUIFDBPATH = sto;

-- We now call the script. Note how we use a relative path that
-- starts with `./sto/`, which is where SEARCHUIFDBPATH
-- is pointing.
SELECT *
FROM SCRIPT(
  SCRIPT_COMMAND('python3 ./sto/helloworld.py')
  RETURNS ('Message varchar(512)'));

The last call should return:

Message
------------
Hello World!
Hello World!
Hello World!
Hello World!

That was a lot of work and we are still at Hello World. Let's try to pass some data into SCRIPT.