热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
注册
dmfldr主键列和自增列装载测试
培训园地/ 文章详情 /

dmfldr主键列和自增列装载测试

이. 2023/09/26 2127 0 0

1概述
有人提问：test表有3列（id,col_1,col_2），id列为主键，并设置默认值为 sys_guid()；col_1和col_2可以为空，使用dmfldr导入数据时报错：非空列不能为空。
具体报错语句如下：
使用dmfldr导入数据
控制文件：
LOAD DATA
INFILE ‘/home/dmdba/data.txt’
INTO TABLE test
FIELDS ‘,’
(col_1,col_2)

data.txt内容：
测试,测试

2分析过程
2.1创建测试表
2.1.1Guid测试
SQL> select sys_guid() from dual;
行号 SYS_GUID()

1 0x21031F30161EB211598957DB9923F76F
已用时间: 4.788(毫秒). 执行号:500.
SQL> select length(sys_guid()) from dual;
行号 LENGTH(SYS_GUID())

1 32
已用时间: 0.587(毫秒). 执行号:504.
说明：生成一个唯一编码串（32 个字符）。
2.1.2创建测试表
create table test(
id VARCHAR(32) PRIMARY KEY default sys_guid(),
col_1 VARCHAR(10),
col_2 VARCHAR(10)
);

SQL> desc test
行号 NAME TYPE$ NULLABLE

1 ID VARCHAR(32) N
2 COL_1 VARCHAR(10) Y
3 COL_2 VARCHAR(10) Y
已用时间: 25.598(毫秒). 执行号:506.
2.1.3导入测试数据
SQL> insert into test(id,col_1,col_2) values(sys_guid(),'测试1','测试数1');
SQL> insert into test(id,col_1,col_2) values(sys_guid(),'测试2','测试数2');
SQL> insert into test(col_1,col_2) values('测试3','测试数3');
SQL> commit;
SQL> select * from test;
行号 ID COL_1 COL_2

1 10A49842181EB21111B5CA8C5C3DE7AF 测试1 测试数1
2 C432FB4F181EB21162BEE8BACF823D3D 测试2 测试数2
3 5998395E181EB2110BAE87121207F9FB 测试3 测试数3
已用时间: 0.549(毫秒). 执行号:514.
SQL>

