云顶娱乐平台 44

【云顶娱乐平台】[转]不同版本的SQL Server之间数据导出导入的方法及性能比较

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

 

4.LOCATION:定义了外部表的位置

工作中有段时间常常涉及到不同版本的数据库间导出导入数据的问题,索性整理一下,并简单比较下性能,有所遗漏的方法也欢迎讨论、补充。

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

code-4

f.验证新外部表的数据

select * from in_tb1;

ENAME                       JOB           SAL  DNAME
------------------------- -------------------- ---- -------------------------
CLARK                  MANAGER              2450 ACCOUNTING
KING                     PRESIDENT             5000 ACCOUNTING
MILLER                   CLERK                 1300 ACCOUNTING
JONES                    MANAGER               2975 RESEARCH
FORD                     ANALYST               3000 RESEARCH
ADAMS                    CLERK                 1100 RESEARCH
SMITH                    CLERK                  800 RESEARCH
SCOTT                    ANALYST               3000 RESEARCH
WARD                     SALESMAN              1250 SALES
TURNER                   SALESMAN              1500 SALES
ALLEN                    SALESMAN              1600 SALES
JAMES                    CLERK                  950 SALES
BLAKE                    MANAGER               2850 SALES
MARTIN                   SALESMAN              1250 SALES

14 rows selected.

03.使用BCP

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

 

1.ORGANIZATION EXTERNAL关键字,必须要有。以表明定义的表为外部表。

01.使用SQL Server Import and Export Tool

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

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

3.DEFAULT DIRECTORY:缺省的目录指明了外部文件所在的路径

07.结果对比

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  
)

code-7

1.外部表的创建语法

createtabletable_name
           (col1 datatype1,col2 datatype2,col3 datatype3)
            organization exteneral
           (…..)
详细语法可参见笔者的另两篇文章

Oracle外部表ORACLE_DATAPUMP类型的创建语法详解:

Oracle外部表ORACLE_LOADER类型的创建语法详解:

云顶娱乐平台 1 

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

 

外部表概述

外部表只能在Oracle
9i之后来使用。简单地说,外部表,是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据,我们可以把一个操作系统文件当成一个只读的数据库表,就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。

可以先看下测试的结果

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

figure-3

e. 新建表,将上述外部表的数据导入到新表中

create table in_tb1
            (ename varchar2(10),job varchar2(9),sal number(7,2),dname varchar(14))
            organization external
            (type oracle_datapump default directory data_dir location('in_tb1'));

00.建立测试环境

建立一个测试的环境,一个数据源数据库,版本为SQL Server
2008,一个目标数据库,版本为SQL Server 2000。

实验环境如下图所示,源数据库使用语句生成了100万的测试数据。

云顶娱乐平台 2

 

云顶娱乐平台 3建立测试表并生成100万的测试数据云顶娱乐平台 4

  IF OBJECT_ID('DEMOTABLE') IS NOT NULL 
      DROP TABLE DEMOTABLE
  GO
  CREATE TABLE DEMOTABLE
      (
        COL1 VARCHAR(50) ,
        COL2 VARCHAR(50) ,
        COL3 VARCHAR(50)
      )
  INSERT  INTO DEMOTABLE
         SELECT TOP 1000000
                 NEWID() ,
                 NEWID() ,
                 NEWID()
         FROM    MASTER..SPT_VALUES T1
                 INNER JOIN MASTER..SPT_VALUES T2 ON 1 = 1
                 INNER JOIN MASTER..SPT_VALUES T3 ON 1 = 1

 

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

云顶娱乐平台 5

 5.对于操作系统平台的限制

不同的操作系统对于外部表有不同的解释和显示方式
如在Linux操作系统中创建的文件是分号分隔且每行一条记录,但该文件在Windows操作系统上打开则并非如此。
建议避免不同操作系统以及不同字符集所带来的影响

02.使用Generate Scripts生成脚本

在源数据库上右键,选择Task
-> Geneate Scripts…

云顶娱乐平台 6

配置相关信息,注意选择数据库的版本并将Script
Data设置成True。

云顶娱乐平台 7

这里需要注意,因为有100万的数据,所以导出的SQL文件就有400多M,所以用SQL
Server Management Studio是打不开的。

所以只能使用sqlcmd执行。

云顶娱乐平台 8sqlcmd语句 

C:>sqlcmd -i export.sql -d ExportDataDemo_Destination -s 192.168.21.165 -U sa -P 1234567890

用时约28分钟

 

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;
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

