MaxCompute:Storage resources - Tiered storage

Choose a storage tier

Select a tier based on how often your data is read:

Set a tier manually

Set the storage tier of a non-partitioned table or a specific partition to IA or Archive. The change takes effect immediately.

For partitioned tables, set the tier on individual partitions, not on the table itself.

Syntax

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

Parameters

Example 1: Set a non-partitioned table to IA

ALTER TABLE tablename
SET TBLPROPERTIES("storagetier"="lowfrequency");

Verify the change with DESC EXTENDED. The StorageTier field shows the current tier:

--View table properties.
DESC extended tablename;

---The following result is returned:
+-------------------------------------------------------------------+
| Owner:                    ALIYUN$mofan_****@test.aliyunid.com      |
| Project:                  mf_mc_****                                |
| TableComment:                                                     |
+-------------------------------------------------------------------+
| CreateTime:               2021-11-18 15:14:00                     |
| LastDDLTime:              2023-09-11 14:34:55                     |
| LastModifiedTime:         2023-09-13 15:02:28                     |
| LastAccessTime:           2023-09-14 10:50:57                     |
+-------------------------------------------------------------------+
| InternalTable: YES      | Size: 1923683131                        |
+-------------------------------------------------------------------+
| Native Columns:                                                   |
+-------------------------------------------------------------------+
| Field| Type| Label |ExtendedLabel| Nullable| DefaultValue|Comment |
+-------------------------------------------------------------------+
| empno    | bigint |       |               | true     | NULL  |    |
| ename    | string |       |               | true     | NULL  |    |
| job      | string |       |               | true     | NULL  |    |
| mgr      | bigint |       |               | true     | NULL  |    |
| hiredate | datetime |     |               | true     | NULL  |    |
| sal      | bigint |       |               | true     | NULL  |    |
| comm     | bigint |       |               | true     | NULL  |    |
| deptno   | bigint |       |               | true     | NULL  |    |
+-------------------------------------------------------------------+
| Extended Info:                                                    |
+-------------------------------------------------------------------+
| TableID:                  8e0cc78c81ab4ad7af30bff7a8e****         |
| IsArchived:               false                                   |
| PhysicalSize:             5771049393                              |
| FileNum:                  3                                       |
| StoredAs:                 AliOrc                                  |
| CompressionStrategy:      normal                                  |
| odps.timemachine.retention.days: 1                                |
| ColdStorageStatus:        N/A                                     |
| encryption_enable:        false                                   |
| StorageTier:              lowfrequency                            |
| StorageTierLastModifiedTime:  2023-09-11 14:34:55                 |
+-------------------------------------------------------------------+

Example 2: Set a partition in the [bank_data_pt](https://www.alibabacloud.com/help/en/maxcompute/getting-started/create-tables-1#section-gwe-8vc-iwx) table to IA

ALTER TABLE bank_data_pt PARTITION (credit='yes') SET PARTITIONPROPERTIES ("storagetier" = 'lowfrequency');

Verify the partition tier:

--View partition properties.
DESC extended bank_data_pt PARTITION(credit='yes');

--The following result is returned:
+------------------------------------------------------------------------------------+
| PartitionSize: 0                                                                   |
+------------------------------------------------------------------------------------+
| CreateTime:               2024-05-10 10:28:16                                      |
| LastDDLTime:              2024-05-10 10:31:01                                      |
| LastModifiedTime:         2024-05-10 10:28:16                                      |
+------------------------------------------------------------------------------------+
| IsExstore:                false                                                    |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| ColdStorageStatus:        N/A                                                      |
| StorageTier:              LowFrequency                                             |
| StorageTierLastModifiedTime:  2024-05-10 10:31:01                                  |
+------------------------------------------------------------------------------------+

Set tiers automatically with lifecycle rules

Configure lifecycle rules to automatically convert storage tiers based on how long data has gone unmodified or unaccessed. Rules operate at the project level or partitioned table level.

• Project-level rules apply to all non-partitioned tables in the project and to partitions in partitioned tables that have no table-level rule defined.

• Partitioned table-level rules take precedence over project-level rules. When all partitions in a table meet the rule conditions, each matching partition is automatically converted.

SETPROJECT odps.table.lifecycle.config=<lifecycle_config_json_string>;

SETPROJECT odps.table.lifecycle.config=;

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <table_name>
 [PRIMARY KEY (<pk_col_name>, <pk_col_name2>),(<col_name> <data_type> [NOT NULL] [DEFAULT <default_value>] [comment <col_comment>], ...)]
 PARTITIONED BY (<col_name> <data_type> [comment <col_comment>], ...)
tblproperties ('lifecycle_config' = '<lifecycle_config_json_string>');

ALTER TABLE <table_name> SET TBLPROPERTIES ('lifecycle_config' = '<lifecycle_config_json_string>');

SHOW CREATE TABLE <table_name>;

ALTER TABLE <table_name> SET TBLPROPERTIES ('lifecycle_config'='{}');

{
  "TierToLowFrequency": {
    "DaysAfterLastModificationGreaterThan": <days>,
    "DaysAfterLastAccessGreaterThan": <days>
  },
  "TierToLongterm": {
    "DaysAfterLastModificationGreaterThan": <days>,
    "DaysAfterLastAccessGreaterThan": <days>
  }
}

{
  \"TierToLowFrequency\": {
    \"DaysAfterLastModificationGreaterThan\": <days>,
    \"DaysAfterLastAccessGreaterThan\": <days>
  },
  \"TierToLongterm\": {
    \"DaysAfterLastModificationGreaterThan\": <days>,
    \"DaysAfterLastAccessGreaterThan\": <days>
  }
}

