如何把海量數據從 Oracle 導入到 Mongodb

一、背景

因為業務需求，現在需要把 Oracle 中幾千萬的數據轉移到 Mongodb，如果通過 PL/SQL Develop 導出，速度會比較慢，而且也很占用帶寬。發現一款軟件 sqluldr2 數據導出速度非常快，我們后面演示通過 sqluldr2 數據導出。

整體思路

把oracle中的數據導入到csv格式，然后在mongodb中使用mongoimport工具導入到mongo數據庫中。

下載地址

官方下載：http://×××w.anysql.net/software/sqluldr.zip
官方下載：http://×××w.onexsoft.com/zh/download

二、安裝工具

程序放在 oracle 的家目錄，第一次執行的時候會報錯，它回去尋找libclntsh.so這個庫文件，這個文件沒有在庫的環境變量里面，我們可以在oracle的安裝目錄里面找到，然后我們做個軟連接就可以了。

ln -s /u01/oracle/11.0.2.4/lib/libclntsh.so /usr/lib64

二、工具參數

• 切換到oracle用戶執行工具

SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.

License: Free for non-commercial useage, else 100 USD per server.

Usage: SQLULDR2 keyword=value [,keyword=value,...]

Valid Keywords:
  user    = username/password@tnsname  
  sql     = SQL file name  
  query   = select statement  
  field   = separator string between fields  
  record  = separator string between records  
  rows    = print progress for every given rows (default, 1000000)   
  file    = output file name(default: uldrdata.txt)  
  log     = log file name, prefix with + to append mode  
  fast    = auto tuning the session level parameters(YES)  
  text    = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).  
  charset = character set name of the target database.  
  ncharset= national character set name of the target database.  
  parfile = read command option from parameter file   
  read    = set DB_FILE_MULTIBLOCK_READ_COUNT at session level  
  sort    = set SORT_AREA_SIZE at session level (UNIT:MB)   
  hash    = set HASH_AREA_SIZE at session level (UNIT:MB)   
  array   = array fetch size  
  head    = print row header(Yes|No)  
  batch   = save to new file for every rows batch (Yes/No)  
  size    = maximum output file piece size (UNIB:MB)  
  serial  = set _serial_direct_read to TRUE at session level  
  trace   = set event 10046 to given level at session level  
  table   = table name in the sqlldr control file  
  control = sqlldr control file and path.  
  mode    = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE   
  buffer  = sqlldr READSIZE and BINDSIZE, default 16 (MB)  
  long    = maximum long field size  
  width   = customized max column width (w1:w2:...)   
  quote   = optional quote string   
  data    = disable real data unload (NO, OFF)   
  alter   = alter session SQLs to be execute before unload   
  safe    = use large buffer to avoid ORA-24345 error (Yes|No)   
  crypt   = encrypted user information only (Yes|No)   
  sedf/t  = enable character translation function   
  null    = replace null with given value   
  escape  = escape character for special characters  
  escf/t  = escape from/to characters list   
  format  = MYSQL: MySQL Insert SQLs, SQL: Insert SQLs.  
  exec    = the command to execute the SQLs.  
  prehead = column name prefix for head line.  
  rowpre  = row prefix string for each line.  
  rowsuf  = row sufix string for each line.  
  colsep  = separator string between column name and value.  
  presql  = SQL or scripts to be executed before data unload.  
  postsql = SQL or scripts to be executed after data unload.  
  lob     = extract lob values to single file (FILE).  
  lobdir  = subdirectory count to store lob files .  
  split   = table name for automatically parallelization.  
  degree  = parallelize data copy degree (2-128).  

1、要導出的數據由query控制

query參數如果整表導出，可以直接寫表名，如果需要查詢運算和where條件，query=“sql文本”，也可以把復雜sql寫入到文本中由query調用。

2、分隔符設置

默認是逗號分隔符，通過field參數指定分隔符。

