OceanBase AI Database 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 AI Database 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.
The data lake capabilities currently supported by OceanBase AI Database 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, S3-compatible object storage, 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 AI Database 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 AI Database 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 the OceanBase AI Database version. For details, see Load an Iceberg table through Catalog. |
| Apache Hive | Table format in the Hadoop ecosystem | HMS Catalog | Supports read-only queries. For details, see Access Hive tables through HMS Catalog. |
External data sources
Data source |
Description |
Access method |
|---|---|---|
| MaxCompute (ODPS) | Alibaba Cloud big data computing service | ODPS Catalog and external tables |
Data file formats
Format |
Description |
Typical 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
Catalog type |
Description |
Purpose |
Documentation |
|---|---|---|---|
| ODPS | MaxCompute project catalog | Queries tables in a MaxCompute project. | ODPS Catalog and external tables |
| HMS | Hive Metastore | Queries HMS-managed Hive or Iceberg tables. | Access Hive tables through HMS Catalog |
| REST | Iceberg REST Catalog | Accesses Iceberg tables through the REST API. For the minimum supported version, see the linked topic. | Load an Iceberg table through Catalog |
Supported storage types
Object storage and distributed storage
Storage type |
URI prefix |
Description |
|---|---|---|
| AWS S3 | s3:// |
File-based external tables, URL external tables, SELECT INTO OUTFILE, and other operations |
| Object storage compatible with the S3 protocol (such as Alibaba Cloud OSS, Huawei Cloud OBS, and Tencent Cloud COS) | s3:// |
Same as above |
| HDFS | hdfs:// |
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 Catalog and 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 the OceanBase AI Database version. Tables accessed through HMS Catalog are typically read-only. For details, see Load an Iceberg table through 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 AI Database version. |
| Data file format | Primarily Parquet. |
| Metadata | Parses metadata.json, manifest files, manifest list files, and related metadata. |
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. |