5.ACCESS PARAMETERS:描述如何对外部表进行访问

RECORDS关键字后定义如何识别数据行  
DELIMITED BY
‘XXX’——换行符,常用newline定义换行,并指明字符集。对于特殊的字符则需要单独定义,如特殊符号,可以使用OX’十六位值’,例如tab(/t)的十六位是9,则DELIMITEDBY0X’09’;
cr(/r)的十六位是d,那么就是DELIMITEDBY0X’0D’。
SKIP X ——跳过X行数据,有些文件中第一行是列名,需要跳过第一行,则使用SKIP
1。
FIELDS关键字后定义如何识别字段,常用的如下:
FIELDS:TERMINATED BY ‘x’——字段分割符。
ENCLOSED BY ‘x’——字段引用符,包含在此符号内的数据都当成一个字段。
例如一行数据格式如:”abc”,”a””b,””c,”。使用参数TERMINATED BY ‘,’
ENCLOSED BY
‘”‘后,系统会读到两个字段,第一个字段的值是abc,第二个字段值是a”b,”c,。
LRTRIM ——删除首尾空白字符。
MISSING FIELD VALUES ARE NULL——某些字段空缺值都设为NULL。
对于字段长度和分割符不确定且准备用作外部表文件,可以使用UltraEdit、Editplus等来进行分析测试,如果文件较大,则需要考虑将文件分割成小文件并从中提取数据进行测试。

07.结果对比

因为这里测试的环境有网络和表结构的特殊情况,不能说明所有情况下效能的差异,但是也可作为参考之用。

下面给出比较结果。

 云顶娱乐平台 9

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

 

外部表的局限性 

1.SQLLDR可以指定多少提交一次,即ROWS=?,
外部表却没有,这对于大数据量的导入有些不方例。
2.sqlldr errors表示允许错误的行数,外部表用REJECT LIMIT
UNLIMITED,这个功能上基本相同。
3.外部表的列不能指定为not nullable,这样就很难拒绝某列为空值的记录。
4.外部表不能使用continueif ,如果记录有换行的就比较难处理。

 

 

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

 

外部表的特性 

位于文件系统之中,按一定格式分割,如文本文件或者其他类型的表可以作为外部表。
对外部表的访问可以通过SQL语句来完成,而不需要先将外部表中的数据装载进数据库中。
外部数据表都是只读的,因此在外部表不能够执行DML操作,也不能创建索引。
ANALYZE语句不支持采集外部表的统计数据,应该使用DMBS_STATS包来采集外部表的统计数据。

 03.使用BCP进行导出导入

在尝试了前面两个效率低下的工具之后,我们终于开始尝试下SQL
Server中专门用于导数据的工具:BCP。

关于BCP的详细用法可以参见MSDN的帮助文档。

我们先使用BCP导出数据。

云顶娱乐平台 10

-U和-P后面分别为数据库的用户名和密码。

云顶娱乐平台 11

云顶娱乐平台,我们可以看到100万的数据导出仅用了1.8秒。

现在我们再使用BCP进行导入。

云顶娱乐平台 12

执行后发现,导入数据使用了20.8秒,还是很快的。

云顶娱乐平台 13

  • 用时1.872秒+20.810秒=22.682秒
  • 【云顶娱乐平台】[转]不同版本的SQL Server之间数据导出导入的方法及性能比较。 

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

 

2..重要参数外部表的类型

ORACLE_LOADER:定义外部表的缺省方式,只能只读方式实现文本数据的装载。
ORACLE_DATAPUMP:支持对数据的装载与卸载,数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表,也可以从内部表卸载数据作为二进制文件填充到外部表。

 04.使用SqlBulkCopy

.NET Framework
2.0中增加的SqlBulkCopy类可以进行高效的数据迁移动作,这也为代码实现数据迁移提供了接口。

并且SqlBulkCopy类提供了修改字段Mapping关系的方法ColumnMappings。

