使用外部表關聯MySQL資料到Oracle
因為業務需要,有個臨時的活動需要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端了。
這個案例還是很簡單的,但是把這個過程做了多步的分解,可以看出在資料遷移中還是有很多的潛在因素需要考慮。
這個流程本來從開發的角度來看似乎是一頭霧水,所以交給他們來規劃就容易出現問題,最後溝通後的流程是下面的形式。
下面這個圖左邊是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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 使用goldengate從mysql同步資料到oracleGoMySqlOracle
- 使用sqlldr載入外部檔案中的資料到Oracle中(轉)SQLOracle
- 使用mysqlimport匯入資料到mysqlMySqlImport
- oracle外部表詳解以及使用Oracle
- 外部表在Oracle資料庫中使用心得Oracle資料庫
- Oracle外部表Oracle
- Oracle 外部表Oracle
- mysql怎麼關聯表?MySql
- mysql-三表關聯MySql
- MySQL表關聯join方式MySql
- 使用外部表管理Oracle 告警日誌Oracle
- 異構資料庫的關聯查詢 oracle hsodbc 關聯mysql資料庫OracleMySql
- 使用oracle外部表進行資料泵解除安裝資料Oracle
- 【轉】Oracle 外部表Oracle
- 資料倉儲中從mysql導資料到oracleMySqlOracle
- 使用sqlldr匯入文字資料到oracleSQLOracle
- mysql 三表關聯查詢MySql
- mysql三表關聯查詢MySql
- 使用canal.adapter同步資料到MySQLAPTMySql
- 使用load data匯入資料到mysqlMySql
- 採用importtsv匯入外部資料到hbase中ImportTTS
- oracle 外部表 external tableOracle
- Oracle外部表 External TableOracle
- Oracle外部表學習Oracle
- 介紹oracle外部表Oracle
- oracle sqlldr 與 外部表OracleSQL
- 多表關聯更新(mysql,oracle,postgreSQL)MySqlOracle
- mysql三張表關聯查詢MySql
- 使用Oracle的外部表查詢警告日誌Oracle
- mysql怎麼複製一張表的資料到另一張表MySql
- MySQL中複製資料表中的資料到新表中的操作教程MySql
- 上傳資料到內表
- Oracle imp 匯入資料到另一個表空間Oracle
- 使用Direct-Path INSERT插入資料到表中
- oracle insert兩個關聯表Oracle
- ORACLE學習之外部表Oracle
- MYSQL A、B表陣列關聯查詢MySql陣列
- mysql三表關聯查詢練習MySql