Overview of Information Schema-MaxCompute(MaxCompute)-阿里云帮助中心

Information Schema is a metadata service of MaxCompute that lets you query project metadata, usage history, and job metrics.

MaxCompute Information Schema provides project metadata and historical usage data. It extends the ANSI SQL-92 Information Schema with fields and views specific to MaxCompute. MaxCompute offers a public project named Information Schema. You can access the read-only views in this project to query metadata and usage history for your own projects.

Limits

Information Schema provides metadata views for the current project only. Cross-project metadata access is not supported. To analyze metadata from multiple projects, retrieve and consolidate the metadata from each project separately.
The metadata system tables provide near real-time views. For applications that require low-latency metadata, retrieve the metadata of specific objects directly using the software development kit (SDK) or command-line interface (CLI).
Metadata and job history data are stored in the Information Schema project. To create snapshot backups of historical data or to retain job history for more than 14 days, you must periodically back up the Information Schema data to a specified project.

Get the Information Schema service

Starting from March 1, 2024, MaxCompute no longer automatically installs the project-level Information Schema for new projects. To query metadata, use the tenant-level Information Schema instead, which provides more comprehensive information. For more information, see Tenant-level Information Schema.

For existing MaxCompute projects, to use the Information Schema service, you must install the Information Schema permission package as a project owner or a RAM user with the Super_Administrator role. For more information about how to grant a management role to a user, see Grant a role to a user. You can install the package in one of the following two ways:

Log on to the MaxCompute client and run the following command:
```
install package Information_Schema.systables;
```
Log on to the DataWorks console and go to the Ad Hoc Query page. For more information, see Run SQL statements in an ad-hoc query (optional). Run the following command:
```
install package Information_Schema.systables;
```
The following is an example.

After the package is installed, the current project can query its metadata through Information Schema. The data is stored in the Information Schema project, and no storage fees apply.

Run the following command to view the views provided by Information Schema.

odps@myproject1> describe package Information_Schema.systables;

Query result example: information_schema 截图返回结果

Query metadata views

When you query a metadata view, you must specify `Information_Schema` before the view name, in the format Information_Schema.view_name.

For example, run the following command in the `myproject1` project to query the metadata of all tables in the project.

odps@myproject1>select * from Information_Schema.tables;

Information Schema also includes job history views for querying job history within the current project. You can filter results by adding a date partition:

odps@myproject1>select * from Information_Schema.tasks_history where ds='yyyymmdd' limit 100;

Grant access permissions

The Information Schema views contain all user data at the project level. By default, only the project owner can access this data. If other users or roles in the project need to access it, you must grant them permissions. For more information, see Access resources across projects based on packages.

Syntax for granting permissions:

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

actions: The operation permission to be granted. The value is Read.
user_name: An Alibaba Cloud account or RAM user that has been added to the project.

You can execute the list users; command in the MaxCompute client to obtain user accounts.
role_name: A role that has been added to the project.

You can execute the list roles; command in the MaxCompute client to obtain the role name.

Example:

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

List of metadata views

Use the Information_Schema metadata views to browse and retrieve metadata.

Use the Information_Schema usage information views to analyze job metrics such as resource consumption, runtime duration, and data processing volume. This helps you optimize jobs or plan resource capacity.

Views have different timeliness or default retention periods. Data beyond the retention period is inaccessible. To retain historical data longer, periodically export data from Information_Schema to a local table.

Billing details:

For projects that use pay-as-you-go compute resources, queries on Information Schema views incur query fees. The fees for SQL queries on views are calculated based on the underlying expanded SQL. To improve query performance, Information Schema views are optimized using Range clustered tables to reduce the amount of data scanned. When you query the TASKS_HISTORY and TUNNELS_HISTORY views, query the data from the previous day after 06:00:00 every day. Avoid querying data from the current day to minimize the amount of scanned data and reduce query fees.
For projects that use subscription compute resources, queries on Information Schema views consume the compute units (CUs) that you purchased.
You do not need to pay storage fees for Information Schema views.

Note

When you export data, explicitly select the field names of the view. Avoid using insert into select * from information_schema.*** to export data. This prevents backup failures that can occur if new fields are added.

The following table lists the metadata views.

