Argonne National Laboratory

Experimental Physics and
Industrial Control System

1994  1995  1996  1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  <20182019  Index 1994  1995  1996  1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  <20182019 
<== Date ==> <== Thread ==>

Subject: Re: EPICS data extraction with mysql and compressed data
From: "Kasemir, Kay" <kasemirk@ornl.gov>
To: Ghribi Adnan <adnan.ghribi@ganil.fr>, "tech-talk@aps.anl.gov" <tech-talk@aps.anl.gov>
Date: Tue, 9 Jan 2018 14:00:05 +0000

Hi:


The archive engine basically stores values on change, or less frequently.

For details see chapter 2 of https://ics-web.sns.ornl.gov/kasemir/archiver/manual.pdf because the ideas go back to the Channel Archiver.


It would be silly to write the same, unchanged value over and over. Plus we want to store the original time stamp of the PV as set by the IOC, not the time when it was received by the archive engine or written to Oracle/MySQL/Postgres. For retrieval, this mostly means two things:


When you are interested in data from time1 to time2, you need to start with the last sample at-or-before time1.

Especially when you look at a "setpoint" type of channel which is only changed by operators, so the last change might have been two weeks ago, but you want to see the value "now" (which is still the unchanged value from two weeks ago). See section 2.7.2 in the old manual.

For MySQL, that means

  SELECT smpl_time, nanosecs 

  FROM sample

  WHERE channel_id=? AND smpl_time<=?

  ORDER BY smpl_time DESC, nanosecs DESC LIMIT 1

For what it's worth, this is the biggest problem with the RDB-based archive. Selecting that single first sample is what can take most of the time when reading data. The following "SELECT smpl_time, nanosecs  FROM sample WHERE channel_id=? AND smpl_time BETWEEN ? AND ?" is usually pretty spiffy, even if it's a million samples.


Secondly, when you look at data for more than one channel, you'll need to somehow arrange the raw data for the channels which will all have their original time stamps into a spreadsheet-type arrangement, using for example 'staircase' or 'linear' interpolation, see section 2.7.3 of the old manual.


Thanks,

Kay




From: tech-talk-bounces@aps.anl.gov <tech-talk-bounces@aps.anl.gov> on behalf of Ghribi Adnan <adnan.ghribi@ganil.fr>
Sent: Tuesday, January 09, 2018 2:37 AM
To: tech-talk@aps.anl.gov
Subject: EPICS data extraction with mysql and compressed data
 
Dear all,

I have a question regarding recovering data archived by epics using mysql. I use a python code to automatically recover and analyse a set of data. Some of these data are constant and compressed by EPICS so when I extract them with respect to a given data stamp, it gives “nan”. The only way to get value is to have a variation. Looping the time stamps is not feasible as I can easily miss the right time stamp. Do you of of any way that could allow to directly extract constant compressed data ? 

Here is the code I use for the extraction :

def db_fetch1(variable, time1, time2):
    import pymysql
    import numpy as np
    conn = pymysql.connect(host='accmysqlslv', port=3306, user='archive', passwd='archive', db='archive2')
    cur = conn.cursor()
    command = "select float_val from sample s where s.channel_id = (select channel_id from channel where name = '" + variable + "') and smpl_time >= '" + time1 + "' and smpl_time < '"+ time2 + "'"
    cur.execute(command)
    output = []
    for row in cur:
        if np.isreal(row[0]) == True:
            output.append(row[0])
    cur.close()
    conn.close()
    output = np.array(output,dtype=float)
    return output

Thank you,

Best regards,
Adnan Ghribi

 

CNRS - GANIL PhD, Adnan GHRIBI

a: Bvd Henri Becquerel, 14000, Caen, France t: +33 2 31 45 46 80 f: +33 2 31 45 46 47

w: http://www.ganil-spiral2.eu/?set_language=en

 
                                                           


Replies:
Re: EPICS data extraction with mysql and compressed data Ghribi Adnan
References:
EPICS data extraction with mysql and compressed data Ghribi Adnan

Navigate by Date:
Prev: Re: Example for User Created File Types in the build system Benjamin Franksen
Next: asynUInt32DigitalClient constructor Davide Marcato
Index: 1994  1995  1996  1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  <20182019 
Navigate by Thread:
Prev: EPICS data extraction with mysql and compressed data Ghribi Adnan
Next: Re: EPICS data extraction with mysql and compressed data Ghribi Adnan
Index: 1994  1995  1996  1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  <20182019 
ANJ, 09 Jan 2018 Valid HTML 4.01! · Home · News · About · Base · Modules · Extensions · Distributions · Download ·
· Search · EPICS V4 · IRMIS · Talk · Bugs · Documents · Links · Licensing ·