CREATE DYNAMIC TABLE - Hologres

本文為您介紹如何建立Dynamic Table。

注意事項

Dynamic Table的使用限制請參見Dynamic Table支援範圍和限制。
從Hologres V3.1版本開始，建立Dynamic Table預設使用新文法。對於V3.0版本中已存在的Dynamic Table，僅支援執行ALTER操作，不支援使用V3.0版本的文法建立表。非分區表可以通過文法轉換命令，將V3.0版本的文法轉換成V3.1版本的新文法，如果是分區表，請手動重新建立。
從Hologres V3.1版本開始，增量重新整理的Dynamic Table需重新建立，您可以通過文法轉換命令進行重建。
從Hologres V3.1版本開始，引擎自適應最佳化Dynamic Table的Refresh執行過程，使Refresh更加穩定地執行。因此，與Refresh相關的Query ID出現負數的情況屬於正常現象。

文法

V3.1及以上版本（新文法）

說明

從Hologres V3.1版本開始，僅支援使用新文法建表。

建表文法

從Hologres V3.1版本開始，Dynamic Table的建表文法如下：

CREATE DYNAMIC TABLE [ IF NOT EXISTS ] [<schema_name>.]<table_name>
[ (<col_name> [, ...] ) ]
[LOGICAL PARTITION BY LIST(<partition_key>)]
WITH (
  -- dynamic table的屬性
  freshness = '<num> {minutes | hours}', -- 必填
  [auto_refresh_enable = {true | false},] -- 非必填
  [auto_refresh_mode = {'full' | 'incremental' | 'auto'},] -- 非必填
  
  [base_table_cdc_format = {'stream' | 'binlog'},] -- 非必填

  [auto_refresh_partition_active_time = '<num> {minutes | hours | days}',] -- 非必填
  [partition_key_time_format = {'YYYYMMDDHH24' | 'YYYY-MM-DD-HH24' | 'YYYY-MM-DD_HH24' | 'YYYYMMDD' | 'YYYY-MM-DD' | 'YYYYMM' | 'YYYY-MM' | 'YYYY'},] --非必填
  
  [computing_resource = {'local' | 'serverless' | '<warehouse_name>'},] -- 非必填，僅Hologres V4.0.7及以上版本支援參數值為warehouse_name。
  [refresh_guc_hg_experimental_serverless_computing_required_cores=xxx,]--非必填，計算資源規格
  
  [refresh_guc_<guc_name> = '<guc_value>',] -- 非必填

  -- 通用屬性
  [orientation = {'column' | 'row' | 'row,column'},]
  [table_group = '<tableGroupName>',]
  [distribution_key = '<columnName>[,...]]',]
  [clustering_key = '<columnName>[:asc] [,...]',]
  [event_time_column = '<columnName> [,...]',]
  [bitmap_columns = '<columnName> [,...]',]
  [dictionary_encoding_columns = '<columnName> [,...]',]
  [time_to_live_in_seconds = '<non_negative_literal>',]
  [storage_mode = {'hot' | 'cold'},]
)
AS
<query>; -- query的定義

參數說明

重新整理模式與資源

參數名	描述	是否必填	預設值
freshness	資料的新鮮度，單位為minutes \| hours，最小值為1mins，引擎會根據每次的Refresh時間、設定的freshness值，自動進行下一次Refresh，相比設定interval重新整理周期，freshness會更加自動化地最大程度保障資料的新鮮度。	是	無
auto_refresh_mode	重新整理模式。取值如下： auto：自動模式。如果Query支援增量重新整理，則優先執行增量重新整理，否則退化為全量重新整理。 incremental：增量重新整理，每次只重新整理增量資料。使用詳情請參見增量重新整理。 full：全量重新整理，每次都以全量的方式重新整理表資料。使用詳情請參見全量重新整理。	否	auto
auto_refresh_enable	開啟或關閉自動重新整理。取值為： true：開啟自動重新整理。 false：關閉自動重新整理。關閉之後，表的後續重新整理都會被停止。	否	true
base_table_cdc_format	增量重新整理消費基表的方式。 stream（預設）：從檔案層級讀取資料的變化記錄，計算增量資料，相比Binlog方式，無額外的儲存開銷，效能也更高。詳情請參見Dynamic Table。 binlog：以Binlog的方式消費基表的資料，設定了該參數後，還需要手動為基表開啟Binlog。詳情請參見訂閱Hologres Binlog。 `begin; call set_table_property('<table_name>', 'binlog.level', 'replica'); call set_table_property('<table_name>', 'binlog.ttl', '2592000'); commit;` 說明從V3.1版本開始，所有表都預設使用Stream方式消費基表資料變更。如果您的表開啟過Binlog，請及時關閉，以避免儲存浪費。當基表為行存表時，僅支援使用Binlog方式，不支援使用Stream方式。建立表後不支援修改該參數，如需修改請重建立表。	否	stream
computing_resource	詳見設定Dynamic Table重新整理資源中的參數說明。	否	serverless
refresh_guc_<guc_name>	支援對Refresh設定GUC參數，支援的GUC請參見GUC參數。	否	無

分區屬性

邏輯分區參數

參數名	描述	是否必填	預設值
LOGICAL PARTITION BY LIST(<partition_key>)	建立Dynamic Table為邏輯分區表，還需要為分區表設定`auto_refresh_partition_active_time`和`partition_key_time_format`兩個參數，才能使用。	否	無
auto_refresh_partition_active_time	分區的重新整理範圍。取值單位包括minutes \| hours \| days。系統會根據設定的auto_refresh_partition_active_time，從目前時間開始追溯歷史分區，自動重新整理範圍內的分區資料。活躍分區：是指`目前時間 - 分區開始時間（由分區名推匯出的時間）< auto_refresh_partition_active_time`。說明 auto_refresh_partition_active_time的單位必須大於一個Partition單位。例如按照天分區，auto_refresh_partition_active_time必須大於24h。支援修改該參數值，修改後僅對未來分區生效。	是	`分區時間單位 + 1 hour`，即允許基表的資料延遲為1小時，例如：如果按天分區，則預設值為25小時（1 day + 1 hour）。
partition_key_time_format	分區格式，當Dynamic Table為邏輯分區表時，系統會根據指定的分區格式產生對應的分區。分區欄位支援的類型及對應的資料格式如下： Partition Key為TEXT/VARCHAR類型： YYYYMMDDHH24、YYYY-MM-DD-HH24、YYYY-MM-DD_HH24、YYYYMMDD、YYYY-MM-DD、YYYYMM、YYYY-MM、YYYY。 Partition Key為INT類型： YYYYMMDDHH24、YYYYMMDD、YYYYMM、YYYY。 Partition Key為DATE類型： YYYY-MM-DD	是	無

物理分區參數

參數名

描述

是否必填

預設值

PARTITION BY LIST(<partition_key>)

建立Dynamic Table為普通分區表。

相比邏輯分區Dynamic Table，普通分區Dynamic Table沒有動態分區的能力，使用上存在一定的限制，建議優先使用邏輯分區。二者區別請參見CREATE LOGICAL PARTITION TABLE。

重要

