You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@carbondata.apache.org by "Ravindra Pesala (JIRA)" <ji...@apache.org> on 2018/07/09 13:52:00 UTC
[jira] [Commented] (CARBONDATA-2661) Query failed with group by
column when MV Datamap created without group by column
[ https://issues.apache.org/jira/browse/CARBONDATA-2661?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16536938#comment-16536938 ]
Ravindra Pesala commented on CARBONDATA-2661:
---------------------------------------------
It is duplicated to Jira 2568 , please close it
> Query failed with group by column when MV Datamap created without group by column
> ---------------------------------------------------------------------------------
>
> Key: CARBONDATA-2661
> URL: https://issues.apache.org/jira/browse/CARBONDATA-2661
> Project: CarbonData
> Issue Type: Bug
> Reporter: xubo245
> Assignee: xubo245
> Priority: Major
>
> {code:java}
> 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/hive/warehouse//data_mv.csv' INTO TABLE fact_table1 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '"','timestampformat'='dd-MM-yyyy');
>
> LOAD DATA local inpath 'hdfs://hacluster/user/hive/warehouse//data_mv.csv' INTO TABLE fact_table1 OPTIONS('DELIMITER'= ',', 'QUOTECHAR'= '"','timestampformat'='dd-MM-yyyy');
> 0: jdbc:hive2://hadoop1:10000> create datamap mv1 using 'mv' as select sum(salary),count(empno) from fact_table1 group by empname;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (0.399 seconds)
> 0: jdbc:hive2://hadoop1:10000> rebuild datamap mv1;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (1.57 seconds)
> 0: jdbc:hive2://hadoop1:10000> create datamap mv2 using 'mv' as select sum(salary) from fact_table1 group by empname;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (0.321 seconds)
> 0: jdbc:hive2://hadoop1:10000> rebuild datamap mv2;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (1.241 seconds)
> 0: jdbc:hive2://hadoop1:10000> show datamap on table fact_table1;
> +--------------+------------+--------------------------+--+
> | DataMapName | ClassName | Associated Table |
> +--------------+------------+--------------------------+--+
> | datamap25 | mv | default.datamap25_table |
> | mv1 | mv | default.mv1_table |
> | mv2 | mv | default.mv2_table |
> +--------------+------------+--------------------------+--+
> 3 rows selected (0.047 seconds)
> 0: jdbc:hive2://hadoop1:10000> select * from default.mv1_table;
> +-------------+--------------+--+
> | sum_salary | count_empno |
> +-------------+--------------+--+
> | 172332 | 18 |
> | 162972 | 18 |
> | 90720 | 18 |
> | 202572 | 18 |
> | 90720 | 18 |
> | 128232 | 18 |
> | 130410 | 18 |
> | 202464 | 18 |
> | 243846 | 18 |
> | 238410 | 18 |
> +-------------+--------------+--+
> 10 rows selected (0.314 seconds)
> 0: jdbc:hive2://hadoop1:10000> select count(*) from default.mv1_table;
> +-----------+--+
> | count(1) |
> +-----------+--+
> | 10 |
> +-----------+--+
> 1 row selected (0.139 seconds)
> 0: jdbc:hive2://hadoop1:10000> select * from default.mv2_table;
> +-------------+--+
> | sum_salary |
> +-------------+--+
> | 172332 |
> | 162972 |
> | 90720 |
> | 202464 |
> | 243846 |
> | 128232 |
> | 130410 |
> | 90720 |
> | 202572 |
> | 238410 |
> +-------------+--+
> 10 rows selected (0.262 seconds)
> 0: jdbc:hive2://hadoop1:10000> explain select sum(salary) from fact_table1 group by empname;
> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
> | plan |
> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
> | == CarbonData Profiler ==
> Table Scan on fact_table1
> - total blocklets: 2
> - filter: none
> - pruned by Main DataMap
> - skipped blocklets: 0
> |
> | == Physical Plan ==
> *HashAggregate(keys=[empname#2499], functions=[sum(cast(salary#2511 as bigint))])
> +- Exchange hashpartitioning(empname#2499, 200)
> +- *HashAggregate(keys=[empname#2499], functions=[partial_sum(cast(salary#2511 as bigint))])
> +- *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[empname#2499,salary#2511] |
> +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
> 2 rows selected (0.164 seconds)
> 0: jdbc:hive2://hadoop1:10000> explain select empname,sum(salary) from fact_table1 group by empname;
> Error: org.apache.spark.sql.AnalysisException: cannot resolve '`fact_table1.empname`' given input columns: [sum_salary, count_empno]; line 6 pos 11;
> 'Aggregate ['fact_table1.empname], ['fact_table1.empname, 'sum('gen_subsumer_0.sum(salary)) AS sum(salary)#2802]
> +- 'SubqueryAlias gen_subsumer_0
> +- 'Aggregate ['fact_table1.empname], [sum(sum_salary#2495L) AS sum(salary)#2800L, sum(count_empno#2496L) AS count(empno)#2801L]
> +- SubqueryAlias mv1_table
> +- Relation[sum_salary#2495L,count_empno#2496L] CarbonDatasourceHadoopRelation [ Database name :default, Table name :mv1_table, Schema :Some(StructType(StructField(sum_salary,LongType,true), StructField(count_empno,LongType,true))) ] (state=,code=0)
> 0: jdbc:hive2://hadoop1:10000> create datamap mv3 using 'mv' as select empname,sum(salary) from fact_table1 group by empname;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (0.318 seconds)
> 0: jdbc:hive2://hadoop1:10000> rebuild datamap mv3;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (1.437 seconds)
> 0: jdbc:hive2://hadoop1:10000> drop datamap mv3;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (0.941 seconds)
> 0: jdbc:hive2://hadoop1:10000> create datamap mv3 using 'mv' as select empname,sum(salary),count(empno) from fact_table1 group by empname;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (0.537 seconds)
> 0: jdbc:hive2://hadoop1:10000> create datamap mv4 using 'mv' as select empname,sum(salary) from fact_table1 group by empname;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (0.271 seconds)
> 0: jdbc:hive2://hadoop1:10000> rebuild datamap mv3;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (1.525 seconds)
> 0: jdbc:hive2://hadoop1:10000> rebuild datamap mv4;
> +---------+--+
> | Result |
> +---------+--+
> +---------+--+
> No rows selected (0.993 seconds)
> 0: jdbc:hive2://hadoop1:10000> explain select empname,sum(salary) from fact_table1 group by empname;
> Error: org.apache.spark.sql.AnalysisException: cannot resolve '`fact_table1.empname`' given input columns: [sum_salary, count_empno]; line 6 pos 11;
> 'Aggregate ['fact_table1.empname], ['fact_table1.empname, 'sum('gen_subsumer_0.sum(salary)) AS sum(salary)#3113]
> +- 'SubqueryAlias gen_subsumer_0
> +- 'Aggregate ['fact_table1.empname], [sum(sum_salary#2495L) AS sum(salary)#3111L, sum(count_empno#2496L) AS count(empno)#3112L]
> +- SubqueryAlias mv1_table
> +- Relation[sum_salary#2495L,count_empno#2496L] CarbonDatasourceHadoopRelation [ Database name :default, Table name :mv1_table, Schema :Some(StructType(StructField(sum_salary,LongType,true), StructField(count_empno,LongType,true))) ] (state=,code=0)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)