CREATE EXTERNAL TABLE

描述

该语句用于在数据库中创建外表（External Table）。

外表的数据存储于外部存储服务（本地文件系统、对象存储、HDFS、MaxCompute 等），OceanBase 侧保存表定义与访问路径。外表为只读对象，可用于查询，不支持 DML 操作，不支持定义约束与创建索引。

权限要求

• 执行 CREATE EXTERNAL TABLE 需要当前用户拥有 CREATE 权限。详见 MySQL 模式下的权限分类。

• 引用 Location 对象创建外表时，需要拥有该 Location 的 READ 权限。

  GRANT READ ON LOCATION test_location_local TO user001 WITH GRANT OPTION;
  

版本说明

语法

CREATE EXTERNAL TABLE table_name (column_definition [, column_definition ...])
    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:
    column_name column_type [AS expr]

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'

type_orc_option:
    TYPE = 'ORC'

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"}

使用方式说明

创建外表时需指定 LOCATION，并根据外表类型选择 FORMAT 或 PROPERTIES（二者互斥，不可同时使用）：

其他常用子句：

参数说明

LOCATION 支持的路径格式

本地路径

LOCATION = '[file://]local_file_path'

local_file_path 可为相对或绝对路径；相对路径时当前目录须为 OceanBase 安装目录。secure_file_priv 配置 OBServer 可访问路径，local_file_path 须为 secure_file_priv 的子路径。

对象存储（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：

LOCATION = 'hdfs://host:port/path'

Hadoop HA（逻辑 nameservice）：

LOCATION = 'hdfs://nameserviceID/path'

客户端 OBServer 侧须包含 HA 集群的 nameservice 定义及故障转移策略。

Kerberos 认证：

在 URL 后附加查询参数：?principal=...&keytab=...&krb5conf=...&configs=...

HA + Kerberos 环境下，configs 常用配置项（多项以 # 分隔）：

示例格式：

dfs.data.transfer.protection=integrity#dfs.nameservices=mycluster#dfs.ha.namenodes.mycluster=nn1,nn2#...

文件格式与属性说明（format_type_options）

CSV 格式参数速查（TYPE = 'CSV'）

format_type_options

TYPE = 'CSV'

各参数默认值见上表。以下为约束与注意事项：

• ESCAPE：指定 CSV 文件的转义符号，只能为 1 个字节。默认值为 ESCAPE ='\'。

• ENCODING：支持字符集见 字符集；默认 UTF8MB4。

• FIELD_OPTIONALLY_ENCLOSED_BY：当文件中包含 NULL 值（非字符串 "NULL"）时，须显式配置且不可为空。

  注意

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

TYPE = 'PARQUET'

指定外部文件格式为 Parquet。

TYPE = 'ORC'

指定外部文件格式为 ORC。

properties_type_options

TYPE = 'ODPS'

用于创建 MaxCompute（ODPS）外表，通过 API 访问远端表（非文件路径外表）。

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。

场景实践与分区映射等详见 ODPS 外部表。

使用说明

• 外表为只读对象，不支持 DML、约束与索引。
• 文件列表在创建时根据 LOCATION 与 PATTERN 收集；目录下新增文件须执行 ALTER EXTERNAL TABLE ... REFRESH 更新。详见 更新外表文件列表。
• 外部文件删除、修改、并发访问、secure_file_priv、HDFS/ODPS 环境等，参见 关于外表与外部文件 与 管理外部文件。
• 无需持久化表对象、仅临时读取外部文件时，可使用 FILES 表函数。

示例

示例一：本地 CSV 外表

1. 准备数据。首先需要设置 secure_file_priv 的路径为 /home/admin/，将要导入的外表数据所对应的 CSV 文件 extdata.csv 放在当前本地连接的 OBServer 节点的 /home/admin/test 路径中。

   设置全局安全路径的示例如下。

   obclient> SET GLOBAL secure_file_priv = "/home/admin/";
   Query OK, 0 rows affected
   obclinet> \q
   Bye
   

   说明

   由于 secure_file_priv 是 GLOBAL 变量，所以需要执行 \q 退出使之生效。

   CSV 文件中的内容如下：

   1,'Dave','Smith','dsmith@outlook.com','friend',32
   2,'Xena','Johnson','xjonson@outlook.com','contact',45
   3,'Fred','Jackon','fjackson@outlook.com','co-worker',19
   4,'Alma','Tyler','atyler@outlook.com','friend',53
   

