OceanBase supports access to external data through external tables and External Catalogs. Supported data sources include files in CSV, Parquet, and ORC formats; Hive and Iceberg lake tables; and MaxCompute (ODPS).
You can use OceanBase to query and analyze data stored in external systems such as object storage, HDFS, and MaxCompute, and perform federated analysis across external and internal tables.
Note
Data lake features are primarily available in MySQL-compatible mode. External Catalog is currently supported only in MySQL-compatible mode. For syntax and version requirements, see the SQL reference topics under 700.reference. For integration procedures, see the related topics in this directory.
The data lake capabilities currently supported by OceanBase include:
- Access to Hive and Iceberg lake tables through External Catalogs
- Access to external files in CSV, Parquet, and ORC formats
- Integration with ODPS and HMS Catalogs
- Access to local file systems, OSS, S3, HDFS, and other external storage systems
- Queries, supported write operations, and exports involving external data
- Management of external tables and External Catalogs
Data lake access model
OceanBase accesses external data through the following layers:
- Storage layer: The location where data is stored, such as OSS, S3, HDFS, or a local file system.
- File format layer: The format in which data files are encoded, such as Parquet, ORC, or CSV.
- Lake table format layer: The format used to organize tables and manage their metadata, such as Iceberg or Hive.
- Catalog layer: The external metadata service, such as HMS, REST, FILESYSTEM for Iceberg, or ODPS Catalog.
- Access layer: The external table or External Catalog through which external data is integrated into the OceanBase SQL query system.
MaxCompute (ODPS) is an external data warehouse service. You can access it through an ODPS Catalog or an ODPS external table configured with PROPERTIES (TYPE = 'ODPS' ...). MaxCompute is not a lake table format such as Iceberg or Hive.
Supported data lake formats
Lake table formats
Format |
Description |
Access method |
Description |
|---|---|---|---|
| Apache Iceberg | Open source table format | External Catalog, such as HMS, REST, or FILESYSTEM | Primarily supports read-only queries. Write support varies by Catalog type and OceanBase version. For details, see Load Iceberg tables by using Catalog. |
| Apache Hive | Table format in the Hadoop ecosystem | HMS Catalog (V4.4.1 or later) | Supports read-only queries. For details, see Load Hive data by using Catalog. |
External data sources
Data source |
Description |
Access method |
|---|---|---|
| MaxCompute (ODPS) | Alibaba Cloud big data computing service | ODPS Catalog for project-level, read-only access, or an ODPS external table for single-table access with INSERT support |
Data file formats
Format |
Description |
Common use cases |
|---|---|---|
| Apache Parquet | Columnar storage | File-based external tables, HMS/Hive tables, underlying Iceberg data files, and SELECT INTO OUTFILE |
| Apache ORC | Columnar storage | File-based external tables, Hive tables, and SELECT INTO OUTFILE |
| CSV | Text format | File-based external tables, SELECT INTO OUTFILE, LOAD DATA, and URL external tables (FILES()) |
File formats and lake table formats belong to different layers. For example, an Iceberg table typically stores its underlying data in Parquet files.
Supported Catalog types
Note
The current CREATE EXTERNAL CATALOG reference topic primarily describes how to use TYPE = 'ODPS'. ODPS Catalog requires OceanBase Database V4.3.5 BP2 or later, and HMS Catalog requires V4.4.1 or later. For details about HMS Catalog, see Load Hive data by using Catalog.
Catalog type |
Description |
Purpose |
Documentation |
|---|---|---|---|
| ODPS | MaxCompute project catalog | Queries tables in a MaxCompute project. Read-only. Requires V4.3.5 BP2 or later. | ODPS Catalog |
| HMS | Hive Metastore | Queries HMS-managed Hive or Iceberg tables. Read-only. Requires V4.4.1 or later. | Load Hive data by using Catalog |
| REST | Iceberg REST Catalog | Accesses Iceberg tables through the REST API. For the minimum supported version, see the linked topic. | Load Iceberg tables by using Catalog |
Supported storage types
Object storage and distributed storage
Storage type |
URI prefix |
Description |
|---|---|---|
| Alibaba Cloud OSS | oss:// |
File-based external tables, URL external tables, SELECT INTO OUTFILE, and other operations |
| AWS S3 | s3:// |
Same as OSS |
| HDFS | hdfs:// |
V4.3.5 BP1 or later; requires deployment of the Java SDK environment |
| Local file system | file:// or a path |
Subject to the secure_file_priv restriction |
Note
Azure Blob (azblob://) is currently used in scenarios such as backup and archiving. For information about whether external table LOCATION supports Azure Blob, see CREATE EXTERNAL TABLE.
Locations and access credentials
When you create an external table or a location, configure access credentials such as access_id and access_key. You can specify the credentials in URL parameters or configure them in advance by using CREATE LOCATION. For the location syntax, see CREATE LOCATION.
Supported data operations
Read operations (SELECT)
Operation |
Applicable objects |
Description |
|---|---|---|
SELECT |
File-based external tables, URL external tables, and External Catalog tables | For the supported scope of each type, see the corresponding topic. |
| Partition pruning | Hive and Iceberg tables accessed through a Catalog | Specify partition columns in the WHERE clause to reduce the amount of data scanned. |
| Time travel | Iceberg tables | Supported in specific scenarios. |
JOIN |
The preceding external objects and internal tables | Supports federated queries. |
Write operations and exports
Operation |
Applicable objects |
Description |
|---|---|---|
INSERT INTO |
ODPS external tables | Write data to remote tables via the MaxCompute API; file-based external tables are read-only. |
INSERT INTO |
Iceberg tables | Support depends on the Catalog type and OceanBase version. Tables accessed through HMS Catalog are typically read-only. For details, see Load Iceberg tables by using Catalog. |
SELECT INTO OUTFILE |
CSV, Parquet, and ORC | Exports data to object storage or a local file. |
CREATE TABLE AS SELECT |
— | Creates an internal table from external data. |
External table and Catalog management
Operation |
Description |
|---|---|
CREATE EXTERNAL TABLE |
Create a file external table or an ODPS external table. |
CREATE EXTERNAL CATALOG |
Creates an External Catalog. |
ALTER EXTERNAL TABLE |
Refresh the file list, manage partitions, and so on. |
DROP TABLE / DROP CATALOG |
Drops an external table or a Catalog. |
AUTO_REFRESH |
Supports OFF, IMMEDIATE, and INTERVAL. |
Iceberg feature support
The following capabilities apply to Iceberg tables accessed through an External Catalog. The specific supported scope varies by Catalog type and version:
Feature |
Support |
|---|---|
| Partitions and partition transforms | Supports reading partitions that use transforms such as year(), bucket(), and truncate(). |
| Delete files | Support for position deletes, equality deletes, and related features depends on the OceanBase version. |
| Data file format | Primarily Parquet. |
| Metadata | Parses metadata.json, manifest files, manifest list files, and related metadata. |
Capability boundaries
Known limitations
Limitation |
Description |
|---|---|
| File-based external tables | Read-only. DML, constraints, and indexes are not supported. |
| External Catalog | Objects in a Catalog are read-only. Operations such as INSERT and DROP TABLE are not supported. To write data to ODPS, use an ODPS external table. |
INSERT OVERWRITE |
Not supported for Iceberg tables. |
| Writes to Iceberg partitions | Partition writes using INSERT INTO are subject to restrictions. |
| COS external table | Creation is no longer supported. |
| Catalog mode | Currently supported only in MySQL-compatible mode. |