dmfldr(DM fast Loader)是达梦 8 提供的快速数据加载的命令行。通过 dmfldr 命令行工具能够把按照特定格式排序的文本数据快速、高效的加载到 DM8 数据库系统中,或把 DM8 数据库中的表数据按照一定格式导出到文本文件。
示例1:将文本 test1.txt 中的数据加载到 DM8 中
1)、准备数据文件
$ cat /home/dmdba/test1.txt 1001,XiaoMing,2022-01-23 1002,XiaoLi 1003,XiaoWang,2021-11-11 1004,XiaoMa,2021-11-25 $
2)、准备控制文件
$ cat fldrtest.ctl LOAD DATA INFILE '/home/dmdba/test1.txt' INTO TABLE TTT.TEST1 FIELDS ',' $
3)、在用户 TTT 下创建测试表
SQL> create table TTT.TEST1(id int,name varchar(20),bir date); executed successfully used time: 198.397(ms). Execute id is 2424. SQL>
4)、快速加载数据到 DM8 中
$ /dm8/bin/dmfldr SYSDBA/DAMENG123@localhost:5236 control=\'/home/dmdba/test1.ctl\'
open fail! path:./libcrypto.so
dmfldr V8
dmfldr:
Copyright (c) 2011, 2015, Dameng. All rights reserved.
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:GBK
Data file counts: 1
/home/dmdba/test1.txt
Error file :fldr.bad
Dest table :TTT.TEST1
Column Name Packed data type End
ID CHARACTER ,
NAME CHARACTER ,
BIR CHARACTER ,
row buffer number is: 2
task thread number is: 2
Data error
col nums in data file is not enough
Data error
Data error
3 rows committed
Dest table :TTT.TEST1
3 Rows loaded success.
Due to data format error, 4 rows abandon.
Due to data error, 0 rows not loaded.
Skip logic record counts: 0
Read logic record counts: 7
Refuse logic record counts: 0
The total time used: 5.408(ms)
$
5)、查看数据
SQL> select * from TTT.TEST1; LINEID ID NAME BIR ---------- ----------- ---------- ---------- 1 1 XiaoMing 2022-01-23 2 3 XiaoWang 2021-11-11 3 4 XiaoMa 2021-11-25 used time: 0.842(ms). Execute id is 2425. SQL>
注:从加载日志可以看到由于 test1.txt 的数据格式问题,第二行没有导入到表 test1 中
示例2:将 TTT 用户下的 TEST1 表导出到 test2.txt 中,各字段以 "|" 分隔
1)、准备控制文件
$ cat test2.ctl LOAD DATA INFILE '/home/dmdba/test2.txt' INTO TABLE TTT.TEST1 FIELDS '|' $
2)、导出数据到 test2.txt
$ /dm8/bin/dmfldr SYSDBA/DAMENG123@localhost:5236 control=\'/home/dmdba/test2.ctl\' mode=\'out\' open fail! path:./libcrypto.so dmfldr V8 3 rows is load out Load finish 3 rows, time:51.875(ms) $
3)、查看 test2.txt 文件内容
$ cat test2.txt 1001|XiaoMing|2022-01-23 1003|XiaoWang|2021-11-11 1004|XiaoMa|2021-11-25 $