[
https://issues.apache.org/jira/browse/CARBONDATA-2540?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16536758#comment-16536758
]
Ravindra Pesala commented on CARBONDATA-2540:
---------------------------------------------
Union is not supported for mv queries, will add validation while creating mv
> MV Dataset - Unionall queries are not fetching data from MV dataset.
> --------------------------------------------------------------------
>
> Key: CARBONDATA-2540
> URL: https://issues.apache.org/jira/browse/CARBONDATA-2540
> Project: CarbonData
> Issue Type: Bug
> Components: data-query
> Reporter: Prasanna Ravichandran
> Priority: Minor
> Labels: Carbondata, MV, Materialistic_Views
> Attachments: data_mv.csv
>
>
> Unionall queries are not fetching data from MV dataset.
> Test queries:
> scala> carbon.sql("drop table if exists fact_table1").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("CREATE TABLE fact_table1 (empno int, empname String,
> designation String, doj Timestamp,workgroupcategory int,
> workgroupcategoryname String, deptno int, deptname String,projectcode int,
> projectjoindate Timestamp, projectenddate Timestamp,attendance
> int,utilization int,salary int)STORED BY
> 'org.apache.carbondata.format'").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("LOAD DATA local inpath
> 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table1
> OPTIONS('DELIMITER'= ',', 'QUOTECHAR'=
> '"','timestampformat'='dd-MM-yyyy')").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("LOAD DATA local inpath
> 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table1
> OPTIONS('DELIMITER'= ',', 'QUOTECHAR'=
> '\"','timestampformat'='dd-MM-yyyy')").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("drop table if exists fact_table2").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("CREATE TABLE fact_table2 (empno int, empname String,
> designation String, doj Timestamp,workgroupcategory int,
> workgroupcategoryname String, deptno int, deptname String,projectcode int,
> projectjoindate Timestamp, projectenddate Timestamp,attendance
> int,utilization int,salary int)STORED BY
> 'org.apache.carbondata.format'").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("LOAD DATA local inpath
> 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table2
> OPTIONS('DELIMITER'= ',', 'QUOTECHAR'=
> '\"','timestampformat'='dd-MM-yyyy')").show(200,false)
> ++
> ||
> ++
> ++
> scala> carbon.sql("LOAD DATA local inpath
> 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table2
> OPTIONS('DELIMITER'= ',', 'QUOTECHAR'=
> '\"','timestampformat'='dd-MM-yyyy')").show(200,false)
> ++
> ||
> ++
> ++
>
> scala> carbon.sql("create datamap mv_unional using 'mv' as Select Z.empno
> From (Select empno,empname From fact_table1 Union All Select empno,empname
> from fact_table2) As Z Group By Z.empno").show(200,false)
> ++
> ||
> ++
> ++
>
> scala> carbon.sql("rebuild datamap mv_unional").show()
> ++
> ||
> ++
> ++
> scala> carbon.sql("explain Select Z.empno From (Select empno,empname From
> fact_table1 Union All Select empno,empname from fact_table2) As Z Group By
> Z.empno").show(200,false)
> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> |plan |
> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> |== CarbonData Profiler ==
> Table Scan on fact_table1
> - total blocklets: 4
> - filter: none
> - pruned by Main DataMap
> - skipped blocklets: 2
> |
> |== Physical Plan ==
> *HashAggregate(keys=[empno#2588], functions=[])
> +- Exchange hashpartitioning(empno#2588, 200)
> +- *HashAggregate(keys=[empno#2588], functions=[])
> +- Union
> :- *BatchedScan CarbonDatasourceHadoopRelation [ Database name :default,
> Table name :fact_table1, Schema
> :Some(StructType(StructField(empno,IntegerType,true),
> StructField(empname,StringType,true),
> StructField(designation,StringType,true),
> StructField(doj,TimestampType,true),
> StructField(workgroupcategory,IntegerType,true),
> StructField(workgroupcategoryname,StringType,true),
> StructField(deptno,IntegerType,true), StructField(deptname,StringType,true),
> StructField(projectcode,IntegerType,true),
> StructField(projectjoindate,TimestampType,true),
> StructField(projectenddate,TimestampType,true),
> StructField(attendance,IntegerType,true),
> StructField(utilization,IntegerType,true),
> StructField(salary,IntegerType,true))) ] default.fact_table1[empno#2588]
> +- *BatchedScan CarbonDatasourceHadoopRelation [ Database name :default,
> Table name :fact_table2, Schema
> :Some(StructType(StructField(empno,IntegerType,true),
> StructField(empname,StringType,true),
> StructField(designation,StringType,true),
> StructField(doj,TimestampType,true),
> StructField(workgroupcategory,IntegerType,true),
> StructField(workgroupcategoryname,StringType,true),
> StructField(deptno,IntegerType,true), StructField(deptname,StringType,true),
> StructField(projectcode,IntegerType,true),
> StructField(projectjoindate,TimestampType,true),
> StructField(projectenddate,TimestampType,true),
> StructField(attendance,IntegerType,true),
> StructField(utilization,IntegerType,true),
> StructField(salary,IntegerType,true))) ] default.fact_table2[empno#2514]|
> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> It is accessing data only from the main tables and not from the created MV
> for the unionall.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
