The data of an external table is stored in a storage system outside the OceanBase cluster (such as a local directory, object storage, HDFS, or MaxCompute). 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 an ordinary table, but it is read-only and does not support DML, constraints, or indexes.
Differences from an ordinary table:
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、DELETEDML. |
Read/Write |
| Constraints and indexes | Does not support constraints such as primary keys, uniqueness constraints, or foreign key constraints, and does not support creating indexes. | Supported |
| Typical performance | Affected by network and external systems | Faster local access |
Considerations
- When creating an external table, the system writes the list of matching files to the system table based on the
LOCATIONandPATTERNclauses. External files are accessed according to this list during scans. - New files added to an external directory do not appear in the list automatically. You must execute Update the external table file list or refer to Manage external files.
- When an external file is deleted, queries usually do not report an error; instead, the corresponding file returns an empty result.
- When an external file is modified, queries read the latest content. Concurrent modification of the file by queries and external processes may result in unexpected results. Therefore, you should avoid modifying files during queries.
Ways to access external data
OceanBase provides multiple ways to access external data. First, choose the method based on whether you need to persist table objects and whether you frequently query the same batch of files:
Method |
Whether to create a table object |
Typical Scenarios |
Syntax and documentation entry |
|---|---|---|---|
| CREATE EXTERNAL TABLE | Yes, metadata is persisted. | 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 query | Ad hoc queries, one-time reads, and LOAD DATA data sources | FILES table function, URL external table (without metadata) |
| External Catalog | Connect to an external directory | Query lakehouse tables such as Hive, Iceberg, and ODPS, with metadata synchronized by Catalog. | CREATE EXTERNAL CATALOG syntax, Catalogs and external tables |
Note
In scenarios without metadata, besides using FILES(), you can also directly read files using the Location URL format in SELECT. For details, see URL external table. For complete parameter descriptions of file formats (FORMAT), refer to CREATE EXTERNAL TABLE.
HDFS external tables and ODPS external tables
OceanBase Database supports accessing different types of external data sources through external tables, including file data in external storage systems and table data in external data platforms. Among these, HDFS belongs to external storage systems, and ODPS (MaxCompute) belongs to external data platforms.
HDFS external tables
The Hadoop Distributed File System (HDFS) is a distributed storage system in the Hadoop ecosystem. Starting from V4.3.5 BP1, OceanBase Database supports accessing data in HDFS through external tables.
Before using an HDFS external table, you need to deploy the OceanBase Database Java SDK environment and configure related parameters such as ob_enable_java_env. For syntax details, refer to the LOCATION and FORMAT sections in CREATE EXTERNAL TABLE.
ODPS (MaxCompute) external tables
ODPS (MaxCompute) is a data platform designed for large-scale data processing. OceanBase Database supports accessing data in ODPS through external tables.
When using it, you can specify the external data source using the PROPERTIES (TYPE = 'ODPS' ...) clause in the external table definition. API_MODE can be set to storage_api or tunnel_api. For syntax details, refer to the properties_type_options section in CREATE EXTERNAL TABLE. For usage instructions and examples, see ODPS external tables.