MaxCompute:Storage resources - Tiered storage

Manual custom settings

You can manually set the storage tier of a non-partitioned table or a partition to IA or Archive. The setting takes effect immediately.

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

Automatically set using lifecycle rules

You can configure lifecycle rules for tiered storage at the project or partitioned table level. The system automatically converts storage tiers based on these rules.

• Project-level settings:

  After configuration, this rule serves as the default rule for all non-partitioned tables in the project and for partitions in partitioned tables that do not have specific rules defined. These tables and partitions are automatically converted to the corresponding storage tier.

• Partitioned table-level settings:

  After a specific rule is set for a partitioned table, it takes precedence over the project-level rule. If all partitions in the table match the rule, the partitions are automatically converted to the corresponding storage tier.

GET_PARTITION_META

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

Precautions

• When you add a row-level rule to a table, consider the access behavior of other users. If other users have accessed the table, set explicit rules for them to prevent unexpected access denial and data filtering issues. For more information, see Row-level access control.

• By default, MaxCompute tables can be accessed not only through SQL but also by external engines, such as Spark and Flink, that read table data. However, the GET_PARTITION_META function is currently supported only by the MaxCompute SQL engine. Therefore, if the GET_PARTITION_META function is used in row-level access control, the table can be read only through the MaxCompute SQL engine. Other engines cannot access the table.

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

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

  • The filter conditions involve only the partition fields of a partitioned table. For example, you can allow access to the Standard storage tier and require that the value of the first-level partition is 2024.

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

    When you use SQL to access storage_table, if the WHERE clause does not contain a partition condition, the system supports partition pruning to prevent a full table scan. Only partitions in storage_table that match the requirements are accessed.

  • The filter condition contains the value of a non-partition field, and the AND operator is used to connect the two filter conditions. For example, you can allow access to the Standard storage tier and use the AND operator with the non-partition value a>100.

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

    When you use SQL to access storage_table, if the WHERE clause does not contain a partition condition, partition pruning is still supported. Only partitions stored in the standard tier are accessed.

  • The filter condition contains the value of a non-partition field, and the OR operator is used to connect the two filter conditions. For example, you can allow access to the Standard storage tier and use the OR operator with the non-partition value a>100.

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

    When you use SQL to access storage_table, two scenarios may occur:

    • If the WHERE clause does not contain a partition condition, all partitions are accessed to scan for data that matches the a>100 condition.

    • If the WHERE clause contains a partition condition, only data in the corresponding partition that matches the standard or a>100 condition is scanned.

Usage 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. All users, including the project owner, cannot access IA or Archive storage data, and null values are returned for these requests.

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

  If the following keywords appear in the Summary section of Logview, row-level filtering is triggered.

  

• Example 2:

  Grant the policy02 permission to the user user_x to access IA and Archive 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 using TO role rolename and then grant the role to the user.
  FILTER USING (get_partition_meta('storage_table', pt1, pt2).storagetier IN ('lowfrequency','longterm'));

  Two scenarios may occur:

  • If the table already has the policy01 permission, all users except `user_x` can access Standard storage data in storage_table but cannot access IA or Archive storage data. Null values are returned for the inaccessible data. The user `user_x` can access IA and Archive storage data in storage_table but cannot access Standard storage data. A null value is returned for this inaccessible data.

  • If the policy01 permission is not configured for the table, all users except `user_x` are denied access to any storage data in storage_table, which results in null return values. Only `user_x` is granted access to IA and Archive storage data but is denied access to Standard storage data, which also results 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 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:

  • If the table has the policy01 permission, all users except `user_y` can access the Standard storage data in storage_table. They cannot access IA or Archive storage data, and a null value is returned for such requests. User user_y can access data from all storage classes in storage_table.

  • If the table does not have the policy01 permission, all users except `user_y` cannot access any data in storage_table, and a null value is returned. Only user user_y can access data of all storage classes in storage_table.