创建外表

通过 CREATE EXTERNAL TABLE 语句来创建外表。创建外表时，需要指定数据文件路径和数据文件的格式，以便读取外部文件中的数据。

权限要求

创建外表需要当前用户拥有 CREATE 权限，查看当前用户权限的操作请参见 查看用户权限。

创建外表

创建外表的 SQL 语句如下：

CREATE EXTERNAL TABLE table_name
( col_name col_type [AS (metadata$filecol{N})] 
  [ , col_name col_type [AS (metadata$filecol{N})] ]
  [ , ... ] )
LOCATION = '<string>'
FORMAT = (
  TYPE = 'CSV'
  LINE_DELIMITER = '<string>' | <expr>
  FIELD_DELIMITER = '<string>' | <expr>
  ESCAPE = '<character>' | <expr>
  FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>
  ENCODING = 'charset'
  NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...)
  SKIP_HEADER = <int>
  SKIP_BLANK_LINES = { TRUE | FALSE }
  TRIM_SPACE = { TRUE | FALSE }
  EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
)
[ PATTERN = '<regex_pattern>' ]

相关参数说明如下：

• col_name col_type [AS (metadata$filecol{N})]：用于定义列。其中，AS (metadata$filecol{N}) 用于手动定义列映射。

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

  默认情况下，外部文件中的数据列与外表定义的列是自动按顺序对应的，即外表的第一列对应外部文件中第一列的数据。

  例如，以下示例中，外表 ext_t1 的 C1 列会自动映射到外部文件的第 1 列数据；C2 列会自动映射到外部文件的第 2 列数据。

  CREATE EXTERNAL TABLE ext_t1 (
    C1 int,
    C2 int
    )
    LOCATION = 'oss://$ACCESS_ID:$ACCESS_KEY@$HOST/tpch_1g_data/lineitem/'
    FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = '|'
    );
  

  当外部文件中的列顺序与外表中定义的列顺序不一致时，可以通过形如 metadata$filecol{N} 的伪列来指定外表的列对应外部文件中的第 N 列。其中，文件中的列从 1 开始编号。

  例如，以下示例中，C1 int AS (metadata$filecol2) 表示外表 ext_t2 的 C1 列对应文件中的第 2 列；C2 int AS (metadata$filecol4) 表示外表 ext_t2 的 C2 列对应外部文件中的第 4 列。

  CREATE EXTERNAL TABLE ext_t2 (
              C1 int AS (metadata$filecol2),
              C2 int AS (metadata$filecol4)
    )
    LOCATION = 'oss://$ACCESS_ID:$ACCESS_KEY@$HOST/tpch_1g_data/lineitem/'
    FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = '|'
    );
  

  注意

  如果需要手动定义列映射，则自动列映射功能将会失效，所有列都需要手动定义映射。

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

  支持以下两种格式：

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

    • local_file_path：既可以为相对路径，也可以为绝对路径。如果要填写相对路径，则当前目录必须为 OceanBase 数据库的安装目录。

      注意

      local_file_path必须是一个目录，而不是一个文件。如果需要指定单独的一个文件，需要在 LOCATION 中指定该文件的上层目录，并通过设置 PATTERN 属性来指定该文件。

    • 对于使用本地 Location 格式的场景，在通过系统变量 secure_file_priv 配置 OceanBase 数据库有权限访问的文件路径时，要求 secure_file_priv 必须是 local_file_path 的上层目录，即 local_file_path 只能是 secure_file_priv 路径的子路径。

      系统变量 secure_file_priv 用于控制导入或导出到文件时，OceanBase 数据库可以访问的路径。更多 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：用于定义外部文件的格式。

  • TYPE：指定外部文件的类型，当前仅支持 CSV 文件。
  • LINE_DELIMITER：指定文件的行分隔符。如果不指定，默认值为 LINE_DELIMITER='\n'。
  • FIELD_DELIMITER：指定文件的列分隔符。如果不指定，默认值为 FIELD_DELIMITER='\t'。
  • ESCAPE：指定文件的转义字符。例如，ESCAPE ='*' 表示将星号（*）指定为转义字符来取代默认的转义字符（\）。如果不指定，默认值为 ESCAPE ='\'。
  • FIELD_OPTIONALLY_ENCLOSED_BY：指定文件中包裹字段值的符号。例如，ESCAPE = '"' 表示将值放在双引号之间。如果不指定，默认值为空。
  • ENCODING：指定文件的字符集编码格式，当前 MySQL 模式支持的所有字符集请参见 字符集。如果不指定，默认值为 UTF8MB4。
  • NULL_IF：指定哪些字符串被当作 NULL 处理。如果不指定，默认值为空。
  • SKIP_HEADER：指定跳过文件头，需要跳过的行数。如果不指定，默认不跳过文件头。
  • SKIP_BLANK_LINES：指定是否跳过空白行。如果不指定，默认值为 FALSE。
  • TRIM_SPACE：指定是否删除文件中字段的头和尾的空格。如果不指定，默认值为 FALSE。
  • EMPTY_FIELD_AS_NULL：指定空字符串是否被当作 NULL 处理。如果不指定，默认值为 FALSE。