云顶娱乐平台 14云顶娱乐平台 15 使用SqlBulkCopy类进行数据迁移

  using System;
  using System.Data;
  using System.Data.SqlClient;

  namespace BulkInsert
  {
      static class Program
      {
          static void Main()
         {
             DateTime dateTimeStart = DateTime.Now;
             Console.WriteLine("Start Insert:" + dateTimeStart.ToString("HH:mm:ss fff"));
             //导入导出的数据库连接
             SqlConnection connectionDestination = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Destination;");
             SqlConnection connectionSource = new SqlConnection("Server =.; User ID=datascan; Password=DTSbsd7188228; Initial CataLog=ExportDataDemo_Source;");

             //实例化一个SqlBulkCopy
             var bulker = new SqlBulkCopy(connectionDestination) { DestinationTableName = "DEMOTABLE", BulkCopyTimeout = 600 };

             //获取源数据库的数据
             SqlCommand sqlcmd = new SqlCommand("SELECT * FROM DEMOTABLE", connectionSource);
             SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlcmd);
             DataTable dataTableSource = new DataTable();
             sqlDataAdapter.Fill(dataTableSource);

             //可以重新定义字段的Mapping关系
             //SqlBulkCopyColumnMapping sqlBulkCopyColumnMapping = new SqlBulkCopyColumnMapping("COL1", "NEW_COL1");
             //bulker.ColumnMappings.Add(sqlBulkCopyColumnMapping);
             connectionDestination.Open();
             bulker.WriteToServer(dataTableSource);
             bulker.Close();
             DateTime dateTimeEnd = DateTime.Now;
             Console.WriteLine("Insert Ending:" + dateTimeEnd.ToString("HH:mm:ss fff"));
         }
     }
 }

执行后

云顶娱乐平台 16

  • 用时14.8秒

 

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

云顶娱乐平台 17

b.创建外部表

create table ex_tb1
            (ename,job,sal,dname)
            organization external
            (type oracle_datapump default directory data_dir location('ex_tb1'))
            parallel 1
            as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;

 01.使用SQL Server Import and Export Tool

使用SQL Server Import and Export
Tool进行数据的导出,也可以在目标数据库端使用Import进行导入,这部分套件也是SSIS的一部分。

在源数据库上右键,选择Task -> Export Data

云顶娱乐平台 18

分别填写源数据库和目标数据库的连接信息。

云顶娱乐平台 19

 

云顶娱乐平台 20

 

选择“copy data from one or more tables or views”

选择需要导数据的表,并且可以编辑列的Mapping关系。

云顶娱乐平台 21

可以选择立即执行或者存储为SSIS的包,用于执行计划等其他用途。

这里我们选择立即执行。

云顶娱乐平台 22

注意导入的时候如果遇到如下的错误

Error
0xc02020f4: Data Flow Task: The column “Tel” cannot be processed because
more than one code page (936 and 1252) are specified for it.
(SQL
Server Import and Export Wizard)

是因为两边的数据库的Collation设置不一样造成的,需要设置同样的Collation。

  • 用时约1分30秒

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

 

4.删除外部表或者目录对象

一般情况下,先删除外部表,然后再删除目录对象,如果目录对象中有多个表,应删除所有表之后再删除目录对象。
如果在未删除外部表的情况下,强制删除了目录,在查询到被删除的外部表时,将收到”对象不存在”的错误信息。
查询dba_external_locations来获得当前所有的目录对象以及相关的外部表,同时会给出这些外部表所对应的操作系统文件的名字。 如果只是在数据库层面上删除外部表,并不会自动删除操作系统上的外部表文件。

06.使用RedGate的SQL Data Compare

