Data in an external table is stored in a storage system outside OceanBase AI Database (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 | Within OceanBase AI Database |
| 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 a system table based on the
LOCATIONandPATTERNparameters. External files are accessed according to this list during scans. - New files added to an external directory do not automatically appear in the list. You must execute ALTER EXTERNAL TABLE or refer to Manage external files.
- When an external file is deleted, queries typically 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 modifications to the query and the external file may result in unexpected results. Therefore, you should avoid modifying files during queries.
Ways to access external data
OceanBase AI Database 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 |
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, 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 AI 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 AI Database supports accessing data in HDFS through external tables.
Before using an HDFS external table, you must deploy the Deploy Java SDK 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 AI 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.