ODPS catalog

OceanBase Database V4.3.5 BP2 and later support the catalog feature. This feature adds a catalog layer to the traditional Database → Table two-level model, forming a three-level architecture of Catalog → Database → Table.

With the ODPS catalog feature, you can directly query data from tables in Alibaba Cloud MaxCompute (formerly known as ODPS) without manually creating external table mappings. This feature is suitable for accelerating offline data warehouse queries.

• Direct query without ETL: You can directly access tables in MaxCompute without the need to create external tables.
• Unified metadata: The database and table structures in ODPS projects are automatically synchronized.
• Cross-source JOIN: You can perform JOIN queries between OceanBase Database internal tables and MaxCompute tables.

Supported capabilities

• Read-only queries: Supports analytical operations such as SELECT, JOIN, and GROUP BY.
• No write operations: Prohibits DML/DDL operations such as INSERT, UPDATE, and DROP TABLE.
• Partition and column pruning: Automatically pushes down filter conditions to reduce data transfer.

Limitations

For more information about environment dependencies, see Deploy OceanBase Java SDK environment.

Prerequisites

1. Permissions

   • The current user must have the CREATE CATALOG permission (MySQL-compatible mode) or the CREATE CATALOG system permission (Oracle-compatible mode).
   • The user must have the USE CATALOG permission (which can be granted to a specific catalog or globally) for queries.

2. Access credentials

   • You have obtained the AccessKey ID and Secret of MaxCompute (or an STS token).
   • The OceanBase cluster can access the MaxCompute endpoint and tunnel endpoint.

3. Authorization configuration

   • The MaxCompute project has authorized the RAM user used by OceanBase (at least Read permission). For more information, see Overview of MaxCompute storage access.

Syntax for creating an ODPS catalog

CREATE EXTERNAL CATALOG [IF NOT EXISTS] catalog_name
PROPERTIES (
    TYPE = 'ODPS',
    [ACCESSTYPE = 'aliyun' | 'sts' | 'app'],
    ACCESSID = 'your-access-id',
    ACCESSKEY = 'your-access-key',
    [STSTOKEN = 'your-sts-token'],  -- Required only when ACCESSTYPE='sts'
    ENDPOINT = 'http://service.cn-hangzhou.maxcompute.aliyun.com/api',
    TUNNEL_ENDPOINT = 'http://dt.cn-hangzhou.maxcompute.aliyun.com',
    PROJECT_NAME = 'your_odps_project',
    [QUOTA_NAME = 'your_quota'],
    [COMPRESSION = 'zlib' | 'zstd' | 'lz4' | 'odps_lz4'],
    API_MODE = {"tunnel_api" | "storage_api"},
    SPLIT = {"byte" | "row"}
    REGION = 'region_name'
);

Parameter description

API_MODE

Starting from OceanBase Database V4.3.5 BP3, the API_MODE and SPLIT parameters are supported in OceanBase Database V4.3.5:

• tunnel_api (default):

  • No special network configuration is required: applicable to all deployment scenarios, without the need for OceanBase Database and MaxCompute to be in the same VPC (Virtual Private Cloud).

  • No additional MaxCompute permissions are required: only the AccessID and AccessKey are needed for authentication, without the need to enable MaxCompute Storage API permissions.

  • Applicable environments:

    • OceanBase Database and MaxCompute are not deployed in the same VPC.
    • MaxCompute Storage API is not enabled.
    • Data transmission is not latency-sensitive.

• storage_api:

  • Network dependency: requires OceanBase Database and MaxCompute to be deployed in the same VPC to achieve low latency and high throughput data transmission.

  • Permission dependency: requires enabling the Storage API in MaxCompute and ensuring the AccessKey has the necessary permissions.

  • Quota requirements: when using the storage_api mode, the QUOTA parameter must be set to pay-as-you-go (on-demand) quota to ensure normal billing and execution of Storage API calls. For more information, see: Overview of MaxCompute Storage API.

  • Applicable environments:

    • OceanBase Database and MaxCompute are in the same VPC.
    • MaxCompute Storage API is enabled.
    • Large volumes of data or high real-time requirements.

Example

CREATE EXTERNAL CATALOG odps_prod
PROPERTIES (
    TYPE = 'ODPS',
    ACCESSID = 'LTAI5tXXXXXX',
    ACCESSKEY = 'xxxxxxxxxxxxxx',
    ENDPOINT = 'http://service.cn-hangzhou.maxcompute.aliyun.com/api',
    TUNNEL_ENDPOINT = 'http://dt.cn-hangzhou.maxcompute.aliyun.com',
    PROJECT_NAME = 'sales_analytics',
    REGION = 'cn-hangzhou'
);

Security recommendations:

• Use RAM subaccounts and follow the principle of least privilege.
• Avoid hardcoding plaintext AKs in SQL; instead, inject them using variables or a key management service.

Usage

Switch catalog

-- Method 1: Switch only the catalog
SET CATALOG odps_prod;

-- Method 2: Switch both the catalog and database
USE odps_prod.sales_db;

Query ODPS tables

-- If the catalog has been switched to odps_prod, you can directly query
SELECT * FROM user_log
WHERE dt = '20250401'
LIMIT 10;

-- Federated query (JOIN with internal tables)
SELECT o.order_id, u.city
FROM internal.test_db.orders o
JOIN users u ON o.user_id = u.id;

Metadata operations

-- View table structure
DESC odps_prod.sales_db.user_log;

-- View create table statement
SHOW CREATE TABLE odps_prod.sales_db.user_log;

-- List all catalogs in the current tenant
SHOW CATALOGS;

-- View create catalog statement
SHOW CREATE CATALOG odps_prod;

Delete a catalog

DROP CATALOG IF EXISTS odps_prod;

References

• Deploy the OceanBase JAVA SDK environment
• CREATE EXTERNAL CATALOG