從Hologres V3.1版本開始，使用新文法時，不支援建立Dynamic Table為物理分區表。

否

無

表屬性參數

參數	描述	是否必填	預設值
參數	描述	是否必填	full	incremental
col_name	Dynamic Table的欄位名。可以顯式指定Dynamic Table的列名，但是不能指定列的屬性和資料類型，引擎會自動推導。說明若指定了列的屬性和資料類型，會導致引擎推導不正確。	否	Query列名	Query列名
orientation	指定Dynamic Table的儲存模式，取值為`column`表示列存。	否	column	column
table_group	指定Dynamic Table所在的Table Group，預設為當前資料庫下的Default Table Group，詳情請參見Table Group與Shard Count操作指南。	否	預設Table Group名稱	預設Table Group名稱
distribution_key	指定Dynamic Table的Distribution Key，詳情請參見分布鍵Distribution Key。	否	無	無
clustering_key	指定Dynamic Table的Clustering Key，詳情請參見聚簇索引Clustering Key。	否	允許設定，有預設推導值。	允許設定，有預設推導值。
event_time_column	指定Dynamic Table的event_time_column，詳情請參見Event Time Column（Segment Key）。	否	無	無
bitmap_columns	指定Dynamic Table的bitmap_columns，詳情請參見位元影像索引Bitmap。	否	TEXT類型欄位	TEXT類型欄位
dictionary_encoding_columns	指定Dynamic Table的dictionary_encoding_columns，詳情請參見字典編碼Dictionary Encoding。	否	TEXT類型欄位	TEXT類型欄位
time_to_live_in_seconds	指定Dynamic Table資料的生命週期。	否	永久	永久
storage_mode	Dynamic Table的儲存模式，取值如下： hot：熱存。 cold：冷存。說明儲存模式詳情請參見資料階層式存放區。	否	hot	hot
binlog_level	Dynamic Table是否開啟Binlog。關於binlog的使用見訂閱Hologres Binlog。說明僅3.1.18及以上版本支援該參數。不建議為全量重新整理的dynamic table開啟Binlog。	否	none	none
binlog_ttl	Dynamic Table開啟Binlog後，Binlog的生命週期。	否	2592000	2592000

Query

Dynamic Table中資料產生的Query，設定的重新整理模式不同，支援的Query類型和基表類型也不同，詳情請參見Dynamic Table支援範圍和限制。

V3.0

建表文法

Hologres V3.0版本Dynamic Table的建表文法如下：

CREATE DYNAMIC TABLE [IF NOT EXISTS] <schema.tablename>(
[col_name],
[col_name]
  ) [PARTITION BY LIST (col_name)]
WITH (
    [refresh_mode='[full|incremental]',]
    [auto_refresh_enable='[true|false',]

  --增量重新整理專用參數：
    [incremental_auto_refresh_schd_start_time='[immediate|<timestamptz>]',]
    [incremental_auto_refresh_interval='[<num> {minute|minutes|hour|hours]',]  
    [incremental_guc_hg_computing_resource='[ local | serverless]',]
    [incremental_guc_hg_experimental_serverless_computing_required_cores='<num>',]

   --全量重新整理專用參數：
    [full_auto_refresh_schd_start_time='[immediate|<timestamptz>]',]
    [full_auto_refresh_interval='[<num> {minute|minutes|hour|hours]',] 
    [full_guc_hg_computing_resource='[ local | serverless]',]--hg_full_refresh_computing_resource預設serverless，可以db層級設定，使用者可以不設定
    [full_guc_hg_experimental_serverless_computing_required_cores='<num>',]
    
   --共用參數，允許設定guc：
   [refresh_guc_<guc>='xxx]',] 
   
  -- Dynamic Table的通用屬性：
    [orientation = '[column]',]
    [table_group = '[tableGroupName]',]
    [distribution_key = 'columnName[,...]]',]
    [clustering_key = '[columnName{:asc]} [,...]]',]
    [event_time_column = '[columnName [,...]]',]
    [bitmap_columns = '[columnName [,...]]',]
    [dictionary_encoding_columns = '[columnName [,...]]',]
    [time_to_live_in_seconds = '<non_negative_literal>',]
    [storage_mode = '[hot | cold]']
    ) 
AS
<query> --query的定義

參數說明

重新整理模式與資源

參數分類	參數名	描述	是否必填	預設值
共用重新整理參數	refresh_mode	指定資料的重新整理模式，支援full、incremental兩種重新整理模式。若不設定該參數，則表示不進行重新整理。	否	無
	auto_refresh_enable	是否開啟自動重新整理。取值為： true：開啟自動重新整理。 false：不開啟自動重新整理。	否	false
	refresh_guc_<guc>	支援對Refresh設定GUC參數，支援的GUC請參見GUC參數。說明例如GUC參數中`timezone`時區設定，參數配置`refresh_guc_timezone = 'GMT-8:00'`。	否	無
增量重新整理（incremental）	incremental_auto_refresh_schd_start_time	增量重新整理的開始時間。取值為： immediate：預設值，建表成功後立即啟動增量重新整理。 <timestamptz>：自訂的增量重新整理開始時間，例如設定為2024-08-24 1:00，則表示2024-08-24 1:00開始執行重新整理任務。	否	immediate
	incremental_auto_refresh_interval	增量重新整理的時間間隔，單位有minute、minutes、hour和hours。取值區間為[1min，48hours]。若不設定該參數，則表示只會在Refresh開始時間執行一次重新整理操作。	否	無
	incremental_guc_hg_computing_resource	指定執行增量重新整理的計算資源，取值為： local：本執行個體資源。 serverless：使用Serverless資源，但需注意查看執行個體是否滿足Serverless需求，詳情請參見Serverless Computing使用指南。說明支援使用`ALTER DATABASE xxx SET incremental_guc_hg_computing_resource=xx`命令在DB層級設定執行增量重新整理的計算資源。	否	local
	incremental_guc_hg_experimental_serverless_computing_required_cores	如果使用Serverless資源執行重新整理，則需要設定重新整理的計算資源量。說明不同規格的執行個體可使用的Serverless資源有一定的限制，詳情請參見Serverless Computing使用指南。	否	無
全量重新整理（full）	full_auto_refresh_schd_start_time	全量重新整理的開始時間。取值為： immediate：預設值，建表成功後立即啟動全量重新整理。 <timestamptz>：自訂的全量重新整理開始時間，例如設定為2024-08-24 1:00，則表示2024-08-24 1:00開始執行重新整理任務。	否	immediate
	full_auto_refresh_interval	全量重新整理的時間間隔，單位有minute、minutes、hour和hours。取值區間為[1min，48hours]。若不設定該參數，則表示只會在Refresh開始時間執行一次重新整理操作。	否	無
	full_guc_hg_computing_resource	指定執行全量重新整理的計算資源，取值為： local：本執行個體資源。 serverless：使用Serverless資源，但需注意查看執行個體是否滿足Serverless需求，詳情請參見Serverless Computing使用指南。說明支援使用`ALTER DATABASE xxx SET full_guc_hg_computing_resource=xx`命令在DB層級設定執行全量重新整理的計算資源。	否	local
	full_guc_hg_experimental_serverless_computing_required_cores	如果使用Serverless執行重新整理，則需要設定重新整理的計算資源量。說明不同規格的執行個體可使用的Serverless資源有一定的限制，詳情請參見Serverless Computing使用指南。	否	無

