5 Replies Latest reply on Feb 8, 2016 11:39 AM by m.ardito

google spreadsheets vdb: I need some hint...

m.ardito Feb 8, 2016 5:41 AM

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

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

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

<description>googless</description>

</model>

<![CDATA[

CREATE VIEW GView(

f1 string,

f2 string,

f3 string

)

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

rareddy Feb 8, 2016 9:21 AM (in response to m.ardito)

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..
Actions
2. Re: google spreadsheets vdb: I need some hint...

m.ardito Feb 8, 2016 10:33 AM (in response to rareddy)

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."
Actions
3. Re: google spreadsheets vdb: I need some hint...

rareddy Feb 8, 2016 10:38 AM (in response to m.ardito)

Well that is progress, you no longer have a NullPointerException. Now did you add the "SupportsDirectQueryProcedure" translator override property as the document suggests?
Actions
4. Re: google spreadsheets vdb: I need some hint...

m.ardito Feb 8, 2016 11:07 AM (in response to rareddy)

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

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
Actions
5. Re: google spreadsheets vdb: I need some hint...

m.ardito Feb 8, 2016 11:39 AM (in response to m.ardito)

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

Go to original post