dmfldr 实战

在上一章简单了解了dmfldr的各参数含义后,本章介绍如何通过设置不同参数值使用dmfldr进行不同情况的数据装载。并介绍了如何通过设置参数提高dmfldr的性能以及使用限制。

注意

本章中皆以在Linux操作系统下进行操作为例,在Windows操作 系统下目录分隔符与单引号转义的处理有些许区别,不再赘述。

3.1 dmfldr 控制文件

控制文件CONTROL是启动dmfldr必须要指定的参数,用于指定数据文件中数据的格式。在数据载入时,dmfldr根据控制文件指定的格式来解析数据文件;导出数据时,dmfldr也会根据控制文件指定的列分隔符、行分隔符等生成数据文件。

控制文件中还可以指定其他dmfldr参数值。

dmfldr控制文件的语法如下所示:

[OPTIONS(

<id>=<value>

……

		)]

LOAD [DATA]

INFILE < <file_option>|<directory_option> >

[BADFILE <path_name>]

[APPEND|REPLACE|INSERT]

<into_table_clause>

<id> ::=参数

<value> ::=值

<file_option> ::= [LIST] <path_name> [<row_term_option>] [,<path_name>
[<row_term_option>]]

<directory_option> ::= DIRECTORY <path_name> [<row_term_option>]

<path_name> ::=文件地址

<row_term_option> ::=STR [X] <delimiter>

<into_table_clause> ::= <into_table_single>{<into_table_single>}

<into_table_single> ::=INTO TABLE [<schema>.]<tablename>

						[EP <ep_option>]

						[WHEN <field_conditions>]

						[FIELDS [TERMINATED BY] [X] <delimiter>]

						[<enclosed_option>]

						[<coldef_option>]

<schema> ::=模式名

<tablename> ::=表名

<ep_option> ::=(<ep_list>)

<ep_list> ::=整型数字列表,以逗号分隔

<field_conditions> ::= <field_condition>{ AND <field_condition>}

<field_condition> ::= [(] <cmp_exp><cmp_ops><cmp_data>[)]

<cmp_exp> ::= <colid> | (p1:p2)

<cmp_ops> ::= = | <> | !=

<cmp_data> ::= [X] '<字符串常量>' | BLANKS | WHITESPACE

<delimiter> ::='<字符串常量>'

<coldef_option> ::=(<col_def>{ ,<col_def>})

<col_def>::=<col_id> [FILLER][<property_option>][<fmt_option>][<term_option>]  [<enclosed_option>][<constant_option>][<fun_option>]

<col_id> ::=列名

<property_option> ::=<position_option> | NULL

<position_option> ::=position(p1:p2) | position(p1)

<fmt_option> ::=DATE FORMAT '<时间日期格式串>'

<term_option> ::= TERMINATED [BY] <wx_option>

<wx_option> ::= WHITESPACE|[X] <delimiter>

<enclosed_option> ::= [OPTIONALLY] ENCLOSE [BY] [X] <delimiter>

<constant_option> ::= CONSTANT "<常量>"

<fun_option> ::= "函数名称()"

