Object table definition

Syntax

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

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

Syntax

DESC <object_table_name>

Parameters

object_table_name: Required. The name of the table.

Example

SET odps.namespace.schema=true; 
DESC ot_demo_day; 

The following result is returned:

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$****@test.aliyunid.com                        |
| Project:                  test_objecttable                                         |
| Schema:                   default                                                  |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2024-09-02 20:01:56                                      |
| LastDDLTime:              2024-09-02 20:01:56                                      |
| LastModifiedTime:         2024-09-02 20:01:56                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| key             | varchar(2048) |       | The name of the object.                     |
| size            | bigint     |       | The size of the returned object in bytes.   |
| type            | varchar(32) |       | The type of the object and valid values: Normal, Multipart, Appendable, and Symlink. |
| last_modified   | timestamp  |       | The last modified time of the object.       |
| storage_class   | varchar(32) |       | The storage class of the object.            |
| etag            | varchar(64) |       | The entity tag (ETag). When an object is created, an ETag is created to identify the content of the object. |
| restore_info    | varchar(256) |       | The restoration status of the object.       |
| owner_id        | bigint     |       | The ID of the bucket owner.                 |
| owner_display_name | varchar(256) |       | The display name of the bucket owner.       |
+------------------------------------------------------------------------------------+

The following table describes key columns in the result.

Syntax

SHOW CREATE TABLE <object_table_name>;

Parameters

object_table_name: Required. The name of the table.

Example

SET odps.namespace.schema=true; 
SHOW CREATE TABLE ot_demo_day; 

The following result is returned:

CREATE OBJECT TABLE IF NOT EXISTS yunqi_object_****.`default`.ot_demo_day 
WITH SERDEPROPERTIES ( 
  'serialization.format'='1', 
  'odps.properties.rolearn'='acs:ram::139699392458****:role/aliyunodpsdefaultrole') 
LOCATION 
  'oss://oss-cn-hangzhou-internal.aliyuncs.com/odps-external-****/ottest/' 
TBLPROPERTIES ( 
  'last_modified_time'='1731478307', 
  'transient_lastDdlTime'='1731478307', 
  'metadata.cache.mode'='manual', 
  'metadata.staleness.seconds'='3600');

Manual refresh

Each refresh performs a full synchronization of the metadata. You can control the timing and frequency of these refreshes.

• Syntax

  ALTER TABLE <objecttable_name> REFRESH METADATA;

• Parameters

  objecttable_name: Required. The name of the table.

• Example

  SET odps.namespace.schema=true; 
  ALTER TABLE ot_demo_day REFRESH METADATA;

Periodic refresh

If the files in the OSS directory that the object table maps to change frequently, you can configure periodic metadata refreshes. This reduces maintenance costs by specifying the relevant parameters when you create the table.

• Syntax

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

• Parameters

  • metadata.staleness.seconds: The refresh interval. This parameter is required for the periodic mode. The value can range from [1, 604800], which represents a range from 1 second to 1 week. This parameter is a best-effort guarantee, and the scheduler will try to execute the refresh as close to the specified interval as possible.

  • metadata.cache.mode: The refresh mode. The available options are:

    • periodic: Periodic trigger.

    • crontab: Scheduled refresh.

    • manual: Manual trigger (default). You can control the trigger timing.

Scheduled refresh

If the files in the OSS directory that the object table maps to change frequently, you can schedule metadata refreshes. This reduces maintenance costs by specifying the relevant parameters when you create the table.

• Syntax

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

• Parameters

  • metadata.crontab.expression: A cron expression that defines the schedule. For example, to trigger a refresh at 2:00 PM every day, you can use the expression 0 0 14 * * ?, which means 0 seconds, 0 minutes, hour 14 (2:00 PM), every day, every month, and ? to not specify a day of the week (mutually exclusive with the day-of-month field to avoid conflicts).

  • metadata.cache.mode: The refresh mode. The available options are:

    • crontab: Scheduled refresh.

    • periodic: Periodic trigger.

    • manual: Manual trigger (default). You can control the trigger timing.

View refresh tasks

You can run the following command to view the history of refresh tasks.

SHOW refresh task history FOR object TABLE <object_table_name>;

• Parameters

  • <object_table_name> must be an object table.

  • Return values: The instance ID of the refresh task (InstanceId), the creation time (CreateTime), the end time (EndTime), and the status (Status).

  • If the Status is Failed, you can run wait InstanceId; to view the log and check for error details.

• Example

  -- View the historical refresh tasks for the object table.
  SET odps.namespace.schema=true; 
  SHOW refresh task history for object table ot_demo_day04;
  
  -- The following result is returned.
  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

Syntax

SELECT * FROM <object_table_name>;

Parameters

object_table_name: Required. The name of the table.

Example

-- You can query the data uploaded to the specified OSS directory. If the amount of data is large, you can limit the result to five rows.
SET odps.namespace.schema=true; 
SELECT * FROM  ot_demo_day [limit 5];

Examples

GET_DATA_FROM_OSS syntax

The GET_DATA_FROM_OSS function reads all or part of an object's content and returns it as binary data.

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

Parameters

Example 1

GET_DATA_FROM_OSS returns BINARY. To work with the content as text, wrap it in the STRING() function.

SET odps.namespace.schema=true;
SELECT STRING(
  GET_DATA_FROM_OSS('<project_name>.default.ot_demo_day', key, 0, -1, 'OUTPUT_NULL')
) 
FROM ot_demo_day;

Example 2

The following statements all read the full content of the Object Table <project_name>.default.ot_demo_day. All six forms are equivalent — they each default offset to 0, length to -1, and object_not_found_policy to OUTPUT_NULL.

SET odps.namespace.schema=true;
-- Complete form with all parameters explicit
SELECT GET_DATA_FROM_OSS('<project_name>.default.ot_demo_day', key, 0, -1, 'OUTPUT_NULL') FROM ot_demo_day;

-- The following statements are equivalent to the one above
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;

Syntax

DROP TABLE [IF EXISTS] <object_table_name>; 

Parameters

object_table_name: Required. The name of the table.

Example

SET odps.namespace.schema=true; 
DROP TABLE IF EXISTS ot_demo_day;

ODPS-0010000:System internal error

• Symptom

  The following error message is reported:

  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

• Cause

  You used a public endpoint for OSS when creating the object table.

• Solution

  When you create an object table, the location parameter must contain an internal endpoint for the oss_endpoint. For information about how to obtain the internal endpoint, see Parameter description. If the error persists after you change the address to an internal endpoint, contact the MaxCompute technical support team by submitting a ticket for support.

Periodic refresh fails

• Symptom

You set periodic refresh parameters when you created the object table. However, the refresh does not run when the interval is reached.

• Solution

Ensure that the location parameter used to create the object table specifies an OSS internal endpoint. For more information about creating an object table, see Parameters.