Shulou(Shulou.com)06/01 Report--

I. background

Because of business requirements, tens of millions of data in Oracle need to be transferred to Mongodb. If exported through PL/SQL Develop, the speed will be slow and it will take up a lot of bandwidth. Found a software sqluldr2 data export speed is very fast, we will demonstrate later through sqluldr2 data export.

Overall thinking

Import the data from oracle into csv format, and then use the mongoimport tool in mongodb to import into the mongo database.

Download address

Official download: http:// × × w.anysql.net/software/sqluldr.zip

Official download: http:// × × w.onexsoft.com/zh/download

II. Installation tools

The program is placed in the oracle home directory and will report an error when it is executed for the first time. It goes back to look for the libclntsh.so library file, which is not in the environment variable of the library. We can find it in the oracle installation directory, and then we can make a soft connection.

Ln-s / u01/oracle/11.0.2.4/lib/libclntsh.so / usr/lib64 II. Switch tool parameters to oracle user execution tool 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. 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. The data to be exported is controlled by query

Query parameter if the entire table is exported, you can write the table name directly, if you need query operations and where conditions, query= "sql text", or you can write complex sql into the text and be called by query.

2. Delimiter setting

The default is the comma delimiter, which is specified by the field parameter.

Sqluldr2 testuser/testuser query=chen.tt1 field= ";" 3. Large data volume operation

For large tables, you can output to multiple files, specify the number of lines or split by file size, for example:

Sqluldr2 testuser/testuser@orcl query= "select * from test_table2" file=test_table2_%B.txt batch=yes rows=500000 III, execute export 1, local execution mode

The users parameter can be omitted and left unwritten, just like expdp username/passwd.

Export ORACLE_SID=orclsqluldr2 testuser/testuser query= "select * from test" file=test_table1.txt2, tns sqluldr2 user=testuser/testuser@orcl query= "select * from test" file=test_table1.txt3, easy connection sqluldr2 user=testuser/testuser@x.x.x.x:1521/orcl query= "select * from test" file=test_table1.txt

Write sentences in strict accordance with the requirements, and there can be no spaces on both sides of the equal sign.

IV. Examples

When everything is ready, switch to the oracle user and execute the following command.

[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 144MB. 3000000 rows exported at 2018-10-09 14:40:50, size 2018 MB. 4000000 rows exported at 2018-10-09 14:40:57, size 2018 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 2018 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. I export the whole table, so query only filled in the table name.

2. Head=yes retains the header.

3. You can see that the speed is very fast, and 10 million of the data is exported in a minute. If it is a new machine, I believe it will be much faster.

5. Import Mongodb data into [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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.