I ran the query with and without SMB join and got different results. Please help explain.
SET hive.enforce.bucketing=true;
create table dbaproceduresbuckets (
owner string ,
object_name string ,
procedure_name string ,
object_id double ,
subprogram_id double ,
overload string ,
object_type string ,
aggregate string ,
pipelined string ,
impltypeowner string ,
impltypename string ,
parallel string ,
interface string ,
deterministic string ,
authid string )
CLUSTERED BY (object_id) SORTED BY (OBJECT_ID ASC) INTO 32 BUCKETS;
CREATE TABLE dbaobjectsbuckets1(
owner string,
object_name string,
subobject_name string,
object_id double,
data_object_id double,
object_type string,
created string,
last_ddl_time string,
timestamp string,
status string,
temporary string,
generated string,
secondary string,
namespace double,
edition_name string) CLUSTERED BY (object_id) SORTED BY (OBJECT_ID ASC) INTO 32 BUCKETS;
**** load the table;
0: jdbc:hive2://xxxxxx:10000> select count(*) from dbaobjectsbuckets1 a, dbaproceduresbuckets b 0: jdbc:hive2://xxxxxxxx:10000> where a.object_id = b.object_id; INFO : Hadoop job information for Stage-2: number of mappers: 3; number of reducers: 1 INFO : 2016-06-13 15:56:00,381 Stage-2 map = 0%, reduce = 0% INFO : 2016-06-13 15:56:55,818 Stage-2 map = 1%, reduce = 0%, Cumulative CPU 122.6 sec INFO : 2016-06-13 15:57:47,124 Stage-2 map = 7%, reduce = 0%, Cumulative CPU 326.86 sec .......... INFO : 2016-06-13 16:05:01,246 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 867.1 sec INFO : MapReduce Total cumulative CPU time: 14 minutes 27 seconds 100 msec INFO : Ended Job = job_1464280256859_0146 +--------+--+ | _c0 | +--------+--+ | 54876 | +--------+--+
****
set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
0: jdbc:hive2://xxxxxxx:10000> select count(*) from dbaobjectsbuckets1 a, dbaproceduresbuckets b
0: jdbc:hive2://xxxxxxxx:10000> where a.object_id = b.object_id;
in the execution plan, I am seeing
| Sorted Merge Bucket Map Join Operator | | condition map: | | Inner Join 0 to 1 | | keys: | | 0 object_id (type: double) | | 1 object_id (type: double)
**** but the result is showing
INFO : Hadoop job information for Stage-1: number of mappers: 32; number of reducers: 1
......
INFO : MapReduce Total cumulative CPU time: 4 minutes 8 seconds 490 msec
INFO : Ended Job = job_1464280256859_0150 +------+--+ | _c0 | +------+--+ | 2 | +------+--+
????? My question is why it only got 2 when I used SMB join?????? It is supposed to be 54876.
Thanks!
