MaxCompute:OBJECT TABLE definition

Syntax

CREATE OBJECT TABLE [IF NOT EXISTS] <objecttable_name>
WITH SERDEPROPERTIES ('<key>' = '<value>')
LOCATION '<location>'
[TBLPROPERTIES ('<key>' = '<value>')]
[COMMENT '<comment>'];

You do not need to define columns. MaxCompute provides the metadata columns automatically.

Parameters

Example

SET odps.namespace.schema=true;

CREATE OBJECT TABLE ot_demo_day
WITH SERDEPROPERTIES (
  'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/odps-external-****/ottest/';

Choose a refresh mode

Select a refresh mode when you create the table or leave it as the default (manual).

Manual refresh

Run the following command to trigger a full metadata refresh:

ALTER TABLE <objecttable_name> REFRESH METADATA;

Example:

ALTER TABLE ot_demo_day REFRESH METADATA;

Periodic refresh

Set metadata.cache.mode to periodic and specify metadata.staleness.seconds when creating the table. The scheduler triggers a full refresh at approximately the interval you specify.

SET odps.namespace.schema=true;
SET odps.sql.type.system.odps2 = true;

CREATE OBJECT TABLE ot_demo_day
WITH SERDEPROPERTIES (
  'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/odps-external-****/ottest/'
TBLPROPERTIES (
  'metadata.cache.mode' = 'periodic',
  'metadata.staleness.seconds' = '3600'
);

metadata.staleness.seconds accepts values between 1 and 604800 (1 second to 1 week). The value is not a strict guarantee — the scheduler uses it as a target.

Scheduled refresh

Set metadata.cache.mode to crontab and specify metadata.crontab.expression when creating the table. Use a standard cron expression to define the exact schedule.

SET odps.namespace.schema=true;
SET odps.sql.type.system.odps2 = true;

CREATE OBJECT TABLE ot_demo_day
WITH SERDEPROPERTIES (
  'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-region-internal.aliyuncs.com/odps-external-****/ottest/'
TBLPROPERTIES (
  'metadata.cache.mode' = 'crontab',
  'metadata.crontab.expression' = '0 0 14 * * ?'
);

The cron expression 0 0 14 * * ? runs a refresh at 2:00 PM every day: 0 (second), 0 (minute), 14 (hour), * (any day of month), * (any month), ? (day-of-week — mutually exclusive with day-of-month).

View refresh task history

Check the status of past refresh tasks with:

SHOW refresh task history FOR object TABLE <object_table_name>;

Example and output:

-- View refresh task history for ot_demo_day04.
SHOW refresh task history FOR object TABLE ot_demo_day04;

ID = 20260105*******f
+---------------------------------------------------------------------------------------------------+
| Project:                  test_project                                                            |
| Schema:                   default                                                                 |
| Task:                     ***                                                                     |
+---------------------------------------------------------------------------------------------------+
| History:                                                                                          |
+---------------------------------------------------------------------------------------------------+
| InstanceId                       | CreateTime             | EndTime                | Status       |
+---------------------------------------------------------------------------------------------------+
| 20260105******************ks     | 2026-01-05 14:12:00    | 2026-01-05 14:12:04    | Terminated   |
| 20260105******************y3     | 2026-01-05 14:10:00    | 2026-01-05 14:10:03    | Terminated   |
+---------------------------------------------------------------------------------------------------+

OK

The output contains one row per task: InstanceId, CreateTime, EndTime, and Status. If a task shows Failed, run wait <InstanceId>; and check the Logview output for details.

GET_DATA_FROM_OSS

GET_DATA_FROM_OSS reads part or all of an object and returns it as a binary value. All examples below use the three-tier path project.schema.object_table to identify the Object Table.

BINARY GET_DATA_FROM_OSS (
  STRING <full_object_table_name>,
  STRING <key>
  [, BIGINT <offset>]
  [, BIGINT <length>]
  [, STRING <object_not_found_policy>]
)

GET_DATA_FROM_OSS returns binary data. Wrap it in STRING() to get a string result.

Examples

Cast the result to STRING:

SELECT STRING(
  GET_DATA_FROM_OSS('<project_name>.default.ot_demo_day', key, 0, -1, 'OUTPUT_NULL')
)
FROM ot_demo_day;

Equivalent calls — all of the following read the full content of each object with default settings (offset=0, length=-1, policy=OUTPUT_NULL):

-- Full explicit form
SELECT GET_DATA_FROM_OSS('<project_name>.default.ot_demo_day', key, 0, -1, 'OUTPUT_NULL') FROM ot_demo_day;

-- Equivalent shorthand forms
SELECT GET_DATA_FROM_OSS('<project_name>.default.ot_demo_day', key) FROM ot_demo_day;

SELECT GET_DATA_FROM_OSS('<project_name>.default.ot_demo_day', key, 0) FROM ot_demo_day;

SELECT GET_DATA_FROM_OSS('<project_name>.default.ot_demo_day', key, 0, -1) FROM ot_demo_day;

SELECT GET_DATA_FROM_OSS('<project_name>.default.ot_demo_day', key, 'OUTPUT_NULL') FROM ot_demo_day;

SELECT GET_DATA_FROM_OSS('<project_name>.default.ot_demo_day', key, 0, 'OUTPUT_NULL') FROM ot_demo_day;

Clear Object Table metadata

TRUNCATE TABLE removes all cached metadata but keeps the table definition:

TRUNCATE TABLE <object_table_name>;

Example:

TRUNCATE TABLE ot_demo_day;

Rename an Object Table

ALTER TABLE <object_table_name> RENAME TO <new_object_table_name>;

Example:

ALTER TABLE ot_demo_day RENAME TO new_ot_demo_day;

Delete an Object Table

Deleting an Object Table removes the cached metadata and stops storage charges. The actual OSS data is not affected. Recreate the table at any time to resume using the data.

DROP TABLE [IF EXISTS] <object_table_name>;

Example:

DROP TABLE IF EXISTS ot_demo_day;

Connection refused error when refreshing metadata

You see an error like:

ODPS-0010000:System internal error -
ActionHandler job failed with failinfo storage service worker error occured:
common/io/oss/oss_file_system_cppsdk.cpp(919):
OSSRequestException: Status: -50, RequestId: ,
ErrorCode: ClientError:-50, Message: E_HTTP_ERROR_CONN_REFUSED

The location parameter was set to a public OSS endpoint. Object Tables require an internal network endpoint. Update the location to use the internal endpoint — see the location parameter description in Create an Object Table.

Periodic refresh does not trigger on schedule

Check that the location parameter uses an OSS internal network domain name, not a public endpoint. See the location parameter description in Create an Object Table.

Periodic refresh task fails with an internal error

You see:

FAILED: ODPS-0010000:System internal error - ActionHandler job failed with failinfo
storage service worker error occured: common/io/oss/oss_file_system_cppsdk.cpp(877):
OSSRequestException: Status: -50, RequestId: , ErrorCode: ClientError:-50, Message:

This is a platform-side error. Submit a ticket to contact MaxCompute technical support.