MaxCompute:Project-level Information Schema (to be deprecated)

How it works

Information Schema exposes two categories of views:

• Metadata views — tables, columns, partitions, user-defined functions (UDFs), resources, users, roles, privileges, and labels in your project. Data is near-real-time, with an approximate 3-hour delay.

• Usage views — job history (TASKS_HISTORY) and data tunnel history (TUNNELS_HISTORY), each retaining data for 14 days. A real-time job snapshot view (TASKS) is also available in preview.

To query a view, prefix the view name with Information_Schema:

SELECT * FROM Information_Schema.tables;
SELECT * FROM Information_Schema.tasks_history WHERE ds='yyyymmdd' LIMIT 100;

MaxCompute-specific views (such as TASKS, TASKS_HISTORY, and TUNNELS_HISTORY) may change without notice. To prevent query breakage when fields are added, always select specific columns rather than using SELECT *.

Limitations

• Project scope only. Information Schema provides metadata for the current project only. To analyze metadata across multiple projects, collect and integrate the data from each project separately.

• Near-real-time delay. Metadata views have an approximate 3-hour delay. For latency-sensitive use cases, use the SDK or CLI to query object metadata directly.

• 14-day history retention. TASKS_HISTORY and TUNNELS_HISTORY retain data for 14 days. To keep history beyond 14 days, export the data to a MaxCompute table on a regular schedule.

• New projects (March 1, 2024 and later). Project-level Information Schema is not provisioned automatically. Use tenant-level Information Schema instead.

Prerequisites

Before you begin, ensure that you have:

• An existing MaxCompute project created before March 1, 2024

• Project owner access, or a RAM user assigned the Super_Administrator role

Install the permission package

Run the following command in the MaxCompute client or on the SQL Query page in the DataWorks console:

install package Information_Schema.systables;

After installation, Information Schema is ready to query. Storage for metadata is free.

To list all views included in the package:

odps@myproject1> describe package Information_Schema.systables;

Grant access to other users

By default, only the project owner can query Information Schema views. Other users and roles see only data they have been explicitly granted access to. To grant access, run the following commands in the MaxCompute client:

grant <actions> on package Information_Schema.systables to user <user_name>;
grant <actions> on package Information_Schema.systables to role <role_name>;

Example:

grant read on package Information_Schema.systables to user RAM$Bob@aliyun.com:user01;

For more information about cross-project resource access, see Cross-project resource access based on packages.

Billing

Usage notes

• Select specific columns. MaxCompute-specific views may change. Avoid SELECT * — select only the columns you need to prevent breakage when fields are added.

• Specify field names when exporting. Avoid INSERT INTO ... SELECT * FROM information_schema.***. If new fields are added to a view, the wildcard selection may fail. Always list field names explicitly.

• Query TASKS_HISTORY and TUNNELS_HISTORY after 6:00 AM. Query the previous day's data after 6:00 AM each day to avoid scanning the current day's incomplete partition, which reduces input data and lowers query costs.

Query examples

The following examples show common patterns for querying Information Schema.

List all tables in the current project:

SELECT table_name, table_type, is_partitioned, create_time
FROM Information_Schema.tables;

Count tables by type:

SELECT table_type, COUNT(table_name) AS table_count
FROM Information_Schema.tables
GROUP BY table_type
ORDER BY table_count DESC;

Query job history for a specific date:

SELECT task_name, task_type, status, start_time, end_time, input_bytes
FROM Information_Schema.tasks_history
WHERE ds = '20240101'
LIMIT 100;

Metadata views

The following table lists all views and their key attributes. Use this table to select the right view for your task.

What's next

• Tenant-level Information Schema — use this for projects created on or after March 1, 2024, or to query metadata across multiple projects.

• Assign a role to a user — manage the Super_Administrator role required to install the permission package.

• Cross-project resource access based on packages — manage Information Schema access for other users and roles.