[GitHub] [carbondata] xubo245 commented on a change in pull request #3275: [CARBONDATA-3425]Added documentation for mv

[GitBox]

xubo245 commented on a change in pull request #3275: [CARBONDATA-3425]Added documentation for mv
URL: https://github.com/apache/carbondata/pull/3275#discussion_r294376086
 
 

 ##########
 File path: docs/datamap/mv-datamap-guide.md
 ##########
 @@ -0,0 +1,208 @@
+<!--
+    Licensed to the Apache Software Foundation (ASF) under one or more
+    contributor license agreements.  See the NOTICE file distributed with
+    this work for additional information regarding copyright ownership.
+    The ASF licenses this file to you under the Apache License, Version 2.0
+    (the "License"); you may not use this file except in compliance with
+    the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+    Unless required by applicable law or agreed to in writing, software
+    distributed under the License is distributed on an "AS IS" BASIS,
+    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+    See the License for the specific language governing permissions and
+    limitations under the License.
+-->
+
+# CarbonData MV DataMap
+
+* [Quick Example](#quick-example)
+* [MV DataMap](#mv-datamap-introduction)
+* [Loading Data](#loading-data)
+* [Querying Data](#querying-data)
+* [Compaction](#compacting-mv-tables)
+* [Data Management](#data-management-with-mv-tables)
+
+## Quick example
+
+Start spark-sql in terminal and run the following queries,
+```
+CREATE TABLE maintable(a int, b string, c int) stored by 'carbondata';
+insert into maintable select 1, 'ab', 2;
+CREATE DATAMAP datamap_1 on table maintable as SELECT a, sum(b) from maintable group by a;
+SELECT a, sum(b) from maintable group by a;
+// NOTE: run explain query and check if query hits the datamap table from the plan
+EXPLAIN SELECT a, sum(b) from maintable group by a;
+```
+
+## MV DataMap Introduction
+  MV tables are created as DataMaps and managed as tables internally by CarbonData. User can create
+  limitless MV datamaps on a table to improve query performance provided the storage requirements
+  and loading time is acceptable.
+
+  MV datamap can be a lazy or a non-lazy datamap. Once MV datamaps are created, CarbonData's
+  CarbonAnalyzer helps to select the most efficient MV datamap based on the user query and rewrite
+  the SQL to select the data from MV datamap instead of main table. Since the data size of MV
+  datamap is smaller and data is pre-processed, user queries are much faster.
+
+  For instance, main table called **sales** which is defined as
+
+  ```
+  CREATE TABLE sales (
+    order_time timestamp,
+    user_id string,
+    sex string,
+    country string,
+    quantity int,
+    price bigint)
+  STORED AS carbondata
+  ```
+
+  User can create MV tables using the Create DataMap DDL
+
+  ```
+  CREATE DATAMAP agg_sales
+  ON TABLE sales
+  USING "MV"
+  AS
+    SELECT country, sex, sum(quantity), avg(price)
+    FROM sales
+    GROUP BY country, sex
+  ```
+ **NOTE**:
+ * Group by/Filter columns has to be provided in projection list while creating mv datamap
+ * If only single parent table is involved in mv datamap creation, then TableProperties of Parent table
+   (if not present in a aggregate function like sum(col)) listed below will be
+   inherited to datamap table
+    1. SORT_COLUMNS
+    2. SORT_SCOPE
+    3. TABLE_BLOCKSIZE
+    4. FLAT_FOLDER
+    5. LONG_STRING_COLUMNS
+    6. LOCAL_DICTIONARY_ENABLE
+    7. LOCAL_DICTIONARY_THRESHOLD
+    8. LOCAL_DICTIONARY_EXCLUDE
+    9. DICTIONARY_INCLUDE
+   10. DICTIONARY_EXCLUDE
+   11. INVERTED_INDEX
+   12. NO_INVERTED_INDEX
+   13. COLUMN_COMPRESSOR
+
+ * All columns of main table at once cannot participate in mv datamap table creation
+ * TableProperties can be provided in DMProperties excluding LOCAL_DICTIONARY_INCLUDE,
+   LOCAL_DICTIONARY_EXCLUDE, DICTIONARY_INCLUDE, DICTIONARY_EXCLUDE, INVERTED_INDEX,
+   NO_INVERTED_INDEX, SORT_COLUMNS, LONG_STRING_COLUMNS, RANGE_COLUMN & COLUMN_META_CACHE
+ * TableProperty given in DMProperties will be considered for mv creation, eventhough if same
+   property is inherited from parent table, which allows user to provide different tableproperties
+   for child table
+ * MV creation with limit or union all ctas queries is unsupported
+
+#### How MV tables are selected
+
+When a user query is submitted, during query planning phase, CarbonData will collect modular plan
+candidates and process the the ModularPlan based on registered summary data sets. Then,
+mv datamap table for this query will be selected among the candidates.
+
+For the main table **sales** and mv table  **agg_sales** created above, following queries
+```
+SELECT country, sex, sum(quantity), avg(price) from sales GROUP BY country, sex
+
+SELECT sex, sum(quantity) from sales GROUP BY sex
+
+SELECT avg(price), country from sales GROUP BY country
+```
+
+will be transformed by CarbonData's query planner to query against mv table
+**agg_sales** instead of the main table **sales**
+
+However, for following queries
+```
+SELECT user_id, country, sex, sum(quantity), avg(price) from sales GROUP BY user_id, country, sex
+
+SELECT sex, avg(quantity) from sales GROUP BY sex
+
+SELECT country, max(price) from sales GROUP BY country
+```
+
+will query against main table **sales** only, because it does not satisfy mv table
 
 Review comment:
   Please optimize it.

----------------------------------------------------------------
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:
[hidden email]


With regards,
Apache Git Services