当前位置：文江博客话题详情

如何从 Proficy Historian 查询原始数据？

发布于 2024-07-08 13:01:58 字数 88 浏览 6 评论 0原文

如何从 Proficy Historian/iHistorian 检索原始时间序列数据？

理想情况下，我会要求提供两个日期之间特定标签的数据。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

撕心裂肺的伤痛 2024-07-15 13:01:58

您可以尝试多种不同的采样模式。

原始
插值
实验室
趋势
计算的

这些模式可使用以下所有 API 获得。

用户 API (ihuapi.dll)
SDK (ihsdk.dll)
OLEDB (iholedb.dll)
客户端访问 API (Proficy.Historian.ClientAccess.API)

其中趋势采样模式可能就是您想要的，因为它是专门为图表/而设计的趋势。不过，实验室和插值也可能有用。

请阅读电子书以获取有关每种采样模式的更多信息。在我的机器上，它存储为 C:\Program Files\GE Fanuc\Proficy Historian\Docs\iHistorian.chm 并且我安装了 3.5 版本。请特别注意以下部分。

使用 Historian OLE DB 提供程序
高级主题 | 检索

以下是如何构建 OLEDB 来进行趋势采样。

set 
    SamplingMode = 'Trend',
    StartTime = '2010-07-01 00:00:00',
    EndTime = '2010-07-02 00:00:00',
    IntervalMilliseconds = 1h
select 
    timestamp, 
    value, 
    quality 
from 
    ihRawData 
where 
    tagname = 'YOUR_TAG'

使用 User API 和 SDK 显示等效方法很复杂（使用 User API 更是如此），因为它们需要代码中的大量管道才能进行设置。客户端访问 API 较新，并在幕后使用 WCF。

顺便说一下，OLEDB 方法有一些限制。

不管文档怎么说，我从来都无法让本机查询参数发挥作用。例如，如果您想将它与 SQL Server Reporting Services 一起使用，那么这将是一个令人惊叹的问题。
您无法将示例写入存档或以任何方式更改 Historian 配置，包括添加/更改标签、写入消息等。
在某些情况下，它可能会有点慢。
它没有提供将多个标记名交叉连接到列中，然后进行样本以便每个时间戳和标记组合都存在一个值的规定。趋势采样模式可以让您完成一半，但仍然不会交叉制表，并且不会实际加载原始样本。但用户 API 和 SDK 也无法做到这一点。

There are several different sampling modes you can experiment with.

Raw
Interpolated
Lab
Trend
Calculated

These modes are available using all of the following APIs.

User API (ihuapi.dll)
SDK (ihsdk.dll)
OLEDB (iholedb.dll)
Client Acess API (Proficy.Historian.ClientAccess.API)

Of these the trend sampling mode is probably what you want since it is specifically designed for charting/trending. Though, lab and interpolated may be useful as well.

Read the electronic book for more information on each sampling mode. On my machine it is stored as C:\Program Files\GE Fanuc\Proficy Historian\Docs\iHistorian.chm and I have version 3.5 installed. Pay particular attention to the following sections.

Using the Historian OLE DB Provider
Advanced Topics | Retrieval

Here is how you can construct an OLEDB to do trend sampling.

set 
    SamplingMode = 'Trend',
    StartTime = '2010-07-01 00:00:00',
    EndTime = '2010-07-02 00:00:00',
    IntervalMilliseconds = 1h
select 
    timestamp, 
    value, 
    quality 
from 
    ihRawData 
where 
    tagname = 'YOUR_TAG'

Showing the equivalent methods using the User API and the SDK are complex (more so with the User API) since they require a lot of plumbing in the code to get setup. The Client Access API is newer and uses WCF behind the scenes.

By the way, there are a few limitations with the OLEDB method though.

Despite what the documentation says I have never been able to get native query parameters to work. That is a showstopper if you want to use it with SQL Server Reporting Services for example.
You cannot write samples into the archive or in any way make changes to the Historian configuration including adding/changing tags, writing messages, etc.
It can be a little slow in some cases.
It has no provision for crosstabbing multiple tagnames into the columns and then carrying forward samples so that a value exists for each timestamp and tag combination. The trend sampling mode gets you halfway there, but still does not crosstab and does not actually load raw samples. Then again the User API and SDK cannot do this either.

回复收藏 0 原文

缘字诀 2024-07-15 13:01:58

我的一位同事将其放在一起：

