Concepts, permissions, and operations of schemas - MaxCompute

MaxCompute supports schemas, which allow finer-grained management of tables, resources, and functions within a project. This topic describes the concepts and permissions related to schemas and how to use them.

Background information

A MaxCompute project is a basic organizational unit of MaxCompute used for isolation and access control of multiple users. A project contains tables, resources, and functions. These objects were originally placed directly under the project, requiring the project to act as a concept similar to a traditional database or schema. This led to unclear concepts (being both a database and a schema), making it inconvenient to use, especially when there are many tables or objects. Now MaxCompute supports schemas, which can be used to classify tables, resources, and functions under a project. The hierarchy is shown in the following figure. 层级图

Key terms

Schema.
Schemas can be used to classify tables, resources, and user-defined functions (UDFs) in projects. A project can contain multiple schemas.
Schema syntax.
To use the schema feature, the syntax needs to detect the semantics of project.schema.table, which is determined by the schema syntax switch. When the schema syntax is enabled, statements in the a.b.c format are identified as project.schema.table, and statements in the a.b format are identified as schema.table. When the schema syntax is disabled, statements in the a.b.c format are not identified, and statements in the a.b format are identified as projet.table. You can enable the schema syntax at the tenant or job level.
- Tenant-level settings determine the default semantics of data access requests submitted by a tenant. You can view the settings on the Tenant Management > Tenant Properties page in the MaxCompute console. If your tenant does not have any projects or has no more than 10 projects, and all projects have been upgraded to support the schema feature and the impact has been evaluated, you can enable the Tenant-level Schema Syntax Switch on the Tenant Properties page. If your tenant has more than 10 projects, you are not allowed or recommended to modify this setting. For more information about how to enable the tenant-level schema feature, see Tenant properties.
- Job-level settings affect only the semantics of the current job and take precedence over tenant-level settings. You can use the set odps.namespace.schema=true | false; command to enable or disable the schema syntax.
Default Schema.
For a project that uses the schema feature, a schema named DEFAULT is built in each project and cannot be customized or deleted.

Limits

The MaxCompute schema feature is being continuously improved, and some modules have not been adapted. These modules can normally use operations initiated by MaxCompute only in the set odps.namespace.schema=false mode. The specific limitations are as follows.

The schema feature is supported for Spark jobs in MaxCompute only if spark.hadoop.odps.spark.version is set to spark-3.1.1-odps0.35.0 or a later version. You must configure the following parameters for Spark jobs:
```
spark.hadoop.odps.spark.version=spark-3.1.1-odps0.35.0 spark.hadoop.odps.spark.default.enable=false spark.sql.catalog.odps.enableNamespaceSchema=true
```
Mars jobs and MapReduce jobs in MaxCompute do not support projects for which the schema feature is enabled. If you enable the schema feature for your project, you cannot run Mars jobs or MapReduce jobs in your project.
Alibaba Cloud services such as Platform for AI (PAI) and Quick BI do not support custom schemas.
The schema feature of MaxCompute is supported for MaxCompute Studio V4.0.0 or later.
Views and UDFs created in the odps.namespace.schema=false mode can be accessed only in the same mode. The same applies to the odps.namespace.schema=true mode.
The schema feature of MaxCompute is supported for Hologres V1.3 or later. To upgrade your Hologres instance, see Upgradation.
MaxCompute SDK for Java 0.40.8 or later, Java Database Connectivity (JDBC) 3.3.2 or later, MaxCompute client V0.40.8 or later, and PyODPS 0.11.3.1 or later support the schema feature of MaxCompute.
DataWorks supports displaying schema-related interactions only after the Schema feature is enabled at the tenant or project level in MaxCompute. For more information, see DataWorks supports MaxCompute Schema.

Feature enablement

If you want to use the schema feature of MaxCompute, you can use one of the following methods to enable the feature:

If you are just starting to use MaxCompute and have no existing projects, you can enable the Tenant-level Schema Syntax Switch on the Tenant Properties page under Tenant Management in the MaxCompute console. This way, all new projects you create will support the schema feature, and all requests will be parsed by default using the syntax where odps.namespace.schema is set to true.
If you have existing projects, but no more than 10, and either no existing jobs or a small number of jobs that you are willing to modify and adapt, you can first upgrade all existing projects to the schema mode, and then enable the Tenant-level Schema Syntax Switch on the Tenant Properties page under Tenant Management in the MaxCompute console. After this is successful, all new projects you create will support the schema feature, and all requests will be parsed by default using the syntax where odps.namespace.schema is set to true.
If you have existing projects and jobs, but currently have a new business that needs to use the schema feature, meaning that one or more projects need to be upgraded to support schemas, you can upgrade projects to support the schema feature as follows. After the upgrade, a schema named DEFAULT is automatically created in the projects.
You can directly perform the upgrade operation on the Project Management page under Workspace in the MaxCompute console. If a project on the Project Management page does not support schemas, the Actions column will have the following entry, which you can use to perform the operation.
Note that at this point, the tenant-level schema syntax is not enabled (and it is not recommended to enable it because it will affect existing jobs). All requests are parsed by default using the syntax where odps.namespace.schema is set to false. Therefore, after a project is upgraded and custom schemas are created, to access data in custom schemas, you need to enable the schema syntax at the job level using set odps.namespace.schema=true；. If multiple projects are associated and the other projects are not upgraded to support the schema feature:
- When the schema syntax is enabled, for projects that have not been upgraded, the data path should be written as projectname.default.tablename.
- When the schema syntax is disabled, for projects that have been upgraded, custom schemas cannot be accessed. The format projectname.tablename will only identify data in the DEFAULT schema.

