DbSignal

 
 
 

Firstly, we recommend the use of general purpose browsers for ad-hoc queries and exploration for signal hierarchy:

In order to programatically query PM, CALS, or NXCALS database, a dedicated API along with parameters is required. An overview is summarised in the table below.

  PM PM CALS CALS* NXCALS NXCALS
query type event signal signal feature signal feature
input system, source, className system, source, className, signal signal signal, features system, (device, property), signal system, (device, property), signal, features
time definition time range timestamp time range time range time range time range
time unit ns ns s s ns ns
return type json json dict of list dict of list spark dataframe spark dataframe
execution time fast fast can be slow fast slow fast
execution type serial serial serial ? serial parallel
use simple simple simple simple simple requires good knowledge of spark

*The pytimber package provides an access to feature queries. Since CALS is about to be discontinued, we decided not to support this query in favor of NXCALS.

The DbSignal provides a unified access to PM, CALS, and NXCALS databases in order to return a common data format, a pandas DataFrame. This enables a more advanced signal processing capabilities. The DbSignal module uses native API for each database:

 
  • PM signal query
In [47]:
import requests
import json

response = requests.get('http://pm-api-pro/v2/pmdata/signal?system=FGC&className=51_self_pmd&source=RPTE.UA47.RB.A45&timestampInNanos=1426220469520000000&signal=STATUS.I_MEAS')
json_response = json.loads(response.text)

name = json_response['content'][0]['namesAndValues'][0]['name']
time = json_response['content'][0]['namesAndValues'][1]['value']
value = json_response['content'][0]['namesAndValues'][0]['value']

pd.DataFrame(value, time, [name]).plot()
Out[47]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f84bdcf77f0>
 
 
  • CALS signal query
In [48]:
import pandas as pd
import pytimber
ldb = pytimber.LoggingDB()

i_meas = ldb.get("RPTE.UA47.RB.A45:I_MEAS", '2015-03-13 05:20:49.4910002', '2015-03-13 05:22:49.4910002')
pd.DataFrame(i_meas.get("RPTE.UA47.RB.A45:I_MEAS")[1], i_meas.get("RPTE.UA47.RB.A45:I_MEAS")[0], ["RPTE.UA47.RB.A45:I_MEAS"]).plot()
Out[48]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f848406d320>
 
 
  • NXCALS signal query
In [49]:
from cern.nxcals.pyquery.builders import *
import pandas as pd

i_meas_df = DevicePropertyQuery.builder(spark).system('CMW') \
    .startTime(pd.Timestamp('2015-03-13T04:20:59.491000000').to_datetime64())\
    .endTime(pd.Timestamp('2015-03-13T04:22:49.491000000')) \
    .entity().device('RPTE.UA47.RB.A45').property('SUB') \
    .buildDataset().select('acqStamp', 'I_MEAS').dropna().sort("acqStamp").toPandas()

i_meas_df.set_index(i_meas_df['acqStamp'], inplace=True)
i_meas_df.drop(columns='acqStamp', inplace=True)
i_meas_df.plot()
Out[49]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f847dc45ba8>
 
 

Signal class encapsulates behavior for working with signal databases. It provides read and write methods for accessing Post Mortem, CALS and InfluxDB APIs.

Read method supports only scalar arguments and returns a pandas Series.
In case of errors, the read method returns a warning message with the raw API use to query in order to quickly reproduce the error. This way, one can exclude the error introduced by the lhcsmapi.

 

1. PM Database

 
  • PM Success Response
In [50]:
from lhcsmapi.dbsignal.Signal import Signal
system = 'FGC'
source = 'RPTE.UA47.RB.A45'
className = '51_self_pmd'
signal = 'STATUS.I_MEAS'
eventTime = 1426220469520000000
i_meas_df = Signal().read('pm', system=system, source=source, className=className, 
                          signal=signal, eventTime=eventTime)
i_meas_df.plot()
Out[50]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f847dc39128>
 
In [51]:
from lhcsmapi.dbsignal.Signal import Signal
system = "QPS"
className = "DQAMCNMB_PMHSU"
magnet = "B20L5"
signal = "I_HDS_4"
timestamp = 1426220469491000000
i_hds_4_df = Signal().read('pm', system=system, className=className, source=magnet,
                           signal=signal, eventTime=timestamp)
