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 model, forming a three-tier architecture of Catalog → Database → Table.

With the ODPS catalog feature, you can directly query data in tables of 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 the ODPS project are automatically synchronized.
• Cross-source JOIN: You can perform JOIN operations between OceanBase internal tables and tables in MaxCompute.

Supported capabilities

• Read-only queries: Supports analysis 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 the OceanBase JAVA SDK environment.

Prerequisites

1. Permissions

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

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 RAM user used by OceanBase has been authorized for MaxCompute (at least the 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 is '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, API_MODE and SPLIT parameters are supported starting from V4.3.5 BP3:

• tunnel_api (default):

  • No special network configuration is required: applicable to all deployment scenarios, regardless of whether OceanBase Database and MaxCompute are 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 the MaxCompute Storage API.

  • Applicable environments:

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

• storage_api:

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

  • Permission dependency: requires the MaxCompute Storage API to be enabled, and the AccessKey must have the necessary permissions.

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

  • Applicable environments:

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

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 subaccount and follow the principle of least privilege.
• Avoid hardcoding plaintext AKs in SQL statements. Instead, inject them using variables or a key management service.

Usage

Switch Catalogs

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

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

Query an ODPS table

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

-- Join an internal table with an external table.
SELECT o.order_id, u.city
FROM internal.test_db.orders o
JOIN users u ON o.user_id = u.id;

Metadata operations

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

-- View the DDL statement for the table.
SHOW CREATE TABLE odps_prod.sales_db.user_log;

-- View all catalogs in the current tenant.
SHOW CATALOGS;

-- View the DDL statement for the catalog.
SHOW CREATE CATALOG odps_prod;

Delete a catalog

DROP CATALOG IF EXISTS odps_prod;

References

• Overview of catalogs
• Deploy the OceanBase Database JAVA SDK environment
• CREATE EXTERNAL CATALOG