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.
s-Server uses Maven for Java development. Before developing a UDF, UDX, or adapter, you will need to have Maven installed on your system. For more information on Maven, see https://maven.apache.org/
Subjects covered include:
Before using materials in this directory, particularly the install script, you will want to set $SQLSTREAM_HOME as an environment variable, to the location sqlstream//s-Server/. You may also find it useful to add this location to your PATH.
Note: one way to set an environment variable and modify your PATH for development purposes is to add the following lines to the .bashrc script in your home directory:
SQLSTREAM_HOME="/home/drew/sqlstream/<VERSION>/s-Server/" export SQLSTREAM_HOME PATH=$PATH:$HOME/bin:$JAVA_HOME/bin:$SQLSTREAM_HOME/bin export PATH
If you install s-Server as sudo, it may be more useful to set an environment variable for your entire system, using, for example, a script in /etc/profile.d
SQLstream’s base SQL capabilities can be extended with plugins in several ways: adapters, UDFs, and UDXs. Plugins are declared to SQLstream as SQL/MED “wrappers”. For general info about SQLstream’s SQL/MED support, see Reading Data from RDBMS Systems.
An adapter is a plugin which allows SQLstream to integrate with an external system. An adapter can implement a data source, bringing data from the external system into SQLstream. Or, an adapter can implement a data sink, delivering streaming data from SQLstream to an external system. In either case, the adapter is responsible for connecting to the external system and transforming data between SQLstream’s relational format and the external data format. If needed, an adapter can have its own thread(s).
Adapters implement SQL/MED “foreign servers” which then host “foreign streams” or “foreign tables”. Foreign streams and tables can be used in SQLstream DML statements just like native streams and tables.
The topic Writing an Extensible Common Data Framework Plugin describes how to customize this framework.
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.
UDFs operate like built-in functions such as FLOOR() or LOWER(). See the topics FLOOR() or LOWER() in the s-Server Streaming SQL Reference Guide for more details. For each occurrence of a UDF within a SQL statement, that UDF is called once per row with scalar parameters that can be constants or column values in that row.
See Writing a Java UDF.
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 Java UDX.
If you set ASPEN_DEBUG_PORT is set in your environment, the server will be started with debugging enabled, and you can set breakpoints and remotely debug your ECDA or UDX code using an Integrated Development Environment (IDE).
Remote debugging can be done from any suitable IDE (Eclipse, IntelliJ, and so on) with access to your UDX class and any dependencies. Follow the instructions for your IDE.
To set ASPEN_DEBUG_PORT when running in non-root foreground mode, use the following code:
export ASPEN_DEBUG_PORT=8000 s-server
To set ASPEN_DEBUG_PORT when running in root mode as a service, it will be necessary to set the variable within /etc/default/s-serverd to make sure it is picked up:
sudo service s-serverd stop sudo echo "export ASPEN_DEBUG_PORT=8000" >> /etc/default/s-serverd sudo service s-serverd start