MaxCompute:Schema operations

Schema syntax switch

The schema syntax switch controls how dotted identifiers are parsed:

Two levels of control are available:

• Tenant-level setting: sets the default parsing behavior for all jobs submitted by the tenant.

• Job-level setting: overrides the tenant-level setting for the current job only. Run set odps.namespace.schema=true; or set odps.namespace.schema=false; at the start of a job.

Views and UDFs created in one mode can only be accessed in the same mode. Mixing modes for the same objects is not supported.

New tenants (no existing projects)

When to use: Your tenant has no projects yet.

1. Log on to the MaxCompute console and select a region.MaxCompute consoleMaxCompute console

2. In the left navigation pane, choose Manage Configurations > Tenants.

3. On the Tenants page, click the Tenant Property tab.

4. Turn on the Tenant-level Schema Syntax switch.

All new projects created after this point support schemas by default, with odps.namespace.schema=true as the default parsing behavior.

Small tenants with few existing jobs (10 or fewer projects)

When to use: Your tenant has 10 or fewer projects and few or no existing jobs.

1. Upgrade each existing project to support schemas:

   1. In the left navigation pane, choose Manage Configurations > Projects.

   2. On the Projects page, find the target project and click Upgrade to Support Schemas in the Actions column.

   3. Repeat for each project.

2. Enable the tenant-level schema syntax:

   1. In the left navigation pane, choose Manage Configurations > Tenants.

   2. On the Tenants page, click the Tenant Property tab.

   3. Turn on the Tenant-level Schema Syntax switch.

After this, all new projects support schemas and requests are parsed with odps.namespace.schema=true by default.

Tenants with existing projects and jobs (selective upgrade)

When to use: Your tenant has existing jobs and you want to enable schemas for specific projects without changing the global setting.

1. In the left navigation pane, choose Manage Configurations > Projects.

2. On the Projects page, find the target project and click Upgrade to Support Schemas in the Actions column.

After the upgrade, a DEFAULT schema is created automatically in the project. All requests are still parsed with odps.namespace.schema=false by default at the tenant level. To access custom schemas in an upgraded project, enable schema syntax at the job level:

set odps.namespace.schema=true;

With the schema syntax disabled, projectname.tablename resolves only to tables in the DEFAULT schema. To access tables in a custom schema, enable the schema syntax at the job level.

Non-upgraded projects: if the schema syntax is enabled, the data path is projectname.default.tablename.

Prerequisites

Before you begin, ensure that you have:

• Created a MaxCompute project

• Schema support enabled on the project (see Enable the schema feature)

Permissions required: The schema owner has full access and access control permissions for the schema and all objects within it by default. To create, describe, or drop schemas, you need the appropriate project-level permissions.

Reference formats

Set the current schema with:

use schema <schema_name>;

After this, unqualified table names resolve to the specified schema in the current project.

Resource reference format

For resources (JAR files, archives, and other files), use a colon (:) as the separator when referencing across schemas or projects:

Examples

use projectA;
set odps.namespace.schema=true; -- Skip if already set at the tenant level.

-- Create and populate a table in the DEFAULT schema.
create table t_a(c1 string, c2 bigint);
INSERT OVERWRITE TABLE t_a VALUES ('a',1),('b',2),('c',3);
select * from t_a;
show tables;
desc t_a;

-- Upload and download data using Tunnel.
tunnel upload <path> t_a[/<pt_spc>];
tunnel download t_a[/pt_spc] <path>;

-- Manage a resource in the DEFAULT schema.
add jar <path>/res_a.jar;
desc resource res_a.jar;
list resources;
get resource res_a.jar D:\;
drop resource res_a.jar;

-- Manage a function in the DEFAULT schema.
create function fun_a as 'xx' using 'res_a.jar';
desc function fun_a;
list functions;
drop function fun_a;

use projectA;
set odps.namespace.schema=true; -- Skip if already set at the tenant level.

-- Switch to schema s_1 and work with table t_c.
use schema s_1;
create table t_c(c1 string, c2 bigint);
INSERT 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>;

