DbSignal

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

• The new PM web browser is available at the following link: http://post-mortem-paas.web.cern.ch/
• The new TIMBER NXCALS web browser is available at: https://timber.cern.ch/
• In order to find the mapping between CALS variables and NXCALS variable and metadata (along with the data migration status), please consult: https://ccde.cern.ch/dataBrowser/search?acwReportName=CALS-to-NXCALS%20Data%20Migration&domainName=CALS

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 REST API: http://pm-api-pro/
• pytimber CALS API: https://github.com/rdemaria/pytimber
• NXCALS python API: http://nxcals-docs.web.cern.ch/0.4.11/

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

BROKEN IMAGE NOT MOVED (drupal url : https://sigmon.web.cern.ch/node/25)

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
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()