问题描述
我经常遇到这样的问题:只要我试图在更复杂的查询中调用UDF,Snowflake查询优化似乎就会中断UDF。为了尝试在我的错误中找到共同点,我提取了一个简单的测试javascript UDTF(名为Generate_Series),它接受3个DOUBLE作为参数,并返回一个表,其中只有一列也属于DOUBLE类型。这可以正常运行:
WITH report_params AS (
SELECT
1::double as first_value,
3::double as last_value,
1::double AS step_value
)
SELECT
*
FROM
report_params,
LATERAL generate_series(
first_value,
last_value,
step_value
)
(Side Discovery)如果我在交叉联接上使用了SLATAL关键字,则不需要将UDTF封装在table()强制转换中。不确定这是押韵的还是有原因的?)
现在,如果我尝试使用子查询作为参数来调用UDTF,这似乎符合所述的snowflake subquery limitations,如下所示:
WITH report_params AS (
SELECT
1::double as first_value,
3::double as last_value,
1::double AS step_value
)
SELECT
*
FROM
table(
generate_series(
(SELECT MAX(first_value) FROM report_params),
(SELECT MAX(last_value) FROM report_params),
(SELECT MAX(step_value) FROM report_params)
)
)
我收到编译错误:
SQL compilation error: syntax error line 12 at position 49 unexpected ','. syntax error line 15 at position 6 unexpected ')'.
这是我遇到的第一个问题,尽管它应该是有效的SQL,但无论如何都会失败。我的猜测是编译器将其误解为相关的子查询。
第二个问题是我有一个很小的包装器UDTF,它重载参数并以整数形式返回值(因为JS-UDF只能接受双精度型)
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))
$$;
直接使用常量调用此重载函数,例如
SELECT * FROM table(generate_series(1::integer,3::integer,1::integer))
此包装UDTF仍工作正常。
但是,当我仅通过将参数值类型更改为INTEGER来调用包装器UDTF(例如
)来修改前面的工作查询(第一个变体)时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
此错误在直接位于上面的表单中相同,也与在第一个工作表单中所示的删除表型并使用LATEAL关键字相同。
我在过去的线程中读到,人们删除了SQL UDTF的SELECT.FROM部分,成功与否参差不齐,但我尝试的迭代似乎都不起作用。
似乎微风可能会使Snowflake SQL编译器处于倾斜状态,不确定是否存在调试模式,在该模式下我可以观察它实际尝试运行的查询的"解构"形式,并查看问题所在。
edit:这是以下示例中引用的底层Javascript UDTF:
CREATE OR REPLACE FUNCTION generate_series(FIRST_VALUE DOUBLE, LAST_VALUE DOUBLE, STEP_VALUE DOUBLE)
RETURNS TABLE (GS_VALUE DOUBLE)
LANGUAGE JAVASCRIPT
AS '{
processRow: function get_params(row, rowWriter, context){
if(row.STEP_VALUE === 0) {
throw new Error("Step cannot be 0");
}
else if(row.STEP_VALUE > 0 && row.FIRST_VALUE > row.LAST_VALUE) {
throw new Error("Positive series is open-ended.");
}
else if(row.STEP_VALUE < 0 && row.FIRST_VALUE < row.LAST_VALUE) {
throw new Error("Negative series is open-ended.");
}
else {
let i = row.FIRST_VALUE;
while(row.STEP_VALUE > 0 ? i <= row.LAST_VALUE : i >= row.LAST_VALUE) {
rowWriter.writeRow({GS_VALUE: i});
i+=row.STEP_VALUE;
}
}}
}';
推荐答案
对于第一个查询,不能将子查询用作UDTF的参数。但是,您可以使用其他CTE。大致如下:
WITH report_params AS (
SELECT
1::double as first_value,
3::double as last_value,
1::double AS step_value
), report_max AS (
SELECT
MAX(first_value) as first_value_max,
MAX(last_value) as last_value_max,
MAX(step_value) as step_value_max
FROM report_params
)
SELECT *
FROM report_max,
table(generate_series(first_value_max,last_value_max,step_value_max)
);
因此,看一下第二个问题,表函数的语法似乎存在问题。如果您将第二个函数修改为如下所示:
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))
$$;
这将与传入的值进行交叉联接,并允许您将变量强制转换为DOUBLE,然后将其转换为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_int(first_value,last_value,step_value));
这篇关于UDTF不能在微不足道的调用之外编译或执行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!