配置单元排序合并桶映射（SMB映射）连接

Question

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;

**** 
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

in the execution plan, I am seeing

 **** 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

谢谢！

Answer 1

在已排序表中插入数据时使用sort by子句

或

set hive.enforce.sorting=true  

在将数据插入排序表之前