0

I'm a hive newbie and having an odyssey of problems getting a large (1TB) HDFS file into a partitioned Hive managed table. Can you please help me get around this? I feel like I have a bad config somewhere because I'm not able to complete reducer jobs.

Here is my query:

DROP TABLE IF EXISTS ts_managed;

SET hive.enforce.sorting = true;

CREATE TABLE IF NOT EXISTS ts_managed (
 svcpt_id VARCHAR(20),
 usage_value FLOAT,
 read_time SMALLINT)
PARTITIONED BY (read_date INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS ORC
TBLPROPERTIES("orc.compress"="snappy","orc.create.index"="true","orc.bloom.filter.columns"="svcpt_id");

SET hive.vectorized.execution.enabled = true;
SET hive.vectorized.execution.reduce.enabled = true;
SET set hive.cbo.enable=true;
SET hive.tez.auto.reducer.parallelism=true;
SET hive.exec.reducers.max=20000;
SET yarn.nodemanager.pmem-check-enabled = true;
SET optimize.sort.dynamic.partitioning=true;
SET hive.exec.max.dynamic.partitions=10000;

INSERT OVERWRITE TABLE ts_managed
PARTITION (read_date)
SELECT svcpt_id, usage, read_time, read_date
FROM ts_raw
DISTRIBUTE BY svcpt_id
SORT BY svcpt_id;

My cluster specs are:

  • VM cluster
  • 4 total nodes
  • 4 data nodes
  • 32 cores
  • 140 GB RAM
  • Hortonworks HDP 3.0
  • Apache Tez as default Hive engine
  • I am the only user of the cluster

My yarn configs are:

yarn.nodemanager.resource.memory-mb = 32GB
yarn.scheduler.minimum-allocation-mb = 512MB
yarn.scheduler.maximum-allocation-mb = 8192MB
yarn-heapsize = 1024MB

My Hive configs are:

hive.tez.container.size = 682MB
hive.heapsize = 4096MB
hive.metastore.heapsize = 1024MB
hive.exec.reducer.bytes.per.reducer = 1GB
hive.auto.convert.join.noconditionaltask.size = 2184.5MB
hive.tex.auto.reducer.parallelism = True
hive.tez.dynamic.partition.pruning = True

My tez configs:

tez.am.resource.memory.mb = 5120MB
tez.grouping.max-size = 1073741824 Bytes
tez.grouping.min-size = 16777216 Bytes
tez.grouping.split-waves = 1.7
tez.runtime.compress = True
tez.runtime.compress.codec = org.apache.hadoop.io.compress.SnappyCodec

I've tried countless configurations including:

  • Partition on date
  • Partition on date, cluster on svcpt_id with buckets
  • Partition on date, bloom filter on svcpt, sort by svcpt_id
  • Partition on date, bloom filter on svcpt, distribute by and sort by svcpt_id

I can get my mapping vertex to run, but I have not gotten my first reducer vertex to complete. Here is my most recent example from the above query:

----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED   1043       1043        0        0       0       0
Reducer 2        container       RUNNING   9636          0        0     9636       1       0
Reducer 3        container        INITED   9636          0        0     9636       0       0
----------------------------------------------------------------------------------------------
VERTICES: 01/03  [=>>-------------------------] 4%    ELAPSED TIME: 6804.08 s
----------------------------------------------------------------------------------------------

The error was:

Error: Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.tez.TezTask. Vertex failed, vertexName=Reducer 2, vertexId=vertex_1537061583429_0010_2_01, diagnostics=[Task failed, taskId=task_1537061583429_0010_2_01_000070, diagnostics=[TaskAttempt 0 failed, info=[Error: Error while running task ( failure ) : java.lang.OutOfMemoryError: unable to create new native thread

I either get this OOM error which I cannot seem to get around or I get datanodes going offline and not being able to meet my replication factor requirements.

At this point I've been troubleshooting for over 2 weeks. Any contacts for professional consultants I can pay to solve this problem would also be appreciated.

Thanks in advance!

Zafar
  • 1,897
  • 15
  • 33
  • can you try to reduce the number of redecuer task. set this to `set mapred.reduce.tasks=100` and see how many reducer task you see in query. – Gaurang Shah Sep 18 '18 at 02:17
  • Do you really need `DISTRIBUTE BY` and `SORT BY` in your query? Did you try different execution engines, for instance `set hive.execution.engine=mr`? – serge_k Sep 18 '18 at 13:26
  • @serge_k `set hive.execution.engine=mr` is not supported in my version of Hortonworks Data Platform. I've tried with and without `DISTRIBUTE BY` and `SORT BY` – Zafar Sep 18 '18 at 16:05
  • @GaurangShah I have tried with 125 reduce tasks and I had nodes go offline. – Zafar Sep 18 '18 at 16:06
  • @serge_k I added the `SORT BY` in my query because with an ORC bloom filter if the data is not sorted incoming then the files in each ORC file are not mutually exclusive with regard to the bloom filter. For instance, if I'm setting my bloom filter on a col with values 1-10 and I have 5 files I want to make sure that each file only has 2 numbers in it. To do so the data must come in sorted. – Zafar Sep 18 '18 at 16:09
  • @Zafar what is the error you are getting? – Gaurang Shah Sep 18 '18 at 16:20
  • @GaurangShah with that config I usually get an IO connection refused error because of space issues that frankly don't make sense to me. If I had 100 reducers wouldn't that yield blocks that were 10GB each? That's a little large. – Zafar Sep 18 '18 at 16:35

1 Answers1

0

I ended up solving this after speaking with a Hortonworks tech guy. Turns out I was over-partitioning my table. Instead of partitioining by day over about 4 years I partitioned by month and it worked great.

Zafar
  • 1,897
  • 15
  • 33