热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
DM8快速装载工具dmfldr使用
技术分享/
文章详情
/
DM8快速装载工具dmfldr使用
赵国伟
2025/04/30
74
0
0
一、概述
1.1功能简介
DM 提供了两种形式的快速装载工具：一是 dmfldr；二是 dmldrc 和 dmldrp。用户通过使用快速装载工具能够把按照一定格式排序的文本数据以简单、快速、高效的方式载入到 DM 数据库中，或把 DM 数据库中的数据按照一定格式载出到文本文件中。
两种形式的工具，功能完全一样，区别是应用场景不同。在软硬件资源充裕的情景下，首选 dmfldr 工具，dmfldr 在一台机器上启动即可，独立完成快速装载任务，简单高效。在机器资源匮乏的极端情景下，需选择 dmldrc 和 dmldrp 工具，分别部署在两台机器上，各自占用较少的机器资源，相互配合共同完成快速装载任务，可克服机器资源不足的问题。其中，表及表的同义词支持数据载入和载出，视图及视图的同义词仅支持数据载出。
1.2系统结构
1.2.1 dmfldr 结构
dmfldr（DM Fast Loader）包含 dmfldr 客户端和 dmfldr 模块两部分。dmfldr 户端实现初始化快速装载环境、读取数据、打包数据和发送数据功能。dmfldr 功能
模块嵌入在数据库服务器中，实现装载功能。两者相互协作，共同完成 dmfldr 的各项功能。
1.2.2 dmldrc 和 dmldrp 结构
快速装载工具所在机器的软硬件资源匮乏会影响快速装载的性能。为了应对这种极端情况，DM 提供了一种轻量型的快速装载工具套装：dmldrc 和 dmldrp。Dmldrc为轻量级快速装载工具的客户端，负责初始化快速装载环境和读取数据；dmldrp 为轻量级快速装载工具的服务器，负责打包数据和发送数据。两者相互配合完成和 dmfldr 客户端一样的功能。dmldrc 和 dmldrp 可部署在不同的机器上。将快速装载工具的任务分配到两个独立的工具上，并部署在不同的机器上，可减轻机器的软硬件压力。
用户还可以根据需要，配置多个 dmldrc 同时工作。dmldrp 为每一个 dmldrc 分配一个专属子进程，专门处理该 dmldrc 读取的数据。多个 dmldrc 相互独立，互不影响。
二、Dmfldr实战
2.1 dmfldr 利用控制文件导入
控制文件 CONTROL 是用于指定数据文件路径和数据文件中数据的格式。在数据载入时，dmfldr 根据控制文件指定的格式来解析数据文件；导出数据时，dmfldr 也会根据控制文件指定的列分隔符、行分隔符等生成数据文件。
1、table1
-- 建表
create table table1(c1 int,c2 varchar(10),c3 timestamp,c4 date);
-- 待导入数据
[dmdba@dm-standalone ~]$ cat /home/dmdba/table1/1.txt
"1","aa","2015-04-21 12:00:00","2025-04-10"
"2","aa","2015-04-21 12:00:00","2025-04-10"
"3","aa","2015-04-21 12:00:00","2025-04-10"
"4","aa","2015-04-21 12:00:00","2025-04-10"
[dmdba@dm-standalone ~]$ cat /home/dmdba/table1/2.txt
"5","aa","2015-04-21 12:00:00","2025-04-10"
"6","aa","2015-04-21 12:00:00","2025-04-10"
"7","aa","2015-04-21 12:00:00","2025-04-10"
"8","aa","2015-04-21 12:00:00","2025-04-10"
2、table2
-- 建表
create table table2(c1 int,c2 varchar(10),c3 timestamp);
-- 待导入数据
[dmdba@dm-standalone ~]$ cat /home/dmdba/3.txt
"1","aa","2015-04-21 12:00:00"
"2","aa","2015-04-21 12:00:01"
"3","aa","2015-04-21 12:00:02"
"4","aa","2015-04-21 12:00:03"
"5","aa","2015-04-21 12:00:04"
3、创建控制文件
OPTIONS
(
SKIP = 0
ROWS = 50000
DIRECT = TRUE
INDEX_OPTION = 1
)
LOAD DATA
INFILE DIRECTORY '/home/dmdba/table1' STR X '0A'
BADFILE '/home/dmdba/t1.bad'
append
INTO TABLE table1
FIELDS ','
(
C1 ENCLOSE BY '"',
C2 ENCLOSE BY '"',
C3 ENCLOSE BY '"',
C4 ENCLOSE BY '"'
)
LOAD DATA
INFILE '/home/dmdba/3.txt' STR X '0A'
BADFILE '/home/dmdba/t2.bad'
append
INTO TABLE table2
FIELDS ','
(
C1 ENCLOSE BY '"',
C2 ENCLOSE BY '"',
C3 ENCLOSE BY '"'
)
4、执行导入命令
[dmdba@dm-standalone ~]$ dmfldr zgw/Zgw_1234 control=\'control.ctl\'
dmfldr V8
Control file:
Loaded rows: All
Rows per commit to server: 50000
Rows to skip: 0
Errors count allowed: 100
Whether to load direct: Yes
Whether to insert identity column: No
Whether data is sorted by cluster index: No
Character sets: UTF-8
Data file counts: 2
/home/dmdba/table1/1.txt
/home/dmdba/table1/2.txt
Error file: /home/dmdba/t1.bad
Dest table: TABLE1
Column Name Packed data type End
C1 CHARACTER ,
C2 CHARACTER ,
C3 CHARACTER ,
C4 CHARACTER ,
row buffer number is: 2
task thread number is: 2
4 rows committed
8 rows committed
Dest table: TABLE1
load success.
8 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 8
Refuse logic record counts: 0
6.551(ms) time used.
Control file:
Loaded rows: All
Rows per commit to server: 50000
Rows to skip: 0
Errors count allowed: 100
Whether to load direct: Yes
Whether to insert identity column: No
Whether data is sorted by cluster index: No
Character sets: UTF-8
Data file counts: 1
/home/dmdba/3.txt
Error file: /home/dmdba/t2.bad
Dest table: TABLE2
Column Name Packed data type End
C1 CHARACTER ,
C2 CHARACTER ,
C3 CHARACTER ,
row buffer number is: 2
task thread number is: 2
5 rows committed
Dest table: TABLE2
load success.
5 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 5
Refuse logic record counts: 0
6.499(ms) time used.
[dmdba@dm-standalone ~]$
5、查看导入结果
[dmdba@dm-standalone table1]$ disql zgw/Zgw_1234
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 5.778(ms)
disql V8
SQL> select * from table1;
LINEID C1 C2 C3 C4
---------- ----------- -- -------------------------- ----------
1 1 aa 2015-04-21 12:00:00.000000 2025-04-10
2 2 aa 2015-04-21 12:00:00.000000 2025-04-10
3 3 aa 2015-04-21 12:00:00.000000 2025-04-10
4 4 aa 2015-04-21 12:00:00.000000 2025-04-10
5 5 aa 2015-04-21 12:00:00.000000 2025-04-10
6 6 aa 2015-04-21 12:00:00.000000 2025-04-10
7 7 aa 2015-04-21 12:00:00.000000 2025-04-10
8 8 aa 2015-04-21 12:00:00.000000 2025-04-10
8 rows got
used time: 4.649(ms). Execute id is 10401.
SQL> select * from table2;
LINEID C1 C2 C3
---------- ----------- -- --------------------------
1 1 aa 2015-04-21 12:00:00.000000
2 2 aa 2015-04-21 12:00:01.000000
3 3 aa 2015-04-21 12:00:02.000000
4 4 aa 2015-04-21 12:00:03.000000
5 5 aa 2015-04-21 12:00:04.000000
used time: 0.221(ms). Execute id is 10402.
SQL>
2.2 dmfldr 利用控制文件导出
– 编写控制文件
[dmdba@dm-standalone ~]$ cat control_out.ctl
OPTIONS
(
SKIP = 0
ROWS = 50000
DIRECT = TRUE
INDEX_OPTION = 1
)
LOAD DATA
INFILE '/home/dmdba/t1_out.txt' STR X '0A'
INTO TABLE table1
FIELDS ','
(
C1 ENCLOSE BY '"',
C2 ENCLOSE BY '"',
C3 ENCLOSE BY '"',
C4 ENCLOSE BY '"'
)
LOAD DATA
INFILE '/home/dmdba/t2_out.txt' STR X '0A'
INTO TABLE table2
FIELDS ','
(
C1 ENCLOSE BY '"',
C2 ENCLOSE BY '"',
C3 ENCLOSE BY '"'
)
– 执行导出命令
[dmdba@dm-standalone ~]$ dmfldr zgw/Zgw_1234 control=\'control_out.ctl\' mode=\'out\'
dmfldr V8
8 rows is load out
export success.
8 rows exported success.
51.083(ms) time used.
5 rows is load out
export success.
5 rows exported success.
7.171(ms) time used.
[dmdba@dm-standalone ~]$
– 查看生成的数据文件
[dmdba@dm-standalone ~]$ cat t1_out.txt
"1","aa","2015-04-21 12:00:00","2025-04-10"
"2","aa","2015-04-21 12:00:00","2025-04-10"
"3","aa","2015-04-21 12:00:00","2025-04-10"
"4","aa","2015-04-21 12:00:00","2025-04-10"
"5","aa","2015-04-21 12:00:00","2025-04-10"
"6","aa","2015-04-21 12:00:00","2025-04-10"
"7","aa","2015-04-21 12:00:00","2025-04-10"
"8","aa","2015-04-21 12:00:00","2025-04-10"
[dmdba@dm-standalone ~]$ cat t2_out.txt
"1","aa","2015-04-21 12:00:00"
"2","aa","2015-04-21 12:00:01"
"3","aa","2015-04-21 12:00:02"
"4","aa","2015-04-21 12:00:03"
"5","aa","2015-04-21 12:00:04"
[dmdba@dm-standalone ~]$
2.3大字段数据导出
– 创建表插入数据
SQL> DROP TABLE IF EXISTS TEST ;
executed successfully
used time: 7.441(ms). Execute id is 10403.
SQL> CREATE TABLE TEST(C1 INT,C2 BLOB,C3 CLOB);
executed successfully
used time: 12.176(ms). Execute id is 10404.
SQL> INSERT INTO TEST VALUES(1,0XAB121032DE,'abcdefg');
affect rows 1
used time: 4.207(ms). Execute id is 10405.
SQL> INSERT INTO TEST VALUES(2,0XAB121032DE,'abcdefg');
affect rows 1
used time: 12.103(ms). Execute id is 10406.
SQL> COMMIT;
executed successfully
used time: 7.599(ms). Execute id is 10407.
SQL>
– 编辑控制文件
[dmdba@dm-standalone ~]$ vim control_lob.ctl
[dmdba@dm-standalone ~]$ cat control_lob.ctl
LOAD DATA
INFILE '/home/dmdba/t_test.txt'
INTO TABLE test
FIELDS '|'
(
C1,
C2,
C3
)
[dmdba@dm-standalone ~]$
– 使用 dmfldr 进行导出数据
[dmdba@dm-standalone ~]$ dmfldr userid=zgw/Zgw_1234 control=\'/home/dmdba/control_lob.ctl\' LOB_DIRECTORY=\'/home/dmdba/lob\' mode=\'out\'
dmfldr V8
2 rows is load out
export success.
2 rows exported success.
8.979(ms) time used.
[dmdba@dm-standalone ~]$
– 查看导出文件
[dmdba@dm-standalone ~]$ cat t_test.txt
1|dmfldr.lob:0:5|dmfldr.lob:5:7
2|dmfldr.lob:12:5|dmfldr.lob:17:7
2.4大字段数据导入
2.4.1 DIRECT 为 TRUE 时大字段数据的载入
重建表：
SQL> DROP TABLE IF EXISTS TEST ;
executed successfully
used time: 18.659(ms). Execute id is 10408.
SQL> CREATE TABLE TEST(C1 INT,C2 BLOB,C3 CLOB);
executed successfully
used time: 22.299(ms). Execute id is 10409.
SQL> select * from test;
no rows
used time: 8.140(ms). Execute id is 10410.
SQL>
编辑数据文件
[dmdba@dm-standalone ~]$ vim t_test.txt
1|dmfldr.lob:0:5|dmfldr.lob:5:7
2|dmfldr.lob:12:5|dmfldr.lob:17:7
[dmdba@dm-standalone ~]$ vim /home/dmdba/lob/dmfldr.lob
defgdefg
[dmdba@dm-standalone ~]$
- 编辑控制文件
[dmdba@dm-standalone ~]$ vim control_lob.ctl
LOAD DATA
INFILE '/home/dmdba/t_test.txt'
INTO TABLE test
FIELDS '|'
(
C1,
C2,
C3
)
使用 dmfldr 进行导入数据。
[dmdba@dm-standalone ~]$ dmfldr userid=zgw/Zgw_1234 control=\'/home/dmdba/control_lob.ctl\' LOB_DIRECTORY=\'/home/dmdba/lob\' mode=\'in\' CLIENT_LOB=TRUE DIRECT=TRUE
dmfldr V8
Control file:
Loaded rows: All
Rows per commit to server: 50000
Rows to skip: 0
Errors count allowed: 100
Whether to load direct: Yes
Whether to insert identity column: No
Whether data is sorted by cluster index: No
Character sets: UTF-8
Data file counts: 1
/home/dmdba/t_test.txt
Error file: fldr.bad
Dest table: TEST
Column Name Packed data type End
C1 CHARACTER |
C2 CHARACTER |
C3 CHARACTER |
row buffer number is: 2
task thread number is: 2
2 rows committed
Dest table: TEST
load success.
2 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 2
Refuse logic record counts: 0
39.395(ms) time used.
[dmdba@dm-standalone ~]$
– 查看数据
SQL> select * from test;
LINEID C1 C2 C3
---------- ----------- ------------ -------
1 1 0xAB121032DE abcdefg
2 2 0xAB121032DE abcdefg
used time: 0.240(ms). Execute id is 10411.
SQL>
2.4.2 DIRECT 为 FALSE时大字段数据的载入
当 MODE 为 IN 且 DIRECT 为 FALSE 时，数据文件中大字段列数据即字段内容。此时，大字段数据长度不允许超过 32KB。BLOB_TYPE 参数指定 BLOB 列内容为十六进制或者字符串：
BLOB_TYPE 为 HEX_CHAR 时，数据文件中 BLOB 列当作为十六进制内容；
BLOB_TYPE 为 HEX 时，数据文件中 BLOB 列为字符串形式内容，导入后会转换为十六进制。
BLOB_TYPE 参数只对 DIRECT 为 FALSE 时有效，默认为 HEX_CHAR。
– 建表 TEST。
SQL> DROP TABLE IF EXISTS TEST;
executed successfully
used time: 30.214(ms). Execute id is 10412.
SQL> CREATE TABLE TEST(C1 INT,C2 BLOB,C3 CLOB);
executed successfully
used time: 15.005(ms). Execute id is 10413.
SQL>
– 编辑数据文件
[dmdba@dm-standalone ~]$ vim /home/dmdba/test_lob_false.txt
1|0x12d3c8a7|abcdefg
2|0x12a4cbac|hijlkmn
3|0x22d3c8b3|adefhjd
– 编辑控制文件
[dmdba@dm-standalone ~]$ vim /home/dmdba/control_lob_false.ctl
LOAD DATA
INFILE '/home/dmdba/test_lob_false.txt'
INTO TABLE test
FIELDS '|'
(
C1,
C2,
C3
)
– 使用 dmfldr 进行导入数据，BLOB_TYPE 为 HEX_CHAR。
[dmdba@dm-standalone ~]$ dmfldr userid=zgw/Zgw_1234 control=\'/home/dmdba/control_lob_false.ctl\' direct=false blob_type=\'hex_char\'
dmfldr V8
Control file:
Loaded rows: All
Rows per commit to server: 50000
Rows to skip: 0
Errors count allowed: 100
Whether to load direct: No
Whether to insert identity column: No
Whether data is sorted by cluster index: No
Character sets: UTF-8
Data file counts: 1
/home/dmdba/test_lob_false.txt
Error file: fldr.bad
Dest table: TEST
Column Name Packed data type End
C1 CHARACTER |
C2 CHARACTER |
C3 CHARACTER |
3 rows processed.
Dest table: TEST
load success.
3 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 3
Refuse logic record counts: 0
3.621(ms) time used.
[dmdba@dm-standalone ~]$
– 查询表数据
SQL> SELECT * FROM TEST;
LINEID C1 C2 C3
---------- ----------- ---------- -------
1 1 0x12D3C8A7 abcdefg
2 2 0x12A4CBAC hijlkmn
3 3 0x22D3C8B3 adefhjd
used time: 4.433(ms). Execute id is 10414.
SQL>
2.5自增列的装载
当 DIRECT 参数为 FALSE 时，dmfldr 将把从数据文件中读取的自增列值作为目标值插入数据库表中，用户应当保证每一行的自增列的值符合自增列的规则，否则将造成数据混乱。
当 DIRECT 参数为 TRUE 时，dmfldr 提供了SET_IDENTITY 参数（缺省为 FALSE）对数据载入时自增列的处理进行设置：如果指定 SET_IDENTITY 选项值为 TRUE，则 dmfldr 将把从数据文件中读取的自增列值作为目标值插入数据库表
中，用户应当保证每一行的自增列的值符合自增列的规则，否则将造成数据混乱；
如果 SET_IDENTITY 选项值设置为 FALSE，则 dmfldr 将忽略数据文件中对应自增列的值，服务器将根据自增列定义和表中已有数据自动生成自增列的值插入每一行的对应列。另外需要注意的是，DMDPC 环境下暂不支持自增列装载。
– 建表 TEST，并插入两行数据
SQL> DROP TABLE IF EXISTS TEST;
executed successfully
used time: 28.342(ms). Execute id is 10415.
SQL> CREATE TABLE TEST(C1 INT IDENTITY(1,1),C2 VARCHAR);
executed successfully
used time: 20.698(ms). Execute id is 10416.
SQL> INSERT INTO TEST(C2) VALUES('AAA');
affect rows 1
used time: 3.046(ms). Execute id is 10417.
SQL> INSERT INTO TEST(C2) VALUES('BBB');
affect rows 1
used time: 2.609(ms). Execute id is 10418.
SQL> COMMIT;
executed successfully
used time: 6.850(ms). Execute id is 10419.
SQL> SELECT * FROM TEST;
LINEID C1 C2
---------- ----------- ---
1 1 AAA
2 2 BBB
used time: 3.313(ms). Execute id is 10420.
SQL>
– 编辑数据文件
[dmdba@dm-standalone ~]$ vim /home/dmdba/test_inc.txt
2|aaa
3|bbb
4|ccc
– 编辑控制文件
[dmdba@dm-standalone ~]$ vim /home/dmdba/control_inc.ctl
LOAD DATA
INFILE '/home/dmdba/test_inc.txt'
INTO TABLE test
FIELDS '|'
(
C1,
C2
)
– 使用 dmfldr 进行数据载入(set_identity=false)
[dmdba@dm-standalone ~]$ dmfldr userid=zgw/Zgw_1234 control=\'/home/dmdba/control_inc.ctl\' direct=true set_identity=false
dmfldr V8
Control file:
Loaded rows: All
Rows per commit to server: 50000
Rows to skip: 0
Errors count allowed: 100
Whether to load direct: Yes
Whether to insert identity column: No
Whether data is sorted by cluster index: No
Character sets: UTF-8
Data file counts: 1
/home/dmdba/test_inc.txt
Error file: fldr.bad
Dest table: TEST
Column Name Packed data type End
C1 CHARACTER |
C2 CHARACTER |
row buffer number is: 2
task thread number is: 2
3 rows committed
Dest table: TEST
load success.
3 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 3
Refuse logic record counts: 0
6.787(ms) time used.
[dmdba@dm-standalone ~]$
– 查看表 TEST 的数据。
SQL> SELECT * FROM TEST;
LINEID C1 C2
---------- ----------- ---
1 1 AAA
2 2 BBB
3 3 aaa
4 4 bbb
5 5 ccc
used time: 0.232(ms). Execute id is 10421.
SQL>
– 清空表重新插入数据
SQL> DROP TABLE IF EXISTS TEST;
executed successfully
used time: 29.021(ms). Execute id is 10422.
SQL> CREATE TABLE TEST(C1 INT IDENTITY(1,1),C2 VARCHAR);
executed successfully
used time: 17.745(ms). Execute id is 10423.
SQL> INSERT INTO TEST(C2) VALUES('AAA');
affect rows 1
used time: 3.648(ms). Execute id is 10424.
SQL> INSERT INTO TEST(C2) VALUES('BBB');
affect rows 1
used time: 11.670(ms). Execute id is 10425.
SQL> COMMIT;
executed successfully
used time: 11.166(ms). Execute id is 10426.
SQL> SELECT * FROM TEST;
LINEID C1 C2
---------- ----------- ---
1 1 AAA
2 2 BBB
used time: 12.711(ms). Execute id is 10427.
SQL>
– 使用 dmfldr 进行数据载入(set_identity=true)
[dmdba@dm-standalone ~]$ dmfldr userid=zgw/Zgw_1234 control=\'/home/dmdba/control_inc.ctl\' direct=true set_identity=true
dmfldr V8
Control file:
Loaded rows: All
Rows per commit to server: 50000
Rows to skip: 0
Errors count allowed: 100
Whether to load direct: Yes
Whether to insert identity column: Yes
Whether data is sorted by cluster index: No
Character sets: UTF-8
Data file counts: 1
/home/dmdba/test_inc.txt
Error file: fldr.bad
Dest table: TEST
Column Name Packed data type End
C1 CHARACTER |
C2 CHARACTER |
row buffer number is: 2
task thread number is: 2
3 rows committed
Dest table: TEST
load success.
3 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 3
Refuse logic record counts: 0
3.377(ms) time used.
– 查看表 TEST 的数据。
SQL> SELECT * FROM TEST;
LINEID C1 C2
---------- ----------- ---
1 1 AAA
2 2 BBB
3 2 aaa
4 3 bbb
5 4 ccc
used time: 0.274(ms). Execute id is 10428.
2.6空值处理
dmfldr 通过设置 NULL_MODE 参数来处理空值。设置为 TRUE，载入时 NULL 字符串处理为 NULL，载出时空值处理为 NULL 字符串设置为 FALSE，载入时 NULL 字符串处理为字符串，载出时空值处理为空串
– 建表 TEST
SQL> DROP TABLE IF EXISTS TEST;
executed successfully
used time: 90.186(ms). Execute id is 10429.
SQL> CREATE TABLE TEST(C1 INT,C2 VARCHAR);
executed successfully
used time: 16.694(ms). Execute id is 10430.
SQL>
– 编辑数据文件
[dmdba@dm-standalone ~]$ vim /home/dmdba/test_null.txt
1|aaa
2|NULL
3|null
– 编辑控制文件
[dmdba@dm-standalone ~]$ vim /home/dmdba/test_null.ctl
LOAD DATA
INFILE '/home/dmdba/test_null.txt'
INTO TABLE test
FIELDS '|'
(
C1,
C2
)
– 使用 dmfldr 进行数据载入。
[dmdba@dm-standalone ~]$ dmfldr userid=zgw/Zgw_1234 control=\'/home/dmdba/test_null.ctl\' null_mode=true
dmfldr V8
Control file:
Loaded rows: All
Rows per commit to server: 50000
Rows to skip: 0
Errors count allowed: 100
Whether to load direct: Yes
Whether to insert identity column: No
Whether data is sorted by cluster index: No
Character sets: UTF-8
Data file counts: 1
/home/dmdba/test_null.txt
Error file: fldr.bad
Dest table: TEST
Column Name Packed data type End
C1 CHARACTER |
C2 CHARACTER |
row buffer number is: 2
task thread number is: 2
3 rows committed
Dest table: TEST
load success.
3 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 3
Refuse logic record counts: 0
19.403(ms) time used.
[dmdba@dm-standalone ~]$
– 查看表 TEST 的数据
SQL> SELECT C1, IFNULL(C2, 'NULL VALUE') FROM TEST;
LINEID C1 "IFNULL"(C2,'NULLVALUE')
---------- ----------- ------------------------
1 1 aaa
2 2 NULL VALUE
3 3 NULL VALUE
used time: 13.960(ms). Execute id is 10431.
SQL>
– 清空表
SQL> truncate table test;
executed successfully
used time: 16.958(ms). Execute id is 10432.
SQL>
– 使用 dmfldr 进行数据载入。
[dmdba@dm-standalone ~]$ dmfldr userid=zgw/Zgw_1234 control=\'/home/dmdba/test_null.ctl\' null_mode=false
dmfldr V8
Control file:
Loaded rows: All
Rows per commit to server: 50000
Rows to skip: 0
Errors count allowed: 100
Whether to load direct: Yes
Whether to insert identity column: No
Whether data is sorted by cluster index: No
Character sets: UTF-8
Data file counts: 1
/home/dmdba/test_null.txt
Error file: fldr.bad
Dest table: TEST
Column Name Packed data type End
C1 CHARACTER |
C2 CHARACTER |
row buffer number is: 2
task thread number is: 2
3 rows committed
Dest table: TEST
load success.
3 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 3
Refuse logic record counts: 0
13.495(ms) time used.
[dmdba@dm-standalone ~]$
– 查看表 TEST 的数据
SQL> SELECT C1, IFNULL(C2, 'NULL VALUE') FROM TEST;
LINEID C1 "IFNULL"(C2,'NULLVALUE')
---------- ----------- ------------------------
1 1 aaa
2 2 NULL
3 3 null
used time: 0.300(ms). Execute id is 10433.
SQL>
2.7多表装载
通过在控制文件中指定多个 INTO TABLE 子句，可以将一批数据同时向多个表进行装载。每个 INTO TABLE 子句中都可以指定 WHEN 过滤条件、FIELDS 子句和列定义子句。
对于多表装载的使用需注意以下几点：
对于第二个及其之后的 INTO TABLE 子句，在其 coldef_option 中，必须为所有列指定 POSITION 选项；
在 INTO TABLE 子句的目标表中，如果目标表各不相同，则一个批次就可以完成装载完成（不管含有多少表，只需扫描一次数据文件即可）；如果含有重复的目标表，则相同的表需要分批次导入（每当遇到相同的表时，就需要再扫描一遍源文件。N个重复的表，就需要扫描 N 次源文件）。
– 建表 TEST1、TEST2
DROP TABLE IF EXISTS TEST1;
DROP TABLE IF EXISTS TEST2;
CREATE TABLE TEST1(C1 INT,C2 INT);
CREATE TABLE TEST2(C1 INT,C2 INT);
– 编辑数据文件 test.txt
[dmdba@dm-standalone ~]$ vim /home/dmdba/test_much.txt
1,2
2,3
3,2
4,8
9,1
– 编辑控制文件 test.ctl
[dmdba@dm-standalone ~]$ vim /home/dmdba/test_much.ctl
LOAD DATA
INFILE '/home/dmdba/test_much.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)
)
– 使用 dmfldr 进行数据载入
[dmdba@dm-standalone ~]$ dmfldr userid=zgw/Zgw_1234 control=\'/home/dmdba/test_much.ctl\'
dmfldr V8
Control file:
Loaded rows: All
Rows per commit to server: 50000
Rows to skip: 0
Errors count allowed: 100
Whether to load direct: Yes
Whether to insert identity column: No
Whether data is sorted by cluster index: No
Character sets: UTF-8
Data file counts: 1
/home/dmdba/test_much.txt
Error file: fldr.bad
Dest table: TEST1
Column Name Packed data type End
C1 CHARACTER ,
C2 CHARACTER ,
row buffer number is: 2
task thread number is: 2
Data file counts: 1
/home/dmdba/test_much.txt
Error file: fldr.bad
Dest table: TEST2
Column Name Packed data type End
C1 CHARACTER ,
C2 CHARACTER ,
row buffer number is: 2
task thread number is: 2
4 rows committed
1 rows committed
Dest table: TEST1
load success.
4 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 5
Refuse logic record counts: 0
55.457(ms) time used.
Dest table: TEST2
load success.
1 rows loaded success.
0 rows not loaded due to data error.
0 rows not loaded due to data format error.
Skip logic record counts: 0
Read logic record counts: 5
Refuse logic record counts: 0
55.457(ms) time used.
[dmdba@dm-standalone ~]$
–查看表 TEST1 和 TEST2 的数据如下
SQL> SELECT * FROM TEST1;
LINEID C1 C2
---------- ----------- -----------
1 2 3
2 3 2
3 4 8
4 9 1
used time: 10.835(ms). Execute id is 10438.
SQL> SELECT * FROM TEST2;
LINEID C1 C2
---------- ----------- -----------
1 1 2
used time: 14.177(ms). Execute id is 10439.
SQL>
2.7 dmfldr 使用限制
dmfldr 的使用存在以下一些限制：
不支持向临时表、外部表装载数据
不支持向系统表装载数据
不支持向带有位图索引的表装载数据
不支持向带有全文索引的表装载数据
不支持向 DCP 代理装载数据
不支持在 DPC 环境下，向带有全局索引的表装载数据
仅支持在 index_option=2 的情况下向带有函数索引的表装载数据
对象表暂不支持快速装载
dmfldr 装载时会对约束进行检查，对各种约束的处理机制如下表所示；
三、dmldrp 和 dmldrc 实战
dmldrp 和 dmldrc 需搭配使用才能完成快速装载。具体分为两步：一是先启动dmldrp；二是使用 dmldrc 执行导入功能。
– 建表 test
SQL> DROP TABLE IF EXISTS TEST;
executed successfully
used time: 273.563(ms). Execute id is 801.
SQL> CREATE TABLE TEST(C1 INT,C2 INT,C3 DATE);
executed successfully
used time: 15.734(ms). Execute id is 802.
SQL>
– 编辑数据文件
[dmdba@monitor ~]$ vim /home/dmdba/test.txt
1 1|2015-11-06
2 2|2015-11-05
3 3|2015-11_04
– 编辑控制文件
[dmdba@monitor ~]$ vim /home/dmdba/test.ctl
LOAD DATA
INFILE '/home/dmdba/test.txt'
INTO TABLE test
FIELDS '|'
(
C1 TERMINATED BY ' ',
C2,
C3 DATE FORMAT 'yyyy-mm-dd'
)
– 启动 dmldrp 服务器
[dmdba@dm-standalone ~]$ dmldrp port=9999
dmldrp V8
dmldrp is ready!
– 使用 dmldrc 进行数据载入
[dmdba@monitor ~]$ dmldrc userid=zgw/Zgw_1234 10.12.10.107:9999 control=\'/home/dmdba/test.ctl\' MODE=\'IN\'
dmldrc V8
控制文件：
加载行数: 全部
每次提交服务器行数: 50000
跳过行数: 0
允许错误数: 100
是否直接加载: Yes
是否插入自增列: No
数据是否已按照聚集索引排序: No
字符集: unknown
数据文件共1个:
/home/dmdba/test.txt
send data length:45
目标表: TEST
load success.
3 行加载成功。
0 行由于数据错误没有加载。
0 行由于数据格式错误被丢弃。
跳过的逻辑记录总数: 0
读取的逻辑记录总数: 3
拒绝的逻辑记录总数: 0
49.321(ms)已使用
[dmdba@monitor ~]$
– 查看数据
SQL> select * from test;
LINEID C1 C2 C3
---------- ----------- ----------- ----------
1 1 1 2015-11-06
2 2 2 2015-11-05
3 3 3 2015-11-04
used time: 4.276(ms). Execute id is 803.
SQL>
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1