-- Reference table t_d in schema s_2 directly, without switching schemas.
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>;

-- Manage a resource in schema s_1 (current 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;

-- Reference a resource in schema s_2 using colon separator.
-- Note: add resource must run from within the target schema.
-- Switch to s_2 first before running add operations across schemas.
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;

-- Manage a function in schema s_1 (current 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;

-- Manage a function in schema s_2 using dot notation.
create function s_2.fun_c as 'xx' using 's_2/resources/res_c.jar';
desc function s_2.fun_c;
list functions in s_2;
drop function s_2.fun_c;

use projectA;
set odps.namespace.schema=true; -- Skip if already set at the tenant level.

-- Create and access table t_f in projectB, schema s_3.
create table projectB.s_3.t_f(c1 string, c2 bigint);
INSERT 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>;

-- Reference a resource in projectB, schema s_3 using colon separator.
-- Note: add resource must run from within the target project and schema.
-- Switch to projectB and use schema s_3 before running add operations.
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;

-- Manage a function in projectB, schema s_3.
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;

Project-level permissions for schema operations

Permissions to create tables, resources, and functions are granted at the project level, not at the schema level. If you have CreateTable, CreateResource, or CreateFunction permissions on a project, those permissions apply automatically to all schemas within that project.

The schema owner has full access and access control permissions for the schema and all objects within it by default.

Fine-grained access control at the schema level is planned for a future release.

• The permissions related to schema objects are as follows.

  Operation	Object	Description
  CreateSchema	Project	Permission to create a schema in a project.
  Describe	Schema	Permission to view a schema.
  List	Schema	Permission to list resources in a schema.
  Drop	Schema	Permission to delete a schema.
  Usage	Schema	Permission to switch the current schema.
  CreateTable	Schema	Permission to create a table in a schema.
  CreateResource	Schema	Permission to create a resource in a schema.
  CreateFunction	Schema	Permission to create a function in a schema.

• The authorization syntax is as follows.

  -- Grant a role or user the permission to create a schema in a project.
  GRANT CreateSchema ON PROJECT <projectname> TO USER/ROLE <username>/<rolename>;
  -- Revoke from a role or user the permission to create a schema in a project.
  REVOKE CreateSchema ON PROJECT <projectname> FROM USER/ROLE <username>/<rolename>;
  -- Grant a role or user permissions to view, delete, or perform other operations on a schema.
  GRANT DESCRIBE/LIST/DROP/ALTER/ALL ON SCHEMA <schemaname> TO <username>/<rolename>;
  -- Revoke from a role or user permissions to view, delete, or perform other operations on a schema.
  REVOKE DESCRIBE/LIST/DROP/ALTER/ALL ON SCHEMA <schemaname> FROM <username>/<rolename>;
  -- View the permissions granted on a schema.
  SHOW GRANTS ON SCHEMA <schemaname>;
  -- View the permissions granted on a schema for a specific role or user.
  SHOW GRANTS ON SCHEMA <schemaname> FOR USER/ROLE <username>/<rolename>;
                      

Grant and revoke permissions on schema objects

When granting permissions on a specific object inside a schema, use the full project.schema.table format. For a complete list of available permissions, see MaxCompute permissions. To manage permissions in the console instead, see Manage user permissions using the console.

-- Grant a role permissions on all tables in a schema.
GRANT schemaObjectPrivileges ON TABLE <project_name>.<schema_name>.* TO role {rolename};

-- Revoke from a role permissions on all tables in a schema.
REVOKE schemaObjectPrivileges ON TABLE <project_name>.<schema_name>.* FROM role {rolename};

-- Grant a role or user permissions on a specific table.
GRANT schemaObjectPrivileges ON TABLE <project_name>.<schema_name>.<tablename> TO {role|user} {rolename | USER name};

-- Revoke from a role or user permissions on a specific table.
REVOKE schemaObjectPrivileges ON TABLE <project_name>.<schema_name>.<tablename> FROM {role|user} {rolename | USER name};

-- View permissions on a table.
SHOW GRANTS ON TABLE <project_name>.<schema_name>.<tablename>;