sqluldr2 testuser/testuser query=chen.tt1 field=";"

3、大數據量操作

對于大表可以輸出到多個文件中，指定行數分割或者按照文件大小分割，例如：

sqluldr2 testuser/testuser@orcl query="select * from test_table2" file=test_table2_%B.txt batch=yes rows=500000

三、執行導出

1、本地執行方式

users參數可以省略不寫，和expdp username/passwd 方式一樣。

export ORACLE_SID=orcl
sqluldr2 testuser/testuser  query="select * from test" file=test_table1.txt

2、tns方式

sqluldr2 user=testuser/testuser@orcl  query="select * from test" file=test_table1.txt

3、簡易連接

sqluldr2 user=testuser/testuser@x.x.x.x:1521/orcl  query="select * from test" file=test_table1.txt

嚴格按照要求寫語句，等號兩邊不能有空格。

四、實例

一切準備就緒之后，切換到oracle用戶下面，執行下面命令。

[oracle@cookie ~]$ ./sqluldr2linux64.bin user=gather/gapass@orcl query="dmp_user_center" head=yes file=/home/oracle/dmp.csv       
           0 rows exported at 2018-10-09 14:40:27, size 0 MB.
     1000000 rows exported at 2018-10-09 14:40:36, size 80 MB.
     2000000 rows exported at 2018-10-09 14:40:43, size 144 MB.
     3000000 rows exported at 2018-10-09 14:40:50, size 212 MB.
     4000000 rows exported at 2018-10-09 14:40:57, size 276 MB.
     5000000 rows exported at 2018-10-09 14:41:04, size 340 MB.
     6000000 rows exported at 2018-10-09 14:41:11, size 404 MB.
     7000000 rows exported at 2018-10-09 14:41:18, size 460 MB.
     8000000 rows exported at 2018-10-09 14:41:25, size 504 MB.
     9000000 rows exported at 2018-10-09 14:41:31, size 548 MB.
     9403362 rows exported at 2018-10-09 14:41:34, size 568 MB.
         output file /home/oracle/dmp.csv closed at 9403362 rows, size 568 MB.

1、我是整表導出，所以query只填寫了表名。
2、head=yes 保留了表頭。
3、可以看到速度很快，一千萬的數據一分鐘就導出來了，如果是新機器，我相信速度會更加快很多。

五、Mongodb數據導入

[root@mbasic ~]# mongoimport -udmp -p dmp --db dmp --collection dmp_user_center --type csv --headerline --ignoreBlanks --file dmp.csv    
2018-10-09T14:49:13.580+0800    connected to: localhost
2018-10-09T14:49:16.551+0800    [........................] dmp.dmp_user_center  5.9 MB/568.5 MB (1.0%)
2018-10-09T14:49:19.551+0800    [........................] dmp.dmp_user_center  11.7 MB/568.5 MB (2.1%)
2018-10-09T14:49:22.551+0800    [........................] dmp.dmp_user_center  17.7 MB/568.5 MB (3.1%)
2018-10-09T14:49:25.551+0800    [........................] dmp.dmp_user_center  23.4 MB/568.5 MB (4.1%)
2018-10-09T14:49:28.551+0800    [#.......................] dmp.dmp_user_center  29.1 MB/568.5 MB (5.1%)
2018-10-09T14:49:31.551+0800    [#.......................] dmp.dmp_user_center  35.0 MB/568.5 MB (6.2%)

2018-10-09T14:54:49.551+0800    [#######################.] dmp.dmp_user_center  563.0 MB/568.5 MB (99.0%)
2018-10-09T14:54:52.551+0800    [#######################.] dmp.dmp_user_center  567.4 MB/568.5 MB (99.8%)
2018-10-09T14:54:53.447+0800    [########################] dmp.dmp_user_center  568.5 MB/568.5 MB (100.0%)
2018-10-09T14:54:53.447+0800    imported 9403362 documents