表屬性參數

參數	描述	是否必填	預設值
參數	描述	是否必填	full	incremental
col_name	Dynamic Table的欄位名。可以顯式指定Dynamic Table的列名，但是不能指定列的屬性和資料類型，引擎會自動推導。說明若指定了列的屬性和資料類型，會導致引擎推導不正確。	否	Query列名	Query列名
orientation	指定Dynamic Table的儲存模式。取值為`column`表示列存。	否	column	column
table_group	指定Dynamic Table所在的Table Group，預設為當前資料庫下的Default Table Group，詳情請參見Table Group與Shard Count操作指南。	否	預設Table Group名稱	預設Table Group名稱
distribution_key	指定Dynamic Table的Distribution Key，詳情請參見分布鍵Distribution Key。	否	無	無
clustering_key	指定Dynamic Table的Clustering Key，詳情請參見聚簇索引Clustering Key。	否	允許設定，有預設推導值。	允許設定，有預設推導值。
event_time_column	指定Dynamic Table的event_time_column，詳情請參見Event Time Column（Segment Key）。	否	無	無
bitmap_columns	指定Dynamic Table的bitmap_columns，詳情請參見位元影像索引Bitmap。	否	TEXT類型欄位	TEXT類型欄位
dictionary_encoding_columns	指定Dynamic Table的dictionary_encoding_columns，詳情請參見字典編碼Dictionary Encoding。	否	TEXT類型欄位	TEXT類型欄位
time_to_live_in_seconds	指定Dynamic Table資料的生命週期。	否	永久	永久
storage_mode	Dynamic Table的儲存模式，取值如下： hot：熱存， cold：冷存。說明儲存模式詳情請參見資料階層式存放區。	否	hot	hot
PARTITION BY LIST	是否為分區表，支援建立Dynamic Table分區表，使用方式與普通分區表相同，不同分區子表可以設定不同的重新整理模式，來滿足業務不同情境的時效性需求。	否	非分區表	非分區表

Query

Dynamic Table中資料產生的Query，設定的重新整理模式不同，支援的Query類型和基表類型也不同，詳情請參見Dynamic Table支援範圍和限制。

增量重新整理

增量重新整理通過自動感知基表資料的變化，將Query中的資料以增量的方式寫入Dynamic Table。相比於全量重新整理，增量重新整理處理的資料量更少，處理時效性會更高。在實際應用中，如果有分鐘級近即時資料查詢的需求，更推薦使用增量重新整理模式，但在使用增量Dynamic Table時需要注意如下幾點：

基表的限制：
- Hologres V3.1版本預設使用Stream模式消費增量資料。如果您的基表在V3.0版本已經開啟過Binlog，建議及時關閉，防止儲存成本增加。
- V3.0版本，需要為基表開啟Binlog，如果是維表JOIN，維表無需開啟Binlog。基表開啟Binlog後，會有一定的儲存開銷，您可參考表格儲存體明細查詢Binlog佔用儲存。
開啟增量重新整理後，系統會在後台產生一張狀態表，用於記錄中間彙總結果，關於狀態表技術原理請參見Dynamic Table。狀態表會儲存中間彙總資料，因此會佔用一定的儲存，查看儲存請參見查看Dynamic Table表結構和血緣。
當前增量重新整理支援的Query和運算元詳情，請參見Dynamic Table支援範圍和限制。

多表JOIN（雙流JOIN）

雙流JOIN即多表JOIN，語義與OLAP查詢相同，基於HASH JOIN實現，支援INNER JOIN、LEFT/RIGHT/FULL OUTER JOIN四種JOIN類型。

V3.1版本

說明

從Hologres V3.1版本開始，雙流JOIN的GUC預設開啟，無需再手動設定。

樣本SQL如下：

CREATE TABLE users (
  user_id INT,
  user_name TEXT,
  PRIMARY KEY (user_id)
);
INSERT INTO users VALUES(1, 'hologres');

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  PRIMARY KEY (order_id)
);
INSERT INTO orders VALUES(1, 1);

CREATE DYNAMIC TABLE dt WITH (
  auto_refresh_mode = 'incremental',
  freshness='10 minutes'
) 
AS 
SELECT order_id, orders.user_id, user_name 
FROM orders LEFT JOIN users ON orders.user_id = users.user_id;

-- 重新整理之後可以看到一條關聯資料
REFRESH TABLE dt;
SELECT * FROM dt;
 order_id | user_id | user_name 
----------+---------+-----------
        1 |       1 | hologres
(1 row)


UPDATE users SET user_name = 'dynamic table' WHERE user_id = 1;
INSERT INTO orders VALUES(4, 1);

-- 重新整理之後可以看到兩條關聯資料，維表更新對所有資料生效，可以訂正已關聯資料
REFRESH TABLE dt;
SELECT * FROM dt;

返回結果如下：

 order_id | user_id |   user_name   
----------+---------+---------------
        1 |       1 | dynamic table
        4 |       1 | dynamic table
(2 rows)

V3.0版本

Hologres從V3.0.26版本開始支援多表JOIN（雙流JOIN），在使用之前請先升級執行個體版本，同時需要執行如下GUC開啟雙流JOIN。

-- Session層級開啟
SET hg_experimental_incremental_dynamic_table_enable_hash_join TO ON;

--DB層級開啟，建立串連生效
ALTER database <db_name> SET hg_experimental_incremental_dynamic_table_enable_hash_join TO ON;

樣本SQL如下：

CREATE TABLE users (
  user_id INT,
  user_name TEXT,
  PRIMARY KEY (user_id)
) WITH (binlog_level = 'replica');
INSERT INTO users VALUES(1, 'hologres');

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  PRIMARY KEY (order_id)
)  WITH (binlog_level = 'replica');
INSERT INTO orders VALUES(1, 1);

CREATE DYNAMIC TABLE dt WITH (refresh_mode = 'incremental') 
AS 
SELECT order_id, orders.user_id, user_name 
FROM orders LEFT JOIN users ON orders.user_id = users.user_id;

-- 重新整理之後可以看到一條關聯資料
REFRESH TABLE dt;
SELECT * FROM dt;
 order_id | user_id | user_name 
----------+---------+-----------
        1 |       1 | hologres
(1 row)


UPDATE users SET user_name = 'dynamic table' WHERE user_id = 1;
INSERT INTO orders VALUES(4, 1);

-- 重新整理之後可以看到兩條關聯資料，維表更新對所有資料生效，可以訂正已關聯資料
REFRESH TABLE dt;
SELECT * FROM dt;

返回結果如下：

 order_id | user_id |   user_name   
----------+---------+---------------
        1 |       1 | dynamic table
        4 |       1 | dynamic table
(2 rows)

維表JON

維表JOIN的語義是：對每條資料，只會關聯當時維表的最新版本資料，即JOIN行為只發生在處理時間（Processing Time）。如果JOIN行為發生後，維表中的資料發生了變化（新增、更新或刪除），則已關聯的維表資料不會同步更新。樣本SQL如下：