Usage notes

This section describes how to use schemas.

Create a project

Before using schemas, you need to create a project. For more information, see Create a MaxCompute project. When you create a project in the MaxCompute console, if the tenant-level syntax switch is enabled (odps.namespace.schema is set to true), the project supports schemas by default. Otherwise, you need to submit an application for new feature testing to upgrade the project to support schemas.

Note

After you submit an application, you need to wait for your project to be upgraded. Project upgrades are performed on every Monday and Thursday.

Manage schemas

Manage schemas using SQL statements

Use the following SQL statements to manage schemas.

View schemas.
```
show schemas;
```
Create a schema.
```
create schema <schema_name>；
```
schema_name is the custom schema name.
View schema information.
```
desc schema <schema_name>；
```
schema_name is the schema name.
Drop a schema.
```
drop schema <schema_name>；
```
schema_name is the schema name.

Manage schemas in the MaxCompute console

Log on to the MaxCompute console. In the top navigation bar, select a region.
In the left-side navigation pane, choose Workspace > Projects.
On the Projects page, find the desired project and click Manage in the Actions column.
On the Project Configuration page, click the Schema tab.
Note
The Schema tab appears only for projects that support the schema feature.
On the Schema tab, you can view, create, and delete schemas.

Perform operations on objects in schemas

To perform operations on objects (tables, views, resources, and functions) in schemas, edit commands in the project.schema.table format.

Notes

Note

All descriptions of tables in this topic also apply to views, resources, and functions.

If you perform operations on a table across projects, you must specify the table in the project.schema.table format in SQL statements.
For cross-schema operations within the same project, the command format can be written as schema.table. That is, if a command is written in the a.b format, a is parsed as the schema, b is parsed as the table, and the project is the current project.
Within the same project, you can use the use schema <schema_name> command to specify the current schema. You can then directly use commands such as select * from a, where a is the table and is automatically parsed to the current project and the specified schema.
If no schema is specified in the context, and you use commands such as select * from a, where a is the table, the table is automatically parsed to the current project and the schema named default.

Examples

Example 1: Perform operations on objects in schemas within the same project (projectA).

Perform operations on objects in the default schema.

use projectA; set odps.namespace.schema=true;-- If you configure this setting at the tenant level, skip this configuration. -- Perform operations on the t_a table. create table t_a(c1 string,c2 bigint); insert into/overwrite table t_a values ('a',1),('b',2),('c',3); select * from t_a; show tables; desc t_a; tunnel upload <path> t_a[/<pt_spc>]; tunnel download t_a[/pt_spc] <path>; -- Perform operations on the res_a.jar resource. add jar <path>/res_a.jar ; desc resource res_a.jar; list resources; get resource res_a.jar D:\; drop resource res_a.jar; -- Perform operations on the fun_a function. create function fun_a as 'xx' using 'res_a.jar'; desc function fun_a; list functions; drop function fun_a;

The parameters are described as follows:

path: The storage path and name of the file.
pt_spc: You need to specify to the lowest level partition, in the format: pt_spcpartition_col1=col1_value1, partition_col2=col2_value1...pt_spc.

Perform operations on objects in the custom schemas s_1 and s_2, including cross-schema operations.

