ODPS External Table

Starting from V4.3.5 BP1 (MySQL-compatible mode), OceanBase Database supports creating ODPS external tables using the CREATE EXTERNAL TABLE statement to access table data in Alibaba Cloud MaxCompute (formerly ODPS). By default, the Tunnel API is used.

Unlike file external tables, ODPS external tables do not use a LOCATION clause to specify a file path. Instead, they connect to tables in a remote ODPS project via the MaxCompute API. OceanBase 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.

Overview

MaxCompute provides two types of data access interfaces integrated with OceanBase:

When creating an ODPS external table, you specify the API to use via the API_MODE clause in the PROPERTIES block. The following table compares the two APIs:

Supported operations

Prerequisites

1. Version and mode: OceanBase Database V4.3.5 BP1 or later (MySQL-compatible mode tenant). For API_MODE = 'storage_api' or SPLIT, V4.3.5 BP3 or later is required.
2. Java environment: The MaxCompute SDK is based on Java. You must deploy a Java SDK environment. For more information, see Deploy the OceanBase Database Java SDK environment.
3. MaxCompute credentials:
   • The AccessKey ID and AccessKey secret of the RAM user (minimum permissions recommended).
   • The endpoint of the MaxCompute service, including region information.
   • When using the Storage API, ensure the same VPC is used and the Storage API permission has been enabled.
4. 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:

OceanBase Database does not cache the full data of a MaxCompute table. Data is fetched via the API during queries, and data is written to a MaxCompute table via the API during writes.

Create an ODPS External Table

Non-partitioned table

Automatic column mapping

If no generated column is specified, OceanBase maps them in the order of column definition 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"}
);

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

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

Key parameter description (PROPERTIES)

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 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 data 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_;

During writing, the number and order of columns must match those defined in the external table. For more information about syntax, see Insert data.

Partition information synchronization strategy

Use AUTO_REFRESH to control the refresh method for MaxCompute partition metadata:

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 and MaxCompute, see:

• Data type mappings

Time zone handling

MaxCompute time types (such as DATETIME) do not have explicit time zone information. OceanBase assumes they are consistent with its own session's time zone by default. It is recommended that OceanBase and MaxCompute use the same time zone (for example, Asia/Shanghai).

Limitations

Considerations

1. Difference between ODPS external tables and file external tables: ODPS external tables access remote tables through the MaxCompute API and do not use a LOCATION file path; file external tables are used to access CSV/Parquet/ORC files located in paths such as OSS or HDFS.
2. Difference between ODPS external tables and ODPS Catalog: External tables map to a single table; Catalog connects to the entire MaxCompute project and synchronizes metadata automatically. See ODPS Catalog.
3. 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.
4. Credential security: It is recommended to use a RAM sub-account and follow the principle of least privilege; avoid hardcoding plaintext AccessKeys in SQL.

References

• ODPS Catalog
• Catalog and External Tables
• CREATE EXTERNAL TABLE
• Data type mappings