[GitHub] [carbondata] niuge01 opened a new pull request #3720: [CARBONDATA-3775] Update materialized view document
Locked
 
|
niuge01 opened a new pull request #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720 ### Why is this PR needed? Update materialized view document synchronously, after the materialized view is separated from the DataMap module. ### What changes were proposed in this PR? 1. Update materialized view syntax comment. 2. Add comment about usage of time series. 3. Move document to document root directory from index directory. ### Does this PR introduce any user interface change? - No ### Is any new testcase added? - No ---------------------------------------------------------------- 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 |
|
|
jackylk commented on a change in pull request #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#discussion_r410555362 ########## File path: docs/mv-guide.md ########## @@ -0,0 +1,342 @@ +<!-- Review comment: can you use rename without adding another doc, so that it is easier to find out what is changed in this doc ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
jackylk commented on a change in pull request #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#discussion_r410555362 ########## File path: docs/mv-guide.md ########## @@ -0,0 +1,342 @@ +<!-- Review comment: can you use rename file instead of adding another file, so that it is easier to find out what is changed in this doc ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
CarbonDataQA1 commented on issue #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#issuecomment-615554201 Build Success with Spark 2.3.4, Please check CI http://121.244.95.60:12545/job/ApacheCarbonPRBuilder2.3/2772/ ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
CarbonDataQA1 commented on issue #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#issuecomment-615556319 Build Success with Spark 2.4.5, Please check CI http://121.244.95.60:12545/job/ApacheCarbon_PR_Builder_2.4.5/1059/ ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
niuge01 commented on a change in pull request #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#discussion_r410667266 ########## File path: docs/mv-guide.md ########## @@ -0,0 +1,342 @@ +<!-- Review comment: I move the file to doument root directory from index directory. before commit, execute "mvn status" at local , display rename & change but after commit, it changed to delete old file and add new file. ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
Indhumathi27 commented on a change in pull request #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#discussion_r410675635 ########## File path: docs/mv-guide.md ########## @@ -0,0 +1,342 @@ +<!-- + 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 Materialized View + +* [Quick Example](#quick-example) +* [Introduction](#introduction) +* [Loading Data](#loading-data) +* [Querying Data](#querying-data) +* [Compaction](#compacting) +* [Data Management](#data-management) +* [Time Series Support](#time-series-support) +* [Time Series RollUp Support](#time-series-rollup-support) + +## Quick example + + Start spark-sql in terminal and run the following queries, + + ``` + CREATE TABLE maintable(a int, b string, c int) stored as carbondata; + INSERT INTO maintable SELECT 1, 'ab', 2; + CREATE MATERIALIZED VIEW view1 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 Index table from the plan + EXPLAIN SELECT a, sum(b) FROM maintable GROUP BY a; + ``` + +## Introduction + + Materialized views are created as sub-queries. User can create limitless materialized view to + improve query performance provided the storage requirements and loading time is acceptable. + + Materialized view can refreshed on commit or on manual. Once materialized views are created, Review comment: ```suggestion Materialized view can be refreshed on commit or on manual. Once materialized views are created, ``` ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
Indhumathi27 commented on a change in pull request #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#discussion_r410675875 ########## File path: docs/mv-guide.md ########## @@ -0,0 +1,342 @@ +<!-- + 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 Materialized View + +* [Quick Example](#quick-example) +* [Introduction](#introduction) +* [Loading Data](#loading-data) +* [Querying Data](#querying-data) +* [Compaction](#compacting) +* [Data Management](#data-management) +* [Time Series Support](#time-series-support) +* [Time Series RollUp Support](#time-series-rollup-support) + +## Quick example + + Start spark-sql in terminal and run the following queries, + + ``` + CREATE TABLE maintable(a int, b string, c int) stored as carbondata; + INSERT INTO maintable SELECT 1, 'ab', 2; + CREATE MATERIALIZED VIEW view1 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 Index table from the plan + EXPLAIN SELECT a, sum(b) FROM maintable GROUP BY a; + ``` + +## Introduction + + Materialized views are created as sub-queries. User can create limitless materialized view to + improve query performance provided the storage requirements and loading time is acceptable. + + Materialized view can refreshed on commit or on manual. Once materialized views are created, + CarbonData's CarbonAnalyzer helps to select the most efficient materialized view based on + the user query and rewrite the SQL to select the data from materialized view instead of + related tables. Since the data size of materialized view is smaller and data is pre-processed, + user queries are much faster. + + For instance, related 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 materialized view using the CREATE MATERIALIZED VIEW statement. + + ``` + CREATE MATERIALIZED VIEW agg_sales + PROPERTIES('TABLE_BLOCKSIZE'='256 MB','LOCAL_DICTIONARY_ENABLE'='false') + AS + SELECT country, sex, sum(quantity), avg(price) + FROM sales + GROUP BY country, sex + ``` + + **NOTE**: + * Group by and Order by columns has to be provided in projection list while creating materialized view. + * If only single related table is involved in materialized view creation, then TableProperties of + related table (if not present in a aggregate function like sum(col)) listed below will be + inherited to materialized view. + 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. INVERTED_INDEX + 10. NO_INVERTED_INDEX + 11. COLUMN_COMPRESSOR + * Creating materialized view with select query containing only project of all columns of related + table is unsupported. + **Example:** + If table 'x' contains columns 'a,b,c', then creating MV Index with below queries is not supported. + 1. ```SELECT a,b,c FROM x``` + 2. ```SELECT * FROM x``` + * TableProperties can be provided in Properties excluding LOCAL_DICTIONARY_INCLUDE, + LOCAL_DICTIONARY_EXCLUDE, INVERTED_INDEX, NO_INVERTED_INDEX, SORT_COLUMNS, LONG_STRING_COLUMNS, + RANGE_COLUMN & COLUMN_META_CACHE. + * TableProperty given in Properties will be considered for materialized view creation, even though + if same property is inherited from related table, which allows user to provide different table + properties for materialized view. + * Materialized view creation with limit or union all CTAS queries is unsupported. + * Materialized view does not support streaming. + +#### How materialized views 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, + materialized view for this query will be selected among the candidates. + + For the related table **sales** and materialized view **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 materialized view**agg_sales** Review comment: ```suggestion will be transformed by CarbonData's query planner to query against materialized view **agg_sales** ``` ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
Indhumathi27 commented on a change in pull request #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#discussion_r410675725 ########## File path: docs/mv-guide.md ########## @@ -0,0 +1,342 @@ +<!-- + 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 Materialized View + +* [Quick Example](#quick-example) +* [Introduction](#introduction) +* [Loading Data](#loading-data) +* [Querying Data](#querying-data) +* [Compaction](#compacting) +* [Data Management](#data-management) +* [Time Series Support](#time-series-support) +* [Time Series RollUp Support](#time-series-rollup-support) + +## Quick example + + Start spark-sql in terminal and run the following queries, + + ``` + CREATE TABLE maintable(a int, b string, c int) stored as carbondata; + INSERT INTO maintable SELECT 1, 'ab', 2; + CREATE MATERIALIZED VIEW view1 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 Index table from the plan + EXPLAIN SELECT a, sum(b) FROM maintable GROUP BY a; + ``` + +## Introduction + + Materialized views are created as sub-queries. User can create limitless materialized view to + improve query performance provided the storage requirements and loading time is acceptable. + + Materialized view can refreshed on commit or on manual. Once materialized views are created, + CarbonData's CarbonAnalyzer helps to select the most efficient materialized view based on Review comment: Class name has to be changed. Should be MVRewriteRule ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
Indhumathi27 commented on a change in pull request #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#discussion_r410676306 ########## File path: docs/mv-guide.md ########## @@ -0,0 +1,342 @@ +<!-- + 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 Materialized View + +* [Quick Example](#quick-example) +* [Introduction](#introduction) +* [Loading Data](#loading-data) +* [Querying Data](#querying-data) +* [Compaction](#compacting) +* [Data Management](#data-management) +* [Time Series Support](#time-series-support) +* [Time Series RollUp Support](#time-series-rollup-support) + +## Quick example + + Start spark-sql in terminal and run the following queries, + + ``` + CREATE TABLE maintable(a int, b string, c int) stored as carbondata; + INSERT INTO maintable SELECT 1, 'ab', 2; + CREATE MATERIALIZED VIEW view1 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 Index table from the plan + EXPLAIN SELECT a, sum(b) FROM maintable GROUP BY a; + ``` + +## Introduction + + Materialized views are created as sub-queries. User can create limitless materialized view to + improve query performance provided the storage requirements and loading time is acceptable. + + Materialized view can refreshed on commit or on manual. Once materialized views are created, + CarbonData's CarbonAnalyzer helps to select the most efficient materialized view based on + the user query and rewrite the SQL to select the data from materialized view instead of + related tables. Since the data size of materialized view is smaller and data is pre-processed, + user queries are much faster. + + For instance, related 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 materialized view using the CREATE MATERIALIZED VIEW statement. + + ``` + CREATE MATERIALIZED VIEW agg_sales + PROPERTIES('TABLE_BLOCKSIZE'='256 MB','LOCAL_DICTIONARY_ENABLE'='false') + AS + SELECT country, sex, sum(quantity), avg(price) + FROM sales + GROUP BY country, sex + ``` + + **NOTE**: + * Group by and Order by columns has to be provided in projection list while creating materialized view. + * If only single related table is involved in materialized view creation, then TableProperties of + related table (if not present in a aggregate function like sum(col)) listed below will be + inherited to materialized view. + 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. INVERTED_INDEX + 10. NO_INVERTED_INDEX + 11. COLUMN_COMPRESSOR + * Creating materialized view with select query containing only project of all columns of related + table is unsupported. + **Example:** + If table 'x' contains columns 'a,b,c', then creating MV Index with below queries is not supported. + 1. ```SELECT a,b,c FROM x``` + 2. ```SELECT * FROM x``` + * TableProperties can be provided in Properties excluding LOCAL_DICTIONARY_INCLUDE, + LOCAL_DICTIONARY_EXCLUDE, INVERTED_INDEX, NO_INVERTED_INDEX, SORT_COLUMNS, LONG_STRING_COLUMNS, + RANGE_COLUMN & COLUMN_META_CACHE. + * TableProperty given in Properties will be considered for materialized view creation, even though + if same property is inherited from related table, which allows user to provide different table + properties for materialized view. + * Materialized view creation with limit or union all CTAS queries is unsupported. + * Materialized view does not support streaming. + +#### How materialized views 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, + materialized view for this query will be selected among the candidates. + + For the related table **sales** and materialized view **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 materialized view**agg_sales** + instead of the related 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 related table **sales** only, because it does not satisfy materialized view + selection logic. + +## Loading data + +### Loading data on commit + + In case of WITHOUT DEFERRED REFRESH, for existing table with loaded data, data load to materialized + view will be triggered by the CREATE MATERIALIZED VIEW statement when user creates the materialized + view. + + For incremental loads to related table, data to materialized view will be loaded once the + corresponding related table load is completed. + +### Loading data on manual + + In case of WITH DEFERRED REFRESH, data load to materialized view will be triggered by the refresh + command. Materialized view will be in DISABLED state in below scenarios. + + * when materialized view is created. + * when data of related table and materialized view are not in sync. + + User should fire REFRESH MATERIALIZED VIEW command to sync all segments of related table with + materialized view and which ENABLES the materialized view for query. + + Command example: + ``` + REFRESH MATERIALIZED VIEW agg_sales + ``` + +### Loading data to multiple materialized views + + During load to related table, if anyone of the load to materialized view fails, then that + corresponding materialized view will be DISABLED and load to other materialized views mapped + to related table will continue. + + User can fire REFRESH MATERIALIZED VIEW command to sync or else the subsequent table load + will load the old failed loads along with current load and enable the disabled materialized view. + + **NOTE**: + * In case of InsertOverwrite/Update operation on related table, all segments of materialized view + will be MARKED_FOR_DELETE and reload to Index table will happen by REFRESH MATERIALIZED VIEW, + in case of materialized view which refresh on manual, once InsertOverwrite/Update operation on Review comment: ```suggestion in case of materialized view which refresh on manual and once the InsertOverwrite/Update operation on ``` ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
Indhumathi27 commented on issue #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#issuecomment-615834315 @niuge01 Please check and fix the link for mv in README.md file also ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
CarbonDataQA1 commented on issue #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#issuecomment-615838588 Build Success with Spark 2.3.4, Please check CI http://121.244.95.60:12545/job/ApacheCarbonPRBuilder2.3/2784/ ---------------------------------------------------------------- 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 |
|
|
In reply to this post by GitBox
CarbonDataQA1 commented on issue #3720: [CARBONDATA-3775] Update materialized view document
URL: https://github.com/apache/carbondata/pull/3720#issuecomment-615840109 Build Success with Spark 2.4.5, Please check CI http://121.244.95.60:12545/job/ApacheCarbon_PR_Builder_2.4.5/1071/ ---------------------------------------------------------------- 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 |
«
Return to Apache CarbonData JIRA issues
|
1 view|%1 views
| Free forum by Nabble | Edit this page |