Styjun

Why does sound not move though a wall?

The number of days until the end of the month

IP addresses from public IP block in my LAN

What does 'made on' mean here?

What does this wavy downward arrow preceding a piano chord mean?

Appropriate certificate to ask for a fibre installation (ANSI/TIA-568.3-D?)

Should I mention being denied entry to UK due to a confusion in my Visa and Ticket bookings?

Target/total memory is higher than max_server_memory

PWM 1Hz on solid state relay

Can my company stop me from working overtime?

Uniform boundedness of the number of number fields having fixed discriminant

How does this change to the opportunity attack rule impact combat?

I have a unique character that I'm having a problem writing. He's a virus!

A factorization game

Find the cheapest shipping option based on item weight

Is there an official reason for not adding a post-credits scene?

Why did Thanos need his ship to help him in the battle scene?

Upside-Down Pyramid Addition...REVERSED!

Why does this derived table improve performance?

How to increase the size of the cursor in Lubuntu 19.04?

I need a disease

Chapter style minimal design

Did we get closer to another plane than we were supposed to, or was the pilot just protecting our delicate sensibilities?

Is the set of non invertible matrices simply connected? What are their homotopy and homology groups?

How to cache the left most table in memory for a left outer join in hive

INNER JOIN vs LEFT JOIN performance in SQL ServerRIGHT/LEFT OUTER JOIN perform differently in HIVE?Hive: Left outer join with rlike conditionNon equi Left outer join in hive workaroundhive left outer join long runningUnable to increase hive dynamic partitions in spark using spark-sqlIssue with Left Outer Join in HiveHive - OR condition with left outer joinMultiple left outer joins on HiveHive Map-Join configuration mistery

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

I have a large table (1Tb of data) that needs to be joined with a smaller table (100k records)

SELECT st.id
FROM small_table st
LEFT JOIN large_table lt
 ON st.id = lt.id

In the above scenario, I am not able to control which table has to be cached into memory.I have tried using MAPJOIN , STREAMTABLE hints and also tried with parameters like conditional task size , small tbale size etc. Since the small table is on the left most side of the join, its not being cached into memory

Is there way to control the table that needs to be cached

Note: I cannot alter the table positions or the code:

Neither here...

...nor here...

Parameters used:

set hive.execution.engine=tez; 
set hive.tez.container.size=4096; 
set hive.merge.mapredfiles=true; 
set tez.shuffle-vertex-manager.min-src-fraction=0.25; 
set tez.shuffle-vertex-manager.max-src-fraction=0.75; 
set hive.exec.dynamic.partition.mode=nonstrict; 
set tez.am.resource.memory.mb=3200 ; 
set tez.am.java.opts=-server -Xmx3200m -Djava.net.preferIPv4Stack=true -XX:+UseNUMA -XX:+UseParallelGC -XX:+UseConcMarkSweepGC ; 
SET hive.auto.convert.join=true; 
set hive.auto.convert.join.noconditionaltask.size=288435456;

performance hive mapjoin

share|improve this question

edited Mar 24 at 8:00

leftjoin

10.9k32356

asked Mar 22 at 23:10

PK25PK25

62

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If I change the postiions of the table ,m then a map join is enabled SELECT st.id FROM large_table lt LEFT JOIN small_table st ON st.id = lt.id
  
  – PK25
  Mar 22 at 23:16
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please add also parameters used
  
  – leftjoin
  Mar 23 at 8:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  set hive.execution.engine=tez; set hive.tez.container.size=4096; set hive.merge.mapredfiles=true; set tez.shuffle-vertex-manager.min-src-fraction=0.25; set tez.shuffle-vertex-manager.max-src-fraction=0.75; set hive.exec.dynamic.partition.mode=nonstrict; set tez.am.resource.memory.mb=3200 ; set tez.am.java.opts=-server -Xmx3200m -Djava.net.preferIPv4Stack=true -XX:+UseNUMA -XX:+UseParallelGC -XX:+UseConcMarkSweepGC ; SET hive.auto.convert.join=true; set hive.auto.convert.join.noconditionaltask.size=288435456;
  
  – PK25
  Mar 24 at 2:38
  

  
  
  
  

add a comment | 

1

I have a large table (1Tb of data) that needs to be joined with a smaller table (100k records)

SELECT st.id
FROM small_table st
LEFT JOIN large_table lt
 ON st.id = lt.id

In the above scenario, I am not able to control which table has to be cached into memory.I have tried using MAPJOIN , STREAMTABLE hints and also tried with parameters like conditional task size , small tbale size etc. Since the small table is on the left most side of the join, its not being cached into memory

Is there way to control the table that needs to be cached

Note: I cannot alter the table positions or the code:

Neither here...

...nor here...

Parameters used:

