6 Replies Latest reply on Apr 25, 2016 6:35 PM by alkarim.samji

    PI Integration with Teiid

    alkarim.samji

      I am trying to connect the PI JDBC adapter with Teiid data virtualization engine, and am running into some errors. Has anyone seen these errors before?

      Here are some details about the environment:

      • Teiid version 8.13.3
      • JDK version 1.8.0.77
      • Wildfly version 9.0.2
      • SQL DAS version 1.4.1.403
      • PI JDBC 1.4.1.404

      SQL DAS is running on a different server than the Teiid client node. I have deployed the following virtual database file after configuring the JDBC driver, but there is a parse exception on the model. Here is the VDB content:

      <vdb name="PIAF" version="1"> 
          <description>OSIsoft PI AF</description>   
          <model name="PIAF-Physical" type="PHYSICAL">
              <property name="importer.useFullSchemaName" value="false"/>
        <property name="importer.tableTypes" value="TABLE,VIEW"/>
              <source name="piaf-connector" translator-name="osisoft-pi" connection-jndi-name="java:/pi-ds"/>
          </model>
          <model name="PIAF-Virtual" type="VIRTUAL">
              <metadata type="DDL"><![CDATA[
              CREATE VIEW IceData (
                  Parent varchar(max),
         Attribute varchar(max),
         Timecol date,
                  Value bigdecimal
              ) AS 
                SELECT eh.Name as Parent, ea.Name as Attribute, a.Time as Timecol, a.Value FROM [Calgary Flames].[Asset].[ElementHierarchy] eh INNER JOIN [Calgary Flames].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID INNER JOIN [Calgary Flames].[Data].[Archive] a ON a.ElementAttributeID = ea.ID WHERE eh.Path = N'\' AND a.Time BETWEEN N'*-1h' AND N'*'  
              ]]> </metadata>
          </model>
      </vdb>
      

       

       

      Here is the error thrown by the JBoss server:

      15:13:11,453 WARN  [org.teiid.RUNTIME] (Worker0_async-teiid-threads1)  TEIID5003
      6 VDB PIAF.1 model "PIAF-Physical" metadata failed to load. Reason:TEIID60013 Du
      plicate Table ft_GetPIPoint: org.teiid.metadata.DuplicateRecordException: TEIID6
      0013 Duplicate Table ft_GetPIPoint
              at org.teiid.metadata.Schema.addTable(Schema.java:49)
              at org.teiid.metadata.MetadataFactory.addTable(MetadataFactory.java:220)
              at org.teiid.translator.jdbc.JDBCMetdataProcessor.addTable(JDBCMetdataPr
      ocessor.java:344)
              at org.teiid.translator.jdbc.JDBCMetdataProcessor.addTable(JDBCMetdataPr
      ocessor.java:327)
              at org.teiid.translator.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataP
      rocessor.java:300)
              at org.teiid.translator.jdbc.JDBCMetdataProcessor.getConnectorMetadata(J
      DBCMetdataProcessor.java:159)
              at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecut
      ionFactory.java:295)
              at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecut
      ionFactory.java:68)
              at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeM
      etadataRepository.java:92)
              at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(Native
      MetadataRepository.java:60)
              at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(Chai
      ningMetadataRepository.java:55)
              at org.teiid.jboss.VDBService$6.run(VDBService.java:395)
              at org.teiid.jboss.VDBService$7.run(VDBService.java:446)
              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkCon
      text.java:276)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.ru
      n(ThreadReuseExecutor.java:119)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseE
      xecutor.java:210)
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.
      java:1142)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor
      .java:617)
              at java.lang.Thread.run(Thread.java:745)
      
        • Re: PI Integration with Teiid
          gregor

          Hello Alkarim,

           

          Teiid appears to be a Linux product, correct?

          PI JDBC Driver 2015 (1.4.1.404) is available for Windows only. You can try PI JDBC Driver 2012 (1.3.0.0251)

            • Re: PI Integration with Teiid
              alkarim.samji

              Hi Gregor - no, Teiid is cross-platform and works with both Linux and Windows. I've got it installed on a Windows Server 2012 environment.

                • Re: PI Integration with Teiid
                  gregor

                  Hello Alkarim,

                   

                  Thank you for the clarification. The JBOSS response you posted is a warning and it somehow doesn't appear to correspond with the VDB content you posted above. The SELECT to create your IceData VIEW doesn't refer ft_GetPIPoint Additionally the complain is about an attempt to add a duplicate table. This looks kind of weird and somehow unrelated, are both belonging together?

                   

                  Have you executed the CREATE VIEW command before? Does the View possibly exist already and therefore cannot be created? Is "CREATE or REPLACE VIEW" supported?

                   

                  Can you please enable PI JDBC logging by setting the driver properties LogFile and LogLevel (please see PI JDBC Driver 2015 Administrator Guide page 13 for details)

                   

                  As a more general test, please run getSnap and getEASnap to verify if the PI JDBC Driver installation is good. Both are described in PI JDBC Driver 2015 Administrator Guide.

              • Re: PI Integration with Teiid
                Marcos Vainer Loeff

                Hello Alkarim,

                 

                I have never installed or used Teiid. Nevertheless, here are some suggestions that came into my mind.

                 

                1) Are you able to run the samples provided by the PI JDBC on the machines with Teiid installed?

                2) Are you using the same credentials?

                3) Can you create a Java application with the same query you wrote on Teiid and make sure if this works fine?

                4) Are you sure you have referenced the jar file from PI JDBC properly on Teiid?

                5) Are you receiving this error for all queries? If you write a simple (without any JOIN), query does it work properly?

                • Re: PI Integration with Teiid
                  bshang

                  I've never used TEIID but the source code here teiid/Schema.java at master · teiid/teiid · GitHub and the exception mention duplicate table names so I'd check first if you are adding an existing table to the map. You may also consider logging this with TEIID support.

                  • Re: PI Integration with Teiid
                    alkarim.samji

                    Gregor, Marcos, and Barry,

                    Thanks for your responses. I have verified that the GetSnap and GetEASnap test applications work well, and have used the JDBC driver in a sample application otherwise as well. I was successfully able to run both OLEDB and OLEDB Enterprise queries through the DBVisualizer tool. I am using the same credentials in all cases. The JDBC adapter is deployed on Teiid properly as I see a successful deployment message in the logs (I had to jump through some hoops to get this part working initially). Per Marcos' suggestion I tried a more simple query, one that did not involve any joins and I got the same error (SELECT Name FROM [Calgary Flames].[Asset].[ElementHierarchy] eh). I also tried running a simple query for PI OLEDB (select tag from pipoint.pipoint where tag='sinusoid') and it was successful. This leads me to believe that the Teiid "translator" for PI queries may be calling the ft_GetPIPoint on its own as it restructures the queries that are passed in before submitting them to the data source, which would be a bug in the Teiid translator. I will look at the source code link you provided and reach out to Teiid support on their forums to see if I can get an answer.

                    Thanks for your help!