You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Ted Xu <te...@gmail.com> on 2010/05/12 04:44:10 UTC
BUG at optimizer or map side aggregate?
Hi all,
I think I found a bug, I'm not sure whether the problem is at optimizer
(PPD) or at map side aggregate.
See query listed below:
-------------------------------------
create table if not exists dm_fact_buyer_prd_info_d (
category_id string
,gmv_trade_num int
,user_id int
)
PARTITIONED BY (ds int);
set hive.optimize.ppd=true;
set hive.map.aggr=true;
explain select 20100426, category_id1,category_id2,assoc_idx
from (
select
category_id1
, category_id2
, count(distinct user_id) as assoc_idx
from (
select
t1.category_id as category_id1
, t2.category_id as category_id2
, t1.user_id
from (
select category_id, user_id
from dm_fact_buyer_prd_info_d
where ds <= 20100426
and ds > 20100419
and category_id >0
and gmv_trade_num>0
group by category_id, user_id ) t1
join (
select category_id, user_id
from dm_fact_buyer_prd_info_d
where ds <= 20100426
and ds >20100419
and category_id >0
and gmv_trade_num >0
group by category_id, user_id ) t2 on t1.user_id=t2.user_id
) t1
group by category_id1, category_id2 ) t_o
where category_id1 <> category_id2
and assoc_idx > 2;
--------------------------------
The query above will fail when execute, throwing exception: "can not cast
UDFOpNotEqual(Text, IntWritable) to UDFOpNotEqual(Text, Text)".
I explained the query and the execute plan looks really wired (see
the highlighted predicate):
--------------------------------
ABSTRACT SYNTAX TREE:
(TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_SUBQUERY
(TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM
(TOK_TABREF dm_fact_buyer_prd_info_d)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR
TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL category_id))
(TOK_SELEXPR (TOK_TABLE_OR_COL user_id))) (TOK_WHERE (and (and (and (<=
(TOK_TABLE_OR_COL ds) 20100426) (> (TOK_TABLE_OR_COL ds) 20100419)) (>
(TOK_TABLE_OR_COL category_id) 0)) (> (TOK_TABLE_OR_COL gmv_trade_num) 0)))
(TOK_GROUPBY (TOK_TABLE_OR_COL category_id) (TOK_TABLE_OR_COL user_id))))
t1) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF
dm_fact_buyer_prd_info_d)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR
TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL category_id))
(TOK_SELEXPR (TOK_TABLE_OR_COL user_id))) (TOK_WHERE (and (and (and (<=
(TOK_TABLE_OR_COL ds) 20100426) (> (TOK_TABLE_OR_COL ds) 20100419)) (>
(TOK_TABLE_OR_COL category_id) 0)) (> (TOK_TABLE_OR_COL gmv_trade_num) 0)))
(TOK_GROUPBY (TOK_TABLE_OR_COL category_id) (TOK_TABLE_OR_COL user_id))))
t2) (= (. (TOK_TABLE_OR_COL t1) user_id) (. (TOK_TABLE_OR_COL t2)
user_id)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT
(TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) category_id) category_id1)
(TOK_SELEXPR (. (TOK_TABLE_OR_COL t2) category_id) category_id2)
(TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) user_id))))) t1)) (TOK_INSERT
(TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR
(TOK_TABLE_OR_COL category_id1)) (TOK_SELEXPR (TOK_TABLE_OR_COL
category_id2)) (TOK_SELEXPR (TOK_FUNCTIONDI count (TOK_TABLE_OR_COL
user_id)) assoc_idx)) (TOK_GROUPBY (TOK_TABLE_OR_COL category_id1)
(TOK_TABLE_OR_COL category_id2)))) t_o)) (TOK_INSERT (TOK_DESTINATION
(TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR 20100426) (TOK_SELEXPR
(TOK_TABLE_OR_COL category_id1)) (TOK_SELEXPR (TOK_TABLE_OR_COL
category_id2)) (TOK_SELEXPR (TOK_TABLE_OR_COL assoc_idx))) (TOK_WHERE (and
(<> (TOK_TABLE_OR_COL category_id1) (TOK_TABLE_OR_COL category_id2)) (>
(TOK_TABLE_OR_COL assoc_idx) 2)))))
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-2 depends on stages: Stage-1, Stage-4
Stage-3 depends on stages: Stage-2
Stage-4 is a root stage
Stage-2 depends on stages: Stage-1, Stage-4
Stage-3 depends on stages: Stage-2
Stage-0 is a root stage
STAGE PLANS:
Stage: Stage-1
Map Reduce
Alias -> Map Operator Tree:
t_o:t1:t1:dm_fact_buyer_prd_info_d
TableScan
alias: dm_fact_buyer_prd_info_d
Filter Operator
predicate:
expr: (((((UDFToDouble(ds) <= UDFToDouble(20100426)) and
(UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id) >
UDFToDouble(0))) and (gmv_trade_num > 0)) and (category_id <> user_id))
type: boolean
Filter Operator
predicate:
expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426)) and
(UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id) >
UDFToDouble(0))) and (gmv_trade_num > 0))
type: boolean
Select Operator
expressions:
expr: category_id
type: string
expr: user_id
type: int
outputColumnNames: category_id, user_id
Group By Operator
keys:
expr: category_id
type: string
expr: user_id
type: int
mode: hash
outputColumnNames: _col0, _col1
Reduce Output Operator
key expressions:
expr: _col0
type: string
expr: _col1
type: int
sort order: ++
Map-reduce partition columns:
expr: _col0
type: string
expr: _col1
type: int
tag: -1
Reduce Operator Tree:
Group By Operator
keys:
expr: KEY._col0
type: string
expr: KEY._col1
type: int
mode: mergepartial
outputColumnNames: _col0, _col1
Select Operator
expressions:
expr: _col0
type: string
expr: _col1
type: int
outputColumnNames: _col0, _col1
File Output Operator
compressed: true
GlobalTableId: 0
table:
input format:
org.apache.hadoop.mapred.SequenceFileInputFormat
output format:
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
Stage: Stage-2
Map Reduce
Alias -> Map Operator Tree:
$INTNAME
Reduce Output Operator
key expressions:
expr: _col1
type: int
sort order: +
Map-reduce partition columns:
expr: _col1
type: int
tag: 0
value expressions:
expr: _col0
type: string
expr: _col1
type: int
$INTNAME1
Reduce Output Operator
key expressions:
expr: _col1
type: int
sort order: +
Map-reduce partition columns:
expr: _col1
type: int
tag: 1
value expressions:
expr: _col0
type: string
Reduce Operator Tree:
Join Operator
condition map:
Inner Join 0 to 1
condition expressions:
0 {VALUE._col0} {VALUE._col1}
1 {VALUE._col0}
outputColumnNames: _col0, _col1, _col2
Select Operator
expressions:
expr: _col0
type: string
expr: _col2
type: string
expr: _col1
type: int
outputColumnNames: _col0, _col1, _col2
Select Operator
expressions:
expr: _col0
type: string
expr: _col1
type: string
expr: _col2
type: int
outputColumnNames: _col0, _col1, _col2
Group By Operator
aggregations:
expr: count(DISTINCT _col2)
keys:
expr: _col0
type: string
expr: _col1
type: string
expr: _col2
type: int
mode: hash
outputColumnNames: _col0, _col1, _col2, _col3
File Output Operator
compressed: true
GlobalTableId: 0
table:
input format:
org.apache.hadoop.mapred.SequenceFileInputFormat
output format:
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
Stage: Stage-3
Map Reduce
Alias -> Map Operator Tree:
file:/group/tbdev/shaojie/scratch/420686432\10003
Reduce Output Operator
key expressions:
expr: _col0
type: string
expr: _col1
type: string
expr: _col2
type: int
sort order: +++
Map-reduce partition columns:
expr: _col0
type: string
expr: _col1
type: string
tag: -1
value expressions:
expr: _col3
type: bigint
Reduce Operator Tree:
Group By Operator
aggregations:
expr: count(DISTINCT KEY._col2)
keys:
expr: KEY._col0
type: string
expr: KEY._col1
type: string
mode: mergepartial
outputColumnNames: _col0, _col1, _col2
Select Operator
expressions:
expr: _col0
type: string
expr: _col1
type: string
expr: _col2
type: bigint
outputColumnNames: _col0, _col1, _col2
Filter Operator
predicate:
expr: ((_col0 <> _col1) and (UDFToDouble(_col2) >
UDFToDouble(2)))
type: boolean
Select Operator
expressions:
expr: 20100426
type: int
expr: _col0
type: string
expr: _col1
type: string
expr: _col2
type: bigint
outputColumnNames: _col0, _col1, _col2, _col3
File Output Operator
compressed: false
GlobalTableId: 0
table:
input format: org.apache.hadoop.mapred.TextInputFormat
output format:
org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Stage: Stage-4
Map Reduce
Alias -> Map Operator Tree:
t_o:t1:t2:dm_fact_buyer_prd_info_d
TableScan
alias: dm_fact_buyer_prd_info_d
Filter Operator
predicate:
expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426)) and
(UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id) >
UDFToDouble(0))) and (gmv_trade_num > 0))
type: boolean
Filter Operator
predicate:
expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426)) and
(UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id) >
UDFToDouble(0))) and (gmv_trade_num > 0))
type: boolean
Select Operator
expressions:
expr: category_id
type: string
expr: user_id
type: int
outputColumnNames: category_id, user_id
Group By Operator
keys:
expr: category_id
type: string
expr: user_id
type: int
mode: hash
outputColumnNames: _col0, _col1
Reduce Output Operator
key expressions:
expr: _col0
type: string
expr: _col1
type: int
sort order: ++
Map-reduce partition columns:
expr: _col0
type: string
expr: _col1
type: int
tag: -1
Reduce Operator Tree:
Group By Operator
keys:
expr: KEY._col0
type: string
expr: KEY._col1
type: int
mode: mergepartial
outputColumnNames: _col0, _col1
Select Operator
expressions:
expr: _col0
type: string
expr: _col1
type: int
outputColumnNames: _col0, _col1
File Output Operator
compressed: true
GlobalTableId: 0
table:
input format:
org.apache.hadoop.mapred.SequenceFileInputFormat
output format:
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
Stage: Stage-0
Fetch Operator
limit: -1
--------------------------------
Well, I tried disabling predicate push down (set hive.optimize.ppd=true),
the error is gone; I tried disabling map side aggregate, the error is
gone,too.
Anybody knows what the problem is? Please give me some advice.
--
Best Regards,
Ted Xu
Re: BUG at optimizer or map side aggregate?
Posted by Zheng Shao <zs...@gmail.com>.
Nice finding! That's likely to be the cause.
Can you open a JIRA issue on issues.apache.org/jira/browse/HIVE
Zheng
On Wed, May 12, 2010 at 1:05 AM, Ted Xu <te...@gmail.com> wrote:
> Zheng,
> Thank you for your reply.
> Well, it seems hard for me to repreduce this bug in a simpler query.
> However, if I change the alias of subquery 't1' (either the inner one or the
> join result), the bug disappears. I'm wondering if there is possible that
> table aliases of different level will conflict when their alias names are
> the same.
>
> 2010/5/12 Zheng Shao <zs...@gmail.com>
>>
>> Yes that does seem to be a bug.
>>
>> Can you try if you can simply the query while reproducing the bug?
>> That will make it a lot easier to debug and fix.
>>
>>
>> Zheng
>>
>> On Tue, May 11, 2010 at 7:44 PM, Ted Xu <te...@gmail.com> wrote:
>> > Hi all,
>> > I think I found a bug, I'm not sure whether the problem is at optimizer
>> > (PPD) or at map side aggregate.
>> > See query listed below:
>> > -------------------------------------
>> > create table if not exists dm_fact_buyer_prd_info_d (
>> > category_id string
>> > ,gmv_trade_num int
>> > ,user_id int
>> > )
>> > PARTITIONED BY (ds int);
>> > set hive.optimize.ppd=true;
>> > set hive.map.aggr=true;
>> > explain select 20100426, category_id1,category_id2,assoc_idx
>> > from (
>> > select
>> > category_id1
>> > , category_id2
>> > , count(distinct user_id) as assoc_idx
>> > from (
>> > select
>> > t1.category_id as category_id1
>> > , t2.category_id as category_id2
>> > , t1.user_id
>> > from (
>> > select category_id, user_id
>> > from dm_fact_buyer_prd_info_d
>> > where ds <= 20100426
>> > and ds > 20100419
>> > and category_id >0
>> > and gmv_trade_num>0
>> > group by category_id, user_id ) t1
>> > join (
>> > select category_id, user_id
>> > from dm_fact_buyer_prd_info_d
>> > where ds <= 20100426
>> > and ds >20100419
>> > and category_id >0
>> > and gmv_trade_num >0
>> > group by category_id, user_id ) t2 on t1.user_id=t2.user_id
>> > ) t1
>> > group by category_id1, category_id2 ) t_o
>> > where category_id1 <> category_id2
>> > and assoc_idx > 2;
>> > --------------------------------
>> > The query above will fail when execute, throwing exception: "can not
>> > cast
>> > UDFOpNotEqual(Text, IntWritable) to UDFOpNotEqual(Text, Text)".
>> > I explained the query and the execute plan looks really wired (see
>> > the highlighted predicate):
>> > --------------------------------
>> > ABSTRACT SYNTAX TREE:
>> > (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_SUBQUERY
>> > (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM
>> > (TOK_TABREF dm_fact_buyer_prd_info_d)) (TOK_INSERT (TOK_DESTINATION
>> > (TOK_DIR
>> > TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL category_id))
>> > (TOK_SELEXPR (TOK_TABLE_OR_COL user_id))) (TOK_WHERE (and (and (and (<=
>> > (TOK_TABLE_OR_COL ds) 20100426) (> (TOK_TABLE_OR_COL ds) 20100419)) (>
>> > (TOK_TABLE_OR_COL category_id) 0)) (> (TOK_TABLE_OR_COL gmv_trade_num)
>> > 0)))
>> > (TOK_GROUPBY (TOK_TABLE_OR_COL category_id) (TOK_TABLE_OR_COL
>> > user_id))))
>> > t1) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF
>> > dm_fact_buyer_prd_info_d)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR
>> > TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL category_id))
>> > (TOK_SELEXPR (TOK_TABLE_OR_COL user_id))) (TOK_WHERE (and (and (and (<=
>> > (TOK_TABLE_OR_COL ds) 20100426) (> (TOK_TABLE_OR_COL ds) 20100419)) (>
>> > (TOK_TABLE_OR_COL category_id) 0)) (> (TOK_TABLE_OR_COL gmv_trade_num)
>> > 0)))
>> > (TOK_GROUPBY (TOK_TABLE_OR_COL category_id) (TOK_TABLE_OR_COL
>> > user_id))))
>> > t2) (= (. (TOK_TABLE_OR_COL t1) user_id) (. (TOK_TABLE_OR_COL t2)
>> > user_id)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE))
>> > (TOK_SELECT
>> > (TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) category_id) category_id1)
>> > (TOK_SELEXPR (. (TOK_TABLE_OR_COL t2) category_id) category_id2)
>> > (TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) user_id))))) t1)) (TOK_INSERT
>> > (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR
>> > (TOK_TABLE_OR_COL category_id1)) (TOK_SELEXPR (TOK_TABLE_OR_COL
>> > category_id2)) (TOK_SELEXPR (TOK_FUNCTIONDI count (TOK_TABLE_OR_COL
>> > user_id)) assoc_idx)) (TOK_GROUPBY (TOK_TABLE_OR_COL category_id1)
>> > (TOK_TABLE_OR_COL category_id2)))) t_o)) (TOK_INSERT (TOK_DESTINATION
>> > (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR 20100426) (TOK_SELEXPR
>> > (TOK_TABLE_OR_COL category_id1)) (TOK_SELEXPR (TOK_TABLE_OR_COL
>> > category_id2)) (TOK_SELEXPR (TOK_TABLE_OR_COL assoc_idx))) (TOK_WHERE
>> > (and
>> > (<> (TOK_TABLE_OR_COL category_id1) (TOK_TABLE_OR_COL category_id2)) (>
>> > (TOK_TABLE_OR_COL assoc_idx) 2)))))
>> >
>> >
>> > STAGE DEPENDENCIES:
>> > Stage-1 is a root stage
>> > Stage-2 depends on stages: Stage-1, Stage-4
>> > Stage-3 depends on stages: Stage-2
>> > Stage-4 is a root stage
>> > Stage-2 depends on stages: Stage-1, Stage-4
>> > Stage-3 depends on stages: Stage-2
>> > Stage-0 is a root stage
>> >
>> >
>> > STAGE PLANS:
>> > Stage: Stage-1
>> > Map Reduce
>> > Alias -> Map Operator Tree:
>> > t_o:t1:t1:dm_fact_buyer_prd_info_d
>> > TableScan
>> > alias: dm_fact_buyer_prd_info_d
>> > Filter Operator
>> > predicate:
>> > expr: (((((UDFToDouble(ds) <= UDFToDouble(20100426))
>> > and
>> > (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id)
>> > >
>> > UDFToDouble(0))) and (gmv_trade_num > 0)) and (category_id <> user_id))
>> > type: boolean
>> > Filter Operator
>> > predicate:
>> > expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426))
>> > and
>> > (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id)
>> > >
>> > UDFToDouble(0))) and (gmv_trade_num > 0))
>> > type: boolean
>> > Select Operator
>> > expressions:
>> > expr: category_id
>> > type: string
>> > expr: user_id
>> > type: int
>> > outputColumnNames: category_id, user_id
>> > Group By Operator
>> > keys:
>> > expr: category_id
>> > type: string
>> > expr: user_id
>> > type: int
>> > mode: hash
>> > outputColumnNames: _col0, _col1
>> > Reduce Output Operator
>> > key expressions:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: int
>> > sort order: ++
>> > Map-reduce partition columns:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: int
>> > tag: -1
>> > Reduce Operator Tree:
>> > Group By Operator
>> > keys:
>> > expr: KEY._col0
>> > type: string
>> > expr: KEY._col1
>> > type: int
>> > mode: mergepartial
>> > outputColumnNames: _col0, _col1
>> > Select Operator
>> > expressions:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: int
>> > outputColumnNames: _col0, _col1
>> > File Output Operator
>> > compressed: true
>> > GlobalTableId: 0
>> > table:
>> > input format:
>> > org.apache.hadoop.mapred.SequenceFileInputFormat
>> > output format:
>> > org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
>> >
>> >
>> > Stage: Stage-2
>> > Map Reduce
>> > Alias -> Map Operator Tree:
>> > $INTNAME
>> > Reduce Output Operator
>> > key expressions:
>> > expr: _col1
>> > type: int
>> > sort order: +
>> > Map-reduce partition columns:
>> > expr: _col1
>> > type: int
>> > tag: 0
>> > value expressions:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: int
>> > $INTNAME1
>> > Reduce Output Operator
>> > key expressions:
>> > expr: _col1
>> > type: int
>> > sort order: +
>> > Map-reduce partition columns:
>> > expr: _col1
>> > type: int
>> > tag: 1
>> > value expressions:
>> > expr: _col0
>> > type: string
>> > Reduce Operator Tree:
>> > Join Operator
>> > condition map:
>> > Inner Join 0 to 1
>> > condition expressions:
>> > 0 {VALUE._col0} {VALUE._col1}
>> > 1 {VALUE._col0}
>> > outputColumnNames: _col0, _col1, _col2
>> > Select Operator
>> > expressions:
>> > expr: _col0
>> > type: string
>> > expr: _col2
>> > type: string
>> > expr: _col1
>> > type: int
>> > outputColumnNames: _col0, _col1, _col2
>> > Select Operator
>> > expressions:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: string
>> > expr: _col2
>> > type: int
>> > outputColumnNames: _col0, _col1, _col2
>> > Group By Operator
>> > aggregations:
>> > expr: count(DISTINCT _col2)
>> > keys:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: string
>> > expr: _col2
>> > type: int
>> > mode: hash
>> > outputColumnNames: _col0, _col1, _col2, _col3
>> > File Output Operator
>> > compressed: true
>> > GlobalTableId: 0
>> > table:
>> > input format:
>> > org.apache.hadoop.mapred.SequenceFileInputFormat
>> > output format:
>> > org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
>> >
>> >
>> > Stage: Stage-3
>> > Map Reduce
>> > Alias -> Map Operator Tree:
>> > file:/group/tbdev/shaojie/scratch/420686432\10003
>> > Reduce Output Operator
>> > key expressions:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: string
>> > expr: _col2
>> > type: int
>> > sort order: +++
>> > Map-reduce partition columns:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: string
>> > tag: -1
>> > value expressions:
>> > expr: _col3
>> > type: bigint
>> > Reduce Operator Tree:
>> > Group By Operator
>> > aggregations:
>> > expr: count(DISTINCT KEY._col2)
>> > keys:
>> > expr: KEY._col0
>> > type: string
>> > expr: KEY._col1
>> > type: string
>> > mode: mergepartial
>> > outputColumnNames: _col0, _col1, _col2
>> > Select Operator
>> > expressions:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: string
>> > expr: _col2
>> > type: bigint
>> > outputColumnNames: _col0, _col1, _col2
>> > Filter Operator
>> > predicate:
>> > expr: ((_col0 <> _col1) and (UDFToDouble(_col2) >
>> > UDFToDouble(2)))
>> > type: boolean
>> > Select Operator
>> > expressions:
>> > expr: 20100426
>> > type: int
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: string
>> > expr: _col2
>> > type: bigint
>> > outputColumnNames: _col0, _col1, _col2, _col3
>> > File Output Operator
>> > compressed: false
>> > GlobalTableId: 0
>> > table:
>> > input format:
>> > org.apache.hadoop.mapred.TextInputFormat
>> > output format:
>> > org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
>> >
>> >
>> > Stage: Stage-4
>> > Map Reduce
>> > Alias -> Map Operator Tree:
>> > t_o:t1:t2:dm_fact_buyer_prd_info_d
>> > TableScan
>> > alias: dm_fact_buyer_prd_info_d
>> > Filter Operator
>> > predicate:
>> > expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426))
>> > and
>> > (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id)
>> > >
>> > UDFToDouble(0))) and (gmv_trade_num > 0))
>> > type: boolean
>> > Filter Operator
>> > predicate:
>> > expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426))
>> > and
>> > (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id)
>> > >
>> > UDFToDouble(0))) and (gmv_trade_num > 0))
>> > type: boolean
>> > Select Operator
>> > expressions:
>> > expr: category_id
>> > type: string
>> > expr: user_id
>> > type: int
>> > outputColumnNames: category_id, user_id
>> > Group By Operator
>> > keys:
>> > expr: category_id
>> > type: string
>> > expr: user_id
>> > type: int
>> > mode: hash
>> > outputColumnNames: _col0, _col1
>> > Reduce Output Operator
>> > key expressions:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: int
>> > sort order: ++
>> > Map-reduce partition columns:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: int
>> > tag: -1
>> > Reduce Operator Tree:
>> > Group By Operator
>> > keys:
>> > expr: KEY._col0
>> > type: string
>> > expr: KEY._col1
>> > type: int
>> > mode: mergepartial
>> > outputColumnNames: _col0, _col1
>> > Select Operator
>> > expressions:
>> > expr: _col0
>> > type: string
>> > expr: _col1
>> > type: int
>> > outputColumnNames: _col0, _col1
>> > File Output Operator
>> > compressed: true
>> > GlobalTableId: 0
>> > table:
>> > input format:
>> > org.apache.hadoop.mapred.SequenceFileInputFormat
>> > output format:
>> > org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
>> >
>> >
>> > Stage: Stage-0
>> > Fetch Operator
>> > limit: -1
>> > --------------------------------
>> >
>> > Well, I tried disabling predicate push down (set
>> > hive.optimize.ppd=true),
>> > the error is gone; I tried disabling map side aggregate, the error is
>> > gone,too.
>> > Anybody knows what the problem is? Please give me some advice.
>> >
>> > --
>> > Best Regards,
>> > Ted Xu
>> >
>>
>>
>>
>> --
>> Yours,
>> Zheng
>> http://www.linkedin.com/in/zshao
>
>
>
> --
> Best Regards,
> Ted Xu
>
--
Yours,
Zheng
http://www.linkedin.com/in/zshao
Re: BUG at optimizer or map side aggregate?
Posted by Ted Xu <te...@gmail.com>.
Zheng,
Thank you for your reply.
Well, it seems hard for me to repreduce this bug in a simpler query.
However, *if I change the alias of subquery 't1' (either the inner one or
the join result), the bug disappears*. I'm wondering if there is possible
that table aliases of different level will conflict when their alias names
are the same.
2010/5/12 Zheng Shao <zs...@gmail.com>
> Yes that does seem to be a bug.
>
> Can you try if you can simply the query while reproducing the bug?
> That will make it a lot easier to debug and fix.
>
>
> Zheng
>
> On Tue, May 11, 2010 at 7:44 PM, Ted Xu <te...@gmail.com> wrote:
> > Hi all,
> > I think I found a bug, I'm not sure whether the problem is at optimizer
> > (PPD) or at map side aggregate.
> > See query listed below:
> > -------------------------------------
> > create table if not exists dm_fact_buyer_prd_info_d (
> > category_id string
> > ,gmv_trade_num int
> > ,user_id int
> > )
> > PARTITIONED BY (ds int);
> > set hive.optimize.ppd=true;
> > set hive.map.aggr=true;
> > explain select 20100426, category_id1,category_id2,assoc_idx
> > from (
> > select
> > category_id1
> > , category_id2
> > , count(distinct user_id) as assoc_idx
> > from (
> > select
> > t1.category_id as category_id1
> > , t2.category_id as category_id2
> > , t1.user_id
> > from (
> > select category_id, user_id
> > from dm_fact_buyer_prd_info_d
> > where ds <= 20100426
> > and ds > 20100419
> > and category_id >0
> > and gmv_trade_num>0
> > group by category_id, user_id ) t1
> > join (
> > select category_id, user_id
> > from dm_fact_buyer_prd_info_d
> > where ds <= 20100426
> > and ds >20100419
> > and category_id >0
> > and gmv_trade_num >0
> > group by category_id, user_id ) t2 on t1.user_id=t2.user_id
> > ) t1
> > group by category_id1, category_id2 ) t_o
> > where category_id1 <> category_id2
> > and assoc_idx > 2;
> > --------------------------------
> > The query above will fail when execute, throwing exception: "can not cast
> > UDFOpNotEqual(Text, IntWritable) to UDFOpNotEqual(Text, Text)".
> > I explained the query and the execute plan looks really wired (see
> > the highlighted predicate):
> > --------------------------------
> > ABSTRACT SYNTAX TREE:
> > (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_SUBQUERY
> > (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM
> > (TOK_TABREF dm_fact_buyer_prd_info_d)) (TOK_INSERT (TOK_DESTINATION
> (TOK_DIR
> > TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL category_id))
> > (TOK_SELEXPR (TOK_TABLE_OR_COL user_id))) (TOK_WHERE (and (and (and (<=
> > (TOK_TABLE_OR_COL ds) 20100426) (> (TOK_TABLE_OR_COL ds) 20100419)) (>
> > (TOK_TABLE_OR_COL category_id) 0)) (> (TOK_TABLE_OR_COL gmv_trade_num)
> 0)))
> > (TOK_GROUPBY (TOK_TABLE_OR_COL category_id) (TOK_TABLE_OR_COL user_id))))
> > t1) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF
> > dm_fact_buyer_prd_info_d)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR
> > TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL category_id))
> > (TOK_SELEXPR (TOK_TABLE_OR_COL user_id))) (TOK_WHERE (and (and (and (<=
> > (TOK_TABLE_OR_COL ds) 20100426) (> (TOK_TABLE_OR_COL ds) 20100419)) (>
> > (TOK_TABLE_OR_COL category_id) 0)) (> (TOK_TABLE_OR_COL gmv_trade_num)
> 0)))
> > (TOK_GROUPBY (TOK_TABLE_OR_COL category_id) (TOK_TABLE_OR_COL user_id))))
> > t2) (= (. (TOK_TABLE_OR_COL t1) user_id) (. (TOK_TABLE_OR_COL t2)
> > user_id)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE))
> (TOK_SELECT
> > (TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) category_id) category_id1)
> > (TOK_SELEXPR (. (TOK_TABLE_OR_COL t2) category_id) category_id2)
> > (TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) user_id))))) t1)) (TOK_INSERT
> > (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR
> > (TOK_TABLE_OR_COL category_id1)) (TOK_SELEXPR (TOK_TABLE_OR_COL
> > category_id2)) (TOK_SELEXPR (TOK_FUNCTIONDI count (TOK_TABLE_OR_COL
> > user_id)) assoc_idx)) (TOK_GROUPBY (TOK_TABLE_OR_COL category_id1)
> > (TOK_TABLE_OR_COL category_id2)))) t_o)) (TOK_INSERT (TOK_DESTINATION
> > (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR 20100426) (TOK_SELEXPR
> > (TOK_TABLE_OR_COL category_id1)) (TOK_SELEXPR (TOK_TABLE_OR_COL
> > category_id2)) (TOK_SELEXPR (TOK_TABLE_OR_COL assoc_idx))) (TOK_WHERE
> (and
> > (<> (TOK_TABLE_OR_COL category_id1) (TOK_TABLE_OR_COL category_id2)) (>
> > (TOK_TABLE_OR_COL assoc_idx) 2)))))
> >
> >
> > STAGE DEPENDENCIES:
> > Stage-1 is a root stage
> > Stage-2 depends on stages: Stage-1, Stage-4
> > Stage-3 depends on stages: Stage-2
> > Stage-4 is a root stage
> > Stage-2 depends on stages: Stage-1, Stage-4
> > Stage-3 depends on stages: Stage-2
> > Stage-0 is a root stage
> >
> >
> > STAGE PLANS:
> > Stage: Stage-1
> > Map Reduce
> > Alias -> Map Operator Tree:
> > t_o:t1:t1:dm_fact_buyer_prd_info_d
> > TableScan
> > alias: dm_fact_buyer_prd_info_d
> > Filter Operator
> > predicate:
> > expr: (((((UDFToDouble(ds) <= UDFToDouble(20100426))
> and
> > (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id)
> >
> > UDFToDouble(0))) and (gmv_trade_num > 0)) and (category_id <> user_id))
> > type: boolean
> > Filter Operator
> > predicate:
> > expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426))
> and
> > (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id)
> >
> > UDFToDouble(0))) and (gmv_trade_num > 0))
> > type: boolean
> > Select Operator
> > expressions:
> > expr: category_id
> > type: string
> > expr: user_id
> > type: int
> > outputColumnNames: category_id, user_id
> > Group By Operator
> > keys:
> > expr: category_id
> > type: string
> > expr: user_id
> > type: int
> > mode: hash
> > outputColumnNames: _col0, _col1
> > Reduce Output Operator
> > key expressions:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: int
> > sort order: ++
> > Map-reduce partition columns:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: int
> > tag: -1
> > Reduce Operator Tree:
> > Group By Operator
> > keys:
> > expr: KEY._col0
> > type: string
> > expr: KEY._col1
> > type: int
> > mode: mergepartial
> > outputColumnNames: _col0, _col1
> > Select Operator
> > expressions:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: int
> > outputColumnNames: _col0, _col1
> > File Output Operator
> > compressed: true
> > GlobalTableId: 0
> > table:
> > input format:
> > org.apache.hadoop.mapred.SequenceFileInputFormat
> > output format:
> > org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
> >
> >
> > Stage: Stage-2
> > Map Reduce
> > Alias -> Map Operator Tree:
> > $INTNAME
> > Reduce Output Operator
> > key expressions:
> > expr: _col1
> > type: int
> > sort order: +
> > Map-reduce partition columns:
> > expr: _col1
> > type: int
> > tag: 0
> > value expressions:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: int
> > $INTNAME1
> > Reduce Output Operator
> > key expressions:
> > expr: _col1
> > type: int
> > sort order: +
> > Map-reduce partition columns:
> > expr: _col1
> > type: int
> > tag: 1
> > value expressions:
> > expr: _col0
> > type: string
> > Reduce Operator Tree:
> > Join Operator
> > condition map:
> > Inner Join 0 to 1
> > condition expressions:
> > 0 {VALUE._col0} {VALUE._col1}
> > 1 {VALUE._col0}
> > outputColumnNames: _col0, _col1, _col2
> > Select Operator
> > expressions:
> > expr: _col0
> > type: string
> > expr: _col2
> > type: string
> > expr: _col1
> > type: int
> > outputColumnNames: _col0, _col1, _col2
> > Select Operator
> > expressions:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: string
> > expr: _col2
> > type: int
> > outputColumnNames: _col0, _col1, _col2
> > Group By Operator
> > aggregations:
> > expr: count(DISTINCT _col2)
> > keys:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: string
> > expr: _col2
> > type: int
> > mode: hash
> > outputColumnNames: _col0, _col1, _col2, _col3
> > File Output Operator
> > compressed: true
> > GlobalTableId: 0
> > table:
> > input format:
> > org.apache.hadoop.mapred.SequenceFileInputFormat
> > output format:
> > org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
> >
> >
> > Stage: Stage-3
> > Map Reduce
> > Alias -> Map Operator Tree:
> > file:/group/tbdev/shaojie/scratch/420686432\10003
> > Reduce Output Operator
> > key expressions:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: string
> > expr: _col2
> > type: int
> > sort order: +++
> > Map-reduce partition columns:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: string
> > tag: -1
> > value expressions:
> > expr: _col3
> > type: bigint
> > Reduce Operator Tree:
> > Group By Operator
> > aggregations:
> > expr: count(DISTINCT KEY._col2)
> > keys:
> > expr: KEY._col0
> > type: string
> > expr: KEY._col1
> > type: string
> > mode: mergepartial
> > outputColumnNames: _col0, _col1, _col2
> > Select Operator
> > expressions:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: string
> > expr: _col2
> > type: bigint
> > outputColumnNames: _col0, _col1, _col2
> > Filter Operator
> > predicate:
> > expr: ((_col0 <> _col1) and (UDFToDouble(_col2) >
> > UDFToDouble(2)))
> > type: boolean
> > Select Operator
> > expressions:
> > expr: 20100426
> > type: int
> > expr: _col0
> > type: string
> > expr: _col1
> > type: string
> > expr: _col2
> > type: bigint
> > outputColumnNames: _col0, _col1, _col2, _col3
> > File Output Operator
> > compressed: false
> > GlobalTableId: 0
> > table:
> > input format:
> org.apache.hadoop.mapred.TextInputFormat
> > output format:
> > org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
> >
> >
> > Stage: Stage-4
> > Map Reduce
> > Alias -> Map Operator Tree:
> > t_o:t1:t2:dm_fact_buyer_prd_info_d
> > TableScan
> > alias: dm_fact_buyer_prd_info_d
> > Filter Operator
> > predicate:
> > expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426)) and
> > (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id)
> >
> > UDFToDouble(0))) and (gmv_trade_num > 0))
> > type: boolean
> > Filter Operator
> > predicate:
> > expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426))
> and
> > (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id)
> >
> > UDFToDouble(0))) and (gmv_trade_num > 0))
> > type: boolean
> > Select Operator
> > expressions:
> > expr: category_id
> > type: string
> > expr: user_id
> > type: int
> > outputColumnNames: category_id, user_id
> > Group By Operator
> > keys:
> > expr: category_id
> > type: string
> > expr: user_id
> > type: int
> > mode: hash
> > outputColumnNames: _col0, _col1
> > Reduce Output Operator
> > key expressions:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: int
> > sort order: ++
> > Map-reduce partition columns:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: int
> > tag: -1
> > Reduce Operator Tree:
> > Group By Operator
> > keys:
> > expr: KEY._col0
> > type: string
> > expr: KEY._col1
> > type: int
> > mode: mergepartial
> > outputColumnNames: _col0, _col1
> > Select Operator
> > expressions:
> > expr: _col0
> > type: string
> > expr: _col1
> > type: int
> > outputColumnNames: _col0, _col1
> > File Output Operator
> > compressed: true
> > GlobalTableId: 0
> > table:
> > input format:
> > org.apache.hadoop.mapred.SequenceFileInputFormat
> > output format:
> > org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
> >
> >
> > Stage: Stage-0
> > Fetch Operator
> > limit: -1
> > --------------------------------
> >
> > Well, I tried disabling predicate push down (set hive.optimize.ppd=true),
> > the error is gone; I tried disabling map side aggregate, the error is
> > gone,too.
> > Anybody knows what the problem is? Please give me some advice.
> >
> > --
> > Best Regards,
> > Ted Xu
> >
>
>
>
> --
> Yours,
> Zheng
> http://www.linkedin.com/in/zshao
>
--
Best Regards,
Ted Xu
Re: BUG at optimizer or map side aggregate?
Posted by Zheng Shao <zs...@gmail.com>.
Yes that does seem to be a bug.
Can you try if you can simply the query while reproducing the bug?
That will make it a lot easier to debug and fix.
Zheng
On Tue, May 11, 2010 at 7:44 PM, Ted Xu <te...@gmail.com> wrote:
> Hi all,
> I think I found a bug, I'm not sure whether the problem is at optimizer
> (PPD) or at map side aggregate.
> See query listed below:
> -------------------------------------
> create table if not exists dm_fact_buyer_prd_info_d (
> category_id string
> ,gmv_trade_num int
> ,user_id int
> )
> PARTITIONED BY (ds int);
> set hive.optimize.ppd=true;
> set hive.map.aggr=true;
> explain select 20100426, category_id1,category_id2,assoc_idx
> from (
> select
> category_id1
> , category_id2
> , count(distinct user_id) as assoc_idx
> from (
> select
> t1.category_id as category_id1
> , t2.category_id as category_id2
> , t1.user_id
> from (
> select category_id, user_id
> from dm_fact_buyer_prd_info_d
> where ds <= 20100426
> and ds > 20100419
> and category_id >0
> and gmv_trade_num>0
> group by category_id, user_id ) t1
> join (
> select category_id, user_id
> from dm_fact_buyer_prd_info_d
> where ds <= 20100426
> and ds >20100419
> and category_id >0
> and gmv_trade_num >0
> group by category_id, user_id ) t2 on t1.user_id=t2.user_id
> ) t1
> group by category_id1, category_id2 ) t_o
> where category_id1 <> category_id2
> and assoc_idx > 2;
> --------------------------------
> The query above will fail when execute, throwing exception: "can not cast
> UDFOpNotEqual(Text, IntWritable) to UDFOpNotEqual(Text, Text)".
> I explained the query and the execute plan looks really wired (see
> the highlighted predicate):
> --------------------------------
> ABSTRACT SYNTAX TREE:
> (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_SUBQUERY
> (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM
> (TOK_TABREF dm_fact_buyer_prd_info_d)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR
> TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL category_id))
> (TOK_SELEXPR (TOK_TABLE_OR_COL user_id))) (TOK_WHERE (and (and (and (<=
> (TOK_TABLE_OR_COL ds) 20100426) (> (TOK_TABLE_OR_COL ds) 20100419)) (>
> (TOK_TABLE_OR_COL category_id) 0)) (> (TOK_TABLE_OR_COL gmv_trade_num) 0)))
> (TOK_GROUPBY (TOK_TABLE_OR_COL category_id) (TOK_TABLE_OR_COL user_id))))
> t1) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF
> dm_fact_buyer_prd_info_d)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR
> TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL category_id))
> (TOK_SELEXPR (TOK_TABLE_OR_COL user_id))) (TOK_WHERE (and (and (and (<=
> (TOK_TABLE_OR_COL ds) 20100426) (> (TOK_TABLE_OR_COL ds) 20100419)) (>
> (TOK_TABLE_OR_COL category_id) 0)) (> (TOK_TABLE_OR_COL gmv_trade_num) 0)))
> (TOK_GROUPBY (TOK_TABLE_OR_COL category_id) (TOK_TABLE_OR_COL user_id))))
> t2) (= (. (TOK_TABLE_OR_COL t1) user_id) (. (TOK_TABLE_OR_COL t2)
> user_id)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT
> (TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) category_id) category_id1)
> (TOK_SELEXPR (. (TOK_TABLE_OR_COL t2) category_id) category_id2)
> (TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) user_id))))) t1)) (TOK_INSERT
> (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR
> (TOK_TABLE_OR_COL category_id1)) (TOK_SELEXPR (TOK_TABLE_OR_COL
> category_id2)) (TOK_SELEXPR (TOK_FUNCTIONDI count (TOK_TABLE_OR_COL
> user_id)) assoc_idx)) (TOK_GROUPBY (TOK_TABLE_OR_COL category_id1)
> (TOK_TABLE_OR_COL category_id2)))) t_o)) (TOK_INSERT (TOK_DESTINATION
> (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR 20100426) (TOK_SELEXPR
> (TOK_TABLE_OR_COL category_id1)) (TOK_SELEXPR (TOK_TABLE_OR_COL
> category_id2)) (TOK_SELEXPR (TOK_TABLE_OR_COL assoc_idx))) (TOK_WHERE (and
> (<> (TOK_TABLE_OR_COL category_id1) (TOK_TABLE_OR_COL category_id2)) (>
> (TOK_TABLE_OR_COL assoc_idx) 2)))))
>
>
> STAGE DEPENDENCIES:
> Stage-1 is a root stage
> Stage-2 depends on stages: Stage-1, Stage-4
> Stage-3 depends on stages: Stage-2
> Stage-4 is a root stage
> Stage-2 depends on stages: Stage-1, Stage-4
> Stage-3 depends on stages: Stage-2
> Stage-0 is a root stage
>
>
> STAGE PLANS:
> Stage: Stage-1
> Map Reduce
> Alias -> Map Operator Tree:
> t_o:t1:t1:dm_fact_buyer_prd_info_d
> TableScan
> alias: dm_fact_buyer_prd_info_d
> Filter Operator
> predicate:
> expr: (((((UDFToDouble(ds) <= UDFToDouble(20100426)) and
> (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id) >
> UDFToDouble(0))) and (gmv_trade_num > 0)) and (category_id <> user_id))
> type: boolean
> Filter Operator
> predicate:
> expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426)) and
> (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id) >
> UDFToDouble(0))) and (gmv_trade_num > 0))
> type: boolean
> Select Operator
> expressions:
> expr: category_id
> type: string
> expr: user_id
> type: int
> outputColumnNames: category_id, user_id
> Group By Operator
> keys:
> expr: category_id
> type: string
> expr: user_id
> type: int
> mode: hash
> outputColumnNames: _col0, _col1
> Reduce Output Operator
> key expressions:
> expr: _col0
> type: string
> expr: _col1
> type: int
> sort order: ++
> Map-reduce partition columns:
> expr: _col0
> type: string
> expr: _col1
> type: int
> tag: -1
> Reduce Operator Tree:
> Group By Operator
> keys:
> expr: KEY._col0
> type: string
> expr: KEY._col1
> type: int
> mode: mergepartial
> outputColumnNames: _col0, _col1
> Select Operator
> expressions:
> expr: _col0
> type: string
> expr: _col1
> type: int
> outputColumnNames: _col0, _col1
> File Output Operator
> compressed: true
> GlobalTableId: 0
> table:
> input format:
> org.apache.hadoop.mapred.SequenceFileInputFormat
> output format:
> org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
>
>
> Stage: Stage-2
> Map Reduce
> Alias -> Map Operator Tree:
> $INTNAME
> Reduce Output Operator
> key expressions:
> expr: _col1
> type: int
> sort order: +
> Map-reduce partition columns:
> expr: _col1
> type: int
> tag: 0
> value expressions:
> expr: _col0
> type: string
> expr: _col1
> type: int
> $INTNAME1
> Reduce Output Operator
> key expressions:
> expr: _col1
> type: int
> sort order: +
> Map-reduce partition columns:
> expr: _col1
> type: int
> tag: 1
> value expressions:
> expr: _col0
> type: string
> Reduce Operator Tree:
> Join Operator
> condition map:
> Inner Join 0 to 1
> condition expressions:
> 0 {VALUE._col0} {VALUE._col1}
> 1 {VALUE._col0}
> outputColumnNames: _col0, _col1, _col2
> Select Operator
> expressions:
> expr: _col0
> type: string
> expr: _col2
> type: string
> expr: _col1
> type: int
> outputColumnNames: _col0, _col1, _col2
> Select Operator
> expressions:
> expr: _col0
> type: string
> expr: _col1
> type: string
> expr: _col2
> type: int
> outputColumnNames: _col0, _col1, _col2
> Group By Operator
> aggregations:
> expr: count(DISTINCT _col2)
> keys:
> expr: _col0
> type: string
> expr: _col1
> type: string
> expr: _col2
> type: int
> mode: hash
> outputColumnNames: _col0, _col1, _col2, _col3
> File Output Operator
> compressed: true
> GlobalTableId: 0
> table:
> input format:
> org.apache.hadoop.mapred.SequenceFileInputFormat
> output format:
> org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
>
>
> Stage: Stage-3
> Map Reduce
> Alias -> Map Operator Tree:
> file:/group/tbdev/shaojie/scratch/420686432\10003
> Reduce Output Operator
> key expressions:
> expr: _col0
> type: string
> expr: _col1
> type: string
> expr: _col2
> type: int
> sort order: +++
> Map-reduce partition columns:
> expr: _col0
> type: string
> expr: _col1
> type: string
> tag: -1
> value expressions:
> expr: _col3
> type: bigint
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: count(DISTINCT KEY._col2)
> keys:
> expr: KEY._col0
> type: string
> expr: KEY._col1
> type: string
> mode: mergepartial
> outputColumnNames: _col0, _col1, _col2
> Select Operator
> expressions:
> expr: _col0
> type: string
> expr: _col1
> type: string
> expr: _col2
> type: bigint
> outputColumnNames: _col0, _col1, _col2
> Filter Operator
> predicate:
> expr: ((_col0 <> _col1) and (UDFToDouble(_col2) >
> UDFToDouble(2)))
> type: boolean
> Select Operator
> expressions:
> expr: 20100426
> type: int
> expr: _col0
> type: string
> expr: _col1
> type: string
> expr: _col2
> type: bigint
> outputColumnNames: _col0, _col1, _col2, _col3
> File Output Operator
> compressed: false
> GlobalTableId: 0
> table:
> input format: org.apache.hadoop.mapred.TextInputFormat
> output format:
> org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
>
>
> Stage: Stage-4
> Map Reduce
> Alias -> Map Operator Tree:
> t_o:t1:t2:dm_fact_buyer_prd_info_d
> TableScan
> alias: dm_fact_buyer_prd_info_d
> Filter Operator
> predicate:
> expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426)) and
> (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id) >
> UDFToDouble(0))) and (gmv_trade_num > 0))
> type: boolean
> Filter Operator
> predicate:
> expr: ((((UDFToDouble(ds) <= UDFToDouble(20100426)) and
> (UDFToDouble(ds) > UDFToDouble(20100419))) and (UDFToDouble(category_id) >
> UDFToDouble(0))) and (gmv_trade_num > 0))
> type: boolean
> Select Operator
> expressions:
> expr: category_id
> type: string
> expr: user_id
> type: int
> outputColumnNames: category_id, user_id
> Group By Operator
> keys:
> expr: category_id
> type: string
> expr: user_id
> type: int
> mode: hash
> outputColumnNames: _col0, _col1
> Reduce Output Operator
> key expressions:
> expr: _col0
> type: string
> expr: _col1
> type: int
> sort order: ++
> Map-reduce partition columns:
> expr: _col0
> type: string
> expr: _col1
> type: int
> tag: -1
> Reduce Operator Tree:
> Group By Operator
> keys:
> expr: KEY._col0
> type: string
> expr: KEY._col1
> type: int
> mode: mergepartial
> outputColumnNames: _col0, _col1
> Select Operator
> expressions:
> expr: _col0
> type: string
> expr: _col1
> type: int
> outputColumnNames: _col0, _col1
> File Output Operator
> compressed: true
> GlobalTableId: 0
> table:
> input format:
> org.apache.hadoop.mapred.SequenceFileInputFormat
> output format:
> org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
>
>
> Stage: Stage-0
> Fetch Operator
> limit: -1
> --------------------------------
>
> Well, I tried disabling predicate push down (set hive.optimize.ppd=true),
> the error is gone; I tried disabling map side aggregate, the error is
> gone,too.
> Anybody knows what the problem is? Please give me some advice.
>
> --
> Best Regards,
> Ted Xu
>
--
Yours,
Zheng
http://www.linkedin.com/in/zshao