Out of memory exception when reading big xlsx file about spark-excel HOT 12 CLOSED

Hello @aprasa02 !

What are the value set for --driver-memory, --executor-memory and --executor-cores when submitting your Spark Job ?

How many partitions does your file contains ?

Usually 100mb are not a problem for Spark.

Best,

Gregory.

from spark-excel.

cluster mode execution with driver memory 5g , executor memory 50g executor cores 64 . The partition we are setting as 100.I feel the issue comes when we are doing the union of multiple dataframes resulting in memory issue. we were testing with 28 tabs , means union of 28 dfs and return it. Since there is no option to create df for all the sheets in one go in crealytics API , forcibly I had to use POI to get sheet names and then passing to crealytics api and then union it all.

from spark-excel.

Can you try and serialize the Excel Dataframes to e.g. Parquet and see if you get the same issues when using those?
Putting multiple sheets in one Dataframe would in theory be possible, but all your sheets would need to have the same schema.

from spark-excel.

I am getting an out of memory error with the online retail dataset

from spark-excel.

It seems that Apache POI is not the most memory-efficient library out there, mostly due to the fact that it provides a mutable API where you can read stuff, modify some arbitrary cells and then write back to a file.
We could switch to something like https://github.com/monitorjbl/excel-streaming-reader which does only the reading part and therefore can stream the data.
I guess that would allow you to read almost arbitrarily-sized data.

from spark-excel.

@aprasa02 Not related, but FYI, in most Hadoop\Spark deployments, you should be able to use the s3a file system directly and shorten your code, instead of setting the path option, pass the s3a url into load.

.load("s3a://bucket_name/folders/filename)

from spark-excel.

Hey @litzebauer,
I saw you created a fork and fixed this issue.
Is it working out?
Do you want to create a PR?

from spark-excel.

from spark-excel.

I created a PR for streaming here: #36
It was much harder than expected to make everything really stream and not consume the iterator accidentally and there is still a failing test which I can hopefully fix soon.

from spark-excel.

I just released 0.9.6 to the Sonatype Maven repo.
It features a maxRowsInMemory option which when used switches to a streaming implementation using https://github.com/monitorjbl/excel-streaming-reader
This library doesn't support all functionality of Apache POI that's why I didn't make it the default (yet).
When running in dev environment, I actually had to override the version of jackson because of conflicts with the Spark-packaged version. If you run into any problems which look like a jackson version mismatch let me know, then we need to shade the dependency.

from spark-excel.

I am getting some build errors with 0.9.6...

[error] 71 errors were encountered during merge
java.lang.RuntimeException: deduplicate: different file contents found in the following:
C:\Users\dwb.ivy2\cache\com.crealytics\spark-excel_2.11\jars\spark-excel_2.11-0.9.6.jar:com/fasterxml/jackson/annotation/JsonAutoDetect$1.class
C:\Users\dwb.ivy2\cache\com.fasterxml.jackson.core\jackson-annotations\bundles\jackson-annotations-2.8.0.jar:com/fasterxml/jackson/annotation/JsonAutoDetect$1.class

from spark-excel.

so I fixed the above problem by modifying my assembly merge strategy

but then I ran in to the same problem as you when running unit tests.. I fixed that by adding this to build.sbt..

assemblyShadeRules in assembly := Seq(
      ShadeRule.rename("com.fasterxml.jackson.**" -> "shadeio.@1").inAll
    )

from spark-excel.