OushuDB实现DBLINK到ORACLE的使用范围

2021-11-04 11:04:20

通过实现创建OushuDB到ORACLE的DBLINK,为实时数仓做有效的补充

背景
传统ORACLE的实现
新型数仓、数据湖通常的实现
实现DBLINK完成有效的补充
- 一般实现
- 可用方式
DBLINK实用程序链接

背景

随着数据量不断的膨胀，分析需求的强化，以及国产化进程，原有的传统数据库已经不能支撑以及完成繁重的任务。

越来越多的数仓以及数据湖感念的平台，迁移至MPP、大数据，云原生平台。

不过在这个过程中，原有的核心系统暂时还保留，或者选用更适合事务处理的数据库。

在这个体系中不可避免的出现异库的数据交互，我们以云原生数据库(OushuDB)与ORACLE为例探讨方案的利弊，并且实现一种DBLINK完成部分需求。

传统ORACLE的实现

ORACLE数据库因为其稳定的性能，庞大的玩家基础，完善的培训机制。在国内系统中随处可见，也非常有代表性。

早期很多数据参库是直接建在ORACLE之上的，只是与核心库分为不同的数据库，核心库到数仓ODS层一般通过OGG、ADG完成数据同步，有些系统直接通过DBLINK或者加上物化视图完成数据交互。

ADG与OGG优点：

ADG与OGG都是通过redo log完成同步，对核心系统影响少

ADG与OGG缺点：

1、只能ORACLE之间使用，异库不适用

2、部署麻烦，运维成本高

DBLINK优点：

1、使用简单方便，无需额外部署组件

2、可以通过透明网关与异库交互

DBLINK缺点：

1、DBLINK受优化器，网络等影响不能达到本地库性能

2、DBLINK使用需要考虑生产核心库的负载，不能滥用，导致核心库故障

新型数仓、数据湖通常的实现

随着实时、准实时数仓的需求越来越多，需要打造流批一体的架构。以OushuDB的流批一体的架构为例

OushuDB新一代数据云平台，依托丰富的生态体系，打造流批一体架构，完成各数据源到OushuDB的各种响应级别的数据同步，这也是推荐的数据交互方式。

不过一些特殊的需求可能需要DBLINK实现，当然这种DBLINK是有条件的使用，不可滥用。

实现DBLINK完成有效的补充

OushuDB拥有丰富的接口，可以方便的二次开发。

一般实现

对于DBLINK通常的做法，可以通过web external table完成外部程序的调用，到达DBLINK的效果。以一个最简单的shell例子说明这个过程：

1、首先要在MASTER上安装ORACLE客户端

2、修改参数gp_external_enable_exec = on

3、定义一个访问ORACLE数据的SHELL脚本 vi lineitem.sh

#!/bin/bash
sqlplus -S sh/sh <<EOF
set arraysize 5000;
set linesize 32767;
set pagesize 0;
set heading off;
set feedback off;

alter session set nls_date_format='YYYY-MM-DD';

select '"'||L_ORDERKEY
||'","'||L_PARTKEY
||'","'||L_SUPPKEY
||'","'||L_LINENUMBER
||'","'||L_QUANTITY
||'","'||L_EXTENDEDPRICE
||'","'||L_DISCOUNT
||'","'||L_TAX
||'","'||L_RETURNFLAG
||'","'||L_LINESTATUS
||'","'||L_SHIPDATE
||'","'||L_COMMITDATE
||'","'||L_RECEIPTDATE
||'","'||L_SHIPINSTRUCT
||'","'||L_SHIPMODE
||'","'||L_COMMENT||'"' as text from LINEITEM_DATA where
rownum<10;

set feedback on;
set heading on;

quit;
EOF
exit

chmod 777 lineitem.sh

4 定义web external table调用lineitem.sh

CREATE EXTERNAL WEB TABLE ora_LINEITEM 
(
l_orderkey      bigint                
,l_partkey      bigint                
,l_suppkey      bigint                
,l_linenumber   bigint                
,l_quantity     numeric(15,2)         
,l_extendedprice numeric(15,2)         
,l_discount     numeric(15,2)         
,l_tax          numeric(15,2)         
,l_returnflag   character(1)          
,l_linestatus   character(1)          
,l_shipdate     date                  
,l_commitdate   date                  
,l_receiptdate  date                  
,l_shipinstruct character(25)         
,l_shipmode     character(10)         
,l_comment      character varying(44)
) 
EXECUTE '/home/oracle/lineitem.sh' 
ON MASTER FORMAT 'CSV' ;

