openHistorian Historian data to be saved into RDBMS (Oracle/Sql Server) using AdoAdapters

GPA Products openHistorian
1

Hello All,

It was suggested that if i want to write Historian data into RDBMS (Oracle/Sql Server) directly use Ado Adapters.
So my query is that for better understanding Is there any example of use of Ado Adapters.

Also i found one discussion

https://gridbits.gridprotectionalliance.org/t/openhistorian-for-dummies/285/3

in which reference for HistorianDataWalker given to write historian data into Sql Server.
Can i go through the project for my purpose which i have asked???

Thanks in Advance
Ankit

2

First, note that if you use the ADO adapters, openHistorian will treat your database as an archive. This means it will push real-time data into the database in much the same way as it would normally push the real-time data into its own archive. It does not provide an on-demand mechanism for migrating data from the historian archive to SQL. If you still would like to pursue that as an option, here’s a modified openPDC post from the old CodePlex discussion boards that would be relevant to your query.

First, open SQL Server Management Studio. In the Server Explorer, drill down to the openHistorian database, select it, and then select New Query from the toolbars at the top of the window. Enter the following query into the editor window, then select Execute from the toolbars at the top of the window.

CREATE TABLE TimeSeriesMeasurement
(
    SignalID UNIQUEIDENTIFIER NOT NULL,
    Timestamp VARCHAR(24) NOT  NULL,
    Value FLOAT NOT NULL
)

In the openHistorian Manager, under Outputs > Manage Custom Outputs…

Name: ADO
Type: ADO: Archives measurements to an ADO data source
Connection String: BulkInsertLimit=500; DataProviderString={AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.SqlClient.SqlConnection; AdapterType=System.Data.SqlClient.SqlDataAdapter}; DbConnectionString={Data Source=localhost\SQLEXPRESS; Initial Catalog=openHistorian; Integrated Security=SSPI}; TableName=TimeSeriesMeasurement; IDFieldName=SignalID; TimestampFieldName=Timestamp; ValueFieldName=Value; SourceIDs=PPA
Enabled: Checked

Make sure to save and initialize this adapter, in that order. The measurements in the openHistorian will then be written to the TimeSeriesMeasurement table, and may be joined with your metadata via the SignalID field.

If instead you would like to simply have the ability to query openHistorian using SQL syntax, then you can try the SqlClr interface that is designed to query the openHistorian via the socket interface and return the measurements as a resultset in SQL Server. Here is the link to the SQL script that enables it.

https://github.com/GridProtectionAlliance/openHistorian/blob/master/Source/Libraries/openHistorian.SqlClr/EnableHistorianSqlClr.sql

And here is an (untested) example query I just came up with.

SELECT *
FROM dbo.GetHistorianData('127.0.0.1', 'PPA', DATEADD(MINUTE, -5, GETDATE()), GETDATE(), NULL)

Note that this script requires you to load unsafe assemblies into your SQL Server database. For that reason, we don’t typically recommend using this in a production system, and we also don’t have any active deployments of this interface that I am aware of. Use at your own risk.

Finally, yes, you can use the HistorianDataWalker to push data into a database. However, the HistorianDataWalker is really just a sample app that demonstrates how to quickly and easily query a block of historian data and do something with it. It does not, out of the box, write data to your SQL database. That task is left as an exercise for the reader.

Here is a link that takes you directly to the function that contains the main loop to read from the archive.
https://github.com/GridProtectionAlliance/HistorianDataWalker/blob/master/Source/HistorianDataWalker.cs#L219

3

Thank You Very Much Stephen.

4

Hello Stephen,
As per your Guidance I have configured ADO for Oracle Database and Connection String is as follows

