How to back fill historical data to openHistorian?

[yuwenpeng]

Hi,

Last month we installed openHistorian as the database to archive the data received from FDRs. We have several years' historical data archived in hundreds of Access .mdb files. Now we plan to migrate the historical data from Access to openHistorian. So I have several questions and need some suggestions on how to do it. The openHistorian we installed uses MySQL as configuration database, and is receiving real-time data from around 100 FDRs. We want to backfill historical data to this openHistorian.

1. Is developing a custom input adapter the correct way to backfill data? Or is there any other better approach?
2. Since the openHistorian is receiving real-time data, will backfill historical data impact the real-time data receiving & archiving, or reduce the performance?
3. Is it doable that we install a new openHistorian server on another computer using exactly the same MySQL configuration database, then backfill data to this new openHistorian and copy all archive files to the openHistorian which is receiving real-time when backfilling is done?

Any suggestion is appreciated!

Best regards, Wenpeng Yu

[ritchiecarroll]

You might consider just using the API directly to write data into the historian, i.e., open the historian database and also open the Access database, read data from Access and start writing data into openHistorian.

We could point you to several examples of this, but here is simple example below.

Note that you should be able to call a large number of these functions in parallel without adversely affecting performance.

You will likely need to reference GSF.Core.dll, GSF.SortedTree.dll, GSF.TimeSeries, and openHistorian.Core.dll in order for the code to run. Also, I would recommend referencing the assemblies from the version of openHistorian you are running that is archiving the data.

using System;
using System.Collections.Generic;
using GSF.Snap;
using GSF.Snap.Filters;
using GSF.Snap.Services;
using GSF.Snap.Services.Reader;
using openHistorian.Net;
using openHistorian.Snap;

// Define a historian measurement for floating-point values
public struct HistorianMeasurement
{
    public readonly ulong ID;
    public readonly DateTime Time;
    public readonly float Value;

    public HistorianMeasurement(ulong id, DateTime time, float value)
    {
        ID = id;
        Time = time;
        Value = value;
    }
}

// Write historian data
public static void WriteHistorianData(string historianServer, string instanceName, IEnumerable<HistorianMeasurement> measurements)
{
    const int DefaultHistorianPort = 38402;

    if (string.IsNullOrEmpty(historianServer))
        throw new ArgumentNullException("historianServer", "Missing historian server parameter");

    if (string.IsNullOrEmpty(instanceName))
        throw new ArgumentNullException("instanceName", "Missing historian instance name parameter");

    string[] parts = historianServer.Split(':');
    string hostName = parts[0];
    int port;

    if (parts.Length < 2 || !int.TryParse(parts[1], out port))
        port = DefaultHistorianPort;

    // Open historian connection
    using (HistorianClient client = new HistorianClient(hostName, port))
    using (ClientDatabaseBase<HistorianKey, HistorianValue> database = client.GetDatabase<HistorianKey, HistorianValue>(instanceName))
    using (HistorianInputQueue queue = new HistorianInputQueue(() => database))
    {
        HistorianKey key = new HistorianKey();
        HistorianValue value = new HistorianValue();

        foreach (HistorianMeasurement measurement in measurements)
        {
            key.PointID = measurement.ID;
            key.TimestampAsDate = measurement.Time;
            value.AsSingle = measurement.Value;
            queue.Enqueue(key, value);
        }

        // Wait for queue to be processed
        while (queue.Size > 0)
            Thread.Sleep(100);
    }
}

[ritchiecarroll]

Alternately - yes - BTW - you can clone the MySQL configuration onto another machine then copy the .D2 files onto the live system - this will work fine so long as the Measurement.ID values match, and with a properly cloned database configuration, they should. This would be a good way to help mitigate the load on the real-time system if that is a concern. To make the live system "see" the historical data, you would likely need to restart the service - I am not sure there a command available to "re-scan" the data folders...

[yuwenpeng]

Thank you Ritchie! That helps a lot. Let me try it.

[ritchiecarroll]

I am closing this for now - if you have any trouble - feel free to re-open, or just post a question on the discussion board here:

http://discussions.gridprotectionalliance.org/c/gpa-products/openhistorian

Thanks! Ritchie