Hi,
Sorry for being late on this. Yes, I am on MR only.
If i run simple sql. it creates common join. I see only 25 mappers only as it creates 25 splits.
while if i give sort merge bucket conditions like below. I see 1000 mappers (number of splits:1000). 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; I have also checked explain plan, it says "Sorted Merge Bucket Map Join Operator". Here are steps: As both the tables were having join on multiple columns. I concatenated those columns into one column keycol and created bucket/join key on that column. CREATE TABLE employee(
keycol string,
src_plfm_id int,
emp_id int,
birth_day string,
age int,
addr1 string,
addr2 string,
addr3 string,
phn_no int,
load_dt string)
PARTITIONED BY (
clnt_id string)
clustered by (keycol) sorted by (keycol asc) into 20 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\u0001'
LINES TERMINATED BY '\n'
STORED AS RCFILE;
(it is textfile format table)
from employee_text
insert overwrite table employee partition (clnt_id)
select concat(src_plfm_id,load_dt,emp_id,birth_day),
src_plfm_id,
emp_id,
birth_day,
age,
addr1,
addr2,
addr3,
phn_no,
load_dt
sort by concat(src_plfm_id,load_dt,emp_id,birth_day);
CREATE TABLE employee_error(
keycol string,
src_plfm_id int,
emp_id int,
birth_day string,
error_done string,
load_dt string)
PARTITIONED BY (
clnt_id string)
clustered by (keycol) sorted by (keycol asc) into 20 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\u0001'
LINES TERMINATED BY '\n'
STORED AS RCFILE;
from employee_error_text
insert overwrite table employee partition (clnt_id)
select concat(src_plfm_id,load_dt,emp_id,birth_day),
src_plfm_id,
emp_id,
birth_day,
error_done,
load_dt
sort by concat(src_plfm_id,load_dt,emp_id,birth_day);
create temporary table emp_error_temp
as select
a.src_plfm_id,
a.emp_id,
a.birth_day,
a.age,
a.addr1,
a.addr2,
a.addr3,
a.phn_no,
a.load_dt,
b.error_done
from employee a JOIN
employee_error b
on (a.keycol = b.keycol); This query is creating 50*20=1000 buckets. why?
... View more
