ODPS Catalog

OceanBase Database supports ODPS Catalog starting from V4.3.5 BP2 (MySQL-compatible mode).

With ODPS Catalog, you can directly query table data in Alibaba Cloud MaxCompute (formerly ODPS) without manually creating external table mappings. It is suitable for offline data warehouse query acceleration scenarios.

• No need for ETL queries: Directly access MaxCompute tables, eliminating the cumbersome operation of creating external tables.
• Unified metadata: Automatically synchronize databases and table structures in the ODPS project.
• Cross-source JOIN: Supports joint queries with internal OceanBase tables.

Supported capabilities

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

Limitations

For detailed information on environment dependencies, see Deploy the OceanBase Database Java SDK environment.

Prerequisites

1. Privilege requirements

   • The current user must have Catalog-related privileges such as CREATE CATALOG and USE CATALOG (MySQL-compatible mode). Catalog capabilities are currently supported only in MySQL-compatible mode.

2. Access credentials

   • Have obtained the MaxCompute AccessKey ID/Secret (or 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 Open Storage.

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'
);

Parameters

API_MODE

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

• tunnel_api (default value):

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

  • No additional MaxCompute permissions required: Authentication is completed with only an AccessID and AccessKey; there is no need to enable the MaxCompute Storage API permission.

  • Application scenarios:

    • OceanBase Database and MaxCompute are not deployed in the same VPC.
    • The MaxCompute Storage API is not enabled.
    • Data transmission has low latency requirements.

• storage_api:

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

  • Permission dependency: The Storage API permission must be enabled in MaxCompute, and the access key (AccessKey) must have the corresponding permissions.

  • Quota requirement: When using the storage_api mode, the QUOTA parameter must be set to pay-as-you-go (pay-per-use quota) to ensure proper billing and execution of Storage API calls. For details, see: Overview of MaxCompute Storage Open Storage.

  • Application scenarios:

    • OceanBase Database and MaxCompute belong to the same VPC network.
    • The MaxCompute Storage API is enabled.
    • The data volume is extremely large or real-time requirements are high.

Creation 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 a RAM sub-account and follow the principle of least privilege.
• Avoid hardcoding plaintext AccessKeys in SQL statements. Instead, inject them through variables or the Key Management Service.

Usage

Switch Catalogs

-- Method 1: Switch Catalog Only
SET CATALOG odps_prod;

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

Query ODPS tables

-- Assuming the catalog has already switched to odps_prod, query directly.
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 the table creation statement
SHOW CREATE TABLE odps_prod.sales_db.user_log;

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

-- View Catalog Creation Statement
SHOW CREATE CATALOG odps_prod;

Drop a catalog

DROP CATALOG IF EXISTS odps_prod;

References

• Catalogs and external tables
• Overview of catalogs
• Deploy the Java SDK environment for OceanBase Database
• CREATE EXTERNAL CATALOG