CREATE EXTERNAL TABLE

描述

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

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

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

权限要求

• 执行 CREATE EXTERNAL TABLE 语句，需要当前用户拥有 CREATE 权限。有关 OceanBase 数据库权限的详细介绍，参见 MySQL 模式下的权限分类。

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

  示例如下：

  授予用于 user001 对 Location 的对象 test_location_local 的读取权限。

  obclient> GRANT READ ON LOCATION test_location_local TO user001 WITH GRANT OPTION;
  

语法

CREATE EXTERNAL TABLE table_name (column_definition_list)
    LOCATION = {'file_path' | @location_name['/path']}
    {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}]

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

参数说明

file_path

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

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

  • 文件在 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_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（默认值）：

      • 无需特殊网络配置：适用于所有部署场景，无需 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。

使用说明

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

示例

示例一

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

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

   obclient> SET GLOBAL secure_file_priv = "/home/admin/";
   obclient> \q
   

   说明

   由于 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
   

示例二

1. 假设 HDFS 上的某个文件路径下，存在 csv 文件，文件内容如下：

   $hdfs dfs -cat /user/test_tbl1.csv
   1,'Emma','2021-09-01'
   2,'William','2021-09-02'
   3,'Olivia','2021-09-03'
   

2. 创建 HDFS 外表。

   目标 hdfs 环境若未打开 kerberos 认证

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

   注意

   其中 hadoop namenode hostname 和 hadoop namenode port 指的是 HDFS 节点的主机名和端口，需要替换为实际值。

   目标 hdfs 环境若打开 kerberos 认证

   1. 环境准备 由于当前的 HDFS 对接使用，实现使用了 jni 框架，所以对应的环境需要支持部署 java 环境。OBServer 如果部署多机器节点，则对应的 OBServer 机器节点也需要进行如下的环境设置，不可仅设置某一节点。

      步骤一： 部署配置 JAVA 环境

      您可以通过 openjdk 地址下载 openjdk 8 最新版本。

      步骤二： 解压缩对应的安装包

      解压后会看到如下文件:

      $ ls $JAVA_HOME
      ASSEMBLY_EXCEPTION  bin  include  jre  lib  LICENSE  man  NOTICE  release  sample  src.zip  THIRD_PARTY_README
      

      步骤三： 部署配置依赖的 HDFS.so 动态库

      您可以通过 https://mirrors.aliyun.com/oceanbase/ 地址下载需要的依赖。 下载完成之后，根据如下命令进行安装。

      sudo rpm -Uvh
      devdeps-hdfs-sdk-3.3.6-xxxxx.xxx.xxx.rpm
      

      执行如下命令检查对应安装是否符合预期，需要有 libhdfs.so 和 libhdfs.so.0.0.0 文件，且对应的软链接正常。

      $ll /usr/local/oceanbase/deps/devel/lib
      total 376
      lrwxrwxrwx 1 root root     16 Dec 24 19:49 libhdfs.so -> libhdfs.so.0.0.0
      -rwxr-xr-x 1 root root 384632 Dec 24 19:09 libhdfs.so.0.0.0
      

      步骤四： 部署配置依赖的 jar 包路径：

      1. 下载 jar 包：访问 阿里云 OceanBase 镜像 下载jar 包。

         注意

         • OceanBase 数据库 V4.3.5 BP1 及之前版本：请下载 1.0.0 版本 devdeps-java-extensions RPM 安装包。
         • OceanBase 数据库 V4.3.5 BP2 及之后版本：请下载 1.0.1 版本 devdeps-java-extensions RPM 安装包。
         • OceanBase 数据库 V4.4.0 版本：请下载 1.0.1 版本 devdeps-java-extensions RPM 安装包。
         • OceanBase 数据库 V4.4.1 版本：请下载 1.0.2 版本 devdeps-java-extensions RPM 安装包。

      2. 安装 jar 包：下载完您需要的 jar 包之后您可以执行如下命令解压安装：

         sudo rpm -Uvh devdeps-java-extensions-x.x.x-xxxxxxxxxxxx.xxx.xxxxxx.rpm
         

      3. 检查对应安装是否符合预期:

         $ll /home/admin/oceanbase/jni_packages
         total 52756
         drwxr-sr-x 4 root root     4096 Dec 24 20:25 hadoop
         drwxr-xr-x 3 root root     4096 Dec 24 20:25 lib
         -rw-r--r-- 1 root root 54008720 Dec 24 19:52 oceanbase-odps-connector-jar-with-dependencies.jar
         

   2. 启动 OBSERVER

      在真正使用 HDFS 第三方外表之前，需要进行对应 OBServer 节点设置，设置参考如下:

      说明

      由于当前的 OBServer 支持的 jni 相关配置项无法做到灵活的及时设置及时生效，所以如果需要尝试变更相关 java 环境变量的配置项，需要重启OBServer 才可生效。以下所有配置项均为集群配置，一次设置即可，不用所有节点单独设置。

      • 使用 sys 租户执行如下命令，打开 OBServer 支持的 java 环境:

        obclient> ALTER SYSTEM SET ob_enable_java_env=true;
        

      • 执行如下命令，设置 OBServer 的 java home 路径:

        obclient> ALTER SYSTEM SET ob_java_home="/home/user/openjdk/jdk8u422-b05";
        

        说明

        路径来自于 open jdk java 安装路径。

      • 设置 java 环境启动的相关配置项:

        注意

        该配置项变更需要重启 OBServer 生效，由于当前 HDFS 对接使用的内存拷贝为 HDFS 数据流直接拷贝到 cpp 内存堆上，可以适当减少 -Xmx2048m 和 -Xms2048m 的设置。

        obclient> ALTER SYSTEM SET ob_java_opts="-Djdk.lang.processReaperUseDefaultStackSize=true -XX:+HeapDumpOnOutOfMemoryError -Xmx2048m -Xms2048m -Xloggc:/home/user/jvmlogs/gc.log -XX:+PrintGCDetails -XX:+PrintGCDateStamps -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/user/jvmlogs/heapdumps/  -XX:+UseG1GC  -XX:-CriticalJNINatives";
        

        说明

        其中，/home/user/jvmlogs 和 /home/user/jvmlogs/heapdumps为生成对应的日志文件夹路径，需要您手动创建。

      • 设置可用的 LD_LIBRARY_PATH 路径:

        说明

        由于 OBServer 支持的 jni sdk 属于拓展功能点，所以使用动态库动态加载的方式，因此需要额外配置对应的动态库路径，设置 LD_LIBRARY_PATH, 且对应 OBServer 节点都应配置才可使用。

        $ vim ~/.bashrc
        export LD_LIBRARY_PATH=/home/user/openjdk/jdk8u422-b05/jre/lib/amd64/server:/usr/local/oceanbase/deps/devel/lib
        

   3. 创建 HDFS 外表

      注意

      带 kerberos 认证配置的情况下，需要对应的 OBServer 节点部署配置相关 keytab（用户认证密钥文件） 和 krb5conf 文件。

      obclient> 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';
      