对于上述控制文件语法,需要说明的是:

  • dmfldr在处理数据文件中换行符时windows默认为0x0D0A(\r\n),非windows默认为0x0A(\n),用户应该根据现有的数据文件中的换行符做相应的调整。对应选项为<row_term_option>,若指定的<value>值为十六进制的字符串值需要指明[X]选项,<value>值不再需要以0x开头。若没有指明[X]选项,则<value>值为指定的字符串;

  • 关于列分隔符,用户应当指定FIELDS或者coldef_option中的至少一种。若两者均存在,则以coldef_option中的设置为准,若分隔符指明[X]选项,则表明此分隔符为十六进制格式的字符串;

  • 关于file_option,用来指定单个文件;

  • 关于directory_option,用来指定整个文件夹。指定此选项后,dmfldr会自动扫描指定文件夹下的所有文件,这些文件数据将被导入到服务器;

  • 关于LIST选项,INFILE使用LIST选项时,表明实际的数据文件路径存储在INFILE指定的文件中,该文件可以存储多个实际的数据文件路径,使用逗号或者换行分割;

  • 关于APPEND|REPLACE|INSERT选项,当dmfldr处于数据装载模式时,INSERT表示插入方式,向空表插入新记录(如果不是空表则会报错无效的装载模式);APPEND表示追加方式,为缺省方式,在表中追加新记录;REPLACE表示替代方式,先清空表再插入新记录。

    当dmfldr处于导出数据模式时,dmfldr会检查导出数据文件是否存在,若不存在则直接创建新文件;若存在,当设置为APPEND时,以追加的方式写入数据;设置为REPLACE时,先删除文件再重新创建新文件;设置为其他值时则报错。

    选项默认值为APPEND;

  • 关于OPTIONS选项,该选项支持命令行参数中除userid,control,help以外的所有参数的指定,每个参数值对使用空格或者换行分割。对于option中出现的参数,在dmfldr的指定执行参数中也出现的,dmfldr会选择option中对应参数的值执行;

  • 关于col_def,FILLER表示跳过处理数据文件中指定列的值;

  • 关于property_option选项

    • position(p1:p2):从数据文件中每行数据的第p1个字节到第p2个字节为该列值,包含边界p1,p2;
    • position(p1):从数据文件中每行数据的第p1个字节开始,到下一个列分隔符之间的数据为该列值,包含边界p1;
    • position选项对大字段数据无效,若对大字段类型指定此选项会报错;
    • NULL:指定的值为NULL,忽略数据文件中的值;

    property_option参数仅对导入有效;

  • 关于term_option选项,该选项用来指定数据文件中指定列的结束标志。列的结束标志可以是WHITESPACE(空格)或者用户自定义的字符串或十六进制串。指定了term_option后,该列不需要用FIELDS分隔;

  • 关于enclosed_option选项,此参数指定封闭符,为可选参数,默认不存在封闭符。若在into_table_clause和coldef_option中均设置了封闭符,则以coldef_option中的设置为准,若封闭符前指定[X]选项,则表明此封闭符为十六进制格式的字符串;

  • 分隔符或封闭符字符串的长度均不能超过255个字节;

  • 关于constant_option选项,指定constant关键字后,数据文件中不需要为该列准备数据,如果指定了,该列数据将作为下一字段数据装载而导致数据混乱。constant选项对大字段类型无效;

  • 关于fun_option选项,目前只支持trim()和replace(colname, srcStr,destStr)函数。trim()函数用于去除列数据的前后空格;replace()函数用于将colname列名指定的列数据中的srcStr替换为destStr,其中srcStr和destStr参数可使用chr(int)函数将数字转换成字符串,要求chr()的参数和返回值均不超过4个字节;

  • 关于ep_option选项,用于指定数据将要发送的目的站点,仅适用于MPP环境。

例:一个dmfldr控制文件的例子

OPTIONS

(

	SKIP = 0

	ROWS = 50000

	DIRECT = TRUE

	INDEX_OPTION = 2

)

LOAD DATA

INFILE '/opt/data/test1.txt' STR X '0A'

BADFILE '/opt/data/test1.bad'

INTO TABLE test1

FIELDS '|'

(

F1,

F2 DATE FORMAT 'YYYY-MM-DD',

F3 NULL,

F4 TERMINATED BY WHITESPACE ENCLOSE BY '(',

F5 CONSTANT "test",

F6 "trim()"

)

LOAD DATA

INFILE '/opt/data/test2.txt' STR X '0A'

BADFILE '/opt/data/test2.bad'

INTO TABLE test2

FIELDS '|'

(

C1 TERMINATED BY ' ',

C2,

C3 DATE FORMAT 'yyyy-mm-dd'

)

在这个例子中:

  • OPTIONS选项中定义了SKIP、ROWS、DIRECT和INDEX_OPTION参数

  • 有两个LOAD选项,表明有一次装载同时处理两个表,在每个LOAD选项中:

    • 指定了数据文件和数据文件的换行符,还指定了BADFILE文件
    • 指定了操作的数据库基表、列分隔符以及列定义

3.2 指定数据文件

当dmfldr工作在IN模式时,从数据文件中读取数据并载入数据库;当工作在OUT模式时,从数据库中将指定数据导出到数据文件。

数据文件通常为文本文件,列与列之间由列分隔符隔开,行与行之间由行分隔符隔开。数据文件中的列分隔符和行分隔符由用户指定,并在控制文件中设置为与数据文件中的一致。

3.2.1 在控制文件中指定数据文件

可以在控制文件的LOAD节点中指定数据文件。

例如:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 INT,C3 DATE);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
1 1|2015-11-06

2 2|2015-11-05

3 3|2015-11_04
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1 TERMINATED BY ' ',

C2,

C3 DATE FORMAT 'yyyy-mm-dd'

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'

3.2.2 使用DATA参数指定数据文件

也可以使用DATA参数指定dmfldr的数据文件,数据文件路径的优先选择顺序为先控制文件,后参数选项。如果控制文件中数据文件路径指定为‘*’,在命令行通过DATA参数指定数据文件路径,DATA所指定的文件路径会替换‘*’。

例如:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 INT,C3 DATE);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
1 1|2015-11-06

2 2|2015-11-05

3 3|2015-11_04
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE *

INTO TABLE test

FIELDS '|'

