创建外表

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

权限要求

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

创建外表

创建外表的 SQL 语句如下：

CREATE EXTERNAL TABLE table_name (column_definition_list)
    LOCATION = 'file_name'
    {FORMAT = (format_type_options)
     | PROPERTIES = (properties_type_options)}
    [PARTITION BY (column_name [, column_name ...])]
    [PARTITION_TYPE = USER_SPECIFIED]
    [PATTERN = 'regex_pattern']
    [AUTO_REFRESH = 'xxx'];

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name column_type [AS metadata$filecol{N} | external$filepos{N}]

format_type_options:
    type_csv_option
    | type_parquet_option
    | type_orc_option

type_csv_option:
    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}
    IGNORE_LAST_EMPTY_COLUMN = {TRUE | FALSE}

type_parquet_option:
    TYPE = 'PARQUET'
    [COLUMN_INDEX_TYPE = 'POSITION, NAME']

type_orc_option:
    TYPE = 'ORC'
    [COLUMN_INDEX_TYPE = 'POSITION, NAME']

properties_type_options:
    type_odps_option

type_odps_option:
    TYPE = 'ODPS'
    ACCESSID = '<string>',
    ACCESSKEY = '<string>',
    ENDPOINT = '<string>',
    PROJECT_NAME = '<string>',
    SCHEMA_NAME = '<string>',
    TABLE_NAME = '<string>',
    QUOTA_NAME = '<string>',
    COMPRESSION_CODE = '<string>',
    API_MODE = {"tunnel_api" | "storage_api"},
    SPLIT = {"byte" | "row"}

相关参数说明如下：

• col_name col_type [AS (metadata$filecol{N} | external$filepos{N})]：用于定义列。其中，AS (metadata$filecol{N}) 用于手动定义列映射，AS (external$filepos{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} 语法：适用于 CSV 格式，指定外表的列对应外部文件中的第 N 列。其中，文件中的列从 1 开始编号。

  • 使用 external$filepos{N} 语法：适用于 Parquet 和 ORC 格式，按位置索引列。需要配合 COLUMN_INDEX_TYPE = 'POSITION' 参数使用。

  例如，以下示例中，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 = '|'
    );
  

  对于 Parquet 和 ORC 格式，您可以使用 external$filepos{N} 语法：

  CREATE EXTERNAL TABLE ext_t3 (
              c1 INT AS (external$filepos1),
              c2 INT AS (external$filepos2),
              c3 VARCHAR(50) AS (external$filepos3),
              c4 DATE AS (external$filepos4)
    )
    LOCATION = 'path/to/data/'
    FORMAT = (TYPE = 'PARQUET' COLUMN_INDEX_TYPE = 'POSITION')
    PATTERN = "000000_0$";
  

  注意

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

  注意

  当使用 external$filepos{N} 语法时，必须设置 COLUMN_INDEX_TYPE = 'POSITION' 参数。

• 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 格式：

    注意

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

    • 文件在 OSS/S3 上时，格式为：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 = ( TYPE = 'CSV'...)：用于定义外部文件格式为 CSV。

  • TYPE：指定外部文件的类型。

  • LINE_DELIMITER：指定文件的行分隔符。如果不指定，默认值为 LINE_DELIMITER='\n'。

  • FIELD_DELIMITER：指定文件的列分隔符。如果不指定，默认值为 FIELD_DELIMITER='\t'。

  • ESCAPE：指定文件的转义字符。例如，ESCAPE ='*' 表示将星号（*）指定为转义字符来取代默认的转义字符（\）。如果不指定，默认值为 ESCAPE ='\'。

  • FIELD_OPTIONALLY_ENCLOSED_BY：指定文件中包裹字段值的符号。例如，ESCAPE = '"' 表示将值放在双引号之间。如果不指定，默认值为空。

    注意

    当外表数据文件中包含 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。

  • IGNORE_LAST_EMPTY_COLUMN：如果文件一行末尾是空字段（即行分隔符前是列分隔符），指定是否忽略掉该空字段。默认值为 TRUE，表示忽略掉最后一个空字段。

• FORMAT = ( TYPE = 'PARQUET'...)：用于定义外部文件格式为 PARQUET。

  • TYPE：指定外部文件的类型为 PARQUET。
  • COLUMN_INDEX_TYPE：指定列索引类型。当前支持 'POSITION' 和 'NAME'，表示按位置索引列和按名称索引列。当使用 external$filepos{N} 语法时，需要设置此参数。