Classification	View	Timeliness/Retention period	Latency description
Metadata information	TABLES	Near real-time view	There is a latency of about 3 hours compared to online data.
	PARTITIONS	Near real-time view
	COLUMNS	Near real-time view
	UDFS	Near real-time view
	RESOURCES	Near real-time view
	UDF_RESOURCES	Near real-time view
	USERS	Near real-time view
	ROLES	Near real-time view
	USER_ROLES	Near real-time view
	PACKAGE_OBJECTS	Near real-time view
	INSTALLED_PACKAGES	Near real-time view
	SCHEMA_PRIVILEGES	Near real-time view
	TABLE_PRIVILEGES	Near real-time view
	COLUMN_PRIVILEGES	Near real-time view
	UDF_PRIVILEGES	Near real-time view
	RESOURCE_PRIVILEGES	Near real-time view
	TABLE_LABELS	Near real-time view
	COLUMN_LABELS	Near real-time view
	TABLE_LABEL_GRANTS	Near real-time view
	COLUMN_LABEL_GRANTS	Near real-time view
Usage information	TASKS	Real-time snapshot of running jobs	There is a latency of a few seconds compared to online data. This feature is currently in preview and is not covered by a Service-Level Agreement (SLA). It will be gradually rolled out.
	TASKS_HISTORY	Near real-time view, partitioned table, retains details for the last 14 days	There is a latency of about 15 minutes compared to online data.
	TUNNELS_HISTORY	Near real-time view, partitioned table, retains details for the last 14 days

TABLES

Information about tables in the project.

Field	Type	Value
table_catalog	STRING	The value is fixed at `odps`.
table_schema	STRING	Project name.
table_name	STRING	Table name.
table_type	STRING	Table type. Valid values: MANAGED_TABLE VIRTUAL_VIEW EXTERNAL_TABLE
is_partitioned	BOOLEAN	Indicates whether the table is a partitioned table.
owner_id	STRING	The ID of the table owner.
owner_name	STRING	Optional. The name of the table owner's Alibaba Cloud account.
create_time	DATETIME	The time when the table was created.
last_modified_time	DATETIME	The time when the table data was last updated.
data_length	BIGINT	If the table is not a partitioned table, this is the data volume of the table. If the table is a partitioned table, the system does not calculate the data volume, and the value is NULL. The PARTITIONS view contains the data volume of each partition in a partitioned table. Unit: byte.
table_comment	STRING	The comment of the table.
life_cycle	BIGINT	Optional. The lifecycle.
is_archived	BOOLEAN	Reserved field. This field is not used.
table_exstore_type	STRING	Reserved field. This field is not used.
cluster_type	STRING	The bucketing (clustering) type of the MaxCompute table. Valid values: HASH and RANGE.
number_buckets	BIGINT	Optional. The number of buckets in a clustered table. A value of 0 indicates that the number is dynamically determined at job runtime.
view_original_text	STRING	The view text for a table of the VIRTUAL_VIEW type.

PARTITIONS

Information about table partitions in the project.

Field	Type	Value
table_catalog	STRING	Static field `odps`.
table_schema	STRING	Project name.
table_name	STRING	Table name.
partition_name	STRING	Partition name. For example, `ds='20190130'`.
create_time	DATETIME	The time when the partition was created.
last_modified_time	DATETIME	The time when the table was last updated.
data_length	BIGINT	The data volume of the partition. Unit: byte.
is_archived	BOOLEAN	Reserved field. This field is not used.
is_exstore	BOOLEAN	Reserved field. This field is not used.
cluster_type	STRING	Optional. The bucketing (clustering) type of the MaxCompute table. Valid values: HASH and RANGE.
number_buckets	BIGINT	Optional. The number of buckets in a clustered table. A value of 0 indicates that the number is dynamically determined at job runtime.

COLUMNS

Information about table fields in the project.

Field	Type	Value
table_catalog	STRING	Static field: `odps`.
table_schema	STRING	Project name.
table_name	STRING	Table name.
column_name	STRING	Column name.
ordinal_position	BIGINT	The ordinal number of the column.
column_default	STRING	The default value of the field.
is_nullable	BOOLEAN	Optional. The value is always YES.
data_type	STRING	Data type.
column_comment	STRING	The comment of the column.
is_partition_key	BOOLEAN	Indicates whether the column is a partition key.

UDFS

Information about user-defined functions (UDFs) in the project.

Field	Type	Value
udf_catalog	STRING	Static field `odps`.
udf_schema	STRING	Project name.
udf_name	STRING	UDF name.
owner_id	STRING	The ID of the UDF owner.
owner_name	STRING	Optional. The name of the UDF owner's Alibaba Cloud account.
create_time	DATETIME	The time when the UDF was created.
last_modified_time	DATETIME	The time when the UDF was last modified.