• PATTERN：用于指定正则模式串，过滤 LOCATION 目录下的文件。对每个 LOCATION 目录下的文件，如果能够匹配该模式串，则外表就可以访问该文件，否则外表会跳过该文件。如果不指定该参数，则默认可以访问 LOCATION 目录下的所有文件。

假设本地机器的 /home/admin/oceanbase/ 下存放了一个 data.csv 文件，文件中的内容如下。

1,"lin",98
2,"hei",90
3,"ali",95

1. 在 OBserver 节点上，租户管理员通过本地 Unix Socket 连接集群的 MySQL 租户。

   连接示例如下：

   obclient -S /home/admin/oceanbase/run/sql.sock -uroot@sys -p********
   

   通过本地 Unix Socket 连接 OceanBase 数据库的具体操作及说明，请参见 secure_file_priv。

2. 配置数据库可以访问的路径 /home/admin/oceanbase/。

   SET GLOBAL secure_file_priv = "/home/admin/oceanbase/";
   

   命令执行成功后，需要重启会话后，修改才能生效。

3. 重新连接数据库后，创建外表 ext_t3。

   CREATE EXTERNAL TABLE ext_t3(id int, name char(10),score int)
   LOCATION = '/home/admin/oceanbase/'
   FORMAT = (
   TYPE = 'CSV'
   FIELD_DELIMITER = ','
   FIELD_OPTIONALLY_ENCLOSED_BY ='"'
   )
   PATTERN = 'data.csv';
   

外表创建成功后，可以像普通表一样使用 SHOW CREATE TABLE 语句来查看表的定义。

SHOW CREATE TABLE ext_t3;

查询结果如下：

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ext_t3 | CREATE EXTERNAL TABLE `ext_t3` (
  `id` int(11) GENERATED ALWAYS AS (metadata$filecol1),
  `name` char(10) GENERATED ALWAYS AS (metadata$filecol2),
  `score` int(11) GENERATED ALWAYS AS (metadata$filecol3)
)
LOCATION='file:///home/admin/oceanbase/'
PATTERN='data.csv'
FORMAT (
  TYPE = 'CSV',
  FIELD_DELIMITER = ',',
  FIELD_OPTIONALLY_ENCLOSED_BY = '"',
  ENCODING = 'utf8mb4'
)DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set 

也可以像普通表一样进行访问。查询外表时，系统通过外表的驱动层直接读取外部文件，并按照文件格式进行解析，转换成 OceanBase 数据库内部的数据类型后返回数据行，查询刚刚创建的外部表 lineitem 的示例如下。

SELECT * FROM ext_t3;

查询结果如下：

+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | lin  |    98 |
|  2 | hei  |    90 |
|  3 | ali  |    95 |
+----+------+-------+
3 rows in set

此外，外表也可以与普通表进行组合查询操作。假设当前数据库中有一个普通表 info，表中的数据如下所示：

+------+--------+------+
| name | sex    | age  |
+------+--------+------+
| lin  | male   |    8 |
| hei  | male   |    9 |
| li   | female |    8 |
+------+--------+------+
3 rows in set

将外部表 ext_t3 与普通表 info 组合查询的示例如下。

SELECT info.* FROM info, ext_t3 WHERE info.name = ext_t3.name AND ext_t3.score > 90;

查询结果如下：

+------+--------+------+
| name | sex    | age  |
+------+--------+------+
| lin  | male   |    8 |
| li   | female |    8 |
+------+--------+------+
2 rows in set

更多查询操作，请参见 读取数据。

外表使用注意事项

• 外表只能执行查询操作，不能执行 DML 操作。

• 查询外表时，如果外表所访问的外部文件已删除，系统不会报错，会返回空行。

• 由于外表所访问的文件由外部存储系统进行管理，当外部存储不可用时，查询外表将会报错。

后续操作

外表创建时，系统会将 LOCATION 中指定路径下匹配 PATTERN 的文件列表保存在 OceanBase 数据库的系统表中，外表扫描时会根据该列表来访问外部文件。如果外部目录中新增了其他文件，则需要执行更新外表文件的操作，将新增文件添加到外表的文件列表中，相关操作请参见 外部文件管理。

外表创建后，也可以删除，删除外表的语句与普通表相同，您使用 DROP TABLE 语句删除外表，详细操作可参考 删除表。

相关文档

• 关于外表

• 外部文件管理