前言

最近正在做一個(gè)項(xiàng)目，需要導(dǎo)出數(shù)據(jù)庫(kù)中的表，但是數(shù)據(jù)庫(kù)中有很多帶有虛擬列的表，而且表中的數(shù)據(jù)非常的龐大，有些表中的數(shù)據(jù)上億條，項(xiàng)目經(jīng)理讓我研究如何快速導(dǎo)出這些數(shù)據(jù)。

下面是我研究的一些經(jīng)歷：

（1）、我先使用plsql developer導(dǎo)出dmp（實(shí)際上是通過(guò)emp導(dǎo)出），但是不能導(dǎo)出帶有虛擬列的表，導(dǎo)出的速度有點(diǎn)慢；

（2）、使用plsql developer自帶的導(dǎo)出功能，如圖所示：

該方法可以導(dǎo)出虛擬列，但是導(dǎo)出的速度很慢，比dmp還慢，大約是方法（1）的2倍時(shí)間。

（3）、使用數(shù)據(jù)泵  DataPump導(dǎo)出，該方法可以導(dǎo)出虛擬列，而且速度還可以，但是如果導(dǎo)出遠(yuǎn)程庫(kù)數(shù)據(jù)的時(shí)候，需要用dblink，而且需要很高的權(quán)限（相當(dāng)于dba的權(quán)限），所以該方法也被排除。

山重水復(fù)疑無(wú)路，柳暗花明又一村，我發(fā)現(xiàn)了sqluldr2這個(gè)神器，又能導(dǎo)出虛擬列，而且導(dǎo)入導(dǎo)出的速度非?？?，下面我們就進(jìn)入正題。

sqluldr2下載與安裝

1、軟件下載地址：

百度云鏈接：https://pan.baidu.com/s/1V8eqyyYsbJqQSD-Sn-RQGg

下載完后并解壓會(huì)生成4個(gè)文件

sqluldr2.exe  用于32位windows平臺(tái)； 

sqluldr2_linux32_10204.bin  適用于linux32位操作系統(tǒng)；

sqluldr2_linux64_10204.bin  適用于linux64位操作系統(tǒng)；

sqluldr264.exe             用于64位windows平臺(tái)。

2、直接在cmd上運(yùn)行（我的sqluldr文件放在H盤(pán)里，我電腦是64位，所以使用sqluldr264）

首先，你必須安裝oracle，沒(méi)有安裝oracle，sqluldr2不能運(yùn)行，運(yùn)行完后出現(xiàn)如下的界面，這樣就證明可以成功運(yùn)行。

sqluldr2 導(dǎo)出

1、導(dǎo)出命令的主要參數(shù)

user=用戶(hù)名/密碼@ip地址:1521/服務(wù)   ,如果是本地庫(kù)，可以只寫(xiě) 用戶(hù)名和密碼：eg：user=用戶(hù)名/密碼

query=”sql查詢(xún)語(yǔ)句”

head=yes|no  是否導(dǎo)出表頭

file=文件存放路徑（該文件可以寫(xiě)很多后綴: .txt  .csv   .dmp 等等，我發(fā)現(xiàn)，導(dǎo)出.dmp文件速度快）

table=查詢(xún)的表名 有這句話，sqluldr2會(huì)自動(dòng)生成一個(gè).ctl文件，導(dǎo)入的時(shí)候會(huì)用到（）;

Field：分隔符，指定字段分隔符，默認(rèn)為逗號(hào)； 比如：field=#  在選擇分隔符時(shí)，一定不能選擇會(huì)在字段值中出現(xiàn)的字符組合，如常見(jiàn)的單詞等，很多次導(dǎo)入時(shí)報(bào)錯(cuò)，回過(guò)頭來(lái)找原因時(shí)，都發(fā)現(xiàn)是因?yàn)榉指舴霈F(xiàn)在字段值中了。

record：分隔符，指定記錄分隔符，默認(rèn)為回車(chē)換行，Windows下的換行；

quote：引號(hào)符，指定非數(shù)字字段前后的引號(hào)符；

charset：字符集，執(zhí)行導(dǎo)出時(shí)的字符集，一般有UTF8、GBK等；

2、常規(guī)的命令

sqluldr264 user=zxx/zxx123@127.0.0.1:1521/orcl query="select * from mv_xlsymx1 where ysyddm='00001H'"  head=yes file=h:\mx.csv  log=+h:\tem.log

3、可以使用sql參數(shù)

可以使用sql參數(shù)代替query

sqluldr264 user=zxx/zxx  sql=h:\test.sql head=yes file=h:\mx.csv 

test.sql是提前維護(hù)好的一個(gè)文件，文件的內(nèi)容為sql語(yǔ)句。

4、帶有table參數(shù)的導(dǎo)出

sqluldr264 user=zxx/zxx query="select * from mv_xlsymx1 where ysyddm='00001H'" table=mv_xlsymx1  head=yes file=h:\mx.csv

它會(huì)生成一個(gè).ctl文件(mv_xlsymx1_sqlldr.ctl,默認(rèn)生成在sqluldr文件下，我的就生成在h:\sqluldr\ mv_xlsymx1_sqlldr.ctl)

5、指定.ctl文件生成的位置

sqluldr264 user=zxx/zxx  query="select * from mv_xlsymx1 where ysyddm='00001H'" table=mv_xlsymx1  control=h:\mx.ctl head=yes file=h:\mx.csv

