使用动态表自动刷新数据和加速查询

动态表 (Dynamic Table) 是一种类似于物化视图的特殊数据库对象，具有自动刷新和查询加速的特性。Apache Cloudberry 引入动态表功能，旨在提供更高效、更灵活的查询处理和数据更新。

动态表支持由基础表、外部表、物化视图等构建，且具有自动更新的功能。用户可以通过定义调度任务来自动刷新动态表数据，确保数据始终保持最新。

使用场景

动态表主要适用于以下几种场景：

为湖仓架构加速查询：对于使用湖仓架构的客户，动态表可以将外部表查询自动替换成在动态表上的查询，提升查询性能。
自动数据刷新：在需要定期更新数据的场景下，动态表可通过设定自动刷新任务来避免人工干预。
实时数据分析：适合需要频繁查询最新数据且对数据刷新要求较高的应用场景，如金融分析、运营监控等。

与物化视图的对比

特性	动态表	AQUMV
用途	自动刷新的特殊表，处理数据流水线，简化 ETL	利用物化视图提高查询效率，自动改写查询
组成与结构	可由普通表、外部表、物化视图等组成	基于物化视图，通常针对单个表
查询改写能力	不支持	支持单表改写
数据刷新机制	可通过 SQL 定义自动刷新周期	需要手动刷新物化视图数据

使用方法

创建动态表

您可以使用 CREATE DYNAMIC TABLE 语句创建动态表。

CREATE DYNAMIC TABLE table_name AS <select_query> [WITH NO DATA] [SCHEDULE '<cron_string>'] [DISTRIBUTED BY <distribution_key>];

参数说明：

<select_query>：用于生成动态表的查询，支持与基础表、物化视图或其他动态表的连接。
WITH NO DATA：如果指定此选项，建表时不会填充数据，只会创建空的动态表。
SCHEDULE '<cron_string>'：指定动态表的刷新周期，使用标准的 Linux cron 表达式，具体表达式的写法请参考 Cron 表达式。
DISTRIBUTED BY：指定动态表的分布键，以优化查询性能。

完整的语法如下：

CREATE DYNAMIC TABLE [ IF NOT EXISTS ] table_name
   [ (column_name [, ...] ) ]
   [ USING method ]
   [ WITH ( storage_parameter [= value] [, ... ] ) ]
   [ TABLESPACE tablespace_name ]
   [SCHEDULE schedule_string]
   AS query
   [ WITH [ NO ] DATA ]
   [DISTRIBUTED BY (keys [, ...])]

使用动态表的示例：

CREATE DYNAMIC TABLE dt0 SCHEDULE '5 * * * *' AS SELECT a, b, sum(c) FROM t1 GROUP BY a, b WITH NO DATA DISTRIBUTED BY(b);

\d dt0
               List of relations
Schema | Name |     Type      |  Owner  | Storage
--------+------+---------------+---------+---------
public | dt0  | dynamic table | gpadmin | heap

以上语句创建一个动态表，并设置每 5 分钟自动刷新一次。

刷新动态表

动态表会根据 SCHEDULE 设置自动刷新。如果您需要手动刷新，可以使用 REFRESH DYNAMIC TABLE 命令，使用时将 table_name 替换为实际的表名：

REFRESH DYNAMIC TABLE table_name;

若需要将表清空并置为不可读取的状态，可以使用：

REFRESH DYNAMIC TABLE table_name WITH NO DATA;

查看调度信息

使用内置函数 pg_get_dynamic_table_schedule() 查看动态表的刷新调度信息。执行时，将 table_name 替换为实际的表名：

SELECT pg_get_dynamic_table_schedule('table_name'::regclass::oid);

删除动态表

使用 DROP DYNAMIC TABLE 删除动态表，删除时会同时删除相关的自动刷新任务。执行时，将 table_name 替换为实际的表名：

DROP DYNAMIC TABLE table_name;

查看分布键

动态表支持在建表时指定分布键。您可以使用 \d+ 命令查看动态表的分布键和查询 SQL 定义。

\d+ dt0

示例输出：

Dynamic table "public.dt0"
Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a      | integer |           |          |         | plain   |             |              |
b      | integer |           |          |         | plain   |             |              |
sum    | bigint  |           |          |         | plain   |             |              |
View definition:
SELECT t1.a,
   t1.b,
   sum(t1.c) AS sum
   FROM t1
   GROUP BY t1.a, t1.b;
Distributed by: (b)
Access method: heap

在这个例子中，动态表 dt0 使用列 b 作为分布键，通过此方式来优化查询性能。查询和聚合操作可以更高效地执行，因为数据根据分布键 b 被分配到不同的节点。

使用示例

