This topic introduces the basic concepts, features, 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 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.
- Information Schema provides metadata views of the current project. Cross-project metadata access is not allowed. 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 project on a regular basis.
Obtain the Information Schema service
Since December 1, 2020, for the newly created MaxCompute project, MaxCompute automatically provides metadata views related to Information Schema for this project. You do not need to manually install the Information Schema permission package.
- Run the following command on the MaxCompute client (odpscmd):
odps@myproject1>install package Information_Schema.systables;
- In DataWorks, click Workspaces in the left-side navigation pane. On the Workspaces
page, find your workspace, and click Data Analytics in the Actions column. On the
DataStudio page, click the Ad-Hoc Query icon and select your query node in the left-side
navigation pane. On the tab that appears, execute the following statement:
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.
Run the following command to view the views provided by the Information Schema project:
odps@myproject1> describe package Information_Schema.systables;
Query a metadata view
To query a metadata view, you must prefix the project name Information Schema to the view name, that is, Information Schema.view_name.
odps@myproject1>select * from Information_Schema.tables;
odps@myproject1>select * from Information_Schema.tasks_history where ds='yyyymmdd' limit 100;
The views provided by Information Schema contain 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 permissions to view the data. For more information, see Package usage method.
grant actions on package <pkgName> to user <username>; grant actions on package <pkgName> to role <role_name>;
grant read on package Information_Schema.systables to user RAM$name@your_account.com:user01;