外部表学习记录

背景

DCP培训学习了外部表的创建，自己本地练习时发现创建完外部表之后查询表记录会报错 [-7082]:外部表数据错误. 以下内容记录创建外部表的步骤及报错处理记录。

数据库版本：1-2-192-2023.01.03-178822-20033-ENT
操作系统版本：4.19.90-24.4.v2101.ky10.x86_64

创建外部表步骤

1、在/opt目录下创建emp.txt文档，并将文件属主改为dmdba用户

[root@localhost bin]# vi /opt/emp.txt 
[root@localhost bin]# chown -R dmdba.dinstall /opt/emp.txt 
[root@localhost bin]# ll /opt/emp.txt 
-rw-r--r-- 1 dmdba dinstall 18  3月 27 08:40 /opt/emp.txt
[root@localhost bin]# 
[root@localhost bin]# cat /opt/emp.txt 
1,AAA
2,BBB
3,CCC
[root@localhost bin]#

2、在disql中创建目录并创建外部表emp.txt

[root@localhost bin]# ./disql SYSDBA/DM01SYSDBA

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.366(ms)
disql V8
SQL> create directory opt as '/opt'; 
操作已执行
已用时间: 19.976(毫秒). 执行号:500.
SQL> CREATE EXTERNAL TABLE EMP(C1 INT,C2 VARCHAR(10))
2   FROM DATAFILE DEFAULT DIRECTORY OPT LOCATION ('emp.txt') 
3   PARMS(FIELDS DELIMITED BY ',');
操作已执行
已用时间: 15.127(毫秒). 执行号:501.

3、查询外部表emp报错 [-7082]:外部表数据错误.

SQL> select * from emp;
select * from emp;
[-7082]:外部表数据错误.
已用时间: 80.323(毫秒). 执行号:0.

按照SQL手册检查了建表语句，并核对了外部文件内容，觉得两者都没有错误，但是查询报错，猜想与文件权限有关，但是emp.txt文件的属主已经是dmdba用户，感觉可能是受/opt目录权限影响，因此将/opt目录权限设置为777进行验证。

4、修改/opt目录权限为777

[root@localhost bin]# ls -l / |grep opt
drwxr-xr-x    4 root  root      108  3月 27 08:40 opt
[root@localhost bin]# 
[root@localhost bin]# chmod 777 /opt
[root@localhost bin]# 
[root@localhost bin]# ls -l / |grep opt
drwxrwxrwx    4 root  root      108  3月 27 08:40 opt

5、再次查询emp表成功查询出数据

[root@localhost bin]# ./disql SYSDBA/DM01SYSDBA

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.464(ms)
disql V8
SQL> select * from emp;

行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB
3          3           CCC

已用时间: 19.800(毫秒). 执行号:600.

6、再次修改/opt目录的权限为原始的755，验证查询是否还会报错

[root@localhost bin]# chmod 755 /opt
[root@localhost bin]# ls -l / |grep opt
drwxr-xr-x    4 root  root      128  3月 27 09:00 opt
[root@localhost bin]# 
[root@localhost bin]# ./disql SYSDBA/DM01SYSDBA

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.433(ms)
disql V8
SQL> select * from emp;

行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB
3          3           CCC

已用时间: 51.410(毫秒). 执行号:700.

将/opt目录权限改回755后查询依然没有报错，难道是读取的缓存，于是修改外部表数据文件，将emp.txt文件内容新增记录，验证查询是否能读取到新记录。

7、保持/opt目录权限为755，在emp.txt文件新增一行数据，查询emp表依然可以读取到新数据

[root@localhost bin]# vi /opt/emp.txt 
[root@localhost bin]# cat /opt/emp.txt 
1,AAA
2,BBB
3,CCC
4,ddd
[root@localhost bin]# 
[root@localhost bin]# ./disql SYSDBA/DM01SYSDBA

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.743(ms)
disql V8
SQL> select * from emp;

行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB
3          3           CCC
4          4           ddd

已用时间: 18.257(毫秒). 执行号:800.

于是在/opt目录下拷贝一个新文件emp2.txt，文件内容及权限和emp.txt保持一致，新建外部表emp2，验证查询是否可以成功。

8、拷贝emp2.txt并创建外部表emp2

