创建外表

创建外表

更新时间：2025-05-06 18:16:42

编辑

本文档将介绍如何使用 SQL 语句来创建外表。

外表简介

外表是指一个逻辑上的表对象，它对应的实际数据存储位置并不在数据库内部，而是存储于外部存储服务中。

更多外表的信息，请参见关于外表。

前提条件

在创建外表前，您需要确认以下事项：

请确认您已连接到 OceanBase 数据库，连接数据库的操作请参见连接方式概述。

说明

当前登录租户所属的租户模式可以由 sys 租户通过查询 oceanbase.DBA_OB_TENANTS 视图进行确认。
创建外表需要当前用户拥有 CREATE 权限，查看当前用户权限的操作请参见查看用户权限。

注意事项

外表只能执行查询操作，不能执行 DML 操作。
查询外表时，如果外表所访问的外部文件已删除，系统不会报错，会返回空行。
由于外表所访问的文件由外部存储系统进行管理，当外部存储不可用时，查询外表将会报错。
由于外部表的数据存储在外部数据源中，因此在查询时会涉及跨网络、文件系统等因素，可能会影响查询性能。因此，需要在创建外部表时选择合适的数据源和优化策略，以提高查询效率。

创建外表语法

CREATE EXTERNAL TABLE 语句通常采用以下形式：：

CREATE EXTERNAL TABLE table_name (column_options [AS (metadata$filecol{N})])
LOCATION = '<string>'
FORMAT = (format_options)
[PATTERN = '<regex_pattern>'];
;

更多 CREATE EXTERNAL TABLE 语句的详细信息，请参见 CREATE EXTERNAL TABLE。

参数解释：

table_name: 外表名。
column_options: 外表的列定义，包括列名称和列的数据类型。
[AS (metadata$filecol{N})]：可选项，用于手动定义列映射。

说明

默认情况下，外部文件中的数据列与外表定义的列是自动按顺序对应的，即外表的第一列对应外部文件中第一列的数据。当外部文件中的列顺序与外表中定义的列顺序不一致时，可以通过形如 `metadata$filecol{N}` 的伪列来指定外表的列对应外部文件中的第 N 列。其中，文件中的列从 1 开始编号。
LOCATION：用于指定外部文件存放的路径。
FORMAT：指定外部文件的格式。
PATTERN：指定一个正则模式串，用于过滤 LOCATION 目录下的文件。

定义外表名

创建外表时，需要先为外表命名，为了避免混淆和歧义，建议在给外部表命名时使用特定的命名规则或前缀来区分普通表和外部表。例如，外表的名称后缀可以为 _csv。

示例如下：

创建一个有关学生信息的外表时，可以命名为 students_csv。

CREATE EXTERNAL TABLE students_csv external_options

注意

因为外表的其他属性未添加，所以上面的 SQL 语句不能运行。

定义列

外表的列不能定义约束，例如，DEFAULT、NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY 等。

外表支持的列类型与普通表一致。OceanBase 数据库 MySQL 模式中支持的数据类型及详细介绍请参见数据类型概述。

定义 LOCATION

LOCATION 选项用来指定外表文件存放的路径。通常外表的数据文件存放于单独一个目录中，文件夹中可以包含子目录，在创建表时，外表会自动收集该目录中的所有文件。

OceanBase 数据库支持以下两种路径格式：

本地 Location 格式：LOCATION = '[file://] local_file_path'

注意

对于使用本地 Location 格式的场景，需设置系统变量 secure_file_priv 配置可以访问的路径。更多信息，请参见 secure_file_priv。
远程 Location 格式：LOCATION = '{oss|cos}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'

$ACCESS_ID、$ACCESS_KEY 和 $HOST 为访问阿里云 OSS 或腾讯云 COS 所需配置的访问信息，这些敏感的访问信息会以加密的方式存放在数据库的系统表中。

注意

使用对象存储路径时，对象存储路径的各项参数由 & 符号进行分隔，请确保您输入的参数值中仅包含英文字母大小写、数字、/-_$+= 以及通配符。如果您输入了上述以外的其他字符，可能会导致设置失败。

定义 FORMAT

FORMAT 选项用来指定外部文件的格式。有以下参数：

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

（可选）定义 PATTERN

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

示例

注意

示例中涉及 IP 的命令做了脱敏处理，在验证时应根据自己机器真实 IP 填写。

以下将以外部文件的位置在本地和在 OceanBase 数据库 MySQL 模式中创建外表为例，步骤如下：

准备外部文件。

执行以下命令，在要登录 OBServer 节点所在机器的 /home/admin 目录下，创建文件 test_tbl1.csv。
```
[admin@xxx /home/admin]# vi test_tbl1.csv
```
文件的内容如下：
```
1,'Emma','2021-09-01'
2,'William','2021-09-02'
3,'Olivia','2021-09-03'
```
设置导入的文件路径。

注意

由于安全原因，设置系统变量 secure_file_priv 时，只能通过本地 Socket 连接数据库执行修改该全局变量的 SQL 语句。更多信息，请参见 secure_file_priv。
1. 执行以下命令，登录到 OBServer 节点所在的机器。
```
ssh admin@10.10.10.1
```
2. 执行以下命令，通过本地 Unix Socket 连接方式连接租户 mysql001。
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
```
3. 执行以下 SQL 命令，设置导入路径为 /home/admin。
```
SET GLOBAL secure_file_priv = "/home/admin";
```

重新连接租户 mysql001。

示例如下：

obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -A -Dtest

执行以下 SQL 命令，创建外表 test_tbl1_csv。

CREATE EXTERNAL TABLE test_tbl1_csv ( 
    id INT, 
    name VARCHAR(50), 
    c_date    DATE
    )
    LOCATION = '/home/admin'
    FORMAT = (
      TYPE = 'CSV'
      FIELD_DELIMITER = ','
      FIELD_OPTIONALLY_ENCLOSED_BY ='\''
      )
    PATTERN = 'test_tbl1.csv';

执行以下 SQL 命令，查看外表 test_tbl1_csv 数据。

SELECT * FROM test_tbl1_csv;

返回结果如下：

+------+---------+------------+
| id   | name    | c_date     |
+------+---------+------------+
|    1 | Emma    | 2021-09-01 |
|    2 | William | 2021-09-02 |
|    3 | Olivia  | 2021-09-03 |
+------+---------+------------+
3 rows in set