配置StarRocks异步物化视图查询

前提条件

• 创建弹性云服务器，请参考购买ECS章节。
• 已创建StarRocks集群，集群运行正常。
• 已安装MySQL客户端。

使用限制

• 创建异步物化视图时必须指定分桶或刷新策略。
• 可以为异步物化视图设置与其基表不同的分区和分桶策略，但异步物化视图的分区列和分桶列必须在查询语句中。

创建异步物化视图

1. 连接StarRocks集群。具体方式请参见手动安装MySQL客户端。

   ./mysql -uadmin -ppassword -h集群内网地址 -P9030

2. 在Default Catalog中创建两张基表，并插入数据。
   • 创建数据库。

     create db_name;

   • 使用数据库。

     use db_name;

   • 创建表goods并插入数据：

     CREATE TABLE goods(
     item_id1 INT comment "产品 ID",
     item_name STRING comment "产品名称",
     price FLOAT comment "产品价格"
     ) DISTRIBUTED BY HASH(item_id1);

     INSERT INTO goods VALUES (1001,"apple",6.5),(1002,"pear",8.0),(1003,"potato",2.2);

   • 创建表order_list并插入数据。

     CREATE TABLE order_list(
     order_id INT comment "订单 ID",
     client_id INT comment "客户 ID",
     item_id2 INT comment "产品 ID",
     order_date DATE
     ) DISTRIBUTED BY HASH(order_id);

     INSERT INTO order_list VALUES
     (10001,101,1001,"2022-03-13"),
     (10001,101,1002,"2022-03-13"),
     (10002,103,1002,"2022-03-13"),
     (10002,103,1003,"2022-03-14"),
     (10003,102,1003,"2022-03-14"),
     (10003,102,1001,"2022-03-14");

3. 查询数据。

   SELECT
   order_id, sum(goods.price) as total
   FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
   GROUP BY order_id;

   

4. 创建异步物化视图。根据查询语句，于表goods和表order_list创建一个“以订单ID为分组，对订单中所有商品价格求和”的异步物化视图，并设定其刷新方式为ASYNC，每天自动刷新。

   CREATE MATERIALIZED VIEW order_mv
   DISTRIBUTED BY HASH(`order_id`)
   REFRESH ASYNC START('2022-09-01 10:00:00') EVERY (interval 1 day)
   AS SELECT
   order_list.order_id,
   sum(goods.price) as total
   FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
   GROUP BY order_id;

5. 查询异步物化视图是否创建成功。

   SELECT * FROM order_mv;

   

异步物化视图常用命令

• 手动刷新异步物化视图。创建异步物化视图后，视图中的数据仅反映创建时刻基表的状态；当基表中的数据发生变化时，需要通过刷新异步物化视图更新数据变化。
  • 异步调用刷新任务。

    REFRESH MATERIALIZED VIEW order_mv;

  • 同步调用刷新任务。

    REFRESH MATERIALIZED VIEW order_mv WITH SYNC MODE;

• 查看异步物化视图。
  • 查看当前数据仓库内的所有异步物化视图。

    SHOW MATERIALIZED VIEWS\G;

  • 查看指定的异步物化视图

    SHOW MATERIALIZED VIEWS WHERE NAME = "order_mv"\G;

    

  • 通过名称匹配查看异步物化视图。

    SHOW MATERIALIZED VIEWS WHERE NAME LIKE "order%"\G;

  • 通过Information Schema中的系统元数据表“materialized_views”查看所有异步物化视图。

    SELECT * FROM information_schema.materialized_views\G;

• 查看异步物化视图的创建语句。

  SHOW CREATE MATERIALIZED VIEW order_mv;

  

• 修改异步物化视图名称。

  ALTER MATERIALIZED VIEW db.order_mv RENAME order_total;

• 删除异步物化视图。

  DROP MATERIALIZED VIEW order_mv;