setproject odps.table.lifecycle.config={"TierToLongterm":{"DaysAfterLastAccessGreaterThan":180},"TierToLowFrequency":{"DaysAfterLastAccessGreaterThan":120}};

--Set the rule when creating a partitioned table.
CREATE TABLE lifecycle_part_t (key string)
PARTITIONED BY (ds STRING)
tblproperties ('lifecycle_config' = '{\"TierToLowFrequency\": {\"DaysAfterLastModificationGreaterThan\": 2,\"DaysAfterLastAccessGreaterThan\": 2},\"TierToLongterm\": {\"DaysAfterLastModificationGreaterThan\": 4,\"DaysAfterLastAccessGreaterThan\": 7}}');

--Modify the rule for an existing partitioned table.
ALTER TABLE lifecycle_part_t SET tblproperties ('lifecycle_config'='{\"TierToLowFrequency\": {\"DaysAfterLastModificationGreaterThan\": 90,\"DaysAfterLastAccessGreaterThan\": 30},\"TierToLongterm\": {\"DaysAfterLastModificationGreaterThan\": 180,\"DaysAfterLastAccessGreaterThan\": 7}}');

GET_PARTITION_META

GET_PARTITION_META only works within row-level access control policies. It cannot be used in regular SQL queries.

Syntax

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

Parameters

Return value

Returns a Struct value of the type struct<storagetier:string>. The struct contains a single String field that describes the storage tier of the corresponding partition.

Usage notes

• When adding a row-level policy to a table, set explicit rules for all existing users to avoid unexpected access denial. For details, see Row-level access control.

• GET_PARTITION_META is only supported by the MaxCompute SQL engine. Tables using this function in row-level policies cannot be read by external engines such as Spark or Flink.

• To read data, users still need the SELECT permission on the table in addition to satisfying the row-level policy.

Partition pruning behavior

How the filter condition is written affects whether the query triggers a full table scan:

Examples

The following examples use the table storage_table:

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

Example 1: Restrict all users to Standard data only

Grant the policy01 permission to the default user, allowing access only to Standard storage data. IA and Archive data returns null for all users, including the project owner.

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: Allow a specific user to access IA and Archive data

Grant the policy02 permission to user_x, allowing access to IA and Archive data.

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'));

With both policy01 and policy02 in effect:

• All users except user_x can access Standard data but get null for IA and Archive data.

• user_x can access IA and Archive data but gets null for Standard data.

Without policy01:

• All users except user_x get null for all data in storage_table.

• user_x can access IA and Archive data but gets null for Standard data.

Example 3: Grant a user access to all storage tiers

Grant the policy03 permission to user_y, allowing access to all data regardless of storage tier.

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

With policy01 in effect:

• All users except user_y can access Standard data, but get null for IA and Archive data.

• user_y can access data in all storage tiers.

Without policy01:

• All users except user_y get null for all data in storage_table.

• user_y can access data in all storage tiers.