Unable to query : Data source - Osisoft PI

[anantk]

Hi,

I am trying to query an Osisoft PI datasource through a Virtual Database using TEIID.

Now, queries to osisoft PI must always specify the time (timestamp or a set of timestamps) for picking values (since all timestamps would include years of data stored as frequently as 1 value per second).

On setting up the sourcemodel and deploying the vdb, the time column was automatically mapped as a timestamp type.

When I try to execute the following Query in the scrapbook:

select "tag", "value" from "pimin" where "time" = NOW()

I get the following Error:

TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PI_Archive: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [{ts '2015-03-18 14:34:05.383'}] SQL: SELECT "piarchive"."pimin"."tag", "piarchive"."pimin"."value" FROM "piarchive"."pimin" WHERE "piarchive"."pimin"."time" = ?]

I get a similar error when I use the parsetimestamp function.

However, when I provide an inappropriate string as a timestamp, an empty resultset is generated.

Eg : select "tag", "value" from "pimin" where "time" = 'tjb'

would generate empty "tag" and "value" columns.

I am assuming that this is occurring because some internal comparison at the TEIID end sends a null timestamp query if an invalid string is given as timestamp. Since no value in the PI data source has null timestamps, empty columns are returned.

Additional information:

The osisoft PI Data Access Server comes with a PIJDBC driver which can be used to connect a JDBC compliant application (TEIID) to PI.

The connection string was specified, along with the username, password etc. while configuring the driver from the jbdevstudio end. The connection test was successful.

The PIJDBCDriver.jar did not initially contain the "METAINF/java.sql.Driver" file. Therefore I added the file containing the fully qualified path to the driver class(com.osisoft.jdbc.Driver).

Following that change, I was able to deploy the virtual database containing the source model linking to PI.

The connection string looks like:

jdbc:pisql://xxx.xxx.xxx.xxx:pppp/Data Source=xxx.xxx.xxx.xxx; Integrated Security=SSPI

TEIID Version: 8.4

JBDevStudio 7.1.1

Any help you could provide in the matter will be deeply appreciated.

Thanks

[rareddy]

Did you use "jdbc-ansi" translator on the VDB? If the JDBC driver does not support ANSI timestamp format, then you may need to extend to create a custom translator.

We also planning on writing a PI translator out of the box for Teiid, but we not have instance of PI database to test. If you want we can collaborate on development of this, let us know.

[anantk]

Hi Ramesh,

Thanks for your prompt reply. I did not find an option of using the jdbc-ansi translator. I selected JDBC (default) as the metadata processor and a Generic JDBC connection profile. Following that, I specified the driver details.

The PIJDBC driver probably does not the support ANSI timestamp format. PI for sure does not.

So, how do I go about creating a custom translator. And where exactly is the translator to be specified while creating a connection?

The JDBC driver is supposed to help access the PI Data Access Server. This Data Access Server (not the same as the the PI Server) enables the user to access the PI data in an RDBMS-SQL format (the time has to be specified) via the PIOLEDB driver. This is needed because the data at the PI server end is not in RDBMS format.

So, we're basically trying to query the Data Access server which is hosting an RDBMS database capable of handling SQL queries via the PIJDBC driver.

It would be awfully convenient to have the PI translator readily accessible. I'll get in touch with my superiors and let you know what they think.

Thanks again for your reply.

[rareddy]

Thanks for your prompt reply. I did not find an option of using the jdbc-ansi translator. I selected JDBC (default) as the metadata processor and a Generic JDBC connection profile. Following that, I specified the driver details.

When you are building the VDB in Designer, you can select the translator name, I was suggesting to use "jdbc-ansi" there, not during the import of the metadata.

The PIJDBC driver probably does not the support ANSI timestamp format. PI for sure does not.

So, how do I go about creating a custom translator. And where exactly is the translator to be specified while creating a connection?

All it matters is how does the PI JDBC driver handling it, because that is what you are using. There are lot of threads on this subject and complete development guide at [1], more over all our code open source, so you can take look at any of the translators. However, for this you need to extend current JDBC translator "JDBCExecutionFactory" and provide any source specific changes. We have many examples of this in the code.

The JDBC driver is supposed to help access the PI Data Access Server. This Data Access Server (not the same as the the PI Server) enables the user to access the PI data in an RDBMS-SQL format (the time has to be specified) via the PIOLEDB driver. This is needed because the data at the PI server end is not in RDBMS format.

So, we're basically trying to query the Data Access server which is hosting an RDBMS database capable of handling SQL queries via the PIJDBC driver.

Ok, is there any interest in accessing the PI Data Access Server directly not using the JDBC driver? You would be building something similar to JDBC driver in Teiid, but will remove one layer. But, in this situation you need to have the data access libraries in Java that can get you direct connection to PI. We came to know of some timing issues with JDBC driver, but do not know full extent of them from our customers.

It would be awfully convenient to have the PI translator readily accessible. I'll get in touch with my superiors and let you know what they think.

Well, you can make it possible for the next guy. Translator development itself is not that bad, we can help you with whatever questions you will have in hopes that you will contribute it back to community

[1] https://docs.jboss.org/author/display/TEIID/Developerhttps://docs.jboss.org/author/display/TEIID/Developer

[rareddy]

[TEIID-3327] Translator for OSIsoft PI - JBoss Issue Tracker

[anantk]

Hi Ramesh,

I've just recently come up with certain issues which suggest that the timestamp formatting may not be the only problem. The PI DAS also hosts certain tables which do not have any time column, hence there is no time restriction. Therefore, if everything else was alright, then a select * type of a query (without any where condition) on the mapped table should yeild all the rows.
However, that too errors out.

Query:
select * from piuser

Error Message:
TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PI_JDBC_Con2: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT "piuser"."piuser"."name", "piuser"."piuser"."description", "piuser"."piuser"."currentcontext", "piuser"."piuser"."defaultcontext", "piuser"."piuser"."password" FROM "piuser"."piuser"]

We have tested the PIJDBC driver using the free "DBVisualizer" tool. The same connection string, same .jar file etc, show all rows for the table.

To be honest, now that I know that It may not just be the timestamp formatting issue within the translators, I find myself a little lost.

Do let me know what your thoughts are.

Thank you once again for your reply.

[rareddy]

Anant,

I think the PI JDBC Driver does not liking the "prepared" statements, thus the issue. You can turn off the PreparedStatement usage in the translator using extension property "UseBindVariables" to "false".

How did you build the VDB? If you used Designer, during the VDB creation, there is tab for "translator" extension properties, you would need to define this on translator, and that translator name you need to use on the model. Below are the screen shots

If you are using the dynamic VDB, see this Translators - Teiid 8.11 (draft) - Project Documentation Editor for "Override Execution Properties"

HTH

Ramesh..

[anantk]

Hi Ramesh,

Thanks for the detailed screenshots. Turning off the prepared statement usage pretty much solved the problem I was facing in querying the non timestamp-associated tables. The queries run successfully on those tables.

However, the same error exists while querying the timestamp-associated tables (except for the prepared values part).

Now that we've been able to take a step forward, I'll be trying out a few more permutations before turning to the

source code.

Thanks once again for all your time. I really appreciate it.

[rareddy]

Anant,

Do you have any code to share on PI translator? That would be really good, we want to have this in Teiid community.

Ramesh..