NPE loading a google spreadsheet

[m.ardito]

Hi,

I am experimenting google spreadsheet datasource since long, mostly they work, but sometime still have troubles, because loading I get n error like:

16:41:11,345 WARN  [org.teiid.RUNTIME] (Worker6_async-teiid-threads7)  TEIID50036 VDB Gforms_ss_edp.1 model "RisposteModel" metadata failed to load. Reason:java.lang.NullPointerException: java.lang.NullPointerException

        at org.teiid.metadata.MetadataFactory.addColumn(MetadataFactory.java:242)

        at org.teiid.translator.google.GoogleMetadataProcessor.addColumnsToTable(GoogleMetadataProcessor.java:98)

        at org.teiid.translator.google.GoogleMetadataProcessor.addTable(GoogleMetadataProcessor.java:63)

        at org.teiid.translator.google.GoogleMetadataProcessor.process(GoogleMetadataProcessor.java:46)

        at org.teiid.translator.google.GoogleMetadataProcessor.process(GoogleMetadataProcessor.java:36)

        at org.teiid.translator.ExecutionFactory.getMetadata(ExecutionFactory.java:951)

        at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:96)

        at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:62)

        at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)

        at org.teiid.jboss.VDBService$6.run(VDBService.java:398)

        at org.teiid.jboss.VDBService$7.run(VDBService.java:449)

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

        at java.lang.Thread.run(Thread.java:745)

This spreadsheet worked before, but now it seems to cause a NPE for a addColumn method, apparently...

what does that mean? how can I solve this issue?

Marco

[rareddy]

Which version? Looks like label missing on the Google Sheet?

[m.ardito]

I used this vdb since 8.x, now is on 9.0.2.

The sheet has many columns, with labels on first row.

vbd is the same, a simple 3 column query...

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Gforms_ss_edp" version="1">
    <description>Gforms_ss</description>

    <property name="UseConnectorMetadata" value="true" />

    <model name="RisposteModel">
        <property name="importer.useFullSchemaName" value="false"/>
        <source name="gformsE" translator-name="google-spreadsheet-override" connection-jndi-name="java:/gssTest"/>
    </model>

    <model name="RisposteView" type="VIRTUAL">
            <metadata type="DDL">
                <![CDATA[
                    CREATE VIEW GView(
                                    f1 string,
                                    f2 string,
                                    f3 string
                    )
                     OPTIONS (materialized true,
                     "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
                     "teiid_rel:MATVIEW_TTL" 120000,
                     "teiid_rel:MATVIEW_PREFER_MEMORY" 'true',
                     "teiid_rel:MATVIEW_UPDATABLE" 'true',
                     "teiid_rel:MATVIEW_SCOPE" 'vdb')
                    AS
                    /*+ cache(ttl:120000) */

                    SELECT
                                    r1.field1,
                                    r1.field2,
                                    r1.field3
                    FROM
                                    (call RisposteModel.native('worksheet=EDP;query=SELECT A, B, C')) w,
                                    ARRAYTABLE(w.tuple COLUMNS "field1" string, "field2" string, "field3" string) AS r1
                    WHERE r1.field1 IS NOT NULL
                    ;
                ]]>
            </metadata>
    </model>

        <translator name="google-spreadsheet-override" type="google-spreadsheet">
                        <property name="SupportsDirectQueryProcedure" value="true"/>
        </translator>
</vdb>

If I create a new google spreadsheet, from scratch, and create a new connection definition to it, it seems to work.

But on my other spreadsheet it does not (anymore).

What can cause that error?

[m.ardito]

But even just this fails: both are "invalid"

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Gforms_ss_basic_edp" version="1">
    <description>Gforms_ss</description>

    <property name="UseConnectorMetadata" value="true" />

    <model name="RisposteModel">
        <property name="importer.useFullSchemaName" value="false"/>
        <source name="gformsE" translator-name="google-spreadsheet-override" connection-jndi-name="java:/gssTest"/>
    </model>

        <translator name="google-spreadsheet-override" type="google-spreadsheet">
                        <property name="SupportsDirectQueryProcedure" value="true"/>
        </translator>
