达梦数据库dmfldr 实战

dmfldr 使用限制

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

1.不支持向临时表、外部表装载数据

2.不支持向系统表装载数据

3.不支持向带有位图索引的表装载数据

4.不支持向带有函数索引的表装载数据

5.不支持向带有全文索引的表装载数据

6.不支持向 DCP 代理装载数据

7.dmfldr 装载时，对约束进行检查，对各种约束的处理机制如下表所示

约束 数据不满足时 数据插入与否 约束是否有效

非空约束(NOT NULL) 报错 不插入 有效

聚集索引(CLUSTER PRIMARY KEY) 报错 不插入 有效

唯一约束(UNIQUE， PRIMARY KEY) 报错 插入 失效

引用约束(FOREIGN KEY) 不报错 插入 有效

CHECK 约束(CHECK) 不报错 插入 有效

创建实例

控制文件案例解读

OPTIONS

(

SKIP = 0 --初始忽略逻辑行数 (0)

ROWS = 50000 --提交频次 (50000), DIRECT 为 FALSE 有效

DIRECT = TRUE --是否使用快速方式装载 (TRUE)

INDEX_OPTION = 2 --2 不刷新二级索引，数据装载完成后重建所有二级索引，一般是表中没有数据或数据很小时

)

LOAD DATA

INFILE '/dm/dmdata/test1.txt' STR X '0A' --指定数据文件路径,dmfldr 在处理数据文件中换行符时 windows 默认为 0x0D0A(\r\n)，非windows 默认为 0x0A(\n),若没有指明[X]选项，则<value>值为指定的字符串

BADFILE '/dm/dmdata/test1.bad' --记录错误数据的文件路径，字符串类型

INTO TABLE test1

FIELDS '|' --指定列分割符

(

F1,

F2 DATE FORMAT 'YYYY-MM-DD', --指定该列数据为时间日期格式串

F3 NULL, --指定的值为 NULL，忽略数据文件中的值

F4 TERMINATED BY WHITESPACE ENCLOSE BY '(', --列的结束标志可以是 WHITESPACE（空格）或者用户自定义的字符串或十六进制串,指定了 term_option 后，该列不需要用 FIELDS 分隔

F5 CONSTANT "test", --指定 constant 关键字后，数据文件中不需要为该列准备数据

F6 "trim()" --trim()函数用于去除列数据的前后空格

)

LOAD DATA

INFILE '/dm/dmdata/test2.txt' STR X '0A'

BADFILE '/dm/dmdata/test2.bad'

INTO TABLE test2

FIELDS '|' --指定列分割符

(

C1 TERMINATED BY ' ', --指定列终止符号为空格

C2,

C3 "sysdate" --sysdate 函数用于将系统当前时间插入指定列，忽略数据文件中指定列的值；

)

例1，--在控制文件中指定数据文件

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

1) 建表 TEST

DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 INT,C3 DATE);

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

1 1|2015-11-06

2 2|2015-11-05

3 3|2015-11_04

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

FIELDS '|'

(

C1 TERMINATED BY ' ',

C2,

C3 DATE FORMAT 'yyyy-mm-dd'

)

4) 使用 dmfldr 进行数据载入

/dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' log=\'/dm/dmdata/test.log\'

5) 查看表 TEST 的数据

SELECT * FROM TEST;

例2 --使用 DATA 参数指定数据文件

使用 DATA 参数指定 dmfldr 的数据文件，数据文件路径的优先选择顺序为先控制文件，后参数选项。

如果控制文件中数据文件路径指定为‘*’，在命令行通过 DATA参数指定数据文件路径，DATA 所指定的文件路径会替换‘*’。

1) 建表 TEST

DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 INT,C3 DATE);

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

1 1|2015-11-06

2 2|2015-11-05

3 3|2015-11_04

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE *

INTO TABLE test

FIELDS '|'

(

C1 TERMINATED BY ' ',

C2,

C3 DATE FORMAT 'yyyy-mm-dd'

)

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' data=\'/dm/dmdata/test.txt\' log=\'/dm/dmdata/test.log\'

5) 查看表 TEST 的数据

SELECT * FROM TEST;

例3 --数据转换与错误数据文件

dmfldr 使用的数据文件都是文本格式的，其中的列值都是以字符串的方式保存在数据文件中。

要想将这些数据载入数据库表中，需要将字符串转换成数据库表各列对应的数据类型。

