Query output in "spreadsheet" format?

[m.ardito]

Teiid motto is "The data you want from the data you have.": well, what if the data I have is SQL and data I want is Spreadsheet?

(I know I'm stretching Teiid scope a bit lot but I'll try anyway, maybe you have suggestions, experiences, etc)

Atm, I'm generating xls files in my php web app getting data from Teiid VDB, and then filling (with phpexcel) a spreadsheet, served by the web server.

Getting data is very fast, filling the spreadsheet is easy, and I can set pretty much any xls feature, but tbh it's kinda slow when exporting thousands of rows, and dozens of columns.

Like, I can generate a 300k cells .csv in 4 seconds, but the .xls takes more than 55. I have some bigger dataset that could make timings worse...

Could Teiid be faster, helper by some library? What if I could have (maybe even materialized!) spreadsheet files served by Teid itself, somehow: there's jdbc, odbc, webservices, odata... could "spreadsheet" be added?

A basic spreadsheet "table" could be more than enough, in most cases, nothing fancy and complicated to define.

I could retrieve the raw "xls" file from teiid, generated form a query on VDB, and then consume it as it is or, in some cases, add bells and whistles (although I admit it would require spreadsheet libraries in my app to read/edit/save it anyway...): background colors, filters, freeze panes, etc.

Is it impossible, out of scope, unuseful, or too complicated to manage specific settings... ?

I thought of alternatives, like a java service, maybe installed aside Teiid in wildfly or on it own app server, to feed VDB data and get XLS, XLSX, ODS, through a web link, or something like that.

Like a Jasper server, querying Teiid, to fill a report which can be exported in many formats... including spreadhseet, pdf... (I know I'm stretching Teiid scope  a bit but I'll try anyway, maybe you have suggestions, experiences, etc)

Do you have any other suggestion/experience? What you think?

Thanks for any comment.

[shawkins]

> What if I could have (maybe even materialized!) spreadsheet files served by Teid itself, somehow: there's jdbc, odbc, webservices, odata... could "spreadsheet" be added?

The key there is what is the defined client/server protocol - for example emulating a google spreadsheet service?  When dealing with things like geospatial data we've opted to provide integration paths with platforms like geoserver and qgis rather than to try to develop something on our own.

> Is it impossible, out of scope, unuseful, or too complicated to manage specific settings... ?

What you're describing could be approached with a virtual procedure that uses caching - the inputs would be the query and other parameters, and the output would be the xsl.  A couple of enhancements may be needed to procedure caching, such as allowing a configurable number of entries.  The xsl creation that could be a custom user defined aggregate that uses apache poi, or you lobby/contribute something like an XSLAGG aggregate.

[rareddy]

"the data you want" is about content of the data, not format of the "data"

Although we do that little bit with OData to supply data in the either XML or JSON, but that is specification.  It is not impossible by any means as others as you mention (Jasper Server) have done it, it is out of scope for Teiid IMO. Typically this has been handled at reporting layer, which Teiid does not have.  On the product side, JDV did come with a Dashboard application from JBPM project (which is being rewritten ) does have Web UI that has capabilities to convert results into other exportable format. You can take look at that and see if that fits your needs.

Ramesh..

[m.ardito]

"the data you want" is about content of the data, not format of the "data"

Ramesh, I do not completely agree with this! it's much more!

I could even have .csv files as models in a VDB, in and I can get out the same data content through sql by odbc, jdbc or odata!

Then I can relationally relate (say) a .csv file to a google spreadsheet, getting new data and consume it in any form.

Teiid is so much enabling, that often makes me think beyond my current usage: what could I do more with this?

I agree (I said it form the start) that this is out of scope for teiid, but I suddenly started thinking to spreadsheet files as the deepest form of data "materialization": a real file.

Thanks for the Dashboard link and info: I'll follow that tool.

[m.ardito]

The key there is what is the defined client/server protocol - for example emulating a google spreadsheet service? When dealing with things like geospatial data we've opted to provide integration paths with platforms like geoserver and qgis rather than to try to develop something on our own.

Yes, I agree and i don't want Teiid to become bloated by unnecessary functions, probably my thought derived also from some experience I had: after setting up Teiid, I have data query capabilities that no other application has in my company, and every now and then someone asks "could you get this data related to that other and send me a spreadsheet with the results?". Usually I perform this through SquirrelSQL, which export in excel format, too. This made me wonder how I could build an efficient way to give those users the ability to self-serve spreadsheets by themselves... A reporting engine would allow much more than this, even dashboarding, but when they need just raw data, a spreadsheet file downloaded is often just what's needed. Here, at last.

What you're describing could be approached with a virtual procedure that uses caching - the inputs would be the query and other parameters, and the output would be the xsl. A couple of enhancements may be needed to procedure caching, such as allowing a configurable number of entries. The xsl creation that could be a custom user defined aggregate that uses apache poi, or you lobby/contribute something like an XSLAGG aggregate.

...that sounds interesting... but I know very little about virtual procedures... so I don't know how could I contribute in this matter...

[shawkins]

> ...that sounds interesting... but I know very little about virtual procedures... so I don't know how could I contribute in this matter...

You would have a procedure like:

create virtual procedure generateXSL(String query, boolean someOption) RETURNS blob AS

BEGIN

    ...

END

The body of the procedure could be complicated depending upon considerations such as:

- whether you want to allow direct sql, or if you need to construct the query from pieces/bindings

- if you want to eliminate the usage of any temporary tables, which means you may need to build up a single query string that represents everything you want

The possible functioning of XSLAGG would be similar to TEXTAGG - Expressions · Teiid Documentation

SELECT XSLAGG(columns..., sheet options) from ...

The logic for a user defined aggregate function using Apache POI may not be that difficult.   The source for TEXTAGG is here - teiid/TextAgg.java at master · teiid/teiid · GitHub

Steve