10 Replies Latest reply on Mar 24, 2016 12:47 PM by m.ardito

    google spreadsheets generated by google forms: error



      (teiid 8.12.3/eap)


      I'm strugglling to understand what is not working in this particular case, a google spreadhseet generated by (and linked to) a google form, which collects all anwsers to the form, one answer per row.

      For the same google account, I have other spreadsheets, generated by me not by google forms, which work as expected, but this generated one is generating issues and does not work.


      This spreadsheet

      - is created from the form, the first time trying to "view responses in sheets", and then any new reponse generates a new line filled.

      - has one sheet, which I renamed from the generated one since it had spaces in its name, but even after this rename, further form compilations, fill a new row in the same renamed sheet, as expected.

      - has the first column named "Timestamp", containing the timestamps of each form compilation, in localized timestamp format, (dd/MM/yyyy hh:mm:ss, eg: 07/03/2016 13:50:23)

      - has other columns, named as "question names" (as defined in the google form), containing data corresponing to what the form question type asked



      - I created a google RA connection definition for this file, using the same access config as other working similar spreadsheets

      - I created a VDB using that RA connection definition to create a model, and also a view, as in other similar spreadsheets


      When I try to load this VDB, it looks active, but as I try to use the vdb in squirrel:

      - the rowcount is retrieved (6 rows)

      - the content is not retrieved, but an error is generated in theserver log, like


      From my findings, I guess the the issue is because the expected format (yyyy-mm-dd hh:mm:ss) is not respected in the google spreadsheet, since google forms uses another one (dd/MM/yyyy hh:mm:ss)... and even if set to the expected one, new rows from forms compilation will have the original format... so it can't be forced.


      I think the problem is in the source model, and not in the view, because if he view tries to get only columns B,C (so skipping the A where Timestamp is generated), the vdb loads, but then fails at retrieving content


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

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


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

          <model name="EsempioModel">

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

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


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

              <metadata type="DDL">


                          CREATE VIEW GView(

                                  f1 string,

                                  f2 string







                                  (call EsempioModel.native('worksheet=Risposte;query=SELECT B, C')) w,

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




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

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




      - the content is not retrieved, but an error is generated in theserver log, like


      15:39:13,224 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue23) i7rF+bCR3N+o TEIID30004 Connector returned a 0 row non-last batch: gformsE.

      15:39:13,226 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue25) i7rF+bCR3N+o Connector worker process failed for atomic-request=i7rF+bCR3N+o.6.0.5: org.teiid.core.types.TransformationException: TEIID10059 Failed to transform String to Timestamp.  Expected format = yyyy-mm-dd hh:mm:ss.fffffffff for Date(2016,2,7,11,57,57)

              at org.teiid.core.types.basic.StringToTimestampTransform.transformDirect(StringToTimestampTransform.java:63) [teiid-common-core-8.12.3.jar:8.12.3]

              at org.teiid.core.types.Transform.transform(Transform.java:47) [teiid-common-core-8.12.3.jar:8.12.3]

              at org.teiid.core.types.DataTypeManager.transformValue(DataTypeManager.java:942) [teiid-common-core-8.12.3.jar:8.12.3]

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

              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.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_91]

              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar: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.$Proxy62.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]

      Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

              at java.sql.Timestamp.valueOf(Timestamp.java:202) [rt.jar:1.7.0_91]

              at org.teiid.core.types.basic.StringToTimestampTransform.transformDirect(StringToTimestampTransform.java:58) [teiid-common-core-8.12.3.jar:8.12.3]

              ... 22 more


      15:39:13,235 WARN  [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue26) i7rF+bCR3N+o TEIID30020 Processing exception for request i7rF+bCR3N+o.6 'TEIID10059 Failed to transform String to Timestamp.  Expected format = yyyy-mm-dd hh:mm:ss.fffffffff for Date(2016,2,7,11,57,57)'. Originally TransformationException 'Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]' Timestamp.java:202. Enable more detailed logging to see the entire stacktrace.



      generated DDL is

      - for the model








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



      - for the view


      CREATE VIEW GView (

      f1 string,

      f2 string



      SELECT r1.field2, r1.field3 FROM (EXEC EsempioModel.native('worksheet=Risposte;query=SELECT B, C')) AS w, ARRAYTABLE(w.tuple COLUMNS field2 string, field3 string) AS r1;



      Am I doing something wrong, is this kind of forms-linked google spreadsheet unsupported (can it be in future?) or is any other issue/error preventing this to work?


      [edit]btw I also tried to create a regular spreadhseet with identical layout but a first column timestamp formatted as string, and as yyyy-mm-dd hh:mm:ss, and this seems to load regularly... so I guess my issue is due to a parsing problem... [/edit]


      Attached you'll find a screenshot of the spreadsheet as seen in the browser view...