• FORMAT = ( TYPE = 'ORC'...)：用于定义外部文件格式为 ORC。

  • TYPE：指定外部文件的类型为 ORC。
  • COLUMN_INDEX_TYPE：指定列索引类型。当前支持 'POSITION'，表示按位置索引列。当使用 external$filepos{N} 语法时，需要设置此参数。

• TYPE = 'ODPS'：指定外部文件的格式为 ODPS 类型。还包含以下字段：

  • ACCESSID：指定阿里云账号的 AccessKey ID，用于身份认证。

  • ACCESSKEY：指定 AccessKey ID 对应的 AccessKey Secret，用于身份验证。

  • ENDPOINT：指定 ODPS 服务的连接地址。

  • PROJECT_NAME：指定访问的目标 ODPS 项目名称。

  • SCHEMA_NAME：可选项，指定 ODPS 中的 Schema 名称。

  • TABLE_NAME：指定 ODPS 中的目标表名。

  • QUOTA_NAME：可选项，指定使用的 Quota。

  • COMPRESSION_CODE：可选项，指定数据源的压缩格式，支持 ZLIB、ZSTD、LZ4、ODPS_LZ4 四种压缩格式，不设置表示不开启压缩。

  • API_MODE：指定调用 ODPS 的 API 模式。取值如下：

    • tunnel_api（默认值）：

      • 无需特殊网络配置：适用于所有部署场景，无需 OceanBase 数据库与 MaxCompute 位于同一 VPC（虚拟私有云）内。

      • 无需 MaxCompute 额外权限：仅需提供 AccessID 和 AccessKey 即可完成认证，无需开通 MaxCompute Storage API 权限。

      • 适用环境：

        • OceanBase 数据库与 MaxCompute 未部署在同一 VPC 中。
        • 未开通 MaxCompute Storage API。
        • 数据传输对延迟要求较低。

    • storage_api：

      • 网络依赖性：要求 OceanBase 数据库与 MaxCompute 必须部署在同一 VPC 内，以实现低延迟、高吞吐量的数据传输。

      • 权限依赖性：需在 MaxCompute 中开通 Storage API 权限，并确保访问密钥（AccessKey）具备相应权限。

      • 适用环境：

        • OceanBase 数据库与 MaxCompute 同属一个 VPC 网络。
        • 已开通 MaxCompute Storage API。
        • 数据量极大或对实时性要求较高。

  • SPLIT：表示使用 storage_api 时，指定按照 byte 或 row 做任务切割分配给各个线程。当一张表各个行数据字节数差异过大的时候，SPLIT 取值 byte，而其他情况取值 row。

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

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

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

Parquet 格式外表示例

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

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

创建 ODPS JAVA SDK 外表

创建 ODPS JAVA SDK 外表，需要 OceanBase 数据库在具有 JAVA SDK 的环境当中使用。关于部署 JAVA SDK 环境的详细介绍信息，参见 部署 OceanBase 数据库 JAVA SDK 环境。

创建 ODPS JAVA SDK 外表示例如下：

CREATE EXTERNAL TABLE lineitem(
    l_orderkey BIGINT,
    l_partkey BIGINT, 
    l_suppkey BIGINT,
    l_linenumber BIGINT, 
    l_quantity DECIMAL(15,2), 
    l_extendedprice DECIMAL(15,2),
    l_discount DECIMAL(15,2),
    l_tax DECIMAL(15,2),
    l_returnflag CHAR(1),
    l_linestatus CHAR(1), 
    l_shipdate DATE, 
    l_commitdate DATE, 
    l_receiptdate DATE,
    l_shipinstruct CHAR(25),
    l_shipmode CHAR(10),
    l_comment VARCHAR(44))
    PROPERTIES = (
        TYPE = 'ODPS'
        ACCESSID = '***********'
        ACCESSKEY = '***********'
        ENDPOINT = 'http://service.cn-hangzhou.maxcompute.aliyun.com/api',
        PROJECT_NAME = 'bigdata_public_dataset',
        SCHEMA_NAME = 'tpch_10g',
        TABLE_NAME = 'lineitem',
        QUOTA_NAME = '',
        COMPRESSION_CODE = '',
        API_MODE = "storage_api",
        SPLIT = "byte"
        ); 

外表使用注意事项

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

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

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

后续操作

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

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

相关文档

• 关于外表

• 外部文件管理