use projectA; set odps.namespace.schema=true;-- If you configure this setting at the tenant level, skip this configuration. -- Perform operations on the t_c table in the s_1 schema. use schema s_1; create table t_c(c1 string,c2 bigint); insert into/overwrite table t_c values ('a',1),('b',2),('c',3); select * from t_c; show tables; drop table t_c; tunnel upload <path> t_c[/<pt_spc>]; tunnel download t_c[/pt_spc] <path>; -- Perform operations on the t_d table in the s_2 schema. create table s_2.t_d(c1 string,c2 bigint); insert into/overwrite table s_2.t_d values ('a',1),('b',2),('c',3); select * from s_2.t_d; show tables in s_2; drop table s_2.t_d; tunnel upload <path> s_2.t_d[/<pt_spc>]; tunnel download s_2.t_d[/pt_spc] <path>; -- Perform operations on the res_b.jar resource in the s_1 schema. use schema s_1; add jar <path>/res_b.jar ; desc resource res_b.jar; list resources; get resource res_b.jar D:\; drop resource res_b.jar; -- Perform operations on the res_c.jar resource in the s_2 schema. add jar xxx ;-- You can add resources only to the current schema or project. You cannot perform this operation across schemas or projects. To perform this operation, you must switch to the s_2 schema. -- When you perform operations on resources across schemas or projects, separate resource levels with colons (:). desc resource s_2:res_c.jar; list resources in s_2; get resource s_2:res_c.jar D:\; drop resource s_2:res_c.jar; -- Perform operations on the fun_b function in the s_1 schema. use schema s_1; create function fun_b as 'xx' using 'res_b.jar' desc function fun_b; list functions; drop function fun_b; -- Perform operations on the fun_c function in the s_2 schema. create function s_2.fun_c as 'xx' using 's_2/resources/res_c.jar' drop function s_2.fun_c; desc function s_2.fun_c; list functions in s_2; drop function s_2.fun_c;

Example 2: Perform cross-project operations. In this example, perform operations on objects of projectB in projectA.

use projectA; set odps.namespace.schema=true; -- If you configure this setting at the tenant level, skip this configuration. -- Perform operations on the t_f table of the s_3 schema in projectB. create table projectB.s_3.t_f(c1 string,c2 bigint); insert into/overwrite table projectB.s_3.t_f values ('a',1),('b',2),('c',3); select * from projectB.s_3.t_f; show tables in projectB.s_3; desc projectB.s_3.t_f; drop table projectB.s_3.t_f; tunnel upload <path> projectB.s_3.t_f[/<pt_spc>]; tunnel download projectB.s_3.t_f[/pt_spc] <path>; -- Perform operations on the res_f.jar resource of the s_3 schema in projectB. add jar xxx ;-- You can add resources only to the current schema or project. You cannot perform this operation across schemas or projects. To perform this operation, you must switch to the s_3 schema in projectB. -- When you perform operations on resources across schemas or projects, separate resource levels with colons (:). desc resource projectB:s_3:res_f.jar; list resources in projectB.s_3; get resource projectB:s_3:res_f.jar D:\; drop resource projectB:s_3:res_f.jar; -- Perform operations on the fun_f function of the s_3 schema in projectB. create function projectB.s_3.fun_f as 'xx' using 'projectB/schemas/s_3/resources/res_f.jar' desc function projectB.s_3.fun_f; list functions in projectB.s_3; drop function projectB.s_3.fun_f;

Policy description

Permissions on schemas.
Some permissions, such as CreateTable, CreateResource, and CreateFunction, can be granted only at the project level. After you grant a user these permissions on a project, the user can perform the related operations in all schemas of the project. Schema-level management of these permissions will be available in the future.
Note
- By default, the owner of a schema has all access permissions on the schema and the objects in the schema and can manage the access permissions of other users on the schema and the objects in the schema.
- If you have the CreateTable, CreateResource, and CreateFunction permissions on a project, you also have these permissions on the schemas of the project.

Permissions on objects in a schema.

When you grant permissions on objects in a schema, you must specify the objects in the project.schema.table format. The following SQL statements are used to grant permissions. For more information about the permissions on specific objects (tables, resources, and functions), see MaxCompute permissions. You can also grant permissions in the MaxCompute console. For more information, see Manage user permissions in the console.

-- Grant a role the permissions to manage all tables in a schema. GRANT schemaObjectPrivileges ON TABLE <project_name>.<schema_name>.* TO role {rolename}; -- Revoke the permissions to manage all tables in a schema from a role. REVOKE schemaObjectPrivileges ON TABLE <project_name>.<schema_name>.* FROM role {rolename}; -- Grant a role or a user the permissions to manage a table in a schema. GRANT schemaObjectPrivileges ON TABLE <project_name>.<schema_name>.<tablename> TO {role|user} {rolename | USER name};-- Grant a role or a user the permissions to manage a table in a schema. -- Revoke the permissions to manage a table in a schema from a role or a user. REVOKE schemaObjectPrivileges ON TABLE <project_name>.<schema_name>.<tablename> FROM {role|user} {rolename | USER name}; -- View the permissions on a table. SHOW GRANTS ON TABLE <project_name>.<schema_name>.<tablename>;

Note

To ensure data security, you cannot use the GRANT schemaObjectPrivileges ON TABLE <project_name>.<schema_name>.xxx* TO role {rolename}; syntax to grant permissions.