This document is intended for developers who want to extend the base SQL capabilities of SQLstream with connections to external systems, custom functions, or row transformations usning Java.
Subjects covered include:
SQLstream now supports the integration of Python modules using Python 2.7 or 3.x.
A UDF is a plugin which takes one or more scalar parameters and returns a scalar result value. For more details, see the topic CREATE FUNCTION in the s-Server Streaming SQL Reference Guide. A UDF can implement complex calculations or interact with an external system. A UDF can also execute SQL - on a row by row basis of course - and so can access other stream data or, more likely, table data stored locally or accessed via SQL/MED. A UDF can execute any Java - though obviously it’s undesirable for a UDF to block for any length of time.
Currently SQLstream does not support Python UDFs.
A UDX is a plugin which takes normal UDF scalar parameters plus zero or more query expressions as cursor inputs and returns a virtual table or stream as output. For more details, see the topic CREATE FUNCTION in the SQLstream s-Server Streaming SQL Reference Guide.
For efficiency and performance, SQLstream/Farrago uses a system-defined calling convention rather than the SQL standard calling convention for “table functions”.
The UDX form that takes cursor inputs is particularly powerful. This form enables the UDX to continuously process one or more input streams of rows, emitting a resulting stream of rows. Any number of input rows can result in any number of output rows.
The UDX processing is performed on its own thread(s), allowing streaming input and output operations to be asynchronous to each other.
See Writing a Python UDX.