如何把海量数据从 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=orclsqluldr2 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: localhost2018-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