(

C1 TERMINATED BY ' ',

C2,

C3 DATE FORMAT 'yyyy-mm-dd'

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\' data=\'/opt/data/test.txt \'

3.3 数据转换与错误数据文件

dmfldr使用的数据文件都是文本格式的,其中的列值都是以字符串的方式保存在数据文件中。要想将这些数据载入数据库表中,需要将字符串转换成数据库表各列对应的数据类型。dmfldr支持所有DM数据库支持的列定义类型,包括字符串、数值、时间日期、时间日期间隔、大字段类型等。

若数据文件的编码方式与DM数据库服务器的编码方式不一样,dmfldr还需要进行字符编码的转换。dmfldr支持UTF8、GBK和GB18030编码之间的相互转换。

数据类型和编码转换工作由dmfldr客户端进行,在这个过程中如果出现错误,dmfldr会跳过该行继续后面的工作,并记录错误行到BADFILE指定的文件。常见的出错的情况有以下几种:

  • 编码转换失败
  • 目标列为字符串类型时,数据长度大于列定义长度
  • 目标列为数值类型时,数据包含非法字符或者转换后超出该数值的范围
  • 目标列为日期类型时,dmfldr默认按yyyy-mm-dd hh:mi:ss的格式解析,如果数据不是这样的格式,需要指定对应列的时间日期fmt格式而未指定

dmfldr错误数据的文件路径由BADFILE参数设置,默认的错误文件名为fldr.bad。用户也可以通过设置控制文件中的OPTIONS选项来指定错误数据文件的路径,同时也可以在控制文件的LOAD节点中指定错误数据文件的路径。错误数据文件路径最终值的优先选择顺序为LOAD节点选项,OPTIONS选项,参数选项。用户可以同时对三种设置方式中的一个或多个设置,但最终的值只取一个。BADFILE仅作用于dmfldr的工作MODE为IN的情况下,MODE为OUT时无效。

当数据类型和编码转换中存在错误数据,而错误数在允许的最大错误数范围内时,dmfldr会将出错的数据记录到错误数据文件中。文件记录的信息为执行程序、时间、目标表、数据文件中存在格式错误的行数据以及转换出错的行数据。

允许的最大容错个数由ERRORS选项设置,取值范围为 [0,4294967295]之间的整数,默认为100。当dmfldr客户端在数据类型和编码转换过程中出现的错误个数超过了ERRORS所设置的数目,dmfldr会停止载入,当前时间点的所有正确数据将会被提交到服务器端。如果载入过程中不允许出现错误则可以将ERRORS设置为0;如果允许所有的错误出现,则可以将ERRORS设置为一个非常大的数。

例如:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 INT,C3 DATE);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
1 1|2015-11-06

2 2|2015-11-05

3 3|2015-11_04

44|aaaa-bbb-ccc
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1 TERMINATED BY ' ',

C2,

C3 DATE FORMAT 'yyyy-mm-dd'

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'
badfile=\'/opt/data/test.bad\'
  1. 查看错误数据文件/opt/data/test.bad,其内容如下
dmfldr: 2015-11-09 16:56:52 SYSDBA->TEST 4|4 aaaa-bbb-ccc

3.4 服务器端错误数据处理

dmfldr客户端将载入的数据进行数据转换和编码转换后,将转换正确的数据发往DM服务器的dmfldr模块,也就是dmfldr的服务器端,由其进行真正的数据载入工作。

dmfldr客户端每次向服务器端发送一批数据,在服务器端插入数据的过程中,由于目的表上可能存在约束等原因,导致某些数据无法插入成功,此时服务器端会将这一批数据全部回滚,并将这批数据全部记为错误数据,但服务器端插入时的错误数据并不会记录到BADFILE中。

ERRORS所统计的错误包含在数据转换和数据插入过程中所产生的数据错误,因此当服务器端插入数据记录的错误数据数加上客户端数据转换时的错误数据数超过ERRORS参数的指定值时,dmfldr服务器会停止插入数据。

3.5 大字段数据处理

dmfldr支持对DM数据库的大字段类型数据的载入和导出,DM数据库支持的大字段数据类型有TEXT、LONGVARCHAR、IMAGE、LONGVARBINARY、BLOB以及CLOB。

3.5.1 大字段数据的导出

当dmfldr工作在导出模式即MODE为OUT时,dmfldr生成大字段对应的数据文件名由LOB_FILE_NAME指定,若未指定默认为dmfldr.lob,文件存放于LOB_DIRECTORY指定的目录,如果未指定LOB_DIRECTORY则存放于指定的导出数据文件同一目录。

例如:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 BLOB,C3 CLOB);
  1. 插入数据
INSERT INTO TEST VALUES(1,0XAB121032DE,'abcdefg');

INSERT INTO TEST VALUES(2,0XAB121032DE,'abcdefg');

COMMIT;
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2,

C3

)
  1. 使用dmfldr进行导出数据
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'
LOB_DIRECTORY=\'/opt/data/\' mode=\'out\'

在这个例子中,指定了LOB_DIRECTORY,而没有指定LOB_FILE_NAME,导出的大字段数据文件将存放在LOB_DIRECTORY指定的/opt/data目录,文件名为dmfldr.lob。

