Oracle与Greenplum数据交互
目录
前言：
数据库系统根据应用及服务场景，一般分为两种：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程序，实现自动同步数据交互，提高工作效率。
2085
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
