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.
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