3.5.2 DIRECT为TRUE时大字段数据的载入

当MODE为IN且DIRECT为TRUE时,此时数据载入若涉及到大字段对象,需要用户指定大字段数据文件。若CLIENT_LOB为TRUE,LOB_DIRECTORY应指定大字段数据文件所在的客户端本地目录;若CLIENT_LOB为FALSE,用户必须先把相关文件传送到DM服务器所在主库,然后使用LOB_DIRECTORY指明存放目录

大字段数据文件在数据文件中指定,可以是任意格式的文件。在数据文件中,大字段以“文件名:起始偏移:长度”的形式记录在数据文件中。指定的文件名无效时,dmfldr会报错,装载失败。对于CLOB类型字段,当指定的偏移、长度范围内带有不完整字符时,dmfldr将装载失败。

例如:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 BLOB,C3 CLOB);
  1. 编辑数据文件test.txt,存放路径为DM服务器所在主库的/opt/data/test.txt,文件内容如下
1|testblob.txt:0:10|testclob.txt:0:10

2|testblob.txt:10:20|testclob.txt:10:20

3|testblob.txt:20:30|testclob.txt:20:30

其中,testblob.txt、testclob.txt为文本文件,长度大于30字节,存放路径为/opt/data。

  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2,

C3

)
  1. 使用dmfldr进行导入数据
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'
LOB_DIRECTORY=\'/opt/data/\'

3.5.3 DIRECT为FALSE时大字段数据的载入

当MODE为IN且DIRECT为FALSE时,数据文件中大字段列数据即字段内容。BLOB_TYPE参数指定BLOB列内容为十六进制或者字符串:

  • BLOB_TYPE为HEX_CHAR时,数据文件中BLOB列当作为十六进制内容;
  • BLOB_TYPE为HEX时,数据文件中BLOB列为字符串形式内容,导入后会转换为十六进制。

BLOB_TYPE参数只对DIRECT为FALSE时有效,默认为HEX_CHAR。

例1:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 BLOB,C3 CLOB);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
1|0x12d3c8a7|abcdefg

2|0x12a4cbac|hijlkmn

3|0x22d3c8b3|adefhjd
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2,

C3

)
  1. 使用dmfldr进行导入数据,BLOB_TYPE为HEX_CHAR
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'
direct=false blob_type=\'hex_char\'
  1. 查询表数据
SELECT * FROM TEST;

行号 C1 C2 C3

---------- ----------- ---------- -------

1 1 0x12D3C8A7 abcdefg

2 2 0x12A4CBAC hijlkmn

3 3 0x22D3C8B3 adefhjd

例2:

步骤1)、2)、3)同例1

  1. 使用dmfldr进行导入数据,BLOB_TYPE为HEX
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'
direct=false blob_type=\'hex\'
  1. 查询表数据
select * from test;

行号 C1 C2 C3

---------- ----------- ---------------------- -------

1 1 0x30783132643363386137 abcdefg

2 2 0x30783132613463626163 hijlkmn

3 3 0x30783232643363386233 adefhjd

3.6 日志文件及日志信息

dmfldr的日志文件路径由LOG参数设置,默认日志文件名为fldr.log。文件记录了装载过程中的装载信息和错误信息以及统计信息。用户也可以通过设置控制文件中的OPTIONS选项来指定日志路径。如果参数及OPTION中同时指定了日志路径则其将以OPTION中指定的路径为最终路径。

例如:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 INT,C3 DATE);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
1|1 2018-11-06

2|2 2018-11-05

3|3 2018-11_04
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2 TERMINATED BY ' ',

C3 DATE FORMAT 'yyyy-mm-dd'

)
  1. 使用dmfldr进行数据载入。
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'
log=\'/opt/data/test.log\'
  1. 查看日志文件/opt/data/test.log,其内容如下:
dmfldr: 2018-12-03 13:46:33 dmfldr:

Copyright (c) 2011, 2015, Dameng. All rights reserved.

控制文件:

加载行数:全部

每次提交服务器行数:50000

跳过行数:0

允许错误数:100

是否直接加载:Yes

是否插入自增列:No

数据是否已按照聚集索引排序:No

字符集:GBK

dmfldr: 2018-12-03 13:46:33

数据文件共1个:

d:\test.txt

错误文件:fldr.bad

目标表:TEST

列名 包装数据类型 终止

C1 CHARACTER |

C2 CHARACTER WHT

C3 yyyy-mm-dd |

行缓冲区数量: 4

任务线程数量: 4

dmfldr: 2018-12-03 13:46:33

目标表:TEST

3 行加载成功。

由于数据格式错误,0行 丢弃。

由于数据错误,0行 没有加载。

跳过的逻辑记录总数:0

读取的逻辑记录总数:3

拒绝的逻辑记录总数:0

用时:20.522(ms)