i_hds_4_df.plot()
Out[51]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f847e0992e8>
 
 
  • PM Fail Response
In [52]:
from lhcsmapi.dbsignal.Signal import Signal
system = "QPS"
className = "DQAMCNMB_PMHSU"
magnet = "B20L5"
signal = "I_HDS_4"
timestamp = 142622046949100000 # last zero removed
i_hds_4_df = Signal().read('pm', system=system, className=className, source=magnet,
                              signal=signal, eventTime=timestamp)
 
/eos/home-m/mmacieje/SWAN_projects/lhc-sm-api/lhcsmapi/dbsignal/post_mortem/PmDbRequest.py:147: UserWarning: Querying Post Mortem failed using the following query: http://pm-api-pro/v2/pmdata/signal?system=QPS&className=DQAMCNMB_PMHSU&source=B20L5&timestampInNanos=142622046949100000&signal=I_HDS_4
  warnings.warn(warning)
 

2. CALS Database

 
  • CALS Success Response

(t_start, t_end)

In [53]:
from lhcsmapi.dbsignal.Signal import Signal
import pytimber
ldb = pytimber.LoggingDB()

signal = "RPTE.UA47.RB.A45:I_MEAS"
ts_start = 1426220459491000000
ts_end = 1426220479491000000
i_meas_df = Signal().read('cals', signal=signal, t_start=ts_start, t_end=ts_end, ldb=ldb)
i_meas_df.plot()
 
WARNING:cmmnbuild_dep_manager:JVM is already started
Out[53]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f847dc67eb8>
 
 

(t_start, t_start+duration[0])

In [54]:
from lhcsmapi.dbsignal.Signal import Signal
import pytimber
ldb = pytimber.LoggingDB()

signal = "RPTE.UA47.RB.A45:I_MEAS"
ts_start = 1426220459491000000
duration = [(20, 's')]
i_meas_df = Signal().read('cals', signal=signal, t_start=ts_start, duration=duration, ldb=ldb)
i_meas_df.plot()
 
WARNING:cmmnbuild_dep_manager:JVM is already started
Out[54]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f847dd928d0>
 
 

(t_start-duration[0], t_start+duration[1])

In [55]:
from lhcsmapi.dbsignal.Signal import Signal
import pytimber
ldb = pytimber.LoggingDB()

signal = "RPTE.UA47.RB.A45:I_MEAS"
ts_start = 1426220469491000000
duration = [(10, 's'), (10, 's')]
i_meas_df = Signal().read('cals', signal=signal, t_start=ts_start, duration=duration, ldb=ldb)
i_meas_df.plot()
 
WARNING:cmmnbuild_dep_manager:JVM is already started
Out[55]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f847ddcb080>
 
 
  • CALS Fail Response
In [56]:
from lhcsmapi.dbsignal.Signal import Signal
import pytimber
ldb = pytimber.LoggingDB()

signal = "RPTE.UA47.RB.A45:I_MEA" # last S missing
ts_start = 1426220469491000000
duration = [(10, 's'), (10, 's')]
i_meas_df = Signal().read('cals', signal=signal, t_start=ts_start, duration=duration, ldb=ldb)
 
WARNING:cmmnbuild_dep_manager:JVM is already started
WARNING:pytimber.pytimber:No variables found.
/eos/home-m/mmacieje/SWAN_projects/lhc-sm-api/lhcsmapi/dbsignal/cals/CalsDbSignal.py:180: UserWarning: Querying CALS with the parameters signal: RPTE.UA47.RB.A45:I_MEA, start time: 1426220459.4910002 and end time: 1426220479.4910002 returned no data.
            An empty DataFrame is returned.
            ___________________________________________________________________________________
            Reproduce the error using the raw pytimber API:

            import pytimber
            ldb = pytimber.LoggingDB()
            ldb.get("RPTE.UA47.RB.A45:I_MEA", 1426220459.4910002, 1426220479.4910002)
  warnings.warn(warning)
 

3. NXCALS Database

 
  • NXCALS Success Response
In [57]:
from lhcsmapi.dbsignal.Signal import Signal

nxcals_system = 'CMW'
nxcals_device = 'RPTE.UA47.RB.A45'
nxcals_property = 'SUB'
signal = 'I_MEAS'
t_start = 1426220469491000000
duration = [(10, 's'), (100, 's')]

