How i can easily extract data from historian with python?

Question

I am fairly new to Proficy Historian and Python. The purpose of my application is to start extracting data from Proficy.

GE (Proficy) Historian is installed on a Virtual Machine Server A while my python is installed in an other VM Server B.

Both servers are under the same firewall and in the same VLAN, so they can communicate.

I have tried to look around but I am not sure which is the easiest method to extract data... Any help?

Answer 1

You can use the OLE DB API included with the proficy iHistorian, see the manual of iHistorian. In the manual you can find the available tables and some examples on SQL code. Check whether the API is correctly installed with the 'Historian Interactive SQL' program included with iHistorian.

I used the python package 'PyADO' in order to connect to the OLE DB API (got this from GitHub Gist, also requires the packages 'win32com' and 'pythoncom'). The module is not available on PyPi, so just download it, extract it and put it in the 'site-packages' folder (or your current working directory). There are some errors in the code, fix this in the 'PyADO.py' file by replacing all the '<>' symbols for 'not equal' with '!='. For python 3 usage also update all print statements from 'print blabla' to 'print(blabla). In order to extract the data the following should suffice (I included 'prettytable' in order to get a decent print of the data):

import PyADO
from prettytable import PrettyTable

conn = PyADO.connect(None,host='IHISTORIAN_SERVER_NAME',user='USERNAME',password='PASSWORD',provider='iHOLEDB.iHistorian.1')
curs = conn.cursor()

curs.execute("SELECT timestamp, value, quality, tagname FROM ihrawdata WHERE samplingmode=rawbytime AND timestamp>='01-Mar-2018 13:58' AND timestamp<='01-Mar-2018 14:30' AND (tagname=SOMETAG OR tagname=OTHERTAG)") 
result = curs.fetchall()
descr = curs.description

header = [i[0] for i in descr]
table = PrettyTable(header)
for row in result:
    table.add_row(row)
print(table)

curs.close()
conn.close()

Resulting in:

+---------------------------+--------------------+------------------+--------------+
|         timestamp         |       value        |     quality      |   tagname    |
+---------------------------+--------------------+------------------+--------------+
| 2018-03-01 13:58:15+00:00 | -4.422495365142822 | Good NonSpecific | SOMETAG      |
| 2018-03-01 13:59:15+00:00 | -3.046903133392334 | Good NonSpecific | SOMETAG      |
| 2018-03-01 13:58:00+00:00 | 61.07942581176758  | Good NonSpecific | OTHERTAG     |
| 2018-03-01 13:59:00+00:00 | 60.99810791015625  | Good NonSpecific | OTHERTAG     |
| 2018-03-01 14:00:00+00:00 | 62.58036422729492  | Good NonSpecific | OTHERTAG     |
+---------------------------+--------------------+------------------+--------------+

I also tried to use the package 'adodbapi', but I was not able to make it work. It seems that ODBC doesn't work at all with iHistorian, which makes the package 'pyodbc' not suitable for this as well. If anybody has a solution to connect to an OLE DB API with a well maintained python package I would be glad to hear about it.

Answer 2

You may need to settle with interfacing with GE Historian's REST API interface with Python. Here is a public link to Historian API documentation for Version 5.5

It is still applicable to the newest versions, but the install iso also has the latest REST API document (in case this URL is out of date). If your client language is flexible, GE Historian also has C, C++, VC++, and VC++6 libraries and examples. You can find them by installing the Historian API SDK from the Client Tools during the Historian installation. They will be located in C:\Program Files\Proficy\Proficy Historian\x86\api\