首页 文章

BigQuery:带有标准SQL的外部UDF

提问于
浏览
0

今天我尝试在Web编辑器UI中使用标准SQL语言编写UDF,我已经取消选中了选项'Use Legacy SQL',但它返回给我以下错误消息:未实现:您不能将旧SQL UDF与标准SQL查询一起使用 . 见https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql#differences_in_user-defined_javascript_functions

因此,我尝试了在Google Cloud 平台上提供的外部UDF示例:https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions . 但它仍然向我返回相同的错误消息 . 在下面的例子中:

CREATE TEMPORARY FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;

问题:如何在Web UI中将标准SQL与外部UDF一起使用?

1 回答

  • 1

    确保不要在"UDF Editor"面板中输入输入 . 它应该与您的查询的其余部分一起使用 . 有关示例,请参见the topic in the migration guide

    #standardSQL
    -- Computes the harmonic mean of the elements in 'arr'.
    -- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
    --   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
    CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>)
      RETURNS FLOAT64 LANGUAGE js AS """
    var sum_of_reciprocals = 0;
    for (var i = 0; i < arr.length; ++i) {
      sum_of_reciprocals += 1 / arr[i];
    }
    return arr.length / sum_of_reciprocals;
    """;
    
    WITH T AS (
      SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
      FROM UNNEST([1, 2, 3, 4, 5]) AS x
    )
    SELECT arr, HarmonicMean(arr) AS h_mean
    FROM T;
    

相关问题