說明

維表的JOIN操作與參與表的資料量無關，只要在SQL中採用維表的JOIN語義即可。

V3.1版本

CREATE TABLE users (
  user_id INT,
  user_name TEXT,
  PRIMARY KEY (user_id)
);
INSERT INTO users VALUES(1, 'hologres');

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  PRIMARY KEY (order_id)
)  WITH (binlog_level = 'replica');

INSERT INTO orders VALUES(1, 1);
CREATE DYNAMIC TABLE dt_join_2 WITH (
    auto_refresh_mode = 'incremental',
    freshness='10 minutes') 
AS 
SELECT order_id, orders.user_id, user_name 
-- FOR SYSTEM_TIME AS OF PROCTIME()用於標識users做為維表
FROM orders LEFT JOIN users FOR SYSTEM_TIME AS OF PROCTIME()
ON orders.user_id = users.user_id;

-- 重新整理之後可以看到一條關聯資料
REFRESH TABLE dt_join_2;
SELECT * FROM dt_join_2;
 order_id | user_id | user_name 
----------+---------+-----------
        1 |       1 | hologres
(1 row)


UPDATE users SET user_name = 'dynamic table' WHERE user_id = 1;
INSERT INTO orders VALUES(4, 1);

-- 重新整理之後可以看到兩條關聯資料，維表更新只對新增資料生效，無法訂正已關聯資料
REFRESH TABLE dt_join_2;
SELECT * FROM dt_join_2;

返回結果如下：

 order_id | user_id |   user_name   
----------+---------+---------------
        1 |       1 | hologres
        4 |       1 | dynamic table
(2 rows)

V3.0版本

CREATE TABLE users (
  user_id INT,
  user_name TEXT,
  PRIMARY KEY (user_id)
);
INSERT INTO users VALUES(1, 'hologres');

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  PRIMARY KEY (order_id)
)  WITH (binlog_level = 'replica');
INSERT INTO orders VALUES(1, 1);

CREATE DYNAMIC TABLE dt_join_2 WITH (refresh_mode = 'incremental') 
AS 
SELECT order_id, orders.user_id, user_name 
-- FOR SYSTEM_TIME AS OF PROCTIME()用於標識users做為維表
FROM orders LEFT JOIN users FOR SYSTEM_TIME AS OF PROCTIME()
ON orders.user_id = users.user_id;

-- 重新整理之後可以看到一條關聯資料
REFRESH TABLE dt_join_2;
SELECT * FROM dt_join_2;

order_id | user_id | user_name 
----------+---------+-----------
        1 |       1 | hologres
(1 row)
 
UPDATE users SET user_name = 'dynamic table' WHERE user_id = 1;
INSERT INTO orders VALUES(4, 1);

-- 重新整理之後可以看到兩條關聯資料，維表更新只對新增資料生效，無法訂正已關聯資料
REFRESH TABLE dt_join_2;
SELECT * FROM dt_join_2;

返回結果如下：

 order_id | user_id |   user_name   
----------+---------+---------------
        1 |       1 | hologres
        4 |       1 | dynamic table
(2 rows)

增量消費湖表（Paimon）

增量重新整理支援消費Paimon湖表，實現湖倉一體化。
支援External Dynamic Table增量讀寫湖倉資料並自動增量回寫至湖（Paimon），實現湖上資料快速加工，查詢加速和自動回寫的能力，簡化湖上資料加工體驗，降低成本。詳情見External Dynamic Table介紹。

全增量一體化重新整理

當前增量Dynamic Table也支援全增量一體消費，即首先消費Query命中的基表全量資料，再消費基表新增的資料。

V3.1版本

V3.1版本，全增量一體化重新整理預設開啟。使用樣本如下：

--準備基表，並開啟binlog插入資料
CREATE TABLE base_sales(
  day TEXT NOT NULL,
  hour INT,
  user_id BIGINT,
  ts TIMESTAMPTZ,
  amount FLOAT,
  pk text NOT NULL PRIMARY KEY
);

-- 為基表匯入資料
INSERT INTO base_sales values ('2024-08-29',1,222222,'2024-08-29 16:41:19.141528+08',5,'ddd');


-- 再為基表匯入增量資料
INSERT INTO base_sales VALUES ('2024-08-29',2,3333,'2024-08-29 17:44:19.141528+08',100,'aaaaa');


-- 建立自動重新整理的增量Dynamic Table，並開啟全增量資料一體消費的GUC
CREATE DYNAMIC TABLE sales_incremental
  WITH (
    auto_refresh_mode='incremental',
    freshness='10 minutes'
  ) 
AS 
  SELECT day, hour, SUM(amount), COUNT(1) 
    FROM base_sales 
  GROUP BY day, hour;

對比資料一致性：

查詢基表

SELECT day, hour, SUM(amount), COUNT(1) 
    FROM base_sales 
  GROUP BY day, hour;

返回結果：

day	    hour	sum	count
2024-08-29	2	100	1
2024-08-29	1	5	1

查詢Dynamic Table

SELECT * FROM sales_incremental;

返回結果：

day	    hour	sum	count
2024-08-29	1	5	1
2024-08-29	2	100	1

V3.0版本

V3.0版本，需要開啟GUC實現全增量一體化重新整理。使用樣本如下：

--準備基表，並開啟binlog插入資料
CREATE TABLE base_sales(
  day TEXT NOT NULL,
  hour INT,
  user_id BIGINT,
  ts TIMESTAMPTZ,
  amount FLOAT,
  pk text NOT NULL PRIMARY KEY
);

-- 為基表匯入資料
INSERT INTO base_sales values ('2024-08-29',1,222222,'2024-08-29 16:41:19.141528+08',5,'ddd');

-- 為基表開啟Binlog
ALTER TABLE base_sales SET (binlog_level = replica);

-- 再為基表匯入增量資料
INSERT INTO base_sales VALUES ('2024-08-29',2,3333,'2024-08-29 17:44:19.141528+08',100,'aaaaa');


-- 建立自動重新整理的增量Dynamic Table，並開啟全增量資料一體消費的GUC
CREATE DYNAMIC TABLE sales_incremental
  WITH (
    refresh_mode='incremental',
    incremental_auto_refresh_schd_start_time = 'immediate',
    incremental_auto_refresh_interval = '3 minutes',
    incremental_guc_hg_experimental_enable_hybrid_incremental_mode= 'true'
  ) 
AS 
  SELECT day, hour, SUM(amount), COUNT(1) 
    FROM base_sales 
  GROUP BY day, hour;

對比資料一致性：

查詢基表

SELECT day, hour, SUM(amount), COUNT(1) 
    FROM base_sales 
  GROUP BY day, hour;

返回結果：

day	    hour	sum	count
2024-08-29	2	100	1
2024-08-29	1	5	1

查詢Dynamic Table

SELECT * FROM sales_incremental;

返回結果：

day	    hour	sum	count
2024-08-29	1	5	1
2024-08-29	2	100	1

全量重新整理

全量重新整理會將Query中的資料以全量的方式寫入Dynamic Table。相比於增量重新整理，全量重新整理的優勢在於：

