5 Replies Latest reply on Feb 8, 2016 11:39 AM by Marco Ardito

    google spreadsheets vdb: I need some hint...

    Marco Ardito Master

      Hi,

       

      I'm exploring new ways, trying to get a dynamic VDB to a "test" google spreadsheet I've created in my google account.

      I tried several setups, but none worked so far, and I found no "complete" VBD example out there, so I am here to ask for some help...

       

      as for the connection, I referred to this page

      https://docs.jboss.org/author/display/teiid813final/Google+Spreadsheet+Data+Sources

       

      and tried this: always using

      - SpreadsheetName: I just put the document name as I see it in the google docs view, ie: "test" (without quotes)

      - BatchSize: specified the default 4096 value

       

      for auth types,

      - when I used ClientLogin, I also supplied

      > username: the account name (in this case the username is an email of my company)

      >- password: obviously, the correct google password for the above account


      - when I used OAuth2, I supplied the

      > RefreshToken: as I got from the curl command specified in the docs


      atm I have this config:

      <resource-adapter id="google">

              <module slot="main" id="org.jboss.teiid.resource-adapter.google"/>

              <connection-definitions>

                      <connection-definition class-name="org.teiid.resource.adapter.google.SpreadsheetManagedConnectionFactory" jndi-name="java:/googleDS" enabled="true" pool-name="googleDS">

                              <config-property name="AuthMethod">

                                      OAuth2

                              </config-property>

                              <config-property name="SpreadsheetName">

                                      test

                              </config-property>

                              <config-property name="BatchSize">

                                      4096

                              </config-property>

                              <config-property name="RefreshToken">

                                      <=== refresh token here ===>

                              </config-property>

                              <security>

                                      <application/>

                              </security>

                              <validation>

                                      <background-validation>false</background-validation>

                              </validation>

                      </connection-definition>

              </connection-definitions>

      </resource-adapter>


      then I reloaded the server, as it was noticed as needed.


      then I tried to create a dynamic VDB using this connection defined in the resource-adapter, but since I found no examples, tried to adapt a "twitter" example found in the quickstarts, to my best knowledge...

      like this:


      <?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" connection-jndi-name="java:/googleDS"/>

          </model>

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

              <metadata type="DDL">

                      <![CDATA[      

                          CREATE VIEW GView(

                                  f1 string,

                                  f2 string,

                                  f3 string

                          )

                                  AS

                          SELECT

                                  googlesheet1.id, googlesheet1.created_at, googlesheet1.text

                          FROM

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

                                  ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS googlesheet1;              

                      ]]>

              </metadata>

          </model>      

      </vdb>

       

      The VDB deploys fine ("success"), but it stays "loading", and the Summary > error field is filled with "TEIID60000 javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/googleDS"

       

      I can't guess any more what is wrong and why...

       

      here is the full stack trace I get from the logs after last time updating the vdb as you see above

       

      11:04:45,824 INFO  [org.jboss.as.server] (HttpManagementService-threads - 12)  JBAS015865: Replaced deployment "googless-vdb.xml" with deployment "googless-vdb.xml"

      11:04:45,806 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 2)  TEIID50030 VDB googless.1 model "googleView" metadata loaded. End Time: 08/02/16 11.04

      11:04:45,862 INFO  [org.jboss.as.repository] (HttpManagementService-threads - 12)  JBAS014901: Content removed from location /opt/teiid-8.12.3/standalone/data/content/d9/9cbb47dbfeacbcab310e7ad909eeef1b691ee4/content

      11:04:45,858 WARN  [org.teiid.RUNTIME] (teiid-async-threads - 4)  TEIID50036 VDB googless.1 model "sheet1" metadata failed to load. Reason:TEIID60000 javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/googleDS: org.teiid.translator.TranslatorException: TEIID60000 javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/googleDS

          at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:175) [teiid-api-8.12.3.jar:8.12.3]

          at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:194) [teiid-api-8.12.3.jar:8.12.3]

          at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:68) [teiid-engine-8.12.3.jar:8.12.3]

          at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:60) [teiid-engine-8.12.3.jar:8.12.3]

          at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55) [teiid-engine-8.12.3.jar:8.12.3]

          at org.teiid.jboss.VDBService$6.run(VDBService.java:395) [teiid-jboss-integration-8.12.3.jar:8.12.3]

          at org.teiid.jboss.VDBService$7.run(VDBService.java:446) [teiid-jboss-integration-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]

          at org.jboss.threads.JBossThread.run(JBossThread.java:122)

      Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/googleDS

          at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:421) [ironjacamar-core-impl-1.0.30.Final-redhat-1.jar:1.0.30.Final-redhat-1]

          at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:327) [ironjacamar-core-impl-1.0.30.Final-redhat-1.jar:1.0.30.Final-redhat-1]

          at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:510) [ironjacamar-core-impl-1.0.30.Final-redhat-1.jar:1.0.30.Final-redhat-1]

          at org.teiid.resource.spi.WrappedConnectionFactory.getConnection(WrappedConnectionFactory.java:58) [teiid-api-8.12.3.jar:8.12.3]

          at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:173) [teiid-api-8.12.3.jar:8.12.3]

          ... 10 more

      Caused by: javax.resource.ResourceException: IJ000658: Unexpected throwable while trying to create a connection: null

          at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:421) [ironjacamar-core-impl-1.0.30.Final-redhat-1.jar:1.0.30.Final-redhat-1]

          at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:453) [ironjacamar-core-impl-1.0.30.Final-redhat-1.jar:1.0.30.Final-redhat-1]

          at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:425) [ironjacamar-core-impl-1.0.30.Final-redhat-1.jar:1.0.30.Final-redhat-1]

          at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:354) [ironjacamar-core-impl-1.0.30.Final-redhat-1.jar:1.0.30.Final-redhat-1]

          ... 14 more

      Caused by: java.lang.NullPointerException

          at org.teiid.resource.adapter.google.SpreadsheetConnectionImpl.<init>(SpreadsheetConnectionImpl.java:60)

          at org.teiid.resource.adapter.google.SpreadsheetManagedConnectionFactory$1.getConnection(SpreadsheetManagedConnectionFactory.java:58)

          at org.teiid.resource.adapter.google.SpreadsheetManagedConnectionFactory$1.getConnection(SpreadsheetManagedConnectionFactory.java:55)

          at org.teiid.resource.spi.BasicManagedConnectionFactory.createManagedConnection(BasicManagedConnectionFactory.java:71) [teiid-api-8.12.3.jar:8.12.3]

          at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.createConnectionEventListener(SemaphoreArrayListManagedConnectionPool.java:834) [ironjacamar-core-impl-1.0.30.Final-redhat-1.jar:1.0.30.Final-redhat-1]

          at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:379) [ironjacamar-core-impl-1.0.30.Final-redhat-1.jar:1.0.30.Final-redhat-1]

          ... 17 more

        • 1. Re: google spreadsheets vdb: I need some hint...
          Ramesh Reddy Master

          Marco,

           

          For NPE you can open up a JIRA as it is always expecting the username and password in the configuration of the resource-adapter, irrespective of the authentication method. As per workaround you can supply a dummy user name and password for now.

           

          Ramesh..

          • 2. Re: google spreadsheets vdb: I need some hint...
            Marco Ardito Master

            Adding Username and Password fields (with real correct values) changes something but I still get an error , unfortunately.

             

            I disabled the vdb, disabled the connection, added username and password fields/values,

            then re-enabled the connection (success), reloaded server (both from web console and jboss-cli) (success),

            then re-enabled the vdb, and now it is FAILED, and status error log shows

            "TEIID31080 googleView.GView validation error: TEIID30352 GOOGLE_SOURCE.NATIVE does not exist."

            • 3. Re: google spreadsheets vdb: I need some hint...
              Ramesh Reddy Master

              Well that is progress, you no longer have a NullPointerException. Now did you add the "SupportsDirectQueryProcedure" translator override property as the document suggests?

              • 4. Re: google spreadsheets vdb: I need some hint...
                Marco Ardito Master

                > Now did you add the "SupportsDirectQueryProcedure" translator override property as the document suggests?

                well, I didn't.

                 

                Now I added that override, and as usual, disabled, re-enabled/ reloadd server, but still nothing changed

                I always get a "failed" state and "TEIID31080 googleView.GView validation error: TEIID30352 GOOGLE_SOURCE.NATIVE does not exist"

                 

                here is a screenshot showing current state. Could it be the view construction? I have poor experience doing this...

                 

                2016-02-08_16-49-38_Teiid Management .jpg

                here is the new stack trace after trying to deploy the vdb now:

                 

                17:06:22,052 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-3)  JBAS015876: Starting deployment of "googless-vdb.xml" (runtime-name: "googless-vdb.xml")

                17:06:22,074 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-4)  TEIID40118 VDB googless.1 added to the repository - is reloading false

                17:06:22,077 INFO  [org.teiid.RUNTIME] (MSC service thread 1-4)  TEIID50029 VDB googless.1 model "sheet1" metadata is currently being loaded. Start Time: 08/02/16 17.06

                17:06:22,081 INFO  [org.teiid.RUNTIME] (MSC service thread 1-4)  TEIID50029 VDB googless.1 model "googleView" metadata is currently being loaded. Start Time: 08/02/16 17.06

                17:06:22,082 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID50030 VDB googless.1 model "sheet1" metadata loaded. End Time: 08/02/16 17.06

                17:06:22,085 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 2)  TEIID50030 VDB googless.1 model "googleView" metadata loaded. End Time: 08/02/16 17.06

                17:06:22,093 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31080 googleView.GView validation error: TEIID30352 GOOGLE_SOURCE.NATIVE does not exist.

                17:06:22,098 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 2)  TEIID40073 The metadata for the VDB googless.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

                17:06:22,103 INFO  [org.jboss.as.server] (HttpManagementService-threads - 7)  JBAS015859: Deployed "googless-vdb.xml" (runtime-name : "googless-vdb.xml")

                17:06:22,105 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (teiid-async-threads - 2)  TEIID40003 VDB googless.1 is set to FAILED

                • 5. Re: google spreadsheets vdb: I need some hint...
                  Marco Ardito Master

                  Ok, looking at last stack trace I got the error, and now is ACTIVE. Still can't query it in squirrel, because I have to learn the correct data type conversion, but this is another issue... and one mine, not teiid's.

                  this is the vdb which has now "active" status...

                   

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

                                              f3 string

                                      )

                                              AS

                                      SELECT

                                              gs1.*

                                      FROM

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

                                              ARRAYTABLE(w.tuple COLUMNS "f1" string , "f2" string, "f3" string) AS gs1;               

                                  ]]>

                          </metadata>

                      </model>       

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

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

                          </translator>

                  </vdb>