[jira] [Commented] (CARBONDATA-2522) MV dataset when created with Joins, then it is not pointing towards the MV, while executing that join query.

[Akash R Nilugal (Jira)]

    [ https://issues.apache.org/jira/browse/CARBONDATA-2522?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16524461#comment-16524461 ]

xubo245 commented on CARBONDATA-2522:
-------------------------------------

It's work fine in cluster:


{code:java}
0: jdbc:hive2://hadoop1:10000> explain select t1.empname as c1, t2.designation from fact_table1 t1,fact_table2 t2,fact_table3 t3  where t1.empname = t2.empname and t1.empname=t3.empname;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
|                                                                                                                                                                         plan                                                                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| == CarbonData Profiler ==
Table Scan on datamap25_table
 - total blocklets: 2
 - filter: none
 - pruned by Main DataMap
    - skipped blocklets: 0
                                                                                                                                                                                                  |
| == Physical Plan ==
*Project [c1#1728, t2_designation#1729 AS designation#1789]
+- *BatchedScan CarbonDatasourceHadoopRelation [ Database name :default, Table name :datamap25_table, Schema :Some(StructType(StructField(c1,StringType,true), StructField(t2_designation,StringType,true))) ] default.datamap25_table[c1#1728,t2_designation#1729]  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
2 rows selected (0.209 seconds)

{code}

> MV dataset when created with Joins, then it is not pointing towards the MV, while executing that join query.
> ------------------------------------------------------------------------------------------------------------
>
>                 Key: CARBONDATA-2522
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-2522
>             Project: CarbonData
>          Issue Type: Bug
>         Environment: 3 Node Opensource ANT Cluster.
>            Reporter: Prasanna Ravichandran
>            Priority: Minor
>              Labels: MV, Materialistic_Views
>         Attachments: MV_joins.docx, data_mv.csv
>
>
> When MV is created on Joining tables, then the explain of that join query points to the maintable, instead of the created MV datamap.  
> Queries:
> drop table if exists fact_table1;
> 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';
> LOAD DATA local inpath 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table1 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '"','timestampformat'='dd-MM-yyyy');
> LOAD DATA local inpath 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table1 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '"','timestampformat'='dd-MM-yyyy');
> drop table if exists fact_table2;
> 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';
> LOAD DATA local inpath 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table2 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '"','timestampformat'='dd-MM-yyyy');
> LOAD DATA local inpath 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table2 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '"','timestampformat'='dd-MM-yyyy');
> drop table if exists fact_table3;
> CREATE TABLE fact_table3 (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';
> LOAD DATA local inpath 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table2 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '"','timestampformat'='dd-MM-yyyy');
> LOAD DATA local inpath 'hdfs://hacluster/user/prasanna/data_mv.csv' INTO TABLE fact_table2 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '"','timestampformat'='dd-MM-yyyy');
> create datamap datamap25 using 'mv' as select t1.empname as c1, t2.designation from fact_table1 t1,fact_table2 t2,fact_table3 t3  where t1.empname = t2.empname and t1.empname=t3.empname;
> explain create datamap datamap25 using 'mv' as select t1.empname as c1, t2.designation from fact_table1 t1,fact_table2 t2,fact_table3 t3  where t1.empname = t2.empname and t1.empname=t3.empname;
>  

--
This message was sent by Atlassian JIRA
(v7.6.3#76005)