Oracle与Greenplum数据交互

Oracle与Greenplum数据交互



目录

前言：... 3

一．Greenplum导入导出... 3

1.1 greenplum数据库表导出数据... 3

1.2 greenplum数据库表导入数据... 4

1.3 greenplum数据库异构迁移备份恢复... 4

二．Oracle导入导出... 5

2.1 oracle数据库表导入数据... 5

2.2 oracle数据库表导出数据... 6

附录：... 7

1.Oracle到greenplum同步通用程序... 7

2.Greenplum到oracle同步通用程序... 7

综述：... 7

前言：
数据库系统根据应用及服务场景，一般分为两种：OLTP和OLAP，前者主要面向前台应用，数据量较小，偏重于高并发及频繁的事物操作，后者主要应用于统计分析。Greenplum可归为OLAP类型，针对大数据量进行查询分析。Greenplum往往会有大数据量入库的场景，Greenplum为标准的关系型数据库，其数据入库可采用传统的INSERT方式，但效率较低。官方提供了几种简便高效的数据入库及出库方式。同时Greenplum与目前应用较为广泛的Oracle数据库系统也存在数据交互的情况，针对业务场景，我们采取用Greenplum外部表的方式进行数据入库及出库，采用sqluldr及sqlldr的方式对Oracle进行出库和入库，进而形成Greenplum与Oracle的数据高效双向交互。文档中主要针对gp与oracle通过生成csv或txt文件方式实现高效数据交互，故仅说明导入导出csv或txt文件的方法。



一．Greenplum导入导出
1.1 greenplum数据库表导出数据
通过创建”可写外部表writable external table”可以实现高效率并行导出数据，具体语法如下：

--创建可写外部表

create writable external table tab_name(字段1,字段2,….)(like 需要导出的表名称)

location(gpfdist地址/文件名) format ‘文件类型’ (delimiter,’分隔符’);

--导出数据

Insert into tab_name select * from需要导出的表名称;

该方式可以实现所有greenplum所有节点并行导出数据，并写入指定的位置的文件中。

【备注：】另外greenplum也提供了非并行简易的导出语法copy命令，命令语法如下：

copy “select * from tab_name” to ‘路径/文件名’ csv;

该方式通过master节点收集所有节点的数据串行地导出数据，适合小数据量的导出，大数据量导出效率较差。

1.2 greenplum数据库表导入数据
(1).通过创建”可读外部表readable external table”可以实现高效率加载数据

具体语法如下：

create readable external table tab_name(字段1,字段2,….)

location(gpfdist地址/文件名) format ‘文件类型’ (delimiter,’分隔符’);

通过gpload导入数据
具体语法如下：

gpload -f tab_yaml.yml -l pload.log

1.3 greenplum数据库异构迁移备份恢复
Greenplum支持两种方式的备份恢复，分别的是：传统的非并行备份恢复(pg_dump 、psql)，和并行备份恢复(gp_dump、psql)。非并行备份恢复通过生成inser语句来实现备份恢复，大数据量的情况下效率极差。

(1).关于备份

常用并行gp_dump来实现备份，通过gp_dump产生的时间戳来辨识备份集合，每个segment并行恢复各自的数据。具体实现步骤：

##备份整库，在每个节点上创建目录/gp_back

gp_dump --gp-c --gp-d=/db_back/ zypt

master 备份文件格式

gp_cdatabase_1_ ##存放create database 语句，用来重建数据库

gp_dump_status_1_ ##日志文件，存放备份日志

gp_dump_1_ ##schema中对象的定义(DDL)文件

gp_dump_1__post_data ##含有和重建表相关的对象

gp_dump_.rpt ##备份报告，包含timestamp key，gp_dump command line，backup type(全量或增量)，备份是否成功等

#指定gp_dump时，只有segment的primary和active master运行备份操作，mirror和standby master不参与备份。

segment 备份文件格式

gp_dump_0_ ##数据备份文件

gp_dump_status_0_ ##日志文件

特别注意14位的timestamp时间戳，在gp_restore恢复时必须指定，gpdb的segment节点数和备份一致，要被恢复的数据库已创建，

使用gp_dump备份时指定了参数如-s(schema only) -a(data only) --gp-c(compressed) --gp-d(alternate dump file location),那么在gp_restore恢复时也要加上。

(2)关于恢复

<>如果恢复的集群与备份的集群节点配置完全相同，可使用gp_restore恢复，命令如下：

