创建分区物化视图

等比例对齐分区

您可以通过使用相同的分区键创建一个分区与基表分区一一对应的物化视图。

针对分区键的类型创建物化视图：

• 如果基表分区键是DATE或DATETIME类型，可以直接为物化视图指定相同的分区键。

  • 语法

    PARTITION BY <base_table_partitioning_column>

  • 示例

    CREATE MATERIALIZED VIEW par_mv1
    REFRESH ASYNC
    PARTITION BY datekey
    AS 
    SELECT 
      k1, 
      sum(v1) AS SUM, 
      datekey 
    FROM par_tbl1 
    GROUP BY datekey, k1;

• 如果基表的分区键是STRING类型，可以使用str2date函数将日期字符串转换为DATE或DATETIME类型。

  • 语法

    PARTITION BY str2date(<base_table_partitioning_column>, <format>)

  • 示例

    CREATE MATERIALIZED VIEW par_mv2
    REFRESH ASYNC
    PARTITION BY str2date(datekey, '%Y-%m-%d')
    AS 
    SELECT 
      k1, 
      sum(v1) AS SUM, 
      datekey 
    FROM par_tbl2 
    GROUP BY datekey, k1;

时间粒度上卷对齐分区

您可以通过在分区键上使用date_trunc函数，创建一个分区时间粒度比基表更粗的物化视图。当检测到基表分区中的数据变更后，StarRocks将会刷新物化视图中对应的上卷分区。

• 如果基表的分区键是DATE或DATETIME类型，可以直接在基表的分区键上使用date_trunc函数。

  • 语法

    PARTITION BY date_trunc(<format>, <base_table_partitioning_column>)

  • 示例

    CREATE MATERIALIZED VIEW par_mv3
    REFRESH ASYNC
    PARTITION BY date_trunc('month', datekey)
    AS 
    SELECT 
      k1, 
      sum(v1) AS SUM, 
      datekey 
    FROM par_tbl1 
    GROUP BY datekey, k1;

• 如果基表的分区键是STRING类型，则必须在SELECT List中将基表的分区键转换为DATE或DATETIME类型，并为其设置别名，然后使用date_trunc函数并将结果指定为物化视图的分区键。

  • 语法

    PARTITION BY 
    date_trunc(<format>, <mv_partitioning_column>)
    AS
    SELECT 
      str2date(<base_table_partitioning_column>, <format>) AS <mv_partitioning_column>

  • 示例

    CREATE MATERIALIZED VIEW par_mv4
    REFRESH ASYNC
    PARTITION BY date_trunc('month', mv_datekey)
    AS 
    SELECT 
      datekey,
      k1, 
      sum(v1) AS SUM, 
      str2date(datekey, '%Y-%m-%d') AS mv_datekey
    FROM par_tbl2 
    GROUP BY datekey, k1;

自定义时间粒度对齐分区

如果您的业务场景需要使用自定义的时间粒度进行分区，您可以创建一个物化视图，并使用time_slice或date_slice函数定义其分区的时间粒度。以上两种函数可以根据指定的时间粒度周期，将给定的时间转化到其所在的时间粒度周期的起始或结束时刻。

您需要在SELECT List中使用time_slice或date_slice函数在基表的分区键上定义新的时间粒度，为其设置别名，然后结合date_trunc函数指定物化视图的分区键，从而创建一个自定义分区时间粒度的物化视图。

• 语法

  PARTITION BY
  date_trunc(<format>, <mv_partitioning_column>)
  AS
  SELECT 
    -- 您可以使用time_slice或date_slice函数。
    date_slice(<base_table_partitioning_column>, <interval>) AS <mv_partitioning_column>

• 示例

  CREATE MATERIALIZED VIEW par_mv5
  REFRESH ASYNC
  PARTITION BY date_trunc('day', mv_datekey)
  AS 
  SELECT 
    k1, 
    sum(v1) AS SUM, 
    date_slice(datekey, INTERVAL 5 HOUR) AS mv_datekey 
  FROM par_tbl1 
  GROUP BY datekey, k1;

多基表对齐分区

如果多张基表的分区可以互相对齐，即基表使用相同类型的分区键，您就可以创建基于这些表的物化视图。您可以使用JOIN连接基表，并将分区键设置为公共列。您也可以使用UNION连接基表。具有对齐分区的基表称为Reference Table。任意Reference Table中的数据变更都将触发对应物化视图分区的刷新任务。

-- 使用JOIN连接基表。
CREATE MATERIALIZED VIEW par_mv6
REFRESH ASYNC
PARTITION BY datekey
AS SELECT 
  par_tbl1.datekey,
  par_tbl1.k1 AS t1k1,
  par_tbl3.k1 AS t2k1, 
  sum(par_tbl1.v1) AS SUM1, 
  sum(par_tbl3.v1) AS SUM2
FROM par_tbl1 JOIN par_tbl3 ON par_tbl1.datekey = par_tbl3.datekey_new
GROUP BY par_tbl1.datekey, t1k1, t2k1;

