必赢官网BCP导出导入大体积数据施行,SQLServer导数据到Oracle

把导出文件上传到Oracle所在的主机上,如CentOS下。

在SQL Server Agent新建一个作业

如果有错误,还会生成与导入文件同名的t1.bad文件。

过程要花上几分钟的时间才能完成,请耐心等待一下。关于数据的构造,可以参考我的另一篇博文:

以下是日志文件,显示数据导入的一些信息。成功导入了18495032行记录,没有导入失败的记录。

BCP几个常用的参数说明:

但从导出导入的速度来说,是最快的,平面文件可以跨不同的数据库进行迁移。如果数据不容忍丢失,只能通过工具来导了,但速度会相对较慢。

figure-2

以下使用第2种方法来进行数据迁移的。

 

sqlldr user/"user_password" control=import-t1.ctl

 

默认下,生成的日志文件在当前目录下。无论成功与否,一定要查看日志。看看是否导入成功或失败,或是部分成功。导入的问题一般从日志文件即可找到。

code-4

load data
CHARACTERSET 'ZHS16GBK'
infile '/data/import/t1.txt' "str 'rn'"
into table SCOTT.T1
fields terminated by '||' TRAILING NULLCOLS
(
carId, 
addTime DATE "YYYY-MM-DD HH24:MI:SS",
lastSearchTime DATE "YYYY-MM-DD HH24:MI:SS",
updateTime DATE "YYYY-MM-DD HH24:MI:SS",
carType ,
userTelephone  ,
isCorrect  ,
userId  ,
validFlag ,
Channel ,
carCode  ,
engineNumber ,
carNumber  
)

关于BULK
INSERT更详细的说明,参考:

  • 字符编码
  • 字段分隔符
  • 行结束符
  • 日期或时间格式
  • 特殊字符
  • 导入字段的顺序
  • 导文件文件的表字段类型和长度是否合适

figure-4

使用Oracle的SQL*LOADER导入平面文件。假如Oracle中有已经创建好的表,与导入文件对应。

figure-6

把以下的内容用vi,写到import-t1.ctl

修改figure-2中的out为in即可,把数据导入。

从SQLServer导数据到Oracle大概有以下几种方法:

 

如果导出时还需要做一些数据的处理,比如多表关联,字符处理等,比较复杂的逻辑,最好是做成存储过程,BCP直接调用存储过程即可。

code-2

  1. 使用SSMS的导出数据向导,使用Microsoft ODBC for Oracle或Oracle
    Provider for OLE DB连接到Oracle
  2. 导出到平面文件
  3. 导出包含数据的SQL脚本。
  4. 使用ETL工具。
  5. 自己开发软件。

 

使用平面文件迁移数据,最大麻烦是就是特殊字符,或是有垃圾数据。如果原数据包含与字符分隔符相同的字符,如这里面的“||”,或是有一些不可见的字符,如回车,换行符,等。这些字符会造成导入时,分割字段错位,导致导入错误,数据导不全,甚至导入失败。

必赢官网 1

[oracle@ttoracle /data/import]$ cat import-t1.log 

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 12:46:09 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   import-t1.ctl
Character Set ZHS16GBK specified for all input.

Data File:      /data/import/t1.txt
  File processing option string: "str '
'"
  Bad File:     t1.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table SCOTT.T1, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CARID                               FIRST     *           CHARACTER            
    Terminator string : '||'
ADDTIME                              NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
LASTSEARCHTIME                       NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
UPDATETIME                           NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
CARTYPE                              NEXT     *           CHARACTER            
    Terminator string : '||'
USERTELEPHONE                        NEXT     *           CHARACTER            
    Terminator string : '||'
ISCORRECT                            NEXT     *           CHARACTER            
    Terminator string : '||'
USERID                               NEXT     *           CHARACTER            
    Terminator string : '||'
VALIDFLAG                            NEXT     *           CHARACTER            
    Terminator string : '||'
CHANNEL                              NEXT     *           CHARACTER            
    Terminator string : '||'
CARCODE                              NEXT     *           CHARACTER            
    Terminator string : '||'
ENGINENUMBER                         NEXT     *           CHARACTER            
    Terminator string : '||'
CARNUMBER                            NEXT     *           CHARACTER            
    Terminator string : '||'


