AWSredshifthdbpg(Greenplum)，plpython,pljavaUDF以及uploadlibrary

背景
AWS redshift UDF 支持sql, plpython编写。

阿里云hdb pg UDF支持plpgsql, sql, plpython, pljava编写。

aws redshift通过create library导入外部LIB。

阿里云hdb pg同样通过CREATE LIBRARY导入外部LIB。

redshift 创建UDF函数
https://docs.aws.amazon.com/redshift/latest/dg/udf-creating-a-scalar-udf.html

CREATE [ OR REPLACE ] FUNCTION f_function_name
( { [py_arg_name py_arg_data_type |
sql_arg_data_type } [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }
AS

$$ { python_program | SELECT_clause } $$

LANGUAGE { plpythonu | sql }

create function f_py_greater (a float, b float)
returns float
stable
as

$$ if a > b: return a return b $$

language plpythonu;
redshift 导入python lib
CREATE [ OR REPLACE ] LIBRARY library_name LANGUAGE plpythonu
FROM
{ &＃39;https://file_url&＃39;
| &＃39;s3://bucketname/file_name&＃39;
authorization
[ REGION [AS] &＃39;aws_region&＃39;]
}
阿里云hdb pg创建UDF函数
https://help.aliyun.com/document_detail/50594.html

create extension pljava;

create library example language java from &＃39;oss://oss-cn-hangzhou.aliyuncs.com filepath&＃61;analytics.jar id&＃61;xxx key&＃61;yyy bucket&＃61;zzz&＃39;;

create table temp (a varchar) distributed randomly;
insert into temp values (&＃39;my string&＃39;);
create or replace function java_substring(varchar, int, int) returns varchar as &＃39;Test.substring&＃39; language java;
select java_substring(a, 1, 5) from temp;
阿里云hdb pg导入java lib
https://help.aliyun.com/document_detail/50595.html

CREATE LIBRARY library_name LANGUAGE [JAVA] FROM oss_location OWNER ownername
CREATE LIBRARY library_name LANGUAGE [JAVA] VALUES file_content_hex OWNER ownername
DROP LIBRARY library_name
create library example language java from &＃39;oss://oss-cn-hangzhou.aliyuncs.com filepath&＃61;analytics.jar id&＃61;xxx key&＃61;yyy bucket&＃61;zzz&＃39;;
参考
https://help.aliyun.com/document_detail/50595.html

https://help.aliyun.com/document_detail/50594.html

https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_LIBRARY.html
转自阿里云德哥