3.7 自增列装载

自增列是比较特殊的列,为了保证数据库中自增列列值的正确性,用户在进行数据载入时需要特别注意。

当DIRECT参数为FALSE时,dmfldr将把从数据文件中读取的自增列值作为目标值插入数据库表中,用户应当保证每一行的自增列的值符合自增列的规则,否则将造成数据混乱。

当DIRECT参数为TRUE时,dmfldr提供了SET_IDENTITY参数(默认为FALSE)对数据载入时自增列的处理进行设置:

  • 如果指定SET_IDENTITY选项值为TRUE,则dmfldr将把从数据文件中读取的自增列值作为目标值插入数据库表中,用户应当保证每一行的自增列的值符合自增列的规则,否则将造成数据混乱;
  • 如果SET_IDENTITY选项值设置为FALSE,则dmfldr将忽略数据文件中对应自增列的值,服务器将根据自增列定义和表中已有数据自动生成自增列的值插入每一行的对应列。

例1:

  1. 建表TEST,并插入两行数据
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT IDENTITY(1,1),C2 VARCHAR);

INSERT INTO TEST(C2) VALUES('AAA');

INSERT INTO TEST(C2) VALUES('BBB');

COMMIT;

此时表TEST中的数据为:

行号 C1 C2

---------- ----------- ---

1 1 AAA

2 2 BBB
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
2|aaa

3|bbb

4|ccc
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'
direct=true set_identity=false
  1. 查看表TEST的数据
SELECT * FROM TEST;

行号 C1 C2

---------- ----------- ---

1 1 AAA

2 2 BBB

3 3 aaa

4 4 bbb

5 5 ccc

在这个例子中,表TEST中已有两行数据,由于SET_IDENTITY置为FALSE,因此在数据载入时dmfldr根据C1列的定义和表中已有数据,为C1列重新插入合适的值。

我们再看看如果将SET_IDENTITY置为TRUE结果会怎样。

例2:

重复例1的1)、2)、3)步骤

  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236  control=\'/opt/data/test.ctl\'  direct=true set_identity=true
  1. 查看表TEST的数据
SELECT * FROM TEST;

行号 C1 C2

---------- ----------- ---

1 1 AAA

2 2 BBB

3 2 aaa

4 3 bbb

5 4 ccc

3.8 数据排序

SORTED参数用来设置数据是否已经按照聚集索引排序,默认为FALSE。

如果设置为TRUE,则用户必须保证数据已按照聚集索引排序完成,并且如果表中存在数据,需要插入的数据索引值要比表中数据的索引值大,服务器在做插入操作时顺序进行插入。若数据并未按照索引排序,则dmfldr会报错,装载失败。

如果设置为FALSE,则服务器对于每条记录进行定位插入。

用户也可以通过设置控制文件中的OPTIONS选项来设置SORTED的值。SORTED参数值的优先选择顺序为OPTIONS选项,参数选项。此参数为可选参数,作用于MODE为IN且DIRECT为TRUE的情况下,对于其他情况此参数无效。

在数据量大,并且确定数据已按照聚集索引排序完成的情况下,将SORTED参数设置为TRUE,可以提升装载性能。

例如:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT CLUSTER PRIMARY KEY,C2 VARCHAR);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
2|aaa

3|bbb

4|ccc

5|ddd

1|zzz
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\' sorted=true

由于本例中数据文件中的数据并没有按照C1列排序,dmfldr将会报错。

不能使用NOSORT选项,数据非有序

3.9 空值处理

dmfldr通过设置NULL_MODE参数来处理空值。

  • 设置为TRUE,载入时NULL字符串处理为NULL,载出时空值处理为NULL字符串
  • 设置为FALSE,载入时NULL字符串处理为字符串,载出时空值处理为空串

例1:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 VARCHAR);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
1|aaa

2|NULL

3|null
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'  null_mode=true
  1. 查看表TEST的数据,数据文件中C2列的“NULL”和“null”字符串都被处理为空值
SELECT C1, IFNULL(C2, 'NULL VALUE') FROM TEST;

行号 C1 IFNULL(C2,'NULLVALUE')

---------- ----------- ----------------------

1 1 aaa

2 2 NULL VALUE

3 3 NULL VALUE

例2:

步骤1)、2)、3)与例1相同

  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'  null_mode=false
  1. 查看表TEST的数据,数据文件中C2列的“NULL”和“null”字符串直接被作为字符串值插入表中
SELECT C1, IFNULL(C2, 'NULL VALUE') FROM TEST;

行号 C1 IFNULL(C2,'NULLVALUE')

---------- ----------- ----------------------

1 1 aaa

2 2 NULL

3 3 null

3.10 类类型装载

