创建外表

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

权限要求

创建外表需要当前用户拥有 CREATE TABLE 权限，查看当前用户权限的操作请参见查看用户权限。如果您没有 CREATE TABLE 权限，请联系管理员为您添加，为用户添加权限的相关操作请参见直接授予权限。

创建外表的 SQL 语句如下：

CREATE EXTERNAL TABLE table_name
( col_name col_type [AS (metadata$filecol{N})] 
  [ , col_name col_type [AS (metadata$filecol{N})] ]
  [ , ... ] )
LOCATION = '<string>'
FORMAT = (
  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}
)
[ PATTERN = '<regex_pattern>' ]

相关参数说明如下：

col_name col_type [AS (metadata$filecol{N})]：用于定义列。其中，AS (metadata$filecol{N}) 用于手动定义列映射。

外表支持的列类型与普通表一致，OceanBase 数据库 Oracle 模式中支持的数据类型及详细介绍请参见数据类型概述。

默认情况下，外部文件中的数据列与外表定义的列是自动按顺序对应的，即外表的第一列对应外部文件中第一列的数据。

例如，以下示例中，外表 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} 的伪列来指定外表的列对应外部文件中的第 N 列。其中，文件中的列从 1 开始编号。

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

如果需要手动定义列映射，则自动列映射功能将会失效，所有列都需要手动定义映射。
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：指定文件的行分隔符。
- FIELD_DELIMITER：指定文件的列分隔符。
- ESCAPE：指定文件的转义字符。
- FIELD_OPTIONALLY_ENCLOSED_BY：指定文件中包裹字段值的符号。例如，ESCAPE = '"' 表示将值放在双引号之间。如果不指定，默认值为空。
  
  注意
  
  当外表数据文件中包含 NULL 值（非字符串 NULL，即不是 "NULL"）时，必须显式配置 FIELD_OPTIONALLY_ENCLOSED_BY 参数，且该参数值不可为空。
- ENCODING：指定文件的字符集编码格式，当前 Oracle 模式支持的所有字符集请参见字符集。如果不指定，默认值为 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。
PATTERN：用于指定正则模式串，过滤 LOCATION 目录下的文件。对每个 LOCATION 目录下的文件，如果能够匹配该模式串，则外表就可以访问该文件，否则外表会跳过该文件。如果不指定该参数，则默认可以访问 LOCATION 目录下的所有文件。

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

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

在 OBserver 节点上，租户管理员通过本地 Unix Socket 连接集群的 Oracle 租户。

连接示例如下：
```
obclient -S /home/admin/oceanbase/run/sql.sock -uroot@sys -p********
```
通过本地 Unix Socket 连接 OceanBase 数据库的具体操作及说明，请参见 secure_file_priv。
配置数据库可以访问的路径 /home/admin/oceanbase/。
```
SET GLOBAL secure_file_priv = "/home/admin/oceanbase/";
```
命令执行成功后，需要重启会话后，修改才能生效。

重新连接数据库后，创建外表 ext_t3。

CREATE EXTERNAL TABLE ext_t3(ID NUMBER(32), NAME VARCHAR2(30),SCORE NUMBER(32))
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" NUMBER(32) GENERATED ALWAYS AS (METADATA$FILECOL1),
  "NAME" VARCHAR2(30) GENERATED ALWAYS AS (METADATA$FILECOL2),
  "SCORE" NUMBER(32) GENERATED ALWAYS AS (METADATA$FILECOL3)
)
LOCATION='file:///home/admin/oceanbase/'
PATTERN='data.csv'
FORMAT (
  TYPE = 'CSV',
  FIELD_DELIMITER = ',',
  FIELD_OPTIONALLY_ENCLOSED_BY = '"',
  ENCODING = 'utf8mb4'
)COMPRESS FOR ARCHIVE 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

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

外表使用注意事项

外表只能执行查询操作，不能执行 DML 操作。
查询外表时，如果外表所访问的外部文件已删除，系统不会报错，会返回空行。
由于外表所访问的文件由外部存储系统进行管理，当外部存储不可用时，查询外表将会报错。

后续操作

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

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

创建外表

权限要求

创建外表

注意

注意

注意

说明

注意

注意

外表使用注意事项

后续操作

相关文档