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

MaxCompute Information Schema provides information such as project metadata and historical usage data. Fields and views that are specific to MaxCompute are added to 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.

For example, the project that you work on is myproject1. In myproject1, execute the following statement to query information_schema.tables and obtain the metadata of all tables in myobject1:

odps@myproject1>select * from information_schema.tables;

Information Schema also contains the task history view, which allows you to query the task history of the current project. You can execute the following statement to query task history information by date:

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

Install the package and obtain access permissions.

Before using the service, you must install the Information Schema package as a project owner and obtain the permission to access the project metadata. Use one of the following methods to install the package:

  • Run the following command in the MaxCompute command line tool (odpscmd):
    odps@myproject1>install package information_schema.systables;
  • In DataWorks, choose DataStudio > Ad-Hoc Query. Then, execute the following statement:
    install package information_schema.systables;

After the package is installed, you are authorized to query the metadata of the current project through Information_Schema. Data is stored in the Information_Schema project. You do not have to pay for metadata storage.

Run the following command to view the list of views provided by Information_Schema:

odps@myproject1> describe package information_schema.systables;
The following figure shows the query result.Snapshot of the result returned by Information Schema

Access authorization

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

The syntax is as follows:

grant actions on package <pkgName> to user <username>;
grant actions on package <pkgName> to role <role_name>;

An example is as follows:

grant read on package information_schema.systables to user RAM$name@your_account.com:user01; 

Limits

  • Information Schema provides metadata views of the current project. Cross-project metadata access is not allowed. If you want to centrally query and analyze the metadata of multiple projects, you must obtain the metadata of each project and integrate the metadata for cross-project metadata analysis.
  • Quasi-real-time views are available for metadata system tables. For applications that require high metadata timeliness, we recommend that you use the SDK or CLI to directly obtain the metadata of a specified object.
  • Metadata and task history data are stored in the Information_Schema project. If you need to create a snapshot of the historical data or obtain historical task data of more than 14 days, you can back up Information_Schema data to a project on a regular basis.