14 Replies Latest reply on Mar 24, 2016 12:40 PM by Marco Ardito

    google spreadsheet VDB: I create a view but it doesn't work.

    Marco Ardito Master

      After solving connection issues (see google spreadsheets vdb: I need some hint...), I have now a valid connection to a private google spreadsheet.

       

      I am using a "test" spreadsheet, very simple, in order to figure out its basic working.

      The spreadhseet

      - is named "test"

      - has a single sheet, named "sheet1"

      - has 3 columns, (a,b,c) with values

      - has data in 5 rows (all string data), ie: strings like "value1", "value 2"... etc.

       

      This is my current VDB source:

       

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

      <vdb name="googless" version="1">

          <description>googless</description>

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

          <model name="sheet1">

              <property name="importer.useFullSchemaName" value="false"/>

              <source name="googlesheet1" translator-name="google-spreadsheet-override" connection-jndi-name="java:/googleDS"/>

          </model>

          <model name="googleView" type="VIRTUAL">

              <metadata type="DDL">

                      <![CDATA[       

                          CREATE VIEW GView(

                                  f1 string,

                                  f2 string

                          )

                                  AS

                          SELECT

                                  gs1.x1, gs1.x2

                          FROM

                                  (call sheet1.native('worksheet=Sheet1;query=SELECT A, B, C')) w,

                                  ARRAYTABLE(w.tuple COLUMNS x1 string , x2 string, x3 string) AS gs1;               

                      ]]>

              </metadata>

          </model>       

              <translator name="google-spreadsheet-override" type="google-spreadsheet">

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

              </translator>

      </vdb>

       

      In squirrel I get the "sheet1" and I can see and query all values from all columns.

       

      in the web console I see this DDL generated for the "sheet1" model

      =====================================================

      CREATE FOREIGN TABLE Sheet1 (

      A string OPTIONS (NAMEINSOURCE 'A', NATIVE_TYPE 'STRING'),

      B string OPTIONS (NAMEINSOURCE 'B', NATIVE_TYPE 'STRING'),

      C string OPTIONS (NAMEINSOURCE 'C', NATIVE_TYPE 'STRING')

      ) OPTIONS (NAMEINSOURCE 'Sheet1');

       

      CREATE FOREIGN PROCEDURE native(IN request string NOT NULL OPTIONS (ANNOTATION 'The native query to execute'), VARIADIC variable object OPTIONS (ANNOTATION 'Any number of varaibles; usage will vary by translator')) RETURNS TABLE (tuple object[])

      OPTIONS (ANNOTATION 'Invokes translator with a native query that returns results in an array of values')

      =====================================================

       

      The "GView" , instead view appears in the squirrel tree, but

      - it show no values in "content", but just three headers (a,b,c)

      - it show two columns in "columns", (f1, f2)

       

      this is the web console reported DDL:

       

      =====================================================

      CREATE VIEW GView (

      f1 string,

      f2 string

      )

      AS

      SELECT gs1.x1, gs1.x2 FROM (EXEC sheet1.native('worksheet=Sheet1;query=SELECT A, B, C')) AS w, ARRAYTABLE(w.tuple COLUMNS x1 string, x2 string, x3 string) AS gs1;

      =====================================================

       

      If I issue a simple query like

      "select * from GView" I get an error in squirrel,

      Error: org.teiid.core.TeiidException

      SQLState:  38000

      ErrorCode: 0

       

      and a correspondent a stack trace reported below

       

      18:53:33,754 ERROR [org.teiid.CONNECTOR] (Worker10_QueryProcessorQueue284) T9y/6WvLuzhN Connector worker process failed for atomic-request=T9y/6WvLuzhN.35.4.1075: java.lang.UnsupportedOperationException

              at java.util.AbstractList.set(AbstractList.java:132) [rt.jar:1.7.0_91]

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.correctTypes(ConnectorWorkItem.java:559)

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:428)

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:220)

              at sun.reflect.GeneratedMethodAccessor99.invoke(Unknown Source) [:1.7.0_91]

              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_91]

              at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_91]

              at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)

              at com.sun.proxy.$Proxy46.more(Unknown Source)

              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:309)

              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)

              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)

              at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_91]

              at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)

              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) [rt.jar:1.7.0_91]

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_91]

              at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_91]

       

      18:53:33,802 ERROR [org.teiid.PROCESSOR] (Worker9_QueryProcessorQueue285) T9y/6WvLuzhN TEIID30019 Unexpected exception for request T9y/6WvLuzhN.35: java.lang.UnsupportedOperationException

              at java.util.AbstractList.set(AbstractList.java:132) [rt.jar:1.7.0_91]

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.correctTypes(ConnectorWorkItem.java:559) [teiid-engine-8.12.3.jar:8.12.3]

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:428) [teiid-engine-8.12.3.jar:8.12.3]

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:220) [teiid-engine-8.12.3.jar:8.12.3]

              at sun.reflect.GeneratedMethodAccessor99.invoke(Unknown Source) [:1.7.0_91]

              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_91]

              at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_91]

              at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) [teiid-engine-8.12.3.jar:8.12.3]

              at com.sun.proxy.$Proxy46.more(Unknown Source)

              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:309) [teiid-engine-8.12.3.jar:8.12.3]

              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112) [teiid-engine-8.12.3.jar:8.12.3]

              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-8.12.3.jar:8.12.3]

              at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_91]

              at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) [teiid-engine-8.12.3.jar:8.12.3]

              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.3.jar:8.12.3]

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.3.jar:8.12.3]

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.3.jar:8.12.3]

              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_91]

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_91]

              at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_91]

       

      What am I doing wrong? Where to look (in my model, but also in docs)?

       

      Marco

        • 1. Re: google spreadsheet VDB: I create a view but it doesn't work.
          Steven Hawkins Master

          I don't think you are doing something wrong.  The issue is that the translator is using Collections.singletonList to return the row and the engine is expecting it to be modifiable to avoid the additional copy one the value types are corrected.  The SingletonList instance returned by the runtime unfortunately doesn't support the set method.  Can you log something for this.

          • 2. Re: google spreadsheet VDB: I create a view but it doesn't work.
            Marco Ardito Master

            I'm sure you're right, but I'm a bit lost...

            I can't understand what you mean by "the additional copy", but it could be fine, as I'm not into teiid that much

            maybe if I could only "expose" the view (GView), and not the raw source (sheet1) at all in the model, that would not create an "additional copy"?

            In the end I think I _could_ need just to expose the view (GView) and not the sheet1 source, if this could solve this issue.

             

            [edit] my guess was wrong, hiding the sheet1 model (<model name="sheet1" visible="false" >) exposes just the Gview, but I get the same error, apparenlty... [/edit]

             

            Anyway, I didn' teven understand what you mean by "Can you log something for this", are you meaning if I can add some more teiid log form my server, in order to get more details?

            Or you wish me to log an issue in the tracker as a base to maybe work on this "Collections.singletonList" issue you found?

            I can do that, just advice would be better log this as a "bug", instead of "feature request" or "enhancement"?

             

             

            Marco

            • 3. Re: google spreadsheet VDB: I create a view but it doesn't work.
              Steven Hawkins Master

              > I can do that, just advice would be better log this as a "bug", instead of "feature request" or "enhancement"?

               

              Sorry I left out the details - this is a bug.  The usage of Collections.singletonList to return the native procedure results gives the engine an unmodifable list that the causes the exception you saw.

              • 5. Re: google spreadsheet VDB: I create a view but it doesn't work.
                Steven Hawkins Master

                This will now work in 8.13.1 (to be released later this week) and 9.0 Alpha1 (sometime next week).

                • 6. Re: google spreadsheet VDB: I create a view but it doesn't work.
                  Marco Ardito Master

                  ...awsome! did I ever mention that I LOVE teiid and its developers???

                   

                  Thanks so much, can't wait for the next release... I'll update the status of my test vdb...

                   

                  Marco

                  • 7. Re: google spreadsheet VDB: I create a view but it doesn't work.
                    Steven Hawkins Master

                    Thanks, for the compliment.  We appreciate you working with us on getting these issues figured out.

                    • 8. Re: google spreadsheet VDB: I create a view but it doesn't work.
                      Marco Ardito Master

                      I'm sorry but the same setup does not work at all in 8.13.* vdb does not even load...

                       

                      on 8.13.1

                      edit: also on 9.0.0.alpha1 same error

                       

                      I get

                      TEIID50036 VDB googless.1 model "sheet1" metadata failed to load. Reason:org.teiid.resource.adapter.google.SpreadsheetConnectionImpl cannot be cast to org.teiid.resource.adapter.google.GoogleSpreadsheetConnection: java.lang.ClassCastException: org.teiid.resource.adapter.google.SpreadsheetConnectionImpl cannot be cast to org.teiid.resource.adapter.google.GoogleSpreadsheetConnection

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

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

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

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

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

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

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

                          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)

                       

                      on 8.13.2 I get

                      TEIID50036 VDB googless.1 model "sheet1" metadata failed to load. Reason:TEIID31097 Metadata load requires a connection factory: TEIID30481 Failed to find the Connection Factory with JNDI name java:/googleDS. Please check the name or deploy the Connection Factory with specified name.: org.teiid.translator.TranslatorException: TEIID31097 Metadata load requires a connection factory

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

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

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

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

                          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)

                       

                      btw I'm creating the RA with a script the same for all my tests:

                      ================================

                      /subsystem=resource-adapters/resource-adapter=google/connection-definitions=googleDS:add(jndi-name=java:/googleDS,class-name=org.teiid.resource.adapter.google.SpreadsheetManagedConnectionFactory, enabled=true, use-java-context=true)

                      /subsystem=resource-adapters/resource-adapter=google/connection-definitions=googleDS/config-properties=AuthMethod:add(value=OAuth2)

                      /subsystem=resource-adapters/resource-adapter=google/connection-definitions=googleDS/config-properties=BatchSize:add(value=4096)

                      /subsystem=resource-adapters/resource-adapter=google/connection-definitions=googleDS/config-properties=RefreshToken:add(value=1/xxxxxxxxxxxxxxxxxxxxxxxxxxxxx)

                      /subsystem=resource-adapters/resource-adapter=google/connection-definitions=googleDS/config-properties=SpreadsheetName:add(value=test)

                      /subsystem=resource-adapters/resource-adapter=google/connection-definitions=googleDS/config-properties=Username:add(value=xxxxxx)

                      /subsystem=resource-adapters/resource-adapter=google/connection-definitions=googleDS/config-properties=Password:add(value=xxxxxx)

                      ================================

                      and the VDB is

                      ================================

                      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

                      <vdb name="googless" version="1">

                          <description>googless</description>

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

                          <model name="sheet1">

                              <property name="importer.useFullSchemaName" value="false"/>

                              <source name="googlesheet1" translator-name="google-spreadsheet-override" connection-jndi-name="java:/googleDS"/>

                          </model>

                          <model name="googleView" type="VIRTUAL">

                              <metadata type="DDL">

                                      <![CDATA[     

                                          CREATE VIEW GView(

                                                  f1 string,

                                                  f2 string

                                          )

                                                  AS

                                          SELECT

                                                  gs1.field1, gs1.field2

                                          FROM

                                                  (call sheet1.native('worksheet=Sheet1;query=SELECT A, B, C')) w,

                                                  ARRAYTABLE(w.tuple COLUMNS "field1" string , "field2" string, "field3" string) AS gs1;             

                                      ]]>

                              </metadata>

                          </model>     

                              <translator name="google-spreadsheet-override" type="google-spreadsheet">

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

                              </translator>

                      </vdb>

                      ================================

                       

                      the above script and vdb load nicely (although I have to reload the server) in 8.12.3

                       

                      Marco

                      • 9. Re: google spreadsheet VDB: I create a view but it doesn't work.
                        Steven Hawkins Master

                        Seems like that is a regression with the switch over to WildFly.  All of my testing was done with embedded, which didn't exhibit this classloading issue.  Another fix will be required to address this.

                        • 10. Re: google spreadsheet VDB: I create a view but it doesn't work.
                          Steven Hawkins Master

                          This was captured as [TEIID-4070] Issues with resource adapters with api modules in wildfly - JBoss Issue Tracker

                           

                          There will be an 8.13.3 with the console changes as well by the end of next week.  Until then you could just use local build.

                          • 11. Re: google spreadsheet VDB: I create a view but it doesn't work.
                            Marco Ardito Master

                            Ok, thanks.

                             

                            I'm sorry recently I had some problems understanding new 8.13 server bundle ([TEIID-4070] Issues with resource adapters with api modules in wildfly - JBoss Issue Tracker ), which I hope didn't made me wrong making assumptions... I will try all new releases all over and retry, just to be sure.

                             

                            Anyway, I saw you made some commit so it was probably real.

                             

                            Marco

                            • 12. Re: google spreadsheet VDB: I create a view but it doesn't work.
                              Marco Ardito Master

                              I tried this vdb in  teiid 9.0.0.alpha2 and, at first it failed before I noticed that now

                              "AuthMethod: Method to access Google. This property can only be OAuth2"

                               

                              so I removed previoulsy needed (despite the OAuth2 setting) Username/Password properties

                              and now it works! I see both the raw model and the view. Good job!

                               

                              I'll wait next 8.13.3 to mark this as compeltely resolved, since 9 alpha is not stable...

                               

                              Marco

                              • 13. Re: google spreadsheet VDB: I create a view but it doesn't work.
                                Steven Hawkins Master

                                > so I removed previoulsy needed (despite the OAuth2 setting) Username/Password properties

                                and now it works!

                                 

                                Yes, google removed the usage of client login for authentication, so I removed it with this change as well.

                                 

                                > I'll wait next 8.13.3 to mark this as compeltely resolved, since 9 alpha is not stable...

                                 

                                8.13.3 should be out in the next couple of days.

                                • 14. Re: google spreadsheet VDB: I create a view but it doesn't work.
                                  Marco Ardito Master

                                  Confirmed working on 8.13.3 stable

                                   

                                  Thanks

                                  Marco