RESOURCES

Information about resources in the project.

Field	Type	Value
resource_catalog	STRING	Static field: `odps`.
resource_schema	STRING	The name of the project.
resource_name	STRING	Resource name.
resource_type	STRING	Resource type. Valid values: Py and Jar.
owner_id	STRING	The ID of the resource owner.
owner_name	STRING	Optional. The name of the resource owner's Alibaba Cloud account.
create_time	DATETIME	The time when the resource was created.
last_modified_time	DATETIME	The time when the resource was last modified.
size	BIGINT	The storage space occupied by the resource.
comment	STRING	The comment of the resource.
is_temp_resource	BOOLEAN	Indicates whether the resource is a temporary resource.

UDF_RESOURCES

Resource dependencies of UDFs in the project.

Field	Type	Value
udf_catalog	STRING	Static field: `odps`.
udf_schema	STRING	Project name.
udf_name	STRING	UDF name.
resource_schema	STRING	The project where the resource is located.
resource_name	STRING	Resource name.

USERS

List of users in the project.

Field	Type	Value
user_catalog	STRING	Valid values: ALIYUN and RAM.
user_schema	STRING	Project name.
user_name	STRING	Optional. Username.
user_id	STRING	User ID.
user_label	STRING	User tag.

ROLES

List of roles in the project.

Field	Type	Value
role_catalog	STRING	Static field `odps`.
role_schema	STRING	Project name.
role_name	STRING	Role name.
role_label	STRING	Role tag.
comment	STRING	The comment of the role.

USER_ROLES

Information about roles that users have in the project.

Field	Type	Value
user_role_catalog	STRING	Static field: `odps`.
user_role_schema	STRING	Project name.
role_name	STRING	Role name.
user_name	STRING	Username.
user_id	STRING	The ID of the user.

PACKAGE_OBJECTS

Information about objects in packages in the project.

Field	Type	Value
package_catalog	STRING	Static field `odps`.
package_schema	STRING	Project name.
package_name	STRING	Package name.
object_type	STRING	The type of the member in the package.
object_name	STRING	The name of the member in the package.
column_name	STRING	The column name of the table.
allowed_privileges	VECTOR<STRING>	The shared permissions.
allowed_label	STRING	The shared tag.

INSTALLED_PACKAGES

Information about packages that are installed in the project.

Field	Type	Value
installed_package_catalog	STRING	Static field `odps`.
installed_package_schema	STRING	Project name.
package_project	STRING	The name of the project where the package was created.
package_name	STRING	Package name.
installed_time	DATETIME	Installation time (reserved field).
allowed_label	STRING	The shared tag.

SCHEMA_PRIVILEGES

Permission information for schemas in the project.

Field	Type	Value
user_catalog	STRING	Static field `odps`.
user_schema	STRING	Project name.
grantee	STRING	Username.
user_id	STRING	Account ID.
grantor	STRING	The account of the grantor. The current value is NULL.
privilege_type	STRING	Permission type.

TABLE_PRIVILEGES

Permission information for tables in the project.

Field	Type	Value
table_catalog	STRING	Static field `odps`.
table_schema	STRING	The name of the project where the table is located.
table_name	STRING	Table name.
grantee	STRING	Username.
user_id	STRING	Account ID.
grantor	STRING	The account of the grantor. The current value is NULL.
privilege_type	STRING	Permission type.
user_schema	STRING	The name of the project where the user is located.

COLUMN_PRIVILEGES

Field-level permission information for tables in the project.

Field	Type	Value
table_catalog	STRING	Static field: `odps`.
table_schema	STRING	The name of the project where the table is located.
table_name	STRING	Table name.
column_name	STRING	Column name.
grantee	STRING	Username.
user_id	STRING	Account ID.
grantor	STRING	Optional. The current value is NULL.
privilege_type	STRING	Permission type.
user_schema	STRING	The name of the project where the user is located.

UDF_PRIVILEGES

Permission information for UDFs in the project.

Field	Type	Value
udf_catalog	STRING	Static field `odps`.
udf_schema	STRING	Project name.
udf_name	STRING	UDF name.
user_schema	STRING	The name of the project where the user is located.
grantee	STRING	Username.
user_id	STRING	Account ID.
grantor	STRING	The account of the grantor. The current value is NULL.
privilege_type	STRING	Permission type.

RESOURCE_PRIVILEGES

Permission information for resources in the project.

