Purpose
This statement is used to create an external table in a database.
Data of external tables is stored in external storage services (such as local file systems, object storage, HDFS, and MaxCompute). OceanBase AI Database stores the table definitions and access paths. External tables are read-only objects. They can be used for queries but do not support DML operations, nor the definition of constraints or the creation of indexes.
Privilege requirements
The current user must have the
CREATEprivilege to executeCREATE EXTERNAL TABLE. For more information, see Permission classification.When creating an external table by referencing a Location object, you must have the
READprivilege on that Location.GRANT READ ON LOCATION test_location_local TO user001 WITH GRANT OPTION;
Syntax
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_expr]
[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}
BYTEORDERMARK = {check | nocheck}
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"}
pattern_expr:
PATTERN [=] 'regex_pattern'
| REGEXP_PATTERN [=] 'regex_pattern'
| GLOB_PATTERN [=] 'glob_pattern'
Usage instructions
When creating an external table, you must specify the LOCATION and choose either FORMAT or PROPERTIES based on the external table type. These two options are mutually exclusive and cannot be used together:
clause |
Applicable External Table Types |
Description |
|---|---|---|
FORMAT = (...) |
File-based external tables (CSV, PARQUET, and ORC) | Specify External File Format and Parsing Options |
PROPERTIES = (...) |
ODPS external table | Specify the MaxCompute connection parameter and API mode. This is not a file format option. |
Other common clauses:
clause |
Description |
|---|---|
LOCATION |
External data paths (local, S3, HDFS, etc.). |
PATTERN |
Regular expression. Filter by LOCATION files in the directory; if not specified, all files in the directory are accessed. The matching results are written to a system table during creation and are used to determine access during scans. |
AUTO_REFRESH |
Foreign table metadata refresh strategy: OFF(Default), INTERVAL, IMMEDIATE. |
PARTITION_TYPE = USER_SPECIFIED |
Used for manual partition management. |
AS expr / metadata$filecol{N} |
Manual column mapping. Once specified, you must define a mapping for all columns; automatic sequential mapping will be invalid. |
Parameters
Parameters |
Description |
|---|---|
| table_name | The name of the external table. |
| column_name | Column name. By default, file columns and external table columns correspond one-to-one in the order they are defined. |
| column_type | Column type; cannot be defined. DEFAULT, NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY constraints. |
| AS expr | Manual column mapping. Through metadata$filecol{N} Specifies that the external table column corresponds to the Nth column in the file (where N starts at 1). For example: c2 INT AS (metadata$filecol4). Note that if manual column mapping is specified, the automatic mapping relationship will be invalid, and all columns must be manually mapped. |
| LOCATION | The data path of the external table, which typically points to a separate directory. The files in this directory (including subdirectories) are collected during creation. For more information about creating a LOCATION object, see CREATE LOCATION. |
| FORMAT | File external table format options. For more information, see format_type_options. |
| PROPERTIES | ODPS external table join options. For more information, see properties_type_options. |
| PARTITION_TYPE | When set to USER_SPECIFIED, the user manually adds or drops partitions. |
| pattern_expr | Specifies the file matching pattern for filtering. For each file path in the LOCATION directory, If a file path matches this pattern string, the external table accesses the file; otherwise, it skips the file. If this parameter is not specified, the default access permission is granted. LOCATION All files in the directory. For the path specified by LOCATION, the list of files that meet PATTERN is stored in a database system table. During external table scans, this list is used to access external files. For more information, see pattern_expr. |
| AUTO_REFRESH | Used for automatic refresh of external tables. Valid values:
|
The list of files that match the PATTERN in the path specified by LOCATION is stored in a database system table. When scanning external tables, this list is used to access the external files.
Supported path formats for LOCATION
Local path
LOCATION = '[file://]local_file_path'
local_file_path can be a relative or absolute path. If it is a relative path, the current directory must be the OceanBase installation directory. secure_file_priv specifies the accessible paths for OBServer nodes, and local_file_path must be a subpath of secure_file_priv.
Object Storage (S3)
LOCATION = 's3://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path'
$ACCESS_ID,$ACCESS_KEY, and$HOSTare the access information required to access AWS S3 and object storage services compatible with the S3 protocol (such as Alibaba Cloud OSS and Huawei Cloud OBS).s3_region: The S3 region. The credentials are stored in an encrypted format in the system table.- The current version is temporarily compatible with existing
oss://format locations (addresses in historical metadata can be parsed normally). When creating new external tables, please uses3://. Compatibility may be removed in future versions. If thehostin existing configurations is still a native OSS endpoint, it needs to be changed to an S3-compatible endpoint.
Notice
In an object storage URL, parameters are separated by &. Parameter values can contain only English letters, digits, /-_$+=, and wildcard characters. Otherwise, the setting may fail.
HDFS Path
Single NameNode:
LOCATION = 'hdfs://host:port/path'
Hadoop HA (Logical Nameservice):
LOCATION = 'hdfs://nameserviceID/path'
The client OBServer side must include the nameservice definition and failover strategy for the HA cluster.
Kerberos authentication:
Append query parameters to the URL: ?principal=...&keytab=...&krb5conf=...&configs=...
URL parameters |
Description |
|---|---|
| principal | The Kerberos login principal refers to the user undergoing authentication. |
| keytab | Key File Path |
| krb5conf | Path to the Kerberos configuration file (for example, /etc/krb5.conf) |
| configs | Additional HDFS configuration. The default value is empty. Multiple items are separated with # Separation. The Kerberos environment typically requires setting dfs.data.transfer.protection; In a high-availability environment, you also need to configure parameters such as nameservice, namenode address, and failover. |
In a high-availability plus Kerberos environment, common configs parameters (multiple parameters are separated by #):
Parameter |
Description |
|---|---|
dfs.data.transfer.protection |
Data transmission protection level, which must be consistent with the cluster configuration. |
dfs.nameservices |
HA Cluster Nameservice ID |
dfs.ha.namenodes.{nameservice} |
List of Standby IDs for NameNode |
dfs.namenode.rpc-address.{nameservice}.{nn} |
RPC Addresses of Each NameNode |
dfs.ha.automatic-failover.enabled.{nameservice} |
Whether to enable automatic failover |
dfs.client.failover.proxy.provider.{nameservice} |
The failover proxy class, typically ConfiguredFailoverProxyProvider |
The example format is as follows:
dfs.data.transfer.protection=integrity#dfs.nameservices=mycluster#dfs.ha.namenodes.mycluster=nn1,nn2#...
Notice
HA-related parameters are bound to the nameservice and must be consistent with the cluster's hdfs-site.xml. For a complete example of HA configuration, see Example 3 below and the Location configuration instructions in Load a Hive table via Catalog.
Note
Before accessing an HDFS external table, you must deploy the Java/JNI/HDFS environment on the OBServer node and configure cluster parameters such as ob_enable_java_env. For more information, see Deploy the OceanBase Java SDK environment.
File format and attribute description (format_type_options)
CSV format parameters (TYPE = 'CSV')
Parameter |
Description |
Default Value/Value Range |
|---|---|---|
LINE_DELIMITER |
Line separator | \n |
FIELD_DELIMITER |
Column separator | \t |
ESCAPE |
Escape character (1 byte) | \ |
FIELD_OPTIONALLY_ENCLOSED_BY |
field wrapper | empty |
ENCODING |
Character set | UTF8MB4 |
NULL_IF |
Strings Treated as NULL | empty |
SKIP_HEADER |
Number of Lines to Skip in the File Header | - |
SKIP_BLANK_LINES |
Whether to skip empty lines | FALSE |
TRIM_SPACE |
Trim leading and trailing spaces of fields | FALSE |
EMPTY_FIELD_AS_NULL |
Whether Empty String Is Considered NULL | FALSE |
IGNORE_LAST_EMPTY_COLUMN |
Whether to Ignore Trailing Empty Columns | TRUE |
COMPRESSION |
Optional. Specifies the file compression format. | Valid values:
|
BYTEORDERMARK
NoteThis parameter is supported starting with V4.6.2.1. |
Optional. Specifies whether to detect and skip BOM characters. If this attribute is not specified, BOM character detection is not performed. | Valid values:
|
format_type_options
TYPE = 'CSV'
The default values of the parameters are listed in the table above. The following are the constraints and considerations:
ESCAPE: The escape character for the CSV file, which must be one byte. Default value:ESCAPE ='\'.ENCODING: Valid character sets are listed in Character sets. The default is UTF8MB4.FIELD_OPTIONALLY_ENCLOSED_BY: When the file containsNULLvalues (other than the string"NULL"), this parameter must be explicitly configured and cannot be empty.Notice
When the external table data file contains
NULLvalues (non-string NULL, that is, not "NULL"), you must explicitly configure theFIELD_OPTIONALLY_ENCLOSED_BYparameter, and its value cannot be empty.
TYPE = 'PARQUET'
Specifies the external file format as Parquet.
TYPE = 'ORC'
Specifies the external file format as ORC.
properties_type_options
TYPE = 'ODPS'
This API is used to create a MaxCompute (ODPS) external table and access a remote table (non-file path external table) through the API.
Parameter |
Description |
|---|---|
| ACCESSID | Alibaba Cloud AccessKey ID, used for identity authentication |
| ACCESSKEY | Specifies the AccessKey secret corresponding to the AccessKey ID for authentication. |
| ENDPOINT | ODPS Service Connection Address |
| PROJECT_NAME | ODPS Project Name |
| SCHEMA_NAME | Optional. The name of the ODPS schema. |
| TABLE_NAME | ODPS Table Name |
| QUOTA_NAME | Optional. The quota name. |
| COMPRESSION_CODE | Optional. Compression format: ZLIB, ZSTD, LZ4, ODPS_LZ4; If not specified, compression is disabled. |
| API_MODE | API mode: tunnel_api(Default) or storage_api. |
| SPLIT | Use storage_api Sharding method during: byte or row. |
API_MODE: Specifies the API mode for calling ODPS. Valid values:
tunnel_api(default):No special network configuration required: Suitable for all deployment scenarios, as OceanBase AI Database and MaxCompute do not need to reside in the same Virtual Private Cloud (VPC).
No additional MaxCompute permissions required: Authentication is completed using only the AccessID and AccessKey; there is no need to enable MaxCompute Storage API permissions.
Applicable environments:
- OceanBase AI Database and MaxCompute are not deployed in the same VPC.
- The MaxCompute Storage API is not enabled.
- Low latency requirements for data transmission.
storage_api:Network dependency: OceanBase AI Database and MaxCompute must be deployed in the same VPC to achieve low-latency, high-throughput data transmission.
Permission dependency: You must enable the Storage API permission in MaxCompute and ensure that the AccessKey has the corresponding permissions.
Applicable environments:
- The OceanBase AI Database and MaxCompute are in the same VPC.
- The MaxCompute Storage API has been enabled.
- The data volume is extremely large or real-time requirements are high.
SPLIT: When usingstorage_api, specifies whether to split tasks bybyteorrowand distribute them to threads. Set this parameter tobytewhen the byte size of data in rows of a table varies greatly, and set it torowin other cases.
For more information about scenario implementation and partition mapping, see [ODPS external tables].
pattern_expr
PATTERN [=] 'regex_pattern': Specifies the file name or regular expression. ThePATTERNandREGEXP_PATTERNparameters are equivalent. This controls which files are loaded. Example:PATTERN = 'extdata.csv': specifies the name of the file to load, which isextdata.csv.- For multiple CSV files, such as
extdata_2024.csvandextdata_2025.csv, you can writePATTERN = 'extdata_\d{4}\.csv'.
REGEXP_PATTERN [=] 'regex_pattern': Specifies to use a regular expression to match file names. This allows for flexible matching of various file names. Example:REGEXP_PATTERN = '.*[.]csv$': Use a regular expression to filter all files with the.csvextension underLOCATION.
GLOB_PATTERN [=] 'glob_pattern': Specifies to use wildcard characters to match file names. This performs a simple match for common file types. Example:GLOB_PATTERN = '*.csv': Matches all files that end with.csv.
Usage instructions
- External tables are read-only objects and do not support DML, constraints, or indexes.
- The file list is collected based on the
LOCATIONandPATTERNparameters during creation. To add files to the directory, executeALTER EXTERNAL TABLE ... REFRESH. For more information, see ALTER EXTERNAL TABLE. - For more information about external file deletion, modification, concurrent access,
secure_file_priv, and HDFS/ODPS environments, see External tables and external files and Manage external files. - When you do not need to persist table objects and only read external files temporarily, you can use the FILES table function.
Examples
Example 1: Local CSV external table
Prepare the data. First, set the path of
secure_file_privto/home/admin/, and place the CSV fileextdata.csvcorresponding to the external table data to be imported in the/home/admin/testdirectory on the current local-connected OBServer node.The following example shows how to set the global security path.
obclient> SET GLOBAL secure_file_priv = "/home/admin/"; Query OK, 0 rows affected obclinet> \q ByeNote
Because
secure_file_privis aGLOBALvariable, you need to execute\qto exit and make it effective.The content of the CSV file is as follows:
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',53After logging in to the database as a user tenant, create an external table named
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';Query data from the external table
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
Example 2: HDFS CSV external table (without Kerberos)
Prerequisite: You have completed the Deploy Java SDK, which is required when accessing 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;
Notice
Replace ${hadoop_namenode_host} and ${hadoop_namenode_port} with the actual HDFS NameNode address and port.
Example 3: HDFS HA + Kerberos
Prerequisites:
- You have completed Deploy Java SDK.
- The Kerberos credential files, such as keytab and krb5.conf, have been deployed on each OBServer node.
- The HA parameters in
configsare consistent with the cluster'snameserviceconfiguration.
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;
Example 4: HDFS Kerberos single-node
The prerequisites are the same as those in Example 3. In a single-NameNode environment, LOCATION can be simplified to:
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';