JavaScript UDFs

This topic covers concepts that are specific to JavaScript UDFs.

In this Topic:

Note

All the CREATE FUNCTION examples in this topic illustrate the syntax for creating UDFs using SnowSQL or the Worksheet (in the Snowflake web interface). If these examples are executed in sfsql, you must include a forward slash character (/) on a new line after the CREATE FUNCTION command to terminate the command.

In SnowSQL and the Worksheet, a forward slash is not required and will return an error if specified.

JavaScript Data Types

Snowflake SQL and JavaScript functions provide similar, but different data types. Objects within Snowflake and JavaScript are transferred using the following mappings.

Integers

JavaScript has no integer type; all numbers are represented as doubles. JavaScript UDFs do not accept or return integer values except through type conversion (i.e. you can pass an integer to a JavaScript UDF that accepts a double).

Doubles

Both Snowflake and JavaScript support double values. These values are transferred as-is.

Strings

Both Snowflake and JavaScript support string values. These values are transferred as-is.

Binary Values

All binary values are converted into JavaScript Uint8Array objects. These typed arrays can be accessed in the same way as regular JavaScript arrays, but they are more efficient and support additional methods.

If a JavaScript function returns a Uint8Array object, it is converted into a Snowflake binary value.

Dates

All timestamp and date types are converted into JavaScript Date() objects. The JavaScript Date type is equivalent to TIMESTAMP_LTZ(3). Consider the following for JavaScript UDFs that accept a date or time:

  • All precision beyond milliseconds is lost.
  • JavaScript Date generated from SQL TIMESTAMP_NTZ no longer act as “wallclock” time; they are influenced by daylight saving. This is similar to behavior when converting TIMESTAMP_NTZ to TIMESTAMP_LTZ.
  • JavaScript Date generated from SQL TIMESTAMP_TZ lose time zone information, but represent the same moment in time as the input (similar to when converting TIMESTAMP_TZ to TIMESTAMP_LTZ).
  • SQL DATE is converted to JavaScript Date representing midnight of the current day in the local time zone.

Additionally, consider the following for JavaScript functions that return DATE and TIMESTAMP types:

  • JavaScript Date objects are converted to the function’s result data type, adhering to the same conversion semantics as casts from SQL TIMESTAMP_LTZ(3) to the return data type.
  • JavaScript Date objects nested inside VARIANT objects are always of type TIMESTAMP_LTZ(3).

Variant, Objects, and Arrays

JavaScript UDFs allow easy, intuitive manipulation of variant and JSON data. Variant objects passed to a UDF are transformed to native JavaScript types and values. Any of the previously-listed values are translated into their corresponding JavaScript types. Variant objects and arrays are converted to JavaScript objects and arrays. Similarly, all values returned by the UDF are transformed into the appropriate variant values. Note that objects and arrays returned by the UDF are subject to size and depth limitations.

-- flatten all arrays and values of objects into a single array
-- order of objects may be lost
CREATE OR REPLACE FUNCTION flatten_complete(v variant)
  RETURNS variant
  LANGUAGE JAVASCRIPT
  AS '
  // always returns an array
  function flatten(input) {
    var returnArray = [];
    if (Array.isArray(input)) {
      var arrayLength = input.length;
      for (var i = 0; i < arrayLength; i++) {
        returnArray.push.apply(returnArray, flatten(input[i]));
      }
    } else if (typeof input === "object") {
      for (var key in input) {
        if (input.hasOwnProperty(key)) {
          returnArray.push.apply(returnArray, flatten(input[key]));
        }
      }
    } else {
      returnArray.push(input);
    }
    return returnArray;
  }

  return flatten(V);
  ';