3. 查询外表数据。

   obclient> SELECT * FROM test_tbl1_csv_mysql;
   

   返回结果如下：

   +----+----------+------------+
   | id | name     | c_date     |
   +----+----------+------------+
   |  1 | Emma     | 2021-09-01 |
   |  2 | William  | 2021-09-02 |
   |  3 | Olivia   | 2021-09-03 |
   +----+----------+------------+
   3 rows in set
   

示例三

1. 假设 HDFS 上的某个文件路径下，存在 csv 文件，文件内容如下：

   $hdfs dfs -cat /hadoop_ha_test/test_simple.csv
   1,lili,19
   2,alic,20
   3,solvi,21
   

2. 启动 Observer 相关 JNI 配置项。

   JNI 配置具体信息参见 步骤二 中 目标 hdfs 环境若打开 kerberos 认证 的 环境准备 内容，或者参见 部署 OceanBase 数据库 JAVA SDK 环境。

3. 创建 HDFS 外表

   obclient> 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';
   

4. 查询外表数据。

   obclient> SELECT * FROM test_ha;
   

   返回结果如下：

   +----+--------+-------+
   | id | r_name | age   |
   +----+--------+-------+
   |  1 | lili   |    19 |
   |  2 | alic   |    20 |
   |  3 | solvi  |    21 |
   +----+--------+-------+
   3 rows in set
   

相关文档

创建外表

外部文件管理

更新外表文件列表