JBossDeveloper

> If this is confirmed, could the adapter be improved to ignore columns with empty labels... ?

 

Yes, that sounds like it needs to be captured as an issue. Can you log something for that?

I just did: [TEIID-4434] Results returned by google spreadhseet may have columns missing labels, preventing the VDB to load - JBoss …

btw, I just connected another google spreadhsheet (which is itself shared from another google account), and it works but it showed the same problem: no column filters, this time, but while on the web browser last used column seems to be "U"

the JSON array returned by Google is like this:

,{"id":"T","label":"Recapiti Telefonici referente","type":"string"}
,{"id":"U","label":"Indirizzo email referente","type":"string"}
,{"id":"V","label":"","type":"string"}
,{"id":"W","label":"","type":"string"}
,{"id":"X","label":"","type":"string"}
,{"id":"Y","label":"","type":"string"}
,{"id":"Z","label":"","type":"string"}
,{"id":"AA","label":"","type":"string"}
,{"id":"AB","label":"","type":"string"}
,{"id":"AC","label":"","type":"string"}
,{"id":"AD","label":"","type":"string"}
,{"id":"AE","label":"","type":"string"}
,{"id":"AF","label":"","type":"string"}
,{"id":"AG","label":"","type":"string"}
,{"id":"AH","label":"","type":"string"}
,{"id":"AI","label":"","type":"string"}
,{"id":"AJ","label":"","type":"string"}
,{"id":"AK","label":"","type":"string"}
,{"id":"AL","label":"","type":"string"}
,{"id":"AM","label":"","type":"string"}
,{"id":"AN","label":"","type":"string"}
,{"id":"AO","label":"","type":"string"}
,{"id":"AP","label":"","type":"string"}
,{"id":"AQ","label":"","type":"string"}]

AQ being last column available in in the web browser, with apparently all rows empty. I don't know why...

As said above, we can't use those spreadsheets directly, because of the "dynamic rows"  issue we had due to Google Spreadsheet's "Column Filters" behaviour (see this post google spreadsheet: if the sheet has a filter set, teiid gets only filtered rows, any workaround? )

We use instead another Google Spreadsheet that "links" the original ones with an IMPORTRANGE formula, which is not affected by the above "dynamic rows" issue, although it has some minor drawback.

We just use the IMPORTRANGE formula to "import" columns really used, and that HAVE some text in the first cell, which can become a Column Label in Teiid... (A:U) in the above JSON example.

Another issue we had was that one Google Spreadsheet (the one shared from another google account), had two columns with identical "labels", and this caused another Issue:

2016-09-07 17:55:51,586 WARN  [org.teiid.RUNTIME] (Worker11_async-teiid-threads17)  TEIID50036 VDB Gforms.1 model "gss" metadata failed to load. 
Reason:TEIID60016 Duplicate Column gss.Answers.telephone: org.teiid.metadata.DuplicateRecordException: TEIID60016 Duplicate Column gss.Answers.telephone
    at org.teiid.metadata.MetadataFactory.addColumn(MetadataFactory.java:247)
    at org.teiid.translator.google.GoogleMetadataProcessor.addColumnsToTable(GoogleMetadataProcessor.java:98)
...

which we resolved asking the shared spreadsheet's owner to rename them, to have unique column names (which is better anyway, of course), and now it works.

while you're at it, could it be worth to work around similar issues using a sort of alias for duplicate column names, like appending an integer, or else?

eg: if another "telephone" column is found, log that on the server but continue creating the second column as "telephone_2", or "expr_1", like some SQL system do when a query result shows similar suplicate names for columns...

> Not possible right now as this is a WildFly issue, this is being worked on currently, will be available in future Teiid versions.

To expand on this, most operations involving resource adapters will simply require WildFly to reload rather than to take effect immediately. I think you could create an entirely new datasource with a new name / configuration rather than trying to modify the existing one.

Ok, I came to the same conclusion, and I just tried it, but even creating an entirely new datasource with a new name / configuration requres a "server reload", doable from the web interface under "runtime" or from jboss-cli....

Nut this still means that ALL resources, all VDBs, will be reloaded, so it has a shorter time span, perhaps, but it still disrupts ongoing usage of other connections, resources and VDBs...

At least if we could just "disable/enable" connections or resource adapters, without requiring a server reload... this is even more important with sources like Google spreadsheets, imho, since it is expected that some "web browser" user, unaware of the Teiid connection, could change something that he thinks would do no harm, but it could really break all Teiid usage of the resource, and to fix, we have to reload all the Teiid infrastructure... more: if that google connection builds a model in a VDB, and we "reuse" that model in another VDB, along with JDBC datasources perhaps, building joins in virtual VIEWS...  this could create issues and prevent also the correct loading of the "importing" VDB...

I was thinking of do that because we could need it... now I'm not so sure anymore...

>>Would be of any interest to Teiid inner working?

If Teiid does not match to what Google is expecting, then Yes. looks like September 14 they will start enforcing, so wait till that date and make sure if this is an issue with Teiid then we can take corrective actions. IMO, since Teiid already uses OAuth it does not affect.

I'm having connection problems in another thread, now... (Google spreadsheet: after spreadsheet structure change, rebuilt connection definition, errors, not working... )

could the above enforcing have something to do with the connection not working anymore...?