6、帶有日志log參數(shù)

當(dāng)集成sqluldr2在腳本中時(shí)，就希望屏蔽上不輸出這些信息，但又希望這些信息能保留，這時(shí)可以用“LOG”選項(xiàng)來(lái)指定日志文件名。

sqluldr264 user=zxx/zxx query="select * from mv_xlsymx1 where ysyddm='00001H'"  head=yes file=h:\mx.csv  log=+h:\tmp.log

注意：這里的log路徑要寫(xiě)上“+”

sqlldr 導(dǎo)入

1、我們先查看sqlldr的幫助文檔

2、導(dǎo)入之前，我們需要先熟悉一下.ctl文件

characterset ：字符集， 一般使用字符集 AL32UTF8，如果出現(xiàn)中文字符集亂碼時(shí)，改成 ZHS16GBK。

fields terminated by 'string'：文本列分隔符。當(dāng)為tab鍵時(shí)，改成'\t'，或者 X'09'；空格分隔符 whitespace，換行分隔符 '\n' 或者 X'0A'；回車(chē)分隔符 '\r' 或者 X'0D'；默認(rèn)為'\t'。

optionally enclosed by 'char'：字段包括符。當(dāng)為 ' ' 時(shí)，不把字段包括在任何引號(hào)符號(hào)中；當(dāng)為 "'" 時(shí)，字段包括在單引號(hào)中；當(dāng)為'"'時(shí)，字段在包括雙引號(hào)中；默認(rèn)不使用引用符。

fields escaped by 'char'：轉(zhuǎn)義字符，默認(rèn)為'\'。

trailing nullcols：表字段沒(méi)有對(duì)應(yīng)的值時(shí)，允許為空。

append into table "T_USER_CTRL"  -- 操作類(lèi)型

　　-- 1) insert into     --為缺省方式，在數(shù)據(jù)裝載開(kāi)始時(shí)要求表為空

　　-- 2) append into  --在表中追加新記錄

　　-- 3) replace into  --刪除舊記錄(相當(dāng)于delete from table 語(yǔ)句)，替換成新裝載的記錄

　　-- 4) truncate  into  --刪除舊記錄(相當(dāng)于 truncate table 語(yǔ)句)，替換成新裝載的記錄skip=1 ：表示插入數(shù)據(jù)時(shí)，跳過(guò)第一行(標(biāo)題)，從第二行開(kāi)始導(dǎo)入；

 3、sqluldr 導(dǎo)入處理

3.1、基本的導(dǎo)入語(yǔ)句

sqlldr userid=hxj/hxj control=h:\sqluldr\mv_xlsymx1_sqlldr.ctl data=h:\mx.csv rows=1000

 如果是本地庫(kù)，可以直接只用 用戶(hù)名/密碼；

如果是遠(yuǎn)程庫(kù)，需要將userid寫(xiě)全    userid=用戶(hù)名/密碼@ip:1521/服務(wù)名

比如：userid=zxx/zxx123@10.3.36.110:1521/orcl，填寫(xiě)自己遠(yuǎn)程庫(kù)地址

3.2、帶有日志log參數(shù)

sqlldr userid=hxj/hxj control=h:\sqluldr\mv_xlsymx1_sqlldr.ctl data=h:\mx.csv log=h:\log\mx.log  rows=1000

注意：這里的log的路徑不能寫(xiě)“+”;

4、虛擬列處理

sqluldr2導(dǎo)出數(shù)據(jù)的時(shí)候，如果該表中含有虛擬列，你導(dǎo)出的時(shí)候沒(méi)有過(guò)濾掉虛擬列，比如：select * from 帶有虛擬列的表,那么你要對(duì)這些虛擬列進(jìn)行處理，否則導(dǎo)入的時(shí)候回報(bào)錯(cuò)。

我發(fā)現(xiàn)了三種處理方法：

4.1、在虛擬列后面加上filler，將這一列過(guò)濾掉。

4.2、將.ctl文件中的虛擬列刪除掉就可以了

4.3、在導(dǎo)出的時(shí)候，不導(dǎo)出虛擬列

比如，不寫(xiě)select * from 表名

直接將不是虛擬列的列名寫(xiě)出來(lái) select id,name from 表名

 5、使用并行處理

 5.1 未使用并行處理

sqlldr userid=hxj/hxj control=h:\ctl\qsddlqymx1_cyqs.ctl data=h:\qsddlqymx1_cyqs.dmp log=h:\log\qsddlqymx1_cyqs.log

1567258條數(shù)據(jù)大概需要 一分半

5.2、使用并行處理數(shù)據(jù)

需要在導(dǎo)入語(yǔ)句中加入  direct=true   parallel=true，如下所示：

sqlldr userid=hxj/hxj control=h:\ctl\qsddlqymx1_cyqs.ctl data=h:\qsddlqymx1_cyqs.dmp log=h:\log\qsddlqymx1_cyqs.log direct=true   parallel=true

并行能更快的導(dǎo)入數(shù)據(jù)，1567258條數(shù)據(jù)大概20秒，但是有缺點(diǎn)（我測(cè)試的時(shí)候發(fā)現(xiàn)的，可能有別的解決方法）

（1）：首先.ctl文件必須是append into table 表名；

（2）：需要導(dǎo)入的表不能有索引。

這是我寫(xiě)的第一篇博客，望看客老爺們多多指教。