SqlLoader

最新推荐文章于 2022-03-10 15:43:57 发布

cjh81378066

最新推荐文章于 2022-03-10 15:43:57 发布

阅读量1k

点赞数 1

Sqlloader的步骤

1） Oracle 数据库端必须已经建好了需要导入的数据表的结构

2）存在数据源文件

3）手工编辑一个XXX.CTL 的控制文件

4）命令行加载数据

Sqlldr命令具体信息如下图

Sqlldr运行的一个具体例子

sqlldr userid=user1/123456 control=bcp1.ctl log=log/bcp1.log bad=bad/bcp1.bad discard=discard/bcp1.discard errors=100 parallel=true

解释：

Userid：表示使用哪个用户进行导入，一般使用system，然后控制文件中写明导入哪个schema下，如果使用本用户导入，这控制文件不需要指定schema，直接指定table_name就可以了

Control：表示使用本目录下的bcp1.ctl控制文件

Log：表示日志写入本目录的log目录下的bcp1.log文件

Bad：表示错误数据写入本目录的bad目录下的bcp1.bad文件

Discard：表示丢弃的数据写入本目录的discard目录下的bcp1.discard文件

Errors：表示最大容忍的错误行数，errors=all会报错，只能是数字，默认50，当错误数据行数达到这个数字时，sqlldr会终止，终止之前导入的数据会存在oracle中，不会丢失。

Parallel:表示开启并行，parallel=20会报错，只能是true或false

控制文件格式如下：

load data

CHARACTERSET AL32UTF8

infile 'tb_Factory.TXT'

into table TB_FACTORY

fields terminated by whitespace

TRAILING NULLCOLS

(column_name1,

column_name2,

column_name3,

column_name4)

或如下，不是统一的分隔字段，每个字段用不同的分隔符

load data

CHARACTERSET AL32UTF8

infile 'tb_Factory.TXT'

into table TB_FACTORY

TRAILING NULLCOLS

(column_name1 terminated by ',',

column_name2 terminated by ';',

column_name3 terminated by '|',

column_name4 terminated by x'09')

CHARACTERSET ZHS16GBK --转换为简体中文

append into table TB_FACTORY --追加到表中

replace into table TB_FACTORY --清空原表后再写入

fields terminated by '|' --竖线

fields terminated by ',' --逗号

fields terminated by whitespace --空白

fields terminated by x'09' --制表符

fields terminated by ',' optionally enclose by '"' --它指定用逗号分隔数据字段，每个字段可以用双引号括起。

TRAILING NULLCOLS必须在fields terminated by下一行，否则会报错

TRAILING NULLCOLS作用：比如案例Sqlserver表有50行，但是由于sqlserver通过bcp导出的txt文件中发现多出了最后一行为空的行，执行sqlldr时控制文件加上TRAILING NULLCOLS会发现oracle为51行，多出了一行内容为空的行，去掉TRAILING NULLCOLS再导入后发现oracle也是50行了

控制文件中如下格式会报错

(FACTORYID NVARCHAR2(50) terminated by whitespace,

PARENTID NVARCHAR2(50) terminated by whitespace)

SQL*Loader-350: Syntax error at line 5.

Expecting valid column specification, "," or ")", found "NVARCHAR2(50)".

如下也报错

(FACTORYID NVARCHAR2 terminated by whitespace,

PARENTID NVARCHAR2 terminated by whitespace)

SQL*Loader-350: Syntax error at line 5.

Expecting valid column specification, "," or ")", found "NVARCHAR2".

换成如下两种都是正常的，因为在使用控制文件时，默认数据源文件中字段是char型，除非在控制中加上转换格式，具体的转换格式必须匹配数据源文件中实际的格式

(FACTORYID terminated by whitespace,

PARENTID terminated by whitespace)

(FACTORYID char(100) terminated by whitespace,

PARENTID char(100) terminated by whitespace)

理解上面红色加粗字段意思的一个案例

比如：控制文件中的”yyyy-mm-dd hh24:mi:ss.ff9”是实际要导入的文件中的格式，不是oracle中要导入的表的字段的格式

数据源文件内容在linux中vi后如下

Oracle建表语句如下

（第三个字段recorddate和数据源文件完全不一样，源文件是秒后面9位，表字段是秒后面6位）

CREATE TABLE WATCHMAIN

(WMID NUMBER(15),MAINID NUMBER(15),RECORDDATE TIMESTAMP(6),UPLOADMODE NUMBER,UPLOADER NVARCHAR2(50))

控制文件内容如下

（控制文件中”yyyy-mm-dd hh24:mi:ss .ff9”必须和实际数据源文件的格式匹配，才能正常导入，否则报错，如改成ff6就报错了）

最后我们在sqlloader的导入日志看到如下内容

（发现sqlloader默认把所有字段都当成char型，除非进行过转换。）

一个涵盖控制文件中字段转换的案例

描述：从windows上使用sqlserver自带的导出工具把数据从sqlserver中导出并在windows上以.csv格式保存，再上传到linux上，再通过sqlloader导入oracle

Sqlserver表结构语句

CREATE TABLE table1

([A1_Datetime] [datetime] NULL,

[A2_Datetime] [datetime] NULL,

[A3_Datetime] [datetime] NULL,

[A4_num] [bit] NOT NULL,

[A5_char] [varchar](50) NULL,

[A6_char] [varchar](500) NULL,

[A7_num] [int] NULL,

[A8_num] [int] NULL)

导出说明

CR回车

LF换行

一般导出是按如下格式，列分隔符使用指标符的，则sqlloader的控制文件中使用fields terminated by x'09'

Sqlserver导出的数据命名为Table1.csv，在linux中vi的结果如下，其中在sql结果中的0变成了False