支援更多的基表類型。
支援更豐富的Query類型、運算元支援等。

全量重新整理相比增量重新整理，處理的資料量更多，消耗的資源可能更多，因此更推薦的應用情境包括：定期報表查看、定期回刷資料等。

說明

更多關於全量重新整理的資訊請參見全量重新整理。

使用樣本

V3.1版本

樣本1：建立增量重新整理Dynamic Table

執行下述操作前，請先參考一鍵匯入公用資料集將tpch_10g公用資料集的資料匯入至Hologres。

說明

建立增量Dynamic Table之前，需要為基表開啟Binlog（維表無需開啟）。

--建立單表增量重新整理的dynamic table，並指定開始重新整理時間，每3min重新整理一次。
CREATE DYNAMIC TABLE public.tpch_q1_incremental 
WITH (
auto_refresh_mode='incremental',
freshness='3 minutes'
) AS SELECT
        l_returnflag,
        l_linestatus,
        COUNT(*) AS count_order
FROM
        hologres_dataset_tpch_10g.lineitem
WHERE
        l_shipdate <= DATE '1998-12-01' - INTERVAL '120' DAY
GROUP BY
        l_returnflag,
        l_linestatus;

樣本2：建立多表JOIN的增量重新整理Dynamic Table

執行下述操作前，請先參考一鍵匯入公用資料集將tpch_10g公用資料集的資料匯入至Hologres。

說明

在建立增量Dynamic Table之前，需要為基表開啟Binlog（維表不需要開啟）。

--建立多表join的增量重新整理dynamic table
CREATE DYNAMIC TABLE dt_join
  WITH (
    auto_refresh_mode='incremental',
    freshness='30 minutes'
  ) 
AS 
SELECT
        l_shipmode,
        SUM(CASE
                WHEN o_orderpriority = '1-URGENT'
                        OR o_orderpriority = '2-HIGH'
                        THEN 1
                ELSE 0
        END) AS high_line_count,
        SUM(CASE
                WHEN o_orderpriority <> '1-URGENT'
                        AND o_orderpriority <> '2-HIGH'
                        THEN 1
                ELSE 0
        END) AS low_line_count
FROM
        hologres_dataset_tpch_10g.orders,
        hologres_dataset_tpch_10g.lineitem
WHERE
        o_orderkey = l_orderkey
        AND l_shipmode IN ('FOB', 'AIR')
        AND l_commitdate < l_receiptdate
        AND l_shipdate < l_commitdate
        AND l_receiptdate >= DATE '1997-01-01'
        AND l_receiptdate < DATE '1997-01-01' + INTERVAL '1' YEAR
GROUP BY
        l_shipmode;

樣本3：建立自動重新整理Dynamic Table

將Dynamic Table設定為自動重新整理，引擎自動選擇重新整理模式，優先使用增量重新整理，若不支援增量重新整理，則退化為全量重新整理。

執行下述操作前，請先參考一鍵匯入公用資料集將tpch_10g公用資料集的資料匯入至Hologres。

--建立自動重新整理的dynamic table，引擎自動選擇重新整理模型。執行的結果是使用增量重新整理模式
CREATE DYNAMIC TABLE thch_q6_auto
  WITH (
    auto_refresh_mode='auto',
    freshness='1 hours'
       ) 
AS
SELECT
        SUM(l_extendedprice * l_discount) AS revenue
FROM
        hologres_dataset_tpch_100g.lineitem
WHERE
        l_shipdate >= DATE '1996-01-01'
        AND l_shipdate < DATE '1996-01-01' + INTERVAL '1' YEAR
        AND l_discount BETWEEN 0.02 - 0.01 AND 0.02 + 0.01
        AND l_quantity < 24;

樣本4：建立邏輯分區Dynamic Table

以即時交易大屏為例，業務通常會有近即時查看當天資料，同時又需要修正歷史資料的即時離線一體化分析需求（詳情請參見業務與資料認知）。我們通常使用Dynamic Table邏輯分區來實現該情境。具體方案如下：

基表是按天分區，最新分區是Flink即時/近即時寫入，歷史分區資料從MaxCompute寫入。
Dynamic Table基於基表建立，使用邏輯分區表，最新的2個分區是活躍分區，通過增量重新整理模式重新整理，滿足業務的近即時資料分析需求
歷史分區是不活躍的分區，使用全量重新整理模式，如果源錶的歷史分區有進行過資料修正/回刷，可以將歷史分區使用全量重新整理進行回刷

本樣本使用Github公用資料集進行示範。

準備基表。

基表的最新資料來源於Flink即時寫入，詳細操作流程請參見基於GitHub公開事件數目據集的離線即時一體化實踐。

DROP TABLE IF EXISTS gh_realtime_data;

BEGIN;
CREATE TABLE gh_realtime_data (
    id BIGINT,
    actor_id BIGINT,
    actor_login TEXT,
    repo_id BIGINT,
    repo_name TEXT,
    org_id BIGINT,
    org_login TEXT,
    type TEXT,
    created_at timestamp with time zone NOT NULL,
    action TEXT,
    iss_or_pr_id BIGINT,
    number BIGINT,
    comment_id BIGINT,
    commit_id TEXT,
    member_id BIGINT,
    rev_or_push_or_rel_id BIGINT,
    ref TEXT,
    ref_type TEXT,
    state TEXT,
    author_association TEXT,
    language TEXT,
    merged BOOLEAN,
    merged_at TIMESTAMP WITH TIME ZONE,
    additions BIGINT,
    deletions BIGINT,
    changed_files BIGINT,
    push_size BIGINT,
    push_distinct_size BIGINT,
    hr TEXT,
    month TEXT,
    year TEXT,
    ds TEXT,
    PRIMARY KEY (id,ds)
)
PARTITION BY LIST (ds);
CALL set_table_property('public.gh_realtime_data', 'distribution_key', 'id');
CALL set_table_property('public.gh_realtime_data', 'event_time_column', 'created_at');
CALL set_table_property('public.gh_realtime_data', 'clustering_key', 'created_at');

