1 2 Previous Next 27 Replies Latest reply on Jun 1, 2016 10:03 AM by asjboss1 Go to original post
      • 15. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
        asjboss1

        Hi Ramesh,

         

        For #1 I created the following issue: TEIID-4184 - Timestamps need to be submitted without supporting {ts} for OSI PI translator

        For #2 I used the workaround of implementing CAST(field as varchar) to get around the first problem but I ran into another issue with Table valued functions (TVFs). The AF logical database contains helper functions which help "transpose" time-series data into a relational, asset-centric format. It looks like once these functions are created, even if they are deleted afterwards somehow there is a reference that remains to them. The Teiid engine tries to look for columns but whereas PI tools can see these columns, Teiid cannot. See screen shot below from the tool PI SQL Commander which shows the schema of the table where the error is being thrown. Not sure if anything can be done about this error?

        Example query:

          SELECT eh.Path, eh.Name, ea.Name, a.Time, a.Value 
          FROM "PIAF-Physical"."Calgary Flames"."Asset"."ElementHierarchy" eh 
          INNER JOIN "PIAF-Physical"."Calgary Flames"."Asset"."ElementAttribute" ea ON CAST(ea.ElementID as varchar) = CAST(eh.ElementID as varchar)
          INNER JOIN "PIAF-Physical"."Calgary Flames"."Data"."Archive" a ON CAST(a.ElementAttributeID as varchar)= CAST(ea.ID as varchar)
          WHERE eh.Path = N'\' 
          AND a.Time BETWEEN N'*-1h' AND N'*'
        

        Error:

        DB PIAF.1 model "PIAF-Physical" metadata loaded. End Time: 5/2/16 7:49 PM
        19:49:04,875 WARN  [org.teiid.PLANNER.RESOLVER] (Worker0_async-teiid-threads1)
        TEIID31071 Invalid table; Table PIAF-Physical.Calgary Flames.DataT.ft_TransposeA
        rchive_Temp Map has no columns defined
        19:49:04,875 WARN  [org.teiid.PLANNER.RESOLVER] (Worker0_async-teiid-threads1)
        TEIID31071 Invalid table; Table PIAF-Physical.Calgary Flames.DataT.ft_TransposeI
        nterpolateRange_Temp Map has no columns defined
        19:49:04,875 WARN  [org.teiid.PLANNER.RESOLVER] (Worker0_async-teiid-threads1)
        TEIID31071 Invalid table; Table PIAF-Physical.Calgary Flames.DataT.ft_vTranspose
        Archive_Temp Map has no columns defined
        19:49:04,875 WARN  [org.teiid.PLANNER.RESOLVER] (Worker0_async-teiid-threads1)
        TEIID31071 Invalid table; Table PIAF-Physical.SAGD Facility Example.DataT.ft_Tra
        nsposeArchive_Well Pad Overview Template has no columns defined
        19:49:04,891 WARN  [org.teiid.PLANNER.RESOLVER] (Worker0_async-teiid-threads1)
        TEIID31071 Invalid table; Table PIAF-Physical.StatoilTest.DataT.ft_TransposeArch
        ive_ProducerWell_1DAVG has no columns defined
        19:49:04,891 WARN  [org.teiid.PLANNER.RESOLVER] (Worker0_async-teiid-threads1)
        TEIID31071 Invalid table; Table PIAF-Physical.StatoilTest.DataT.ft_TransposeInte
        rpolateRange_ProducerWell_1DAVG has no columns defined
        19:49:04,891 INFO  [org.teiid.RUNTIME] (Worker0_async-teiid-threads1)  TEIID4007
        3 The metadata for the VDB PIAF.1 is loaded, however it is not valid. Check mode
        ls for errors. Correct the metadata and re-deploy.
        19:49:04,891 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker0_async-teiid
        -threads1)  TEIID40003 VDB PIAF.1 is set to FAILED
        

        Schemas as seen through SQL Commander:

        Schemas.png

        Schema as seen through DBVisualizer:

        Schema2.png

        Finally for #2b, I did notice other items that are not supported either, like the CROSS APPLY statement. Would you like me to raise an enhancement to support a bunch of these functions? I attached the OLEDB Enterprise User Guide which shows the valid SQL syntax for these types of queries.

        • 16. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
          shawkins

          > Not sure if anything can be done about this error?

           

          This would require specific logic in the importer to recognize table valued functions and then use a separate logic to find the columns - as they don't seem to be exposed by standard JDBC DatabaseMetaData.  At the very list we could filter the table valued functions from import so that the process doesn't error.

           

          Can you log something for this and the earlier error with getting the primary keys?

           

          > Finally for #2b, I did notice other items that are not supported either, like the CROSS APPLY statement.

           

          Cross apply in Teiid is handled by the ansi nested table syntax:

           

          select .. from x, TABLE(call table_function(x.y...)) as t

           

          However we don't currently push down these joins - that is something that could be added though in which case we would have the translator use the CROSS APPLY syntax.

           

          So the enhancements would need to cover -  import table valued functions as Teiid procedures, allow the pushdown of nested table joins, and update the PI translator to use the CROSS APPLY syntax.

          • 18. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
            rareddy

            Is there any demo PI server we can you suggest for our testing?

            • 19. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
              asjboss1

              Ramesh - unfortunately, not that I know of. We have a PI server at our office but due to sensitive information we cannot give you access to it. I am checking with my supervisors to determine if there is any alternative whereby we can set up a test server but there may be licensing issues. I'll let you know if something is possible.

               

              OSIsoft offers membership to their "Developers Club" where you can download and install the PI server in your environment, or use the PI server in the cloud. However, there is a charge for it. A yearly charge is posted but it may be possible to get the subscription for a monthly duration. The sales team may provide a reduced cost to you since this is for an open source project. See https://pisquare.osisoft.com/docs/DOC-1101

              • 20. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                asjboss1

                Ramesh, I spoke to my supervisor and there are a few options where we can help setup a test environment. Do you guys have a VM that we can access? If so we can setup a demo installation for you on that server. Let me know.

                • 21. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                  rareddy

                  I do not have externally exposed VM, how about we set up Amazon AWS account? can this be installed on RHEL instance?

                  • 22. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                    asjboss1

                    Sure, Amazon AWS would work. Let me know of the server details and I can set this up. This cannot be installed on RHEL as only Windows is supported. FYI - I am away for a couple of weeks at end of this week so will be slow to respond in that time.

                    • 23. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                      rareddy

                      Cool, let me setup a Amazon AWS based Windows image, then will get in touch with you with details.

                      • 24. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                        asjboss1

                        Hi Ramesh, I'm back from holidays. I was wondering if you had a chance to setup that Amazon server so that we could put PI on there?

                        • 25. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                          rareddy

                          Sorry, have not got around to do that, will try to do that. Can you send me your email in direct email to me, as I do not want share details for AWS instance on a public forum.

                          • 26. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                            rareddy

                            I have the instance ready, I created Windows 2012 Server with 16 GB RAM and 30 GB space. Let me know if that works, and send me your direct email for me to send details.

                            • 27. Re: Teiid PI JDBC Connection Error - Null Pointer Exception
                              asjboss1

                              Hi Ramesh, I have sent you an invite on Google Hangouts. I will try and setup the necessary software on the instance this week. The specs you provided are sufficient.

                              1 2 Previous Next