JavaScript UDFs 简介¶

示例¶

下面示例中的代码创建了一个名为 my_array_reverse 的 UDF，其处理程序代码接受输入 ARRAY，并按相反顺序返回包含元素的 ARRAY。根据 SQL-JavaScript 数据类型映射 中描述的映射，JavaScript 实参和返回类型通过 Snowflake 与 SQL 相互转换。

请注意，即使在 SQL 代码中输入的参数名称不是大写字母，JavaScript 代码也必须将这些名称全部大写。

-- Create the UDF.
CREATE OR REPLACE FUNCTION my_array_reverse(a ARRAY)
  RETURNS ARRAY
  LANGUAGE JAVASCRIPT
AS
$$
  return A.reverse();
$$
;

整数和双精度¶

JavaScript 没有整型类型；所有数字都表示为双精度。除非通过类型转换，否则 JavaScript UDFs 不接受或返回整数值（即，您可以将向接受双精度的 JavaScript UDF 传递整数）。

Snowflake SQL 和 JavaScript 都支持双精度值。这些值按原样传输。

字符串¶

Snowflake SQL 和 JavaScript 都支持字符串值。这些值按原样传输。

二进制值¶

所有二进制值都转换为 JavaScript Uint8Array 对象。这些类型化数组的访问方式与常规 JavaScript 数组相同，但效率更高，并且支持更多方法。

如果 JavaScript UDF 返回 Uint8Array 对象，则将其转换为 Snowflake SQL 二进制值。

日期¶

所有时间戳和日期类型都将转换为 JavaScript Date() 对象。JavaScript 日期类型等效于 Snowflake SQL 中的 TIMESTAMP_LTZ(3)。

对于接受日期或时间的 JavaScript UDFs，请参考以下说明：

• 精度超过毫秒的所有数据都会丢失。

• 从 SQL TIMESTAMP_NTZ 生成的 JavaScript Date 不再作为“挂钟”时间；它会受到夏令时的影响。这与将 TIMESTAMP_NTZ 转换为 TIMESTAMP_LTZ 时的行为类似。

• 从 SQL TIMESTAMP_TZ 生成的 JavaScript Date 会丢失时区信息，但表示的时间与输入时间相同（类似于将 TIMESTAMP_TZ 转换为 TIMESTAMP_LTZ）。

• SQL DATE 会转换为 JavaScript Date，代表当地时区当前日期的午夜。

此外，对于返回 DATE 和 TIMESTAMP 类型的 JavaScript UDFs，请考虑以下注意事项：

• JavaScript Date 对象会转换为 UDF 的结果数据类型，其转换语义与从 TIMESTAMP_LTZ(3) 到返回数据类型的转换语义相同。

• 嵌套在 VARIANT 对象中的 JavaScript Date 对象始终是 TIMESTAMP_LTZ(3) 类型。

变体、对象和数组¶

JavaScript UDFs 允许轻松、直观地操作变体和 JSON 数据。传递给 UDF 的变体对象会转换为原生 JavaScript 类型和值。之前列出的任何值都会转换为相应的 JavaScript 类型。变体对象和数组会转换为 JavaScript 对象和数组。同样，UDF 返回的所有值都将转换为适当的变体值。请注意，UDF 返回的对象和数组受大小和深度的限制。

-- 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 '
  // Define a function flatten(), which 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;
  }

  // Now call the function flatten() that we defined earlier.
  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        |
-----------+

NULL 和未定义的值¶

使用 JavaScript UDFs 时，请密切注意可能包含 NULL 值的行和变量。具体而言，Snowflake 包含两个不同的 NULL 值（SQL NULL 和变体的 JSON null），而 JavaScript 除 null 外还包含 undefined 值。

JavaScript UDF 的 SQL NULL 实参将转换为 JavaScript undefined 值。同样，返回的 JavaScript undefined 值也会转换回 SQL NULL。这适用于所有数据类型，包括变体。对于非变体类型，返回的 JavaScript null 值也会转换为 SQL NULL 值。

变体类型的实参和返回值区分 JavaScript 的 undefined 和 null 值。SQL NULL 继续转换为 JavaScript undefined`（JavaScript :code:`undefined 返回 SQL NULL）；变体 JSON null 转换为 JavaScript null`（JavaScript :code:`null 返回变体 JSON null）。嵌入到 JavaScript 对象（作为值）或数组中的 undefined 值将导致元素被省略。

