600万人のユーザーデータをMYSQL、MSSQL、Oracleデータベースにインポートする方法
60437 ワード
1.MySqlデータベースのインポート
参考文献:http://zhuaxia.org/blog/post/145
1.1.LOAD DATA INFILE構文
得られたデータベースファイルはテキストファイルwww.csdnであるからである.net.sql、そのためmysqlの中のLOAD DATA INFILEコマンドを使う必要があります.LOAD DATA INFILEの文法構造は以下の通りです.
View Code
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]1.2.データを格納するテーブルの作成
テキスト構造を観察すると、各行は次のように構成されています.
username # password # email中間は「#」で分割します.したがって、作成したテーブルには必ずusername、password、emailフィールドが含まれていますが、テーブルにプライマリ・キー列を追加し、自動的に成長させる必要があります.これにより、データを追加するときに手動でプライマリ・キー列を追加する必要はありません.したがって、テーブル構造は次のとおりです.
View Code
CREATE TABLE `csdnuser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;注意:MySQLではMyISAMエンジンとInnoDBエンジンの違いがありますが、このブログのテーマではありませんので、省略します.
1.3.データのインポート
テーブルの作成後、load data infileコマンドでデータをインポートできます.具体的なインポートコマンドは次のとおりです.
load data local infile 'd:\\www.csdn.net.sql' into table csdnuser2 fields terminated by ' # ' (username,password,email);およそ1分ほど運転するとインポートが完了し、インポートが完了すると次のように表示されます.
Query OK, 6428632 rows affected, 2030 warnings (54.47 sec)
Records: 6428632 Deleted: 0 Skipped: 0 Warnings: 295注意:
インストールMySQL 5.1デフォルトのデータベース・ファイルの保存先は、C:Documents and SettingsAll UsersApplication DataMySQLです.たとえば、私たちのcsdndbはパスC:Documents and SettingsAll UsersApplication DataMySQLMySQL Server 5.1datacsdndbにあります.システムを頻繁に復元する場合は、cディスクに置くのは安全ではありません.インストール時にインストールパスを変更することができます.次の図に示します.
2.Sql Serverデータベースのインポート
参考文献:http://qiaolevip.iteye.com/blog/1324649
2.1.BULK INSERT構文
Sql Serverでは、BULK INSERTコマンドを使用してデータをインポートします.このコマンドは、ユーザーが指定した形式でデータベース・テーブルまたはビューにデータ・ファイルをインポートします.BULK INSERT構文は次のとおりです.
View Code
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] FIRSTROW = first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ]
[ [ , ] MAXERRORS = max_errors ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
[ [ , ] TABLOCK ]
[ [ , ] ERRORFILE = 'file_name' ]
)] 2.2. データベーステーブルの作成
View Code
if exists(select * from sysobjects where name = 'csdn1')
drop table csdn1
create table csdn1(
--id int identity(1,1) primary key,
username varchar(50) not null,
[password] varchar(50) not null,
email varchar(50) not null
)2.3.bulk insertを使用してデータをインポートする
View Code
bulk insert csdn1
from 'D:\www.csdn.net.sql'
with(
--FORMATFILE = 'C:\BCPFORMAT.xml',
--FirstRow --default 1
--KEEPIDENTITY,
fieldterminator = ' # ',
rowterminator = '
'
)(6428632行が影響を受ける)、所要時間:00:02:00
ここでfieldterminator='#'、列区切り記号を表し、rowterminator='、行区切り記号を表します.
2.4.プライマリ・キー列の追加
参照:http://topic.csdn.net/u/20090913/15/fa2e7e65-73d8-4b64-b6e0-bd583f564d86.html?95717
上記の操作では、データベースにデータをインポートするが、データベーステーブルcsnd 1にプライマリ・キー列がないことがわかり、テーブルにwww.csdnを追加する.net.sqlファイルにないプライマリ・キー列以降、データベースのインポートはどのように行いますか?複数回のテストでは、元のbulk insertコマンドではインポートできませんが、bulkのようなコマンドでインポートできます.
まず、プライマリ・キーid付きcsdnuserテーブルを作成します.
View Code
-- csdnuser
if exists(select * from sysobjects where name = 'csdnuser')
drop table csdnuser
create table csdnuser(
id int identity(1,1) primary key,
username varchar(50) not null,
[password] varchar(50) not null,
email varchar(50) not null
)次に、次のコマンドを使用してインポートします.
View Code
--
INSERT INTO csdnuser([username],[password],[email])
SELECT * FROM OPENROWSET(
BULK 'D:/www.csdn.net.sql',
FORMATFILE='d:/BCPFORMAT.xml'
) AS T;(6,428,632行が影響を受ける)、所要時間:00:01:25
3.Oracleデータベースのインポート
3.1.ターゲット・テーブルは3列のみ
参照:http://www.cnblogs.com/nocode/archive/2011/12/26/2302343.html
まずデータベース・テーブルを作成し、scottユーザーの下でcsdnテーブルを作成します.
View Code
CREATE TABLE "SCOTT"."CSDN"
(
"USERNAME" VARCHAR2(256 BYTE),
"PASSWORD" VARCHAR2(256 BYTE),
"EMAIL" VARCHAR2(256 BYTE)
)制御ファイルD:www.csdnを作成する.net.sql
View Code
UNRECOVERABLE
LOAD DATA INFILE 'D:\www.csdn.net.sql'
INSERT into table CSDN
fields terminated by '#'
(
USERNAME,
PASSWORD,
EMAIL
)cmdコマンドラインを開き、sqlldrコマンドを使用してインポートします.インポートコマンドは次のとおりです.
C:\Users\xuwei>sqlldr userid=scott/tiger@orcl control=D:/ora_csdn.ctrl DIRECT=TRUE log=resulthis.out出力されたログファイルはresulthisです.out、彼はC:Usersxuweiディレクトリの下にいます.上記のsqlldrはこのディレクトリの下で実行されているからです.resulthis.outの内容は以下の通りです.
View Code
SQL*Loader: Release 11.1.0.6.0 - Production on 6 11 15:17:07 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
: D:/ora_csdn.ctrl
: D:\www.csdn.net.sql
: D:/www.csdn.net.bad
:
( )
: ALL
: 0
: 50
:
:
UNRECOVERABLE; 。
CSDN,
INSERT
------------------------------ ---------- ----- ---- ---- ---------------------
USERNAME FIRST * # CHARACTER
PASSWORD NEXT * # CHARACTER
EMAIL NEXT * # CHARACTER
CSDN:
6428632 。
, 0 。
WHEN , 0 。
, 0 。
。
: 5000
: 256000
: 1048576
: 0
: 6428632
: 0
: 0
SQL*Loader : 1370
SQL*Loader : 0
6 11 15:17:07 2012
6 11 15:17:22 2012
: 00: 00: 15.08
CPU : 00: 00: 05.52上記のログから、インポートにかかる時間は29.86秒であることがわかります.
3.2データテーブルにプライマリ・キー列を追加し、自動的に増加
参照先:
(1) Mysql,SqlServer,Oracleプライマリ・キーの自動増加の設定
(2) sqlldrでsequenceを使用する
最初はプライマリ・キーのsequenceを作成し、トリガでプライマリ・キーを挿入することを考慮しましたが、エラーが発生していました.その後、制御ファイルでsequenceを使用してプライマリ・キーを挿入して操作を完了します.
データベース表の作成:CSDNUSER
View Code
CREATE TABLE "SCOTT"."CSDNUSER"
(
"ID" int primary key not null,
"USERNAME" VARCHAR2(256),
"PASSWORD" VARCHAR2(256),
"EMAIL" VARCHAR2(256)
)_______________________________________________
PS:2012-6-11
インデックスのレベルBLEVELが必要な場合は、次のクエリ文で求めることができます.
select index_name, blevel, num_rows from user_indexes where table_name = 'CSDNUSER'; ただし、検索結果は0です.その後、上記のテーブルに問題が発生したため、primary keyに名前が付けられていないこと、primary keyの名前が指定されていない場合、システムはデフォルトでSYS_などの名前を設定します.C 0036842などのキー名.そこで、上記のプライマリ・キーを削除してから、プライマリ・キーを追加します.
View Code
--
alter table csdnuser drop constraint SYS_C0038642;
--
alter table csdnuser add constraint pk_csdnuser primary key(ID);もちろん、テーブルを作成するときにプライマリ・キー名を直接作成することもできます.
View Code
CREATE TABLE "CSDNUSER"
(
"ID" INT ,
"USERNAME" VARCHAR2(256),
"PASSWORD" VARCHAR2(256),
"EMAIL" VARCHAR2(256),
CONSTRAINT "PK_CSDNUSER" PRIMARY KEY ("ID")
)プライマリ・キーを作成するときに、プライマリ・キーも一定のディスク領域を占めているため、時間がかかることがわかりました.
————————————————————————————
制御ファイルの作成:ora_csdn2.ctrl
View Code
load data
infile 'D:\www.csdn2.net.sql'
Append into table CSDNUSER2
fields terminated by ' # '
trailing nullcols
( ID sequence(max,1),
USERNAME,
PASSWORD,
EMAIL
)cmdでsqlldrコマンドを実行する
C:\Users\xuwei>sqlldr userid=scott/tiger@orcl control=D:/ora_csdn2.ctrl direct=true log=resulthis.outresulthis.outの内容は以下の通りです.
View Code
SQL*Loader: Release 11.1.0.6.0 - Production on 6 11 15:30:00 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
: D:/ora_csdn2.ctrl
: D:\www.csdn.net.sql
: D:/www.csdn.net.bad
:
( )
: ALL
: 0
: 50
:
:
UNRECOVERABLE; 。
CSDNUSER,
INSERT
TRAILING NULLCOLS
------------------------------ ---------- ----- ---- ---- ---------------------
ID SEQUENCE (MAX, 1)
USERNAME FIRST * CHARACTER
: ' # '
PASSWORD NEXT * CHARACTER
: ' # '
EMAIL NEXT * CHARACTER
: ' # '
CSDNUSER :
SCOTT.SYS_C0038642 , 6428632
CSDNUSER:
6428632 。
, 0 。
WHEN , 0 。
, 0 。
。
: 5000
: 256000
: 1048576
: 0
: 6428632
: 0
: 0
SQL*Loader : 1370
SQL*Loader : 2
6 11 15:30:00 2012
6 11 15:31:02 2012
: 00: 01: 02.45
CPU : 00: 00: 15.323.3データテーブルに年齢列を追加する(2012-6-13)
上記のデータを用いてデータ分析を行うと、内容が単調すぎることがわかります.次に、元のデータに年齢age列を追加します.元の「username#password#email」のデータソースファイルを「username#password#email#age」のファイルにします.ageはjavaコードでファイルに書き込まれた[18,99]区間のランダム数です.乱数の生成は、前のブログ:JAva指定範囲の乱数を生成を参照できます.
Javaプログラムでwww.csdn.net.sqlソースファイルageの列を追加し、新しいファイルwww.csdn 22を生成する.net.sql
View Code
package edu.sjtu.erplab.io;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.util.Random;
public class FileAddCol {
public static void main(String[] args) {
File file = new File("D:\\www.csdn22.net.sql");//
try {
FileOutputStream fos = new FileOutputStream(file);//
BufferedReader br = new BufferedReader(new FileReader("D:\\www.csdn.net.sql"));
String temp = null;// 。
StringBuffer sb = new StringBuffer();// StringBuffer
/**
*
*/
int max=99;
int min=18;
Random random = new Random();
while ((temp = br.readLine()) != null) // ， 。
{
// ， ：java.lang.OutOfMemoryError: Java heap space
if(sb.length()>1000000)
{
fos.write(sb.toString().getBytes());
sb.delete(0, sb.length()-1);
}
int s = random.nextInt(max)%(max-min+1) + min;
temp =temp+" # "+s;
sb.append(temp + "\r
");
}
fos.write(sb.toString().getBytes());
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}データベース・テーブルの作成:csdnuser 3
View Code
CREATE TABLE "SCOTT"."CSDNUSER3"
(
"ID" INT,
"USERNAME" VARCHAR2(256),
"PASSWORD" VARCHAR2(256),
"EMAIL" VARCHAR2(256),
"AGE" INT,
CONSTRAINT "PK_CSDNUSER3_ID" PRIMARY KEY ("ID")
)制御ファイルora_の作成csdn3.ctrl
View Code
UNRECOVERABLE
load data
infile 'D:\www.csdn22.net.sql'
insert into table CSDNUSER3
fields terminated by ' # '
trailing nullcols
( ID sequence(max,1),
USERNAME,
PASSWORD,
EMAIL,
AGE
)cmdコマンドラインを開き、sqlldrコマンドを使用してインポートします.インポートコマンドは次のとおりです.
C:\Users\xuwei>sqlldr userid=scott/tiger@orcl control=D:/ora_csdn3.ctrl DIRECT=TRUE log=resulthis.out出力されたログファイルはresulthisです.out、彼はC:Usersxuweiディレクトリの下にいます.上記のsqlldrはこのディレクトリの下で実行されているからです.resulthis.outの内容は以下の通りです.
View Code
SQL*Loader: Release 11.1.0.6.0 - Production on 6 13 12:18:21 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
: D:/ora_csdn3.ctrl
: D:\www.csdn22.net.sql
: D:/www.csdn22.net.bad
:
( )
: ALL
: 0
: 50
:
:
UNRECOVERABLE; 。
CSDNUSER3,
INSERT
TRAILING NULLCOLS
------------------------------ ---------- ----- ---- ---- ---------------------
ID SEQUENCE (MAX, 1)
USERNAME FIRST * CHARACTER
: ' # '
PASSWORD NEXT * CHARACTER
: ' # '
EMAIL NEXT * CHARACTER
: ' # '
AGE NEXT * CHARACTER
: ' # '
CSDNUSER3 :
SCOTT.PK_CSDNUSER3_ID , 6428632
CSDNUSER3:
6428632 。
, 0 。
WHEN , 0 。
, 0 。
。
: 5000
: 256000
: 1048576
: 0
: 6428632
: 0
: 0
SQL*Loader : 1523
SQL*Loader : 1202
6 13 12:18:21 2012
6 13 12:18:59 2012
: 00: 00: 38.00
CPU : 00: 00: 15.88上記のログから、インポートに38.00秒かかることがわかります.
SQL*Loaderのパフォーマンスの向上:
一般的なインポートとdirectインポートの違い:
通常のインポートでは、INSERT文を使用してデータをインポートできます.Directインポートでは、データベースの関連ロジック(DIRECT=TRUE)をスキップし、データファイルに直接データをインポートできます.たとえばdirectインポートでは、プライマリ・キーの一意性は検出されません.
SQLLDRの使い方
用法:SQLLDR keyword=value[,keyword=value,...]有効なキーワード:
View Code
userid -- ORACLE username/password
control -- Control file name
log -- Log file name
bad -- Bad file name
data -- Data file name
discard -- Discard file name
discardmax -- Number of discards to allow ( )
skip -- Number of logical records to skip ( 0)
load -- Number of logical records to load ( )
errors -- Number of errors to allow ( 50)
rows -- Number of rows in conventional path bind array or between direct path data saves（ : 64, ）
bindsize -- Size of conventional path bind array in bytes( 256000)
silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path ( FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load ( FALSE)
file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions( FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable( FALSE)
readsize -- Size of Read buffer ( 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE( NOT_USED)
columnarrayrows -- Number of rows for direct path column array( 5000)
streamsize -- Size of direct path stream buffer in bytes( 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session( FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE( 7200)
date_cache -- size (in entries) of date conversion cache( 1000)インポートされた4つのタイプ
TRAILING NULLCOLS--テーブルのフィールドに対応する値がない場合は空にします.