IT博客汇
  • 首页
  • 精华
  • 技术
  • 设计
  • 资讯
  • 扯淡
  • 权利声明
    • 登录 注册

    [原]GreenPlum 集群中通过orafunc实现Oracle兼容函数

    mchdba发表于 2017-05-18 16:57:29
    love 0

    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



沪ICP备19023445号-2号
友情链接