DataProviderString={AssemblyName={System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=Oracle.DataAccess.Client.OracleConnection; AdapterType=Oracle.DataAccess.Client.OracleDataAdapter}; DbConnectionString={Data Source=Data Source=openHistorian; User Id=openHistorian; Password=openHistorian; Integrated Security=SSPI}; InputMeasurementKeys={7861ade4-3c50-4e18-90e5-30990edd2677;0b36ca0b-6c9c-4919-993c-316d4044ec73;115711f0-7407-4fa6-9b88-dd5ab341e6e4;5ea8ff51-a35b-4308-b414-7e4524d98254;269ab701-7e3e-455b-ab1f-4e94a6274368;4ee453be-a3b6-4eb0-9a98-cce97f6ffab3;a4ffeeee-ec5a-424a-8560-2da853a68610;e46badb3-47ae-41d8-bb96-b56296510a9c;8cd6dadf-cbc7-4925-93d4-02baa3c9eb29;137226c6-583f-4981-9684-1758c349b602;53d3187d-951d-4892-a8ef-c14cca8bc198;d41ffb06-c2b3-49bd-aec2-f2918e469ce7;8b77ccee-b349-4658-bcc1-1cd7f51305c6;b58ed719-f0e0-404a-8d05-faf13993b8b7;af1f1e71-3746-4b08-b267-062bb1b8ae70;43d20af3-34a9-4011-8b64-8d4c37e2e4c0;2e39d86f-9fac-4fb7-9bb0-1604d6c5c347;a95abd93-d23e-4a2d-b0db-ad5e3be9571f}; TableName=TIMESERIESMEASUREMENT; IDFieldName=SIGNALID; TimestampFieldName=TIMESTAMP; ValueFieldName=VALUE; SourceIDs=PPA; BulkInsertLimit=500

But when i initialize it gives me Error shown in Errors as follows
1.
#################################################################################
Application Domain: openHistorian.exe
Assembly Codebase: C:/Program Files/openHistorian/openHistorian.exe
Assembly Full Name: openHistorian, Version=2.6.30.0, Culture=neutral, PublicKeyToken=null
Assembly Version: 2.6.30.0
Assembly Build Date: 7/25/2019 12:17:36 AM
.Net Runtime Version: 4.0.30319.42000

Exception Source: AdoAdapters
Exception Type: System.NullReferenceException
Exception Message: Object reference not set to an instance of an object.
Exception Target Site: AttemptDisconnection

---- Stack Trace ----
AdoAdapters.AdoOutputAdapter.AttemptDisconnection()
openHistorian.exe: N 00024
GSF.TimeSeries.Adapters.OutputAdapterBase.Stop()
openHistorian.exe: N 00108

(Outer Exception)
Machine OS: Microsoft Windows NT 6.1.7601 Service Pack 1
Current User ID: NT SERVICE\openHistorian

Application Domain: openHistorian.exe
Assembly Codebase: C:/Program Files/openHistorian/openHistorian.exe
Assembly Full Name: openHistorian, Version=2.6.30.0, Culture=neutral, PublicKeyToken=null
Assembly Version: 2.6.30.0
Assembly Build Date: 7/25/2019 12:17:36 AM
.Net Runtime Version: 4.0.30319.42000

Exception Source:
Exception Type: System.InvalidOperationException
Exception Message: Exception occurred during disconnect: Object reference not set to an instance of an object.

---- Stack Trace ----

#################################################################################

2.
#################################################################################
(Inner Exception)
Machine OS: Microsoft Windows NT 6.1.7601 Service Pack 1
Current User ID: NT SERVICE\openHistorian

Application Domain: openHistorian.exe
Assembly Codebase: C:/Program Files/openHistorian/openHistorian.exe
Assembly Full Name: openHistorian, Version=2.6.30.0, Culture=neutral, PublicKeyToken=null
Assembly Version: 2.6.30.0
Assembly Build Date: 7/25/2019 12:17:36 AM
.Net Runtime Version: 4.0.30319.42000

Exception Source: mscorlib
Exception Type: System.ArgumentNullException
Exception Message: Value cannot be null.
Parameter name: type
Exception Target Site: CreateInstance

---- Stack Trace ----
System.Activator.CreateInstance(type As Type, nonPublic As Boolean)
openHistorian.exe: N 14196364
System.Activator.CreateInstance(type As Type)
openHistorian.exe: N 00012
AdoAdapters.AdoOutputAdapter.Initialize()
openHistorian.exe: N 01504
GSF.TimeSeries.Adapters.AdapterCollectionBase`1.Initialize(item As T)
openHistorian.exe: N 00343

(Outer Exception)
Machine OS: Microsoft Windows NT 6.1.7601 Service Pack 1
Current User ID: NT SERVICE\openHistorian

Application Domain: openHistorian.exe
Assembly Codebase: C:/Program Files/openHistorian/openHistorian.exe
Assembly Full Name: openHistorian, Version=2.6.30.0, Culture=neutral, PublicKeyToken=null
Assembly Version: 2.6.30.0
Assembly Build Date: 7/25/2019 12:17:36 AM
.Net Runtime Version: 4.0.30319.42000

Exception Source:
Exception Type: System.InvalidOperationException
Exception Message: Failed to initialize adapter ADO: Value cannot be null.
Parameter name: type

---- Stack Trace ----
#################################################################################

Kindly do the needful

Thanks in Advance
Ankit

5

The NullReferenceException in AttemptDisconnection occurs because m_connection is null due to the ArgumentNullException. The ArgumentNullException indicates that the connection type was not found in the data provider assembly. You are referencing types in the ODP.NET assembly provided by Oracle, but the AssemblyName is still set to System.Data so naturally the type would not be found in that assembly. Try using this as the DataProviderString instead.

AssemblyName={Oracle.DataAccess, Version=2.112.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342}; ConnectionType=Oracle.DataAccess.Client.OracleConnection; AdapterType=Oracle.DataAccess.Client.OracleDataAdapter

And good luck. I’ve never tried to use this adapter with Oracle before.

6

Thanks Stephen,
After that implementation the error was gone but now i got another error

127989	Oracle.DataAccess	System.ArgumentException	Value does not fall within the expected range.	"Date and Time:         9/30/2019 4:00:44 PM
Machine OS:            Microsoft Windows NT 6.1.7601 Service Pack 1
Current User ID:       NT SERVICE\openHistorian
Current User Name:     
Current User Phone:    
Current User Email:    

Application Domain:    openHistorian.exe
Assembly Codebase:     C:/Program Files/openHistorian/openHistorian.exe
Assembly Full Name:    openHistorian, Version=2.6.30.0, Culture=neutral, PublicKeyToken=null
Assembly Version:      2.6.30.0
Assembly Build Date:   7/25/2019 12:17:36 AM
.Net Runtime Version:  4.0.30319.42000

Exception Source:      Oracle.DataAccess
Exception Type:        System.ArgumentException
Exception Message:     Value does not fall within the expected range.
Exception Target Site: set_Value

---- Stack Trace ----
   Oracle.DataAccess.Client.OracleParameter.set_Value(value As Object)
       openHistorian.exe: N 00428
   AdoAdapters.AdoOutputAdapter.BulkInsert(measurements As IEnumerable`1)
       openHistorian.exe: N 01710
   AdoAdapters.AdoOutputAdapter.ProcessMeasurements(measurements As IMeasurement[])
       openHistorian.exe: N 00085
   GSF.Collections.ProcessQueue`1.ProcessItems(items As T[])
       openHistorian.exe: N 00045

"	30-09-2019 16:00:44

What it indicates?? And I need to debug the Service how can i debug it ?? so i could reach out to the issue and troubleshoot it and can resolve it.

Also from where AdoOutputAdapter object created ??

Thanks in Advance

7

Here is a link to the code for the AdoOutputAdapter.
https://github.com/GridProtectionAlliance/gsf/blob/master/Source/Libraries/Adapters/AdoAdapters/AdoOutputAdapter.cs

It looks like ODP.NET has some validation on parameter values based on data types. We are not setting the data type because the ADO.NET interfaces do not provide a mechanism for setting the OracleDbType on the OracleParameter. I suspect the easiest thing for you to do might be to create your own custom adapter derived from the AdoOutputAdapter code, but make direct references to the ODP.NET libraries and classes so you can set the OracleDbType property on your OracleParameters.

As for how to debug the service, you can download the openHistorian source code, open it in Visual Studio, select the Debug configuration and the openHistorian project, and hit the Debug button. To debug the AdoOutputAdapter code, you will have to build a debug version of the GSF libraries, copy them into the Dependencies folder, and then debug openHistorian in Visual Studio.

8

Thanks Stephen

Yes i have downloaded the openHistorian source code and Set Startup Project as openHistorianManager and started debugging as it invokes openHistorian Service but it openHistorianManager could not connect to Service as Client. Is there anything missed?

Thanks in Advance

9

To debug the service, you need to debug the openHistorian project, not the openHistorianManager project.

EDIT: Also note, you may need to use the Configuration Setup Utility to set up the system before you start debugging. The build files can be found in ...\openHistorian\Build\Output\Debug\openHistorian.

10

Thank You Stephen for valuable guidance. Though tried to implement Output Adapter into Oracle Database but it is giving errors. So tried to reconfigure setup in SQL Server and it worked and Data inserted into the specified Table. I have some queries and Doubts for it.

  1. What I have done during Configuration Setup I have chose PPA as ADO Adapter itself as Historian Instance and later updated Connection String into Historian Instance. And after restart it is inserting Values into Specified Table.

Now i want to ask that from where (in source code) it is Taking Measurement Ids and Values for Inserting into Database???

Also this data is stored as per the openHistorian mechanism or it stores strait forward Data. i.e. in openHistorian Data stored as per Binary Search Tree mechanism upon commiting it stores data in archive file ?? But in Database i have found that it stores strait forward. Also openHistorian Stores Data as PointId, Timestamp, EntryNumber and Value as here in Database it is stored as SignalId Timestamp and Value. Why the Difference made??

Thanks in Advance

11

This is the method that receives the measurements from the openHistorian routing system.
https://github.com/GridProtectionAlliance/gsf/blob/master/Source/Libraries/Adapters/AdoAdapters/AdoOutputAdapter.cs#L303

And that calls the BulkInsert function, which pushes the measurements into the database.
https://github.com/GridProtectionAlliance/gsf/blob/master/Source/Libraries/Adapters/AdoAdapters/AdoOutputAdapter.cs#L359

Because you used the Configuration Setup Utility to modify your primary historian, your openHistorian system is not saving to its own archive files. All your data is going into the database table and nowhere else. The data being stored in your database is simply inserted into a single table. At that point, it’s up to your RDBMS instance to store that data as a binary search tree or whatever.

I won’t go into the history regarding how and when the AdoOutputAdapter and the openHistorian archive code was designed. One reason the AdoOutputAdapter uses signal ID is because it’s more portable. Since the adapter configuration includes its own connection string, your time-series data can go into an entirely separate data container from the configuration database. The other reason it uses signal ID is because the signal ID is more readily available via reflection on the IMeasurement interface.

Thanks,
Stephen