问题描述
我正在尝试计算多个值并在select子句中获取它们.它是通过UDF计算还是通过过程计算对我来说都无关紧要,但是我无法弄清楚如何以任何一种方式进行计算.我想这样使用它:
I'm trying to compute multiple values and fetch them in a select clause. Whether its computed via UDF or procedure does not matter to me but I can't figure out how to do it in either way. I want to use it like this:
SELECT ID, BITMAP(ID) FROM X;
然后应返回具有列ID, Bitset1, Bitset2
的表.如果可能的话,它应该表现得尽可能好.
which then should return a table with columns ID, Bitset1, Bitset2
. If possible it should be as performant as possible.
我目前有三个版本(简化后,实际计算量更多):
I have three versions currently (simplified, there's actual more computation):
表UDF
CREATE FUNCTION TPCH.BITMAP(IN col BIGINT)
RETURNS table("BITSET1" bigint, "BITSET2" bigint)
AS BEGIN
declare bitset1, bitset2 bigint;
bitset1 = 1;
bitset2 = 2;
return select bitset1 as "BITSET1", bitset2 as "BITSET2" from sys.dummy;
END;
标量UDF
CREATE FUNCTION BITMAP(IN col BIGINT)
RETURNS bitset1 bigint, bitset2 bigint
AS BEGIN
declare bitset1, bitset2 bigint;
bitset1 = 1;
bitset2 = 2;
END;
过程
create procedure BITMAP(in col BIGINT,
out bitsets table("BITSET1" bigint, "BITSET2" bigint))
as begin
bitsets = select 1 as "BITSET1", 2 as "BITSET2" from sys.dummy;
end;
如果我从上面执行select语句,则会得到不同的异常:
If I execute the select statement from above I get different exceptions:
对于 TUDF /过程:cannot use procedure or table function in select projection column or cannot use window function w/o OVER: P_BITMAP
对于 SUDF :This user defined function has multiple outputs, but this usage requires a single output. Specify a single output.
我发现对于 SUDF ,我可以这样写:
I figured out that for SUDF I can write it like this:
select ID, BITMAP(ID).bitset1, BITMAP(ID).bitset2 from X;
但是这两次执行BITMAP(ID),这使它变慢.我想一次性获取两个值.
But this executes BITMAP(ID) twice which makes it slow. I want to fetch both values in one go.
在哈纳还能做到吗?还是有其他选择?
Is this even possible to do in Hana or are there other options?
为清楚起见,udf和过程通常取决于输入.为了简单起见,我仅在示例中返回1和2.
EDIT 1: for clarification the udfs and procedure usually depend on the input. I just return 1 and 2 in the example for simplicity.
推荐答案
如所讨论的,在HANA 1中,存在与用户定义函数有关的以下限制:
As discussed, in HANA 1 there exist the following limitation concerning user defined functions:
- 表函数不接受联接表的值作为输入参数. IE.它们不能用于实现横向连接" 标量函数将针对语句中出现的每个实例被调用和执行.从等效调用中获取多个返回参数也将多次调用该函数.
- 表函数和过程不能在SELECT语句的投影列表中使用(非HANA专用)
- table functions don't accept values of joined tables as input parameters. I.e. they cannot be used to implement a "lateral join"
- scalar functions will be called and executed for every instance occurring in the statement. Fetching multiple return parameters from equivalent calls will also call the function multiple times.
- table functions and procedures cannot be used in the projection list of a SELECT statement (not HANA specific)
要将用户定义的函数并行应用于多个值,HANA 2提供了多种新功能,例如
To apply a user defined function to many values in parallel, HANA 2 provides multiple new features, e.g.
-
MAP_MERGE
函数,以实现map-reduce调用模式 - 确定性的用户定义函数,用于缓存结果,以便后续调用可以使用预先计算的结果
MAP_MERGE
function, to implement a map-reduce calling pattern- deterministic user-defined functions, that cache results, so that sub-sequent calls can use the precomputed result
添加:可以在此处找到HANA 2中MAP_MERGE功能的一个很好的示例:"> https://blogs.sap.com/2016/12/01/sap-hana-2.0-sps- 0-new-developer-features-database-development/
add: a good example for the MAP_MERGE feature in HANA 2 can be found here: https://blogs.sap.com/2016/12/01/sap-hana-2.0-sps-0-new-developer-features-database-development/
这篇关于从UDF或过程中选择多个参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!