Javascript Functions in Flink with SQLStreamBuilder

November 5, 2019 in SQLStreamBuilder



Javascript Functions in Flink with SQLStreamBuilder

Eventador SQLStreamBuilder allows you to write stream processors and perform computations on streaming data using SQL. This allows many more people in your organization to make use of, and get value from, streaming data.

But what if the SQL grammar just doesn’t quite cover your use case? Or maybe you just need something custom? What if we want to enrich or validate data based on more complex business logic? What if we need to call a function in a proprietary library?

SQLStreamBuilder Javascript Functions to the rescue. Javascript Functions allow you to create arbitrary functions and call them directly from SQL. You con’t need to restart your system, stop your cluster, or compile/recompile anything. Just specify a function and get to business.

You write them like this:

// hello world
function HELLOWORLD() {
return "Hello World";
}
HELLOWORLD();

And call them like this:

SELECT HELLOWORLD() FROM dual;

Design motivations

When developing an interface to extend functions to SQLStreamBuilder, there was a couple of critical implementation characteristics we felt had to be incorporated into the final product:

  • When you define a function it must not require the restart or recompile of the backend services. It must be evaluated dynamically.
  • It must give you instant feedback in terms of validity, so you know you are writing valid javascript.
  • It must guide you through specification of the inputs and outputs and associated data types.
  • It must be high performance.

KSQL has UDFs, and so does Flink. But neither of these solutions met the above criteria. The solution came from the Oracle Nashorn Engine. Nashorn is included in Java8 and allows for writing ECMA standard javascript that is dynamically run on the JVM. It’s future proof in the sense that GraalVM continues to provide this type of functionality going forward—and supports many different languages.

Because of this decision, we also got another very powerful characteristic—we can utilize the entire Java8 API as well. You can invoke Java methods from Javascript, for example, like this:

var x = java.text.SimpleDateFormat(strFmt).parse(strDate);

Check out these links on type conversions, invoking Java methods from javascript and using jjs (recommended) to write javascript.

Things you can do with SQLStreamBuilder Javascript Functions

There are a ton of powerful and, frankly, fun things you can do with javascript functions. You have a massive amount of programming capabilities because of the availability of native Javascript functions and the Java8 API. Just a couple examples are:

Easily format any time of date string into a EPOCH format (or any other):

// convert date string to EPOCH
function TO_EPOCH(strDate) {
var strFmt = "yyyy-MM-dd'T'HH:ss:mm.SSS";
var c = new java.text.SimpleDateFormat(strFmt).parse(strDate).getTime()/1000;
return c.toString();
}
TO_EPOCH($p0);

Look up values from other services or APIs:

// get airplane details from external API
function GETPLANE(icao) {
try {
var c = new java.net.URL('http://xxxyyyzzz.org/' + 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)

Perform custom unit conversions:

// Deduct 32, then multiply by 5, then divide by 9
function FTOC(f){
o = (((f-32)*5)/9);
return o;
}
FTOC($p0);

Building a SQLStreamBuilder Javascript Function

It’s as simple as it sounds–just create a bit of Javascript and reference it in your SQL statement as a function. Let’s create our own HELLO WORLD example:

  1. In SQLStreamBuilder select Functions, then Add.
  2. Enter a logical name for the function. Name it HELLOWORLD and give it a description (optional).
  3. Select Javascript as the language
  4. Select an output type. In this case, let’s use String.
  5. We don’t have any inputs.
  6. Now we create the actual Javascript code. Let’s use:
// hello world
function HELLOWORLD() {
return "Hello World";
}
HELLOWORLD();

Note: the function name should be upper case.

  1. Save the function, and we are ready to use it in SQL queries.

Let’s run a SQL query to test it:

-- assuming we have a virtual table named mytest
SELECT HELLOWORLD() AS hello FROM dual;

The results should look like:

hello
-----
"Hello World"
view raw javascriptfunctions hosted with ❤ by GitHub

It’s that easy. You’ve just ran your first query using a Javascript Function. No need for cluster restart/recovery, no recompiling the server(s). All dynamically evaluated in the JDK at runtime.

Creating Javascript Functions (UDFs) for streaming SQL with Kafka and Flink in SQLStreamBuilder
Creation of SQLStreamBuilder Javascript Functions

A library of functions

You can build a library of useful functions that you and your team have access to. Maybe they are just utility functions your team uses a lot, or perhaps some customized business logic, or even ways to enrich data. Everyone on your team (with edit permissions) can contribute and use these functions.

Creating Javascript Functions (UDFs) for streaming SQL with Kafka and Flink in SQLStreamBuilder

Trying it for yourself

We hope you find new powerful ways to utilize this new functionality. If you are interested in trying it out, you can sign up for a free trial of SQLStreamBuilder here and get started right away.

Get started with an interactive streaming SQL engine for Apache Kafka and streamlined Apache Flink management.

Leave a Reply

Your email address will not be published. Required fields are marked *