How to synchronize some configuration tables to GP library issues from Oracle database during the daily production process of the system.
First, the way you used it before:
0. Export plain text format from Oracle using 3rd party tools and store locally;
1. Loading the library with the GP external table
The advantage of this approach is that the performance is very high for a table that needs to synchronize large amounts of data, the disadvantage is a bit cumbersome, the main is real-time, there is a task scheduling cycle and export import time period.
My solution to this is to simplify the process and query in real time:
0. Querying Oracle data tables directly using the GP Web external table technology
Because it is a configuration table, the amount of data is generally very small, a few k to a few W records in this way is almost instantaneous consumption.
The first is the source table for Oracle:
[Email protected] ~]$ Sqlplus gtlions/gtlions
Sql*plus:release 10.2.0.5.0-production on Thu 8 15:41:06 2014
Copyright (c) 1982, Oracle. All rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options
Sql> select * from test;
ID NAME
---------- -----
1 A
1 A
Then query the Oracle table directly in GP:
[Email protected] ~]$ Psql gtlions
Psql (8.2.15)
Type ' help ' for help.
gtlions=# drop External table if exists sqlplus_test;
DROP EXTERNAL TABLE
gtlions=# Create external Web table sqlplus_test (ID int,name varchar (5)) Execute ' sqlplus-s gtlions/[email protected] < ; <eof
Gtlions ' # set echo OFF;
Gtlions ' # set heading OFF;
Gtlions ' # set feedback off;
Gtlions ' # set linesize 32767;
Gtlions ' # set pagesize 0;
Gtlions ' # Select Id| | "," | | name from Test;
Gtlions ' # exit;
Gtlions ' # EOF ' on master format ' CSV ' (header);
Notice:header means that each one of the data files have a HEADER row.
CREATE EXTERNAL TABLE
gtlions=# select * from Sqlplus_test;
ID | Name
----+------
1 | A
2 | B
(2 rows)
So easy,bingo!
-eof
