Forum Discussion

rustin's avatar
rustin
New Contributor
2 years ago

Create Excel files larger than 100MB

I need to create an Excel file which is larger than 100MB in file writer. I am receiving an error message in File Writer: 

Possible reasons can be failure in URL connection or file access denial, detail: SLDB does not support data larger than 100 MB
I am copying the whole message below. Is there a way to workaround this?
 
 
Failed to write to gdrive.xlsx

Resolution:
Address the reported issue.

Reason:
Possible reasons can be failure in URL connection or file access denial, detail: SLDB does not support data larger than 100 MB
Hide Details...
File Writer[570b47e3a415f5440eb1e2dc_43c3cbfb-6c35-4be5-98d4-9dbe3c917864 -- c6bcdbd4-c8aa-4b4f-bb4f-19441389290f]
com.snaplogic.snap.api.SnapDataException: Failed to write to gdrive.xlsx at com.snaplogic.snaps.binary.AbstractWriter.throwExceptionCantWrite(AbstractWriter.java:739) at com.snaplogic.snaps.binary.AbstractWriter.writeData(AbstractWriter.java:595) at com.snaplogic.snaps.binary.AbstractWriter.process(AbstractWriter.java:379) at com.snaplogic.snaps.binary.AbstractWriter.doWork(AbstractWriter.java:329) at com.snaplogic.snap.api.SimpleBinarySnap.execute(SimpleBinarySnap.java:57) at com.snaplogic.cc.snap.common.SnapRunnableImpl.executeSnap(SnapRunnableImpl.java:812) at com.snaplogic.cc.snap.common.SnapRunnableImpl.execute(SnapRunnableImpl.java:586) at com.snaplogic.cc.snap.common.SnapRunnableImpl.doRun(SnapRunnableImpl.java:877) at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:436) at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:120) at java.base/java.util.concurrent.FutureTask.run(Unknown Source) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.base/java.util.concurrent.FutureTask.run(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.base/java.lang.Thread.run(Unknown Source) Caused by: com.snaplogic.api.ExecutionException: SLDB does not support data larger than 100 MB at com.snaplogic.common.url.protocol.sldb.SldbOutputStream.withinLimits(SldbOutputStream.java:230) at com.snaplogic.common.url.protocol.sldb.SldbOutputStream.write(SldbOutputStream.java:120) at java.base/java.io.BufferedOutputStream.write(Unknown Source) at com.snaplogic.snaps.binary.AbstractWriter.copy(AbstractWriter.java:522) at com.snaplogic.snaps.binary.AbstractWriter.writeData(AbstractWriter.java:582) ... 14 more Reason: Possible reasons can be failure in URL connection or file access denial, detail: SLDB does not support data larger than 100 MB Resolution: Address the reported issue. Error Fingerprint[0] = efp:com.snaplogic.snaps.binary.BcA2Uzfu Error Fingerprint[1] = efp:com.snaplogic.common.url.protocol.sldb.WQwFN6-j
 

12 Replies

  • rustin's avatar
    rustin
    New Contributor
    2 years ago

    I also tried this, with same error:

    I tried running on both Groundplex and Cloud.

  • koryknick's avatar
    koryknick
    Employee
    2 years ago

    rustin - the SLDB (file space in your SnapLogic project folder) has a limit of 100MB.  The path specified by pipe.tmpDir is limited by the amount of temp space allocated to SnapLogic on the execution node, so you would need to check with your server admins in your environment.

    However, Excel itself has a maximum record count of 1,048,576 rows and 16,384 columns per sheet.  If you need more than that, would CSV be sufficient?  The file size would be significantly more as a CSV since Excel compresses the data, but it may be worth trying.

    Also note that pipe.tmpDir is transient and exists only during pipeline execution, so as soon as the pipeline completes, everything in that directory is purged.

    Hope this helps!

    • rustin's avatar
      rustin
      New Contributor
      2 years ago

      koryknick , thanks for the reply. I will check with the admins about the temp space. 

      I do not need more data than what fits into Excel (I am uploading the data to Excel manually now and want to automate it), so no need to use CSV.

    • rustin's avatar
      rustin
      New Contributor
      2 years ago

      I checked with the admin and the Temp space allocate min 4 GB to Max 8 GB. That should be sufficient for the task that I am trying to automate, right? My excel files are 100MB to 900MB. 

    • rustin's avatar
      rustin
      New Contributor
      2 years ago

      koryknick - I checked with the admins and the temp space allocate min 4 GB to Max 8 GB - so this should be sufficient. 
      Another suggested solution was to skip creating excel file in Snaplogic, but connect HTTP client straight after Snowflake select, but this is doing thousands of Excel files in the destination folder, each file containing 50 rows of data. Is there something more we can do to make it work? Thanks.

       

  • koryknick's avatar
    koryknick
    Employee
    2 years ago

    rustin - There must be another issue causing the error writing to pipe.tmpDir - I have verified in my environment that a file that fails to write to SLDB due to file size over 100MB is successfully written to my pipe.tmpDir

    Can you share the error you see when using pipe.tmpDir?  Is it an error in the File Writer or Excel Formatter?

     

     

  • koryknick's avatar
    koryknick
    Employee
    2 years ago

    rustin - it looks like you're still trying to write to the SLDB which does not support files over 100MB.  Can you target a different endpoint to write your file, such as SFTP or S3?

    • rustin's avatar
      rustin
      New Contributor
      2 years ago

      koryknick , I am not sure how to do that. I assume I need to talk to IT whether we have some location on SFTP or S3 which can be used for that, correct? How can I then point to that location? Is it a part of the file name? My settings as of now attached. 

  • koryknick's avatar
    koryknick
    Employee
    2 years ago

    The filename in your File Writer is a static string; click on the equal sign to enable it as an expression (i.e. it will appear darkened).  This will allow the file to be written to a temporary directory (since you are using pipe.tmpDir) on the snapex execution node and prove that the Excel Formatter is able to handle the data volume.  Please review the File Writer snap documentation to gain an understanding on how you could write to an alternate endpoint that you can safely store the file over 100MB.

    Hope this helps!

    • rustin's avatar
      rustin
      New Contributor
      2 years ago

      I changed the equal sign and now it run with no errors. However, the file was not created in the GDrive. The issue is probably in File Writer. There is one file with size over 200MB on the input, but 1 file with 0 bytes on the output. Any thought on this?