Recently a new OSIsoft partner asked about SQL access to the PI System, specifically JDBC. This post lists a variety of information relating to JDBC that could be useful to the partner and maybe other folks too!
First off, a confession, I have been biased (ok, so a little bit of click bait, as there is also some wariness too!) against JDBC because:
1. different connection types required for points (if not referenced through Asset Framework (AF) and Asset Framework.
2, requires middleware
3. queries can be complicated
4. not good for retrieving *lots* of data
5. and I had little data on the performance.
So in general, instead would generally steer folks towards PI Web API or AF SDK.
That all stated, it was time to take a closer look and also get up to speed on new developments with the SQL family of products.
PI SQL 2018!
The good news is that a number of these items are being addressed in 2018! At PI World SF 2018 Bodo Bachmann presented on the next generation of SQL products and a number of the items above are being addressed! Relating back to the previous list, here are some updates:
1. and 2.. There is a new PI SQL engine that supports the PI Server and is part of AF Server 2018
3. Simplified query support
5. Improved performance.
Now these are not available today for JDBC (May 2018). OLEDB support will be available as part of PI Server 2018 with support for ODBC and JDBC later this year - see the link to the presentation for more details.
So, good to know for the future, what about the request from the partner who is looking to implement integration now?
What are the requirements?
Always good to step back and take a look at the big picture in terms of requirements and then options.
- Linux based
- Using SQL technologies to access other on premise systems
JDBC is looking good, what about feature supported?
JDBC connections can make use of two possible middleware components:
- PI OLEDB Enterprise
- PI OLEDB Provider
that are covered in the PI Data Access feature comparison
OK, so the comparison is an eye chart, however does a fairly good job of comparing the functionality of the developer technologies.
(and I'm not just saying that because I'm one of the document contributors)
To Asset Framework or not to AF?
At this point it might be tempting to go with the OLEDB Provider because it keeps it simple by accessing only points (tags).
Not so fast Wonderwoman or Batman or <insert-reference-of-choice>, why not utilize the Asset Framework even if the customer is not using it because:
- Worth noting that points are accessible via OLEDB Enterprise too as long as they are referenced in Asset Framework (i.e: an attribute with a PI Point data reference).
- enable additional context to be made available to the partner application - units of measure, non-time series artifacts
- provide a standard view to be created in Asset Framework that can be applied across multiple customer sites
- utilize templates to simplify implementation.
While this will be some work up front, benefits can be realized longer term.
More JDBC please
Ok, so our new partner would like to get more information about JDBC and try it out. Where to go?
Windows system to run the PI SQL DAS components (OLEDB software below)
Supported Linux system to run JDBC driver (the driver is also supported on Windows)
There will be three software packages to download and install from the Tech support website > Products > PI Developer Technologies:
- JDBC driver
- PI OLEDB Enterprise
- PI OLEDB Provider (for testing purposes! See AF section above)
Once installed, a suggested place to start is on the Windows system and run PI SQL Commander Lite that includes a set of sample queries.
(In case the link fails https://livelibrary.osisoft.com > PI Developer Technologies > PI SQL Commander Lite > Object Explorer > Run sample SQL queries)
If you are a PI Developer Club member (labs are included) or have purchased access to the learning labs you can access PI World event labs using a pre-configured Virtual Machine environment (once started a lab can be accessed for 24 hours).
There is a lab that includes the PI SQL Commander Lite and related PI SQL software (note the JDBC software is *not* installed): PI World 2018: Develop a PI JDBC Project to Exchange Data with Raspberry Pi Gadgets.