dmfldr 支持所有 DM 数据库支持的列定义类型，包括字符串、数值、时间日期、时间日期间隔、大字段类型等。

1) 建表 TEST

DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 INT,C3 DATE);

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

1 1|2015-11-06

2 2|2015-11-05

3 3|2015-11_04

44|aaaa-bbb-ccc

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

FIELDS '|'

(

C1 TERMINATED BY ' ',

C2,

C3 DATE FORMAT 'yyyy-mm-dd'

)

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' badfile=\'/dm/dmdata/test.bad\' log=\'/dm/dmdata/test.log\'

5) 查看错误数据文件/dm/dmdata/test.bad

cat /dm/dmdata/test.bad

dmfldr: 2022-06-22 11:07:06 SYSDBA->TEST 44|aaaa-bbb-ccc

例4 --大字段数据的导出

当 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);

2) 插入数据

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

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

COMMIT;

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2,

C3

)

4) 使用 dmfldr 进行导出数据

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' LOB_DIRECTORY=\'/dm/dmdata/\' mode=\'out\' log=\'/dm/dmdata/test.log\'

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

例5 --DIRECT 为 TRUE 时大字段数据的载入

当 MODE 为 IN 且 DIRECT 为 TRUE 时，此时数据载入若涉及到大字段对象，需要用户指定大字段数据文件。

若 CLIENT_LOB 为 TRUE，LOB_DIRECTORY 应指定大字段数据文件所在的客户端本地目录，且此时待载入的大字段长度不得超过 2G，

否则无法成功载入大字段；若 CLIENT_LOB 为 FALSE，用户必须先把相关文件传送到 DM 服务器所在主库，然后使用 LOB_DIRECTORY 指明存放目录

大字段数据文件在数据文件中指定，可以是任意格式的文件。在数据文件中，大字段以“文件名：起始偏移：长度”的形式记录在数据文件中。

指定的文件名无效时，dmfldr 会报错，装载失败。对于 CLOB 类型字段，当指定的偏移、长度范围内带有不完整字符时，dmfldr 将装载失败。

1) 建表 TEST

DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 BLOB,C3 CLOB);

2) 编辑数据文件 test.txt，存放路径为 DM 服务器所在主库的/dm/dmdata/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 字节，存放路径为/dm/dmdata。

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2,

C3

)

4) 使用 dmfldr 进行导入数据

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' LOB_DIRECTORY=\'/dm/dmdata/\' log=\'/dm/dmdata/test.log\'

5) 查看表 TEST 的数据

SELECT * FROM TEST;

例6 --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) 建表 TEST

DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 BLOB,C3 CLOB);

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

1|0x12d3c8a7|abcdefg

2|0x12a4cbac|hijlkmn

3|0x22d3c8b3|adefhjd

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2,

C3

)

4) 使用 dmfldr 进行导入数据，BLOB_TYPE 为 HEX_CHAR

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' direct=false blob_type=\'hex_char\' log=\'/dm/dmdata/test.log\'

5) 查询表数据

SELECT * FROM TEST;

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

4) 使用 dmfldr 进行导入数据，BLOB_TYPE 为 HEX

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' direct=false blob_type=\'hex\' log=\'/dm/dmdata/test.log\'

5) 查询表数据

select * from test;

行号 C1 C2 C3

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

1 1 0x30783132643363386137 abcdefg

2 2 0x30783132613463626163 hijlkmn

3 3 0x30783232643363386233 adefhjd

例7 --自增列装载

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

当 DIRECT 参数为 FALSE 时，dmfldr 将把从数据文件中读取的自增列值作为目标值插入数据库表中，

用户应当保证每一行的自增列的值符合自增列的规则，否则将造成数据混乱。

当 DIRECT 参数为 TRUE 时，dmfldr 提供了 SET_IDENTITY 参数（默认为 FALSE）对数据载入时自增列的处理进行设置：

--如果指定 SET_IDENTITY 选项值为 TRUE，则 dmfldr 将把从数据文件中读取的自增列值作为目标值插入数据库表中，用户应当保证每一行的自增列的值符合自增列的规则，否则将造成数据混乱；

--如果 SET_IDENTITY 选项值设置为 FALSE，则 dmfldr 将忽略数据文件中对应自增列的值，服务器将根据自增列定义和表中已有数据自动生成自增列的值插入每一行的对应列。

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

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

2|aaa

3|bbb