2.2Dmfldr测试
2.2.1测试1：带主键ID值显式导入测试
2.2.1.1编辑数据文件 test.txt
编辑数据文件 test.txt，存放路径为/dm8/dmdbms/data/dmfldr/test.txt，文件内容如下：
0x21031F30161EB211598957DB9923F76F|测试4|测试数4
0x77F84232161EB21101BF0E4984F9FD3E|测试5|测试数5
0xCAABC532161EB2114B8898F1D4D7334F|测试6|测试数6
注意：VARCHAR类型的字段在txt文件中不要用’’单引号括起来。
2.2.1.2编辑控制文件 test.ctl
编辑控制文件 test.ctl，存放路径为/dm8/dmdbms/data/dmfldr/test.ctl，内容如下：
OPTIONS
(
CHARACTER_CODE=’UTF-8’
)
LOAD DATA
INFILE '/dm8/dmdbms/data/dmfldr/test.txt'
BADFILE '/dm8/dmdbms/data/dmfldr/test.bad'
INTO TABLE test
FIELDS '|'
(
ID,
COL_1,
COL_2
)
2.2.1.3使用 dmfldr 进行数据载入
/dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test.log'
导入报错：3 行由于数据错误没有加载，0行提交成功
[dmdba@master dmfldr]$ /dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test.log'
dmfldr V8
控制文件：
加载行数:全部
每次提交服务器行数:50000
跳过行数:0
允许错误数:100
是否直接加载:Yes
是否插入自增列:No
数据是否已按照聚集索引排序:No
字符集:GBK
数据文件共1个:
/dm8/dmdbms/data/dmfldr/test.txt
错误文件:/dm8/dmdbms/data/dmfldr/test.bad
目标表:TEST
列名 包装数据类型 终止
ID CHARACTER |
COL_1 CHARACTER |
COL_2 CHARACTER |
行缓冲区数量: 2
任务线程数量: 2
0行记录已提交
目标表:TEST
load success.
0 行加载成功。
0 行由于数据格式错误被丢弃。
3 行由于数据错误没有加载。
跳过的逻辑记录总数:0
读取的逻辑记录总数:3
拒绝的逻辑记录总数:3
2.424(ms)已使用
[dmdba@master dmfldr]$
检查报错bad文件
[dmdba@master dmfldr]$ cat test.bad
dmfldr: 2022-11-14 16:51:56 SYSDBA->TEST DMFLDR BAD FILE
dmfldr: 2022-11-14 16:51:56 SYSDBA->TEST '0x21031F30161EB211598957DB9923F76F'|'测试4'|'测试数4'
dmfldr: 2022-11-14 16:51:56 SYSDBA->TEST '0x77F84232161EB21101BF0E4984F9FD3E'|'测试5'|'测试数5'
dmfldr: 2022-11-14 16:51:56 SYSDBA->TEST '0xCAABC532161EB2114B8898F1D4D7334F'|'测试6'|'测试数6'
[dmdba@master dmfldr]$
说明：怀疑primary key主键值不能显式插入。
Insert直接显式插入试试
insert into test(id,col_1,col_2) values('0x21031F30161EB211598957DB9923F76F','测试4','测试数4');
select length(0x21031F30161EB211598957DB9923F76F) from dual;
insert into test(id,col_1,col_2) values('0x21031F30161EB211598957DB9923F7','测试4','测试数4');
image.png
2.2.1.4sys_guid()和guid()返回值不一样
image.png
image.png
说明：可以看到guid()返回的确实是32位的字符，而sys_guid()返回的是34位的字符，虽然用length(sys_guid())检查显示还是32位。
2.2.1.5使用 dmfldr 再次进行数据载入
/dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test.log'
删除test.txt中ID值2位字符
image.png
Dmfldr显式导入主键值成功
image.png
2.2.2测试2：不带主键ID值导入测试
2.2.2.1编辑数据文件 test1.txt
编辑数据文件 test1.txt，存放路径为/dm8/dmdbms/data/dmfldr/test1.txt，文件内容如下：
测试7|测试数7
测试8|测试数8
注意：VARCHAR类型的字段在txt文件中不要用’’单引号括起来。
2.2.2.2编辑控制文件 test1.ctl
编辑控制文件 test1.ctl，存放路径为/dm8/dmdbms/data/dmfldr/test1.ctl，内容如下：
OPTIONS
(
CHARACTER_CODE=’UTF-8’
)
LOAD DATA
INFILE '/dm8/dmdbms/data/dmfldr/test1.txt'
BADFILE '/dm8/dmdbms/data/dmfldr/test1.bad'
INTO TABLE test
FIELDS '|'
(
COL_1,
COL_2
)
2.2.2.3使用 dmfldr 进行数据载入
/dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test1.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test1.log'
导入报错：2行由于数据错误没有加载，0行提交成功
[dmdba@master dmfldr]$ /dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test1.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test1.log'
dmfldr V8
控制文件：
加载行数:全部
每次提交服务器行数:50000
跳过行数:0
允许错误数:100
是否直接加载:Yes
是否插入自增列:No
数据是否已按照聚集索引排序:No
字符集:UTF-8
数据文件共1个:
/dm8/dmdbms/data/dmfldr/test1.txt
错误文件:/dm8/dmdbms/data/dmfldr/test1.bad
目标表:TEST
列名 包装数据类型 终止
COL_1 CHARACTER |
COL_2 CHARACTER |
行缓冲区数量: 2
任务线程数量: 2
违反非空约束
0行记录已提交
目标表:TEST
load success.
0 行加载成功。
0 行由于数据格式错误被丢弃。
2 行由于数据错误没有加载。
跳过的逻辑记录总数:0
读取的逻辑记录总数:2
拒绝的逻辑记录总数:2
2.278(ms)已使用
[dmdba@master dmfldr]$
检查log日志文件
[dmdba@master dmfldr]$ cat dmfldr_test1.log
dmfldr: 2022-11-14 17:44:27 SYSDBA->TEST 违反非空约束
dmfldr: 2022-11-14 17:44:27 SYSDBA->TEST
说明：和问答中的报错一致，看来主键值不像自增列那样可以自增插入。
2.2.3测试3：主键ID值sys_guid()导入测试
2.2.3.1编辑数据文件 test2.txt
编辑数据文件 test2.txt，存放路径为/dm8/dmdbms/data/dmfldr/test2.txt，文件内容如下：
sys_guid()|测试7|测试数7
sys_guid()|测试8|测试数8
注意：VARCHAR类型的字段在txt文件中不要用’’单引号括起来。
2.2.3.2编辑控制文件 test2.ctl
编辑控制文件 test2.ctl，存放路径为/dm8/dmdbms/data/dmfldr/test2.ctl，内容如下：
OPTIONS
(
CHARACTER_CODE=’UTF-8’
)
LOAD DATA
INFILE '/dm8/dmdbms/data/dmfldr/test1.txt'
BADFILE '/dm8/dmdbms/data/dmfldr/test1.bad'
INTO TABLE test
FIELDS '|'
(
ID,
COL_1,
COL_2
)
2.2.3.3使用 dmfldr 进行数据载入
/dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test2.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test2.log'
2行记录导入成功
[dmdba@master dmfldr]$ /dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test2.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test2.log'
dmfldr V8
控制文件：
加载行数:全部
每次提交服务器行数:50000
跳过行数:0
允许错误数:100
是否直接加载:Yes
是否插入自增列:No
数据是否已按照聚集索引排序:No
字符集:UTF-8
数据文件共1个:
/dm8/dmdbms/data/dmfldr/test2.txt
错误文件:/dm8/dmdbms/data/dmfldr/test2.bad
目标表:TEST
列名 包装数据类型 终止
ID CHARACTER |
COL_1 CHARACTER |
COL_2 CHARACTER |
行缓冲区数量: 2
任务线程数量: 2
2行记录已提交
违反唯一性约束
目标表:TEST
load success.
2 行加载成功。
0 行由于数据格式错误被丢弃。
0 行由于数据错误没有加载。
跳过的逻辑记录总数:0
读取的逻辑记录总数:2
拒绝的逻辑记录总数:0
2.176(ms)已使用
[dmdba@master dmfldr]$
检查log日志文件报错：违反唯一性约束，但是数据还是插入成功了
image.png
检查TEST表数据
SQL> select * from test;
行号 ID COL_1 COL_2

1 10A49842181EB21111B5CA8C5C3DE7AF 测试1 测试数1
2 C432FB4F181EB21162BEE8BACF823D3D 测试2 测试数2
3 5998395E181EB2110BAE87121207F9FB 测试3 测试数3
4 0x21031F30161EB211598957DB9923F7 测试4 测试数4
5 0x77F84232161EB21101BF0E4984F9FD 测试5 测试数5
6 0xCAABC532161EB2114B8898F1D4D733 测试6 测试数6
7 sys_guid() 测试7 测试数7
8 sys_guid() 测试8 测试数8
8 rows got
已用时间: 0.540(毫秒). 执行号:541.
SQL>
说明：虽然dmfldr命令导入成功，但是数据内容不对。
2.2.4测试4：ctl控制文件设置函数导入测试
2.2.4.1编辑数据文件 test4.txt
编辑数据文件 test4.txt，存放路径为/dm8/dmdbms/data/dmfldr/test4.txt，文件内容如下：
|测试7|测试数7
|测试8|测试数8
注意：VARCHAR类型的字段在txt文件中不要用’’单引号括起来。
2.2.4.2编辑控制文件 test4.ctl
编辑控制文件 test4.ctl，存放路径为/dm8/dmdbms/data/dmfldr/test4.ctl，内容如下：
OPTIONS
(
CHARACTER_CODE='UTF-8'
)
LOAD DATA
INFILE '/dm8/dmdbms/data/dmfldr/test4.txt'
BADFILE '/dm8/dmdbms/data/dmfldr/test4.bad'
INTO TABLE TEST
FIELDS '|'
(
ID "sys_guid()",
COL_1,
COL_2
)
2.2.4.3使用 dmfldr 进行数据载入
/dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test4.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test4.log'
导入报错：不支持的处理函数
[dmdba@master dmfldr]$ /dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test4.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test4.log'
dmfldr V8
控制文件：
加载行数:全部
每次提交服务器行数:50000
跳过行数:0
允许错误数:100
是否直接加载:Yes
是否插入自增列:No
数据是否已按照聚集索引排序:No
字符集:UTF-8
不支持的处理函数
目标表:SYSDBA.TEST
load fail.
0 行加载成功。
0 行由于数据格式错误被丢弃。
0 行由于数据错误没有加载。
跳过的逻辑记录总数:0
读取的逻辑记录总数:0
拒绝的逻辑记录总数:0
0.000(ms)已使用
[dmdba@master dmfldr]$
2.2.4.4编辑控制文件 test4.ctl
编辑控制文件 test4.ctl，存放路径为/dm8/dmdbms/data/dmfldr/test4.ctl，内容如下：
OPTIONS
(
CHARACTER_CODE='UTF-8'
)
LOAD DATA
INFILE '/dm8/dmdbms/data/dmfldr/test4.txt'
BADFILE '/dm8/dmdbms/data/dmfldr/test4.bad'
INTO TABLE TEST
FIELDS '|'
(
ID "guid()",
COL_1,
COL_2
)
2.2.4.5使用 dmfldr 进行数据载入
/dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test4.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test4.log'
导入报错：不支持的处理函数
image.png
image.png
2.2.4.6Dmfldr不支持guid()函数

2.2.5测试总结
当导入表是包含主键的表且主键默认值是sys_guid()函数时，无法使用dmfldr导入数据，可行的方法如下：
当原表主键ID已经有具体值时，可以使用dmfldr显式的把数据导出再导入到表中。
无具体主键ID值时，使用dmfldr把数据导入到一个新表中（除主键字段外的所有字段），再使用insert into test as select sys_guid(),col1,col2…coln from test1的方式来导入数据。
使用其他数据迁移工具如DTS，dexp和dimp等进行数据迁移。

2.3Dmfldr自增列测试
自增列是比较特殊的列，为了保证数据库中自增列列值的正确性，用户在进行数据载入时需要特别注意。
当 DIRECT 参数为 FALSE 时，dmfldr 将把从数据文件中读取的自增列值作为目标值插入数据库表中，用户应当保证每一行的自增列的值符合自增列的规则，否则将造成数据混乱。
当 DIRECT 参数为 TRUE 时，dmfldr 提供了 SET_IDENTITY 参数（默认为 FALSE）对数据载入时自增列的处理进行设置：
如果指定 SET_IDENTITY 选项值为 TRUE，则 dmfldr 将把从数据文件中读取的自增列值作为目标值插入数据库表中，用户应当保证每一行的自增列的值符合自增列的规则，否则将造成数据混乱；
如果 SET_IDENTITY 选项值设置为 FALSE，则 dmfldr 将忽略数据文件中对应自增列的值，服务器将根据自增列定义和表中已有数据自动生成自增列的值插入每一行的对应列。
另外需要注意的是，DMDPC 环境下暂不支持自增列装载。
DIRECT表示是否使用快速方式装载，默认值是(TRUE)。
2.3.1创建测试数据
创建测试表
create table test1 (ID INT IDENTITY(1,1), NAME VARCHAR(10));

insert into test1(name) values('张一');
insert into test1(name) values('张二');
insert into test1(name) values('张三');
insert into test1(name) values('张四');
commit;
检查测试表
SQL> desc test1
行号 NAME TYPE$ NULLABLE

1 ID INTEGER N
2 NAME VARCHAR(10) Y
已用时间: 5.387(毫秒). 执行号:551.
SQL> select * from test1;
行号 ID NAME

1 1 张一
2 2 张二
3 3 张三
4 4 张四
已用时间: 0.486(毫秒). 执行号:552.
SQL>
2.3.2测试1：SET_IDENTITY为TRUE时导入测试
2.3.2.1编辑数据文件 test1.txt
编辑数据文件 test1.txt，存放路径为/dm8/dmdbms/data/dmfldr/test1.txt，文件内容如下：
3|张1
4|张2
5|张3
6|张4
注意：VARCHAR类型的字段在txt文件中不要用’’单引号括起来。
2.3.2.2编辑控制文件 test1.ctl
编辑控制文件 test1.ctl，存放路径为/dm8/dmdbms/data/dmfldr/test1.ctl，内容如下：
OPTIONS
(
CHARACTER_CODE='UTF-8'
SET_IDENTITY=TRUE
)
LOAD DATA
INFILE '/dm8/dmdbms/data/dmfldr/test1.txt'
BADFILE '/dm8/dmdbms/data/dmfldr/test1.bad'
INTO TABLE test1
FIELDS '|'
(
ID,
NAME
)
2.3.2.3使用 dmfldr 进行数据载入
/dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test1.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test1.log'
4行记录导入成功
[dmdba@master dmfldr]$ /dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test1.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test1.log'
dmfldr V8
控制文件：
加载行数:全部
每次提交服务器行数:50000
跳过行数:0
允许错误数:100
是否直接加载:Yes
是否插入自增列:Yes
数据是否已按照聚集索引排序:No
字符集:UTF-8
数据文件共1个:
/dm8/dmdbms/data/dmfldr/test1.txt
错误文件:/dm8/dmdbms/data/dmfldr/test1.bad
目标表:TEST1
列名 包装数据类型 终止
ID CHARACTER |
NAME CHARACTER |
行缓冲区数量: 2
任务线程数量: 2
4行记录已提交
目标表:TEST1
load success.
4 行加载成功。
0 行由于数据格式错误被丢弃。
0 行由于数据错误没有加载。
跳过的逻辑记录总数:0
读取的逻辑记录总数:4
拒绝的逻辑记录总数:0
1.456(ms)已使用
[dmdba@master dmfldr]$
检查TEST表数据
SQL> select * from test1;
行号 ID NAME

1 1 张一
2 2 张二
3 3 张三
4 4 张四
5 3 张1
6 4 张2
7 5 张3
8 6 张4
8 rows got
已用时间: 0.231(毫秒). 执行号:553.
SQL>
说明：虽然dmfldr数据导入成功，但是ID字段的自增性却没有实现，导致有重复数据，此方法不可用。

2.3.3测试2：SET_IDENTITY为FALSE时导入测试
2.3.3.1编辑数据文件 test2.txt
编辑数据文件 test2.txt，存放路径为/dm8/dmdbms/data/dmfldr/test2.txt，文件内容如下：
3|王1
4|王2
5|王3
6|王4
注意：VARCHAR类型的字段在txt文件中不要用’’单引号括起来。
2.3.3.2编辑控制文件 test2.ctl
编辑控制文件 test2.ctl，存放路径为/dm8/dmdbms/data/dmfldr/test2.ctl，内容如下：
OPTIONS
(
CHARACTER_CODE='UTF-8'
SET_IDENTITY=FALSE
)
LOAD DATA
INFILE '/dm8/dmdbms/data/dmfldr/test2.txt'
BADFILE '/dm8/dmdbms/data/dmfldr/test2.bad'
INTO TABLE test1
FIELDS '|'
(
ID,
NAME
)
2.3.3.3使用 dmfldr 进行数据载入
/dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test2.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test2.log'
4行记录导入成功
[dmdba@master dmfldr]$ /dm8/dmdbms/bin/dmfldr userid=SYSDBA/SYSDBA@localhost:15236 control='/dm8/dmdbms/data/dmfldr/test2.ctl' log='/dm8/dmdbms/data/dmfldr/dmfldr_test2.log'
dmfldr V8
控制文件：
加载行数:全部
每次提交服务器行数:50000
跳过行数:0
允许错误数:100
是否直接加载:Yes
是否插入自增列:No
数据是否已按照聚集索引排序:No
字符集:UTF-8
数据文件共1个:
/dm8/dmdbms/data/dmfldr/test2.txt
错误文件:/dm8/dmdbms/data/dmfldr/test2.bad
目标表:TEST1
列名 包装数据类型 终止
ID CHARACTER |
NAME CHARACTER |
行缓冲区数量: 2
任务线程数量: 2
4行记录已提交
目标表:TEST1
load success.
4 行加载成功。
0 行由于数据格式错误被丢弃。
0 行由于数据错误没有加载。
跳过的逻辑记录总数:0
读取的逻辑记录总数:4
拒绝的逻辑记录总数:0
1.588(ms)已使用
[dmdba@master dmfldr]$
检查TEST表数据
SQL> select * from test1;
行号 ID NAME

1 1 张一
2 2 张二
3 3 张三
4 4 张四
5 3 张1
6 4 张2
7 5 张3
8 6 张4
9 7 王1
10 8 王2
11 9 王3
12 10 王4
12 rows got
已用时间: 0.183(毫秒). 执行号:555.
SQL>
说明：dmfldr数据导入成功，且ID自增字段实现数据自增性，此方法可用。
2.3.4自增列测试总结
当使用dmfldr导入自增列数据时，需要关注导入表的已有数据和导入数据是否有重复值：
如果无重复值，建议使用：SET_IDENTITY 参数值为 TRUE（默认为 FALSE）导入数据。
如果有重复值，先把重复值排掉，使用：SET_IDENTITY 参数值为 TRUE（默认为 FALSE）导入数据，再使用SET_IDENTITY 参数值为 FALSE导入重复的数据。

上一篇：
DM通过系统包并行收集统计信息
评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服
logo
logo
logo
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范

鄂ICP备18017926号-1