时间戳

首页 > 解决方案 > Snowflake 编译器错误的解决方法

snowflake-cloud-data-platform - Snowflake 编译器错误的解决方法

问题描述

有时，Snowflake SQL 编译器会为了自己的利益而试图变得过于聪明。这是对上一个问题的跟进，其中为我给定的用例提供了一个聪明的解决方案，但该解决方案遇到了一些限制。

简要背景；我有一个 JS-UDTF，它接受 3 个浮点参数来返回表示系列的行GENERATE_SERIES(FLOAT,FLOAT,FLOAT)，以及一个 SQL-UDTF GENERATE_SERIES(INT,INT,INT)，它将参数转换为浮点数，调用 JS-UDTF，然后将结果返回为整数。我对这个包装 UDTF 的原始版本是：

CREATE OR REPLACE FUNCTION generate_series(FIRST_VALUE INTEGER, LAST_VALUE INTEGER, STEP_VALUE INTEGER)
RETURNS TABLE (GS_VALUE INTEGER)
AS
$$
SELECT GS_VALUE::INTEGER AS GS_VALUE FROM table(generate_series(FIRST_VALUE::DOUBLE,LAST_VALUE::DOUBLE,STEP_VALUE::DOUBLE))
$$;

在输入不是常量的大多数情况下，这将失败，例如：

WITH report_params AS (
  SELECT
    1::integer as first_value,
    3::integer as last_value,
    1::integer AS step_value
)      
SELECT
  *
FROM
    report_params, table(
  generate_series(
    first_value,
    last_value,
    step_value
  )
)

会返回错误：

SQL compilation error: Unsupported subquery type cannot be evaluated

提供的欺骗 SQL 编译器行为的解决方案是将函数参数封装到 VALUES 表中并交叉连接内部 UDTF：

CREATE OR REPLACE FUNCTION generate_series_int(FIRST_VALUE INTEGER, LAST_VALUE INTEGER, STEP_VALUE INTEGER)
    RETURNS TABLE (GS_VALUE INTEGER)
    AS
$$
    SELECT GS_VALUE::INTEGER AS GS_VALUE 
    FROM (VALUES (first_value, last_value, step_value)), 
         table(generate_series(first_value::double,last_value::double,step_value::double))
$$;

这对大多数调用都很有效，但是我发现 SQL 编译器再次出现这种情况。这是一个重现问题的简化示例：

WITH report_params AS (
  SELECT
    1::integer AS first_value,
    DATEDIFF('DAY','2020-01-01'::date,'2020-02-01'::date)::integer AS last_value,
    1::integer AS step_value
)      
SELECT
  *
FROM
  report_params, table(
  COMMON.FN.generate_series(
    first_value,
    last_value,
    step_value
  )
);

这会导致错误：

SQL compilation error: Invalid expression [CORRELATION(SYS_VW.LAST_VALUE_3)] in VALUES clause

该错误似乎很明显（我认为）编译器试图将函数代码嵌入到外部查询中，在运行前将函数视为宏。

此时的答案可能只是我对 Snowflake 当前功能的要求过高，但是为了学习并继续构建我认为非常有用的 UDF 库，我很好奇是否有解决方案失踪。

标签： snowflake-cloud-data-platform

解决方案

主要问题是您编写了相关子查询。

WITH report_params AS (
  SELECT * FROM VALUES
    (1, 30, 1)
    v(first_value,last_value, step_value) 
)      
SELECT
  *
FROM
  report_params, table(
  COMMON.FN.generate_series(
    first_value,
    last_value,
    step_value
  )
);

就像您在 CTE 中添加第二行一样

WITH report_params AS (
  SELECT * FROM VALUES
    (1, 30, 1),
    (2, 40, 2)
    v(first_value,last_value, step_value) 
)      
SELECT
  *
FROM
  report_params, table(
  COMMON.FN.generate_series(
    first_value,
    last_value,
    step_value
  )
);

更明显的是，这是相关的，雪花应该由谁来执行它并不那么明显。

上述数据的理想状态（如果它是有效的 SQL）

WITH report_params AS (
   SELECT *
      ,mod(v.first_value,v.step_value) as mod_offset
      FROM VALUES
        (0, 5, 20, 1),
        (1, 3, 15, 3),
        (2, 4, 15, 3),
        (3, 5, 15, 3)
        v(id, first_value,last_value, step_value) 
), report_ranges AS (
  SELECT min(first_value) as mmin,
    max(last_value) as mmax
  FROM report_params
  WHERE first_value <= last_value AND step_value > 0
), all_range AS (
  SELECT 
   row_number() over (order by seq8()) + rr.mmin - 1 as seq
  FROM report_ranges rr, 
  TABLE(GENERATOR( ROWCOUNT => (rr.mmax - rr.mmin) + 1 ))
)
SELECT 
  ar.seq
  ,rp.id, rp.first_value, rp.last_value, rp.step_value, rp.mod_offset
