【OceanBase专栏】OceanBase 外部表实验

OceanBase && Oracle相关文档，希望互相学习，共同进步

1.概述

OceanBase 支持外部表（External Table），可将外部数据（如 CSV 文件）映射为数据库表。这种方式可以通过 SQL 查询外部数据，也可以用于数据导入。

OceanBase 数据库中，创建外部表（External Table）是一种非常有用的功能，它允许用户在不移动数据的情况下，直接查询存储在其他系统中的数据。这对于数据仓库和数据湖解决方案尤其有用，因为它可以整合多种数据源，包括文件系统（如 HDFS、S3）、数据库等。

语法：

CREATE EXTERNAL TABLE <table_name>
( [ <column_name> <column_type> [AS ] ]
[ , <column_name> <column_type> [AS ] ]
[ , ... ] )
LOCATION = ''
FORMAT = (
TYPE = 'CSV'
LINE_DELIMITER = '' |
FIELD_DELIMITER = '' |
ESCAPE = '' |
FIELD_OPTIONALLY_ENCLOSED_BY = '' |
ENCODING = 'charset'
NULL_IF = ('' | , '' | ...)
SKIP_HEADER =
SKIP_BLANK_LINES = { TRUE | FALSE }
TRIM_SPACE = { TRUE | FALSE }
EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
)
[ PATTERN = '<regex_pattern>' ]

column_name 外表的列名称。默认下，文件的数据列、外表定义的列是自动按顺序对应。column_type 定义外表的列类型，但不能定义约束（如，DEFAULT、NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY 等）。

as 用于手动指定列映射。当文件中的列顺序与外表的列所定义顺序不一致时，可以通过 metadata$filecol{N} 所表示的伪列来指定外表的列与文件中的第 N 列的对应关系。

如，c2 INT AS (metadata$filecol4) 表示外表的 c2 列对应文件中的第 4 列。

注意：如果指定了手动列映射，则自动映射关系会失效，且所有列都需手动定义映射。

LOCATION指定外表文件存放的路径。通常外表的数据文件存放于单独一个目录中，文件夹中可以包含子目录，在创建表时，外表会自动收集该目录中的所有文件。
本地 LOCATION 格式为 LOCATION = '[file://] local_file_path'，其中local_file_path 可以为相对路径，也可以为绝对路径。如果是相对路径，则当前目录必须为 OceanBase 数据库的安装目录；secure_file_priv 配置 OBServer 节点有权限访问的文件路径。这里只能是 secure_file_priv 路径的子路径。
远程 LOCATION 格式：

LOCATION = '{oss|cos}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'，其中 $ACCESS_ID、$ACCESS_KEY 和 $HOST 是访问 OSS/COS 时需要配置的访问信息，这些敏感的访问信息会以加密的方式存放在数据库的系统表中。

FORMAT指定外部文件的格式：
TYPE：指定外部文件的类型，当前仅支持 CSV 类型。
LINE_DELIMITER：指定 CSV 文件的行分隔符。
FIELD_DELIMITER：指定 CSV 文件的列分隔符。
ESCAPE：指定 CSV 文件的转义符号，只能为 1 个字节。
FIELD_OPTIONALLY_ENCLOSED_BY：指定 CSV 文件包裹字段值的符号。默认空。
ENCODING：指定文件的字符集编码格式，当前 Oracle 模式支持的所有字符集请参见字符集。如果不指定，默认值为 UTF8MB4。
NULL_IF：指定被当作 NULL 处理的字符串。默认值为空。
SKIP_HEADER：跳过文件头，并指定跳过的行数。
SKIP_BLANK_LINES：指定是否跳过空白行。默认值为 FALSE，表示不跳过空白的行。
TRIM_SPACE：指定是否删除文件中字段的头部和尾部空格。默认值为 FALSE，表示不删除文件中字段头尾的空格。
EMPTY_FIELD_AS_NULL：指定是否将空字符串当作 NULL 处理。默认值为 FALSE，表示不将空字符串当做 NULL 处理。

PATTERN：正则模式串，过滤 LOCATION 目录下的文件。对于每个 LOCATION 目录下的文件路径，如果能够匹配该模式串，外表会访问这个文件，否则外表会跳过这个文件。如果不指定该参数，则默认可以访问 LOCATION 目录下的所有文件。外表会将LOCATION 指定路径下满足 PATTERN 的文件列表保存在数据库系统表中，外表扫描时会根据这个列表来访问外部的文件。文件列表可以有自动更新和手动更新两种方式。

2. 实验

2.1 准备外部表文件

vim ext_table.dat

cat ext_table.dat
1,晓明
2,小花
3,小量

截图：

2.2 创建外部表