[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
EXEC [master]..xp_cmdshell
'BCP TestDB_2005.dbo.T1 out E:T1_02.txt -c -T'
GO

外部表定义的几个重点 

 

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

 

c.验证外部表

select * from emp_new;

EMP_ID ENAME      JOB              MGR_ID    HIREDATE            SALARY     COMM       DEPT_ID
------ ---------- --------------- ---------- ------------------- ---------- ---------- ----------
  7654 MARTIN     SALESMAN        7698       1981-09-28 00:00:00 1250       0           30
  7698 BLAKE      MANAGER         7839       1981-05-01 00:00:00 1550       0           30
  7934 MILLER     CLERK           7782       1982-01-23 00:00:00 3500       0           10
  7369 SMITH      CLERK           7902       1980-12-17 00:00:00 100        0           20
  7499 ALLEN      SALESMAN        7698       1981-02-20 00:00:00 250        0           30
  7521 WARD       SALESMAN        7698       1981-02-22 00:00:00 450        0           30
  7566 JONES      MANAGER         7839       1981-04-02 00:00:00 1150       0           20

7 rows selected.

04.使用SqlBulkCopy

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

在General页,选择Owner,这里选择sa。

创建外部表 

使用CREATE TABLE语句的ORGANIZATION
EXTENERAL子句来创建外部表。外部表不分配任何盘区,因为仅仅是在数据字典中创建元数据。

05.使用Linked Server进行数据迁移

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

figure-13

1.需要先建立目录对象

在建立对象的时候,需要小心,Oracle数据库系统不会去确认这个目录是否真的存在。如果在输入这个目录对象的时候,不小心把路径写错了,那可能这个外
部表仍然可以正常建立,但是却无法查询到数据。由于建立目录对象时,缺乏这种自我检查的机制,为此在将路径赋予给这个目录对象时,需要特别的注意。另外需
要注意的是路径的大小写。在Windows操作系统中,其路径是不区分大小写的。而在Linux操作系统,这个路径需要区分大小写。故在不同的操作系统
中,建立目录对象时需要注意这个大小写的差异

06.使用RedGate的SQL Data Compare进行数据迁移

第三方的工具,有数据库结构比较的工具SQL Compare和数据比较工具SQL Data
Compare。

云顶娱乐平台 23

执行

云顶娱乐平台 24

因为也是生成INSERT的SQL执行的,所以就不做过多比较了,上面已经测试过了。

 

 

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

figure-9

a.创建系统目录以及Oracle数据目录名来建立对应关系,同时授予权限

$ mkdir -p /home/oracle/external_tb/data

create or replace directory data_dir as '/home/oracle/external_tb/data/';
grant read,write on directory data_dir to scott;

00.建立测试环境

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

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

b.创建外部表

create table emp_new(
                    emp_id number(4),
                    ename varchar2(15),
                    job varchar2(12),
                    mgr_id number(4),
                    hiredate date,
                    salary number(8),
                    comm number(8),
                    dept_id number(2)
                    )
            organization external
                    (
                    type oracle_loader
                    default directory data_dir
                    access parameters(
                                    records delimited by newline
                                    badfile 'emp_new%a_%p.bad'
                                    logfile 'emp_new%a_%p.log'
                                    fields terminated by ','
                                    optionally enclosed by '"'
                                    lrtrim missing field values are null
                                    reject rows with all null fields
                                    )
                    location ('1.txt','2.txt')
)
parallel 
reject limit unlimited;

02.使用Generate Scripts

云顶娱乐平台 25

3.在建立临时表时的相关限制

对表中字段的名称存在特殊字符的情况下,必须使用英文状态的下的双引号将该表列名称连接起来。如采用”SalseID#”。
对于列名字中特殊符号未采用双引号括起来时,会导致无法正常查询数据。
建议不用使用特殊的列标题字符
在创建外部表的时候,并没有在数据库中创建表,也不会为外部表分配任何的存储空间。
创建外部表只是在数据字典中创建了外部表的元数据,以便对应访问外部表中的数据,而不在数据库中存储外部表的数据。
简单地说,数据库存储的只是与外部文件的一种对应关系,如字段与字段的对应关系。而没有存储实际的数据。
由于存储实际数据,故无法为外部表创建索引,同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作。

05.使用Linked Server进行数据迁移

先在源数据库上对目标数据库建立Linked
Server,或者反过来也行。 

云顶娱乐平台 26云顶娱乐平台 27建立Linked Server

 EXEC sp_addlinkedserver @server = 'LinkedServerToDemo',
     @srvproduct = 'Export Data Testing', @provider = 'MSDASQL',
     @provstr = 'DRIVER={SQL Server};SERVER=192.168.21.165;UID=sa;PWD=password;'

云顶娱乐平台 28云顶娱乐平台 29是用INSERT INTO…SELECT…进行导入

  DECLARE @begin_date DATETIME
  DECLARE @end_date DATETIME
  SELECT  @begin_date = GETDATE()

  INSERT  INTO LinkedServerToDemo.ExportDataDemo_Destination.dbo.DEMOTABLE
          SELECT  *
          FROM    ExportDataDemo_Source.dbo.DEMOTABLE

  SELECT  @end_date = GETDATE()
 SELECT  DATEDIFF(ms, @begin_date, @end_date) AS '用时/毫秒' 

执行用时

云顶娱乐平台 30

  • 用时7.97分钟

 

 

3.使用外部文件数据,使用oracle_loader来填充数据来生成外部表

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

 a.准备外部数据源文件

cat /home/oracle/external_tb/data/1.txt
"7369","SMITH","CLERK","7902","17-DEC-80","100","0","20"
"7499","ALLEN","SALESMAN","7698","20-FEB-81","250","0","30"
"7521","WARD","SALESMAN","7698","22-FEB-81","450","0","30"
"7566","JONES","MANAGER","7839","02-APR-81","1150","0","20"

$ cat /home/oracle/external_tb/data/2.txt
"7654","MARTIN","SALESMAN","7698","28-SEP-81","1250","0","30"
"7698","BLAKE","MANAGER","7839","01-MAY-81","1550","0","30"
"7934","MILLER","CLERK","7782","23-JAN-82","3500","0","10"

简单的导出例子2:

c.验证外部表

select * from ex_tb1;

ENAME                       JOB           SAL  DNAME
------------------------- -------------------- ---- -------------------------
CLARK                  MANAGER              2450 ACCOUNTING
KING                     PRESIDENT             5000 ACCOUNTING
MILLER                   CLERK                 1300 ACCOUNTING
JONES                    MANAGER               2975 RESEARCH
FORD                     ANALYST               3000 RESEARCH
ADAMS                    CLERK                 1100 RESEARCH
SMITH                    CLERK                  800 RESEARCH
SCOTT                    ANALYST               3000 RESEARCH
WARD                     SALESMAN              1250 SALES
TURNER                   SALESMAN              1500 SALES
ALLEN                    SALESMAN              1600 SALES
JAMES                    CLERK                  950 SALES
BLAKE                    MANAGER               2850 SALES
MARTIN                   SALESMAN              1250 SALES

14 rows selected.

对于使用上述方式创建的外部表可以将其复制到其他路径作为外部表的原始数据来生成新的外部表,用于转移数据。

code-8

d.将外部表文件复制一个新的文件名,用以模拟到其他服务器上

$ cp /home/oracle/external_tb/data/ex_tb1 /home/oracle/external_tb/data/in_tb1

figure-6

2.由查询结果集,使用Oracle_datapump来填充数据来生成外部表

figure-2

b.获得平面文件的位置

select * from user_external_locations order by table_name;

TABLE_NAME LOCATION   DIRECTORY DIRECTORY_NAME
---------- ---------- --------- --------------------
EMP_NEW    1.txt      SYS       DATA_DIR
EMP_NEW    2.txt      SYS       DATA_DIR
EX_TB1     ex_tb1     SYS       DATA_DIR
IN_TB1     in_tb1     SYS       DATA_DIR

 

figure-5

外部表对错误的处理 

REJECT LIMIT UNLIMITED
在创建外部表时最后加入LIMIT子句,表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制
BADFILE和NOBADFILE子句
用于指定将捕获到的转换错误存放到哪个文件。如果指定了NOBADFILE则表示忽略转换期间的错误
如果未指定该参数,则系统自动在源目录下生成与外部表同名的.BAD文件BADFILE记录本次操作的结果,下次将会被覆盖
LOGFILE和NOLOGFILE子句
同样在access parameters中加入LOGFILE
‘LOG_FILE.log’子句,则所有Oracle的错误信息放入’LOG_FILE.log’中
而NOLOGFILE子句则表示不记录错误信息到log中,如忽略该子句,系统自动在源目录下生成与外部表同名的.LOG文件
注意以下几个常见的问题
1.外部表经常遇到BUFFER不足的情况,因此尽可能的增大READSIZE
2.换行符不对产生的问题。在不同的操作系统中换行符的表示方法不一样,碰到错误日志提示如是换行符问题,可以使用
UltraEdit打开,直接看十六进制
3.特定行报错时,查看带有”BAD”的日志文件,其中保存了出错的数据,用记事本打开看看那里出错,是否存在于外部表定义相冲突

3. 扩展

2.对于操作系统文件的要求

建立外部表时,必须指定操作系统文件所使用的分隔符号。并且该分隔符有且只有一个。创建外部表时,不能含有标题列。如果这个标题信息与外部表的字段类型不一致(如字段内容是number数据类型,而标题信息则是字符型数据,则在查询时就会出错)。如果数据类型恰巧一致的话,这个标题信息Oracle数据库也会当作普通记录来对待。

当Oracle数据库系统访问这个操作系统文件的时候,会在这个文件所在的目录自动创建一个日志文件。无论最后是否访问成功,这个日志文件都会如期建立。查看这个日志文件,可以了解数据库访问外部表的频率、是否成功访问等等。默认情况下,该日志在与外部表的相同directory下产生。

 

创建外部表的注意事项 

1. BCP的用法

g.创建正常的表,将外部表数据导入,这就是利用ORACLE_DATAPUMP类型的额外部表实现数据迁移

create table tb1 as select * from in_tb1;

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

a.查看外部表信息

select TABLE_NAME,TYPE_NAME,DEFAULT_DIRECTORY_NAME,REJECT_LIMIT,ACCESS_PARAMETERS from user_external_tables;

 

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

 4.外部表相关视图

 

从个人来讲,我更喜欢使用第二种跟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.

一般来说,从低版本备份的数据库可以直接在高版本的数据库中恢复的,比如SQL2000的备份可以在SQL2005或SQL2008中恢复,除非是跨度太大的之外。比如SQL2000的备份就不能直接在SQL2012中恢复,只能恢复到SQL2008,再从SQL2008备份出来,最后到SQL2012上恢复。

云顶娱乐平台 31

figure-7

前言

使用BULK
INSERT把数据导入到目标表数据。为提高性能,可临时删除索引,导完之后再重建索引等。请注意要预留足够的磁盘空间。这里大概花了15分钟导完。

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

更详细的参数,请参考:

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

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

云顶娱乐平台 32

而高版本的备份一般不能在低版本中恢复,如SQL2008的备份不能在SQL2005或SQL2000中恢复。而实际中,却又会遇到这种需求。最好是通过高版本SSMS直接连接两个不同版本的数据库,通过数据库间的数据导出导入或写脚本,把高版本的数据导到低版本的数据库中。这是比较快速安全的方法。但是如果两个版本的数据库不能相连,只能是把数据导出来,再导入。对于数据量不大来说,使用SSMS的导出导入功能,或是生成包含数据的脚本即可(下图)。对于大数据来说,却是一个灾难,如前面有2000万数据的大表,生成数据的脚本也有几个G大,直接使用SSMS执行是不可能的了。只能是使用SQLCMD实用工具,在后台执行SQL脚本,或者借助BCP、BULK
INSERT等这种大容量数据导出导入的工具。

 

由于工作关系,有时要开发一些客户的数据接口,每天自动导入比较大量的数据。限制于应用程序等因素影响,所以考虑直接使用SQL
SERVER的BULK
INSERT每天自动去读取相关目录的中间文件。尽管目录是动态的,但由于中间文件是固定格式的,通过编写动态SQL,最后封装成存储过程,放到JOB中,配置运行的计划,即可完成自动化的工作。下面简单演示下过程:

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

2.2 导入数据

使用上面介绍的用法导出数据:

figure-17

4. 总结

在Schedules页,配置执行的时间和频率等。完成。

在SQL Server Agent新建一个作业

 

 

使用完之后,可以把sp_cmdshell关闭。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO
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]"]

 

