ODPS Catalog
OceanBase AI Database supports ODPS Catalog. 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 ETL required: Access MaxCompute tables directly, eliminating the need to create external tables manually.
- Unified metadata: Automatically synchronizes databases and table structures from the ODPS project.
- Cross-source JOIN: Supports joint queries with internal OceanBase AI Database tables.
Supported capabilities
- Read-only queries: Supports analytical operations such as
SELECT,JOIN, andGROUP BY. - No write support: Prohibits DML or DDL operations such as
INSERT,UPDATE, andDROP TABLE. - Partition pruning/column pruning: Automatically pushes filter conditions down to reduce data transmission.
Limitations
Limitations |
Description |
|---|---|
| Complex data types | Nested complex types such as ARRAY<MAP<STRING, BIGINT>> are not supported. |
| Performance dependency | Query speed is affected by the MaxCompute Tunnel quota and network bandwidth. |
| Environment dependencies | The Java SDK must be deployed because the MaxCompute SDK is based on Java. |
For detailed information on environment dependencies, see Deploy Java SDK.
Prerequisites
Privilege requirements
- The current user must have Catalog-related privileges such as
CREATE CATALOGandUSE CATALOG(MySQL-compatible mode). Catalog capabilities are currently supported only in MySQL-compatible mode.
- The current user must have Catalog-related privileges such as
Access credentials
- Have obtained the MaxCompute AccessKey ID/Secret (or STS Token).
- The OceanBase AI Database cluster can access the MaxCompute Endpoint and Tunnel Endpoint.
Authorization configuration
- The MaxCompute project has authorized the RAM user used by OceanBase AI Database (at least
Readpermission). For details, see Overview of MaxCompute Open Storage.
- The MaxCompute project has authorized the RAM user used by OceanBase AI Database (at least
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
Parameter |
Required |
Description |
|---|---|---|
| TYPE | Yes | The value is fixed to 'ODPS'. |
| ACCESSTYPE | No | Account type. Default value: aliyun. Supported values: aliyun, sts, and app. |
| ACCESSID / ACCESSKEY | Yes | AccessKey of the RAM user, not the root account AK. |
| STSTOKEN | Conditional | Required only when ACCESSTYPE = 'sts'. |
| ENDPOINT | Yes | MaxCompute service endpoint, including the region. |
| TUNNEL_ENDPOINT | Yes | Tunnel service address, used for efficient data pulling. |
| PROJECT_NAME | Yes | MaxCompute project name, which is equivalent to a database. |
| QUOTA_NAME | No | Compute resource quota, if any. |
| COMPRESSION | No | Data compression format, which must match the ODPS table. If not specified, compression is disabled. |
| API_MODE | No | API mode: tunnel_api (default) or storage_api. |
| SPLIT | Conditional | Sharding method when storage_api is used: byte or row. |
| REGION | Yes | Region where MaxCompute is enabled. |
API_MODE
The following sections describe the supported values of the API_MODE and SPLIT parameters.
tunnel_api(default):No special network configuration required: Suitable for all deployment scenarios. OceanBase AI Database and MaxCompute do not need to reside in the same VPC.
No additional MaxCompute permissions required: Authentication can be completed with only an AccessID and AccessKey. The MaxCompute Storage API permission does not need to be enabled.
Application scenarios:
- OceanBase AI 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 AI 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 AccessKey must have the corresponding permissions.
Quota requirement: When the
storage_apimode is used, setQUOTA_NAMEto a pay-as-you-go quota to ensure correct billing and execution of Storage API calls. For details, see Overview of MaxCompute Storage Open Storage.Application scenarios:
- OceanBase AI 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. Inject them through variables or the Key Management Service.
Usage
Switch Catalogs
-- Method 1: Switch the Catalog only
SET CATALOG odps_prod;
-- Method 2: Switch both the Catalog and database
USE odps_prod.sales_db;
Query ODPS tables
-- Assuming that the session 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 the 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 the Catalog creation statement
SHOW CREATE CATALOG odps_prod;
Drop a Catalog
DROP CATALOG IF EXISTS odps_prod;
ODPS external tables
OceanBase AI Database supports creating ODPS external tables using the CREATE EXTERNAL TABLE statement to access table data in Alibaba Cloud MaxCompute (formerly ODPS). The Tunnel API is used by default.
Unlike a File external tables, an ODPS external table does not use the LOCATION clause to specify a file path. Instead, it connects to a table in a remote ODPS project through the MaxCompute API. OceanBase AI Database stores the external table definition and column mapping locally and invokes the ODPS Storage API or Tunnel API to interact with MaxCompute during queries or writes.
Note
If you need to access multiple tables under an entire MaxCompute project without creating external tables for each table individually, use ODPS Catalog as described above. This topic describes ODPS external tables at the single-table level.
Overview
MaxCompute provides two types of data access interfaces integrated with OceanBase AI Database:
API |
Purpose |
Key features |
|---|---|---|
| Storage API | Data Service API | Supports fine-grained access features such as partition filtering and predicate pushdown. |
| Tunnel API | Data Upload/Download API | Used for batch full-table import and export. No server-side filtering capability. |
When creating an ODPS external table, specify the API to use through the API_MODE clause in the PROPERTIES block. The following table compares the two APIs:
Dimension |
Storage API |
Tunnel API |
|---|---|---|
| Data filtering | Supports filtering data based on SQL conditions to transmit only the required data. | Server-side filtering is not supported. The entire data set must be transmitted. |
| Sharding strategy | Automatic sharding, by byte or row count | Manual sharding, with relatively complex configuration |
| Network requirements | Must be deployed in the same VPC as MaxCompute, and the Storage API permission must be enabled. | No special VPC requirements |
| Application scenarios | Conditional queries on partitioned tables and analysis scenarios that require reduced data transmission | The Storage API is not enabled, or compatibility scenarios |
Supported operations
Action |
Supported operations |
|---|---|
SELECT query |
Supported |
INSERT INTO / INSERT OVERWRITE |
Supported |
| Non-partitioned tables and partitioned tables | Supported |
| Dynamic partition identification | Supported (requires the AUTO_REFRESH configuration) |
UPDATE / DELETE |
Not supported |
Prerequisites
- Java environment: MaxCompute SDK is based on Java. You must deploy a Java SDK environment. For more information, see Deploy Java SDK.
- MaxCompute credentials:
- The AccessKey ID and AccessKey secret of the RAM user (with minimal permissions recommended).
- The endpoint of the MaxCompute service (including region information).
- When using the Storage API, ensure both systems are within the same VPC and the Storage API permission has been enabled.
- Table permissions: The RAM user must have the appropriate read and write permissions on the target MaxCompute project and table.
ODPS external table access model
The access chain for an ODPS external table is as follows:
Component |
Purpose |
|---|---|
| Define an external table in OceanBase AI Database | Saves column mappings, partition definitions, and ODPS connection parameters |
| MaxCompute API | Storage API or Tunnel API, responsible for communicating with the MaxCompute server |
| MaxCompute table | Table storing data remotely (non-file path external table) |
OceanBase AI Database does not cache the full data of a MaxCompute table. Data is fetched through the API during queries, and data is written to the MaxCompute table through the API during writes.
Create an ODPS external table
Non-partitioned table
Automatic column mapping
If no generated columns are specified, OceanBase AI Database maps them in column definition order as external$tablecol1, external$tablecol2, and so on.
CREATE EXTERNAL TABLE t1 (c1 INT, c2 INT)
PROPERTIES = (
TYPE = 'ODPS',
ACCESSID = '*****',
ACCESSKEY = '*****',
ENDPOINT = 'http://service.cn-hangzhou.maxcompute.aliyun.com/api',
PROJECT_NAME = 'odps_project',
SCHEMA_NAME = '',
TABLE_NAME = 't1',
QUOTA_NAME = '',
COMPRESSION_CODE = '',
API_MODE = {"tunnel_api"}
);
Explicit column mapping (recommended)
CREATE EXTERNAL TABLE t1 (
c1 INT AS (external$tablecol1),
c2 INT AS (external$tablecol2)
)
PROPERTIES = (
TYPE = 'ODPS',
ACCESSID = '*****',
ACCESSKEY = '*****',
ENDPOINT = 'http://service.cn-hangzhou.maxcompute.aliyun.com/api',
PROJECT_NAME = 'odps_project',
SCHEMA_NAME = '',
TABLE_NAME = 't1',
QUOTA_NAME = '',
COMPRESSION_CODE = 'lz4',
API_MODE = {"tunnel_api"}
);
Notice
AS (external$tablecolx) specifies the x-th ordinary column (non-partitioning column) to map to a column in the MaxCompute table. The numbering starts from 1. If no generated column is specified, numbers are automatically assigned in column definition order.
Partitioned table
Partitioning columns must be explicitly declared using metadata$partition_list_colX, and the PARTITION BY clause must match the partition structure of the MaxCompute table.
CREATE EXTERNAL TABLE t2 (
c1 INT,
c2 INT,
c3 VARCHAR(20) AS (metadata$partition_list_col1),
c4 VARCHAR(20) AS (metadata$partition_list_col2)
)
PROPERTIES = (
TYPE = 'ODPS',
ACCESSID = '*****',
ACCESSKEY = '*****',
ENDPOINT = 'http://service.cn-hangzhou.maxcompute.aliyun.com/api',
PROJECT_NAME = 'odps_project',
SCHEMA_NAME = '',
TABLE_NAME = 't2',
QUOTA_NAME = '',
COMPRESSION_CODE = '',
API_MODE = {"tunnel_api"}
)
PARTITION BY (c3, c4);
Notice
metadata$partition_list_colxspecifies the x-th partitioning column of the MaxCompute table. The numbering starts from 1 and cannot be omitted.- The partitioning columns of the MaxCompute table and the OceanBase AI Database external table must correspond one-to-one and have the same number.
Incorrect example: If a partitioning column is not declared with metadata$partition_list_colX, it will be treated as an ordinary column, causing the query to fail.
-- Incorrect syntax
CREATE EXTERNAL TABLE t2 (
c1 INT,
c2 INT,
c3 VARCHAR(20), -- Missing AS (metadata$partition_list_col1)
c4 VARCHAR(20) -- Missing AS (metadata$partition_list_col2)
)
PROPERTIES ( ... )
PARTITION BY (c3, c4);
PROPERTIES parameters
Parameter |
Required |
Description |
|---|---|---|
| TYPE | Yes | The value is fixed to 'ODPS'. |
| ACCESSID / ACCESSKEY | Yes | AccessKey of the RAM user. Least privilege is recommended. |
| ENDPOINT | Yes | MaxCompute service address, including the region. |
| PROJECT_NAME | Yes | MaxCompute project name. |
| TABLE_NAME | Yes | MaxCompute table name. |
| SCHEMA_NAME | No | Required when the table is under a schema. |
| ACCESSTYPE | No | Account type: aliyun (default), sts, or app. |
| STSTOKEN | Conditional | Required only when ACCESSTYPE = 'sts'. |
| QUOTA_NAME | No | Compute resource quota. |
| COMPRESSION_CODE | No | Compression format: zlib, zstd, lz4, or odps_lz4. |
| API_MODE | Yes | {"tunnel_api"} or {"storage_api"}. |
| SPLIT | Conditional | Sharding method when storage_api is used: byte or row. |
For the complete syntax, see CREATE EXTERNAL TABLE.
Query data from MaxCompute
The syntax for querying an ODPS external table is the same as that for querying a regular table.
SELECT * FROM t1;
-- Specify the degree of parallelism.
SELECT /*+ PARALLEL(N) */ * FROM t1;
Write data to MaxCompute
OceanBase AI Database supports writing data to an ODPS external table using either INSERT INTO or INSERT OVERWRITE.
-- Append write
INSERT INTO external_table_name
SELECT column_list FROM source_table [WHERE ...];
-- Overwrite
INSERT OVERWRITE external_table_name
SELECT column_list FROM source_table [WHERE ...];
Example of writing to a non-partitioned table
INSERT INTO t1 SELECT * FROM t1_;
INSERT /*+ PARALLEL(N) */ INTO t1 SELECT * FROM t1_;
-- Overwrite
INSERT OVERWRITE t1 SELECT * FROM t1_;
-- Write to specified columns
INSERT INTO t1 (c1) SELECT c1 FROM t1_;
Example of writing to a partitioned table
INSERT INTO t2 PARTITION (c3 = 'abc', c4 = 'def') SELECT * FROM t2_;
INSERT OVERWRITE t2 PARTITION (c3 = 'abc', c4 = 'def') SELECT * FROM t2_;
The number and order of columns during write must match the external table definition. For more syntax details, see Insert data.
Partition information synchronization strategy
Use AUTO_REFRESH to control the refresh method for MaxCompute partition metadata:
Policy |
Description |
Application scenarios |
|---|---|---|
IMMEDIATE |
Automatically refresh on each query | Frequent partition changes |
OFF |
Manual refresh only | Static partitioned table |
INTERVAL |
Refresh by scheduled task | Medium-frequency updates |
Example of enabling immediate refresh
CREATE EXTERNAL TABLE t2 ( ... )
AUTO_REFRESH = IMMEDIATE
PROPERTIES ( ... )
PARTITION BY (c3, c4);
Manual refresh
ALTER EXTERNAL TABLE t2 REFRESH;
Type mapping and limitations
Type mapping
For data type mappings between OceanBase AI Database and MaxCompute, see Data type mappings.
Time zone handling
MaxCompute time types (such as DATETIME) do not have explicit time zone information. OceanBase AI Database assumes that they are consistent with its own session time zone by default. It is recommended that OceanBase AI Database and MaxCompute use the same time zone (for example, Asia/Shanghai).
Limitations
Limitations |
Description |
|---|---|
| Mode | Only MySQL-compatible mode is supported. |
| Complex type | Nested complex types such as ARRAY<MAP<STRING, BIGINT>> are not supported. |
| Write column constraints | The number and order of columns during write must strictly match the definition in the external table. |
| Performance | Query speed is affected by the MaxCompute API quota and network bandwidth. |
Considerations
- Difference between ODPS external tables and file external tables: ODPS external tables access remote tables through the MaxCompute API and do not use a
LOCATIONfile path. File external tables are used to access CSV, Parquet, or ORC files under paths such as S3, S3-compatible object storage, or HDFS. - Difference between ODPS external tables and ODPS Catalog: External tables map to a single table. Catalog connects to the entire MaxCompute project and automatically synchronizes metadata. For details, see ODPS Catalog.
- API selection: Prefer the Storage API when you need to reduce data transmission and support conditional filtering. Use the Tunnel API if the Storage API is not available or the network does not meet VPC requirements.
- Credential security: It is recommended that you use a RAM sub-account and follow the principle of least privilege. Avoid hardcoding plaintext AccessKeys in SQL statements.