Exporting large table from RDBMS

On Fri, Nov 11, 2016 at 10:56 AM, Marco Lettere <m.lettere at gmail.com> wrote: > Hi all, > > I'm currently facing the issue of exporting a large table stored in a MySQL > instance to a csv that has then to be zipped and served through a web > service. > > I'd love to use XQuery and the Basex functionality for doing it because it > feels perfectly natural. > > The problem is that the XQuery script that performs the SQL Query and > transformation to CSV crashes with an Out of Memory exception. > > I know how to increase memory and so on but I'd like to find a stable > solution that is as less as possible dependent from the system requirements. > > Currently the only idea is to page the operation and (maybe exploiting > parallel execution) build an intermediate result on disk files that will be > assembled before delivery. > > Isn't there any more elegant solution based somehow on the streaming > functionality or anything like this? > > Thanks for any advice you can provide. > > Regards, > > Marco. >

Marco Lettere <m.lettere at gmail.com> schrieb am 10:57 Freitag, 11.November 2016:

> Marco, I've faced the same problem and frustration, and I agree that it > would be great if BaseX could be used for such tasks. Strengthening its > potential role as a data integration engine. > Kind regards, > Hans > > > Marco Lettere <m.lettere at gmail.com> schrieb am 10:57 Freitag, 11.November > 2016: > > > Hi all, > > I'm currently facing the issue of exporting a large table stored in a > MySQL instance to a csv that has then to be zipped and served through a > web service. > > I'd love to use XQuery and the Basex functionality for doing it because > it feels perfectly natural. > > The problem is that the XQuery script that performs the SQL Query and > transformation to CSV crashes with an Out of Memory exception. > > I know how to increase memory and so on but I'd like to find a stable > solution that is as less as possible dependent from the system requirements. > > Currently the only idea is to page the operation and (maybe exploiting > parallel execution) build an intermediate result on disk files that will > be assembled before delivery. > > Isn't there any more elegant solution based somehow on the streaming > functionality or anything like this? > > Thanks for any advice you can provide. > > Regards, > > Marco. > > >

On 11/11/2016 14:27, Christian Grün wrote: > …more input would be welcome ;) > >> Marco, I've faced the same problem and frustration, and I agree that it >> would be great if BaseX could be used for such tasks. Strengthening its >> potential role as a data integration engine. >> Kind regards, >> Hans >> >> >> Marco Lettere <m.lettere at gmail.com> schrieb am 10:57 Freitag, 11.November >> 2016: >> >> >> Hi all, >> >> I'm currently facing the issue of exporting a large table stored in a >> MySQL instance to a csv that has then to be zipped and served through a >> web service. >> >> I'd love to use XQuery and the Basex functionality for doing it because >> it feels perfectly natural. >> >> The problem is that the XQuery script that performs the SQL Query and >> transformation to CSV crashes with an Out of Memory exception. >> >> I know how to increase memory and so on but I'd like to find a stable >> solution that is as less as possible dependent from the system requirements. >> >> Currently the only idea is to page the operation and (maybe exploiting >> parallel execution) build an intermediate result on disk files that will >> be assembled before delivery. >> >> Isn't there any more elegant solution based somehow on the streaming >> functionality or anything like this? >> >> Thanks for any advice you can provide. >> >> Regards, >> >> Marco. >> >> >>

> <csv>{ > $sql-results ! > <record> > <id>{./sql:column[lower-case(@name)="id"]/string()}</id> > <name>{./sql:column[lower-case(@name)="name"]/string()}</name> > <timestamps>{./sql:column[lower-case(@name)="timestamp"]/string()}</timestamps> > <value>{./sql:column[lower-case(@name)="value"]/string()}</value> > </record> > } > </csv> > };

On 11/11/2016 16:11, Christian Grün wrote: > Thanks! This looks like a blocker: > >> <csv>{ >> $sql-results ! >> <record> >> <id>{./sql:column[lower-case(@name)="id"]/string()}</id> >> <name>{./sql:column[lower-case(@name)="name"]/string()}</name> >> <timestamps>{./sql:column[lower-case(@name)="timestamp"]/string()}</timestamps> >> <value>{./sql:column[lower-case(@name)="value"]/string()}</value> >> </record> >> } >> </csv> >> }; > As you are wrapping a root element around all results, everything > needs to be cached in main memory and thus prevents streaming.

On Mon, Nov 14, 2016 at 12:47 PM, Marco Lettere <m.lettere at gmail.com> wrote: > Hi all, > back with some results.... > > We have been able to stream the data from mysql into a string representation > of the expected CSV rewriting the functions like this: > > declare function j:csv-export($table as xs:string) { > let $connection := sql:connect(conf:get("jdbc.url"), > conf:get("jdbc.user"), conf:get("jdbc.pass")) > let $tmp-file := string-join(('"id","name","timestamp","value"', > sql:execute($connection, "SELECT * FROM " || $table || $where) > ! (out:nl() || j:to-cvs-row(.)))) > return ($tmp-file, sql:close($connection)) > }; > > declare function j:to-cvs-row($sql-result as node()) { > $sql-result/sql:column[lower-case(@name)="id"]/string() || > conf:get("csv.sep") || > '"' || $sql-result/sql:column[lower-case(@name)="name"]/string() || > '"' || conf:get("csv.sep") || > '"' || > $sql-result/sql:column[lower-case(@name)="timestamp"]/string() || '"' || > conf:get("csv.sep") || > '"' || $sql-result/sql:column[lower-case(@name)="value"]/string() || > '"' > }; > > Now, since we have to export actually three or four tables from the rdbms > into a zip archive, the only bottleneck that remains is that still the whole > string for each csv has to be kept in memory before zipping it to the > archive together with some small metadata. > > We use something like the following code: > > let $metadata-entry := <archive:entry > last-modified='{current-dateTime()}'compression-level='8'>metadata.json</archive:entry> > let $table-entry := $tables ! <archive:entry > last-modified='{current-dateTime()}' compression-level='8'>{ . || > ".csv"}</archive:entry> > let $table-data := $tables ! ji:csv-export(.) > return archive:create(($metadata-entry, $table-entries), > (serialize($metadata), $table-data))) > > Are there suggestions to keep the very effective "streaming behaviour" also > while adding the entries to the zip archive? > Thanks a lot! > Marco. > > > On 11/11/2016 16:11, Christian Grün wrote: >> >> Thanks! This looks like a blocker: >> >>> <csv>{ >>> $sql-results ! >>> <record> >>> <id>{./sql:column[lower-case(@name)="id"]/string()}</id> >>> <name>{./sql:column[lower-case(@name)="name"]/string()}</name> >>> >>> <timestamps>{./sql:column[lower-case(@name)="timestamp"]/string()}</timestamps> >>> <value>{./sql:column[lower-case(@name)="value"]/string()}</value> >>> </record> >>> } >>> </csv> >>> }; >> >> As you are wrapping a root element around all results, everything >> needs to be cached in main memory and thus prevents streaming. > > >