MaxCompute:Configure storage tiers for storage resources

Manual configuration

For non-partitioned tables or partitions in partitioned tables, you can manually specify the IA or long-term storage tier. The setting takes effect immediately after manual configuration.

ALTER TABLE <TABLE_NAME> [PARTITION(<PARTITION_SPEC>)]
        SET <TBLPROPERTIES|PARTITIONPROPERTIES>("storagetier"="standard|lowfrequency|longterm");

Automatic configuration based on lifecycle rules

For projects or partitioned tables, you can define rules for the lifecycles of storage tiers. The system can trigger automatic conversion among storage tiers based on the lifecycle rules.

• After you define storage tier lifecycle rules for a project, if all the non-partitioned tables or partitions in the project meet the rules, the system automatically performs storage tier conversion for the non-partitioned tables or partitions.

• After you define storage tier lifecycle rules for a partitioned table, if all the partitions in the table meet the rules, the system automatically performs storage tier conversion for the partitions.

GET_PARTITION_META

Syntax

struct GET_PARTITION_META(<tableName>, <pt_col1>, <pt_col2>, ..., <pt_col_n>);

Parameter description

Return value description

Returns a Struct (struct<storagetier:string>) type value. The Struct contains one String field that describes the storage class of the corresponding partition.

Precautions

• When adding row-level rules to a table, you need to consider the access behavior of other users. If other users have accessed the table, set explicit rules for them to prevent unexpected access denial. For more details, see Row-level access control.

• MaxCompute tables can be accessed through SQL and external engines, such as Spark or Flink. However, the GET_PARTITION_META function is currently supported only by the MaxCompute SQL engine. Tables using the GET_PARTITION_META function in row-level access control are inaccessible by other engines.

• To access data, you must have data access privileges in addition to row-level permissions, specifically the SELECT permission on the data.

• Filter conditions in GET_PARTITION_META result in different partition pruning effects depending on the scenario:

  • The filter conditions involve only partition fields. For example, allowing access to standard storage with the first-level partition value being 2024.

    GET_PARTITION_META('storage_table', pt1, pt2).storagetier == 'standard') AND pt1='2024'

    When utilizing SQL to query storage_table, if the WHERE clause lacks partition conditions, the system automatically supports partition pruning to prevent a full table scan. This ensures that only the partitions meeting the requirements in storage_table are accessed for read-only mode.

  • The filter condition contains a non-partition field value and connects two filter conditions by using an AND operation. For example, allowing access to standard storage while concurrently applying an AND operation for non-partition values where a > 100.

    GET_PARTITION_META('storage_table', pt1, pt2).storagetier == 'standard') AND a > 100

    When utilizing SQL to query storage_table, if the WHERE clause lacks partition conditions, partition pruning is supported. In this case, only the partitions designated as standard are accessed.

  • The filter condition contains a non-partition field value and connects two filter conditions by using the OR operation. For example, allowing access to standard storage while concurrently applying an OR operation for non-partition values where a>100.

    get_partition_meta('storage_table', pt1, pt2).storagetier == 'standard') OR a > 100

    When utilizing SQL to query storage_table, you may encounter one of two scenarios:

    • If the WHERE clause lacks partition conditions, this results in a full table scan to retrieve data satisfying the condition a>100.

    • If the WHERE clause includes partition conditions, only data within the relevant partition that meets the condition standard or a>100 is scanned.

Examples

Define the partitioned table storage_table.

CREATE TABLE storage_table(a BIGINT, b BIGINT) PARTITIONED BY (pt1 STRING, pt2 STRING);

• Example 1:

  Grant the policy01 permission to the default user to access standard storage data in storage_table. Users cannot access IA or long-term storage data, and null values are returned.

  CREATE ROW ACCESS POLICY policy01 
  ON storage_table 
  TO DEFAULT 
  FILTER USING (get_partition_meta('storage_table', pt1, pt2).storagetier == 'standard');

• Example 2:

  Grant the policy02 permission to user user_x to access IA and long-term storage data in storage_table.

  CREATE ROW ACCESS POLICY policy02 
  ON storage_table 
  TO USER (user_x)  -- You can also grant permissions to a role by using the TO role rolename clause and then grant the role to the user.
  FILTER USING (get_partition_meta('storage_table', pt1, pt2).storagetier IN ('lowfrequency','longterm'));

  Two possible scenarios:

  • When the policy01 permission is configured for the project, all users (except user_x) are granted access to the standard storage data in storage_table. However, they do not have access to retrieve IA or long-term storage data, resulting in null return values. User user_x has access to the IA and long-term storage data within storage_table, but is denied access to the standard storage data, which also results in null values.

  • When the policy01 permission is not configured for the project, all users (except user_x) are denied access to any storage data in storage_table, resulting in null return values. Only user user_x is granted access to the IA and long-term storage data, but is denied access to the standard storage data, also resulting in null values.

• Example 3:

  Grant the policy03 permission to user user_y to access all data in storage_table.

  CREATE ROW ACCESS POLICY policy03 
  ON storage_table 
  TO USER (user_y)  -- You can also grant permissions to a role by using the TO role rolename clause and then grant the role to the user.
  FILTER USING (true);  -- The constant true indicates that all data in the table can be accessed

  Two possible scenarios :

  • When the policy01 permission is configured for the project, all users (except for user_y) are granted access to the standard storage data within the storage_table. However, they do not have access to retrieve IA or long-term storage data, resulting in null return values. User user_y has access to all storage classes in the storage_table.

  • When the policy01 permission is not configured for the project, all users (except for user_y) are denied to access any storage data within the storage_table, resulting in null return values. Only user_y is granted access to all storage class data in the storage_table.