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