Creating User Defined Functions

With 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. Select Console from the left-hand menu.
  2. Select Functions > Add.
  3. Name the function HELLO_WORLD, give it a short description, select JavaScript as the language.
  4. Select STRING as output type and add STRING to the input type by selecting it from the list and clicking the plus button.
  5. 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
    
  6. Click Save.
  7. Once created, you can use a User Function in your SQL statement:
    -- 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";