Table SCOTT.T1:
  18495032 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 214656 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:      18495032
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Jun 15 12:46:09 2018
Run ended on Fri Jun 15 12:55:58 2018

Elapsed time was:     00:09:48.90
CPU time was:         00:03:37.62

必赢官网 2

使用BCP合适导出大容量数据。这里导出千万级别的数据,也是很快就能成功。

必赢官网 3

BCP "exec TestDB.dbo.export_t1 " queryout d:exportt1.txt -c -t"||" -S"192.168.1.100" -Urpt -Prpt123
pause

USE TestDB
GO

CREATE PROC [dbo].[export_usercar]
AS
    SELECT  [carId]
           ,CONVERT(NVARCHAR(30), [addTime], 120)
           ,CONVERT(NVARCHAR(30), [lastSearchTime], 120)
           ,CONVERT(NVARCHAR(30), [updateTime], 120)
           ,[carType]
           ,[userTelephone]
           ,[isCorrect]
           ,[userId]
           ,[validFlag]
           ,[Channel]
           ,[carCode]
           ,[engineNumber]
           ,[carNumber]
    FROM    [TestDB].[dbo].[t1] WITH ( NOLOCK )
    WHERE   validFlag = 1
            AND isCorrect = 1;

简单的导出例子2:

使用SQL*LOADER注意几个问题:

3.1.1 编写导入脚本

使用sqlldr命令把数据导入到Oracle中。

 

3. 扩展

 

首先要配置好的是SQL SERVER有权限读取相关目录和文件的权限。在Sql Server
Configuration Manager –> SQL Server Services
选择相应的实例,右键选择属性,在Log On页签,使用有足够权限启动SQL
SERVER和有权限读取相关目录的用户,比如读取网络盘。

3.1.2 配置JOB

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see "Surface Area Configuration" in SQL Server Books Online.

这里不使用格式化文件进行导出导入的演示了。详细介绍与使用,请参考联机丛书。

USE AdventureWorks2008R2
GO

IF OBJECT_ID(N'T1') IS NOT NULL
BEGIN
    DROP TABLE T1
END
GO

CREATE TABLE T1 (
    id_ INT,
    col_1 NVARCHAR(50),
    col_2 NVARCHAR(40),
    col_3 NVARCHAR(40),
    col_4 NVARCHAR(40),
    col_5 INT,
    col_6 FLOAT,
    col_7 DECIMAL(18,8),
    col_8 BIT,
    input_date DATETIME DEFAULT(GETDATE())
)
GO

WITH CTE1 AS ( 
SELECT a.[object_id] FROM master.sys.all_objects AS a,master.sys.all_objects AS b,sys.databases AS c
WHERE c.database_id <= 5
)

,CTE2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) as row_no FROM CTE1
)

INSERT INTO T1 (id_,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8)
SELECT row_no,REPLICATE(N'博客园 ',10),NEWID(),NEWID(),NEWID(),CAST(row_no * RAND() * 10 AS INT),row_no * RAND(),row_no * RAND(),CAST(row_no * RAND() AS INT) % 2
FROM CTE2 WHERE row_no <= 20000000
GO

2.1 导出数据

 

figure-7

 

更详细的参数,请参考:

3.1
数据导出导入自动化与数据接口

 

 

4. 总结

 

必赢官网 4

figure-12

而对于复杂的大容量导入情况,通常都会需要格式化文件。在以下情况下,必须使用格式化文件:

 

必赢官网 5

这里使用-w参数。BCP可以在CMD下导出数据,测试导出2000万条记录,我的笔记本使用了近8分钟左右的时间。BCP同时也可以在SSMS中执行,使用了6分多钟时间,比CMD下速度要快些,生成的文件大小一致,每个文件近5GB。

 

 

BCP 实用工具可以在 Microsoft SQL Server
实例和用户指定格式的数据文件间大容量复制数据。使用
BCP实用工具可以将大量新行导入 SQL Server
表,或将表数据导入数据文件。除非与 queryout
选项一起使用,否则使用该实用工具不需要了解 Transact-SQL
知识。BCP既可以在CMD提示符下运行,也可以在SSMS下执行。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

figure-8