创建一个包含一个字符串和一个 NULL 值的表：

CopyExpand

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

Show lessSee more

Scroll to top

创建一个函数，将字符串转换为 NULL，将 NULL 转换为字符串：

CopyExpand

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

Show lessSee more

Scroll to top

调用函数：

CopyExpand

select string_reverse_nulls(s) 
    from strings
    order by 1;
+-------------------------+
| STRING_REVERSE_NULLS(S) |
|-------------------------|
| string was null         |
| NULL                    |
+-------------------------+

Show lessSee more

Scroll to top

创建一个函数，显示传递 SQL NULL 和传递变体 JSON null 之间的区别：

CopyExpand

CREATE OR REPLACE FUNCTION variant_nulls(V VARIANT)
      RETURNS VARCHAR
      LANGUAGE JAVASCRIPT
      AS '
      if (V === undefined) {
        return "input was SQL null";
      } else if (V === null) {
        return "input was variant null";
      } else {
        return V;
      }
      ';

Show lessSee more

Scroll to top

CopyExpand

select null, 
       variant_nulls(cast(null as variant)),
       variant_nulls(PARSE_JSON('null'))
       ;
+------+--------------------------------------+-----------------------------------+
| NULL | VARIANT_NULLS(CAST(NULL AS VARIANT)) | VARIANT_NULLS(PARSE_JSON('NULL')) |
|------+--------------------------------------+-----------------------------------|
| NULL | input was SQL null                   | input was variant null            |
+------+--------------------------------------+-----------------------------------+

Show lessSee more

Scroll to top

创建一个函数，显示返回 undefined、null 与包含 undefined 和 null 的变体之间的区别（注意，undefined 值会从返回的变体中移除）：

CopyExpand

CREATE OR REPLACE FUNCTION variant_nulls(V VARIANT)
      RETURNS variant
      LANGUAGE JAVASCRIPT
      AS $$
      if (V == 'return undefined') {
        return undefined;
      } else if (V == 'return null') {
        return null;
      } else if (V == 3) {
        return {
            key1 : undefined,
            key2 : null
            };
      } else {
        return V;
      }
      $$;

Show lessSee more

Scroll to top

CopyExpand

select variant_nulls('return undefined'::VARIANT) AS "RETURNED UNDEFINED",
       variant_nulls('return null'::VARIANT) AS "RETURNED NULL",
       variant_nulls(3) AS "RETURNED VARIANT WITH UNDEFINED AND NULL; NOTE THAT UNDEFINED WAS REMOVED";
+--------------------+---------------+---------------------------------------------------------------------------+
| RETURNED UNDEFINED | RETURNED NULL | RETURNED VARIANT WITH UNDEFINED AND NULL; NOTE THAT UNDEFINED WAS REMOVED |
|--------------------+---------------+---------------------------------------------------------------------------|
| NULL               | null          | {                                                                         |
|                    |               |   "key2": null                                                            |
|                    |               | }                                                                         |
+--------------------+---------------+---------------------------------------------------------------------------+

Show lessSee more

Scroll to top

JavaScript 中的类型转换¶

JavaScript 会在许多不同类型之间隐式转换值。返回任何值时，该值首先转换为请求的返回类型，然后再转换为 SQL 值。例如，如果返回一个数字，但 UDF 声明返回的是一个字符串，则此数字将在 JavaScript 中转换为字符串。请注意，JavaScript 编程错误（如返回错误的类型）可能会被这种行为所掩盖。此外，如果在转换值的类型时出现错误，也会导致出错。

JavaScript 数字范围¶

精度完好无损的数字的范围是

-(2^53 -1)

到

(2^53 -1)

Snowflake NUMBER(p, s)和 DOUBLE 数据类型中的有效值范围更大。从 Snowflake 检索值并将其存储到 JavaScript 数值变量中可能会导致精度损失。例如：

CopyExpand

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;

Show lessSee more

Scroll to top

CopyExpand

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

Show lessSee more

Scroll to top

前两列应匹配，第三列应包含 0.0。

该问题适用于 JavaScript 用户定义的函数 (UDFs) 和存储过程。

如果您在存储过程中使用 getColumnValue() 时遇到问题，则可以通过以字符串形式检索值来避免该问题，例如：

getColumnValueAsString()

然后，您可以返回存储过程中的字符串，并在 SQL 中将该字符串转换为数字数据类型。