set hive.execution.engine=tez; 
set hive.tez.container.size=4096; 
set hive.merge.mapredfiles=true; 
set tez.shuffle-vertex-manager.min-src-fraction=0.25; 
set tez.shuffle-vertex-manager.max-src-fraction=0.75; 
set hive.exec.dynamic.partition.mode=nonstrict; 
set tez.am.resource.memory.mb=3200 ; 
set tez.am.java.opts=-server -Xmx3200m -Djava.net.preferIPv4Stack=true -XX:+UseNUMA -XX:+UseParallelGC -XX:+UseConcMarkSweepGC ; 
SET hive.auto.convert.join=true; 
set hive.auto.convert.join.noconditionaltask.size=288435456;

performance hive mapjoin

share|improve this question

edited Mar 24 at 8:00

leftjoin

10.9k32356

asked Mar 22 at 23:10

PK25PK25

62

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If I change the postiions of the table ,m then a map join is enabled SELECT st.id FROM large_table lt LEFT JOIN small_table st ON st.id = lt.id
  
  – PK25
  Mar 22 at 23:16
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please add also parameters used
  
  – leftjoin
  Mar 23 at 8:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  set hive.execution.engine=tez; set hive.tez.container.size=4096; set hive.merge.mapredfiles=true; set tez.shuffle-vertex-manager.min-src-fraction=0.25; set tez.shuffle-vertex-manager.max-src-fraction=0.75; set hive.exec.dynamic.partition.mode=nonstrict; set tez.am.resource.memory.mb=3200 ; set tez.am.java.opts=-server -Xmx3200m -Djava.net.preferIPv4Stack=true -XX:+UseNUMA -XX:+UseParallelGC -XX:+UseConcMarkSweepGC ; SET hive.auto.convert.join=true; set hive.auto.convert.join.noconditionaltask.size=288435456;
  
  – PK25
  Mar 24 at 2:38
  

  
  
  
  

add a comment | 

I have a large table (1Tb of data) that needs to be joined with a smaller table (100k records)

SELECT st.id
FROM small_table st
LEFT JOIN large_table lt
 ON st.id = lt.id

In the above scenario, I am not able to control which table has to be cached into memory.I have tried using MAPJOIN , STREAMTABLE hints and also tried with parameters like conditional task size , small tbale size etc. Since the small table is on the left most side of the join, its not being cached into memory

Is there way to control the table that needs to be cached

Note: I cannot alter the table positions or the code:

Neither here...

...nor here...

Parameters used:

set hive.execution.engine=tez; 
set hive.tez.container.size=4096; 
set hive.merge.mapredfiles=true; 
set tez.shuffle-vertex-manager.min-src-fraction=0.25; 
set tez.shuffle-vertex-manager.max-src-fraction=0.75; 
set hive.exec.dynamic.partition.mode=nonstrict; 
set tez.am.resource.memory.mb=3200 ; 
set tez.am.java.opts=-server -Xmx3200m -Djava.net.preferIPv4Stack=true -XX:+UseNUMA -XX:+UseParallelGC -XX:+UseConcMarkSweepGC ; 
SET hive.auto.convert.join=true; 
set hive.auto.convert.join.noconditionaltask.size=288435456;

performance hive mapjoin

share|improve this question

edited Mar 24 at 8:00

leftjoin

10.9k32356

asked Mar 22 at 23:10

PK25PK25

62

SELECT st.id
FROM small_table st
LEFT JOIN large_table lt
 ON st.id = lt.id

set hive.execution.engine=tez; 
set hive.tez.container.size=4096; 
set hive.merge.mapredfiles=true; 
set tez.shuffle-vertex-manager.min-src-fraction=0.25; 
set tez.shuffle-vertex-manager.max-src-fraction=0.75; 
set hive.exec.dynamic.partition.mode=nonstrict; 
set tez.am.resource.memory.mb=3200 ; 
set tez.am.java.opts=-server -Xmx3200m -Djava.net.preferIPv4Stack=true -XX:+UseNUMA -XX:+UseParallelGC -XX:+UseConcMarkSweepGC ; 
SET hive.auto.convert.join=true; 
set hive.auto.convert.join.noconditionaltask.size=288435456;

share|improve this question

edited Mar 24 at 8:00

leftjoin

10.9k32356

asked Mar 22 at 23:10

PK25PK25

62

share|improve this question

edited Mar 24 at 8:00

leftjoin

10.9k32356

asked Mar 22 at 23:10

PK25PK25

62

edited Mar 24 at 8:00

leftjoin

10.9k32356

edited Mar 24 at 8:00

leftjoin

10.9k32356

asked Mar 22 at 23:10

PK25PK25

62

asked Mar 22 at 23:10

PK25PK25

62