2. 用户租户登录数据库后，创建外表 contacts。

   obclient> CREATE EXTERNAL TABLE contacts (
       id    INT,
       firstname  VARCHAR(100),
       lastname   VARCHAR(100),
       email      VARCHAR(255),
       category   CHAR(30),
       age        NUMBER
      )
      LOCATION = '/home/admin/test'
      FORMAT = (
        TYPE = 'CSV'
        FIELD_DELIMITER = ','
        FIELD_OPTIONALLY_ENCLOSED_BY ='\''
       )
     PATTERN = 'extdata.csv';
   

3. 查询外表 contacts 中的数据。

   obclient> SELECT * FROM contacts;
   +----+-----------+----------+----------------------+-----------+------+
   | id | firstname | lastname | email                | category  | age  |
   +----+-----------+----------+----------------------+-----------+------+
   |  1 | Dave      | Smith    | dsmith@outlook.com   | friend    |   32 |
   |  2 | Xena      | Johnson  | xjonson@outlook.com  | contact   |   45 |
   |  3 | Fred      | Jackon   | fjackson@outlook.com | co-worker |   19 |
   |  4 | Alma      | Tyler    | atyler@outlook.com   | friend    |   53 |
   +----+-----------+----------+----------------------+-----------+------+
   4 rows in set
   

示例二：HDFS CSV 外表（无 Kerberos）

前提：已完成 Java SDK 环境部署（访问 HDFS 时需要）。

CREATE EXTERNAL TABLE test_tbl1_csv_mysql (
    id INT,
    name VARCHAR(50),
    c_date DATE
)
LOCATION = 'hdfs://${hadoop_namenode_host}:${hadoop_namenode_port}/user'
FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY = '\''
)
PATTERN = 'test_tbl1.csv';

SELECT * FROM test_tbl1_csv_mysql;

示例三：HDFS HA + Kerberos

前提：

• 已完成 Java SDK 环境部署。
• OBServer 各节点已部署 keytab、krb5.conf 等 Kerberos 凭据文件。
• configs 中 HA 参数与集群 nameservice 配置一致。

CREATE EXTERNAL TABLE test_ha (
    id INT,
    r_name VARCHAR(100),
    age INT
)
LOCATION = 'hdfs://mycluster/hadoop_ha_test?principal=ha/xxx@xxx.com&keytab=/path/to/ha.keytab&krb5conf=/path/to/krb5conf_file&configs=dfs.data.transfer.protection=integrity#dfs.nameservices=mycluster#dfs.ha.namenodes.mycluster=nn1,nn2#dfs.namenode.rpc-address.mycluster.nn1=localhost1:port#dfs.namenode.rpc-address.mycluster.nn2=localhost2:port#dfs.ha.automatic-failover.enabled.mycluster=true#dfs.client.failover.proxy.provider.mycluster=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY = '\''
)
PATTERN = 'test_simple.csv';

SELECT * FROM test_ha;

示例四：HDFS Kerberos 单节点

前提同示例三。单 NameNode 环境下 LOCATION 可简化为：

CREATE EXTERNAL TABLE partsupp (
    PS_PARTKEY    INTEGER,
    PS_SUPPKEY    INTEGER,
    PS_AVAILQTY   INTEGER,
    PS_SUPPLYCOST DECIMAL(15,2),
    PS_COMMENT    VARCHAR(199)
)
LOCATION = 'hdfs://localhost:8020/tpch_csv?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 = 'partsupp.tbl';

相关文档

• 关于外表与外部文件
• 创建和查询外表
• ODPS 外部表
• FILES 表函数
• 管理外部文件
• 更新外表文件列表
• 部署 OceanBase JAVA SDK 环境