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

    google spreadsheets generated by google forms: error

    m.ardito
    m.ardito Mar 7, 2016 10:07 AM

      Hi,

      (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

       

      Now,

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

          <description>Esempio</description>

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

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

              <metadata type="DDL">

                      <![CDATA[     

                          CREATE VIEW GView(

                                  f1 string,

                                  f2 string

                          )

                                  AS

                          SELECT

                                  r1.field2,

                                  r1.field3

                          FROM

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

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

                      ]]>

              </metadata>

          </model>     

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

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

              </translator>

      </vdb>

       

      - 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 TABLE Risposte (

      domanda1 string OPTIONS (NAMEINSOURCE 'domanda1', UPDATABLE FALSE, NATIVE_TYPE 'STRING'),

      "Timestamp" timestamp OPTIONS (NAMEINSOURCE 'Timestamp', UPDATABLE FALSE, NATIVE_TYPE 'DATETIME'),

      domanda2 string OPTIONS (NAMEINSOURCE 'domanda2', UPDATABLE FALSE, NATIVE_TYPE 'STRING')

      ) OPTIONS (NAMEINSOURCE 'Risposte', UPDATABLE TRUE);

       

      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

      )

      AS

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

       

      Marco