Field	Type	Value
resource_catalog	STRING	Static field `odps`.
resource_schema	STRING	Project name.
resource_name	STRING	Resource name.
user_schema	STRING	The project where the user is located.
grantee	STRING	Username.
user_id	STRING	Account ID.
grantor	STRING	The account of the grantor. The current value is NULL.
privilege_type	STRING	Permission type.

TABLE_LABELS

Information about labels for tables in the project.

Field	Type	Value
table_catalog	STRING	Static field `odps`.
table_schema	STRING	Project name.
table_name	STRING	Table name.
label_type	STRING	Tag type (always NULL).
label_level	STRING	Tag level.

COLUMN_LABELS

Information about field-level labels for tables in the project.

Field	Type	Value
table_catalog	STRING	Static field `odps`.
table_schema	STRING	Project name.
table_name	STRING	Table name.
column_name	STRING	Field name.
label_type	STRING	Tag type (always NULL).
label_level	STRING	Tag level.

TABLE_LABEL_GRANTS

Information about label grants for tables in the project.

Field	Type	Value
table_label_grant_catalog	STRING	Static field: `odps`.
table_label_grant_schema	STRING	The name of the project where the user is located.
user	STRING	User name.
user_id	STRING	The ID of the user.
table_schema	STRING	The name of the project where the table is located.
table_name	STRING	Table name.
grantor	STRING	The account of the grantor. The current value is NULL.
label_level	STRING	The granted tag level.
expired	DATETIME	Expiration time.

COLUMN_LABEL_GRANTS

Information about label grants for table fields in the project.

Field	Type	Value
column_label_grant_catalog	STRING	Static field `odps`.
column_label_grant_schema	STRING	The name of the project where the user is located.
user	STRING	User name.
user_id	STRING	The ID of the user.
table_schema	STRING	The name of the project where the table is located.
table_name	STRING	Table name.
column_name	STRING	Field name.
grantor	STRING	The account of the grantor. The current value is NULL.
label_level	STRING	The granted tag level.
expired	DATETIME	Expiration time.

TASKS

Real-time snapshot of jobs for real-time job monitoring.

Important

The TASKS view is currently in preview. The fields and their content may change. This feature is not covered by a service-level agreement (SLA). Check Announcements for updates on its release status.

Field	Type	Value
project_name	STRING	Project name.
task_name	STRING	Job name.
task_type	STRING	Job type. Valid values for job type: SQL: SQL job CUPID: Spark or Mars job SQLCost: SQL cost estimation job SQLRT: SQL query acceleration job LOT: MapReduce job PS: Parameter Server for PAI AlgoTask: machine learning job
inst_id	STRING	Instance ID.
status	STRING	The running status at the moment of data collection. Valid values: Running and Waiting.
owner_id	STRING	The ID of the Alibaba Cloud account that submitted the job.
owner_name	STRING	The name of the Alibaba Cloud account that submitted the job.
start_time	DATETIME	The time when the job started.
priority	BIGINT	The priority of the job. This parameter is supported only for jobs that use subscription resources.
signature	STRING	The signature of the job.
queue_name	STRING	The name of the compute queue.
cpu_usage	BIGINT	Current CPU usage. The value is core × 100.
mem_usage	BIGINT	Current memory usage. Unit: MB.
gpu_usage	BIGINT	Current GPU usage. The value is card × 100.
total_cpu_usage	BIGINT	Cumulative CPU usage. The value is core × 100 × s.
total_mem_usage	BIGINT	Cumulative memory usage. The value is MB × s.
total_gpu_usage	BIGINT	Cumulative GPU usage. The value is card × 100 × s.
cpu_min_ratio	BIGINT	The ratio of the current CPU usage of the job to the guaranteed resource quota of the queue. This parameter is supported only for jobs that use subscription resources.
mem_min_ratio	BIGINT	The ratio of the current memory usage of the job to the guaranteed resource quota of the queue. This parameter is supported only for jobs that use subscription resources.
gpu_min_ratio	BIGINT	The ratio of the current GPU usage of the job to the guaranteed resource quota of the queue. This parameter is supported only for jobs that use subscription resources.
cpu_max_ratio	BIGINT	The ratio of the current CPU usage of the job to the maximum elastic resource quota of the queue. This parameter is supported only for jobs that use subscription resources.
mem_max_ratio	BIGINT	The ratio of the current memory usage of the job to the maximum elastic resource quota of the queue. This parameter is supported only for jobs that use subscription resources.
gpu_max_ratio	BIGINT	The ratio of the current GPU usage of the job to the maximum elastic resource quota of the queue. This parameter is supported only for jobs that use subscription resources.
settings	STRING	Custom scheduling settings from upper-layer services such as DataWorks.
additional_info	STRING	Additional information. This is a reserved field.

