[jira] [Commented] (CARBONDATA-2551) [MV] Query failed when MV Datamap created with Expression in Aggregate

[Akash R Nilugal (Jira)]

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

xubo245 commented on CARBONDATA-2551:
-------------------------------------

It's work fine in cluster:


{code:java}
0: jdbc:hive2://hadoop1:10000> explain select unit,year,country,b_country,sum(salary) from babu2 group by unit,year,country,b_country;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
|                                                                                                                                                                                                                                                                                                                                                                                                                                           plan                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| == CarbonData Profiler ==
Table Scan on agr22_table
 - total blocklets: 1
 - filter: none
 - pruned by Main DataMap
    - skipped blocklets: 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| == Physical Plan ==
*HashAggregate(keys=[babu2_unit#1026, babu2_year#1027, babu2_country#1028, babu2_b_country#1029], functions=[sum(sum_salary#1030)])
+- Exchange hashpartitioning(babu2_unit#1026, babu2_year#1027, babu2_country#1028, babu2_b_country#1029, 200)
   +- *HashAggregate(keys=[babu2_unit#1026, babu2_year#1027, babu2_country#1028, babu2_b_country#1029], functions=[partial_sum(sum_salary#1030)])
      +- *BatchedScan CarbonDatasourceHadoopRelation [ Database name :default, Table name :agr22_table, Schema :Some(StructType(StructField(babu2_unit,ShortType,true), StructField(babu2_year,ShortType,true), StructField(babu2_country,ShortType,true), StructField(babu2_b_country,ShortType,true), StructField(sum_salary,DoubleType,true))) ] default.agr22_table[babu2_unit#1026,babu2_year#1027,babu2_country#1028,babu2_b_country#1029,sum_salary#1030]  |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
2 rows selected (0.404 seconds)
0: jdbc:hive2://hadoop1:10000>  select unit,year,country,b_country,sum(salary) from babu2 group by unit,year,country,b_country;
+-------+-------+----------+------------+--------------+--+
| unit  | year  | country  | b_country  | sum(salary)  |
+-------+-------+----------+------------+--------------+--+
| 12    | 14    | 12       | 11         | 12.0         |
+-------+-------+----------+------------+--------------+--+
1 row selected (0.466 seconds)

{code}

> [MV] Query failed when MV Datamap created with Expression in Aggregate
> ----------------------------------------------------------------------
>
>                 Key: CARBONDATA-2551
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-2551
>             Project: CarbonData
>          Issue Type: Bug
>            Reporter: Babulal
>            Assignee: xubo245
>            Priority: Major
>
> CREATE TABLE IF NOT EXISTS babu2 (
>  month smallint,
>  code string ,
>  country smallint,
>  salary double ,
>  unit smallint,
>  b_country smallint,
>  imei smallint,
>  year smallint)
> STORED BY 'org.apache.carbondata.format' TBLPROPERTIES('SORT_SCOPE'='GLOBAL_SORT','table_blocksize'='256','sort_columns'='year,imei,country,b_country,code,month,unit');
>  
> insert into babu2 select 12,12,12,12,12,11,12,14;
> 0: jdbc:hive2://10.18.222.231:23040> create datamap agr21 using 'mv' as select unit,year,country,b_country,sum(case when imei=1 and (year=2015 )then salary else 0 end) v2015ex from babu2 group by unit,year,country,b_country;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (0.399 seconds)
> 0: jdbc:hive2://10.18.222.231:23040> rebuild datamap agr21;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (12.643 seconds)
> 0: jdbc:hive2://10.18.222.231:23040> create datamap agr22 using 'mv' as select unit,year,country,b_country,sum(salary) from babu2 group by unit,year,country,b_country;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (0.429 seconds)
> 0: jdbc:hive2://10.18.222.231:23040> rebuild datamap agr22;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (12.318 seconds)
> 0: jdbc:hive2://10.18.222.231:23040> explain select unit,year,country,b_country,sum(salary) from babu2 group by unit,year,country,b_country;
> Error: org.apache.spark.sql.AnalysisException: cannot resolve '`babu2.salary`' given input columns: [b_country, year, unit, v2015ex, country]; line 1 pos 132;
> 'Project [UDF:preAgg() AS preAgg#4106, unit#3857, year#3860, country#3855, b_country#3858, 'sum('babu2.salary) AS sum(salary)#4107]
> +- SubqueryAlias gen_subsumer_0
>  +- Aggregate [unit#3857, year#3860, country#3855, b_country#3858], [unit#3857, year#3860, country#3855, b_country#3858, sum(CASE WHEN ((cast(imei#3859 as int) = 1) && (cast(year#3860 as int) = 2015)) THEN salary#3856 ELSE 0.0 END) AS v2015ex#4105]
>  +- SubqueryAlias babu2
>  +- Relation[month#3853,code#3854,country#3855,salary#3856,unit#3857,b_country#3858,imei#3859,year#3860] CarbonDatasourceHadoopRelation [ Database name :default, Table name :babu2, Schema :Some(StructType(StructField(month,ShortType,true), StructField(code,StringType,true), StructField(country,ShortType,true), StructField(salary,DoubleType,true), StructField(unit,ShortType,true), StructField(b_country,ShortType,true), StructField(imei,ShortType,true), StructField(year,ShortType,true))) ] (state=,code=0)
>  
>  
> 0: jdbc:hive2://10.18.222.231:23040> desc agr21_table;
> +------------------+------------+----------+--+
> | col_name | data_type | comment |
> +------------------+------------+----------+--+
> | babu2_unit | smallint | NULL |
> | babu2_year | smallint | NULL |
> | babu2_country | smallint | NULL |
> | babu2_b_country | smallint | NULL |
> | v2015ex | double | NULL |
> +------------------+------------+----------+--+
> 5 rows selected (0.059 seconds)
>  
> BLR1000023613:/srv/spark2.2Bigdata/sparkJdbc # hadoop fs -cat /user/hive/warehouse/carbon.store/_system/agr21.dmschema
> 18/05/29 00:19:05 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
> {"dataMapName":"agr21","providerName":"mv","relationIdentifier":\{"databaseName":"default","tableName":"agr21_table","tableId":""},"ctasQuery":" select unit,year,country,b_country,sum(case when imei\u003d1 and (year\u003d2015 )then salary else 0 end) v2015ex from babu2 group by unit,year,country,b_country","properties":\{"_internal.deferred.rebuild":"false"},"parentTables":[\{"databaseName":"default","tableName":"babu2","tableId":""}]}BLR1000023613:/srv/spark2.2Bigdata/sparkJdbc #

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