dmfldr支持对CLASS类型数据的装载。CLASS类型装载和LOB类型一样,载入时需要指定LOB_DIRECTORY,另外需要指定ENABLE_CLASS_TYPE为TRUE;导出时,默认导出目录和数据文件所在目录一致,导出类类型形成的大字段文件默认为dmfldr.lob,导出目录和大字段文件可通过LOB_DIRECTORY和LOB_FILE_NAME参数设置。

注意

CLASS类型大字段数据文件无法通过手动创建,只有从表中导出到大字段文件中,并使用数据文件CLASS字段对应的偏移、字长进行导入。

例如:

  1. 建表TEST

数据准备,创建类mycls的类头与类体,之后创建表TEST,其C2列类型为mycls类类型,并向TEST中插入两行数据。

--类头创建

CREATE CLASS mycls

AS

TYPE rec_type IS RECORD (c1 INT, c2 INT); 	--类型声明

id INT; 									--成员变量

r rec_type; 								--成员变量

FUNCTION f1(a INT, b INT) RETURN rec_type; 	--成员函数

FUNCTION mycls(id INT , r_c1 INT, r_c2 INT) RETURN mycls;--用户自定义构造函数

END;

/

--类体创建

CREATE OR REPLACE CLASS BODY mycls

AS

FUNCTION f1(a INT, b INT) RETURN rec_type

AS

BEGIN

	r.c1 = a;

	r.c2 = b;

	RETURN r;

END;

FUNCTION mycls(id INT, r_c1 INT, r_c2 INT) RETURN mycls

AS

BEGIN

	this.id = id; 						--可以使用this.来访问自身的成员

	r.c1 = r_c1; 						--this也可以省略

	r.c2 = r_c2;

	RETURN this; 						--使用return this 返回本对象

END;

END;

/

--建表TEST

DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 mycls);

--插入数据:

INSERT INTO TEST VALUES(1,mycls(1,1,1));

INSERT INTO TEST VALUES(2,mycls(2,2,2));

COMMIT;
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)
  1. 使用dmfldr进行数据导出
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\' mode=\'out\' lob_directory=\'/opt/data\'
  1. 在指定的LOB_DIRECTORY目录会生成大字段文件dmfldr.lob,在指定的数据文件路径生成的数据文件内容如下:
1|dmfldr.lob:0:70

2|dmfldr.lob:70:70
  1. 创建一张新表TEST2,表的两列都为mycls类类型
CREATE TABLE TEST2(C1 MYCLS,C2 MYCLS);
  1. 编辑数据文件test2.txt,存放路径为/opt/data/test2.txt,文件内容如下:
dmfldr.lob:70:70|dmfldr.lob:0:70

dmfldr.lob:0:70|dmfldr.lob:70:70
  1. 编辑控制文件test2.ctl,存放路径为/opt/data/test2.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test2.txt'

INTO TABLE test2

FIELDS '|'

(

C1,

C2

)
  1. 使用dmfldr进行数据导入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test2.ctl\' 
lob_directory=\'/opt/data/\' enable_class_type=true
  1. 查看表TEST2的数据
行号 C1 C2

---------- ----------------------------- -----------------------------

1 SYSDBA.MYCLS(2,REC_TYPE(2,2)) SYSDBA.MYCLS(1,REC_TYPE(1,1))

2 SYSDBA.MYCLS(1,REC_TYPE(1,1)) SYSDBA.MYCLS(2,REC_TYPE(2,2))

3.11 条件过滤

通过在控制文件中指定WHEN<field_conditions>子句,可以在装载过程中对数据进行过滤,符合field_conditions条件的数据才会被装载。

对于条件过滤的使用需注意以下几点:

  • 判断条件中的操作符仅支持比较相等和不相等,即=、!=和<>这三个比较操作符;
  • 目前仅支持使用AND连接多个过滤条件;
  • BLANKS和WHITESPACE表示若干个空格;
  • 判断条件若使用(p1:p2)作为比较表达式,其意义与在POSTION子句中的意义相同,表示从该行指定位置获取数据进行比较,起始位置和结束位置表示的都是字节位置,包含边界p1,p2;
  • 如果判断条件中使用colid作为比较表达式,该列必须在INTO表的coldef_option中进行说明;
  • 如果判断条件中使用colid作为比较表达式,判断条件中使用的列仅用于过滤,并没有对应表中的某个实际列,应在col_def中指明FILLER属性表示装载时跳过该列;
  • 如果判断条件中比较数据是字符常量值,其长度小于比较表达式长度,则在其之后补充空格;如果判断条件中比较数据是二进制串常量,其长度小于比较表达式长度,则在之后补充0。

例如:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 INT);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
12

23

32

48

91
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

WHEN C1 != '2'