COMMENT ON COLUMN public.gh_realtime_data.id IS '事件ID';
COMMENT ON COLUMN public.gh_realtime_data.actor_id IS '事件發起人ID';
COMMENT ON COLUMN public.gh_realtime_data.actor_login IS '事件發起人登入名稱';
COMMENT ON COLUMN public.gh_realtime_data.repo_id IS 'repoID';
COMMENT ON COLUMN public.gh_realtime_data.repo_name IS 'repo名稱';
COMMENT ON COLUMN public.gh_realtime_data.org_id IS 'repo所屬組織ID';
COMMENT ON COLUMN public.gh_realtime_data.org_login IS 'repo所屬組織名稱';
COMMENT ON COLUMN public.gh_realtime_data.type IS '事件類型';
COMMENT ON COLUMN public.gh_realtime_data.created_at IS '事件發生時間';
COMMENT ON COLUMN public.gh_realtime_data.action IS '事件行為';
COMMENT ON COLUMN public.gh_realtime_data.iss_or_pr_id IS 'issue/pull_request ID';
COMMENT ON COLUMN public.gh_realtime_data.number IS 'issue/pull_request 序號';
COMMENT ON COLUMN public.gh_realtime_data.comment_id IS 'comment（評論）ID';
COMMENT ON COLUMN public.gh_realtime_data.commit_id IS '提交記錄ID';
COMMENT ON COLUMN public.gh_realtime_data.member_id IS '成員ID';
COMMENT ON COLUMN public.gh_realtime_data.rev_or_push_or_rel_id IS 'review/push/release ID';
COMMENT ON COLUMN public.gh_realtime_data.ref IS '建立/刪除的資源名稱';
COMMENT ON COLUMN public.gh_realtime_data.ref_type IS '建立/刪除的資源類型';
COMMENT ON COLUMN public.gh_realtime_data.state IS 'issue/pull_request/pull_request_review的狀態';
COMMENT ON COLUMN public.gh_realtime_data.author_association IS 'actor與repo之間的關係';
COMMENT ON COLUMN public.gh_realtime_data.language IS '程式設計語言';
COMMENT ON COLUMN public.gh_realtime_data.merged IS '是否接受合并';
COMMENT ON COLUMN public.gh_realtime_data.merged_at IS '代碼合并時間';
COMMENT ON COLUMN public.gh_realtime_data.additions IS '代碼增加行數';
COMMENT ON COLUMN public.gh_realtime_data.deletions IS '代碼減少行數';
COMMENT ON COLUMN public.gh_realtime_data.changed_files IS 'pull request 改變檔案數量';
COMMENT ON COLUMN public.gh_realtime_data.push_size IS '提交數量';
COMMENT ON COLUMN public.gh_realtime_data.push_distinct_size IS '不同的提交數量';
COMMENT ON COLUMN public.gh_realtime_data.hr IS '事件發生所在小時，如00點23分，hr=00';
COMMENT ON COLUMN public.gh_realtime_data.month IS '事件發生所在月，如2015年10月，month=2015-10';
COMMENT ON COLUMN public.gh_realtime_data.year IS '事件發生所在年，如2015年，year=2015';
COMMENT ON COLUMN public.gh_realtime_data.ds IS '事件發生所在日，ds=yyyy-mm-dd';

COMMIT;

建立Dynamic Table邏輯分區表。

CREATE  DYNAMIC TABLE ads_dt_github_event
LOGICAL PARTITION BY LIST(ds)
WITH (
  -- dynamic table的屬性
  freshness = '5 minutes', 
  auto_refresh_mode = 'auto', 
  auto_refresh_partition_active_time = '2 days' ,
  partition_key_time_format = 'YYYY-MM-DD'
)
AS
SELECT
    repo_name,
    COUNT(*) AS events,
    ds
FROM
    gh_realtime_data
GROUP BY repo_name,ds

查詢Dynamic Table。
```
SELECT * FROM ads_dt_github_event ;
```
回刷歷史分區。
如果基表有歷史資料變更，例如2025-04-01的資料有變更，Dynamic Table的資料需要同步更新，可以將Dynamic Table的歷史分區設定成全量重新整理模式，並結合Serverless執行一次重新整理。
```
REFRESH OVERWRITE DYNAMIC TABLE ads_dt_github_event
PARTITION (ds = '2025-04-01') 
WITH (
  refresh_mode = 'full'
);
```

樣本5：通過Dynamic Table增量重新整理計算任意長周期UV計算

從Hologres V3.1版本開始，Dynamic Table增量重新整理支援RB_BUILD_AGG函數，以實現任意長周期的UV等計算，相比原預彙總方案，增量重新整理的優勢在於：

更快的效能：每次計算時只需要計算增量的資料，計算效能更快。
更低的成本：參與計算的資料量減少，使用的計算資源也隨之減少，有效降低成本。可以支援更長周期的資料量計算。

樣本如下：

準備一張使用者明細表。

BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
     uid INT,
     country TEXT,
     prov TEXT,
     city TEXT,
     channel TEXT,
     operator TEXT,
     brand TEXT,
     ip TEXT,
     click_time TEXT,
     year TEXT,
     month TEXT,
     day TEXT,
     ymd TEXT NOT NULL
);
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
--distribution_key根據需求設定，根據該表的即時查詢需求，從什麼維度做分區能夠取得較好效果即可
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
--用於做where過濾條件，包含完整年月日時間欄位推薦設為clustering_key和event_time_column
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

通過Dynamic Table增量重新整理計算UV。

CREATE DYNAMIC TABLE ads_uv_dt
  WITH (
    freshness = '5 minutes', 
    auto_refresh_mode = 'incremental') 
  AS 
SELECT  
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1)
FROM    ods_app_detail
WHERE ymd >= '20231201' AND ymd <='20240502'
GROUP BY country,prov,city,ymd;

查詢任意周期的UV。

SELECT  
RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  SUM(pv) AS pv
FROM    ads_uv_dt
WHERE   ymd = '20240329'
GROUP BY country,prov,city;

V3.0版本

樣本1：建立全量重新整理Dynamic Table並自動開始執行重新整理

執行下述操作前，請先參考一鍵匯入公用資料集將tpch_10g公用資料集的資料匯入至Hologres。

--建立 “test” Schema
CREATE SCHEMA test;

--建立單表全量重新整理的dynamic table，並立即開始重新整理，每1小時重新整理一次。
CREATE DYNAMIC TABLE test.thch_q1_full
  WITH (
    refresh_mode='full',
    auto_refresh_enable='true',
    full_auto_refresh_interval='1 hours',
    full_guc_hg_computing_resource='serverless',
    full_guc_hg_experimental_serverless_computing_required_cores='32'
       ) 
AS
  SELECT
        l_returnflag,
        l_linestatus,
        SUM(l_quantity) AS sum_qty,
        SUM(l_extendedprice) AS sum_base_price,
        SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
        SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
        AVG(l_quantity) AS avg_qty,
        AVG(l_extendedprice) AS avg_price,
        AVG(l_discount) AS avg_disc,
        COUNT(*) AS count_order
FROM
        hologres_dataset_tpch_10g.lineitem
WHERE
        l_shipdate <= DATE '1998-12-01' - INTERVAL '120' DAY
GROUP BY
        l_returnflag,
        l_linestatus;

樣本2：建立增量重新整理的Dynamic Table並指定開始重新整理時間

執行下述操作前，請先參考一鍵匯入公用資料集將tpch_10g公用資料集的資料匯入至Hologres。

建立增量Dynamic Table樣本如下：

說明

在建立增量Dynamic Table之前，需要為基表開啟Binlog（維表不需要開啟）。

--為基表開binlog：
BEGIN;
CALL set_table_property('hologres_dataset_tpch_10g.lineitem', 'binlog.level', 'replica');
COMMIT;

--建立單表增量重新整理的dynamic table，並指定開始重新整理時間，每3min重新整理一次
CREATE DYNAMIC TABLE public.tpch_q1_incremental 
WITH (
refresh_mode='incremental',
auto_refresh_enable='true',
incremental_auto_refresh_schd_start_time='2024-09-15 23:50:0',
incremental_auto_refresh_interval='3 minutes',
incremental_guc_hg_computing_resource='serverless',
incremental_guc_hg_experimental_serverless_computing_required_cores='30'
) AS SELECT
        l_returnflag,
        l_linestatus,
        COUNT(*) AS count_order
