SQLStreamBuilder Javascript Functions

Introduction

SQLStreamBuilder Javascript Functions allow you to write powerful functions in Javascript that you can use to enhance the functionality of SQL. User Functions can be simple translation functions like Celsius to Fahrenheit, more complex business logic, or even looking up data from external sources.

Creating a Javascript Function

User functions are written in Javascript. A you write them you create a library of useful functions. To write your first Javascript Function:

  • Log into SQLStreamBuilder.
  • Select SQLStreamBuilder
  • Select Functions -> Add
  • Name it HELLO_WORLD, give it a short description, select Javascript as the language.
  • Select INT as output type and add INT to the input type by selecting it from the list and hitting the plus button.
  • Paste this code into the Javascript editor:
// Add 1 to a number
function HELLO_WORLD(num){
    return num+1;
}
HELLO_WORLD($p0); // this line must exist
  • Click Save.

user_functions

Using a Javascript Function in a SQL query

Once created, you can use a User Function in your SQL statement like:

-- use a static number
SELECT HELLO_WORLD(1) FROM dual;

-- use a column
SELECT HELLO_WORLD(temp) FROM mytemps;

-- in the predicate
SELECT temp FROM mytemps WHERE HELLO_WORLD(temp) > 20;

-- casting proper datatypes
SELECT HELLO_WORLD(CAST(mytempstring AS integer)) as integer_temp FROM mytemps;

Note: that valid inputs can be a field in the source virtual table or any other valid input. Functions must be upper case.

Invoke Java methods from Javascript Functions

User Functions have access to the Java8 API, this increases the overall usefulness and power. For instance:

function GETPLANE(icao) {
  try {
    var c = new java.net.URL('http://yyyyyy.io' + icao).openConnection();
    c.requestMethod='GET';
    var reader = new java.io.BufferedReader(new java.io.InputStreamReader(c.inputStream));
    return reader.readLine();
  } catch(err) {
    return "Unknown: " + err;
  }
}
GETPLANE($p0);

Developing Javascript Functions

When developing Javascript functions that are more complicated than just simple logic, it is recommended to use the jjs command line utility to create and iterate while writing functions. When the function does what you want then migrate it to the console. Additionally these files/functions can be saved in a source code control system like git/Github.

  • Create a file for your function. A good practice is to name the file the name of the function.
  • Create some sample input when calling the function.
  • Call jjs on the command line to test the function.
$>cat TO_EPOCH.js
function TO_EPOCH(strDate) {
   var strFmt = "yyyy-MM-dd HH:ss:mm";
   var c = new java.text.SimpleDateFormat(strFmt).parse(strDate).getTime()/1000;
   return c.toString();
}

print(TO_EPOCH("2019-02-02 22:23:13"));

then

$>jjs TO_EPOCH.js
1549167203

Once you are happy with the code, copy/paste the function only to your code window when creating the Javascript Function in SQLStreamBuilder.