FROM all_range as ar
JOIN report_params as rp ON ar.seq BETWEEN rp.first_value AND rp.last_value AND mod(ar.seq, rp.step_value) = rp.mod_offset
ORDER BY 2,1;

但是如果您在存储过程（或外部）中生成它可以替换为

 WITH report_params AS (
   SELECT *
      ,mod(v.first_value,v.step_value) as mod_offset
      FROM VALUES
        (0, 5, 20, 1),
        (1, 3, 15, 3),
        (2, 4, 15, 3),
        (3, 5, 15, 3)
        v(id, first_value,last_value, step_value) 
), all_range AS (
  SELECT 
   row_number() over (order by seq8()) + 3 /*min*/ - 1 as seq
  FROM TABLE(GENERATOR( ROWCOUNT => (20/*max*/ - 3/*min*/) + 1 ))
)
SELECT 
  ar.seq
  ,rp.id
  ,rp.first_value, rp.last_value, rp.step_value, rp.mod_offset
FROM all_range as ar
JOIN report_params as rp ON ar.seq BETWEEN rp.first_value AND rp.last_value AND mod(ar.seq, rp.step_value) = rp.mod_offset
ORDER BY 2,1;

给予：

SEQ ID  FIRST_VALUE LAST_VALUE  STEP_VALUE  MOD_OFFSET
5   0   5   20  1   0
6   0   5   20  1   0
7   0   5   20  1   0
8   0   5   20  1   0
9   0   5   20  1   0
10  0   5   20  1   0
11  0   5   20  1   0
12  0   5   20  1   0
13  0   5   20  1   0
14  0   5   20  1   0
15  0   5   20  1   0
16  0   5   20  1   0
17  0   5   20  1   0
18  0   5   20  1   0
19  0   5   20  1   0
20  0   5   20  1   0
3   1   3   15  3   0
6   1   3   15  3   0
9   1   3   15  3   0
12  1   3   15  3   0
15  1   3   15  3   0
4   2   4   15  3   1
7   2   4   15  3   1
10  2   4   15  3   1
13  2   4   15  3   1
5   3   5   15  3   2
8   3   5   15  3   2
11  3   5   15  3   2
14  3   5   15  3   2

我无法猜测的问题是，感觉就像您试图在表函数 JS 函数后面隐藏一些复杂性，或者由于未说明的原因而使事情变得复杂。

[编辑对 1-9 的评论] agenerate_series和GENERATORis 前者之间的主要区别几乎是 UDF 或 CTE，在雪花中，您必须在其自己的子选择中使用 GENERATOR，否则您会得到混乱的结果。

 with s1 as (
  SELECT 
    row_number() over (order by seq8()) -1 as seq
  FROM 
  TABLE(GENERATOR( ROWCOUNT => 3 ))
), s2 as (
  SELECT 
    row_number() over (order by seq8()) -1 as seq
  FROM 
  TABLE(GENERATOR( ROWCOUNT => 3 ))
)
select s1.seq as a, s2.seq as b
from s1, s2
order by 1,2;

给出 9 行的两个数据混合，喜欢你不是你想要的。然而

with s1 as (
  SELECT 
    row_number() over (order by seq8()) -1 as seq
  FROM 
  TABLE(GENERATOR( ROWCOUNT => 3 ))
)
SELECT 
  row_number() over (order by seq8()) -1 as a
  ,s1.seq as b
FROM 
TABLE(GENERATOR( ROWCOUNT => 3 )), s1;

给出 1-9，因为在序列代码运行之前，GENERATOR（行的创建者）已经与其他数据交叉。

提供的原始解决方案的另一个版本是

WITH report_params AS (
   SELECT *
      ,trunc(div0((last_value-first_value),step_value)) as steps
      FROM VALUES
        (0, 5, 20, 1),
        (1, 3, 15, 3),
        (2, 4, 15, 3),
        (3, 5, 15, 3)
        v(id, first_value,last_value, step_value) 
), large_range AS (
  SELECT 
   row_number() over (order by seq8()) -1 as seq
  FROM 
  TABLE(GENERATOR( ROWCOUNT => 1000 ))
)
select rp.id
    ,rp.first_value + (lr.seq*rp.step_value) as val
from report_params as rp
join large_range as lr on lr.seq <= rp.steps
order by 1,2;

我更喜欢它，因为混合的性质更加清晰。但它仍然说明了雪花和其他 RDB 之间的思维方式差异。在 postgress 中进行 per-row 操作是没有成本的，因为它诞生于一个全是 per-row 操作的时代，但是 snowflake 没有 per-row 选项，并且因为它不能在每一行上做事情，所以它可以独立做很多行。这意味着每行的所有表达式都需要移到前面然后加入。因此，上面试图显示的内容。

推荐阅读