OceanBase supports accessing external data through external tables and external catalogs, covering data sources such as file-based data (CSV, Parquet, ORC), Hive/Iceberg lake tables, and MaxCompute (ODPS).
You can query and analyze data stored in external systems such as object storage, HDFS, or MaxCompute within OceanBase, and perform federated analysis with internal tables.
Note
Data lake capabilities are primarily supported in MySQL-compatible mode; Catalog capabilities are currently only supported in MySQL-compatible mode. For specific syntax and version boundaries, refer to the SQL reference documentation under 700.reference. For access steps, see the sub-documents in this directory.
The data lake capabilities currently supported by OceanBase include:
- Access to lake table formats (Hive, Iceberg via External Catalog)
- Access to external file formats (CSV, Parquet, ORC)
- Integration with external catalogs (ODPS Catalog; HMS Catalog)
- Access to external storage (local, OSS, S3, HDFS, etc.)
- Querying, partial writing, and exporting of external data
- Management of external tables and external catalogs
Data Lake Access Model
When OceanBase accesses external data, it involves the following layers:
- Storage Layer: The actual location where data is stored, such as OSS, S3, HDFS, or local file systems.
- File Format Layer: The encoding method of the data files, such as Parquet, ORC, CSV.
- Lake Table Format Layer: The organization and metadata management methods of tables, such as Iceberg, Hive.
- Catalog Layer: External metadata services, such as HMS, REST, FILESYSTEM (for Iceberg), ODPS Catalog.
- Access Layer: Incorporates external data into OceanBase's SQL query system via external tables or External Catalogs.
MaxCompute (ODPS) is an external data warehouse service. It is accessed via ODPS Catalog or ODPS external tables (PROPERTIES (TYPE = 'ODPS' ...)), and does not belong to the same category as Iceberg/Hive lake table formats.
Supported Data Lake Formats
Lake Table Format
Format |
Description |
Access method |
Description |
|---|---|---|---|
| Apache Iceberg | Open Source Table Format | External Catalog (HMS, REST, FILESYSTEM, etc.) | Read-only queries are the primary use case. Write capability varies by catalog type and version. For details, see Load an Iceberg table via a catalog. |
| Apache Hive | Hadoop ecosystem table | HMS Catalog(V4.4.1+) | Read-only query; For more information, see Load a Hive table via Catalog. |
External Data Sources
data source |
Description |
Access method |
|---|---|---|
| MaxCompute(ODPS) | Alibaba Cloud Big Data Computing Service | ODPS Catalog (Project-level, read-only) or ODPS external table (Single table, supportsINSERT) |
File Format
Format |
Description |
Typical Scenarios |
|---|---|---|
| Apache Parquet | Columnar storage | File external tables, HMS/Hive tables, Iceberg underlying files,SELECT INTO OUTFILE |
| Apache ORC | Columnar storage | File external tables, Hive tables,SELECT INTO OUTFILE |
| CSV | Text format | File external tables,SELECT INTO OUTFILE、LOAD DATA, URL external tables (FILES()) |
File formats and lake table formats belong to different layers. For example, Iceberg tables typically use Parquet for data storage at the underlying layer.
Supported Catalog Types
Notice
The current CREATE EXTERNAL CATALOG SQL reference document primarily describes the usage of TYPE = 'ODPS'. To create an ODPS Catalog, you need OceanBase Database V4.3.5 BP2 or later; to create an HMS Catalog, you need V4.4.1 or later. For detailed information on HMS Catalog, see Load Hive tables through Catalog.
Catalog Type |
Description |
Purpose |
Documentation |
|---|---|---|---|
| ODPS | MaxCompute project directory | Query tables in a MaxCompute project (read-only, V4.3.5 BP2+) | ODPS Catalog |
| HMS | Hive Metastore | Query Hive/Iceberg tables managed by HMS (read-only, V4.4.1+) | Load Hive tables via catalog |
| REST | Iceberg REST Catalog | Accessing an Iceberg table via REST API (For the minimum version, see the document on loading an Iceberg table via Catalog.) | Load an Iceberg table via Catalog |
Supported storage types
Object storage and distributed storage
Storage type |
URI prefix |
Description |
|---|---|---|
| Alibaba Cloud OSS | oss:// |
File external tables, URL external tables,SELECT INTO OUTFILEetc. |
| AWS S3 | s3:// |
Same as above |
| HDFS | hdfs:// |
V4.3.5 BP1 or later; requires deployment of the Java SDK environment |
| Local File System | file://or path |
Affected bysecure_file_privLimitations |
Note
Azure Blob (azblob://) is currently used in scenarios such as backup/archiving. Whether the external table LOCATION supports it is subject to the specification in CREATE EXTERNAL TABLE.
Location and access credentials
When creating an external table or location, you need to configure access credentials (such as access_id and access_key). These can be configured in advance via URL parameters or using the CREATE LOCATION statement. For the syntax of Location, refer to CREATE LOCATION.
Supported data operations
Read operations (SELECT)
Operation |
Applicability |
Description |
|---|---|---|
SELECTQuery |
File external tables, URL external tables, and External Catalog tables | For more information about the supported types, see the corresponding subdocuments. |
| Partition Pruning | Hive / Iceberg (via Catalog) | InWHERESpecify partitioning columns to narrow down the scan. |
| Time Travel | Iceberg | Supported Scenarios |
JOIN |
The preceding external objects and internal tables | Supports federated queries |
Write operations and exports
Operation |
Applicability |
Description |
|---|---|---|
INSERT INTO |
ODPS external tables | Write data to a remote table by using the MaxCompute API. File external tables are read-only. |
INSERT INTO |
Iceberg Table | Some catalog types and versions may be supported; HMS Catalog is usually read-only. For details, see Load an Iceberg table via a catalog. |
SELECT INTO OUTFILE |
CSV、Parquet、ORC | Export to object storage or local file |
CREATE TABLE AS SELECT |
— | Create internal tables 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 |
Create External Directory |
ALTER EXTERNAL TABLE |
Refresh the file list, manage partitions, and so on. |
DROP TABLE / DROP CATALOG |
Drop External Table or Catalog |
AUTO_REFRESH |
SupportedOFF、IMMEDIATE、INTERVAL |
Iceberg feature support
The following capabilities apply to Iceberg tables accessed via External Catalog. The specific supported scope varies by Catalog type and version:
Features |
Supported operations |
|---|---|
| Partitioning and Partition Transform | Supports reading data from files containingyear()、bucket()、truncate()Partitioning transformed data |
| Delete File | Capabilities such as Position Delete and Equality Delete are subject to the current version. |
| Data File Format | Primarily in Parquet format |
| metadata | Parsemetadata.json、manifest、manifest-listetc. |
Capability boundaries
Known limitations
Limitations |
Description |
|---|---|
| File-based external table | Read-only, no DML, constraints, or indexes supported |
| External Catalog | Objects under the Catalog are read-only and do not supportINSERT、DROP TABLE(For writing to ODPS, use an external table of ODPS.) |
INSERT OVERWRITE |
Iceberg tables are not supported. |
| Iceberg Partition Writing | INSERT INTOWrite operations to partitions of an Iceberg table are limited. |
| COS external table | Creation is no longer supported. |
| Catalog mode | Currently only MySQL-compatible mode |