创建外表

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

外表简介

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

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

前提条件

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

• 您已部署 OceanBase 集群并且创建了 MySQL 模式租户。更多有关部署 OceanBase 集群的信息，请参见 部署概述。

• 您已连接到 OceanBase 数据库的 MySQL 租户。更多连接数据库的信息，请参见 连接方式概述。

• 您已创建数据库。更多有关创建数据库的信息，请参见 创建数据库。

• 您已拥有 CREATE 权限。查看当前用户权限的相关操作请参见 查看用户权限。如果不具备该权限，请联系管理员为您授权，用户授权的相关操作请参见 直接授予权限。

注意事项

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

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

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

• 由于外部表的数据存储在外部数据源中，因此在查询时会涉及跨网络、文件系统等因素，可能会影响查询性能。因此，需要在创建外部表时选择合适的数据源和优化策略，以提高查询效率。

使用命令行创建外表

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

定义外表名

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

示例如下：

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

CREATE EXTERNAL TABLE students_csv external_options

定义列

外表的列不能定义约束，例如，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|S3}://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path' ，其中 $ACCESS_ID、$ACCESS_KEY 和 $HOST 为访问阿里云 OSS、AWS S3 以及兼容 S3 协议的对象存储所需配置的访问信息，s3_region 为使用 S3 时选择的区域信息，这些敏感的访问信息会以加密的方式存放在数据库的系统表中

  • 文件在 HDFS 上时，有以下格式：

    • 基于单节点 NameNode（NN）地址访问集群格式为：LOCATION = hdfs://localhost:port/PATH，其中 localhost 指的是 HDFS 的地址，port 指的是 HDFS 的端口号，PATH 指 HDFS 中的文件路径。

      • 带 kerberos 认证的格式为：LOCATION = 'hdfs://localhost:port/user?principal=xxx&keytab=xxx&krb5conf=xxx&configs=xxx'，其中：

        • principal：指登录认证用户。
        • keytab：指定用户认证的密钥文件路径。
        • krb5conf：指定用户使用 kerberos 环境的描述文件路径。
        • configs：指定额外的 HDFS 配置项，默认为空，但是如果是 kerberos 环境，则通常该配置项有值，需要进行配置，例如：dfs.data.transfer.protection=authentication,privacy，指定数据传输保护级别为 authentication 和 privacy。

    • 基于 Hadoop HA（高可用）的逻辑命名服务访问集群格式为：LOCATION = hdfs://nameserviceID/PATH，其中 nameserviceID 指的是 HDFS 的是 Hadoop HA 的逻辑命名服务 ID，PATH 指文件路径。

      说明

      请确保客户端 OBServer 侧的配置包含 HA 集群的 nameservice 定义及故障转移策略。

      • 带 kerberos 认证的格式为：LOCATION = 'hdfs://nameserviceID/PATH?principal=xxx&keytab=xxx&krb5conf=xxx&configs=dfs.data.transfer.protection=${string}#dfs.nameservices=${nameservice id}#dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}#dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}#dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}#dfs.ha.automatic-failover.enabled.${nameservice id}=true#dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'，其中：

        • principal：指登录认证用户，设置为非主节点 NN 的 pricipal。

        • keytab 和 krb5conf：与单节点 NN 设置一样。

        • configs：指定额外的 HDFS 配置项，需要设置多配置项，仅和 HA 配置项和安全配置项相关：

          • dfs.data.transfer.protection=${string}：对齐集群的 dfs.data.transfer.protection 的配置。
          • dfs.nameservices=${nameservice id}：指明当前 HA 集群的 namesevice（别名）。
          • dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}：指明 HA 集群的 namenode 后备 ID 列表。
          • dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}：设置说明 namenode1 具体 namenode，方便客户端路由。
          • dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}：设置说明 namenode2 具体 namenode，方便客户端路由。
          • dfs.ha.automatic-failover.enabled.${nameservice id}=true：让 HA 集群获取相关请求之后，自动获取可用 namenode 进行响应服务。
          • dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider：指示 HA 集群进行主备切换的逻辑工具类，也可自定义打包上传 HA 集群自己需要的逻辑等。

      注意

      HA 部分配置项是和 namespace 绑定，例如如下示例三的 mycluster, 注意相关配置项结合设置。

定义 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 文件中包裹字段值的符号。默认值为空。

    注意

    当外表数据文件中包含 NULL 值（非字符串 NULL，即不是 "NULL"）时，必须显式配置 FIELD_OPTIONALLY_ENCLOSED_BY 参数，且该参数值不可为空。

  • 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 类型。
• FORMAT = ( TYPE = 'ORC'... ) 用于指定外部文件的格式为 ORC 类型。

（可选）定义 PATTERN

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

（可选）定义外表分区

自动定义外表分区

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

手动定义外表分区

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

示例

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

1. 准备外部文件。

   执行以下命令，在要登录 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'
   

2. 设置导入的文件路径。

   注意

   由于安全原因，设置系统变量 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";
      

3. 重新连接租户 mysql001。

   示例如下：

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

4. 执行以下 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';
   

5. 执行以下 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
   

示例二

HDFS 外表创建示例

带 kerberos：

CREATE EXTERNAL TABLE ext_data ( 
  id INT, 
  name VARCHAR(50), 
  c_date    DATE
)
LOCATION = 'hdfs://localhost:8020/user?principal=principal_str&keytab=/path/to/keytab&krb5conf=/path/to/krb5conf_file&configs=xxx=xxx#xxx=xxx'
FORMAT = (
TYPE = 'CSV'
FIELD_DELIMITER = ','
FIELD_OPTIONALLY_ENCLOSED_BY ='"'
)
PATTERN = 'data.csv';

其中：

• principal：指定登录认证用户。
• keytab:指定用户认证的密钥文件路径。
• krb5conf:指定用户使用 kerberos 环境的描述文件路径。
• configs: 指定额外的 HDFS 配置项，默认为空，但是如果是 kerberos 环境，则通常该配置项有值，需要进行配置，例如：dfs.data.transfer.protection=authentication,privacy，指定数据传输保护级别为 authentication 和 privacy。

不带 kerberos：

CREATE EXTERNAL TABLE ext_data ( 
  id INT, 
  name VARCHAR(50), 
  c_date    DATE
)
LOCATION = 'hdfs://localhost:8020/user'
FORMAT = (
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  FIELD_OPTIONALLY_ENCLOSED_BY ='\''
  )
PATTERN = 'test_tbl1.csv';

相关文档

• 当需要删除外部表时，可以使用与删除普通表相同的方式进行操作。更多有关删除表的详细信息，请参见 删除表。
• 更多有关查看和更新外表可访问文件的信息，请参见 管理外部文件。