nightscape
commented
3 years ago Can you try something along the lines of .option("maxRowsInMemory", 20) (see the README)?
Open cedtnee opened 3 years ago
nightscape
commented
3 years ago Can you try something along the lines of .option("maxRowsInMemory", 20) (see the README)?
cedtnee
commented
3 years ago When i used .option("maxRowsInMemory", 20),the result dataframe is empty.
When i increased the .option("maxRowsInMemory", 30000) i have this error:
An exception or error caused a run to abort: GC overhead limit exceeded
java.lang.OutOfMemoryError: GC overhead limit exceeded
at com.sun.xml.internal.stream.events.StartElementEvent.init(StartElementEvent.java:76)
at com.sun.xml.internal.stream.events.StartElementEvent.
nightscape
commented
3 years ago I'm not surprised by the OutOfMemoryError, but getting an empty result is weird...
Could you try some more values in between 20 and 30000?
AlexZhang267
commented
3 years ago I used .option("maxRowsInMemory", 200), but it's still often to get an OOM error. My excel file is only 16MB. This is error message.
diagnostics: User class threw exception: java.lang.OutOfMemoryError: Java heap space
at org.apache.xmlbeans.impl.store.Cur.createElementXobj(Cur.java:260)
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.startElement(Cur.java:2997)
at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3164)
at org.apache.xerces.parsers.AbstractSAXParser.startElement(Unknown Source)
at org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3422)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1272)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1259)
at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.SstDocument$Factory.parse(Unknown Source)
at shadeio.poi.xssf.model.SharedStringsTable.readFrom(SharedStringsTable.java:123)
at shadeio.poi.xssf.model.SharedStringsTable.(SharedStringsTable.java:111)
at shadeio.poi.xssf.eventusermodel.XSSFReader.getSharedStringsTable(XSSFReader.java:115)
at shadeio.pjfanning.xlsx.impl.StreamingWorkbookReader.init(StreamingWorkbookReader.java:132)
at shadeio.pjfanning.xlsx.impl.StreamingWorkbookReader.init(StreamingWorkbookReader.java:90)
at shadeio.pjfanning.xlsx.StreamingReader$Builder.open(StreamingReader.java:307)
at com.crealytics.spark.excel.StreamingWorkbookReader.openWorkbook(WorkbookReader.scala:63)
at com.crealytics.spark.excel.WorkbookReader$class.withWorkbook(WorkbookReader.scala:14)
at com.crealytics.spark.excel.StreamingWorkbookReader.withWorkbook(WorkbookReader.scala:54)
at com.crealytics.spark.excel.ExcelRelation.excerpt$lzycompute(ExcelRelation.scala:31)
at com.crealytics.spark.excel.ExcelRelation.excerpt(ExcelRelation.scala:31)
at com.crealytics.spark.excel.ExcelRelation.headerColumns$lzycompute(ExcelRelation.scala:102)
at com.crealytics.spark.excel.ExcelRelation.headerColumns(ExcelRelation.scala:101)
at com.crealytics.spark.excel.ExcelRelation$$anonfun$inferSchema$1.apply(ExcelRelation.scala:163)
at com.crealytics.spark.excel.ExcelRelation$$anonfun$inferSchema$1.apply(ExcelRelation.scala:162)
nightscape
commented
3 years ago @AlexZhang267 I unfortunately can't invest much time into spark-excel at the moment. If possible, I would recommend to increase Spark's memory to work around this.
kennydataml
commented
2 years ago I'm getting the same problem here.
tried using "maxRowsinMemory" with values like 1000, 10000, 100000.
It works fine for the first 3 excel files but I have 8 to process and the driver node always dies on the 4th excel file. Each file is roughly 600 MB each.
I tried spark.driver.extraJavaOptions -XX:+UseG1GC and increased driver memory to 56 GB but the driver node still crashes.
My understanding is the driver will crash if there's too many occurrences of full garbage collection.
I also noticed the cluster doesn't scale up beyond 1 worker node even though I have auto scale set to 4 worker nodes.
So clearly there is some type of collect() happening on the driver node with this library? and this library doesn't seem to use the spark parallelism very well?
quanghgx
commented
2 years ago Hi @kennydataml , I am not sure if this is related. How do you read these 8 files? It will help if we have simplified (without sensitive data) and reproducible code. I am preparing a PR for native support of multiple files loading. Sincerely,
kennydataml
commented
2 years ago I unfortunately can't get a scrubbed excel for you since it's on a client laptop. the code is simple. I only read in one excel at a time with a for loop. so basically
for xlpath in excels:
csvpath = xlpath split join yadayda
try: # exception handling since we don't know the number of sheets
for i in range(15): # dynamic number of sheets
df = (spark.read
.format("crealytics ... spark excel yada yada")
.option("dataAddress", f"{i}!A1") # sub sheet index here
.option("header", "true")
.option("maxRowsInMemory", 100000)
.load(xlpath)
# write excel to csv
(df.write
.repartition(200) # attempting to circumvent memory issues
.format("csv")
.mode("append")
.option("header", "true")
.save(csvpath)
)
except Exception as err:
print(repr(err))I've narrowed down the problem to only 1 of 8 excel files. I can consistently reproduce it on that particular excel file. It opens up just fine using microsoft excel, so I'm puzzled why only 1 particular excel file gives me an issue. The behaviour I'm observing is I can read the first 6 sheets, but it hangs on the 7th sheet when it tries to append write to the csv path. There is no spark job initiated for writing as expected, and it just hangs for an hour straight. I've tried running the code without looping through all excels, targeting only that particular excel file and it will hang consistently on the 7th sheet. It's really weird, I'm going to do some more testing tomorrow with a fresh copy of the data.
NestorAGC123
commented
2 years ago I have the same issue and my excel file is only 2 MB, It also happens on some specific files
quanghgx
commented
2 years ago Hi @NestorAGC123 , Is it possible to share your excel file after removing sensible data?
pjfanning
commented
2 years ago the problem is that spark-excel reads the file as an input stream and that uses far more memory than reading it as java.io.File - I have logged https://bz.apache.org/bugzilla/show_bug.cgi?id=65581 for a possible solution
yanghong
commented
2 years ago Does this problem still unclosed? I have the same problem.
pjfanning
commented
2 years ago @yanghong maybe you could do a PR based on https://bz.apache.org/bugzilla/show_bug.cgi?id=65581 changes ?
yanghong
commented
2 years ago @yanghong maybe you could do a PR based on https://bz.apache.org/bugzilla/show_bug.cgi?id=65581 changes ?
Fine, it‘s poi’s limitaion.
Expected Behavior
Current Behavior
Troubleshouting
Your Environment