select value from table(flatten(flatten_complete(parse_json(
'[
  {"key1" : [1, 2], "key2" : ["string1", "string2"]},
  {"key3" : [{"inner key 1" : 10, "inner key 2" : 11}, 12]}
  ]'))));

-----------+
   VALUE   |
-----------+
 1         |
 2         |
 "string1" |
 "string2" |
 10        |
 11        |
 12        |
-----------+

JavaScript Arguments and Returned Values

Arguments may be referenced directly by name within JavaScript. Note that an unquoted identifier must be referenced with the capitalized variable name. As arguments and the function are referenced from within JavaScript, they must be legal JavaScript identifiers. Specifically, function and argument names must begin with a letter or $, while subsequent characters may be alphanumeric, $, or _. Additionally, names may not be JavaScript-reserved words.

The following three examples illustrate functions that use arguments referenced by name:

-- Valid function.  'N' must be capitalized

CREATE OR REPLACE FUNCTION add5(n double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  AS 'return N + 5;';

select add5(0.0);

-- Valid function.  Lowercase argument is double-quoted

CREATE OR REPLACE FUNCTION add5_quoted("n" double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  AS 'return n + 5;';

select add5_quoted(0.0);

-- Invalid function. Error returned at runtime because JavaScript identifier 'n' cannot be resolved

CREATE OR REPLACE FUNCTION add5_lowercase(n double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  AS 'return n + 5;';

select add5_lowercase(0.0);

Null and Undefined Values

When using JavaScript UDFs, you should pay close attention to rows and variables that may contain null values. Specifically, Snowflake contains two distinct null values (SQL null and variant’s JSON null), while JavaScript contains the undefined value in addition to null.

SQL null arguments to a JavaScript UDF will translate to the JavaScript undefined value. Likewise, returned JavaScript undefined values translate back to SQL null. This is true for all data types, including variant. For non-variant types, a returned JavaScript null will also result in a SQL null value.

Arguments and returned values of the variant type distinguish between JavaScript’s undefined and null values. SQL null continues to translate to JavaScript undefined (and JavaScript undefined back to SQL null); variant JSON null translates to JavaScript null (and JavaScript null back to variant JSON null). An undefined value embedded in a JavaScript object (as the value) or array will cause the element to be omitted.

CREATE OR REPLACE FUNCTION string_reverse_nulls(s string)
  RETURNS string
  LANGUAGE JAVASCRIPT
  AS '
  if (S === undefined) {
    return "string was null";
  } else
  {
    return undefined;
  }
  ';
/

create or replace table strings (s string);
insert into strings values (null), ('non-null string');
select string_reverse_nulls(s) from strings;

-------------------------+
 STRING_REVERSE_NULLS(S) |
-------------------------+
 string was null         |
 [NULL]                  |
-------------------------+
CREATE OR REPLACE FUNCTION variant_nulls(v variant)
  RETURNS variant
  LANGUAGE JAVASCRIPT
  AS '
  if (V === undefined) {
    return "input SQL null";
  } else if (V === null) {
    return "input variant null";
  } else if (V == 1) {
    return null;
  } else if (V == 2) {
    return undefined;
  } else if (V == 3) {
    return {
      key1 : undefined,
      key2 : null
    };
  } else {
    return V;
  }
  ';
/

select null, variant_nulls(cast(null as variant)), variant_nulls(1), variant_nulls(2), variant_nulls(3);

--------+--------------------------------------+------------------+------------------+------------------+
  NULL  | VARIANT_NULLS(CAST(NULL AS VARIANT)) | VARIANT_NULLS(1) | VARIANT_NULLS(2) | VARIANT_NULLS(3) |
--------+--------------------------------------+------------------+------------------+------------------+
 [NULL] | "input SQL null"                     | null             | [NULL]           | {                |
        |                                      |                  |                  |   "key2": null   |
        |                                      |                  |                  | }                |
--------+--------------------------------------+------------------+------------------+------------------+

Type Conversion within JavaScript

JavaScript will implicitly convert values between many different types. When any value is returned, the value is first converted to the requested return type before being translated to a SQL value. For example, if a number is returned, but the function is declared as returning a string, this number will converted to a string within JavaScript. Keep in mind that JavaScript programming errors, such as returning the wrong type, may be hidden by this behavior. In addition, if an error is thrown while converting the value’s type, an error will result.

JavaScript Errors

Any errors encountered while executing JavaScript appear to the user as SQL errors. This includes parsing errors, runtime errors, and uncaught error thrown within the UDF. If the error contains a stacktrace, it will be printed along with the error message. It is acceptable to throw an error without catching it in order to end the query and produce a SQL error.

While debugging you may find it useful to print argument values along with the error message so that they appear in the SQL error message text. For deterministic functions, this provides the necessary data to reproduce errors in a local JavaScript engine. One common pattern is to place an entire JavaScript UDF body in a try-catch block, append argument values to the caught error’s message, and throw an error with the extended message. You should consider removing such mechanisms prior to deploying UDFs to a production environment; recording values in error messages may unintentionally reveal sensitive data.

JavaScript UDF Limitations

We place several limitations on JavaScript user-defined functions. These constraints ensure stability within Snowflake’s environment.

Memory

JavaScript UDFs will fail if they consume too much memory. The specific limit is subject to change. Using too much memory will result in an error being returned.

Time

JavaScript functions that take too long will be killed and an error returned to the user. JavaScript UDFs that enter endless loops will result in errors.

Stack Depth

Excessive stack depth due to recursion will result in an error.

Global State

You should not rely on global data being available between function calls from one row to the next. The global state may be erased between UDF calls to distinct rows. It is safe to use global state within a UDF.

Libraries

Snowflake JavaScript UDFs give access to the standard JavaScript library. Note that this excludes many objects and methods typically provided by browsers. There is no mechanism to import, include, or call additional libraries. All required code should be embedded within the UDF.

Additionally, the built-in JavaScript eval() function is disabled.

Returned Variant Size and Depth

Returned variant objects are subject to size and nesting-depth limitations. Should any object be too large (several megabytes; subject to change) or too deep (currently allow a nesting depth of 1000 in objects and arrays; subject to change) you will receive an error.

JavaScript UDF Security

Snowflake’s JavaScript UDFs are designed to be safe and secure by providing several layers of query and data isolation. Servers within virtual warehouses are accessible only from the warehouse’s account (warehouses are single-tenant). Table data is encrypted within the virtual warehouse to prevent unauthorized access. Snowflake JavaScript UDFs are executed within a restricted engine, preventing system calls from the JavaScript context (e.g., no network and disk access) and constraining the system resources available to the engine, specifically memory. As a result, Snowflake JavaScript UDFs may access only the data needed for the query and may not affect the state of the underlying system other than consuming a reasonable amount of memory and processor time.