4|ccc

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' direct=true set_identity=false log=\'/dm/dmdata/test.log\'

5) 查看表 TEST 的数据

SELECT * FROM TEST;

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

--如果将 SET_IDENTITY 置为 TRUE 结果会怎样

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

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' direct=true set_identity=true log=\'/dm/dmdata/test.log\'

5) 查看表 TEST 的数据

SELECT * FROM TEST;

例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);

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

2|aaa

3|bbb

4|ccc

5|ddd

1|zzz

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' sorted=true log=\'/dm/dmdata/test.log\'

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

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

例9 --空值处理

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

--设置为 TRUE，载入时 NULL 字符串处理为 NULL，载出时空值处理为 NULL 字符串

--设置为 FALSE，载入时 NULL 字符串处理为字符串，载出时空值处理为空串

1) 建表 TEST

DROP TABLE TEST;

CREATE TABLE TEST(C1 INT,C2 VARCHAR);

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

1|aaa

2|NULL

3|NULL

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' null_mode=true log=\'/dm/dmdata/test.log\'

5) 查看表 TEST 的数据，数据文件中 C2 列的“NULL”和“null”字符串都被处理为空值

SELECT C1, IFNULL(C2, 'NULL VALUE') FROM TEST;

--设置为 FALSE

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

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' null_mode=false log=\'/dm/dmdata/test.log\'

5) 查看表 TEST 的数据，数据文件中 C2 列的“NULL”和“null”字符串直接被作为字符串值插入表中

SELECT C1, IFNULL(C2, 'NULL VALUE') FROM TEST;

例10 --条件过滤

通过在控制文件中指定 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);

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

12

23

32

48

91

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

WHEN C1 != '2'

(

C1 position (1:1),

C2 position (2:2)

)

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' log=\'/dm/dmdata/test.log\'

5) 查看表 TEST 的数据，可以看到数据文件中的行 2,3 被过滤掉了。

SELECT * FROM TEST;

例11 --多表装载

通过在控制文件中指定多个 INTO TABLE 子句，可以将一批数据同时向多个表进行装载。

每个 INTO TABLE 子句中都可以指定 WHEN 过滤条件、FIELDS 子句和列定义子句。

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

--对于第二个及其之后的 INTO TABLE 子句，在其 coldef_option 中，必须为所有列指定 POSITION 选项；

--在 INTO TABLE 子句的目标表中，如果目标表各不相同，则一个批次就可以完成装载完成（不管含有多少表，只需扫描一次数据文件即可）；

--如果含有重复的目标表，则相同的表需要分批次导入（每当遇到相同的表时，就需要再扫描一遍源文件。N 个重复的表，就需要扫描 N 次源文件）。

1) 建表 TEST1、TEST2

DROP TABLE TEST1;

DROP TABLE TEST2;

CREATE TABLE TEST1(C1 INT,C2 INT);

CREATE TABLE TEST2(C1 INT,C2 INT);

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

1,2

2,3

3,2

4,8

9,1

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test1

WHEN C1 != '1'

FIELDS ','

(

c1 position (1:1),

c2 position (3:3)

)

INTO TABLE test2

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

FIELDS ','

(

c1 position (1:1),

c2 position (3:3)

)

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' log=\'/dm/dmdata/test.log\'

5) 查看表 TEST1 和 TEST2 的数据如下

SELECT * FROM TEST1;

SELECT * FROM TEST2;

例12 --个性化设置

用户通过设置 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);

2) 编辑数据文件 test.txt，存放路径为/dm/dmdata/test.txt，文件内容如下

1|aaa

2|bbb

3|ccc

4|ddd

5|eee

6|fff

7|ggg

8|hhh

9|iii

10|jjj

3) 编辑控制文件 test.ctl，存放路径为/dm/dmdata/test.ctl，内容如下：

LOAD DATA

INFILE '/dm/dmdata/test.txt'

INTO TABLE test

FIELDS '|'

(

C1,

C2

)

4) 使用 dmfldr 进行数据载入

./dmfldr userid=SYSDBA/dameng123@172.16.10.84:5236 control=\'/dm/dmdata/test.ctl\' skip=3 load=5 log=\'/dm/dmdata/test.log\'

5) 查看表 TEST 的数据，载入时跳过了数据文件的前 3 行数据，且只载入了 5 条数据。

SELECT * FROM TEST;

点不点看你哦 ：https://eco.dameng.com/