GreenPlum 集群中通过orafunc实现Oracle兼容函数
栏目: 数据库 · PostgreSQL · 发布时间: 6年前
内容简介：GreenPlum 集群中通过orafunc实现Oracle兼容函数
oracle数据迁移到greenplum有一个不得不面对的难题，那就是很多存储过程中调用的存储函数，一个个改起来太麻烦了，所以如果想迁移顺利，一个比较的办法是，在greenplum里面自己实现同名字同功能的函数。
1，查看是否已经安装了orafunc插件
[root@dwhm01_2_111 ~]# find / -name orafunc* /data/soft/yes/share/postgresql/contrib/orafunc.sql /data/soft/yes/lib/postgresql/orafunc.so /data/greenplum-db-4.3.12.0/share/postgresql/contrib/orafunc.sql /data/greenplum-db-4.3.12.0/lib/postgresql/orafunc.so [root@dwhm01_2_111 ~]#
OK，看到orafunc.sql已经有了，那就可以实行安装了。
2，准备测试数据
yueworld_db=# create table z4(name varchar, d date);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'name' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
yueworld_db=#
yueworld_db=#
yueworld_db=# insert into z4 values('zhangsan','2017-03-05');
INSERT 0 1
yueworld_db=# insert into z4 values('lisi','2017-03-09');
INSERT 0 1
yueworld_db=# insert into z4 values(null,'2017-03-11');
INSERT 0 1
yueworld_db=# select * from z4;
name | d
----------+------------
| 2017-03-11
lisi | 2017-03-09
zhangsan | 2017-03-05
(3 rows)
yueworld_db=#
3，安装oracle函数
安装命令：psql -dstu_db -f $GPHOME/share/postgresql/contrib/orafunc.sql
安装过程：
[gpadmin@dwhm01_2_111 ~]$ psql -dstu_db -f $GPHOME/share/postgresql/contrib/orafunc.sql CREATE SCHEMA SET BEGIN CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION psql:/usr/local/greenplum-db/share/postgresql/contrib/orafunc.sql:172: NOTICE: aggregate oracompat.listagg(text) does not exist, skipping DROP AGGREGATE CREATE AGGREGATE psql:/usr/local/greenplum-db/share/postgresql/contrib/orafunc.sql:178: NOTICE: aggregate oracompat.listagg(text,text) does not exist, skipping DROP AGGREGATE CREATE AGGREGATE CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION COMMIT [gpadmin@dwhm01_2_111 ~]$
4，查看已经安装的函数组件
[gpadmin@dwhm01_2_111 lib]$ psql -dstu_db
psql (8.2.15)
Type "help" for help.
yueworld_db=# \df oracompat.*
List of functions
Schema | Name | Result data type | Argument data types | Type
-----------+------------------+--------------------------+-------------------------------------------------+--------
oracompat | add_months | date | day date, value integer | normal
oracompat | bitand | bigint | bigint, bigint | normal
oracompat | concat | text | anyarray, anyarray | normal
oracompat | concat | text | anyarray, text | normal
oracompat | concat | text | text, anyarray | normal
oracompat | concat | text | text, text | normal
oracompat | dump | character varying | "any" | normal
oracompat | dump | character varying | "any", integer | normal
oracompat | instr | integer | str text, patt text | normal
oracompat | instr | integer | str text, patt text, start integer | normal
oracompat | instr | integer | str text, patt text, start integer, nth integer | normal
oracompat | last_day | date | value date | normal
oracompat | listagg | text | text | agg
oracompat | listagg | text | text, text | agg
oracompat | listagg1_transfn | text | text, text | normal
oracompat | listagg2_transfn | text | text, text, text | normal
oracompat | lnnvl | boolean | boolean | normal
oracompat | months_between | numeric | date1 date, date2 date | normal
oracompat | nanvl | double precision | double precision, double precision | normal
oracompat | nanvl | numeric | numeric, numeric | normal
oracompat | nanvl | real | real, real | normal
oracompat | next_day | date | value date, weekday integer | normal
oracompat | next_day | date | value date, weekday text | normal
oracompat | nlssort | bytea | text, text | normal
oracompat | nvl | anyelement | anyelement, anyelement | normal
oracompat | nvl2 | anyelement | anyelement, anyelement, anyelement | normal
oracompat | reverse | text | str text | normal
oracompat | reverse | text | str text, start integer | normal
oracompat | reverse | text | str text, start integer, _end integer | normal
oracompat | round | date | value date | normal
oracompat | round | date | value date, fmt text | normal
oracompat | round | timestamp with time zone | value timestamp with time zone | normal
oracompat | round | timestamp with time zone | value timestamp with time zone, fmt text | normal
oracompat | substr | text | str text, start integer | normal
oracompat | substr | text | str text, start integer, len integer | normal
oracompat | trunc | date | value date | normal
oracompat | trunc | date | value date, fmt text | normal
oracompat | trunc | timestamp with time zone | value timestamp with time zone | normal
oracompat | trunc | timestamp with time zone | value timestamp with time zone, fmt text | normal
(39 rows)
yueworld_db=#
5，去check函数是否可以执行：
yueworld_db=# select nvl(null,8);
ERROR: function nvl(unknown, integer) does not exist
LINE 1: select nvl(null,8);
^
HINT: No function matches the given name and argument types. You may need to add explicit type casts.
yueworld_db=#
查看当前路径地址：
yueworld_db=# show search_path; search_path ---------------- "$user",public (1 row) yueworld_db=#
Oracle的兼容函数都安装在oracompat的schema下面。为了访问这些Oracle函数，可以指定oracompat前缀或者修改数据库的搜索路径：
ALTER DATABASEstu_db SET search_path = mch,dw, public, oracompat; yueworld_db=# ALTER DATABASE stu_db SET search_path = dw, public, oracompat; ALTER DATABASE yueworld_db=#
然后重新登陆测试
[gpadmin@dwhm01_2_111 lib]$ psql -dstu_db psql (8.2.15) Type "help" for help. yueworld_db=# select nvl(null,8); nvl ----- 8 (1 row) stu_db=# stu_db=# select nvl(name,'null'),decode(name,'zhangsan',1,'lisi',2,0),d from z4; nvl | case | d ----------+------+------------ lisi | 2 | 2017-03-09 null | 0 | 2017-03-11 zhangsan | 1 | 2017-03-05 (3 rows) stu_db=#
OK，好，问题来了，我现在登录的是gpadmin账号，超级管理员，所以生效，下面切换回普通用户是否也能生效呢？
blog源地址： http://blog.csdn.net/mchdba/article/details/72475802 ，作者黄杉 mchdba，谢绝转载。
6，普通用户mch是否能使用这些函数
[gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -W
Password for user mch:
psql: FATAL: no pg_hba.conf entry for host "[local]", user "mch", database "stu_db", SSL off
[gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -W -h192.168.2.111
Password for user mch:
psql (8.2.15)
Type "help" for help.
stu_db=>
stu_db=> select instr('greenplum', 'e') from z4;
ERROR: function instr(unknown, unknown) does not exist
LINE 1: select instr('greenplum', 'e') from z4;
^
HINT: No function matches the given name and argument types. You may need to add explicit type casts.
stu_db=>
查看下当前路径，是否可以看到oracompat下面的函数信息
stu_db=> \df oracompat.*
List of functions
Schema | Name | Result data type | Argument data types | Type
-----------+------------------+--------------------------+-------------------------------------------------+--------
oracompat | add_months | date | day date, value integer | normal
oracompat | bitand | bigint | bigint, bigint | normal
oracompat | concat | text | anyarray, anyarray | normal
oracompat | concat | text | anyarray, text | normal
oracompat | concat | text | text, anyarray | normal
oracompat | concat | text | text, text | normal
oracompat | dump | character varying | "any" | normal
oracompat | dump | character varying | "any", integer | normal
oracompat | instr | integer | str text, patt text | normal
oracompat | instr | integer | str text, patt text, start integer | normal
oracompat | instr | integer | str text, patt text, start integer, nth integer | normal
oracompat | last_day | date | value date | normal
oracompat | listagg | text | text | agg
oracompat | listagg | text | text, text | agg
oracompat | listagg1_transfn | text | text, text | normal
oracompat | listagg2_transfn | text | text, text, text | normal
oracompat | lnnvl | boolean | boolean | normal
oracompat | months_between | numeric | date1 date, date2 date | normal
oracompat | nanvl | double precision | double precision, double precision | normal
oracompat | nanvl | numeric | numeric, numeric | normal
oracompat | nanvl | real | real, real | normal
oracompat | next_day | date | value date, weekday integer | normal
oracompat | next_day | date | value date, weekday text | normal
oracompat | nlssort | bytea | text, text | normal
oracompat | nvl | anyelement | anyelement, anyelement | normal
oracompat | nvl2 | anyelement | anyelement, anyelement, anyelement | normal
oracompat | reverse | text | str text | normal
oracompat | reverse | text | str text, start integer | normal
oracompat | reverse | text | str text, start integer, _end integer | normal
oracompat | round | date | value date | normal
oracompat | round | date | value date, fmt text | normal
oracompat | round | timestamp with time zone | value timestamp with time zone | normal
oracompat | round | timestamp with time zone | value timestamp with time zone, fmt text | normal
oracompat | substr | text | str text, start integer | normal
oracompat | substr | text | str text, start integer, len integer | normal
oracompat | trunc | date | value date | normal
oracompat | trunc | date | value date, fmt text | normal
oracompat | trunc | timestamp with time zone | value timestamp with time zone | normal
oracompat | trunc | timestamp with time zone | value timestamp with time zone, fmt text | normal
(39 rows)
stu_db=>
既然可以看到oracompat下面的函数信息，那为什么不能调用呢？问题分析，因为oracompat的schema是超级管理员所建，所以普通用户mch无法访问，那么需要将这个schema下面的权限授予mch用户才行，切换回gpadmin超级管理员，赋予mch对schema oracompat的权限，执行授权命令：grant all on schema oracompat to mch;
[gpadmin@dwhm01_2_111 lib]$ psql -dstu_db psql (8.2.15) Type "help" for help. stu_db=# grant all on schema oracompat to mch; GRANT stu_db=# \q
然后再切换成mch用户问题解决，执行查询函数，OK，可以调用decode、instr函数了。
[gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -W -h192.168.2.111 Password for user mch: psql (8.2.15) Type "help" for help. stu_db=> select nvl(name,'None'),decode(name,'zhangsan',1,'lisi',2,0),instr(name,'li'),d from z4; nvl | case | instr | d ----------+------+-------+------------ lisi | 2 | 1 | 2017-03-09 None | 0 | | 2017-03-11 zhangsan | 1 | 0 | 2017-03-05 (3 rows) stu_db=>
7，诡异的问题，psql突然连接不上
[gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch psql: FATAL: no pg_hba.conf entry for host "[local]", user "mch", database "stu_db", SSL off [gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -W Password for user mch: psql: FATAL: no pg_hba.conf entry for host "[local]", user "mch", database "stu_db", SSL off [gpadmin@dwhm01_2_111 gpseg-1]$
【问题解决】：需要加上-h参数
[gpadmin@dwhm01_2_111 gpseg-1]$ [gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -W -h192.168.2.111 Password for user mch: psql (8.2.15) Type "help" for help. stu_db=> \q [gpadmin@dwhm01_2_111 gpseg-1]$
601777 601258，共享汽车2个概念股，而且还捎带了雄安概念
8，卸载oracle函数
最后，如果你觉得这个不太方便，也可以卸载这些oracle函数
psql -dstu_db -f $GPHOME/share/postgresql/contrib/uninstall_orafunc.sql
9，存储函数，授权
建立存储函数
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
函数赋予给另外一个用户mch
stu_db=# grant execute on function add(integer,integer) to mch; GRANT stu_db=#
参考文章地址： http://gpdb.docs.pivotal.io/4360/utility_guide/orafce_ref.html?spm=5176.100239.blogcont8943.6.7TCKiv
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
- Elasticsearch 集群搭建和集群原理
- Zookeeper集群 + Kafka集群 + KafkaOffsetMonitor 监控
- Zookeeper学习系列【二】Zookeeper 集群章节之集群搭建
- Kafka从上手到实践-Kafka集群：启动Kafka集群
- 借 Redis cluster 集群，聊一聊集群中数据分布算法
- K8S集群入门：运行一个应用程序究竟需要多少集群？
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Hacker's Delight
Henry S. Warren Jr. / Addison-Wesley / 2002-7-27 / USD 59.99
A collection useful programming advice the author has collected over the years; small algorithms that make the programmer's task easier. * At long last, proven short-cuts to mastering difficult aspec......一起来看看 《Hacker's Delight》 这本书的介绍吧!
