Oracle与Greenplum数据交互

2023-03-09 00:00:00 数据 备份 导入 导出 恢复

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

相关文章