1 2 Previous Next 19 Replies Latest reply on Sep 23, 2016 8:18 AM by Steven Hawkins

    NPE loading a google spreadsheet

    Marco Ardito Master

      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

        • 1. Re: NPE loading a google spreadsheet
          Ramesh Reddy Master

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

          • 2. Re: NPE loading a google spreadsheet
            Marco Ardito Master

            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?

            • 3. Re: NPE loading a google spreadsheet
              Marco Ardito Master

              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>
              
              • 4. Re: NPE loading a google spreadsheet
                Marco Ardito Master

                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.

                • 5. Re: NPE loading a google spreadsheet
                  Ramesh Reddy Master

                  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?

                  • 6. Re: NPE loading a google spreadsheet
                    Marco Ardito Master

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

                    • 7. Re: NPE loading a google spreadsheet
                      Ramesh Reddy Master

                      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.

                      • 8. Re: NPE loading a google spreadsheet
                        Marco Ardito Master

                        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?

                        • 9. Re: NPE loading a google spreadsheet
                          Marco Ardito Master

                          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?

                          • 10. Re: NPE loading a google spreadsheet
                            Ramesh Reddy Master

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

                            • 11. Re: NPE loading a google spreadsheet
                              Marco Ardito Master
                              >>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?

                              • 12. Re: NPE loading a google spreadsheet
                                Steven Hawkins Master

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

                                 

                                 

                                • 13. Re: NPE loading a google spreadsheet
                                  Ramesh Reddy Master

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

                                  • 14. Re: NPE loading a google spreadsheet
                                    Steven Hawkins Master

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

                                    1 2 Previous Next