Support Questions

Find answers, ask questions, and share your expertise

Why hive query with hint cannot be converted into map only join??

avatar
Expert Contributor

select t1.student_id, t2.course_name from table_one t1 join table_two t2 on (t1.course_id=t2.course_id);

The above one is the basic query i am dealing with.Where t1 and t2 are less in size 160 bytes and 50 bytes. When i run the above query ,Map only join is running as usual as "hive.auto.convert.join=true" by default in 0.14. Now,

I made "set hive.auto.convert.join=false;".I run the query .Then query chooses "Default or map reduce join " as i taught.

Now i changed query like

select /*+ MAPJOIN(t2) */ t1.student_id,t2.course_name from table_one t1 join table_two t2 on (t1.course_id=t2.course_id);

i.e I am giving hint through query.But In this case also,query chooses default join only.So i have 2 queries(still auto conversion set false)

1) How to give hint through query,so that query should select map only join.(when auto conversion set false).

2)What is the configuration parameter to know size where tables can fit it.So that map only join run. or How to know the size where tables can fit with in to run map only join.

1 ACCEPTED SOLUTION

avatar

@Suresh Bonam

If you want to use hint, you have to set paremeter below as false (default is true):

set hive.ignore.mapjoin.hint=false;

View solution in original post

4 REPLIES 4

avatar
Expert Contributor

Yupp...i got the solution .

Map join hint will no longer be valid for some queries. Drop the hint in those cases. Hive will automatically try to convert join to map-join with config hive.auto.convert.join set to true.The only reason for the user to specify a mapjoin currently is if they want

it to be converted to a bucketed-mapjoin or a sort-merge bucketed mapjoin. Eventually, that should also go away, but that may take some time to stabilize.Me myself find out the solution.

https://issues.apache.org/jira/browse/HIVE-3784

avatar

@Suresh Bonam

If you want to use hint, you have to set paremeter below as false (default is true):

set hive.ignore.mapjoin.hint=false;

avatar
Expert Contributor

@Guilherme Braccialli yupp...its working.

avatar
New Contributor

If you want to use hint, you have to set paremeter below as false (default is true):

set hive.ignore.mapjoin.hint=false;