gp_restore –gp-k=具体时间戳 --gp-c -d 数据库名。

<>如果恢复的集群与备份的集群节点配置异构，则只能实现串行的方法，将所有的节点备份文件放置在master节点的文件夹下，然后通过psql命令进行恢复，效率尚可，步骤如下：

1.装载master备份文件以恢复数据库对象

psql database_name -f /db_back/gp_dump_1_1_20170515131936

2.装载每个segment的备份文件以恢复数据

psql database_name -f /db_back/gp_dump_0_2_20170515131936

..

3.装载table相关对象的文件，恢复数据库对象如索引、触发器、主键约束等

psql database_name -f /db_back/gp_dump_1_1_20170515131936_post_data

二．Oracle导入导出
Oracle官方提供了许多比较简便灵活的数据导出导入方法，如常规的exp、 imp工具，高效的expdp、impdp工具，但其导出文件只是针对oracle本身识别，如果要导入到其他系统数据库中，则需要导出成文本文件。Oracle对于文本文件的数据导入提供了高效的sqlload工具，可以实现数据的高效导入，但是对于将数据导出成文本文件，则并未提供高效的导出工具。以往通过流写入或spool方式写入外部文件，但针对大数据量效率很差。国内DBA自行编写实现了一个高效的oracle数据导出文件工具sqluldr，可以实现10w级数据秒级别导出。

2.1 oracle数据库表导入数据
通过oracle自带的工具sqlldr实现文本数据的高效入库，sqlldr语法如下：

sqlldr user/password@ora control=D:\ct.ctl log=D:\LoadDatalog.txt

其中control为控制文件，该文件需要预先编写好，具体格式如下：

LOAD DATA

INFILE 'E:\test\data.tmp'

BADFILE 'E:\test\data.bad'

DISCARDFILE 'E:\test\data.dsc'

DISCARDMAX 1000

APPEND

INTO TABLE "TB_TEST"

FIELDS TERMINATED BY '|'

TRAILING NULLCOLS

(

USER_ID,

USER_NAME,

REG_TIME DATE(20) "YYYY-MM-DD HH24:MI:SS"

)

也可以通过直接路径加载的方式导入数据，直接路径(direct path):sqlldr不使用sql，而是直接格式化数据块，绕过整个sql引擎和undo生成，同时还可能避开redo常规路径装载使用sql insert语句和内存中的键数据缓存将数据装载到oracle数据库的表中。在sqlldr命令中加入direct=true参数，1秒钟可导入10w数据，传统路径加载一般1秒钟导入1w数据。

2.2 oracle数据库表导出数据
Oracle数据导出成文本文件，可通过spool或oracle内部包utl_file实现，但效率及通用性较差，下面说明sqluldr导出的方式：

<>首先将 sqluldr.exe复制到$ORACLE_HOME的bin目录，即可开始使用(注意linux环境为sqluldr2_linux64_10204.bin)；

<>执行导出sqluldr2 USER=user/password@ora query="select * from XTMENU" HEAD=YES table=xttab charset=ZHS16GBK file=e:\ xttab.csv log=+e:\sqluldr.log

#使用table参数时，在目录下会生成对应的ctl控制文件

#按记录数切分文件的功能取决于三个命令行选项：FILE、ROWS、BATCH，其中FILE选项指定的文件名中需要包括"%b"特征串，以表示生成的符号，ROWS指定单个文件的记录数，而BATCH则指定是否切换成多个文件

query "select .." 指定一个sql语句，通常用双引号括起来

sql 文件名 指定包括sql语句的文本文件名

long 字节数 指定导出long类型时的大长度，大32K

head yes或no 指定行是否输出列名，默认no

field 字段分隔符 指定字段分隔符，默认为逗号

record 记录分隔符 指定记录分隔符，默认为回车换行，windows下的换行

quote 引号符 指定非数字字段前后的引号符

附录：
1.Oracle到greenplum同步通用程序


2.Greenplum到oracle同步通用程序
综述：
首先，通过sqluldr导出oracle数据生成文本数据，再通过greenplum可读外部表或gpload导入数据。其次，通过greenplum可写外部表导出数据生成文本数据，再通过sqlldr方式入库oracle数据库，实现greeenplum和oracle数据库海量数据的交互流通。同时可应用以上流程编写自动化etl程序，实现自动同步数据交互，提高工作效率。

本文来源：https://blog.csdn.net/bing55555/article/details/120291486