在 web.config 中：

<add name="HistorianConnectionString" 
     providerName="ihOLEDB.iHistorian.1" 
     connectionString="
       Provider=ihOLEDB.iHistorian;
       User Id=;
       Password=;
       Data Source=localhost;"
/>

在数据层中：

public DataTable GetProficyData(string tagName, DateTime startDate, DateTime endDate)
{
    using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection())
    {
        cn.ConnectionString = webConfig.ConnectionStrings.ConnectionStrings["HistorianConnectionString"];
        cn.Open();

        string queryString = string.Format(
                "set samplingmode = rawbytime\n select value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' and value > 0 order by timestamp",
                tagName.Replace("'", "\""), startDate, endDate);

        System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        DataSet ds = new DataSet();

        adp.Fill(ds);
        return ds.Tables[0];
    }
}

更新：

这很有效，但我们遇到了标签不经常更新的问题。如果标签在请求的 startDate 和 endDate 的开始或结束附近没有更新，则趋势看起来会很糟糕。更糟糕的是，仍然存在在请求的窗口期间没有明确点的情况——我们不会得到任何数据。

我通过进行三个查询解决了这个问题：

开始日期之前的前一个值开始
日期和结束日期之间的点
结束日期之后的下一个值

这是一种可能效率低下的方法但它确实有效：

