Apache CarbonData Dev Mailing List archive › Apache CarbonData JIRA issues

[GitHub] [carbondata] ravipesala commented on a change in pull request #3275: [WIP]Added documentation for mv

Classic

List

Threaded

1 message

GitBox

[GitHub] [carbondata] ravipesala commented on a change in pull request #3275: [WIP]Added documentation for mv

ravipesala commented on a change in pull request #3275: [WIP]Added documentation for mv
URL: https://github.com/apache/carbondata/pull/3275#discussion_r293701912

##########
File path: docs/datamap/mv-datamap-guide.md
##########
@@ -0,0 +1,263 @@
+
+
+# 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
+Download and unzip spark-2.2.0-bin-hadoop2.7.tgz, and export $SPARK_HOME
+
+Package carbon jar, and copy assembly/target/scala-2.11/carbondata_2.11-x.x.x-SNAPSHOT-shade-hadoop2.7.2.jar to $SPARK_HOME/jars
+```shell
+mvn clean package -DskipTests -Pspark-2.2 -Pmv
+```
+
+Start spark-shell in new terminal, type :paste, then copy and run the following code.
+```scala
+ import java.io.File
+ import org.apache.spark.sql.CarbonSession._
+ import org.apache.spark.sql.SparkSession
+
+ val warehouse = new File("./warehouse").getCanonicalPath
+ val metastore = new File("./metastore").getCanonicalPath
+
+ val spark = SparkSession
+ .builder()
+ .master("local")
+ .appName("MVDatamapExample")
+ .config("spark.sql.warehouse.dir", warehouse)
+ .getOrCreateCarbonSession(warehouse, metastore)
+
+ spark.sparkContext.setLogLevel("ERROR")
+
+ // drop table if exists previously
+ spark.sql(s"DROP TABLE IF EXISTS sales")
+
+ // Create main table
+ spark.sql(
+ s"""
+ | CREATE TABLE sales (
+ | user_id string,
+ | country string,
+ | quantity int,
+ | price bigint)
+ | STORED AS carbondata
+ """.stripMargin)
+
+ // Create mv datamap table on the main table
+ // If main table already have data, following command
+ // will trigger one immediate load to the mv table
+ spark.sql(
+ s"""
+ | CREATE DATAMAP agg_sales
+ | ON TABLE sales
+ | USING "mv"
+ | AS
+ | SELECT country, sum(quantity), avg(price)
+ | FROM sales
+ | GROUP BY country
+ """.stripMargin)
+
+ import spark.implicits._
+ import org.apache.spark.sql.SaveMode
+ import scala.util.Random
+
+ // Load data to the main table, it will also
+ // trigger immediate load to mv table in case of non-lazy datamap.
+ val r = new Random()
+ spark.sparkContext.parallelize(1 to 10)
+ .map(x => ("ID." + r.nextInt(100000), "country" + x % 8, x % 50, x % 60))
+ .toDF("user_id", "country", "quantity", "price")
+ .write
+ .format("carbondata")
+ .option("tableName", "sales")
+ .option("compress", "true")
+ .mode(SaveMode.Append)
+ .save()
+
+ spark.sql(
+ s"""
+ |SELECT country, sum(quantity), avg(price)
+ | from sales GROUP BY country
+ """.stripMargin).show
+
+ spark.stop
+```
+
+## MV DataMap Introduction
+ Pre-aggregate datamap supports only aggregation on single table where as MV datamap is implemented
+ to support projection, projection with filter, aggregation and join capabilities also. MV tables are
+ created as DataMaps and managed as tables internally by CarbonData. User can create as many MV
+ datamaps required to improve query performance, provided the storage requirements and loading
+ speeds are 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, 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 one parent table is involved in mv datamap creation, then TableProperties of Parent table
+ (if not present in a aggregate function like sum(col)) like SORT_COLUMNS, SORT_SCOPE, TABLE_BLOCKSIZE,
+ FLAT_FOLDER, LONG_STRING_COLUMNS, LOCAL_DICTIONARY_ENABLE, LOCAL_DICTIONARY_THRESHOLD,
+ LOCAL_DICTIONARY_INCLUDE, LOCAL_DICTIONARY_EXCLUDE, DICTIONARY_INCLUDE, DICTIONARY_EXCLUDE,
+ INVERTED_INDEX, NO_INVERTED_INDEX, COLUMN_COMPRESSOR will be inherited to datamap table
+ * 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(**NOTE**:
+ TableProperty given in DMProperties will be considered for mv creation, eventhough if same
+ property is inherited from parent table)
+
+#### 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
+selection logic.
+
+## Loading data
+
+### Loading data to Non-Lazy MV Datamap
+
+In case of WITHOUT DEFERRED REBUILD, for existing table with loaded data, data load to MV table will
+be triggered by the CREATE DATAMAP statement when user creates the MV table.
+For incremental loads to main table, data to datamap will be loaded once the corresponding main
+table load is completed.
+
+### Loading data to Lazy MV Datamap
+
+In case of WITH DEFERRED REBUILD, data load to MV table will be triggered by the [Manual Refresh](./datamap-management.md#manual-refresh)
+command. MV datamap will be in DISABLED state in below scenarios,
+ * when mv datamap is created
+ * when data of main table and datamap are not in sync
+
+User should fire REBUILD DATAMAP command to sync all segments of main table with datamap table and
+which ENABLES the datamap for query
+
+### Loading data to Multiple MV's
+During load to main table, if anyone of the load to datamap table fails, then that corresponding
+datamap will be DISABLED and load to other datamaps mapped to main table will continue. User
+can fire REBUILD DATAMAP command to sync and enable the disabled datamap.

Review comment:
User can fire REBUILD DATAMAP command to sync or else the subsequent table load will load the old failed loads along with current load and enable the disabled datamap.

----------------------------------------------------------------
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