[GitHub] [incubator-doris] EmmyMiao87 edited a comment on issue #2101: 支持物化视图

[GitBox]

EmmyMiao87 edited a comment on issue #2101: 支持物化视图
URL: 
https://github.com/apache/incubator-doris/issues/2101#issuecomment-561964857
 
 
   ## 创建 Materialized View
   
   + step1: 用户创建 MV
   + step2: 检查声明的 MV 语句是否符合要求
        + 查询语句，语义解析正确 
        + Select clause 语句中列不能带别名 
        + From clause **只能涉及 Base 表名，不能是 MV 表名**，且只能单张表。
        + Where clause 禁止声明
        + Group by 中除支持的几种聚合函数外，其余函数均不支持，也可以不声明聚合信息（对于duplicate 数据模型）
        + Order by 所有声明在排序中的列将作为 MV 表的 key 列
   + step3: 通过 MVSelector 选择合适的 Base 表。*注意：这里的 Base 表可能是某个 MV 表*
   + step4: 创建 MVJob 元信息包括：MaterializedIndex, Tablet, Replica。提交 MV Job
   
   ```
   **step1**
   CREATE Materialized View [MV name] AS
     SELECT select_expr[, select_expr ...]
     FROM [Base table name]
     GROUP BY column_name[, column_name ...]
     ORDER BY column_name[, column_name ...]
     
   例子：原始表主要存储的是每个查询语句在不同阶段的耗时，是一个 duplicate 模型的明细表。
   包含 key 列（query_id, backend_id）和 value 列（type, time） 
   
   query 1： 查询 query_id 为1的查询语句的总耗时，直接匹配 base 表即可
   select query_id, sum(time) from base_table where query_id=1 group by 
query_id;
   
   query 2: 查询 backend_id 为1的聚合类查询耗时的75分位数为多少。
   select backend_id, PERCENTILE_APPROX(time, 0.75) from base_table where 
backend_id=1 and type='GROUP_BY' group by backend_id;
   
   为 query 2 创建新的 MV 表，使用(backend_id, type)作为 key 列，加快查询速度。
   create materialized view backend_type_table as 
       select backend_id, type, query_id, time 
       from base_table 
       order by backend_id, type
   
   ```
   
   ```
   Class AddMaterializedViewClause {
       String mvName;
       SelectStmt mvQuery;
       
       void analyze() {
           **step2**
           mvQuery.analyze();
       }
   }
   
   Class MVHandler {
       void processAddRollup(AddMaterializedViewClause addMVClause) {
           if (olapTable.getKeysType() ==  KeysType.DUP_KEYS) {
               SelectStmt mvQuery = addMVClause.getMVQuery();
               **step2**
               checkMVQuery(mvQuery);
               **step3**
               rollupSelector.selectBestMV(mvQuery.getAggInfo(), 
mvQuery.getFromClause.get(0));
               **step4**
               createMetaOfMV();
               addAlterJobV2(mvJob);
           }
       }
       
       void checkMVQuery(SelectStmt mvQuery) throw DdlException() {
           1. check select clause
           2. check from clause
           3. check where clause
           4. check aggregate info
           5. check order by clause
       }
   }
   
   Class MVSelector {
       long selectBestMV(AggregateInfo aggInfo, OlapTable baseTable) {
           下部分详细说明
       }
   }
   
   ```

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org