The data of an external table is stored in a storage system outside the OceanBase cluster (such as a local directory, object storage, or HDFS). The database only stores the metadata and access path. After an external table is created, it can be used for operations such as SELECT, JOIN, and aggregation like a regular table, but it is read-only and does not support DML, constraints, or indexes.
Differences from regular tables:
Features |
external table |
Regular Table |
|---|---|---|
| Data Location | External storage | OceanBase Database |
| Read/Write | External table read-only: YesSELECT, and cannot be executed on it.INSERT、UPDATE、DELETEand other DML operations. |
Read/Write |
| Constraints and indexes | Not supported | Supported |
| Typical performance | Affected by network and external systems | Faster local access |
Considerations
- Files are collected based on the
LOCATIONandPATTERNparameters during creation. To add new files, you need to refresh the collection. For more information, see Manage external files and ALTER EXTERNAL TABLE. - For an external table partition with
PARTITION_TYPE = USER_SPECIFIED, you can add or delete partitionLOCATIONSusing ALTER EXTERNAL TABLE. Note that the ALTER capabilities in MySQL-compatible mode are subject to the corresponding documentation. - Deleted external files do not cause errors during queries; concurrently modifying external files may result in unexpected outcomes.
External data access methods
Method |
Whether to create a table object |
Typical Scenarios |
Syntax and documentation entry |
|---|---|---|---|
| CREATE EXTERNAL TABLE | Yes | Requires frequent queries, JOINs with internal tables, and management of file lists and partitions | CREATE EXTERNAL TABLE, Create and query external tables |
| FILES TABLE Function | No | Ad hoc exploration and one-time reading | FILES table function, URL external table (without metadata) |
Note
The complete parameters for the file format (FORMAT) are specified in the CREATE EXTERNAL TABLE statement. Features such as External Catalog are primarily documented in the MySQL-compatible mode documentation. For Oracle-compatible mode, refer to the actual supported features.
HDFS external tables
To access HDFS, you must deploy the OceanBase Database Java SDK environment and configure items such as ob_enable_java_env. For details, see CREATE EXTERNAL TABLE. For troubleshooting, see Troubleshooting guide for external tables.