2014-11-20 13:46:26.837000000 2014-11-20 13:46:26 2014-11-20 13:46:27.120 False HB6YIMEID2 YIMEID35710406530815超过250个字符XX 7777777 8888888

Oracle中的建表语句

CREATE TABLE table1

(

A1_Datetime TIMESTAMP(6),

A2_Datetime TIMESTAMP(6),

A3_Datetime TIMESTAMP(6),

A4_num number(1) not null,

"A5_char" NVARCHAR2(50),

A6_char NVARCHAR2(500),

A7_num number(20),

A8_num number(20),

)

Sqlloader的控制文件内容如下

load data

CHARACTERSET ZHS16GBK

infile 'Table1.csv'

into table TABle1

fields terminated by x'09'

TRAILING NULLCOLS

(A1_DATATIME timestamp “yyyy-mm-dd hh24:mi:ss.ff9”,

a2_datetime timestamp “yyyy-mm-dd hh24:mi:ss”,

a3_DATAtime timestamp “yyyy-mm-dd hh24:mi:ss.ff3”,

A4_NUM "case when :a4_num='False' then 0 else 1 end",

“A5_char”,

A6_CHAR char(500),

A7_NUM,

A8_NUM)

以上控制文件完美无缺，但是再执行sqlldr后还是发现会报错，报错信息中发现只有最后一列A8_NUM报错 ORA-01722: invalid number，而第七列A7_NUM也是数字却不报错。原因就是因为最后一列在windows格式下面涵盖了回车符，这样数字+回车符就不=数字了，需要在linux中执行dos2unix Table1.csv把数据文件转换为linux格式，再执行sqlldr就正常了

以上A4_NUM中:a4_num='False'要注意大小写，要和在linux中打开的文本文件中显示的一致,当然decode也是ok的

A4_NUM "decode(:A4_NUM,'False',0,'true',1,0)"

A4_NUM "case when :A4_NUM ='False' then 0 else 1 end"

控制文件格式总结如下

1. infile后面的文件名必须严格大小写，因为是匹配linux下面的文件

2. into table后面的表名大小写随便，但是如果oracle建表是表名有双引号””,则必须加上””,且里面的大小写必须严格匹配

3. 控制文件中字段大小写随便，但是如果oracle建表时字段加了双引号””,则控制文件中字段也必须加上双引号，且双引号里面大小写严格匹配，如上面例子”A5_char”

例如建表时isshow、ISACTIVE没有双引号，则如下大小写都正常

isshow "case when :isshow='False' then 0 else 1 end",

ISACTIVE "case when :isactive='False' then 0 else 1 end"

4. 数据源文件中如果有date格式的数据，控制文件中的转换格式必须严格匹配数据源文件中的格式，而不是匹配oracle建表的表结构格式，如上面例子前三个字段A1、A2、A3

5. 当控制文件中使用到函数或case等语句时，如果要匹配具体值，则必须严格匹配数据源文件中的结果，且大小写严格，而不是匹配sqlserver中的sql查询结果，如上面例子A4

case when a4_num=’False’，而不是case when a4_num=0，且false大小写严格。

6. 当oracle建表的字段长度超过250时，控制文件中必须在字段后面加char(length)，比如nvarchar2(1000)，则必须加上char(1000)，具体原因就是因为控制文件(CTL)中默认为是char类型。所以，当该列数据长度超过255（char类型长度）时会提示出错Field in data file exceeds maximum length

7. 当oracle表的最后一个字段是number或timestamp类型时，需要把数据源文件进行dos2unix转换。否则报错如下

ORA-01722: invalid number

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

8. 最重要的一点，如何编写好控制文件，需要看两点：一查看oracle建表的字段类型，二查看linux下vi数据源文件的内容，结合两点来一起思考。虽然在linux下vi数据源文件看到是类似时间或number的数据，但是sqlloader默认把数据源文件字段都当成char型，这个时候需要转换格式。而且还需要在vi编辑器下查看文件会不会出现什么其他不一样的东西，比如文件在windows上生成，在linux下vi打开后发现第一行第一列出现feff，这就需要对文件进行格式转换，比如在windows下把文件转换成ANSI格式，再上传到linux上，保证vi后不再出现类似问题，而不是在sqlloader的控制文件中对字段进行转换。

比如sqlserver显示的sql查询结果，null显示为NULL，把sql查询结果导出生成为文件后，而linux下面把NULL当成字符型，就出现这样的情况，同样的字段不为空显示为数字、时间，为空显示为NULL，sqlloader导入时就报错了，说NULL的内容为字符型不符合为数字、时间的类型，我们就需要如下转换（说明一个字段可以同时在控制文件中编写转换成多种不同格式或结果值，但是如果是时间类型必须把字段类型和vi编辑器下数据源文件的具体格式如秒后几位小数点放在前面，即理念就是先把数据字段类型和数据源文件具体格式放前面，再写其他转换语句如case或函数，number就不需要这样了，虽然sqlloader把number当成char，oracle表结构是number，但是在linux下vi可以认出那是数字。）

Column_name1 "case when :RECORDCOUNT='NULL' then null else : Column_name1 end"

Column_name2 timestamp "yyyy-mm-dd hh24:mi:ss.ff3" "case when : Column_name2='NULL' then null else :Column_name1 end"

如果控制文件中字段有双引号，后面又有双引号的条件，则字段还需要双引号，但是要加转义字符\

"valid" "case when :\"valid\"='False' then 0 else 1 end",

"SN_DateTime" timestamp "yyyy-mm-dd hh24:mi:ss.ff3" "case when :\"SN_DateTime\"='NULL' then null else :\"SN_DateTime\" end",