AnalyticDB：資料存放區冷熱分層

文法

ALTER TABLE <分區子表名> SET ttl interval '<scheduling_interval>' move to storage_cold;

您可以使用psql執行\d+查看具體分區表的子表名。

樣本

建立一個分區表tiered_storage_partition_hdfs。

CREATE TABLE tiered_storage_partition_hdfs(a int,b int) distributed by (a) partition by range(a) (start(1) end(20) every(10));

向分區子表tiered_storage_partition_hdfs_1_prt_1中寫入資料。

INSERT INTO tiered_storage_partition_hdfs_1_prt_1 values(1, 1), (2, 2), (3, 3), (4, 4);

指定三天后將分區子表tiered_storage_partition_hdfs_1_prt_1轉冷。例如，2023年07月17日09:00:00執行ALTER TABLE操作，那麼三天后（2023年07月20日09:00:00）會將tiered_storage_partition_hdfs_1_prt_1子分區整錶轉冷，其他分區均不會有任何遷移操作。

ALTER TABLE tiered_storage_partition_hdfs_1_prt_1 SET ttl interval '3days' move to storage_cold;

查詢指定表的冷熱狀態

您可以選擇任意一種方式查詢表的冷熱狀態。冷表返回cold，熱表返回hot。

• 方式一：

  SELECT pg_tiered_storage_relation_status('<table_name>'::regclass::oid::bigint);

• 方式二：

  SELECT pg_tiered_storage_relation_status(<表的oid>::bigint);

  通過SELECT oid FROM pg_class where relname='<table_name>';查詢表的oid。

文法

SELECT pg_tiered_storage_move_table_to_storage_cold('<schema_name>', '<分區子表名>');

您可以使用psql執行\d+查看具體分區表的子表名。

樣本

• 樣本一：對分區子表立即轉冷。

  在public的Schema中建立一個分區表tiered_storage_partition_oss。

  CREATE TABLE tiered_storage_partition_oss(a int,b int) DISTRIBUTED BY (a) PARTITION BY range(a) (start(1) end(20) every(10));

  說明

  本樣本會產生2個分區子表tiered_storage_partition_oss_1_prt_1和tiered_storage_partition_oss_1_prt_2。

  向分區子表tiered_storage_partition_oss_1_prt_1中寫入資料。

  INSERT INTO  tiered_storage_partition_oss_1_prt_1 VALUES(1, 1), (2, 2), (3, 3), (4, 4);

  將分區子表立即轉冷。

  SELECT pg_tiered_storage_move_table_to_storage_cold('public', 'tiered_storage_partition_oss_1_prt_1');

