Unable to get result on querying piavg or pitotal or pimax views of Pi server via Teiid

[cm.kumar]

Issue:

Empty result set while querying data with this query via VDB,

SELECT pi."Time",

pi."value", pi.tag

from "PiArchive"."piavg" pi

where pi.timeStep = '1d' AND pi.tag= 'CDT158' and pi."time" > '2018-02-22 19:59:36.0'

Things established or working,

1. Have a VDB, which has a view model which connects to PI server via PIJDBC driver connector.

2. Connection to Pi server is happening fine, no issues here.

3. Querying to PiArchive.picomp or PiArchive.picomp2 returns data.

SELECT pi."Time",

pi."value", pi.tag

from "PiArchive"."PiComp2" pi

where pi.tag= 'CDT158' and pi."time" > '2018-02-22 19:59:36.0'

4. Same queries runs connecting to the PIJDBC driver directly and also via PIOLEDB tester.

None of the aggregate view in Piserver returns data, piavg or pitotal or pimax etc.

Any pointers or help?

[shawkins]

What is the data type in teiid for timeStep?  If it is a date/time type that literal comparison is not valid.  If that is the case see [TEIID-5097] Cannot run time-based queries against Osisoft PI - JBoss Issue Tracker

To workaround you need to wrap the literal in a function that will the direct comparison in the generated source query.

[cm.kumar]

Yes, the data type of timestep is 'time'. So if I pass it in time format (like hh:mm:ss), it returns results. But for timesteps in days or months, the option is to push functions from VDB to Pi, is it right?

[shawkins]

Yes that is correct.

[cm.kumar]

thanks Steven.

Can you give some more information around this push-down function?

Like I'm not logically able to connect, meaning, this query now works,

SELECT pi."Time",

pi."value", pi.tag

from "PiArchive"."PiAvg" pi

where pi.timestep = '23:00:00' AND pi.tag= 'CDT158' and

pi.time BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -14, now()) AND now()

But timestep since it accepts time, max probably we could go is like 23:59:59. But how do we convert like '2d' into valid time via push down function?

[shawkins]

> But how do we convert like '2d' into valid time via push down function?

Teiid 10.1+ adds a pushdown function on the pi schema:  create function interval(param string) returns timestamp options ("teiid_rel:native-query" $1)

If you are using an older Teiid version, you can add a similar function on the source schema either via DDL or Designer.

Then you don't need to convert an interval literal, but instead use the function:

pi.timeStep = pi.interval('1d')

[thiru28]

I have included the VDB xml

<model name="PI" type="PHYSICAL" visible="true">     

          <property name="importer.importProcedures" value="true"/>

          <property name="importer.tableTypes" value="TABLE"></property>

           <property name="importer.importKeys" value="false"></property>

            <source translator-name="osisoft-pi" connection-jndi-name="java:/PISrc" name="PISrc" />

           <metadata type="NATIVE"/>

            <metadata type="DDL">

            <![CDATA[

                    CREATE FOREIGN FUNCTION interval1 (arg1 string) RETURNS timestamp OPTIONS ("teiid_rel:native-query" '$1');

            ]]>

        </metadata>

    </model>  

I am executing the select statement on the Squirrel,

SELECT pi."Time", pi."value", pi.tag from "PiArchive"."PiAvg" pi where

pi.tag= 'CDT158' and

pi.timestep = pi.interval1('5d') AND

pi.time BETWEEN '2018-01-27'  AND '2018-01-28'

I got an error below,

Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PISrc: 0 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT TOP 100 g_0.[time] AS c_0, g_0.[value] AS c_1, g_0.[tag] AS c_2 FROM [piarchive].[piavg] AS g_0 WHERE g_0.[tag] = 'CDT158' AND cast(g_0.[timestep] AS DateTime) = '5d' AND g_0.[time] >= '2018-01-27 00:00:00.0' AND g_0.[time] <= '2018-01-28 00:00:00.0']

SQLState:  50000

ErrorCode: 30504

Please advise me.

[thiru28]

Its working fine from the below code changes in VDB.xml

<metadata type="NATIVE"/> 

<metadata type="DDL"> 

            <![CDATA[

CREATE FOREIGN FUNCTION TIMEINTERVAL(arg1 string) RETURNS TIME OPTIONS ("teiid_rel:native-query" '$1');

]]> 

</metadata>

Squirrel select statement,

SELECT pi.Tag, pi."Time", pi."value" FROM "PiArchive"."PiAvg" pi

WHERE pi.tag= 'CDT158' AND

pi.timestep = TIMEINTERVAL('1w')  AND

pi.time BETWEEN '2018-01-01' AND '2018-12-31'

[rareddy]

Metadata elements in the DDL are chained. When you define just the DDL one, then the model is only populated with metadata that is defined. Adding "NATIVE" brings in the metadata from the source about your schema. NATIVE is the default, but only when no other metadata type is defined.