i_meas_df = Signal().read('nxcals', nxcals_system=nxcals_system,  nxcals_device=nxcals_device, nxcals_property=nxcals_property, 
                          signal=signal, t_start=t_start, duration=duration, spark=spark)
i_meas_df.plot()
Out[57]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f847de20ef0>
 
In [58]:
from lhcsmapi.dbsignal.Signal import Signal
nxcals_system = 'WINCCOA'

signal = 'DCBA.15R4.R:U_MAG'
t_start = 1426220469491000000
duration = [(10, 's'), (100, 's')]

u_mag_df = Signal().read('nxcals', nxcals_system=nxcals_system,
                          signal=signal, t_start=t_start, duration=duration, spark=spark)
u_mag_df.plot()
Out[58]:
<matplotlib.axes._subplots.AxesSubplot at 0x7f847dcd67b8>
 
In [59]:
from lhcsmapi.dbsignal.Signal import Signal

bmode_df = Signal().read('nxcals', nxcals_system='CMW',
              signal='HX:BMODE', t_start='2015-03-13 05:20:59.4910002', t_end='2015-04-13 05:20:59.4910002', spark=spark)
bmode_df.head()
Out[59]:
  HX:BMODE
nxcals_timestamp  
1426513864882000000 SETUP
1426515551191000000 NOBEAM
1426597426610000000 SETUP
1426597576404000000 STABLE
1426598591040000000 UNSTABLE
 
  • NXCALS Fail Response
In [60]:
from lhcsmapi.dbsignal.Signal import Signal
nxcals_system = 'CMW'
nxcals_device = 'RPTE.UA47.RB.A4' # missing last 5
nxcals_property = 'SUB'
signal = 'I_MEAS'
t_start = 1426220469491000000
duration = [(10, 's'), (100, 's')]

i_meas_df = Signal().read('nxcals', nxcals_system=nxcals_system,  nxcals_device=nxcals_device, nxcals_property=nxcals_property, 
                          signal=signal, t_start=t_start, duration=duration, spark=spark)
i_meas_df.plot()
 
---------------------------------------------------------------------------
Py4JJavaError                             Traceback (most recent call last)
/cvmfs/sft.cern.ch/lcg/views/LCG_95apython3_nxcals/x86_64-centos7-gcc7-opt/lib/python3.6/site-packages/pyspark/sql/utils.py in deco(*a, **kw)
     62         try:
---> 63             return f(*a, **kw)
     64         except py4j.protocol.Py4JJavaError as e:

/cvmfs/sft.cern.ch/lcg/views/LCG_95apython3_nxcals/x86_64-centos7-gcc7-opt/lib/python3.6/site-packages/py4j/protocol.py in get_return_value(answer, gateway_client, target_id, name)
    327                     "An error occurred while calling {0}{1}{2}.\n".
--> 328                     format(target_id, ".", name), value)
    329             else:

Py4JJavaError: An error occurred while calling o210.select.
: org.apache.spark.sql.AnalysisException: cannot resolve '`acqStamp`' given input columns: [__record_version__, nxcals_entity_id, class, __record_timestamp__, property, device];;
'Project ['acqStamp, 'I_MEAS]
+- LocalRelation <empty>, [nxcals_entity_id#282L, device#283, property#284, class#285, __record_version__#286L, __record_timestamp__#287L]

	at org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)
	...

Exception: Querying NXCALS with the parameters signal: I_MEAS, start time: 2015-03-13T04:20:59.491000000, end time: 2015-03-13T04:22:49.491000000, nxcals_system CMW, nxcals_device RPTE.UA47.RB.A4, nxcals_property SUB failed
                            ___________________________________________________________________________________
                            Reproduce the error using the raw NXCALS API:

                            from cern.nxcals.pyquery.builders import *
                            DevicePropertyQuery.builder(spark).nxcals_system(''CMW'').startTime(''2015-03-13T04:20:59.491000000'').endTime(''2015-03-13T04:22:49.491000000'').entity().nxcals_device(''RPTE.UA47.RB.A4'').nxcals_property(''SUB'').buildDataset().select('acqStamp', ''signal'').dropna().sort("acqStamp").toPandas()