Skip to content

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 STRING as output type and add STRING to the input type by selecting it from the list and hitting the plus button.
  • Paste this code into the Javascript editor:
// check to see if the card is VISA
function HELLO_WORLD(card){
    var cardType = "Other";
    if (card.charAt(0) == 4){
      cardType = "Visa";
    }
    return cardType;
}
HELLO_WORLD($p0); // this line must exist
  • Click Save.

Using a Javascript Function in a SQL query

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

-- simple usage
SELECT HELLO_WORLD(card) AS IS_VISA
FROM ev_sample_fraud;

-- in the predicate
SELECT amount, card
FROM ev_sample_fraud
WHERE HELLO_WORLD(card) = "Visa";

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 only the function to your code window when creating the Javascript Function in SQLStreamBuilder.