Python 3

This article explains how to use Python 3 for UDF scripting in Exasol.

For additional information about Python, refer to the official Python Documentation.

run() and cleanup()

The method run() is called for each input tuple (SCALAR) or each group (SET). Its parameter is a type of execution context and provides access to the data and the iterator in case of a SET script.

To initialize expensive steps (such as opening external connections), you can write code outside the run() method, since this code is executed once by each virtual machine at the start of execution. For deinitialization purposes, the method cleanup() is available. This method is called once for each virtual machine at the end of execution.

Parameters

The internal Python data types are not identical to SQL data types. Therefore, casts must be done for the input and output data.

Usage notes

• The input parameters can be addressed by their names, for example, ctx.my_input. In this case, ctx refers to the name of the context parameter that is passed to the run() method.

  You can also use a dynamic number of parameters by using the notation (...). The parameters can then be accessed through an index where ctx[0] is the first parameter. The number of parameters and their data types will be part of the metadata and determined when the script is called. For example: CREATE PYTHON3 SCALAR SCRIPT my_script (...).

• For better performance, use DOUBLE instead of DECIMAL.

• The data type datetime.datetime only supports six fractional digits, which means that TIMESTAMP input/output values are truncated to six fractional digits even if TIMESTAMP(7), TIMESTAMP(8), or TIMESTAMP(9) are used.

• The value None is the equivalent of the SQL NULL.

Data iterator

For scripts having multiple input tuples per call (keyword SET), you can iterate through that data using the method next(), which is accessible through the context. Initially, the iterator points to the first input row. For iterating you can use a while True loop which is aborted in case if not ctx.next().

If the input data is empty, then the run() method will not be called, and similar to aggregate functions the NULL value is returned as result (for example, SELECT MAX(x) FROM t WHERE false).

The method reset() resets the iterator to the first input element. This allows you to do multiple iterations through the data, if this is required for your algorithm.

The method size() returns the number of input values.

emit()

You can return multiple output tuples per call (keyword EMITS) using the method emit(). The method expects as many parameters as output columns were defined. In the case of dynamic output parameters, it is handy in Python to use a list object that can be unpacked using *.

For example: ctx.emit(*currentRow).

Import other scripts

You can import other scripts through the method exa.import_script(). The return value of this method must be assigned to a variable that represents the imported module.

Syntax

<alias> = exa.import_script('<schema>.<script>')

Examples

CREATE SCHEMA IF NOT EXISTS TEST;
            --/
            CREATE OR REPLACE PYTHON3 SCALAR SCRIPT TEST.PYTHON_DEMO() RETURNS VARCHAR(2000) AS
            def run(ctx):
            return "Minimal Python UDF"
            /
            select TEST.PYTHON_DEMO();
            CREATE SCHEMA IF NOT EXISTS LIB;
            --/
            CREATE OR REPLACE PYTHON3 SCALAR SCRIPT LIB.MYLIB() RETURNS INT AS
            def helloWorld():
            return "Hello Python3 World!"
            /
            CREATE SCHEMA IF NOT EXISTS TEST;
            --/
            CREATE OR REPLACE PYTHON3 SCALAR SCRIPT TEST.MYHELLOWORLD() RETURNS VARCHAR(2000) AS
            l = exa.import_script('LIB.MYLIB')
            def run(ctx):
            return l.helloWorld()
            /
            select TEST.MYHELLOWORLD();

Access connection definitions

The data that has been specified when defining connections with CREATE CONNECTION is available in Python UDF scripts through the method exa.get_connection("<connection name>"). The result is a Python object with the following fields:

Auxiliary libraries

The following libraries are provided that are not already part of the language:

/*
            This example loads from a webserver
            and processes the following file goalies.xml:
            <?xml version='1.0' encoding='UTF-8'?>
            <users>
            <user active="1">
            <first_name>Manuel</first_name>
            <last_name>Neuer</last_name>
            </user>
            <user active="1">
            <first_name>Joe</first_name>
            <last_name>Hart</last_name>
            </user>
            <user active="0">
            <first_name>Oliver</first_name>
            <last_name>Kahn</last_name>
            </user>
            </users>
            */
            --/
            CREATE PYTHON3 SCALAR SCRIPT process_users(url VARCHAR(500))
            EMITS (firstname VARCHAR(20), lastname VARCHAR(20)) AS
            import urllib.request
            import lxml.etree as etree
            def run(ctx):
            data = b''.join(urllib.request.urlopen(ctx.url).readlines())
            tree = etree.XML(data)
            for user in tree.findall('user/[@active="1"]'):
            fn = user.findtext('first_name')
            ln = user.findtext('family_name')
            ctx.emit(fn, ln)
            /
        

SELECT p.system_value 
FROM exa_parameters p
WHERE p.parameter_name in ('SCRIPT_LANGUAGES')
;

ALTER SYSTEM SET SCRIPT_LANGUAGES = 'PYTHON=builtin_python R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3';