...
No Format |
---|
set hive.auto.convert.join=true; select count(*) from store_sales join time_dim on (ss_sold_time_sk = t_time_sk) |
The default value for hive.auto.convert.join was false in Hive 0.10.0. Hive 0.11.0 changed the default to true (HIVE-3297). Note that hive-default.xml.template incorrectly gives the default as false in Hive 0.11.0 through 0.13.1.
...
- Merge M*-MR patterns into a single MR.
- Merge MJ->MJ into a single MJ when possible.
- Merge MJ* patterns into a single Map stage as a chain of MJ operators. (Not yet implemented.)
If hive.auto.convert.join
is set to true the optimizer not only converts joins to mapjoins but also merges MJ* patterns as much as possible.
...
No Format |
---|
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;
|
There is an option to set the big table selection policy using the following configuration:
...
The names describe their uses. This is especially useful for the fact-fact join (query 82 in the TPC DS benchmark).
SMB Join across
...
Tables with
...
Different Keys
If the tables have differing number of keys, for example Table A has two 2 SORT columns and Table B has 1 SORT column, then you might get an index out of bounds exception.
The following query results in an index out of bounds exception because emp_person let us say for example has one 1 sort column while emp_pay_history has 2 sort columns.
...
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT p.*, py.*
FROM emp_pay_history py INNER JOIN emp_person p
ON p.empid = py.empid
WHERE p.etl_process_run_id |
Generate Hash Tables on the Task Side
...