AnsweredAssumed Answered

Hive, Parquet, ORC plus partitions = Out of Memory

Question asked by dodoman on Nov 3, 2016

Hello there,

 

we found new un"expected beahaviour"  in hive.

 

Issue:

We imported a table into hive by sqoop with the following settings.

Querying the data by drill and hive works fine but the performance of hive without partitions is really bad if we are using any kind of filters.

 nohup time /opt/mapr/sqoop/sqoop-1.4.6/bin/sqoop-import --connect "..." --password ... --username ... --table "MY.MYTABLE" -m 16 --create-hive-table --hive-import --hive-table "mytable" --target-dir "maprfs:///tmp/plaintext/mytable" --split-by "trip_org" &

So we repartitioned the table. Because hive does not support repartitioning yet, we created a new table by the following query:

SET hive.exec.dynamic.partition=true;
SET hive.exec.max.dynamic.partitions=1000
SET hive.exec.dynamic.partition.mode=nonstrict
(Don't nail me here on the quotation marks please)

CREATE TABLE mytable2(52 Columns with datatypes)  PARTITIONED BY( trip_beg_year smallint, trip_beg_month tinyint, trip_beg_day tinyint)  STORED AS PARQUET;

CREATE TABLE mytable3 (52 Columns with datatypes)   PARTITIONED BY( trip_beg_year smallint, trip_beg_month tinyint, trip_beg_day tinyint)  CLUSTERED BY(trip_org, trip_dst,trip_bkg_year,trip_bkg_month,trip_bkg_day) INTO 64 BUCKETS  STORED AS ORC;



We created these 2 tables . Each with and without beeing clustered, with the statements we showed before.

So until this point everything looks ok. 

 

Now we added a single data entry with the following command for testing if the partitioning is producing the expected result:

 

INSERT INTO TABLE mytableX PARTITION (trip_beg_year, trip_beg_month, trip_beg_day)  select * FROM plaintable;

 

If we add "limit 1" to the statement, the query works.

If transform the fully 150 GB we run into Out of Memory errors, without using all the memory.

 

Now the interesting part:

If we create the same table, with partitioning and clustering enabled but without using a compressed datatype, we are able to transform the database.

 

 

Environment:

Hive Table in the Hive Default Data format

Original Table: 50 column, 150 GB size.

Target format: 50 columns, enabled partitioning, enabled clustering, as parquet datatype.

 

5 Node Cluster

4GB Memory min. Memory for YARN

Max Memory of a single container: 8GB

MapR 5.1

Hive 1.2.1 - Last build before June 2016 (Upgrades from MapR are not working here, you forgot to fix configurations after changes you did. btw)

 

We also tried:

Increasing the size of:

mapred.map.child.java.opts

Inside and outside of a hive query.

(Don't nail me here on syntax again. It was done correctly)

 

What we tried and what we have found already:

 

https://hvivani.com.ar/2014/12/06/hive-dealing-with-out-of-memory-and-garbage-collector-errors/ 

We set these values at mapred-site.xml and inside of hive. This had no effect at all.

 

We also found a second person in the world which has a similiar setup:

hadoop - Not able to apply dynamic partitioning for a huge data set in Hive - Stack Overflow 

 

 

 

Error Logs:

Container Overview of the Ressource Manager:

Go to the Ressource Manager -> Select the app -> Click on History -> Get this overview:

attempt_1478019514248_0002_m_000000_0 FAILED /default-rack/hdp04.:8042 logs Tue Nov 1 17:20:59 +0000 2016 Tue Nov 1 17:21:18 +0000 2016 18sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000000_1 FAILED /default-rack/hdp02.:8042 logs Tue Nov 1 17:21:25 +0000 2016 Tue Nov 1 17:21:38 +0000 2016 12sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000000_2 FAILED /default-rack/hdp02.:8042 logs Tue Nov 1 17:21:50 +0000 2016 Tue Nov 1 17:22:01 +0000 2016 11sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000001_0 FAILED /default-rack/hdp05.:8042 logs Tue Nov 1 17:20:59 +0000 2016 Tue Nov 1 17:21:21 +0000 2016 21sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000001_1 FAILED /default-rack/hdp01.:8042 logs Tue Nov 1 17:21:31 +0000 2016 Tue Nov 1 17:21:36 +0000 2016 5sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000002_0 FAILED /default-rack/hdp01.:8042 logs Tue Nov 1 17:20:59 +0000 2016 Tue Nov 1 17:21:07 +0000 2016 7sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000002_1 FAILED /default-rack/hdp02.:8042 logs Tue Nov 1 17:21:15 +0000 2016 Tue Nov 1 17:21:24 +0000 2016 8sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000002_2 FAILED /default-rack/hdp04.:8042 logs Tue Nov 1 17:21:35 +0000 2016 Tue Nov 1 17:21:54 +0000 2016 18sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000002_3 FAILED /default-rack/hdp02.:8042 logs Tue Nov 1 17:22:08 +0000 2016 Tue Nov 1 17:22:27 +0000 2016 19sec Error: Java heap space
attempt_1478019514248_0002_m_000003_0 FAILED /default-rack/hdp02.:8042 logs Tue Nov 1 17:20:59 +0000 2016 Tue Nov 1 17:21:13 +0000 2016 14sec Error: Java heap space
attempt_1478019514248_0002_m_000003_1 FAILED /default-rack/hdp05.:8042 logs Tue Nov 1 17:21:23 +0000 2016 Tue Nov 1 17:21:42 +0000 2016 18sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000003_2 FAILED /default-rack/hdp04.:8042 logs Tue Nov 1 17:21:55 +0000 2016 Tue Nov 1 17:22:14 +0000 2016 18sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000006_0 FAILED /default-rack/hdp01.:8042 logs Tue Nov 1 17:21:00 +0000 2016 Tue Nov 1 17:21:29 +0000 2016 28sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000007_0 FAILED /default-rack/hdp02.:8042 logs Tue Nov 1 17:21:00 +0000 2016 Tue Nov 1 17:21:12 +0000 2016 11sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000007_1 FAILED /default-rack/hdp03.:8042 logs Tue Nov 1 17:21:17 +0000 2016 Tue Nov 1 17:21:33 +0000 2016 15sec Error: GC overhead limit exceeded
attempt_1478019514248_0002_m_000007_2 FAILED /default-rack/hdp05.:8042 logs Tue Nov 1 17:21:44 +0000 2016 Tue Nov 1 17:22:03 +0000 2016 19sec Error: GC overhead limit exceeded

Now the log of a single container:

 

.....


Scaling row group sizes to 5.21% for 60 writers
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.codec.CodecConfig: Compression set to false
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.codec.CodecConfig: Compression: UNCOMPRESSED
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Parquet block size to 134217728
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Parquet page size to 1048576
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Parquet dictionary page size to 1048576
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Dictionary is on
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Validation is off
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Writer version is: PARQUET_1_0
Nov 1, 2016 5:41:47 PM WARNING: parquet.hadoop.MemoryManager: Total allocation exceeds 50.00% (419,430,400 bytes) of heap memory
Scaling row group sizes to 5.12% for 61 writers
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.codec.CodecConfig: Compression set to false
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.codec.CodecConfig: Compression: UNCOMPRESSED
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Parquet block size to 134217728
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Parquet page size to 1048576
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Parquet dictionary page size to 1048576
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Dictionary is on
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Validation is off
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Writer version is: PARQUET_1_0
Nov 1, 2016 5:41:47 PM WARNING: parquet.hadoop.MemoryManager: Total allocation exceeds 50.00% (419,430,400 bytes) of heap memory
Scaling row group sizes to 5.04% for 62 writers
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.codec.CodecConfig: Compression set to false
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.codec.CodecConfig: Compression: UNCOMPRESSED
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Parquet block size to 134217728
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Parquet page size to 1048576
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Parquet dictionary page size to 1048576
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Dictionary is on
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Validation is off
Nov 1, 2016 5:41:47 PM INFO: parquet.hadoop.ParquetOutputFormat: Writer version is: PARQUET_1_0
Nov 1, 2016 5:41:47 PM WARNING: parquet.hadoop.MemoryManager: Total allocation exceeds 50.00% (419,430,400 bytes) of heap memory
Scaling row group sizes to 4.96% for 63 writers
.....

 

And the error which is killing the application:

2016-11-01 17:41:48,142 INFO [main] org.apache.hadoop.hive.ql.exec.FileSinkOperator: Writing to temp file: FS maprfs:/user/hive/warehouse/mytable/.hive-staging_hive_2016-11-01_17-41-31_992_4073918584263656908-4/_task_tmp.-ext-10002/trip_beg_year=2016/trip_beg_month=09/trip_beg_day=07/_tmp.000001_0
2016-11-01 17:41:48,142 INFO [main] org.apache.hadoop.hive.ql.exec.FileSinkOperator: New Final Path: FS maprfs:/user/hive/warehouse/mytable/.hive-staging_hive_2016-11-01_17-41-31_992_4073918584263656908-4/_tmp.-ext-10002/trip_beg_year=2016/trip_beg_month=09/trip_beg_day=07/000001_0
2016-11-01 17:41:48,161 INFO [main] org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat: creating new record writer...org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat@1440c311
2016-11-01 17:41:48,161 INFO [main] org.apache.hadoop.hive.ql.io.parquet.write.ParquetRecordWriterWrapper: initialize serde with table properties.
2016-11-01 17:41:48,161 INFO [main] org.apache.hadoop.hive.ql.io.parquet.write.ParquetRecordWriterWrapper: creating real writer to write at maprfs:/user/hive/warehouse/mytable/.hive-staging_hive_2016-11-01_17-41-31_992_4073918584263656908-4/_task_tmp.-ext-10002/trip_beg_year=2016/trip_beg_month=09/trip_beg_day=07/_tmp.000001_0
2016-11-01 17:41:49,298 INFO [main] org.apache.hadoop.hive.ql.io.parquet.write.ParquetRecordWriterWrapper: real writer: parquet.hadoop.ParquetRecordWriter@f973499
2016-11-01 17:41:49,299 INFO [main] org.apache.hadoop.hive.ql.exec.FileSinkOperator: Final Path: FS maprfs:/user/hive/warehouse/mytable/.hive-staging_hive_2016-11-01_17-41-31_992_4073918584263656908-4/_tmp.-ext-10002/trip_beg_year=2016/trip_beg_month=09/trip_beg_day=02/000001_0
2016-11-01 17:41:49,299 INFO [main] org.apache.hadoop.hive.ql.exec.FileSinkOperator: Writing to temp file: FS maprfs:/user/hive/warehouse/mytable/.hive-staging_hive_2016-11-01_17-41-31_992_4073918584263656908-4/_task_tmp.-ext-10002/trip_beg_year=2016/trip_beg_month=09/trip_beg_day=02/_tmp.000001_0
2016-11-01 17:41:49,299 INFO [main] org.apache.hadoop.hive.ql.exec.FileSinkOperator: New Final Path: FS maprfs:/user/hive/warehouse/mytable/.hive-staging_hive_2016-11-01_17-41-31_992_4073918584263656908-4/_tmp.-ext-10002/trip_beg_year=2016/trip_beg_month=09/trip_beg_day=02/000001_0
2016-11-01 17:41:49,306 INFO [main] org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat: creating new record writer...org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat@4d33940d
2016-11-01 17:41:49,306 INFO [main] org.apache.hadoop.hive.ql.io.parquet.write.ParquetRecordWriterWrapper: initialize serde with table properties.
2016-11-01 17:41:49,306 INFO [main] org.apache.hadoop.hive.ql.io.parquet.write.ParquetRecordWriterWrapper: creating real writer to write at maprfs:/user/hive/warehouse/mytable/.hive-staging_hive_2016-11-01_17-41-31_992_4073918584263656908-4/_task_tmp.-ext-10002/trip_beg_year=2016/trip_beg_month=09/trip_beg_day=02/_tmp.000001_0
2016-11-01 17:41:50,322 INFO [main] org.apache.hadoop.hive.ql.exec.MapOperator: 3 finished. closing...
2016-11-01 17:41:50,322 INFO [main] org.apache.hadoop.hive.ql.exec.MapOperator: DESERIALIZE_ERRORS:0
2016-11-01 17:41:50,322 INFO [main] org.apache.hadoop.hive.ql.exec.MapOperator: RECORDS_IN:181
2016-11-01 17:41:50,322 INFO [main] org.apache.hadoop.hive.ql.exec.TableScanOperator: 0 finished. closing...
2016-11-01 17:41:50,322 INFO [main] org.apache.hadoop.hive.ql.exec.SelectOperator: 1 finished. closing...
2016-11-01 17:41:50,322 INFO [main] org.apache.hadoop.hive.ql.exec.FileSinkOperator: 2 finished. closing...
2016-11-01 17:41:50,322 INFO [main] org.apache.hadoop.hive.ql.exec.FileSinkOperator: FS[2]: records written - 181
2016-11-01 17:41:56,328 FATAL [main] org.apache.hadoop.mapred.YarnChild: Error running child : java.lang.OutOfMemoryError: GC overhead limit exceeded
     at parquet.column.values.dictionary.IntList.initSlab(IntList.java:90)
     at parquet.column.values.dictionary.IntList.<init>(IntList.java:86)
     at parquet.column.values.dictionary.DictionaryValuesWriter.reset(DictionaryValuesWriter.java:182)
     at parquet.column.values.fallback.FallbackValuesWriter.reset(FallbackValuesWriter.java:97)
     at parquet.column.impl.ColumnWriterV1.writePage(ColumnWriterV1.java:165)
     at parquet.column.impl.ColumnWriterV1.flush(ColumnWriterV1.java:241)
     at parquet.column.impl.ColumnWriteStoreV1.flush(ColumnWriteStoreV1.java:126)
     at parquet.hadoop.InternalParquetRecordWriter.flushRowGroupToStore(InternalParquetRecordWriter.java:154)
     at parquet.hadoop.InternalParquetRecordWriter.close(InternalParquetRecordWriter.java:113)
     at parquet.hadoop.ParquetRecordWriter.close(ParquetRecordWriter.java:112)
     at org.apache.hadoop.hive.ql.io.parquet.write.ParquetRecordWriterWrapper.close(ParquetRecordWriterWrapper.java:102)
     at org.apache.hadoop.hive.ql.io.parquet.write.ParquetRecordWriterWrapper.close(ParquetRecordWriterWrapper.java:119)
     at org.apache.hadoop.hive.ql.exec.FileSinkOperator$FSPaths.abortWriters(FileSinkOperator.java:252)
     at org.apache.hadoop.hive.ql.exec.FileSinkOperator.closeOp(FileSinkOperator.java:1063)
     at org.apache.hadoop.hive.ql.exec.Operator.close(Operator.java:616)
     at org.apache.hadoop.hive.ql.exec.Operator.close(Operator.java:630)
     at org.apache.hadoop.hive.ql.exec.Operator.close(Operator.java:630)
     at org.apache.hadoop.hive.ql.exec.Operator.close(Operator.java:630)
     at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.close(ExecMapper.java:192)
     at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:61)
     at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:458)
     at org.apache.hadoop.mapred.MapTask.run(MapTask.java:348)
     at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
     at java.security.AccessController.doPrivileged(Native Method)
     at javax.security.auth.Subject.doAs(Subject.java:422)
     at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1595)
     at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)

 

 

When does this issue get fixed? It seems to exist for a really long time. Does anyone have any ideas if this issue still exist in hive2?

 

Best regards,

Oliver

Outcomes