云顶娱乐平台 33

figure-8

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

 

figure-11

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

 

3.1.1 编写导入脚本

云顶娱乐平台 34

 

 

 

figure-15

云顶娱乐平台 35

figure-1

3.1.2 配置JOB

 

 

 

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

 

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

相比BCP的导入,BULK INSERT提供更灵活的选择。

BCP导入数据

 

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

 

 

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

基于安全的考虑,系统默认没有开启xp_cmdshell选项。使用下面语句开启此选项。

code-1

 

 

 

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

在SSMS上同时也可以执行:

code-5

 

figure-16

云顶娱乐平台 36

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

figure-4

介绍完BCP的导出导入,以及BULK
INSERT的导入,下面进行一些实际的操作。为了接近实际环境,创建一张10个字段的表,包含有几种常用的数据类型,构造2000万的数据,包含中文和英文。为了更快插入测试数据,先不创建索引。在执行下面代码之前,请留意下数据库的日志恢复模式是否设置为大容量模式或简单模式,以及磁盘空间是否足够(我的实践中,数据生成后数据文件和日志文件大概需要40G的空间)。

使用BULK INSERT导入数据

云顶娱乐平台 37

code-2

云顶娱乐平台 38

 

2. 实践

 

云顶娱乐平台 39

云顶娱乐平台 40

BCP几个常用的参数说明:

 

云顶娱乐平台 41

figure-14

 

2.1 导出数据

简单的导出例子1:

figure-10

figure-12

 

 

语法:

code-3

  • 具有不同架构的多个表使用同一数据文件作为数据源。

  • 数据文件中的字段数不同于目标表中的列数;例如:

    • 目标表中至少包含一个定义了默认值或允许为 NULL 的列。

    • 用户不具有对目标表的一个或多个列的 SELECT/INSERT 权限。

    • 具有不同架构的两个或多个表使用同一个数据文件。

     

  • 数据文件和表的列顺序不同。

  • 数据文件列的终止字符或前缀长度不同。

code-6

 

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

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

 

云顶娱乐平台 42

 

云顶娱乐平台 43

云顶娱乐平台 44