CREATE EXTERNAL TABLE

描述

该语句用来在数据库中创建一张新的外表。

外表是数据库管理系统中的一项关键功能，通常数据库中的表存放于数据库的存储空间中，而外表的数据存储于外部存储服务中。

创建外表时需要定义数据的文件路径和文件格式，之后用户可以通过外表从外部存储服务中读取文件的数据。外表是只读的，可以在查询语句使用，但是不能执行 DML 操作。外表不支持定义约束和创建索引。

语法

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

参数说明

参数	描述
table_name	要创建的外表的名称。
column_name	外表的列名称。默认情况下，文件中的数据列和外表定义的列是自动按顺序对应起来的。
column_type	定义外表的列类型，但是不能定义约束（例如，DEFAULT、NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY 等）。
AS expr	用于手动指定列映射。当文件中的列顺序与外表的列所定义顺序不一致时，可以通过 metadata$filecol{N} 所表示的伪列来指定外表的列与文件中的第 N 列的对应关系。例如，c2 INT AS (metadata$filecol4) 表示外表的 c2 列对应文件中的第 4 列。需要注意的是，如果指定了手动列映射，那么自动映射关系将会失效，而且所有的列都需要手动定义映射关系。
LOCATION = 'file_name'	指定外表文件存放的路径。通常外表的数据文件存放于单独一个目录中，文件夹中可以包含子目录，在创建表时，外表会自动收集该目录中的所有文件。详细介绍可参见下文 file_name。
FORMAT = (format_type_options)	指定外部文件格式的相关属性，使用 TYPE 指定导出 CSV、PARQUET、ORC 文件格式，TYPE 不能为空。详细介绍可参见下文 format_type_options。
PROPERTIES = (properties_type_options)	指定外部文件格式的相关属性，使用 TYPE 指定导出 ODPS 文件格式，TYPE 不能为空。详细介绍可参见下文 properties_type_options。
PATTERN	指定一个正则模式串，用于过滤 LOCATION 目录下的文件。对于每个 LOCATION 目录下的文件路径，如果能够匹配该模式串，外表会访问这个文件，否则外表会跳过这个文件。如果不指定该参数，则默认可以访问 LOCATION 目录下的所有文件。外表会将LOCATION 指定路径下满足 PATTERN 的文件列表保存在数据库系统表中，外表扫描时会根据这个列表来访问外部的文件。
PARTITION_TYPE = USER_SPECIFIED	当您需要自己手动添加和删除分区，而不是让外表自动管理分区时，需要指定 PARTITION_TYPE = USER_SPECIFIED 字段。
AUTO_REFRESH = 'xxx'	用于外表自动刷新。取值如下： OFF：默认值，表示关闭自动刷新。 INTERVAL：让该外表可以通过 dbms_external_table 来管理自动刷新规则。 IMMEDIATE：代表每次 SQL 访问外表时，需要去刷新一次该 SQL 相关的外表 META。

file_name

外表文件存放的路径有以下格式：

• 文件在本地时，本地 LOCATION 格式为：LOCATION = '[file://] local_file_path'，其中 local_file_path 可以为相对路径，也可以为绝对路径。如果填写的是相对路径，则当前目录必须为 seekdb 的安装目录；secure_file_priv 用于配置 seekdb 有权限访问的文件路径。local_file_path 只能是 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 时选择的区域信息，这些敏感的访问信息会以加密的方式存放在数据库的系统表中。

format_type_options

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

  • 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 处理。

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

• TYPE = 'PARQUET'：指定外部文件的格式为 PARQUET 类型。

• TYPE = 'ORC'：指定外部文件的格式为 ORC 类型。

properties_type_options

• 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（默认值）：

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

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

      • 适用环境：

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

    • storage_api：

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

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

      • 适用环境：

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

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

使用说明

• 当外部文件被删除时，外表访问文件列表中的文件将会不存在，这种情况下，外表会忽略不存在的文件。
• 当外部文件被修改时，外表访问外部文件的最新的数据。当外部文件的修改与外表查询并发时，可能会产生不符合预期的结果，需要避免在查询外表的同时修改外部文件。
• 当外部目录下有新增文件时，外表仅会访问文件列表中的文件，如果需要将新增文件添加到外表的文件列表中，需要执行更新外表文件操作。

示例

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

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

   SET GLOBAL secure_file_priv = ""
   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。

   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 中的数据。

   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 (0.003 sec)

相关文档

• 创建外表
• 外部文件管理
• 更新外表文件列表