• 樣本二：使用pg_cron設定按天分區的分區子表定時轉冷。

  在資料庫etl中建立按天分區的daily_log_details分區表。

  CREATE TABLE daily_log_details (id INT, log_message text, created_date character varying(64))
  PARTITION BY LIST (created_date)
  (
     PARTITION p20230601 VALUES ('20230601'),
     PARTITION p20230602 VALUES ('20230602'),
     PARTITION p20230603 VALUES ('20230603'),
     PARTITION p20230604 VALUES ('20230604'),
     PARTITION p20230605 VALUES ('20230605'),
     PARTITION p20230606 VALUES ('20230606'),
     PARTITION p20230607 VALUES ('20230607'),
     PARTITION p20230608 VALUES ('20230608'),
     PARTITION p20230609 VALUES ('20230609'),
     PARTITION p20230610 VALUES ('20230610'),
     PARTITION p20230611 VALUES ('20230611'),
     DEFAULT PARTITION others
  );

  設定將10天前的分區子錶轉冷，執行使用者為etl_user，轉冷執行時間為3點。按照如下步驟操作。

  1. 在資料庫etl中建立清理函數。

     CREATE OR REPLACE FUNCTION pg_tiered_storage_move_partition_daily_table_to_cold_storage(schemaname text, tablename text) RETURNS void
     AS $$
     DECLARE
         fetch_overdue_partition_sql text;
         cold_storage_sql text;
         target record;
     BEGIN
         fetch_overdue_partition_sql := 'WITH targetpartitions AS
         (SELECT * FROM pg_partitions 
         WHERE tablename = $1
         AND schemaname = $2 
         AND partitionlevel = 1
         AND partitionisdefault = FALSE)
         SELECT partitiontablename FROM targetpartitions WHERE 
         to_date(substring(targetpartitions.partitionname FROM 2), ''YYYYMMDD'') <= current_date - INTERVAL ''10 days''';
         
         -- fetch overdue partitions
         FOR target IN EXECUTE fetch_overdue_partition_sql USING tablename, schemaname LOOP 
            cold_storage_sql := 'SELECT pg_tiered_storage_move_table_to_storage_cold($1::text, $2::text)';
            raise notice 'sql %', cold_storage_sql;
            EXECUTE cold_storage_sql USING schemaname, target.partitiontablename;
         END LOOP;
     END;
     $$
     LANGUAGE plpgsql;

  2. 串連到postgres資料庫執行轉冷語句。

     SELECT cron.schedule('etl_daily_transfer_to_cold', '0 3 * * *', 'SELECT pg_tiered_storage_move_partition_daily_table_to_cold_storage(''public'', ''daily_log_details'');', 'etl', 'etl_user');

• 樣本三：使用pg_cron設定按月分區的分區子表定時轉冷。

  在資料庫etl中建立按月分區的month_log_details分區表。

  CREATE TABLE month_log_details (id INT, log_message text, created_date character varying(64))
  PARTITION BY LIST (created_date)
  (
     PARTITION p202306 VALUES ('202306'),
     PARTITION p202307 VALUES ('202307'),
     PARTITION p202308 VALUES ('202308'),
     PARTITION p202309 VALUES ('202309'),
     PARTITION p202310 VALUES ('202310'),
     DEFAULT PARTITION others
  );

  設定將3個月前的分區子錶轉冷，執行使用者為etl_user，轉冷執行時間為5點。按照如下步驟操作。

  1. 在資料庫etl中建立清理函數。

     CREATE OR REPLACE FUNCTION pg_tiered_storage_move_partition_table_to_cold_storage(schemaname text, tablename text) RETURNS void
     AS $$
     DECLARE
         fetch_overdue_partition_sql text;
         cold_storage_sql text;
         target record;
     BEGIN
         fetch_overdue_partition_sql := 'WITH targetpartitions AS
         (SELECT * FROM pg_partitions 
         WHERE tablename = $1
         AND schemaname = $2 
         AND partitionlevel = 1
         AND partitionisdefault = FALSE)
         SELECT partitiontablename FROM targetpartitions WHERE 
         to_date(substring(targetpartitions.partitionname FROM 2), ''YYYYMM'') <= current_date - INTERVAL ''3 months''';
         
         -- fetch overdue partitions
         FOR target IN EXECUTE fetch_overdue_partition_sql USING tablename, schemaname LOOP 
            cold_storage_sql := 'SELECT pg_tiered_storage_move_table_to_storage_cold($1::text, $2::text)';
            raise notice 'sql %', cold_storage_sql;
            EXECUTE cold_storage_sql USING schemaname, target.partitiontablename;
         END LOOP;
     END;
     $$
     LANGUAGE plpgsql;

  2. 串連到postgres資料庫執行轉冷語句。

     SELECT cron.schedule('etl_month_transfer_to_cold', '0 5 1 * *', 'SELECT pg_tiered_storage_move_partition_table_to_cold_storage(''public'', ''month_log_details'');', 'etl', 'etl_user');

查詢指定表的冷熱狀態

執行以下語句，查詢表的冷熱狀態。冷表返回cold，熱表返回hot。

SELECT pg_tiered_storage_table_status('<schema_name>', '<table_name>|<分區子表名>')