OSI PI - query data
From PI SQL Commander (similar to SSMS) you can run queries against the database / tables.
To get history values of an asset (element) attribute:
select
e.name as ElementName
,e.ID
,ea.name as AttributeName
,a.[Time]
--,a.Value
,a.ValueDbl
from [YOUR DATABASE].[Asset].Element e
inner join [YOUR DATABASE].[Asset].ELementAttribute ea
one.ID =ea.ElementID
inner join [YOUR DATABASE].[Data].Archive a
ona.ElementAttributeID =ea.ID
where e.Name ='element Name'
and ea.name ='element attribute name'
and [Time]>'2022-02-04'
The Snapshot table may be empty, so getting values from the Archive.
The 'Value' column in the Archive seems very special. It might be a dynamic type or something. If you try to select the max(Value) or min(Value) it will fail with error like '[max] Overload resolution failed for (Variant) argument(s).'
Its probably because the type is not certain.
But if changing it to max(ValueDbl) for example, the type is double, and the query will return OK.
Note
From RTQP, e.g. Running a query through ODBC / OLEDB / JDBC drivers.
The database schema is different.
Most of the asset and data tables are under the 'element' schema, as documented here https://docs.osisoft.com/bundle/pi-sql-data-access-server-rtqp-engine/page/element-schema.html
The table names and column names are different. E.g. ElementAttribute is just Attribute in RTQP. To use ElementAttribute, it needs to use element.Attribute by adding the schema name 'element' in front of it. Also the column name 'Time' in Archive is called 'Timestamp' if using RTQP.
Below is the query that works in ODBC as opposed to the above query that works in PI SQL commander
SELECTe.Name asElement
,ea.Name Attribute
,a.TimeStamp
,a.Value_Double
FROM Element.Element e
inner join Element.Attribute ea ONea.ElementID =e.ID
inner join Element.Archive a ONa.AttributeID =ea.ID
WHERE
e.Name ='element name'
and ea.name ='element attribute name'
and a.TimeStamp >'2022-02-04'
Note, if using the 'Value' column in ODBC query, it will fail, as the type can not be recognized, so always use the Value_'Type' column with a specific type.
Here is an example of running the query in Python through ODBC. The PI SQL client needs to be installed before hand to have the ODBC driver available.
import pyodbc
conn_str = "Driver=PI SQL Client; AF Server=your server; AF Database=your database; Integrated Security=SSPI;"
sql = '''
SELECT e.Name as Element
,ea.Name Attribute
,a.TimeStamp
,a.Value_Double
FROM Element.Element e
inner join Element.Attribute ea ON ea.ElementID = e.ID
inner join Element.Archive a ON a.AttributeID = ea.ID
WHERE
e.Name = 'element name'
and ea.name = 'element attribute name'
and a.TimeStamp > '2022-02-04'
'''
conn = pyodbc.connect(conn_str, autocommit=True)
cursor = conn.cursor()
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
print(row)
cursor.close()
conn.close()
TimeStamp precision
An article about PI time precision : https://pisquare.osisoft.com/s/Blog-Detail/a8r1I000000GvqzQAC/a-detailed-exploration-of-aftime-precision
OSIPI's archive time precision is 15 microseconds.
In query through SQL commander lite or RTQP, using the Time filter may have boundary issue.
As an example below, the where clause selects only Time > 2022-06-14T06:59:45.568
But a record with time equals to that still returns.
That is probably a precision issue. The actual time is larger than 2022-06-14T06:59:45.568 but displayed the converted version.
This causes problem for high water mark as the boundary is always wrong if it loads from the max Time.
A better way is to use format function to return the full precision timestamp
SELECT Format(a.Time,'yyyy-MM-dd HH:mm:ss.fffffff')
FROM .[Asset].[ElementHierarchy] eh
INNER JOIN [Asset].[ElementAttribute] ea ON ea.ElementID =eh.ElementID
INNER JOIN [Data].[Archive]a ON a.ElementAttributeID =ea.ID
WHERE eh.Name ='element name'
AND ea.Name ='attribute name'
AND a.Time>'2022-06-14 06:59:45.568069'
AND a.Time<='2022-06-14 09:00:00'