您的位置:澳门新葡8455最新网站 > 数据库管理 > SQLServer导数据到Oracle,BCP导出导入大容量数据实

SQLServer导数据到Oracle,BCP导出导入大容量数据实

发布时间:2019-10-09 09:10编辑:数据库管理浏览(124)

    从SQLServer导数据到Oracle差非常的少有以下两种艺术:

    前言

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

    SQL SEKoleosVE宝马X5提供二种区别的数据导出导入的工具,也足以编制SQL脚本,使用存款和储蓄进程,生成所需的数据文件,乃至能够转移包括SQL语句和数目标台本文件。春兰秋菊势,以适用分化的要求。上面介绍大体量数据导出导入的利器——BCP实用工具。同期在背后也介绍BULK INSERT导入大容积数据,以及BCP结合BULK INSERT做多少接口的施行(在SQL二零零六Haval2上试行)。

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

     

    行使BCP合适导出大体积数据。这里导出千万等第的数目,也是快速就能够成功。

    1. BCP的用法

    只要导出时还亟需做一些数据的管理,举例多表关联,字符管理等,相比复杂的逻辑,最棒是做成存款和储蓄进程,BCP直接调用存款和储蓄进程就能够。

    BCP 实用工具能够在 Microsoft SQL Server 实例和客户钦赐格式的数据文件间大体量复制数据。使用 BCP实用工具能够将大气新行导入 SQL Server 表,或将表数据导入数据文件。除非与 queryout 选项一齐利用,不然使用该实用工具没有必要了然 Transact-SQL 知识。BCP不仅可以够在CMD提示符下运行,也能够在SSMS下试行。

    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;
    

    图片 1

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

    figure-1

    使用Oracle的SQL*LOADE宝马7系导入平面文件。要是Oracle中有一度创办好的表,与导入文本对应。

     

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

    语法:

    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  
    )
    
    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]"]
    
     
    

    使用SQL*LOADE帕杰罗注意多少个难题:

     

    • 字符编码
    • 字段分隔符
    • 行终止符
    • 日期或时间格式
    • 特殊字符
    • 导入字段的逐一
    • 导文件文件的表字段类型和尺寸是或不是确切

    简言之的导出例子1:

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

    图片 2

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

    figure-2

    私下认可下,生成的日记文件在当前目录下。无论成功与否,必须要翻开日志。看看是或不是导入成功或歇业,或是部分成功。导入的主题材料日常从日记文件就可以找到。

     

    倘诺有不当,还有大概会转移与导入文本同名的t1.bad文件。

    回顾的导出例子2:

    以下是日记文件,呈现数据导入的一对音信。成功导入了18495032行记录,未有导入战败的笔录。

    图片 3

    [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
    

    figure-3

    运用平面文件迁移数据,最大麻烦是就是特殊字符,或是有破烂数据。假设原数据富含与字符分隔符同样的字符,如那中间的“||”,或是有一对不可知的字符,如回车,换行符,等。这几个字符会产生导入时,分割字段错位,导致导入错误,数据导不全,乃至导入退步。

     

    但从导出导入的速度来讲,是最快的,平面文件能够跨差异的数据库实行搬迁。即便数据不容忍错失,只可以通过工具来导了,但速度会相对非常慢。

    在SSMS上还要也能够推行:

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

    code-1

     

    图片 4

    figure-4

     

     

    EXEC [master]..xp_cmdshell
    'BCP "SELECT * FROM TestDB_2005.dbo.T1" queryout E:T1_03.txt -c -T'
    GO
    

    code-2

     
    
     
    

    图片 5

    figure-5

     

    从个体来讲,小编更爱好使用第三种跟queryout分选一齐使用的写法,因为这么能够进一步灵活决定要导出的多寡。要是实践BCP命令碰到那样的一无所长提示:

    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.

    依据安全的思量,系统私下认可未有展开xp_cmdshell选项。使用下边语句开启此选项。

    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    
    EXEC sp_configure 'xp_cmdshell', 1
    RECONFIGURE
    GO
    

    code-3

     

    动用完现在,能够把sp_cmdshell关闭。

    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    
    EXEC sp_configure 'xp_cmdshell', 0
    RECONFIGURE
    GO
    

    code-4

     

    BCP导入数据

    修改figure-2中的out为in就能够,把多少导入。

    图片 6

    figure-6

     

    图片 7

    figure-7

     

    行使BULK INSERT导入数据

    BULK INSERT dbo.T1 FROM 'E:T1.txt'
    WITH (
        FIELDTERMINATOR = 't',
        ROWTERMINATOR = 'n'    
    )
    

    code-5

     

    图片 8

    figure-8

     

    至于BULK INSERT更详实的表明,参谋:

    比较BCP的导入,BULK INSERT提供更加灵活的抉择。

     

    BCP多少个常用的参数表明:

    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 才能成功登录。

    更详尽的参数,请参见:

     

     

     

    2. 实践

    2.1 导出多少

    介绍完BCP的导出导入,以及BULK INSERT的导入,下边实行一些其实的操作。为了好像实际条件,成立一张十三个字段的表,包涵有两种常用的数据类型,构造三千万的数目,满含汉语和乌Crane语。为了更加快插入测量检验数据,先不创设索引。在试行上面代码以前,请小心下数据库的日志恢复生机格局是不是设置为大体积方式或简捷格局,以及磁盘空间是或不是丰盛(作者的实行中,数据变化后数据文件和日志文件大致要求40G的长空)。

    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
    

    code-6

     

    进程要花上几分钟的小时手艺到位,请耐心等待一下。关于数据的构造,能够参见小编的另一篇博文:

    运用方面介绍的用法导出多少:

    EXEC [master]..xp_cmdshell
    'BCP AdventureWorks2008R2.dbo.T1 out E:T1_04.txt -w -T -S KENSQLSERVER08R2'
    GO
    

    code-7

     

    那边运用-w参数。BCP能够在CMD下导出多少,测量检验导出两千万条记下,我的记录簿使用了近8分钟左右的时日。BCP同不平日候也能够在SSMS中举行,使用了6分多钟时间,比CMD下速度要快些,生成的文件大小一致,各个文件近5GB。

    图片 9

    figure-9

     

    图片 10

    figure-10

     

    而对于复杂的大体量导入境况,平日都会供给格式化文件。在偏下情状下,必需选用格式化文件:

    • 持有差别架构的八个表使用一样数据文件作为数据源。

    • 数据文件中的字段数差异于指标表中的列数;举个例子:

      • 目的表中最少含有叁个概念了默许值或允许为 NULL 的列。

      • 顾客不富有对目的表的八个或多个列的 SELECT/INSERT 权限。

      • 怀有差异架构的多少个或八个表使用同二个数据文件。

       

    • 数据文件和表的列顺序区别。

    • 数据文件列的告一段落字符或前缀长度不一致。

     

    此地不应用格式化文件进行导出导入的示范了。详细介绍与运用,请参照他事他说加以考察联机丛书。

     

    2.2 导入数据

    动用BULK INSERT把数据导入到目的表数据。为增高品质,可有时删除索引,导完之后再重新建立索引等。请留神要预留丰裕的磁盘空间。这里差相当少花了15分钟导完。

    图片 11

    figure-11

     

     

    3. 扩展

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

    是因为专门的学问事关,临时要开支一些顾客的多寡接口,每一日活动导入相比较豁达的数据。限制于应用程序等因素影响,所以思虑直接利用SQL SERAV4VE猎豹CS6的BULK INSERT每一天活动去读取相关目录的中级文件。就算目录是动态的,但出于中等文件是固定格式的,通过编写制定动态SQL,最终封装成存款和储蓄进度,放到JOB中,配置运维的铺排,就可以产生自动化的干活。上边简单演示下进程:

     

    3.1.1 编写导入脚本

    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
    

    code-8

     

    3.1.2 配置JOB

    先是要布署好的是SQL SE奥迪Q7VEPAJERO有权力读取相关目录和文书的权位。在Sql Server Configuration Manager --> SQL Server Services 选取相应的实例,右键选用属性,在Log On页签,使用有丰裕权限运营SQL SE途观VETiguan和有权力读取相关目录的客商,比如读取网络盘。

    图片 12

    figure-12

     

    在SQL Server Agent新建一个学业

    图片 13

    figure-13

     

    在General页,采纳Owner,这里采纳sa。

    图片 14

    figure-14

     

    在Steps页,在Command里试行写好的蕴藏进度。

    图片 15

    figure-15

     

    在Schedules页,配置试行的时刻和频率等。实现。

    图片 16

    figure-16

     

     

    3.2 高版本数据库降级到低版本

    诚如的话,从低版本备份的数据库可以直接在高版本的数据库中苏醒的,举例SQL两千的备份能够在SQL二〇〇六或SQL二〇〇八中过来,除非是跨度太大的之外。比方SQL3000的备份就不可能一向在SQL二〇一三中平复,只好复苏到SQL二〇一〇,再从SQL2010备份出来,最后到SQL2011上回复。

    而高版本的备份经常不可能在低版本中复苏,如SQL二零一零的备份无法在SQL二零零五或SQL两千中还原。而实际中,却又会碰着这种须求。最佳是由此高版本SSMS直接连接七个不等版本的数据库,通过数据库间的数码导出导入或写剧本,把高版本的数额导到低版本的数据库中。那是对比快捷安全的不二秘籍。不过假如多个本子的数据库不可能循环不断,只可以是把数据导出来,再导入。对于数据量一点都不大以来,使用SSMS的导出导入功效,或是生成满含数据的脚本就能够(下图)。对于大数据以来,却是三个不幸,如前方有两千万数量的大表,生成数据的剧本也可能有多少个G大,直接利用SSMS奉行是不恐怕的了。只可以是行使SQLCMD实用工具,在后台实行SQL脚本,恐怕借助BCP、BULK INSERT等这种大容积数据导出导入的工具。

    图片 17

    figure-17

     

    4. 总结

    运用BCP并组成BULK INSERT可完成大容积数据的高速导出导入,并得以兑现其自动化工作。对于少许数码以来,操作也不算很复杂。那是除了SSMS上的图形化学工业具之外,又三个不胜实用的工具。

     

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:SQLServer导数据到Oracle,BCP导出导入大容量数据实

    关键词: