创建外部表引起外部表数据错误的常见原因分析

外部表，是指不存在于数据库中的表。通过向达梦提供描述外部表的元数据，可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。
外部表的数据存储在操作系统中，是操作系统文件，建立外部表的时候，不会产生段，页簇等存储结构，只有与表相关的定义放在数据字典中。在数据库中不能对外部表的内容进行修改，不能对外部表建立主键，索引，外键。不需要将外部表的数据载入到数据库中来，通过 sql 解码器来访问外部表。
外部表创建之后，查询外部表数据，提示外部表数据错误。这个问题应该是比较常见的，数据库具体返回的错误值为“-7082: 外部表数据错误”。下面我们来分析一下以前该问题的常见原因。

1、文件夹权限引起的外部表数据错误

该问题主要由txt文件所在目录的权限导致，而非文件本身的权限。具体情况可以看一下测试过程：
外部表数据t1.txt

[root@localhost test]# cat t1.txt 11,张三,20220423 12,李嘉,20230524 13,王五,20220522 14,赵六,20240101 15,李学光,20230409 16,陈子李,20230724 17,赵乾,20231024

复制

外部表数据t2.txt

[dmdba@localhost test]$ cat t2.txt 11,张三,20220423 12,李嘉,20230524 13,王五,20220522 14,赵六,20240101 15,李学光,20230409 16,陈子李,20230724 17,赵乾,20231024

复制

两个文件除所属用户和用户组外其他信息均一致。

[root@localhost test]# ll 总用量 8 -rw-r--r-- 1 root root 139 4月 24 21:51 t1.txt -rw-r--r-- 1 dmdba dinstall 139 4月 24 22:03 t2.txt

复制

分别创建外部表test.t1和test.t2

SQL> create external table test.t1(id int, name varchar(20),birthday date) from datafile
2   default directory testdir location('t1.txt')
3   parms( fields delimited by ',');
操作已执行
已用时间: 73.062(毫秒). 执行号:2500.
SQL> create external table test.t2(id int, name varchar(20),birthday date) from datafile
2   default directory testdir location('t2.txt')
3   parms( fields delimited by ',');
操作已执行
已用时间: 24.997(毫秒). 执行号:2501.
复制

查询外部表数据，均提示外部表数据错误。

SQL> select * from test.t1;
select * from test.t1;
[-7082]:外部表数据错误.
已用时间: 3.125(毫秒). 执行号:0.
SQL> select * from test.t2;
select * from test.t2;
[-7082]:外部表数据错误.
已用时间: 1.998(毫秒). 执行号:0.
复制

我们查看确认test目录的用户权限，达梦数据库一般使用dmdba用户作为操作系统级别的管理员账号，此时test目录属于root用户。

[root@localhost workfiles]# ll 总用量 4 drwxr-xr-x 2 root root 4096 4月 24 22:02 test

复制

我们修改test目录的所属的用户和用户组后外部表就可以正常查询了。

root@localhost workfiles]# chown dmdba:dinstall test [root@localhost workfiles]# ll 总用量 8 drwxr-xr-x 2 dmdba dinstall 4096 4月 24 22:02 test SQL> select * from test.t1; 行号 ID NAME BIRTHDAY ---------- ----------- --------- ---------- 1 11 张三 2022-04-23 2 12 李嘉 2023-05-24 3 13 王五 2022-05-22 4 14 赵六 2024-01-01 5 15 李学光 2023-04-09 6 16 陈子李 2023-07-24 7 17 赵乾 2023-10-24 7 rows got 已用时间: 17.703(毫秒). 执行号:2505. SQL> select * from test.t2; 行号 ID NAME BIRTHDAY ---------- ----------- --------- ---------- 1 11 张三 2022-04-23 2 12 李嘉 2023-05-24 3 13 王五 2022-05-22 4 14 赵六 2024-01-01 5 15 李学光 2023-04-09 6 16 陈子李 2023-07-24 7 17 赵乾 2023-10-24 7 rows got 已用时间: 38.845(毫秒). 执行号:2506.

复制

或者给test目录赋予777权限也可以达到同样的效果。