(

C1 position (1:1),

C2 position (2:2)

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'
  1. 查看表TEST的数据,可以看到数据文件中的行2,3被过滤掉了。
SELECT * FROM TEST;

行号 C1 C2

---------- ----------- -----------

1 1 2

2 3 2

3 4 8

4 9 1

3.12 多表装载

通过在控制文件中指定多个INTO TABLE子句,可以将一批数据同时向多个表进行装载。每个INTO TABLE子句中都可以指定WHEN过滤条件、FIELDS子句和列定义子句。

对于多表装载的使用需注意以下几点:

  • 每个INTO TABLE子句的目标表必须是不同的表;
  • 多表装载时不支持直接装载分区表子表;
  • 对于第二个及其之后的INTO TABLE子句,在其coldef_option中,必须为第一列指定POSITION选项;

例如:

  1. 建表TEST1、TEST2
DROP TABLE TEST1;

DROP TABLE TEST2;

CREATE TABLE TEST1(C1 INT,C2 INT);

CREATE TABLE TEST2(C1 INT,C2 INT);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
1,2

2,3

3,2

4,8

9,1
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test1

WHEN C1 != '1'

FIELDS ','

(

c1 position (1:1),

c2

)

INTO TABLE test2

WHEN (3:3) = '2' AND c1 != '3'

FIELDS ','

(

c1 position (1:1),

c2

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\'
  1. 查看表TEST1和TEST2的数据如下
SELECT * FROM TEST1;

行号 C1 C2

---------- ----------- -----------

1 2 3

2 3 2

3 4 8

4 9 1

SELECT * FROM TEST2;

行号 C1 C2

---------- ----------- -----------

1 1 2

3.13 个性化设置

用户通过设置dmfldr的SKIP、LOAD、ROWS参数,可以根据自己的需求调整装载的起始行、装载最大行数以及每次提交的行数。

SKIP参数用来设置跳过数据文件起始的逻辑行数,整形数值。默认的跳过起始行数为0行。如果用户指定了多个文件,且起始文件中的行数不足SKIP所指定的行数,则dmfldr工具会扫描下一个文件直至累加的行数等于SKIP所设置的行数或者所有文件都已扫描结束。

LOAD参数用来设置装载的最大行数,整形数值。默认的最大装载行数为数据文件中的所有行数。LOAD指定的值不包括SKIP指定的跳过的行数。

ROWS参数用来设置每次提交的行数,整形数值。默认的提交行数为50000行。提交行数的值表示提交到服务器的行数,并不一定代表按照数据文件中的数据顺序的行数。用户可以根据实际情况调整每次提交的行数,以达到性能的最佳点。ROWS参数作用于MODE为IN的情况下,当MODE为OUT时无效。

例如:

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 VARCHAR);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
1|aaa

2|bbb

3|ccc

4|ddd

5|eee

6|fff

7|ggg

8|hhh

9|iii

10|jjj
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\' skip=3 load=5
  1. 查看表TEST的数据,载入时跳过了数据文件的前3行数据,且只载入了5条数据。
SELECT * FROM TEST;

行号 C1 C2

---------- ----------- ---

1 4 ddd

2 5 eee

3 6 fff

4 7 ggg

5 8 hhh

3.14 主备切换时的数据继续载入

在DM数据守护主备环境下,如果dmfldr数据载入中发生主备切换,dmfldr支持主备切换完成后自动继续装载数据,并且能保持数据正确。

要使用此项功能,USERID参数需要使用主备服务名方式进行配置,如:

dmfldr USERID=SYSDBA/SYSDBA@dw CONTROL='c:\fldr.ctl'

同时在dm_svc.conf配置文件中配置主备服务名,如:

dw=(192.168.0.101:5236, 192.168.0.102:5236)

目前主备切换时的数据继续载入功能还存在以下功能限制:

  • 目前仅支持单机的主备,不支持MPP主备。

但是,若在MPP主备环境中使用MPP_CLIENT=FALSE,等同于单机,也是支持的。

  • 目前不支持分区表装载

3.15 MPP本地分发

MPP_CLIENT参数用来设置在DM MPP环境下使用dmfldr进行数据装载时的数据分发方式。

当DM数据库服务器环境为单站点时此参数无效。当服务器环境为MPP环境时,若MPP_CLIENT为TRUE,dmfldr采用客户端分发模式;若MPP_CLIENT为FALSE,则采用本地分发模式。

客户端分发模式下,数据在dmfldr客户端分发好后直接往指定站点发送数据。

本地分发模式下,导入时,dmfldr直接将数据发送到连接的站点,数据最终在连接的站点;导出时,dmfldr只导出连接站点的数据。

MPP环境下要配置dmmal.ini文件中的MAL_INST_HOST和MAL_INST_PORT参数。

例如:

当前DM数据库服务器环境为MPP环境,两个节点,SEQNO号分别为0、1。使用dmfldr采用本地分发方式进行数据装载,连接的节点为SEQNO为0的节点。

  1. 建表TEST
DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 INT);
  1. 编辑数据文件test.txt,存放路径为/opt/data/test.txt,文件内容如下
1|1

2|2

3|3