FROM
        hologres_dataset_tpch_10g.lineitem
WHERE
        l_shipdate <= DATE '1998-12-01' - INTERVAL '120' DAY
GROUP BY
        l_returnflag,
        l_linestatus
;

樣本3：建立多表JOIN的全量重新整理Dynamic Table

--建立query為多表join的dynamic table，全量重新整理模式，每3小時重新整理一次。
CREATE DYNAMIC TABLE dt_q_full
  WITH (
    refresh_mode='full',
    auto_refresh_enable='true',
    full_auto_refresh_schd_start_time='immediate',
    full_auto_refresh_interval='3 hours',
    full_guc_hg_computing_resource='serverless',
    full_guc_hg_experimental_serverless_computing_required_cores='64'
  ) 
AS 
SELECT
        o_orderpriority,
        COUNT(*) AS order_count
FROM
        hologres_dataset_tpch_10g.orders
WHERE
        o_orderdate >= DATE '1996-07-01'
        AND o_orderdate < DATE '1996-07-01' + INTERVAL '3' MONTH
        AND EXISTS (
                SELECT
                        *
                FROM
                        hologres_dataset_tpch_10g.lineitem
                WHERE
                        l_orderkey = o_orderkey
                        AND l_commitdate < l_receiptdate
        )
GROUP BY
        o_orderpriority;

樣本4：建立維表JOIN的增量重新整理Dynamic Table

建立維表JOIN的增量Dynamic Table樣本如下：

說明

在建立增量Dynamic Table之前，需要為基表開啟Binlog（維表不需要開啟）。

維表JOIN的語義是：對每條資料，只會關聯當時維表的最新版本資料，即JOIN行為只發生在處理時間（Processing Time）。如果JOIN行為發生後，維表中的資料發生了變化（新增、更新或刪除），則已關聯的維表資料不會被同步變化。SQL樣本如下：

--明細表
BEGIN;
CREATE TABLE public.sale_detail(
        app_id TEXT,
        uid TEXT,
        product TEXT,
        gmv BIGINT,
        order_time TIMESTAMPTZ
);
--為基表開binlog，維表不需要開啟
CALL set_table_property('public.sale_detail', 'binlog.level', 'replica');
COMMIT;

--屬性工作表
CREATE TABLE public.user_info(
        uid TEXT,
        province TEXT,
        city TEXT
);

CREATE DYNAMIC TABLE public.dt_sales_incremental
  WITH (
    refresh_mode='incremental',
    auto_refresh_enable='true',
    incremental_auto_refresh_schd_start_time='2024-09-15 00:00:00',
    incremental_auto_refresh_interval='5 minutes',
    incremental_guc_hg_computing_resource='serverless',
    incremental_guc_hg_experimental_serverless_computing_required_cores='128') 
AS 
SELECT 
    sale_detail.app_id,
    sale_detail.uid,
    product,
    SUM(sale_detail.gmv) AS sum_gmv,
    sale_detail.order_time,
    user_info.province,
    user_info.city 
FROM public.sale_detail 
INNER JOIN public.user_info  FOR SYSTEM_TIME AS OF PROCTIME()
ON sale_detail.uid =user_info.uid
GROUP BY sale_detail.app_id,sale_detail.uid,sale_detail.product,sale_detail.order_time,user_info.province,user_info.city;

樣本5：建立分區Dynamic Table

以即時交易大屏為例，業務通常會有近即時查看當天資料，同時又需要修正歷史資料的需求。在這種情境下，我們通常使用Dynamic Table增量重新整理和全量重新整理來實現。做法如下：

建立分區表作為基表，最新分區採用即時/近即時寫入方式，歷史分區偶爾有資料修正的操作。
建立Dynamic Table，將其作為分區父表，最新的分區使用增量重新整理模式，滿足業務的近即時資料分析需求。
將歷史分區切換成全量重新整理模式，如果源錶的歷史分區進行過資料修正/回刷，則Dynamic Table的歷史分區也可以使用全量重新整理模式進行一次回刷，同時建議結合Serverless提升回刷速度。

樣本如下：

準備基表和資料。

基表為分區表，最新分區採用即時資料寫入方式。

-- 建立分區源表
CREATE TABLE base_sales(
  uid INT,
  opreate_time TIMESTAMPTZ,
  amount FLOAT,
  tt TEXT NOT NULL,
  ds TEXT,
  PRIMARY KEY(ds)
) PARTITION BY LIST (ds) ;

--歷史分區
CREATE TABLE base_sales_20240615 PARTITION OF base_sales FOR VALUES IN ('20240615');
INSERT INTO base_sales_20240615 VALUES (2,'2024-06-15 16:18:25.387466+08','111','2','20240615');

--最新分區，一般是即時寫入
CREATE TABLE base_sales_20240616 PARTITION OF base_sales FOR VALUES IN ('20240616');
INSERT INTO base_sales_20240616 VALUES (1,'2024-06-16 16:08:25.387466+08','2','1','20240616');

建立Dynamic Table分區父表，父表只設定Query的定義，不設定重新整理模式。

--建立extension
CREATE EXTENSION roaringbitmap;

CREATE DYNAMIC TABLE partition_dt_base_sales
PARTITION BY LIST (ds)
as
SELECT  
 public.RB_BUILD_AGG(uid),
 opreate_time,
 amount,
 tt,
 ds,
COUNT(1)
FROM    base_sales
GROUP BY opreate_time ,amount,tt,ds;

建立子表，並為子表設定重新整理模式。

您可手動建立Dynamic Table分區子表，也可以使用DataWorks資料開發動態建立Dynamic Table分區子表。最新分區建立為增量重新整理模式，歷史分區設定為全量重新整理模式。

-- 為基表開啟Binlog
ALTER TABLE base_sales SET (binlog_level = replica);

-- 假設歷史Dynamic Table分區子表如下：
CREATE DYNAMIC TABLE partition_dt_base_sales_20240615 PARTITION OF partition_dt_base_sales FOR VALUES IN ('20240615')
  WITH (
    refresh_mode='incremental',
    auto_refresh_enable='true',
    incremental_auto_refresh_schd_start_time='immediate',
    incremental_auto_refresh_interval='30 minutes'
       );

-- 建立新的Dynamic Table分區子表,並指定最新分區的重新整理模式為增量重新整理，建表成功後立即開始啟動重新整理，重新整理間隔為30分鐘，並使用本執行個體資源重新整理。
CREATE DYNAMIC TABLE partition_dt_base_sales_20240616 PARTITION OF partition_dt_base_sales FOR VALUES IN ('20240616')
  WITH (
    refresh_mode='incremental',
    auto_refresh_enable='true',
    incremental_auto_refresh_schd_start_time='immediate',
    incremental_auto_refresh_interval='30 minutes'
       );

--將歷史分區轉換為全量重新整理模式
ALTER DYNAMIC TABLE partition_dt_base_sales_20240615 SET (refresh_mode = 'full');
--如果歷史分區需要資料訂正，可以執行一次refresh，建議配合serverless來執行。
SET hg_computing_resource = 'serverless';
REFRESH DYNAMIC TABLE partition_dt_base_sales_20240615;