</vdb>

[m.ardito]

Another update:

As said, I tried a new Google Spreadsheet, with the only default sheet ("sheet1"), filled with sample text data, and it worked. Let's call this "test"

But I can't (anymore) get data from another (with real data, coming from Google Forms, from extgernal users) that I am sure it worked before. Let's call this "Answers"

Now, I just  tried this trick: I put a special formula in cell A1 of  "Test"

=IMPORTRANGE("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";"A:Z")

where "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" is the long alphanumeric "key" string of the real data source, ie: "Answers"

This formula dynamically copies in "Test" all data I have in "Answers". It's a sort of linked range.

Now, Teiid can connect to "Test", and there I can see all data belonging to "Answers" in SquirrelSQL. But I can't connect Teiid directly to "Answers"...

Weird isn't it?

If this kind of link/formula will prove to be reliable, I could even try to work with that, but it has drawbacks, for us, and I would really understand what is not working with "Answers"... and work with it directly, if possible.

[rareddy]

com.google.gdata.core library version went from 1.0 in 8.12 to 1.47.1 in 9.0 in Teiid. Search if there are any issues due to that.

May be locally change the version back in your WildFly/Teiid installation, and see if corrects the issue, if it does then for some reason the new API does not work with existing old documents?

[m.ardito]

Sorry, I have no experience regarding this:

1) where could one start looking for issues related to com.google.gdata.core library versions?

I just found this, I'll try to search "issues"... but...

gdata-java-client/RELEASE_NOTES.txt at master · google/gdata-java-client · GitHub

...at least if I knew why that NPE is produced...?

2) I guess you suggest to try running my teiid 9.0.2 using an older library taken from an 8.12. If yes, which one?

I could think of

\modules\system\layers\dv\org\jboss\teiid\resource-adapter\google\main\connector-google-9.0.2.jar

\modules\system\layers\dv\org\jboss\teiid\translator\google\api\main\google-api-9.0.2.jar

How can I find the version of com.google.gdata.core library included in any teiid version?

[edit]

Anyway, it's weird, because I can use the link

https://docs.google.com/spreadsheets/d/<worksheet key>/gviz/tq

with the <worksheet key> of my old "answers" spreadsheet (not loading in teiid), and I get the JSON array representation of the spreadsheet...

and still it works with the new "test" which links the whole range of "answers" (I don't get the linking formula, I get "answers"'s cell values)

I kind of remember that internally teiid uses calls like those to get data from google spreadsheets...? or at least it did, and they seem to still work, both with old and new google spreadsheets...

[rareddy]

The gdata library is missing thus the issue IMO. If you have the 8.12 installation, then copy the contents "teiid-8.12.4/modules/system/layers/dv/org/jboss/teiid/translator/google/api/main" to "teiid-9.0.4/modules/system/layers/dv/org/jboss/teiid/translator/google/api/main" and check if that works? If yes, then create JIRA I will this missing library in the build and push to next to 9.0.x and 9.1 builds.

[m.ardito]

I probably found the cause... my "answers" sheet is retrieved by the google adapter in a way that includes an "empty" column at the end... I guess that Teiid tries to add that column, but it has no "label"...

and this causes the NPE on "org.teiid.metadata.MetadataFactory.addColumn(MetadataFactory.java:242)"

I'll try to make this understandable:

here is the last columns labels of my worksheet

if you note, las "used" column is BA, and it has a label, but someone (probably some colleague using the sheet by web interface) has inserted "column filters" until the next (empty) BB column...

In fact, using the syntax

https://docs.google.com/spreadsheets/d/<worksheet key>/gviz/tq

I can see that the JSON array returned represents the column like this

...

,{"id":"AW","label":"ASS: Assegnato a","type":"string"}

,{"id":"AX","label":"ASS: stato","type":"string"}

,{"id":"AY","label":"ASS: Aggiornato il","type":"string"}

,{"id":"AZ","label":"ASS: Note","type":"string"}

