AWS redshift->hdb pg(Greenplum)， plpython, pljava UDF 以及upload library

背景

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  
{ 'https://file_url'  
| 's3://bucketname/file_name'  
authorization  
  [ REGION [AS] 'aws_region']  
}  

阿里云hdb pg创建UDF函数

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

create extension pljava;  
  
create library example language java from 'oss://oss-cn-hangzhou.aliyuncs.com filepath=analytics.jar id=xxx key=yyy bucket=zzz';  
  
create table temp (a varchar) distributed randomly;  
insert into temp values ('my string');  
create or replace function java_substring(varchar, int, int) returns varchar as 'Test.substring' 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 'oss://oss-cn-hangzhou.aliyuncs.com filepath=analytics.jar id=xxx key=yyy bucket=zzz';  

参考

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

digoal’s 大量PostgreSQL文章入口