OSISoft PI Translator (osisoft-pi)

Also see common JDBC Translator Information

The OSISoft Translator, known by the type name osisoft-pi, is for use with OSIsoft PI OLEDB Enterprise. This translator uses the JDBC driver provided by the OSISoft.

Usage

You can develop a VDB like follows to fetch metadata from PI and give you access to executing queries against PI.

pi-vdb.xml

<vdb name="pi" version="1">
    <model name="AF">
        <property name="importer.importProcedures" value="true"/>
        <source connection-jndi-name="java:/pi-ds" name="pi-connector" translator-name="osisoft-pi"/>
    </model>
</vdb>

Deploy this file into Teiid using CLI or using management console

deploy pi-vdb.xml

Once the metadata is loaded and VDB is active you can use Teiid JDBC/ODBC driver or OData to connect to the VDB and issue queries.

PI Translator Capabilities

PI translator is extension of jdbc-ansi translator, so all the SQL ANSI queries are supported. PI translator also supports LATERAL join with Table Valued Functions (TVF). An example Teiid query looks like

SELECT EH.Name, BT."Time", BT."Number of Computers", BT."Temperature"
    FROM Sample.Asset.ElementHierarchy EH
    LEFT JOIN  LATERAL (exec "TransposeArchive_Building Template"(EH.ElementID, TIMESTAMPADD(SQL_TSI_HOUR, -1, now()), now())) BT on 1=1
    WHERE EH.ElementID IN (SELECT ElementID FROM Sample.Asset.ElementHierarchy WHERE Path='\Data Center\')
Note
ANSI SQL semantics require a ON clause, but CROSS APPLY or OUTER APPLY do no have a ON clause, so for this reason user need to pass in a dummy ON clause like ON (1 = 1), which will be ignored when converted to APPLY clause which will be pushed down.

By default this translator turns off the "importer.ImportKeys" to false.

Note
The PI data type, "GUID" will need to be modeled as "String" and must define the NATIVE_TYPE on column as "guid", then Teiid translator will appropriately convert the data back forth with the PI datasource’s native guid type with appropriate type casting from string.

Pushdown Functions

PI accepts time interval literals that are not recognized by Teiid. If you wish to make a comparison based upon an interval, use the PI.inteveral function:

select * from Archive a where a.time between PI.interval('*-14d') and PI.interval('*')

Known Issues: TEIID-5123 - Casting a string containing a negative or zero value (e.g. '-24' or '0') to Float/Single fails with PI Jdbc driver.

JCA Resource Adapter

The resource adapter for this translator is provided through OSISoft PI Data Sources. Refer to Admin Guide for configuration.

results matching ""

    No results matching ""