文法轉換命令

Hologres 自V3.1版本開始，Dynamic Table的文法有所升級。當您從V3.0版本升級後，需要使用V3.1文法重建立表，Hologres提供文法轉換工具，方便您更加簡便的完成文法轉換。

需重建立表情境

增量重新整理的Dynamic Table：若是Dynamic Table是增量重新整理，則必須重新建立。
升級檢查中文法不相容，必須重建立表，具體情況需以升級檢查報告為準。

說明

通過V3.0文法建立的Dynamic Table除以上情境外，可以不需要重新建立，但只能對錶執行ALTER，不能使用V3.0版本文法建表。

轉換文法命令的適用情境

僅適用於非分區表（重新整理模式包含增量重新整理和全量重新整理），如果您在V3.0版本是Dynamic Table分區表，需要手動重建，並建議您使用Dynamic Table邏輯分區。

查看需要轉換的表

通過如下命令可以查看當前執行個體中升級後需要轉換的表：

非分區表

SELECT DISTINCT 
    p.dynamic_table_namespace as table_namespace, 
    p.dynamic_table_name as table_name
FROM hologres.hg_dynamic_table_properties p
JOIN pg_class c ON c.relname = p.dynamic_table_name
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = p.dynamic_table_namespace
WHERE p.property_key = 'refresh_mode' 
    AND p.property_value = 'incremental'
    AND c.relispartition = false 
    AND c.relkind != 'p';

分區表

SELECT DISTINCT 
    pn.nspname as parent_schema,
    pc.relname as parent_name
FROM hologres.hg_dynamic_table_properties p
JOIN pg_class c ON c.relname = p.dynamic_table_name
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = p.dynamic_table_namespace
JOIN pg_inherits i ON c.oid = i.inhrelid
JOIN pg_class pc ON pc.oid = i.inhparent
JOIN pg_namespace pn ON pn.oid = pc.relnamespace
WHERE p.property_key = 'refresh_mode' 
    AND p.property_value = 'incremental'
    AND c.relispartition = true 
    AND c.relkind != 'p';

轉換文法命令的使用

在使用轉換文法命令需注意以下情況：

僅Hologres V3.1.11及以上版本支援該命令文法。
需要Superuser執行該命令。
執行完成後，將會有如下行為變更：
- 如果表開啟了自動重新整理，將會立即啟動自動重新整理，建議在業務低峰期執行，避免任務同一時間啟動相互搶佔資源，同時也建議使serverless執行重新整理，可以更好的將重新整理任務隔離。
- 資源使用的變更：
  - 使用3.1版本新文法（3.1版本和3.2版本），對於計算群組型執行個體，使用的warehouse資源為base表所在TG的leader warehouse以及Dynamic Table表所在TG的leader Warehouse資源。
  - 使用老版本文法（3.0版本）或者執行個體是4.1版本使用新文法，對於計算群組型執行個體，使用的warehouse資源為Dynamic Table所在的TG的leader warehouse。
- 串連數的變更：新文法將會使用更加穩定和高效的底層調度能力，每個Dynamic Table將會多一個串連，如果執行個體的串連數使用率較高，且執行個體中dynamic table數量比較多（幾百張），建議先適當清理空閑串連。

轉換文法的命令如下：

--僅適用於非分區表（重新整理模式包含全量重新整理和增量重新整理），分區表請手動重新建立，並建議使用邏輯分區。
--單個錶轉換
call hg_dynamic_table_config_upgrade('<table_name>');

--批量轉換所有表，請謹慎使用。
call hg_upgrade_all_normal_dynamic_tables();

說明

該方式會將當前資料庫下所有V3.0版本的Dynamic Table分區錶轉換為V3.1版本。

文法參數映射

使用文法轉換命令，V3.0版本和V3.1版本的參數以及對應的值對應如下：

V3.0版本參數名	V3.1版本參數名	說明
refresh_mode	auto_refresh_mode	轉換後與轉換前參數值保持一致。例如：轉換前配置為`refresh_mode='incremental'`，轉換後為`auto_refresh_mode='incremental'`。
auto_refresh_enable	auto_refresh_enable	參數值保持一致。
{refresh_mode}_auto_refresh_schd_start_time	freshness	auto_refresh_interval的值將會為轉換為freshness的值。例如：轉換前配置為`full_auto_refresh_interval='30 minutes'`，轉換後為`freshness='30 minutes'`。
{refresh_mode}_auto_refresh_interval	freshness
{refresh_mode}_guc_hg_computing_resource	computing_resource	轉換後與轉換前參數值保持一致。例如：轉換前配置為`full_guc_hg_computing_resource='serverless'`，轉換後為`computing_resource='serverless'`。
{refresh_mode}_guc_hg_experimental_serverless_computing_required_cores	refresh_guc_hg_experimental_serverless_computing_required_cores	轉換後與轉換前參數值保持一致。
{refresh_mode}_guc_<guc>	refresh_guc_<guc_name>	轉換後與轉換前參數值保持一致。例如：轉換前配置為 `incremental_guc_hg_experimental_max_consumed_rows_per_refresh='1000000'`，轉換後為`refresh_guc_hg_experimental_max_consumed_rows_per_refresh='1000000'` 。
orientation等表屬性	orientation等表屬性	表的基本屬性都會保持不變。

下一步：管理Dynamic Table

Dynamic Table建立成功後，您可以執行如下操作：

查看Dynamic Table DDL、血緣關係等。詳情請參見查看Dynamic Table表結構和血緣。
修改Dynamic Table相關屬性，詳情請參見ALTER DYNAMIC TABLE。

常見問題

問題現象：Segment Key/Clustering Key為空白報錯。報錯樣本如下：

ERROR: commit ddl phase1 failed: the index partition key "xxx" should not be nullable

問題原因：Dynamic Table的Segment Key/Clustering是個允許非空值的列。Segment Key和Clustering Key的設定規則詳情，請參見Event Time Column（Segment Key）。
解決方案：
1. 如果報錯的欄位為Clustering Key，則在Hologres V3.1.26、V3.2.9、V4.0.0及之後的版本中所建立的增量重新整理表將不會出現報錯。而在這些版本之前建立的表，請務必先升級執行個體版本，然後執行如下GUC，以修改表屬性，允許Clustering Key 為空白。
```
--3.1及以上版本文法，允許segment key為nullable
ALTER DYNAMIC TABLE [ IF EXISTS ] [<schema>.]<table_name> SET
(refresh_guc_hg_experimental_enable_nullable_segment_key=true);
```
2. 若報錯的欄位為Segment Key，可以對錶執行以下GUC，以修改表屬性，允許Segment Key 為空白。同時，從 Hologres V4.1 版本起，將允許 Segment Key 為空白，建議進行版本升級。
```
--3.1及以上版本文法，clustering key為nullable
ALTER DYNAMIC TABLE [ IF EXISTS ] [<schema>.]<table_name> SET
(refresh_guc_hg_experimental_enable_nullable_clustering_key=true);
```