TASKS_HISTORY

History of completed jobs in the MaxCompute project. Data from the last 14 days is retained.

Field	Type	Value
task_catalog	STRING	The value is fixed to `odps`.
task_schema	STRING	Project name.
task_name	STRING	Job name.
task_type	STRING	Job type. Valid values for job type: SQL: SQL job CUPID: Spark or Mars job SQLCost: SQL cost estimation job SQLRT: SQL query acceleration job LOT: MapReduce job PS: Parameter Server for PAI AlgoTask: machine learning job
inst_id	STRING	Instance ID.
status	STRING	The running status at the moment of data collection (not a real-time status). The following states are included: Terminated: The job has finished running. Failed: The job failed. Cancelled: The job was canceled.
owner_id	STRING	Account ID.
owner_name	STRING	The cloud account name.
result	STRING	Has a value only when an SQL job fails. It provides error information.
start_time	DATETIME	The time when the job started.
end_time	DATETIME	The time when the job ended. The value is NULL if the job did not end on the same day.
input_records	BIGINT	The number of records read by the job.
output_records	BIGINT	The number of records output by the job.
input_bytes	BIGINT	The amount of data actually scanned, which is the same as in Logview.
output_bytes	BIGINT	The number of output bytes.
input_tables	STRING	The input tables of the job, in the format [project.table1, project.table2]. Some jobs, such as SQL COST jobs, do not have this information.
output_tables	STRING	The output tables of the job, in the format [project.table1, project.table2].
operation_text	STRING	The source_xml of the query statement. The value is NULL if the source_xml exceeds 256 KB.
signature	STRING	Optional. The signature of the job.
complexity	DOUBLE	Optional. The complexity of the job. This field is available only for SQL jobs.
cost_cpu	DOUBLE	The CPU consumption of the job. A value of 100 indicates 1 core × s. For example, if a job runs on 10 cores for 5 seconds, the cost_cpu is 10 × 100 × 5 = 5000.
cost_mem	DOUBLE	The memory consumption of the job (MB × s).
settings	STRING	Information passed in by upper-layer scheduling or users, stored in JSON format. It includes the following fields: USERAGENT, BIZID, SKYNET_ID, and SKYNET_NODENAME.
ds	STRING	The date of data collection. For example, 20190101.

TUNNELS_HISTORY

Historical data of batch uploads and downloads through data channels. Data from the last 14 days is retained.

Field	Type	Value
tunnel_catalog	STRING	Static field `odps`.
tunnel_schema	STRING	Project name.
session_id	STRING	Session ID, in the format `TIMESTAMP (YYYYMMDDHHmmss, 14 characters) + ip (8 characters) + numHex (8 characters)`. For example, 2013060414484474e5e60a00000002.
operate_type	STRING	Operation type. Valid values: UPLOADLOG DOWNLOADLOG DOWNLOADINSTANCELOG
tunnel_type	STRING	Channel type. Valid values: TUNNEL LOG and TUNNEL INSTANCE LOG.
request_id	STRING	Request ID.
object_type	STRING	The type of the object being operated on. Valid values: TABLE and INSTANCE.
object_name	STRING	Table name or instance ID.
partition_spec	STRING	Partition information. For example, `time=20130222,loc=beijing`.
data_size	BIGINT	The number of data bytes. Unit: byte.
block_id	BIGINT	The block number for a Tunnel upload. This is valid only when the operation type is UPLOADLOG. Otherwise, it is empty.
offset	BIGINT	The starting offset for a download, indicating the record from which to start (starting from 0).
length	BIGINT	Also known as record_count. The number of records in the current download or upload. For downloads, this is the length value specified by the user.
owner_id	STRING	Alibaba Cloud account ID.
owner_name	STRING	Alibaba Cloud account name.
start_time	DATETIME	The time when the request is initiated.
end_time	DATETIME	The time when the request ended.
client_ip	STRING	The IP address of the client that initiated the Tunnel request.
user_agent	STRING	User Agent. Information about the client that initiated the Tunnel request, such as the Java version and operating system.
columns	STRING	The set of columns specified for a Tunnel data download.
ds	STRING	The date of data collection. For example, 20190101.