The data of an external table is stored in a storage system outside the OceanBase cluster (such as local directories, object storage, HDFS, or MaxCompute). The database only retains the metadata and access paths. 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:
Feature |
External table |
Regular table |
|---|---|---|
| Data location | External storage | OceanBase storage |
| Read/write support | Read-only. You can execute SELECT statements against an external table, but you cannot execute DML statements such as INSERT, UPDATE, or DELETE. |
Read/write |
| Constraints and indexes | Primary key, unique, foreign key, and other constraints are not supported. Indexes are also not supported. | Supported |
| Typical performance | Affected by the network and external system | Faster local access |
Considerations
- When creating an external table, the system writes a 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 the 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. Modifying an external file while it is being queried may produce unexpected results. Avoid modifying files during queries.
Ways to access external data
OceanBase provides multiple ways to access external data. First, choose based on whether you need to persist table objects and whether you frequently query the same batch of files:
Method |
Creates a table object |
Common use cases |
Syntax and documentation |
|---|---|---|---|
| CREATE EXTERNAL TABLE | Yes. Metadata is persisted. | Repeatedly querying the same files, joining external tables with internal tables, or managing file lists and partitions | CREATE EXTERNAL TABLE, Create and query external tables |
| FILES table function | No. Used for ad hoc queries. | Exploring data temporarily, reading data once, or using data as a LOAD DATA source |
FILES table function, URL external table (without metadata) |
| External Catalog | Connects to an external Catalog. | Querying lakehouse tables such as Hive, Iceberg, and ODPS tables, with metadata synchronized by the 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 in SELECT. For details, see URL external table. For complete parameter descriptions of file format (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. OceanBase Database supports accessing data in HDFS through external tables starting from V4.3.5 BP1.
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 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 via the external table's PROPERTIES (TYPE = 'ODPS' ...) clause. 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.