Overview of Information Schema - MaxCompute - Alibaba Cloud Documentation Center

Information Schema is the metadata service of MaxCompute. This topic describes the basic concepts, features, and limits of Information Schema.

MaxCompute Information Schema provides information such as project metadata and historical usage data. Fields and views that are specific to MaxCompute are added to ANSI SQL-92 Information Schema. MaxCompute provides a public project named Information Schema. You can query the metadata and historical usage data of your project by accessing the read-only views provided by this public project.

Limits

Information Schema provides metadata views of the current project. You are not authorized to access cross-project metadata. If you want to query and analyze the metadata of multiple projects, you must obtain the metadata of each project and integrate the metadata.
Quasi-real-time views are provided for metadata system tables. For applications that require high metadata timeliness, we recommend that you use an SDK or CLI to obtain the metadata of a specified object.
Metadata and historical data of jobs are stored in the Information Schema project. To create a snapshot of the historical data or obtain historical job data of more than 14 days, you can back up Information Schema data to a specified project on a regular basis.

Obtain the Information Schema service

For a project that was created on December 1, 2020 and onwards, MaxCompute automatically provides metadata views related to Information Schema for this project. You do not need to manually install the Information Schema permission package.

Before you use Information Schema in an existing project, you must obtain the permissions to access the project metadata. To obtain the permissions, install the Information Schema permission package as the project owner or a Resource Access Management (RAM) user that is assigned the Super_Administrator role. For more information, see User authentication. You can use one of the following methods to install the permission package:

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 Use the ad-hoc query feature to execute SQL statements (optional). Run the following command:
```
install package Information_Schema.systables;
```

After the package is installed, you can use Information Schema to query the metadata of the current project. Data is stored in the Information Schema project. You do not need to pay for metadata storage.

You can run the following command to query the views provided by the Information Schema project:

odps@myproject1> describe package Information_Schema.systables;

The following figure shows an example of the query result.

Query a metadata view

If you want to query a metadata view, you must prefix the project name Information Schema to the view name. The format is Information Schema.view_name.

If the project that you access is myproject1, you can run the following command to query the metadata of all tables in myproject1:

odps@myproject1>select * from Information_Schema.tables;

The Information Schema project also contains the job history view. This view allows you to query the job history of the current project. You can run the following command to query historical jobs by date:

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

Access authorization

The views provided by Information Schema contain all the user data at the project level. By default, the owner of a project can view the user data of this project. Other users or roles in the project must be granted the required permissions to view the data. For more information, see Use a package.

Syntax of the statements that are used to grant permissions to users or roles:

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

actions: the permissions that you want to grant. Set the value to Read.
user_name: an Alibaba Cloud account or RAM user that is added to the project.
You can run the list users; command on the MaxCompute client to obtain user accounts.
role_name: a role that is added to the project.
You can run the list roles; command on the MaxCompute client to obtain role names.

Example:

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