使用外部表關聯MySQL資料到Oracle

leixue0906發表於2015-10-31
因為業務需要,有個臨時的活動需要DBA來支援一些資料業務,問題來了,需要從MySQL端同步一部分資料到Oracle端,然後從Oracle端匹配查到相應的資料返回給MySQL,至於原因,也是不同的業務系統,不同的許可權分配,還沒法做到一個應用端去讀取這些資訊,而且也有安全的考慮,大體就是兩部分的資料也是互相補充,但又彼此獨立,是一個全集和子集的關係。
這個流程本來從開發的角度來看似乎是一頭霧水,所以交給他們來規劃就容易出現問題,最後溝通後的流程是下面的形式。
下面這個圖左邊是Oracle的環境,右邊是MySQL的環境,兩個環境的表中都存在一個共同的欄位就是序列號serial_no,而且MySQL段的序列號是Oracle端的子集,兩者是存在一一對映關係的。

現在的問題是MySQL端可以提供uid,但是無法得知cn_number,因為這部分資訊在Oracle端。Oracle端又沒有uid的概念,所以需要MySQL端提供serial_no來對映才可以。
所以一來二去,得到的流程就是需要5個步驟。
首先開發部門提供需要的uid(1),然後MySQL端抽取後把檔案同步到Oracle端(2),然後在Oracle端進行關聯查詢,得到一個uid和cn_number的組合(3),然後同步到MySQL端,
最後MySQL端得到這部分資料,最終開發的需求就完成了。

很快就得到了MySQL端同步過來的資料,是個本文檔案,內容如下:
687914 | 0d6c3956-d53d-4e14-9fba-cb73cec661e6
694786 | 41159bb3-970b-4b6b-9c5d-46e1f3d388be
746010 | 011d632d-149a-4e3d-ad00-dcae53f60825
1226533| 78dd80d3-6ad0-4bd5-aa1d-843c32b7ddab
1399846| 3dcc5982-bcc7-4cbf-9f99-b5a51b932b1d
1400221| 4fc505eb-20a6-451c-8674-5667e33167e7
因為推送過來的表的資料可能會有變化的,但是每次都去更新表的資料還是有些繁瑣,一個方便的辦法就是外部表了。
CREATE TABLE passport_ext
      (uid    number,
       serial_no  varchar2(100))
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_dir
      ACCESS PARAMETERS
        (
        FIELDS TERMINATED BY '|'        
        )
      LOCATION ('mysql_uid_serial_no.lst')
     );
所以一旦MySQL端推送檔案過來了,我只需要替換文字檔案即可,然後就不用反覆做資料的匯入了。
直接拿過來關聯即可。
但是建立外部表的時候老師拋錯,最後發現uid是保留字,用下面的例子來驗證。
>  CREATE TABLE passport_ext (uid varchar2(10));
 CREATE TABLE passport_ext (uid varchar2(10))
                                          *
ERROR at line 1:
ORA-00904: : invalid identifier
>  CREATE TABLE passport_ext (uidd varchar2(10));
Table created.
其實後面經過老貓指點,還是可以用"uid"來代替的,這個用法就跟MySQL裡面的反引號類似了。
create table test1(`int` int);
Query OK, 0 rows affected (0.00 sec)
不過這個時候還是要注意。下面的輸出結果,其實如果用雙引號,還是不規範的,而且需要應用端去修改,這樣就是一個隱藏的雷。多謝懷總指點。
SQL> create table test("uid" number);
Table created.
SQL> select uid,UID,"uid" from test;
UID UID uid
---------- ---------- ----------
0 0 1
所以在資料匯入之後還是最好把欄位名改過來,我就直接改成了uidd,因為欄位名錶關聯沒有強制要求uid這個列名。
關於保留字可以透過下面的方式來查詢
SQL> select * from v$reserved_words where keyword='UID';
外部表載入了之後,關聯的時候發現竟然沒有匹配的資料,最後發現還是得trim一下資料
select t1.uidd ,t2.cn_number from passport_ext t1,passport t2 where trim(t1.serial_no)=t2.seriao_no;
透過這種方式就得到了一個資料清單,可以再次推送給MySQL端了。
這個案例還是很簡單的,但是把這個過程做了多步的分解,可以看出在資料遷移中還是有很多的潛在因素需要考慮。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26845409/viewspace-1819689/,如需轉載,請註明出處,否則將追究法律責任。

相關文章