5、直接查询

Select * from ora_LINEITEM;

上面的方法可以改成jdbc或者oci调用，不过这种方式其实实用价值不高，因为这种方式每次执行都会全量将数据读取到master然后在做处理，就算加where条件也是全量读取的master后的行为，这相当于select * from tab的效果，那么小表没有关系，大表呢？经常会出现OOM情况，实际只能读取维表、码表，但是这种需求一般都是要对流水表的实时数据获取，码表这种配置表，定期同步就行了，所以适用范围很小。

可用方式

真实可用的方式还是要通过UDF实现，代价就是友好度下降了。

OushuDB可以多语言UDF，比如c、java、python

下面以pljava的例子完成这个过程

1、安装pljava

通过pg_language视图可以检查现有语言支持，初始化没有java

执行/usr/local/hawq/share/postgresql/pljava/install.sql完成创建

这时候就可以建pljava函数了

不过首先我们得完成java程序

为了实现返回记录，要用到ResultSetHandle接口，这个接口在pljava.jar中

样例代码：

package cn.oushu.dblink;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.postgresql.pljava.ResultSetHandle;

public class getDataBase implements ResultSetHandle {
     private final String m_filter;
     private final String m_tablename;
     private Statement m_statement;
     private Conn c = new Conn();
     private Connection conn = null;
     public getDataBase(String tablename, String filter){
         m_tablename= tablename;
         m_filter= filter;
     }
     public ResultSet getResultSet() throws SQLException
     {
         conn = c.sourceConnection();
         m_statement= conn.createStatement();

         if(m_filter.length() > 0) {
         	return m_statement.executeQuery("SELECT * FROM " + m_tablename + "  WHERE " + m_filter);
         } else{
         	return m_statement.executeQuery("SELECT * FROM " + m_tablename);
         }
     }

     public void close() throws SQLException {
     	m_statement.close();
     }
     public static ResultSetHandle listDataBase(String tablename, String filter) {
     return new getDataBase(tablename, filter);
     }
}

完成代码编写后打成jar包，加上其他依赖jar

放在/usr/local/hawq/lib/postgresql/java/下

修改pljava_classpath参数把jar包都加进来

这些准备好就可以建数据库函数

drop function if exists
public.lineitem_data_ora_poc(text,text);

create or replace function public.lineitem_data_ora_poc (funcname_in text,filter_in text)
RETURNS table(
L_ORDERKEY numeric(38,0)
,L_PARTKEY numeric(38,0)
,L_SUPPKEY numeric(38,0)
,L_LINENUMBER numeric(38,0)
,L_QUANTITY numeric(15,2)
,L_EXTENDEDPRICE numeric(15,2)
,L_DISCOUNT numeric(15,2)
,L_TAX numeric(15,2)
,L_RETURNFLAG char(1)
,L_LINESTATUS char(1)
,L_SHIPDATE timestamp
,L_COMMITDATE timestamp
,L_RECEIPTDATE timestamp
,L_SHIPINSTRUCT char(25)
,L_SHIPMODE char(10)
,L_COMMENT varchar(44)
)
 AS
'cn.oushu.dblink.getDataBase.listDataBase'
 IMMUTABLE
LANGUAGE javau;

执行函数

Join操作

以上的方式能将条件下推给oracle这样获取到OushuDB的数据就是条件刷选后的数据，数据控制在一定范围就不会OOM

使用限制

这种DBLINK的方式还是有限制条件的，不能滥用

1、查询的结果集一定不能太大，否则不但有OOM的可能，在join时因为没有数据库统计信息也会影响执行计划

2、查询一定要带条件，条件最好是oracle的索引，或者分区，这样也能对oracle核心库有所保护

上面条件不符合建议将这个需求加入到流批架构中

这个方案可以改oci完成，当然性能会更好，之所以用java主要是方便，不用装客户端，java方便（懒）

DBLINK实用程序链接

链接：https://pan.baidu.com/s/16UucBYqlMLvzwq2Cscz_fw
提取码：9cpt

码农公寓