CREATE EXTERNAL TABLE ext_table (
    id INT,
    nm VARCHAR(100)
)
LOCATION= '/home/oceanbase/test/'
 FORMAT = (  
     TYPE = 'CSV'
     FIELD_DELIMITER = ','
     FIELD_OPTIONALLY_ENCLOSED_BY =''''
    )PATTERN ='ext_table.dat';

--drop table ext_table
select * from ext_table;

截图：创建成功 ok

2.3 查询外部表

SELECT * FROM ext_table;

2.4 将数据插入内部表

INSERT INTO your_table SELECT * FROM ext_table;

3.实验空行、首行情况

3.1 准备外部文件

vim ext_table_skip.dat
[oceanbase@appx-container test]$ cat ext_table_skip.dat
1,晓明

2,小花
3,小量

截图：外部文件中多一些空行，中间、结尾等

3.2 创建外部表测试

CREATE EXTERNAL TABLE ext_table_skip (
    id INT,
    nm VARCHAR(100)
)
LOCATION= '/home/oceanbase/test/'
 FORMAT = (  
     TYPE = 'CSV'
     --SKIP_HEADER = 1
    -- SKIP_BLANK_LINES = TRUE
     FIELD_DELIMITER = ','
     FIELD_OPTIONALLY_ENCLOSED_BY =''''
    )PATTERN ='ext_table_skip.dat';

执行截图：

3.3 查询外部表

select * from ext_table_skip;
截图：发现空行都进入表中了。

3.4 加入参数控制空行、表头，再次测试

drop table ext_table_skip;

CREATE EXTERNAL TABLE ext_table_skip (
    id INT,
    nm VARCHAR(100)
)
LOCATION= '/home/oceanbase/test/'
 FORMAT = (  
     TYPE = 'CSV'
     SKIP_HEADER = 1
     SKIP_BLANK_LINES = TRUE
     FIELD_DELIMITER = ','
     FIELD_OPTIONALLY_ENCLOSED_BY =''''
    )PATTERN ='ext_table_skip.dat';

执行截图：

此时查询：发现空行排除，且跳过了文件头1行 ok

实验验证：ok

OceanBase--相关知识

【OceanBase专栏】ODC连接、OBD常见命令

【OceanBase专栏】SECURE_FILE_PRIV 设置报错解决

【OceanBase专栏】LOAD DATA实验

【OceanBase专栏】OB租户-创建实验

项目管理--相关知识

项目管理-项目绩效域1/2-CSDN博客

项目管理-项目绩效域1/2_八大绩效域和十大管理有什么联系-CSDN博客

项目管理-项目绩效域2/2_绩效域团不策划-CSDN博客

高项-案例分析万能答案（作业分享）-CSDN博客

项目管理-计算题公式【复习】_项目管理进度计算题公式:乐观-CSDN博客

项目管理-配置管理与变更-CSDN博客

项目管理-项目管理科学基础-CSDN博客

项目管理-高级项目管理-CSDN博客

Oracle其他文档，希望互相学习，共同进步

Oracle-找回误删的表数据(LogMiner 挖掘日志)_oracle日志挖掘恢复数据-CSDN博客

oracle 跟踪文件--审计日志_oracle审计日志-CSDN博客

ORA-12899报错，遇到数据表某字段长度奇怪现象：“Oracle字符型，长度50”但length查却没有50_varchar(50) oracle 超出截断-CSDN博客

EXP-00091: Exporting questionable statistics.解决方案-CSDN博客

Oracle 更换监听端口-CSDN博客

【OceanBase专栏】OceanBase 外部表实验

OceanBase && Oracle相关文档，希望互相学习，共同进步

风123456789～-CSDN博客

1.概述

OceanBase 支持外部表（External Table），可将外部数据（如 CSV 文件）映射为数据库表。这种方式可以通过 SQL 查询外部数据，也可以用于数据导入。

语法：

CREATE EXTERNAL TABLE <table_name>
( [ <column_name> <column_type> [AS ] ]
[ , <column_name> <column_type> [AS ] ]
[ , ... ] )
LOCATION = ''
FORMAT = (
TYPE = 'CSV'
LINE_DELIMITER = '' |
FIELD_DELIMITER = '' |
ESCAPE = '' |
FIELD_OPTIONALLY_ENCLOSED_BY = '' |
ENCODING = 'charset'
NULL_IF = ('' | , '' | ...)
SKIP_HEADER =
SKIP_BLANK_LINES = { TRUE | FALSE }
TRIM_SPACE = { TRUE | FALSE }
EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
)
[ PATTERN = '<regex_pattern>' ]

column_name 外表的列名称。默认下，文件的数据列、外表定义的列是自动按顺序对应。column_type 定义外表的列类型，但不能定义约束（如，DEFAULT、NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY 等）。

as 用于手动指定列映射。当文件中的列顺序与外表的列所定义顺序不一致时，可以通过 metadata$filecol{N} 所表示的伪列来指定外表的列与文件中的第 N 列的对应关系。

如，c2 INT AS (metadata$filecol4) 表示外表的 c2 列对应文件中的第 4 列。

注意：如果指定了手动列映射，则自动映射关系会失效，且所有列都需手动定义映射。

LOCATION指定外表文件存放的路径。通常外表的数据文件存放于单独一个目录中，文件夹中可以包含子目录，在创建表时，外表会自动收集该目录中的所有文件。
本地 LOCATION 格式为 LOCATION = '[file://] local_file_path'，其中local_file_path 可以为相对路径，也可以为绝对路径。如果是相对路径，则当前目录必须为 OceanBase 数据库的安装目录；secure_file_priv 配置 OBServer 节点有权限访问的文件路径。这里只能是 secure_file_priv 路径的子路径。
远程 LOCATION 格式：

LOCATION = '{oss|cos}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'，其中 $ACCESS_ID、$ACCESS_KEY 和 $HOST 是访问 OSS/COS 时需要配置的访问信息，这些敏感的访问信息会以加密的方式存放在数据库的系统表中。

FORMAT指定外部文件的格式：
TYPE：指定外部文件的类型，当前仅支持 CSV 类型。
LINE_DELIMITER：指定 CSV 文件的行分隔符。
FIELD_DELIMITER：指定 CSV 文件的列分隔符。
ESCAPE：指定 CSV 文件的转义符号，只能为 1 个字节。
FIELD_OPTIONALLY_ENCLOSED_BY：指定 CSV 文件包裹字段值的符号。默认空。
ENCODING：指定文件的字符集编码格式，当前 Oracle 模式支持的所有字符集请参见字符集。如果不指定，默认值为 UTF8MB4。
NULL_IF：指定被当作 NULL 处理的字符串。默认值为空。
SKIP_HEADER：跳过文件头，并指定跳过的行数。
SKIP_BLANK_LINES：指定是否跳过空白行。默认值为 FALSE，表示不跳过空白的行。
TRIM_SPACE：指定是否删除文件中字段的头部和尾部空格。默认值为 FALSE，表示不删除文件中字段头尾的空格。
EMPTY_FIELD_AS_NULL：指定是否将空字符串当作 NULL 处理。默认值为 FALSE，表示不将空字符串当做 NULL 处理。

PATTERN：正则模式串，过滤 LOCATION 目录下的文件。对于每个 LOCATION 目录下的文件路径，如果能够匹配该模式串，外表会访问这个文件，否则外表会跳过这个文件。如果不指定该参数，则默认可以访问 LOCATION 目录下的所有文件。外表会将LOCATION 指定路径下满足 PATTERN 的文件列表保存在数据库系统表中，外表扫描时会根据这个列表来访问外部的文件。文件列表可以有自动更新和手动更新两种方式。

2. 实验

2.1 准备外部表文件

vim ext_table.dat

cat ext_table.dat
1,晓明
2,小花
3,小量

截图：

2.2 创建外部表

CREATE EXTERNAL TABLE ext_table (
    id INT,
    nm VARCHAR(100)
)
LOCATION= '/home/oceanbase/test/'
 FORMAT = (  
     TYPE = 'CSV'
     FIELD_DELIMITER = ','
     FIELD_OPTIONALLY_ENCLOSED_BY =''''
    )PATTERN ='ext_table.dat';

