Forum Stats

  • 3,758,922 Users
  • 2,251,475 Discussions
  • 7,870,433 Comments

Discussions

How do I compress the output?

SteveB
SteveB Member Posts: 38
edited Jun 25, 2018 12:39PM in SQLcl

Hi.

(Using sqlcl 18.1 on 11.2.0.4)

In sqldev I can compress a json 'export' of a table. In sqlcl I can set the sqlformat to json - which is awesome. I can't see how I can also compress. I want to dump a 90M row table in json format and I'd really prefer to zip it on the way out.

Is this possible?

Thanks,

Steve

Gaz in OzGlen Conway

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Jun 11, 2018 8:10PM

    Not certain if anyone has put a script (javascript) out on

    https://github.com/oracle/oracle-db-tools/tree/master/sqlcl/examples

    that does exactly what you want, but in a discussion on stack overflow

    https://stackoverflow.com/questions/41143734/compressing-data-during-spooling-from-oracle-table

    "thatjeffsmith" replies

    sqlcl does automatic CSV [or JSON] formatting, also it lets you run javascript, so you could use a variation of this to compress the data as it goes into the spool github.com/oracle/oracle-db-tools/commit/

    Cheers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Jun 11, 2018 9:42PM

    By "zip it on the way out" do you mean "stream the text, to zip" so the zip file is never full unzipped size"?

    What is your OS and version?

    UNIX/Linux OS's can do this using named pipes.

    https://duckduckgo.com/?q=linux+named+pipe&t=ffab&ia=web

  • SteveB
    SteveB Member Posts: 38
    edited Jun 12, 2018 12:08AM

    Thanks Glen and Gaz for your responses.  Gaz, I really want to be able to do this portably (and from within sqlcl) if possible.  Named pipes requires a bit more mucking around, and I've got to have another process consuming the pipe data and outputting it.

    So looking through some of the example scripts, it seems that the global object sqlcl has a setOut({bufferedOutputStream}) method.

    I'm guessing here but if there were a getOut method (there doesn't seem to be), I could create a new command 'zip' that I could invoke after the spool but before executing the SELECT that would replace the output stream with a GZIPOutputStream based on the original??

    It would be great if the API were documented beyond what I could find here - https://github.com/oracle/oracle-db-tools/blob/master/sqlcl/SCRIPTING.md.  Saying there are "tons of methods" is just teasing.  ;-)  Maybe I missed it.  If so, please accept my apologies and point me to it.

    Thanks,

    Steve

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Jun 14, 2018 2:06PM

    Resorting back to the command-line...

    $ cat json.sqlconnect gaz/[email protected] pages 0 feedback offset sqlformat jsonselect level                                                             n,       CHR(level + 64)                                                   s,       TO_CHAR(TRUNC(sysdate) + (level - 1)/48, 'yyyy-mm-dd hh24:mi:ss') dfrom   dualconnect by level < 27/exit$ sql -s -l /NOLOG @json.sql | zip json -updating: - (deflated 77%)$ unzip -p json # >a_file_of_your_choice.txt...{"results":[{"columns":[{"name":"N","type":"NUMBER"},{"name":"S","type":"NUMBER"},{"name":"D","type":"NUMBER"}],"items":[{"n":1,"s":"A","d":"2018-06-15 00:00:00"},{"n":2,"s":"B","d":"2018-06-15 00:30:00"},{"n":3,"s":"C","d":"2018-06-15 01:00:00"},{"n":4,"s":"D","d":"2018-06-15 01:30:00"},{"n":5,"s":"E","d":"2018-06-15 02:00:00"},{"n":6,"s":"F","d":"2018-06-15 02:30:00"},{"n":7,"s":"G","d":"2018-06-15 03:00:00"},{"n":8,"s":"H","d":"2018-06-15 03:30:00"},{"n":9,"s":"I","d":"2018-06-15 04:00:00"},{"n":10,"s":"J","d":"2018-06-15 04:30:00"},{"n":11,"s":"K","d":"2018-06-15 05:00:00"},{"n":12,"s":"L","d":"2018-06-15 05:30:00"},{"n":13,"s":"M","d":"2018-06-15 06:00:00"},{"n":14,"s":"N","d":"2018-06-15 06:30:00"},{"n":15,"s":"O","d":"2018-06-15 07:00:00"},{"n":16,"s":"P","d":"2018-06-15 07:30:00"},{"n":17,"s":"Q","d":"2018-06-15 08:00:00"},{"n":18,"s":"R","d":"2018-06-15 08:30:00"},{"n":19,"s":"S","d":"2018-06-15 09:00:00"},{"n":20,"s":"T","d":"2018-06-15 09:30:00"},{"n":21,"s":"U","d":"2018-06-15 10:00:00"},{"n":22,"s":"V","d":"2018-06-15 10:30:00"},{"n":23,"s":"W","d":"2018-06-15 11:00:00"},{"n":24,"s":"X","d":"2018-06-15 11:30:00"},{"n":25,"s":"Y","d":"2018-06-15 12:00:00"},{"n":26,"s":"Z","d":"2018-06-15 12:30:00"}]}]}$$

    Works on Linux and windows. Only difference with windows is CRLF for the data coming out of sqlcl.

    F:\>sql -s -l -noupdates /NOLOG @json.sql | zip json -updating: - (deflated 77%)F:\>unzip -p json{"results":[{"columns":[{"name":"N","type":"NUMBER"},{"name":"S","type":"VARCHAR2"},{"name":"D","type":"VARCHAR2"}],"items":[{"n":1,"s":"A","d":"2018-06-15 00:00:00"},{"n":2,"s":"B","d":"2018-06-15 00:30:00"},{"n":3,"s":"C","d":"2018-06-15 01:00:00"},{"n":4,"s":"D","d":"2018-06-15 01:30:00"}...F:\>
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,982 Employee
    edited Jun 25, 2018 7:53AM

    For 18.2 we will have added this feature.

    set sqlformat json

    spool c:\data.json.zip

    select ...

    spool off

    output will be a compressed json file.

    The trick is to just spool to a ZIP file extension.

    image

    Gaz in OzGlen Conway
  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Jun 25, 2018 12:39PM

    Cool!  And I guess that enhancement was necessary.  Of course, just running the query with json sqlformat from javascript does not return any of the metadata in the result set.  Thanks for the extra "secret sauce".