示例 1：加速湖仓架构的外部表查询

通过使用动态表，在处理数据湖或外部存储的数据时，Apache Cloudberry 将对外部表的查询自动替换为在动态表上的查询，从而加速查询过程。

创建一个可读取的外部表 ext_r，数据来源于指定的文件 dynamic_table_text_file.txt。
```
CREATE READABLE EXTERNAL TABLE ext_r(id int)
   LOCATION('demoprot://dynamic_table_text_file.txt')
FORMAT 'text';
```
以上创建外部表的语句中，dynamic_table_text_file.txt 是一个示例文件路径，而 demoport 是一个示例协议（可以为 gpfdist 或其他协议）。

使用 EXPLAIN 查看表的查询执行计划，查询条件是 id > 5。

EXPLAIN(COSTS OFF, VERBOSE)
SELECT sum(id) FROM ext_r WHERE id > 5;

以下查询计划出现了 Foreign Scan on dynamic_table_schema.ext_r，由此可知执行器直接扫描了外部表 ext_r。

QUERY PLAN
--------------------------------------------------------------
Finalize Aggregate
Output: sum(id)
->  Gather Motion 3:1  (slice1; segments: 3)
      Output: (PARTIAL sum(id))
      ->  Partial Aggregate
            Output: PARTIAL sum(id)
            ->  Foreign Scan on dynamic_table_schema.ext_r
                  Output: id
                  Filter: (ext_r.id > 5)

创建动态表 dt_external，将外部表 ext_r 中的数据筛选后存入动态表 dt_external，并执行 ANALYZE 来更新表的统计信息。
```
CREATE DYNAMIC TABLE dt_external AS
SELECT * FROM ext_r WHERE id > 5;
ANALYZE dt_external;
```

在事务中设置相关参数，开启动态表查询加速功能。

BEGIN;
SET optimizer = OFF;
SET LOCAL enable_answer_query_using_materialized_views = ON;
SET LOCAL aqumv_allow_foreign_table = ON;

再次使用 EXPLAIN 查看表的查询执行计划，查询条件是 id > 5。

EXPLAIN(COSTS OFF, VERBOSE)
SELECT sum(id) FROM ext_r WHERE id > 5;
COMMIT;

以下查询计划中出现了 Seq Scan on dynamic_table_schema.dt_external，由此可知，执行器使用了动态表自动替换外部表查询，对 ext_r 外部表的查询替换成对 dt_external 动态表的查询。

QUERY PLAN
---------------------------------------------------------------
Finalize Aggregate
Output: sum(id)
->  Gather Motion 3:1  (slice1; segments: 3)
      Output: (PARTIAL sum(id))
      ->  Partial Aggregate
            Output: PARTIAL sum(id)
            ->  Seq Scan on dynamic_table_schema.dt_external
                  Output: id
Settings: enable_answer_query_using_materialized_views = 'on',
optimizer = 'off'
Optimizer: Postgres query optimizer
(10 rows)

通过这个流程，查询效率得到了提高，因为原本需要扫描外部表的操作被动态表的扫描替代，减少了查询的响应时间。

示例 2：创建空的动态表

如果在创建时不希望自动填充数据，可以使用 WITH NO DATA 选项。示例如下：

创建基表 existing_table 并插入数据：

CREATE TABLE existing_table (
   id INT,
   name VARCHAR(100),
   value INT
);

INSERT INTO existing_table (id, name, value) VALUES
(1, 'Alice', 100),
(2, 'Bob', 150),
(3, 'Charlie', 200);

创建空的动态表：
```
CREATE DYNAMIC TABLE empty_table AS 
SELECT * FROM existing_table 
WITH NO DATA;
```
该语句创建了一个空的动态表，之后可以通过手动刷新来填充数据。

验证该表为空：

SELECT * FROM empty_table;

-- ERROR:  materialized view "empty_table" has not been populated
-- HINT:  Use the REFRESH MATERIALIZED VIEW command.

注意事项

刷新策略：确保设置适当的刷新间隔。如果刷新间隔过于频繁，可能会增加系统负载；如果刷新间隔过于稀疏，查询数据可能不够及时。
查询替换：动态表的查询性能优势在于自动替换外部表查询，因此，适合用于经常需要外部表数据的查询场景。
数据一致性：由于动态表是物化视图的一种，因此要注意刷新策略和数据一致性的平衡，确保业务逻辑的准确性。

使用动态表自动刷新数据和加速查询

使用场景​

与物化视图的对比​

使用方法​

创建动态表​

刷新动态表​

查看调度信息​

删除动态表​

查看分布键​

使用示例​

示例 1：加速湖仓架构的外部表查询​

示例 2：创建空的动态表​

注意事项​