[root@localhost workfiles]# chmod 777 test [root@localhost workfiles]# ll 总用量 4 drwxrwxrwx 2 root root 4096 4月 24 22:13 test SQL> select * from test.t1; 行号 ID NAME BIRTHDAY ---------- ----------- --------- ---------- 1 11 张三 2022-04-23 2 12 李嘉 2023-05-24 3 13 王五 2022-05-22 4 14 赵六 2024-01-01 5 15 李学光 2023-04-09 6 16 陈子李 2023-07-24 7 17 赵乾 2023-10-24 7 rows got 已用时间: 2.576(毫秒). 执行号:2509.

复制

2、数据格式不匹配造成的外部表数据错误

文件中的数据格式，与数据库定义中的字段数据格式不匹配造成的外部表数据错误。
外部表数据t3.txt

[root@localhost test]# cat t3.txt 
AA,张三,20220423
12,李嘉,20230524
13,王五,20220522
14,赵六,20240101
15,李学光,20230409
16,陈子李,20230724
17,赵乾,20231024
SQL> create external table test.t3(id int, name varchar(20),birthday date) from datafile
2   default directory testdir location('t3.txt')
3   parms( fields delimited by ',');
操作已执行
已用时间: 816.819(毫秒). 执行号:2510.
SQL> select * from test.t3;
select * from test.t3;
[-7082]:外部表数据错误.
已用时间: 71.573(毫秒). 执行号:0.
复制

张三的ID明显不是int类型，所以报错，如果把id改为字符类型，则可以正常查询出数据。

SQL> create external table test.t4(id varchar(20), name varchar(20),birthday date) from datafile
2   default directory testdir location('t3.txt')
3   parms( fields delimited by ',');
操作已执行
已用时间: 40.146(毫秒). 执行号:2512.
SQL> select * from test.t4;

行号     ID NAME      BIRTHDAY  
---------- -- --------- ----------
1          AA 张三    2022-04-23
2          12 李嘉    2023-05-24
3          13 王五    2022-05-22
4          14 赵六    2024-01-01
5          15 李学光 2023-04-09
6          16 陈子李 2023-07-24
7          17 赵乾    2023-10-24

7 rows got

已用时间: 7.225(毫秒). 执行号:2513.
复制

还有一种比较容易忽略的问题就是因添加了列名导致的数据量类型不匹配的问题。
外部表数据t5.txt

[root@localhost test]# cat t5.txt /*id,name,birthday*/ 11,张三,20220423 12,李嘉,20230524 13,王五,20220522 14,赵六,20240101 15,李学光,20230409 16,陈子李,20230724 17,赵乾,20231024

复制

创建外部表t5，但是查询失败。

SQL> create external table test.t5(id int, name varchar(20),birthday date) from datafile
2   default directory testdir location('t5.txt')
3   parms( fields delimited by ',');
操作已执行
已用时间: 28.839(毫秒). 执行号:2514.
SQL> select * from test.t5;
select * from test.t5;
[-7082]:外部表数据错误.
已用时间: 4.771(毫秒). 执行号:0.
复制

如果把表中的字段类型都改为字符类型就很容易发现问题了，创建外部表t6。

SQL> create external table test.t6(id varchar(20), name varchar(20),birthday varchar(20)) from datafile
2   default directory testdir location('t5.txt')
3   parms( fields delimited by ',');
操作已执行
已用时间: 4.551(毫秒). 执行号:2515.
SQL> select * from test.t6;

行号     ID   NAME      BIRTHDAY  
---------- ---- --------- ----------
1          /*id name      birthday*/
2          11   张三    20220423
3          12   李嘉    20230524
4          13   王五    20220522
5          14   赵六    20240101
6          15   李学光 20230409
7          16   陈子李 20230724
8          17   赵乾    20231024

8 rows got

已用时间: 3.116(毫秒). 执行号:2517.
复制

外部表把列名也当做记录查询了出来，要避免这种问题，可以在创建外部表时过滤掉列名，创建外部表t7。

SQL> create external table test.t7(id int, name varchar(20),birthday date) from datafile 2 default directory testdir location('t5.txt') 3 parms( fields delimited by ',',skip 1); 操作已执行已用时间: 45.209(毫秒). 执行号:2518. SQL> select * from test.t7; 行号 ID NAME BIRTHDAY ---------- ----------- --------- ---------- 1 11 张三 2022-04-23 2 12 李嘉 2023-05-24 3 13 王五 2022-05-22 4 14 赵六 2024-01-01 5 15 李学光 2023-04-09 6 16 陈子李 2023-07-24 7 17 赵乾 2023-10-24 7 rows got 已用时间: 3.707(毫秒). 执行号:2519.

复制