google spreadsheet VDB: I create a view but it doesn't work.
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