-- 使用UNION连接基表。
CREATE MATERIALIZED VIEW par_mv7
REFRESH ASYNC
PARTITION BY datekey
AS SELECT 
  par_tbl1.datekey,
  par_tbl1.k1 AS t1k1,
  sum(par_tbl1.v1) AS SUM1
FROM par_tbl1
GROUP BY 
  par_tbl1.datekey,
  par_tbl1.k1
UNION ALL
SELECT
  par_tbl3.datekey_new,
  par_tbl3.k1 AS t2k1, 
  sum(par_tbl3.v1) AS SUM2
FROM par_tbl3
GROUP BY 
  par_tbl3.datekey_new,
  par_tbl3.k1;

实现增量刷新和透明改写

您可以创建一个分区物化视图，通过分区刷新来实现对物化视图的增量更新，并通过局部数据物化实现查询的透明重写。

在创建物化视图时，必须考虑以下几个方面：

• 刷新粒度

  您可以通过 partition_refresh_number 属性来指定每次刷新操作的粒度。该属性控制单次刷新中最多刷新的分区数量。若待刷新的分区数量超过该值，StarRocks会自动将刷新任务拆分并分批完成，分区刷新顺序为时间由远至近（不包括未来分区）。partition_refresh_number的默认值为-1，表示不拆分刷新任务。

• 物化范围

  物化数据的保留范围由 partition_ttl_number（在v3.1.5之前的版本）或 partition_ttl（推荐用 v3.1.5及更高版本）属性控制。partition_ttl_number指定要保留的最新分区的数量，partition_ttl设定物化视图数据的时间范围。在每次刷新中，StarRocks按时间顺序排列分区，仅保留符合TTL要求的分区。

• 刷新策略

  • 自动刷新（REFRESH ASYNC）：当基表数据发生变化时，物化视图将自动刷新。

  • 定时刷新（REFRESH ASYNC [START (<start_time>)] EVERY (INTERVAL <interval>)）：根据设定间隔定期刷新物化视图。

    说明

    自动刷新和定时刷新会在触发刷新任务后自动执行。StarRocks会记录和比较基表中每个分区的数据版本，数据版本的变化表明相应数据已更新。一旦检测到基表分区数据变更，StarRocks将刷新对应物化视图分区；若无数据变化，则跳过该分区的刷新。

  • 手动刷新（REFRESH MANUAL）：该模式下，您需要通过手动执行REFRESH MATERIALIZED VIEW语句进行刷新。您可以指定刷新的时间范围，以避免刷新整个物化视图。若语句中指定了FORCE，StarRocks 将强制刷新相应视图或分区，不论基表中数据是否变更。通过在语句中添加WITH SYNC MODE，可使刷新任务同步执行，只返回任务成功或失败的结果。

以下示例展示了如何创建一个名为 par_mv8 的分区物化视图。该视图设计为当基础表的分区发生数据更新时自动刷新对应分区。刷新操作以分批方式进行，每次仅针对一个分区（设定 "partition_refresh_number" = "1"），并且为了控制存储开销，物化视图仅维护最近两个分区（通过设置 "partition_ttl_number" = "2" 实现），多余的旧分区会在刷新周期中被自动删除。

CREATE MATERIALIZED VIEW par_mv8
REFRESH ASYNC
PARTITION BY datekey
PROPERTIES (
  "partition_ttl_number" = "2",
  "partition_refresh_number" = "1"
) AS
SELECT k1, SUM(v1) AS total_v1, datekey
FROM par_tbl1
GROUP BY datekey, k1;

您可以使用 REFRESH MATERIALIZED VIEW 语句来刷新该物化视图。以下示例通过同步调用的方式强制刷新par_mv8中指定时间范围内的分区。

REFRESH MATERIALIZED VIEW par_mv8
PARTITION START ('2021-01-03') END ('2021-01-04')
FORCE WITH SYNC MODE;

执行后，您可以在控制台的EMR StarRocks Manager的异步物化视图页面，查看分区的刷新情况。

TTL（Time-To-Live）机制使得 par_mv8 能够自动管理其分区生命周期，这对于那些关注最新数据查询的场景极为关键。它不仅加速了针对近期数据的查询（例如，最近一周或一月的数据），还有效减少了存储占用。当查询请求超出物化视图现存分区范围时，系统会智能地将查询重定向至基础表，确保数据完整性。

在以下示例中，Query 1由于命中了par_mv8中保留的分区，因此能够通过物化视图实现加速；而Query 2则因不在分区保留的时间范围内，将被退回至基表进行执行。

-- Query 1
SELECT 
  k1, 
  sum(v1) AS SUM, 
  datekey 
FROM par_tbl1
WHERE datekey='2021-01-04'
GROUP BY datekey, k1;

-- Query 2
SELECT 
  k1, 
  sum(v1) AS SUM, 
  datekey 
FROM par_tbl1
WHERE datekey='2021-01-01'
GROUP BY datekey, k1;

下图演示了物化视图在数据库查询性能中的作用，通过缓存部分结果来提高响应速度。