Rockset

    User-Defined Functions

    JavaScript UDFs (user-defined functions) allow you to write JavaScript functions and invoke them from your SQL query. They come in handy when you want to perform certain operations on your data and the set of built-in SQL functions do not meet your needs.

    Two types of JavaScript UDFs are supported:

    1. Scalar JavaScript UDFs: these are vanilla JavaScript functions that return a single value. These are the most common ones.
    2. Tabular JavaScript UDFs: these are JavaScript functions that return iterables and can be used as SQL table functions that produce rows. These UDFs can only be used as part of the FROM clause in your SQL query.

    JavaScript UDFs are supported in your SQL queries, Query Lambdas, and SQL ingest transformation. They are not supported in Views just yet. JavaScript UDFs to define custom Aggregate or Window functions are not supported yet. JavaScript UDFs are only available on dedicated Virtual Instances.

    Syntax

    Simply wrap your JavaScript functions within the script {{{ <your JavaScript code> }}} tag and then continue constructing your SQL query as usual. The JavaScript functions that you’d like to use in SQL must be exported as if your script was a JavaScript module.

    script {{{
    <your JavaScript code>
    }}}
    
    SELECT ...

    Hello World Example

    script {{{
    export function helloworld(name) {
        return 'hello, ' + name
    }
    }}}
    
    SELECT _script.helloworld('ferro');

    The above code will return:

    hello, ferro

    Note that, while JavaScript can use both single and double quotes for strings (’hello’ and "hello" would both work), Rockset SQL only allows single quotes (’ferro’, NOT "ferro").

    Data Types

    Input Parameter Types

    When parameters are passed into JavaScript UDFs, Rockset values will be converted to JavaScript values according to their types, as follows:

    Rockset Data TypeJavascript TypeRemarks
    NULLnull
    UNDEFINEDnullNote that this is not mapped to JavaScript undefined. You can use JavaScript undefined to detect that your function was called with fewer arguments than specified.
    intnumberNote that this will result in loss of precision for values ≥ 2^53. If you want to handle large integers up to int64, see instructions regarding BigInt below.
    intBigIntWhen function property input_convert_int64_to_bigint is set to true
    floatnumber
    stringstring
    boolboolean or numberThe parameter value could be a JavaScript boolean or a JavaScript number and will evaluate to “true” or “false” when used in a boolean expression.
    objectobject
    objectMapWhen function property input_convert_object_to_map is set to true
    arrayarray or TypedArrayNote that a Rockset array consisting solely of booleans may be converted to Uint8Array.
    dateDate
    timeDate
    datetimeDate
    timestampDate

    Special Properties for Input Parameters

    You can optionally do the following data type conversions on the JavaScript UDF input parameters by setting certain properties on the function definition:

    • int64BigInt
      • Set the property input_convert_int64_to_bigint = true
    • objectMap
      • Set the property input_convert_object_to_map = true For example:
    script {{{
    
    export function echo(n) { return n }
    
    echo.input_convert_int64_to_bigint = true
    
    }}}
    
    SELECT _script.echo(9223372036854775807);

    The above function will return the correct value 9223372036854775807 since the Rockset datatype will get mapped to BigInt.

    If input_convert_int64_to_bigint was not set to true, then the SQL above will return 9223372036854776000 instead, since JavaScript numbers lose precision beyond 2^53.

    Output Return Types

    JavaScript values returned by the UDF will be converted into Rockset values according to their types, as follows:

    Javascript TypeRockset Data TypeRemarks
    nullNULL
    undefinedNULLNote that this is not Rockset UNDEFINED
    numberint or floatBased on whether value is integral or not. It will always return a Rockset float when function property output_always_convert_number_to_float is set to true.
    BigIntint or errorNote that Rockset int data type are signed 64-bit integers. If value returned in JavaScript BigInt doesn’t fit in Rockset int, an error will be thrown.
    stringstring
    booleanbool
    plain JS objectobjectThe Rockset object will contain all enumerable, non-Symbol keys from the JS object. If either of these keys or their corresponding values cannot be converted to Rockset types, an error will be thrown.
    Mapobject
    Setarray
    arrayarray
    TypedArrayarrayExcept Uint8Array, see below.
    Uint8Arrayarray of boolNote that the values in the JavaScript Uint8Array are interpreted as boolean instead of number.
    Datetimestamp

    Special Properties for Output Return Types

    You can optionally do the following data type conversions on the JavaScript UDF return values by setting certain properties on the function definition:

    • number -> float
      • Set the property output_always_convert_number_to_float = true

    For Example:

    script {{{
    
    export function echo(n) { return n }
    
    echo.output_always_convert_number_to_float = true
    
    }}}
    
    SELECT typeof(_script.echo(10));

    The above function will return float. But if output_always_convert_number_to_float was not set to true, then it will return int.

    Tabular JavaScript UDFs

    These are JavaScript functions that return iterables and can be used as SQL table functions that produce rows. These UDFs can only be used as part of the FROM clause in your SQL query. While not required, it is very convenient to use generator functions to define tabular UDFs. The iterable must yield an object of {field: value, ...} for every row.

    Hello Worlds Example

    script {{{
    
    // Note "function*" which defines a generator function
    export function* helloworlds(names) {
        for (const name of names) {
            yield {"greeting": "hello, " + name};
         }
    }
    
    }}}
    
    SELECT u.greeting
    FROM   _script.helloworlds(['ferro', 'ferret', 'rocks-d-bear']) u;
    +---------------------+
    | greeting            | 
    |---------------------+
    | hello, ferro        |
    | hello, ferret       |
    | hello, rocks-d-bear |
    +---------------------+

    More Examples

    ARRAY_SUM

    Sum all numeric values in the given input array.

    script{{{
    
    export function array_sum(arr) {
        let sum = 0;
        for (var i in arr) {
          sum += arr[i];
        }
        return sum;
    }
    
    }}}
    
    SELECT _script.array_sum([1, 2, 3, 4])
    10

    Fibonacci

    Return the nth number in the Fibonacci sequence.

    script {{{
    
    function _fib(n, a, b) {
      if (n == 0) {
        return a
      }
      return _fib(n-1, b, a+b)
    }
    
    export function fibonacci(n) {
      return _fib(n, 0, 1)
    }
    
    }}}
    
    SELECT i, _script.fibonacci(i) as fibo
    FROM   UNNEST(SEQUENCE(0, 10) as i) u;
    +-------+-------+
    | i     | fibo  |
    |-------|-------|
    |0      | 0     |
    |1	| 1     |
    |2	| 1     |
    |3	| 2     |
    |4  	| 3     |
    |5	| 5     |
    |6	| 8     |
    |7	| 13    |
    |8	| 21    |
    |9	| 34    |
    |10     | 55    |
    +-------+-------+

    Transpose

    Build a table function that takes an array of objects as input, transposes the rows and columns and then returns a row for every input column in the original input.

    script {{{
    
    export function* transpose(rows, pivot_column, new_column) {
        // transpose all rows and columns into a 2D map
        let rows_map = {}
        for (var i in rows) {
          let istr = '' + i
          for (const k in rows[i]) {
          	if (!(k in rows_map)) {
              rows_map[k] = new Object()
            }
            rows_map[k][istr] = rows[i][k]
    	}
        }
    
    	  // return a row for every column in the original rows_map
        for (const col in rows_map) {
          if (col === pivot_column) {
            // no need to produce a row for the pivot column
            continue
          }
    
          // build a new row to return
          let transposed_row = {}
          // set value for new column
          transposed_row[new_column] = col
    
          // set all other fields
          for (const istr in rows_map[col]) {
            let column_name = rows_map[pivot_column][istr]
            let column_value = rows_map[col][istr]
            transposed_row[column_name] = column_value
          }
          yield transposed_row
        }
        
    }
    
    }}}
    
    
    WITH _sample as (
      SELECT [
        {'SCENARIO': 'S1', 'RED': 100, 'BLUE': 110, 'GREEN': 95},
        {'SCENARIO': 'S2', 'RED': 550, 'BLUE': 400, 'GREEN': 350},
        {'SCENARIO': 'S3', 'RED': 211, 'BLUE': 110, 'GREEN': 295},
        {'SCENARIO': 'S4', 'RED': 950, 'BLUE': 950, 'GREEN': 950},
      ] as rows
    )
    
    SELECT 
        u.*
    FROM 
        _sample as s,
        _script.transpose(s.rows, 'SCENARIO', 'COLOR') u;
    
    /* 
    -- Use the following SQL to return the rows in original form for testing
    SELECT 
        u.*
    FROM 
        _sample as s,
        unnest(s.rows) u;
     */
    +-------+------+------+------+------+
    |COLOR	| S1   | S2   | S4   | S3   |
    |-------|------|------|------|------|
    |BLUE	| 110  | 400  | 950  | 110  |
    |GREEN	| 95   | 350  | 950  | 295  |
    |RED	| 100  | 550  | 950  | 211  |
    +-------+------+------+------+------+

    Security

    JavaScript UDFs run in an isolated and sandboxed environment for security purposes.

    • Each query executes its own script functions and cannot access anything from outside its isolated environment.
    • JavaScript UDFs do not have access to the network or the filesystem.
    • JavaScript UDFs are supported only on dedicated Virtual Instances and will not be available on shared or free Virtual Instances.
    • All code generation functionality is disabled (eval, the Function constructor, etc).
    • JavaScript UDFs do not have access to any timing facility. Date.now() returns the same value for the entire duration of the query, which is the same value that CURRENT_TIMESTAMP() would return in SQL.

    Known Limitations

    JavaScript UDFs currently have the following limitations:

    • They can’t be saved as first class entities in Rockset, and must be specified in the script section of the query every time the query is executed.
    • They cannot be shared within or across workspaces.
    • They can’t be used in View definitions.
    • You can’t use JavaScript to define custom Aggregate or Window functions.