,{"id":"BA","label":"Num dip caricati","type":"date","pattern":"d/m/yy"}

,{"id":"BB","label":"","type":"string"}

...

Now, I guess Teiid is trying to convert this line

,{"id":"BB","label":"","type":"string"}

into a Column, but failing (it has no label), and thus causing a

"org.teiid.metadata.MetadataFactory.addColumn(MetadataFactory.java:242)"

This is alse reflected in my "=IMPORTRANGE(...)" formula, in the "test" spreadsheet:

- if I use =IMPORTRANGE("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";"A:BB"), Teiid fails loading the VDB , because it includes the BB column from the original "answers"

- if I use =IMPORTRANGE("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";"A:BA"), Teiid successfuly loads the VDB, because it excludes the BB column from the original "answers"

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

Note, doing some test to discover the above, I had to change that IMPORTRANGE formula, until I discovered that "BB" was the apparent cause of my NPE, but I also discovered that the only way I had to force Teiid to really "reload" the remote Google spreadsheet file is to completely stop Teiid, and then restart it from scratch (thus, interrupting all others perfectly valid VDBs I had...). I tried several other methods, to make Teiid re-read my remote spreadsheet

- that single VDB "reload" button, didn't work,

- disabling/enabling that single VDB xml didn't work

- undeploying/redeploying that single VDB xml didn't work

I had to stop and then start again the whole server... only this worked...

Is there any other way to "refresh" a gogle spreadsheet coonnection, other than this? That would be handy...

That said... should I still try the library swap you suggested above? Would it be useful/worth anyway?

[m.ardito]

Adding another thing, partly unrelated but maybe important

As said above, I use the syntax

https://docs.google.com/spreadsheets/d/<worksheet key>/gviz/tq

in a browser to verify the JSON data returned, and it works with all my spreadsheets (it seems to return only the first sheet, perhaps...)

Some sheets JSON answer is starting like this

/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1234567894","table":{"cols":[{"id":"A","label":"Timestamp","type":"datetime","pattern":"dd/MM/yyyy 

...and then goes on with JSON Aarrays of cells data

while on others I get a JSON answer starting like this

/*O_o*/
window.yzxqswi || window.alert("Access to Google Sheets data from this web page will soon start failing.  For information on fixing this, see https://g.co/sheets/3p-auth-changes.");window.yzxqswi = 1;
/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"78945612347","table":{"cols":[{"id":"A","label":"Timestamp","type":"datetime","pattern":"dd/MM/yyyy 

Note the line 2.

it points to

Google Apps Developer Blog: Enhanced third-party access protection for Google Sheets

Would be of any interest to Teiid inner working?

[rareddy]

>>Is there any other way to "refresh" a gogle spreadsheet coonnection, other than this? That would be handy...

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

>>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.

>>should I still try the library swap you suggested above? Would it be useful/worth anyway?

yes.

[m.ardito]

>>should I still try the library swap you suggested above? Would it be useful/worth anyway?

yes.

This is what I have now (latest 8.x I tried was 8.13.2)

As said earlier, atm on 9.0.2 google spreadsheets seem to work, I think my NPE was due to the google answer, because it returns an empty column.

So, the try would be replace all content above with what is below, restart Teiid, and then verify... if it still works?

[shawkins]

> The gdata library is missing thus the issue IMO

No, it's not supposed to be there.  See [TEIID-3945] Switch to more standard locations for google dependencies - JBoss Issue Tracker  and [TEIID-4070] Issues with resource adapters with api modules in wildfly - JBoss Issue Tracker

> So, the try would be replace all content above with what is below, restart Teiid, and then verify... if it still works?

No since you say 9.0.2 is working that is unnecessary.

> 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?

> 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.

[rareddy]

>No, it's not supposed to be there.

Are you saying gdata is no longer required or they just are in a different module? In a build from my local branch I do not see gdata library at all in the distribution.

[shawkins]

With TEIID-4070 it had to only be in the resource adapter, and with TEIID-3945 it's name changed to core-version.jar