Hi All, I have the following hive query run on Tez/YARN involving two hive tables A and B: select
PreQuery.name,
sum(case when PreQuery.Geode < 10.0 then 1 else 0 end) 10mCount,
sum(case when PreQuery.Geode < 50.0 then 1 else 0 end) 50mCount,
sum(case when PreQuery.Geode < 1000.0 then 1 else 0 end) 100mCount
from
( select
a.name,
ST_GeodesicLengthWGS84( ST_SetSRID( ST_LineString(a.lat, a.lon, b.lat, b.lon),4326)) as Geode
from a, b) PreQuery
GROUP BY
PreQuery.name
ORDER by
1000mCount desc Table A has 45,000 rows, numFiles=1 as a managed ORC file and totalDataSize=1423246. Table B has 54,000 rows with a totalDataSize=11876624 and numFiles=1 stored as a managed TEXT FILE. My HDP2.4 cluster has 3 nodes providing a total of 12vCores with a minimum allocation of 256MB (1vCore) and a maximum allocation of 1048MB (4 vCores) YARN container(s). there is no bucketing or partitioning on these tables. My main Hive/Tez settings are: yarn.scheduler.minimum-allocation-mb=256MB
yarn.scheduler.maximum-allocation-mb=1048MB
hive.tez.container.size=512MB
hive.tez.java.opts=-server -Djava.net.preferIPv4Stack=true -XX:NewRatio=8 -XX:+UseNUMA -XX:+UseG1GC -XX:+ResizeTLAB -XX:+PrintGCDetails -verbose:gc -XX:+PrintGCTimeStamps
mapreduce.map.memory.mb=256MB
mapreduce.map.java.opts=-Xmx204m
mapreduce.reduce.memory.mb=512MB
mapreduce.reduce.java.opts=-Xmx409m
tez.runtime.io.sort.mb=68MB
hive.auto.convert.sortmerge.join=true
hive.auto.convert.sortmerge.join.to.mapjoin=false
When I run the query I see the following output in the DaG graphical view: Mapper 4 completes instantly but then Mapper 1 says its running, but its been running for hours and nothing is progressing. The DAG indicates that it is running at 20% with no clear sign of progress. I'm running through query through the beeline command tool, the latest outputs: "INFO : Map 1: 0(+1)/1Map 4: 1/1Reducer 2: 0/2Reducer 3: 0/1". Looking at the YARN UI manager the job created only requests 2 containers from the underlying cluster using 512MB of memory per container. So my general question is how can I optimise this to run faster? Is it an issue with the query itself, the way the tables are setup or is more resource required in the platform. I feel like given the small volume of data im processing the available resource in the cluster should be more than enough which leads me to believe I'm doing something very wrong or the config is not correct. Any help much appreciated, M
... View more