[root@localhost bin]# cp /opt/emp.txt /opt/emp2.txt 
[root@localhost bin]# chown -R dmdba.dinstall /opt/emp2.txt 
[root@localhost bin]# ls -l /opt/emp*
-rw-r--r-- 1 dmdba dinstall 24  3月 27 09:11 /opt/emp2.txt
-rw-r--r-- 1 dmdba dinstall 24  3月 27 09:05 /opt/emp.txt
[root@localhost bin]# 
[root@localhost bin]# ./disql SYSDBA/DM01SYSDBA

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 4.096(ms)
disql V8
SQL> CREATE EXTERNAL TABLE EMP2(C1 INT,C2 VARCHAR(10))
2   FROM DATAFILE DEFAULT DIRECTORY OPT LOCATION ('emp2.txt')
3   PARMS(FIELDS DELIMITED BY ',');
操作已执行
已用时间: 8.089(毫秒). 执行号:900.
SQL> 
SQL> select * from emp2;
select * from emp2;
[-7082]:外部表数据错误.
已用时间: 17.711(毫秒). 执行号:0.

9、再次使用emp.txt文件创建外部表emp1

SQL> CREATE EXTERNAL TABLE EMP1(C1 INT,C2 VARCHAR(10))
2   FROM DATAFILE DEFAULT DIRECTORY OPT LOCATION ('emp.txt')
3   PARMS(FIELDS DELIMITED BY ',');
操作已执行
已用时间: 4.695(毫秒). 执行号:902.
SQL> 
SQL> select * from emp1;
select * from emp1;
[-7082]:外部表数据错误.
已用时间: 2.267(毫秒). 执行号:0.

10、在/opt目录为755时，新建的外部表查询都报错，再次修改/opt目录权限为777进行验证。

[root@localhost bin]# chmod 777 /opt
[root@localhost bin]# ls -l / |grep opt
drwxrwxrwx    4 root  root      144  3月 27 09:11 opt
[root@localhost bin]# 
[root@localhost bin]# ./disql SYSDBA/DM01SYSDBA

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 4.351(ms)
disql V8
SQL> select * from emp2;

行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB
3          3           CCC
4          4           ddd

已用时间: 3.975(毫秒). 执行号:1000.
SQL> select * from emp1;

行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB
3          3           CCC
4          4           ddd

已用时间: 3.853(毫秒). 执行号:1001.

11、将/opt目录权限改回755，再次随机查询emp2表

[root@localhost bin]# chmod 755 /opt
[root@localhost bin]# ls -l / |grep opt
drwxr-xr-x    4 root  root      186  3月 27 09:17 opt
[root@localhost bin]# 
[root@localhost bin]# ./disql SYSDBA/DM01SYSDBA

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.086(ms)
disql V8
SQL> select * from emp2;

行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB
3          3           CCC
4          4           ddd

已用时间: 2.628(毫秒). 执行号:1100.

12、拷贝/opt/emp.txt至dmdba用户家目录下

[root@localhost bin]# cp /opt/emp.txt /home/dmdba/emp.txt 
[root@localhost bin]# ll /home/dmdba/emp.txt 
-rw-r--r-- 1 root root 24  3月 27 09:22 /home/dmdba/emp.txt
[root@localhost bin]# chown dmdba.dinstall /home/dmdba/emp.txt 
[root@localhost bin]# 
[root@localhost bin]# ll /home/dmdba/emp.txt 
-rw-r--r-- 1 dmdba dinstall 24  3月 27 09:22 /home/dmdba/emp.txt

13、将dmdba家目录下的emp.txt创建为外部表

[root@localhost bin]# ./disql SYSDBA/DM01SYSDBA

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.245(ms)
disql V8
SQL> 
SQL> create directory home as '/home/dmdba'; 
操作已执行
已用时间: 5.733(毫秒). 执行号:1400.
SQL> CREATE EXTERNAL TABLE EMP_H(
C1 INT,
C2 VARCHAR(10)
) FROM DATAFILE DEFAULT DIRECTORY HOME LOCATION ('emp.txt') 
PARMS(FIELDS DELIMITED BY ',');   
操作已执行
已用时间: 5.206(毫秒). 执行号:1401.
SQL>  
SQL> select * from EMP_H;

行号     C1          C2 
---------- ----------- ---
1          1           AAA
2          2           BBB
3          3           CCC
4          4           ddd

已用时间: 17.470(毫秒). 执行号:1402.

总结

经过以上测试发现，在/opt目录权限为755时，即使使用/opt目录下属主为dmdba的文件新建外部表，查询外部表数据也会报错 [-7082]:外部表数据错误. 但是修改/opt目录权限为777后查询不再报错，即使再次修改/opt目录权限为755，查询依然正常。使用dmdba用户家目录下的文件创建外部表，查询就不会报错。

至于为什么使用/opt目录下的文件创建外部表时需要修改/opt目录权限至777并且修改为777后再次修改为755后查询都不再报错，本人就不太清楚其中原理了，希望有知道的大佬可以给解析一下这个现象。

背景