1. Overview

	PI JDBC provides data access to the OSISoft PI System through the use of SQL queries.

This guide includes:

1. Terms and abbreviations used in the Pi JDBC guides.
2. Information on PI JDBC.
3. A description of the Edge One™ PI JDBC Module.
4. Information on the Pi JDBC Module Data Structure.

To learn how to configure the Edge One™ Pi JDBC module, please refer to the PI JDBC Configuration Guide.

Terms and Abbreviations

About PI JDBC

PI Java Database Connectivity (JDBC) provides data access to OSISOft PI systems through the use of SQL queries.

JDBC is a an API that is built into every Java Runtime Engine (JRE) and defines how a client can access a database. For relational database access, Java applications use JDBC.

The Edge One™ PI JDBC Connector uses the PI SQL Data Access Server (PI SQL DAS) as a gateway, which provides secure network communication (HTTPS) and executes the queries. PI SQL Data Access Server is required for PI JDBC Connector.

Edge One™ PI JDBC Module

The Edge One™ PI JDBC module is supplied as a container that enables Edge One™ to perform read operations on PI Server databases accessible through a JDBC interface. Data collected from a PI Server can be processed locally by the optional Edge One™ Flows module, by a SmartPlug™ application or another containerized application. The processed data can then be sent to CloudPlugs IoT, or to a supported public cloud, data lake or analytics application.

The Edge One™ PI JDBC module publishes data values retrieved from PI Server databases into CloudPlugs channels making them available to other devices and applications subscribing to those channels.
All the communications with supervisory applications in the cloud are encrypted, event driven and asynchronous, resulting in substantial traffic savings.

A single Edge One™ deployment can operate multiple PI-JDBC projects to connect to multiple Pi servers and/or multiple Pi Server databases. The database endpoints, such as an OLE database, and schemas, such as a PI archive, are configured in each project to retrieve data from the appropriate locations.

Connections to the PI servers are secure and can be configured to require certificate validation and authentication of access credentials.

The Pi JBDC Connector includes the following driver versions:

• PI JDBC 2010
• PI JDBC 2012
• PI JDBC 2014
• PI JDBC 2016
• PI JDBC 2018
• PI JDBC 2019

Back to Top

Pi JDBC Module Data Structure

The Edge One™ Pi JDBC Connector Module uses the standard data representation of all Edge One™ modules.

Device data is stored in JSON, key-value pairs in which the key is the register address and the value is the value of the register.

Pi Server database schemas need to be associated with an Edge One™ Group in order for their data to become accessible to Edge One™.

• Group Name, a string that represents the custom name that is entered for the group of devices.
• Group ID, a number that will be used to identify the group inside the Edge One™ data structure.

The MQTT topics carrying Pi JDBC payloads have the following structure:

topic:  data/PID/GID

where,

	data  	indicates that this is a data topic.
	PID		a number or Project ID that identifies the Pi JDBC project generating data.
	GID		a number or Group ID as indicated above.

The data inside this topic is a JSON such as:

{
   "mod":"pi-jdbc",
   "vals":[
      {
         "q":68,
         "err":"this value is cached on the connector",
         "v":"-272.09302325581393",
         "k":"ffffffdc-ffff-ffff-beff-ffffffffffff",
         "ts":1582718694000
      },
      {
         "q":68,
         "err":"this value is cached on the connector",
         "v":"240.0",
         "k":"ffffffdb-ffff-ffff-f6ff-ffffffffffff",
         "ts":1582718694000
      }
   ],
   "g":"1",
   "pid":"pi2016-ole-ent",
   "ts":1582718867250
}

Data can be read locally or sent to a remote MQTT server or cloud such as CloudPlugs IoT using the Message Router

Back to Top