4|4

5|5

6|6

7|7

8|8

9|9

10|10
  1. 编辑控制文件test.ctl,存放路径为/opt/data/test.ctl,内容如下:
LOAD DATA

INFILE '/opt/data/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)
  1. 使用dmfldr进行数据载入
./dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/opt/data/test.ctl\' mpp_client=false
  1. 查询SEQNO为0的节点上TEST表的数据
SELECT * FROM TEST WHERE SF_GET_EP_SEQNO(ROWID)=0;

行号 C1 C2

---------- ----------- -----------

1 1 1

2 2 2

3 3 3

4 4 4

5 5 5

6 6 6

7 7 7

8 8 8

9 9 9

10 10 10
  1. 查询SEQNO为1的节点上TEST表的数据
SELECT * FROM TEST WHERE SF_GET_EP_SEQNO(ROWID)=0;

未选定行

3.16 提升dmfldr性能

用户在使用dmfldr时根据系统和数据的具体情况对一些参数进行调整,可以获得更好的性能,这些参数包括:

  • BUFFER_NODE_SIZE

BUFFER_NODE_SIZE设置读取文件缓冲区页大小,值越大,缓冲区的页越大,每次读取的数据就越多,每次发送到服务器的数据也就越多,效率越高。但其大小受dmfldr客户端内存大小限制。

  • READ_ROWS

在某些情况下,BUFFER_NODE_SIZE读入的数据行数很大,而后续操作处理不了这么大的行数,此时可以用READ_ROWS来限制处理的行数。dmfldr取READ_ROWS和BUFFER_NODE_SIZE中较小的值作为一次处理的行数。

  • SEND_NODE_NUMBER

指定dmfldr在数据载入时发送节点的个数,默认由系统计算一个初始值。若在数据载入时发现发送节点不够用,系统会动态增加分配。在系统内存足够的情况下,可以适当设大SEND_NODE_NUMBER值,提升dmfldr载入性能。

  • TASK_THREAD_NUMBER

指定dmfldr在数据载入时处理用户数据的线程数目。默认情况下,dmfldr将该参数值设为系统CPU的个数,但当CPU个数大于8时,默认值都被置为8。在dmfldr客户端所在机器CPU大于8环境中,提高TASK_THREAD_NUMBER值可以提升dmfldr装载性能。

  • BLDR_NUM

水平分区表装载时,指定服务器BLDR的最大个数,默认为64。

服务器的BLDR保存水平分区子表相关信息,BLDR_NUM的设置也就指定了服务器能同时载入的水平分区子表的个数。若BLDR_NUM设置太大,当水平分区子表数过多时,可能会导致服务器内存不足。当载入时实际需要的BLDR个数超出BLDR_NUM设置时,会淘汰指定子表的BLDR,并替换为新的子表BLDR。

  • BDTA_SIZE

BDTA(Batch Data)的大小,默认为5000。

BDTA代表DM数据库批量数据处理机制中一个批量,在内存、CPU允许的条件下,增大BDTA_SIZE能加快装载速度;在网络是装载性能瓶颈时,增大BDTA_SIZE影响不大。

  • INDEX_OPTION

索引的设置选项,默认为1。INDEX_OPTION的可选项有1、2和3。

1代表服务器装载数据时先不刷新二级索引,而是将新数据按照索引预先排序,在装载完成后,再将排好序的数据插入索引。如果在数据载入前,目标表中已有较多数据,建议INDEX_OPTION置为1。

2代表服务器在快速装载过程中不刷新二级索引数据,只在装载完成时重建所有二级索引。如果在数据载入前,目标表中没有数据或数据量较小,建议INDEX_OPTION置为2。

3代表服务器使用追加模式来进行二级索引的插入, 在数据装载的过程中,
同时进行二级索引的插入, 当原有数据量远大于插入数据量时, 建议INDEX_OPTION置为3。

3.17 dmfldr使用限制

dmfldr的使用存在以下一些限制:

  • 不支持向临时表、外部表装载数据
  • 不支持向系统表装载数据
  • 不支持向带有位图索引的表装载数据
  • 不支持向带有函数索引的表装载数据
  • 不支持向带有全文索引的表装载数据
  • 不支持向带有全局索引的表装载数据
  • 不支持向DCP代理装载数据
  • dmfldr装载时,对约束进行检查,对各种约束的处理机制如下表所示
表3.1 dmfldr的约束检查机制
约束 数据不满足时 数据插入与否 约束是否有效
非空约束(NOT NULL) 报错 不插入 有效
聚集索引(CLUSTER PRIMARY KEY) 报错 不插入 有效
唯一约束(UNIQUE, PRIMARY KEY) 报错 插入 失效
引用约束(FOREIGN KEY) 不报错 插入 有效
CHECK约束(CHECK) 不报错 插入 有效
微信扫码
分享文档
扫一扫
联系客服