CREATE PROCEDURE sp_import_data
AS
BEGIN 
DECLARE @path NVARCHAR(500)
DECLARE @sql NVARCHAR(MAX)
/*S_PARAMETERS表是可以在应用程序上配置路径的*/
SELECT  @path = value_ + CONVERT(NVARCHAR, getdate(), 23) + '.txt' FROM S_PARAMETERS WHERE [type] = 'Import'
/*T4是一张临时的中间表。先把数据从文件中读入到中间表,最后通过脚本把T4中间表的数据插入到实际的业务表中*/
SET @sql=N'BULK INSERT T4 FROM '''+ @path + '''
WITH (
    FIELDTERMINATOR = ''*'',
    ROWTERMINATOR = ''n''

)'
EXEC (@sql)
END
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

 

code-8

SQL
SERVER提供多种不同的数据导出导入的工具,也可以编写SQL脚本,使用存储过程,生成所需的数据文件,甚至可以生成包含SQL语句和数据的脚本文件。各有优缺点,以适用不同的需求。下面介绍大容量数据导出导入的利器——BCP实用工具。同时在后面也介绍BULK
INSERT导入大容量数据,以及BCP结合BULK
INSERT做数据接口的实践(在SQL2008R2上实践)。

语法:

EXEC [master]..xp_cmdshell
'BCP TestDB_2005.dbo.T1 out E:T1_02.txt -c -T'
GO

必赢官网 6

 

 

使用BCP并结合BULK
INSERT可实现大容量数据的快速导出导入,并可以实现其自动化工作。对于少量数据来说,操作也不算很复杂。这是除了SSMS上的图形化工具之外,又一个非常实用的工具。

简单的导出例子1:

必赢官网 7

database_name 指定的表或视图所在数据库的名称。如果未指定,则使用用户的默认数据库。
in | out| queryout | format
  • in 从文件复制到数据库表或视图。

  • out 从数据库表或视图复制到文件。如果指定了现有文件,则该文件将被覆盖。提取数据时,请注意 bcp 实用工具将空字符串表示为 null,而将 null 字符串表示为空字符串。

  • queryout 从查询中复制,仅当从查询大容量复制数据时才必须指定此选项。

  • format 根据指定的选项(-n-c-w-N)以及表或视图的分隔符创建格式化文件。大容量复制数据时,bcp 命令可以引用一个格式化文件,从而避免以交互方式重复输入格式信息。format 选项要求指定 -f 选项;创建 XML 格式化文件时还需要指定 -x 选项。

    in 从文件复制到数据库表或视图。
    out 从数据库表或视图复制到文件。如果指定了现有文件,则该文件将被覆盖。提取数据时,请注意 bcp 实用工具将空字符串表示为 null,而将 null 字符串表示为空字符串。
    queryout 从查询中复制,仅当从查询大容量复制数据时才必须指定此选项。

-c 使用字符数据类型执行该操作。此选项不提示输入每个字段;它使用 char 作为存储类型,不带前缀;使用 t(制表符)作为字段分隔符,使用 rn(换行符)作为行终止符。
-w 使用 Unicode 字符执行大容量复制操作。此选项不提示输入每个字段;它使用 nchar 作为存储类型,不带前缀;使用 t(制表符)作为字段分隔符,使用 n(换行符)作为行终止符。
-tfield_term 指定字段终止符。默认值为 t(制表符)。使用此参数可以替代默认字段终止符。
-rrow_term 指定行终止符。默认值为 n(换行符)。使用此参数可替代默认行终止符。
-Sserver_name[ instance_name] 指定要连接的 SQL Server 实例。如果未指定服务器,则 bcp 实用工具将连接到本地计算机上的默认 SQL Server 实例。如果从网络或本地命名实例上的远程计算机中运行 bcp 命令,则必须使用此选项。若要连接到服务器上的 SQL Server 默认实例,请仅指定 server_name。若要连接到 SQL Server 的命名实例,请指定 server_nameinstance_name。
-Ulogin_id 指定用于连接到 SQL Server 的登录 ID。
-Ppassword 指定登录 ID 的密码。如果未使用此选项,bcp 命令将提示输入密码。如果在命令提示符的末尾使用此选项,但不提供密码,则 bcp 将使用默认密码 (NULL)。
-T 指定 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL Server。不需要网络用户的安全凭据、login_id 和 password。如果未指定 –T,则需要指定 –U–P 才能成功登录。

在Steps页,在Command里执行写好的存储过程。

必赢官网 8

 

 

bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-ddatabase_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )] 
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] 
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-S [server_name[instance_name]]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

 

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注