[ 
https://issues.apache.org/jira/browse/HIVE-15581?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

James Ball resolved HIVE-15581.
-------------------------------
    Resolution: Won't Fix

Close as issue specific to MapReduce.

> Unable to use advanced aggregation with multiple inserts clause
> ---------------------------------------------------------------
>
>                 Key: HIVE-15581
>                 URL: https://issues.apache.org/jira/browse/HIVE-15581
>             Project: Hive
>          Issue Type: Bug
>          Components: Hive
>    Affects Versions: 1.2.1
>            Reporter: James Ball
>              Labels: newbie
>
> ■Use Cases
> - Use multiple insert clauses within a single query to insert multiple static 
> (user-defined) partitions into a single table.
> - Use advanced aggregation (cube) features within each insert clause to 
> include subtotals of columns for each partition
> ■Expected Behaviour
> - Subtotals are inserted for all combinations of the set of columns
> ■Observed Behaviour
> - No subtotals are not inserted for any combination of the set of columns
> ■Sample Queries
> {code:sql}
> // Create test tables
> create table if not exists
>       table1
>       (
>               column1 string,
>               column2 string,
>               column3 int
>               )
>       stored as orc
>       tblproperties
>       (
>               "orc.compress" = "SNAPPY"
>               );
> create table if not exists
>       table2
>       (
>               column1 string,
>               column2 string,
>               column3 int
>               )
>       partitioned by
>       (
>               partition1 string
>               )
>       stored as orc
>       tblproperties
>       (
>               "orc.compress" = "SNAPPY"
>               );
> create table if not exists
>       table3
>       (
>               column1 string,
>               column2 string,
>               column3 int
>               )
>       partitioned by
>       (
>               partition1 string
>               )
>       stored as orc
>       tblproperties
>       (
>               "orc.compress" = "SNAPPY"
>               );
> {code}
> {code:sql}
> // Insert test values
> insert overwrite table
>       table1
>       values
>               ('value1', 'value1', 1),
>               ('value2', 'value2', 1),
>               ('value3', 'value3', 1);
> {code}
> {code:sql}
> // Single insert clause with multiple inserts syntax
> // Subtotals are inserted into target table
> from
>       table1
> insert overwrite table
>       table2
>       partition
>       (
>               partition1 = 'value1'
>               )
>       select
>               column1,
>               column2,
>               sum(column3) as column3
>       group by
>               column1,
>               column2
>       with cube;
> {code}
> {code:sql}
> // Multiple insert clauses with multiple inserts syntax
> // Subtotals are not inserted into target table
> from
>       table1
> insert overwrite table
>       table3
>       partition
>       (
>               partition1 = 'value1'
>               )
>       select
>               column1,
>               column2,
>               sum(column3) as column3
>       group by
>               column1,
>               column2
>       with cube
> insert overwrite table
>       table3
>       partition
>       (
>               partition1 = 'value2'
>               )
>       select
>               column1,
>               column2,
>               sum(column3) as column3
>       group by
>               column1,
>               column2
>       with cube;
> {code}
> ■Executions Plans
> - Single insert clause with multiple inserts syntax
> {noformat}
> STAGE DEPENDENCIES:
>   Stage-1 is a root stage
>   Stage-0 depends on stages: Stage-1
>   Stage-2 depends on stages: Stage-0
> STAGE PLANS:
>   Stage: Stage-1
>     Map Reduce
>       Map Operator Tree:
>           TableScan
>             alias: table1
>             Statistics: Num rows: 3 Data size: 552 Basic stats: COMPLETE 
> Column stats: NONE
>             Select Operator
>               expressions: column1 (type: string), column2 (type: string), 
> column3 (type: int)
>               outputColumnNames: column1, column2, column3
>               Statistics: Num rows: 3 Data size: 552 Basic stats: COMPLETE 
> Column stats: NONE
>               Group By Operator
>                 aggregations: sum(column3)
>                 keys: column1 (type: string), column2 (type: string), '0' 
> (type: string)
>                 mode: hash
>                 outputColumnNames: _col0, _col1, _col2, _col3
>                 Statistics: Num rows: 12 Data size: 2208 Basic stats: 
> COMPLETE Column stats: NONE
>                 Reduce Output Operator
>                   key expressions: _col0 (type: string), _col1 (type: 
> string), _col2 (type: string)
>                   sort order: +++
>                   Map-reduce partition columns: _col0 (type: string), _col1 
> (type: string), _col2 (type: string)
>                   Statistics: Num rows: 12 Data size: 2208 Basic stats: 
> COMPLETE Column stats: NONE
>                   value expressions: _col3 (type: bigint)
>       Reduce Operator Tree:
>         Group By Operator
>           aggregations: sum(VALUE._col0)
>           keys: KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 
> (type: string)
>           mode: mergepartial
>           outputColumnNames: _col0, _col1, _col3
>           Statistics: Num rows: 6 Data size: 1104 Basic stats: COMPLETE 
> Column stats: NONE
>           pruneGroupingSetId: true
>           Select Operator
>             expressions: _col0 (type: string), _col1 (type: string), 
> UDFToInteger(_col3) (type: int)
>             outputColumnNames: _col0, _col1, _col2
>             Statistics: Num rows: 6 Data size: 1104 Basic stats: COMPLETE 
> Column stats: NONE
>             File Output Operator
>               compressed: false
>               Statistics: Num rows: 6 Data size: 1104 Basic stats: COMPLETE 
> Column stats: NONE
>               table:
>                   input format: 
> org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
>                   output format: 
> org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
>                   serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
>                   name: zzz_james_ball.table2
>   Stage: Stage-0
>     Move Operator
>       tables:
>           partition:
>             partition1 value1
>           replace: true
>           table:
>               input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
>               output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
>               serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
>               name: zzz_james_ball.table2
>   Stage: Stage-2
>     Stats-Aggr Operator
> {noformat}
> - Single insert clause with multiple inserts syntax
> {noformat}
> STAGE DEPENDENCIES:
>   Stage-2 is a root stage
>   Stage-0 depends on stages: Stage-2
>   Stage-3 depends on stages: Stage-0
>   Stage-1 depends on stages: Stage-2
>   Stage-4 depends on stages: Stage-1
> STAGE PLANS:
>   Stage: Stage-2
>     Map Reduce
>       Map Operator Tree:
>           TableScan
>             alias: table1
>             Statistics: Num rows: 3 Data size: 552 Basic stats: COMPLETE 
> Column stats: NONE
>             Select Operator
>               expressions: column1 (type: string), column2 (type: string), 
> column3 (type: int)
>               outputColumnNames: column1, column2, column3
>               Statistics: Num rows: 3 Data size: 552 Basic stats: COMPLETE 
> Column stats: NONE
>               Reduce Output Operator
>                 key expressions: column1 (type: string), column2 (type: 
> string)
>                 sort order: ++
>                 Map-reduce partition columns: column1 (type: string), column2 
> (type: string)
>                 Statistics: Num rows: 3 Data size: 552 Basic stats: COMPLETE 
> Column stats: NONE
>                 value expressions: column3 (type: int)
>       Reduce Operator Tree:
>         Forward
>           Statistics: Num rows: 3 Data size: 552 Basic stats: COMPLETE Column 
> stats: NONE
>           Group By Operator
>             aggregations: sum(VALUE._col0)
>             keys: KEY._col0 (type: string), KEY._col1 (type: string)
>             mode: complete
>             outputColumnNames: _col0, _col1, _col2
>             Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE 
> Column stats: NONE
>             Select Operator
>               expressions: _col0 (type: string), _col1 (type: string), 
> UDFToInteger(_col2) (type: int)
>               outputColumnNames: _col0, _col1, _col2
>               Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE 
> Column stats: NONE
>               File Output Operator
>                 compressed: false
>                 Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE 
> Column stats: NONE
>                 table:
>                     input format: 
> org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
>                     output format: 
> org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
>                     serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
>                     name: zzz_james_ball.table3
>           Group By Operator
>             aggregations: sum(VALUE._col0)
>             keys: KEY._col0 (type: string), KEY._col1 (type: string)
>             mode: complete
>             outputColumnNames: _col0, _col1, _col2
>             Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE 
> Column stats: NONE
>             Select Operator
>               expressions: _col0 (type: string), _col1 (type: string), 
> UDFToInteger(_col2) (type: int)
>               outputColumnNames: _col0, _col1, _col2
>               Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE 
> Column stats: NONE
>               File Output Operator
>                 compressed: false
>                 Statistics: Num rows: 1 Data size: 184 Basic stats: COMPLETE 
> Column stats: NONE
>                 table:
>                     input format: 
> org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
>                     output format: 
> org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
>                     serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
>                     name: zzz_james_ball.table3
>   Stage: Stage-0
>     Move Operator
>       tables:
>           partition:
>             partition1 value1
>           replace: true
>           table:
>               input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
>               output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
>               serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
>               name: zzz_james_ball.table3
>   Stage: Stage-3
>     Stats-Aggr Operator
>   Stage: Stage-1
>     Move Operator
>       tables:
>           partition:
>             partition1 value2
>           replace: true
>           table:
>               input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
>               output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
>               serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde
>               name: zzz_james_ball.table3
>   Stage: Stage-4
>     Stats-Aggr Operator
> {noformat}
> ■Notes
> - This problem occurs with all advanced aggregation features (cube, grouping 
> sets, rollup)
> - This problem occurs whether hive.map.aggr is set to true or false
> - Dynamic partitions are not used because the partition values are set 
> manually within the where conditions of each insert clause



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Reply via email to