github-fill

创建外表

更新时间：2025-05-07 10:31:33

编辑

本文将向您介绍如何使用 SQL 语句来创建外表，同时介绍创建外表的前提条件、外表简介和注意事项等，并提供一些示例。

外表简介

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

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

前提条件

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

您已部署 OceanBase 集群并且创建了 MySQL 模式租户。更多有关部署 OceanBase 集群的信息，请参见部署概述。
您已连接到 OceanBase 数据库的 MySQL 租户。更多连接数据库的信息，请参见连接方式概述。
您已创建数据库。更多有关创建数据库的信息，请参见创建数据库。
您已拥有 CREATE 权限。查看当前用户权限的相关操作请参见查看用户权限。如果不具备该权限，请联系管理员为您授权，用户授权的相关操作请参见直接授予权限。

注意事项

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

使用命令行创建外表

请使用 CREATE EXTERNAL TABLE 语句创建外表。

定义外表名

创建外表时，需要先为外表命名，为了避免混淆和歧义，建议在给外部表命名时使用特定的命名规则或前缀来区分普通表和外部表。例如，外表的名称后缀可以为 _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|S3}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'

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

注意

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

定义 FORMAT

FORMAT = ( TYPE = 'CSV'... ) 用于指定外部文件的格式为 CSV 类型。有以下参数：
- TYPE：指定外部文件的类型。
- 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 处理。
FORMAT = ( TYPE = 'PARQUET'... ) 用于指定外部文件的格式为 PARQUET 类型。

（可选）定义 PATTERN

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

（可选）定义外表分区

自动定义外表分区

外表会根据分区键定义的表达式，计算并添加分区。您在查询时，可以指定分区键的值或者范围。此时会进行分区裁剪，外表只会读取该分区下的文件。

手动定义外表分区

当您需要自己手动添加和删除分区，而不是让外表自动管理分区时，需要指定 PARTITION_TYPE = USER_SPECIFIED 字段。

示例

注意

示例中涉及 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。