UDF Examples

Scalar functions

User defined scalar functions (keyword SCALAR) are the simplest case of user defined scripts, returning one scalar result value (keyword RETURNS) or several result tuples (keyword SET) for each input value or tuple.

The scripts must implement a function run() in which the processing is done. This function is called during the execution and gets a kind of context as parameter, which is the actual interface between the script and the database.

In the following example, a script is defined that returns the maximum of two values. This is equivalent to the CASE expression CASE WHEN x>=y THEN x WHEN x<y THEN y ELSE NULL END.

--/
CREATE LUA SCALAR SCRIPT my_maximum (a DOUBLE, b DOUBLE)
    RETURNS DOUBLE AS
function run(ctx)
 if ctx.a == null or ctx.b == null
    then return null
 end
 if ctx.a > ctx.b
    then return ctx.a
    else return ctx.b
 end
end

/
SELECT x,y,my_maximum(x,y) FROM t;

Aggregate and analytic functions

UDF scripts are important if the script processes multiple rows at once. You can create any kind of aggregate or analytic function. By defining the keyword SET you specify that multiple input tuples are processed. Within the run() method, you can iterate through this data using the method next().

Additionally, scripts can either return a single scalar value (keyword RETURNS) or multiple result tuples (keyword EMITS).

The following example defines two scripts: the aggregate function my_average (simulates AVG) and the analytic function my_sum. The scripts create three values per input row: a sequential number, the current value, and the sum of the previous values. The second script processes the input data in sorted order using the ORDER BY clause.

--/
CREATE LUA SET SCRIPT my_average (a DOUBLE)
    RETURNS DOUBLE AS
function run(ctx)
  if ctx.size() == 0
    then return null
  else
    local num_sum = 0
    repeat
      if ctx.a ~= null then
        num_sum = num_sum + ctx.a    
      end
    until not ctx.next()
    return num_sum/ctx.size() 
  end
end
/

SELECT my_average(x) FROM t;

--/
CREATE LUA SET SCRIPT my_sum (a DOUBLE)
    EMITS (count_values DOUBLE, val DOUBLE, sum_values DOUBLE) AS
function run(ctx)
  local num_sum = 0
  local num_count = 0
  repeat
    if ctx.a ~= null then
      num_sum = num_sum + ctx.a
      num_count = num_count + 1
      ctx.emit(num_count,ctx.a,num_sum)
       end
  until not ctx.next()
end
/

SELECT my_sum(x ORDER BY x) FROM t;

Dynamic input and output parameters

To create flexible scripts with dynamic parameters, you can use the syntax (...) within CREATE SCRIPT. You can use these scripts for any input data type, such as a maximum function independent of the data type, and for multiple input columns. You can make the number of output parameters and their type dynamic by using an EMITS script.

To access and evaluate dynamic input parameters in UDF scripts, extract the number of input parameters and their types from the metadata, then access each parameter value by its index. For example, in Python, the number of input parameters is stored in the variable exa.meta.input_column_count.

If the UDF script is defined with dynamic output parameters, the actual output parameters and their types are dynamically determined whenever the UDF is called. There are three possibilities:

• You can specify the output parameters directly in the query after the UDF call, using the EMITS keyword followed by the names and types the UDF should output in this specific call.

• If the UDF is used in the top level SELECT of an INSERT INTO SELECT statement, the columns of the target table are used as output parameters.

• If EMITS and INSERT INTO SELECT are not specified, the database tries to call the function default_output_columns() (the name varies, this example is for Python) which returns the output parameters dynamically, for example, based on the input parameters. This method can be implemented by the user. For more information see Details for Programming Languages.

The default_output_columns() function is called before the run(ctx) function, since it provides input for the query execution plan. The default_output_columns() function can only access the exa object, which means that it can either access the metadata exa.meta, the connection object (CREATE CONNECTION) trough exa.get_connection(), or other scripts through exa.import_scripts(). The emit columns can therefore not be data dependent and must be known before.

Example 1:

-- Define a simple sampling script where the last parameter defines
-- the percentage of samples to be emitted.

--/
CREATE PYTHON3 SCALAR SCRIPT sample_simple (...) EMITS (...) AS
from random import randint, seed
seed(1001)
def run(ctx):
 percent = ctx[exa.meta.input_column_count-1]
 if randint(0,100) <= percent:
  currentRow = [ctx[i] for i in range(0, exa.meta.input_column_count-1)]
  ctx.emit(*currentRow)
/

Example 2:

-- This is the same UDF, but output arguments are automatically generated
-- to avoid explicit EMITS definition in SELECT.
-- In default_output_columns(), a prefix 'c' is added to the column names
-- because the input columns are auto-generated numbers

--/
CREATE PYTHON3 SCALAR SCRIPT sample (...) EMITS (...) AS
from random import randint, seed
seed(1001)
def run(ctx):
 percent = ctx[exa.meta.input_column_count-1]
 if randint(0,100) <= percent:
  currentRow = [ctx[i] for i in range(0, exa.meta.input_column_count-1)]
  ctx.emit(*currentRow)
def default_output_columns():
 output_args = list()
 for i in range(0, exa.meta.input_column_count-1):
  name = exa.meta.input_columns[i].name
  type = exa.meta.input_columns[i].sql_type
  output_args.append("c" + name + " " + type)
 return str(", ".join(output_args))
/

Example 3:

-- The first UDF requires to specify the output columns via EMITS.
-- Here, 20% of rows should be extracted randomly.

SELECT sample_simple(id, user_name, page_visits, 20)
 EMITS (id INT, user_name VARCHAR(100), PAGE_VISITS int)
 FROM people;

-- In case of INSERT INTO, the UDF uses the target types automatically
--/
CREATE TABLE people_sample LIKE people;
INSERT INTO people_sample
 SELECT sample_simple(id, user_name, page_visits, 20) FROM people;

Access to external services

You can exchange data with external services in scripts. In the following example, a list of URLs stored in a table is processed. The script reads the corresponding documents from a web server and calculates the length of the document.

Each script language provides different libraries to connect to the internet.

Example:

--/
CREATE LUA SCALAR SCRIPT length_of_doc (url VARCHAR(50))
    EMITS (url VARCHAR(50), doc_length DOUBLE) AS
HTTP = require("socket.http")
function run(ctx)
    file = http.request(ctx.url)
    if file == nil then error('Cannot open URL ' .. ctx.url) end
    ctx.emit(ctx.url, unicode.utf8.len(file))
end
/
 
SELECT length_of_doc(url) FROM t;

Example results:

User-defined ETL using UDFs

You can use UDF scripts to implement flexible ETL processes by defining how to extract and convert data from external sources. For more information, see User-defined IMPORT/EXPORT Using UDFs.