Python 2.7 (Deprecated)

Python 2 has reached end of life and is no longer supported in Exasol. We recommend that you use Python 3 in all UDFs.

Python 2.7 (the final version of the Python 2 series) reached end of life status in 2020 and is no longer supported in Exasol. We therefore recommend that you use Python 3 in all your UDFs. If you need to continue using Python 2 in your UDFs, refer to Using Python 2 with Exasol 7.1.20 or later for a suggested workaround.

run() and cleanup() Methods

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 at the beginning by each virtual machine. For deinitialization purpose, the method cleanup() is available. It is called once for each virtual machine, at the end of the execution.

Parameters

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

The Python keyword None is the equivalent of SQL NULL.

The input parameters can be addressed by their names. For example, ctx.my_input. 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 via the notation (...), for example, CREATE PYTHON SCALAR SCRIPT my_script (...). The parameters can then be accessed through an index (ctx[0] for the first parameter). The number of parameters and their data types (both determined during the call of the script) are part of the metadata.

For better performance, use DOUBLE instead of DECIMAL for parameter types.

Metadata

You can access the following metadata through global variables:

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).

Additionally, there is a method reset() that resets the iterator to the first input element. Therefore, you can 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 which can be unpacked using * (like in the example above: ctx.emit(*currentRow)).

Import of Other Scripts

Other scripts can be imported through the method exa.import_script(). The return value of this method must be assigned to a variable, representing the imported module.

Syntax

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

Example

--/
CREATE OR REPLACE PYTHON SCALAR SCRIPT LIB.MYLIB() RETURNS INT AS
def helloWorld():
  return "Hello Python World!"
/

--/
CREATE OR REPLACE PYTHON SCALAR SCRIPT TEST.MYHELLOWORLD() RETURNS VARCHAR(2000) AS
l = exa.import_script('LIB.MYLIB')
def run(ctx):
  return l.helloWorld()
/

select TEST.MYHELLOWORLD();

Accessing 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:

Example

/*
  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 PYTHON SCALAR SCRIPT process_users(url VARCHAR(500))
EMITS (firstname VARCHAR(20), lastname VARCHAR(20)) AS
import re
import xml.etree.cElementTree as etree
from urllib2 import urlopen

def run(ctx):
    content = etree.parse(urlopen(ctx.url))
    for user in content.findall('user/[@active="1"]'):
        ctx.emit(user.find('first_name').text, user.find('last_name').text)
/
 
SELECT process_users ('http://www.my_valid_webserver/goalies.xml')
FROM DUAL;

Adapter Script Callback Function

For virtual schemas an adapter script must define the function adapter_call(request_json). The parameter is a string in JSON containing the Virtual Schema API request. The return value must also be a string in JSON containing the response. For the Virtual Schema API Documentation, see Information for Developers. The callback function will be executed only on a single node.