public DataTable GetProficyData(string tagName, DateTime startDate, DateTime endDate)
{
    DataSet ds = new DataSet();
    string queryString;
    System.Data.OleDb.OleDbDataAdapter adp;

    using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection())
    {
        cn.ConnectionString = proficyConn.ConnectionString;
        cn.Open();

        // always get a start value
        queryString = string.Format(
             "set samplingmode = lab\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
            tagName.Replace("'", "\""), startDate.AddMinutes(-1), startDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        // get the range
        queryString = string.Format(
             "set samplingmode = rawbytime\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
            tagName.Replace("'", "\""), startDate, endDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        // always get an end value
        queryString = string.Format(
             "set samplingmode = lab\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
        tagName.Replace("'", "\""), endDate.AddMinutes(-1), endDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        return ds.Tables[0];
    }
}

是的，我知道，这些查询应该参数化。

A coworker of mine put this together:

In web.config:

<add name="HistorianConnectionString" 
     providerName="ihOLEDB.iHistorian.1" 
     connectionString="
       Provider=ihOLEDB.iHistorian;
       User Id=;
       Password=;
       Data Source=localhost;"
/>

In the data layer:

public DataTable GetProficyData(string tagName, DateTime startDate, DateTime endDate)
{
    using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection())
    {
        cn.ConnectionString = webConfig.ConnectionStrings.ConnectionStrings["HistorianConnectionString"];
        cn.Open();

        string queryString = string.Format(
                "set samplingmode = rawbytime\n select value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' and value > 0 order by timestamp",
                tagName.Replace("'", "\""), startDate, endDate);

        System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        DataSet ds = new DataSet();

        adp.Fill(ds);
        return ds.Tables[0];
    }
}

Update:

This worked well but we ran into an issue with tags that don't update very often. If the tag didn't update near the start or end of the requested startDate and endDate, the trends would look bad. Worse, still were cases where there were no explicit points during the window requested--we'd get no data back.

I resolved this by making three queries:

The previous value before the start-date
The points between startDate and endDate
The next value after the endDate

This is a potentially inefficient way to do it but It Works:

public DataTable GetProficyData(string tagName, DateTime startDate, DateTime endDate)
{
    DataSet ds = new DataSet();
    string queryString;
    System.Data.OleDb.OleDbDataAdapter adp;

    using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection())
    {
        cn.ConnectionString = proficyConn.ConnectionString;
        cn.Open();

        // always get a start value
        queryString = string.Format(
             "set samplingmode = lab\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
            tagName.Replace("'", "\""), startDate.AddMinutes(-1), startDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        // get the range
        queryString = string.Format(
             "set samplingmode = rawbytime\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
            tagName.Replace("'", "\""), startDate, endDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        // always get an end value
        queryString = string.Format(
             "set samplingmode = lab\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
        tagName.Replace("'", "\""), endDate.AddMinutes(-1), endDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        return ds.Tables[0];
    }
}

And yes, I know, those queries should be parameterized.

回复收藏 0 原文

兲鉂ぱ嘚淚 2024-07-15 13:01:58

我们编写了一个包装 DLL，如下所示：

[DllImport("IHUAPI.dll", CallingConvention = CallingConvention.StdCall, EntryPoint = "ihuReadRawDataByTime@24")]
public static extern int ihuReadRawDataByTime(int serverhandle, string tagname, ref IHU_TIMESTAMP startTime, ref IHU_TIMESTAMP endTime, ref int noOfSamples, ref IHU_DATA_SAMPLE* dataValues);
...
private int _handle;

public HistorianTypes.ErrorCode ReadRawByTime(string tagName, DateTime startTime, DateTime endTime,
                                              out double[] timeStamps, out double[] values, out IhuComment [] comments)
{
    var startTimeStruct = new IhuApi.IHU_TIMESTAMP();  //Custom datetime to epoch extension method
    var endTimeStruct = new IhuApi.IHU_TIMESTAMP();

    int lRet = 0;
    int noOfSamples = 0;
    startTimeStruct = DateTimeToTimeStruct(dstZone.ToUniversalTime(startTime));
    endTimeStruct = DateTimeToTimeStruct(dstZone.ToUniversalTime(endTime));
    IhuApi.IHU_DATA_SAMPLE* dataSample = (IhuApi.IHU_DATA_SAMPLE*)new IntPtr(0);

    try {
        lRet = IhuApi.ihuReadRawDataByTime
            (
                _handle, // the handle returned from the connect
                tagName, // the single tagname to retrieve
                ref startTimeStruct, // start time for query
                ref endTimeStruct, // end time for query
                ref noOfSamples, // will be set by API
                ref dataSample // will be allocated and populated in the user API
            );
            ....

一些注意事项是 iFIX 将在启动时检查 DLL 是否已加载，因此您需要执行诸如动态加载/卸载 DLL 之类的操作，以便其他应用程序不会崩溃。我们通过动态删除/添加注册表项来做到这一点。

另一种情况是，如果您轮询 10,000 个样本，其中 1 个样本已损坏，则会删除所有 10,000 个样本。您需要实现一个错误数据处理程序，该处理程序将从错误数据的两侧开始，并逐步递增以获取错误样本两侧的所有数据。

有几个 C 头文件包含 DLL 的所有错误代码和函数头。

We wrote a wrapper DLL that looked like this like this:

[DllImport("IHUAPI.dll", CallingConvention = CallingConvention.StdCall, EntryPoint = "ihuReadRawDataByTime@24")]
public static extern int ihuReadRawDataByTime(int serverhandle, string tagname, ref IHU_TIMESTAMP startTime, ref IHU_TIMESTAMP endTime, ref int noOfSamples, ref IHU_DATA_SAMPLE* dataValues);
...
private int _handle;

public HistorianTypes.ErrorCode ReadRawByTime(string tagName, DateTime startTime, DateTime endTime,
                                              out double[] timeStamps, out double[] values, out IhuComment [] comments)
{
    var startTimeStruct = new IhuApi.IHU_TIMESTAMP();  //Custom datetime to epoch extension method
    var endTimeStruct = new IhuApi.IHU_TIMESTAMP();

    int lRet = 0;
    int noOfSamples = 0;
    startTimeStruct = DateTimeToTimeStruct(dstZone.ToUniversalTime(startTime));
    endTimeStruct = DateTimeToTimeStruct(dstZone.ToUniversalTime(endTime));
    IhuApi.IHU_DATA_SAMPLE* dataSample = (IhuApi.IHU_DATA_SAMPLE*)new IntPtr(0);

    try {
        lRet = IhuApi.ihuReadRawDataByTime
            (
                _handle, // the handle returned from the connect
                tagName, // the single tagname to retrieve
                ref startTimeStruct, // start time for query
                ref endTimeStruct, // end time for query
                ref noOfSamples, // will be set by API
                ref dataSample // will be allocated and populated in the user API
            );
            ....

Some notes are that iFIX will check if the DLL is loaded on startup so you need to do things like dynamically load/unload the DLL so that other applications don't crash. We did this by deleting/adding registry keys on the fly.

Another one is if you poll 10,000 samples and 1 of the samples are corrupted it will drop all 10,000 samples. You need to implement a bad data handler that will start at either side of the bad data and increment in steps to get all data either side of the bad sample.

There are several C header files that contain all of the error codes and the function header for the DLL.

回复收藏 0 原文