--drop table ext_table
select * from ext_table;

截图：创建成功 ok

2.3 查询外部表

SELECT * FROM ext_table;

2.4 将数据插入内部表

INSERT INTO your_table SELECT * FROM ext_table;

3.实验空行、首行情况

3.1 准备外部文件

vim ext_table_skip.dat
[oceanbase@appx-container test]$ cat ext_table_skip.dat
1,晓明

2,小花
3,小量

截图：外部文件中多一些空行，中间、结尾等

3.2 创建外部表测试

CREATE EXTERNAL TABLE ext_table_skip (
    id INT,
    nm VARCHAR(100)
)
LOCATION= '/home/oceanbase/test/'
 FORMAT = (  
     TYPE = 'CSV'
     --SKIP_HEADER = 1
    -- SKIP_BLANK_LINES = TRUE
     FIELD_DELIMITER = ','
     FIELD_OPTIONALLY_ENCLOSED_BY =''''
    )PATTERN ='ext_table_skip.dat';

执行截图：

3.3 查询外部表

select * from ext_table_skip;
截图：发现空行都进入表中了。

3.4 加入参数控制空行、表头，再次测试

drop table ext_table_skip;

CREATE EXTERNAL TABLE ext_table_skip (
    id INT,
    nm VARCHAR(100)
)
LOCATION= '/home/oceanbase/test/'
 FORMAT = (  
     TYPE = 'CSV'
     SKIP_HEADER = 1
     SKIP_BLANK_LINES = TRUE
     FIELD_DELIMITER = ','
     FIELD_OPTIONALLY_ENCLOSED_BY =''''
    )PATTERN ='ext_table_skip.dat';

执行截图：

此时查询：发现空行排除，且跳过了文件头1行 ok