Using SQL Stream BuilderPDF version

Creating Javascript User-defined Functions

With Cloudera SQL Stream Builder, you can create user functions 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. User functions are written in JavaScript. When you write them, you create a library of useful functions.
  1. Navigate to the Streaming SQL Console.  Show Me How
    1. Go to your cluster in Cloudera Manager.
    2. Select SQL Stream Builder from the list of services.
    3. Click SQLStreamBuilder Console.
      The Streaming SQL Console opens in a new window.
  2. Open a project from the Projects page of Streaming SQL Console.  Show Me How
    1. Select an already existing project from the list by clicking the Open button or Switch button.
    2. Create a new project by clicking the New Project button.
    3. Import a project by clicking the Import button.
    You are redirected to the Explorer view of the project.
  3. Click next to Functions.
  4. Click New Function.
  5. From the dropdown menu select JavaScript.
  6. Add a Name to the UDF.
    For example, name the UDF to HELLO_WORLD.
  7. Add a Description to the UDF.
  8. Select the Output and Input data type.
    For example, select STRING.
  9. Paste the JavaScript code to the editor.
    For example:
    // 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
  10. Click Create.
  11. Once created, you can use a User Defined Function in your SQL statement:
    For example:
    -- 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";