Practical Use of MaxCompute Metadata: Statistical Analysis of Project Information

This article mainly introduces MaxCompute's tenant-level Information Schema and focuses on using the CATALOGS view of metadata for project-related statistics.

By Haiqing

The tenant-level Information Schema of MaxCompute provides project metadata and historical data from the perspective of tenants. You can pull a type of metadata from all your projects in the same metadata center at a time for statistical analysis.

This article focuses on project statistics through the CATALOGS view of metadata. If you haven't used the tenant-level Information Schema, read the background information, feature introduction, fees, usage limits, and precautions written in Tenant-level Information Schema in advance to avoid unnecessary problems.

Count All Projects with Backup and Restoration Configuration Set for Over One Day

Scenario

We have observed a rapid increase in backup and restoration storage costs. The Cost Analysis section of the MaxCompute console indicates that several projects have incurred these costs and exceeded the expected amount. Therefore, it is necessary to identify which projects have backups configured for more than one day and to obtain a detailed project list for further verification and assessment.

Solution

You can use the SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS view within the tenant-level Information Schema to view and acquire data. The query statements are as follows:

SET odps.namespace.schema = true; -- If you have enabled the tenant-level schema syntax switch for your current tenant, you do not need to execute this flag.
SELECT  catalog_name
        ,create_time
        ,region
        ,get_json_object(json_parse(settings),'$."odps.timemachine.retention.days"') timemachine_retention_days
FROM    SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS
;

View the IP Address Whitelist Configurations of All Projects

Scenario

In light of heightened security requirements, access to MaxCompute data must be restricted to specified IP addresses, necessitating daily checks of the IP whitelist settings for each project.

Solution

To view and retrieve the information, you can use the SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS view within the tenant-level Information Schema. The corresponding query statement is as follows:

SET odps.namespace.schema = true; -- If you have enabled the tenant-level schema syntax switch for your current tenant, you do not need to execute this flag.
SELECT  catalog_name
        ,create_time
        ,region
        ,get_json_object(json_parse(settings),'$."odps.security.ip.whitelist"') ip_whitelist
        ,get_json_object(json_parse(settings),'$."odps.security.vpc.whitelist"') vpcip_whitelist
FROM    SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS;

Check Whether the Consumption Limit Has Been Set for All Projects

Scenario

There has been a recent spike in costs. Analysis through the MaxCompute console's cost analysis feature reveals that the SQL cost item for a pay-as-you-go project has increased sharply. Investigation shows this surge is due to large, unnecessary SQL executions, which can be curtailed by setting a maximum SQL consumption limit in the project's configuration. Therefore, it is important to review the settings of this property across all projects.

Solution

Data can be viewed and fetched using the SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS view in the tenant-level Information Schema. The query statement is as follows:

SET odps.namespace.schema = true; -- If you have enabled the tenant-level schema syntax switch for your current tenant, you do not need to execute this flag.
SELECT  catalog_name
        ,create_time
        ,region
        ,get_json_object(json_parse(settings),'$."odps.sql.metering.value.max"') sql_metering_value_max
FROM    SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS;

Summary

The scenarios presented above are just a few examples. In reality, you can leverage the SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS view to access more information and fulfill a wider range of business needs.

Community

Practical Use of MaxCompute Metadata: Statistical Analysis of Project Information

Count All Projects with Backup and Restoration Configuration Set for Over One Day

Scenario

Solution

View the IP Address Whitelist Configurations of All Projects

Scenario

Solution

Check Whether the Consumption Limit Has Been Set for All Projects

Scenario

Solution

Summary

Read previous post:

Read next post:

Alibaba Cloud MaxCompute

You may also like

Comments

Alibaba Cloud MaxCompute

Related Products

Big Data Consulting for Data Technology Solution

MaxCompute

Big Data Consulting Services for Retail Solution

Digital Marketing Solution