Working with fundamentals data

Currently in NXCALS there are following variables defined which are pointing to entities holding fundamental data:

Variable name Device/Property From To
CPS:NXCALS_FUNDAMENTAL CPS.TGM/FULL-TELEGRAM.STRC - 24.09.20 00:00:00
- XTIM.PX.SCY-CT/Acquisition} 24.09.20 00:00:00 -
LEI:NXCALS_FUNDAMENTAL LEI.TGM/FULL-TELEGRAM.STRC - 24.09.20 00:00:00
- XTIM.EX.SCY-CT/Acquisition 24.09.20 00:00:00 -
LNA:NXCALS_FUNDAMENTAL LNA.TGM/FULL-TELEGRAM.STRC - 20.03.19 00:00:00
- XTIM.AX.SCY-CT/Acquisition 20.03.19 00:00:00 -
PSB:NXCALS_FUNDAMENTAL PSB.TGM/FULL-TELEGRAM.STRC - 24.09.20 00:00:00
- XTIM.BX.SCY-CT/Acquisition 24.09.20 00:00:00 -
SPS:NXCALS_FUNDAMENTAL SPS.TGM/FULL-TELEGRAM.STRC - 19.03.19 00:00:00
- XTIM.SX.SCY-CT/Acquisition 19.03.19 00:00:00 -

Important

Data originates from TGM and XTIM devices and it must be taken into account during the extraction process in case when selected time frame spans over effective date range for each device.

Sometimes it is necessary to make use of field aliases (like in the provided code snippet), since field names in the corresponding entities schemas may differ.

That is especially valid when refering to such a field in a query condition for instance.

Below one can find an example of entity schemas corresponding to SPS fundamental data. Note difference in names for LSA cycle field: LSA_CYCLE <-> lsaCycleName.

root
 |-- BEAMID: integer (nullable = true)
 |-- BPNCY: integer (nullable = true)
 |-- BPNM: integer (nullable = true)
 |-- COMLN: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- CYCLE: integer (nullable = true)
 |-- CYTAG: integer (nullable = true)
 |-- DDEST: string (nullable = true)
 |-- DEST: string (nullable = true)
 |-- DURN: integer (nullable = true)
 |-- FTOP_ENG: integer (nullable = true)
 |-- INJ_ENG: integer (nullable = true)
 |-- LHCSEQE: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- LHCSEQR: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- MISC: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- MISC_A: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- MMODE: string (nullable = true)
 |-- NBINJ: integer (nullable = true)
 |-- NCYTAG: integer (nullable = true)
 |-- NDURN: integer (nullable = true)
 |-- NUSER: string (nullable = true)
 |-- PARTY: string (nullable = true)
 |-- SCNUM: integer (nullable = true)
 |-- SCTAG: integer (nullable = true)
 |-- SPCON: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- USER: string (nullable = true)
 |-- __LSA_CYCLE__: string (nullable = true)
 |-- nxcals_timestamp: long (nullable = true)
 |-- __record_version__: long (nullable = true)
 |-- acqStamp: long (nullable = true)
 |-- class: string (nullable = true)
 |-- cyclestamp: long (nullable = true)
 |-- device: string (nullable = true)
 |-- property: string (nullable = true)
 |-- selector: string (nullable = true)
 |-- nxcals_entity_id: long (nullable = true)
 |-- nxcals_variable_name: string (nullable = true)
root
 |-- BASIC_PERIOD_NB: integer (nullable = true)
 |-- BEAM_ID: integer (nullable = true)
 |-- BEAM_LEVEL_NORMAL: boolean (nullable = true)
 |-- BEAM_LEVEL_SPARE: boolean (nullable = true)
 |-- BEAM_STATE_DUMP: boolean (nullable = true)
 |-- BP_DURATION_MS: integer (nullable = true)
 |-- CYCLE_DURATION_MS: integer (nullable = true)
 |-- CYCLE_NB: integer (nullable = true)
 |-- CYCLE_TAG: integer (nullable = true)
 |-- DYN_DEST: string (nullable = true)
 |-- ENERGY_FTOP: integer (nullable = true)
 |-- ENERGY_INJ: integer (nullable = true)
 |-- INJECTED_BATCHES: integer (nullable = true)
 |-- LHC_BEAM_NOMINAL: boolean (nullable = true)
 |-- LSE_NB_BATCHES: integer (nullable = true)
 |-- LSE_NOMINAL_BEAM: boolean (nullable = true)
 |-- LSE_RING1: boolean (nullable = true)
 |-- LSE_RING2: boolean (nullable = true)
 |-- LSQ_ALLOW_CONTROL: boolean (nullable = true)
 |-- LSQ_CONTROL: boolean (nullable = true)
 |-- MACHINE_MODE: string (nullable = true)
 |-- NB_INJECTIONS: integer (nullable = true)
 |-- NEXT_LHC_CYCLE: boolean (nullable = true)
 |-- PARTICLE: string (nullable = true)
 |-- PROG_DEST: string (nullable = true)
 |-- SUPERCYCLE_NB: integer (nullable = true)
 |-- USER: string (nullable = true)
 |-- nxcals_timestamp: long (nullable = true)
 |-- __record_version__: long (nullable = true)
 |-- acqC: integer (nullable = true)
 |-- acqStamp: long (nullable = true)
 |-- class: string (nullable = true)
 |-- cyclestamp: long (nullable = true)
 |-- device: string (nullable = true)
 |-- lsaCycleName: string (nullable = true)
 |-- oCounter: long (nullable = true)
 |-- property: string (nullable = true)
 |-- selector: string (nullable = true)
 |-- nxcals_entity_id: long (nullable = true)
 |-- nxcals_variable_name: string (nullable = true)

Note

The fundamental variables which are defined in CALS have not been ported yet to NXCALS. The fundamental data is only accessible through the 5 entities for which filtering criteria must be applied "manually", such as timing user and LSA cycle name.

In order to see NXCALS data filtered by Fundamentals one can follow the steps below starting with:

Importing necessary libraries:

from nxcals.api.extraction.data.builders import *
from pyspark.sql.functions import broadcast
import cern.nxcals.api.extraction.data.builders.DataQuery;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.apache.spark.sql.functions.broadcast;

Retrieving fundamental data timestamps:

funds = DataQuery.builder(spark).byVariables() \
    .system('CMW') \
    .startTime('2018-09-03 00:00:00.000').endTime('2018-09-03 01:00:00.000') \
    .fieldAliases({'LSA_CYCLE': ['__LSA_CYCLE__', 'lsaCycleName']}) \
    .variable('SPS:NXCALS_FUNDAMENTAL') \
    .buildDataset()

funds.printSchema()
funds.show(10)

funds_filtered = funds.select("nxcals_timestamp").where("LSA_CYCLE = 'AWAKE_1INJ_FB60_FT850_Q20_2018_V1' and USER='AWAKE1' ")

print("fundamentals = ", funds.count())
print("fundamentals filtered = ", funds_filtered.count())
List<String> fieldAliasesList = new ArrayList<>();
fieldAliasesList.add("__LSA_CYCLE__");
fieldAliasesList.add("lsaCycleName");

Map<String, List<String>> fieldAliases = new HashMap<>();
fieldAliases.put("LSA_CYCLE", fieldAliasesList);

Dataset<Row> funds = DataQuery.builder(spark).byVariables()
    .system("CMW")
    .startTime("2018-09-03 00:00:00.000").endTime("2018-09-03 01:00:00.000")
    .fieldAliases(fieldAliases)
    .variable("SPS:NXCALS_FUNDAMENTAL")
    .buildDataset();

funds.printSchema();
funds.show(10);

Dataset<Row> funds_filtered = funds.select("nxcals_timestamp").where("LSA_CYCLE = 'AWAKE_1INJ_FB60_FT850_Q20_2018_V1' and USER='AWAKE1' ");

System.out.println("fundamentals = " + funds.count());
System.out.println("fundamentals filtered = " + funds_filtered.count());
Click to see expected application output...
root
 |-- BEAMID: integer (nullable = true)
 |-- BPNCY: integer (nullable = true)
 |-- BPNM: integer (nullable = true)
 |-- COMLN: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- CYCLE: integer (nullable = true)
 |-- CYTAG: integer (nullable = true)
 |-- DDEST: string (nullable = true)
 |-- DEST: string (nullable = true)
 |-- DURN: integer (nullable = true)
 |-- FTOP_ENG: integer (nullable = true)
 |-- INJ_ENG: integer (nullable = true)
 |-- LHCSEQE: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- LHCSEQR: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- LSA_CYCLE: string (nullable = true)
 |-- MISC: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- MISC_A: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- MMODE: string (nullable = true)
 |-- NBINJ: integer (nullable = true)
 |-- NCYTAG: integer (nullable = true)
 |-- NDURN: integer (nullable = true)
 |-- NUSER: string (nullable = true)
 |-- PARTY: string (nullable = true)
 |-- SCNUM: integer (nullable = true)
 |-- SCTAG: integer (nullable = true)
 |-- SPCON: struct (nullable = true)
 |    |-- elements: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- dimensions: array (nullable = true)
 |    |    |-- element: integer (containsNull = true)
 |-- USER: string (nullable = true)
 |-- nxcals_timestamp: long (nullable = true)
 |-- __record_version__: long (nullable = true)
 |-- acqStamp: long (nullable = true)
 |-- class: string (nullable = true)
 |-- cyclestamp: long (nullable = true)
 |-- device: string (nullable = true)
 |-- property: string (nullable = true)
 |-- selector: string (nullable = true)
 |-- nxcals_entity_id: long (nullable = true)
 |-- nxcals_variable_name: string (nullable = true)

+------+-----+----+-----+-----+-----+--------+--------+----+--------+-------+-------+-------+--------------------+----+------+-----+-----+------+-----+-----+-----+-----+-----+-----+-------+-------------------+------------------+-------------------+-------+-------------------+-------+------------------+--------+----------------+--------------------+
|BEAMID|BPNCY|BPNM|COMLN|CYCLE|CYTAG|   DDEST|    DEST|DURN|FTOP_ENG|INJ_ENG|LHCSEQE|LHCSEQR|           LSA_CYCLE|MISC|MISC_A|MMODE|NBINJ|NCYTAG|NDURN|NUSER|PARTY|SCNUM|SCTAG|SPCON|   USER|   nxcals_timestamp|__record_version__|           acqStamp|  class|         cyclestamp| device|          property|selector|nxcals_entity_id|nxcals_variable_name|
+------+-----+----+-----+-----+-----+--------+--------+----+--------+-------+-------+-------+--------------------+----+------+-----+-----+------+-----+-----+-----+-----+-----+-----+-------+-------------------+------------------+-------------------+-------+-------------------+-------+------------------+--------+----------------+--------------------+
|  9289| null|null| null| null| null|SPS_DUMP|SPS_DUMP|null|    null|   null|   null|   null|MD_26_L60_Q20_201...|null|  null| null| null|  null| null| null| null| null| null| null|    MD1|1535934766935000000|                 0|1535934766935000000|SPS.TGM|1535934766935000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
|  9289| null|null| null| null| null|SPS_DUMP|SPS_DUMP|null|    null|   null|   null|   null|MD_26_L60_Q20_201...|null|  null| null| null|  null| null| null| null| null| null| null|    MD1|1535936365335000000|                 0|1535936365335000000|SPS.TGM|1535936365335000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
| 59150| null|null| null| null| null| FTARGET| FTARGET|null|    null|   null|   null|   null|SFT_PRO_MTE_L4780...|null|  null| null| null|  null| null| null| null|   59| null| null|SFTPRO2|1535935396935000000|                 0|1535935396935000000|SPS.TGM|1535935396935000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
|  9289| null|null| null| null| null|SPS_DUMP|SPS_DUMP|null|    null|   null|   null|   null|MD_26_L60_Q20_201...|null|  null| null| null|  null| null| null| null| null| null| null|    MD1|1535936192535000000|                 0|1535936192535000000|SPS.TGM|1535936192535000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
|  9289| null|null| null| null| null|SPS_DUMP|SPS_DUMP|null|    null|   null|   null|   null|MD_26_L60_Q20_201...|null|  null| null| null|  null| null| null| null| null| null| null|    MD1|1535933514135000000|                 0|1535933514135000000|SPS.TGM|1535933514135000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
|  9289| null|null| null| null| null|SPS_DUMP|SPS_DUMP|null|    null|   null|   null|   null|MD_26_L60_Q20_201...|null|  null| null| null|  null| null| null| null| null| null| null|    MD1|1535934723735000000|                 0|1535934723735000000|SPS.TGM|1535934723735000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
| 59150| null|null| null| null| null| FTARGET| FTARGET|null|    null|   null|   null|   null|SFT_PRO_MTE_L4780...|null|  null| null| null|  null| null| null| null| null| null| null|SFTPRO2|1535934986535000000|                 0|1535934986535000000|SPS.TGM|1535934986535000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
| 59150| null|null| null| null| null| FTARGET| FTARGET|null|    null|   null|   null|   null|SFT_PRO_MTE_L4780...|null|  null| null| null|  null| null| null| null| null| null| null|SFTPRO2|1535935936935000000|                 0|1535935936935000000|SPS.TGM|1535935936935000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
|  9289| null|null| null| null| null|SPS_DUMP|SPS_DUMP|null|    null|   null|   null|   null|MD_26_L60_Q20_201...|null|  null| null| null|  null| null| null| null| null| null| null|    MD1|1535933082135000000|                 0|1535933082135000000|SPS.TGM|1535933082135000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
|  9636| null|null| null| null| null|   AWAKE|   AWAKE|null|    null|   null|   null|   null|AWAKE_1INJ_FB60_F...|null|  null| null| null|  null| null| null| null| null| null| null| AWAKE1|1535936336535000000|                 0|1535936336535000000|SPS.TGM|1535936336535000000|SPS.TGM|FULL-TELEGRAM.STRC|    null|         1613095|SPS:NXCALS_FUNDAM...|
+------+-----+----+-----+-----+-----+--------+--------+----+--------+-------+-------+-------+--------------------+----+------+-----+-----+------+-----+-----+-----+-----+-----+-----+-------+-------------------+------------------+-------------------+-------+-------------------+-------+------------------+--------+----------------+--------------------+
only showing top 10 rows

fundamentals =  500
fundamentals filtered =  166

Retrieving data filtered by fundamentals:

data = DataQuery.builder(spark).byVariables() \
    .system('CMW') \
    .startTime('2018-09-01 00:00:00.000').endTime('2018-09-31 23:59:59.999') \
    .variable('SPS.BQM:BUNCH_INTENSITIES') \
    .buildDataset()

data_filtered = data.join(broadcast(funds_filtered), "nxcals_timestamp")

print("data = ", data.count())
print("data filtered = ", data_filtered.count())

data_filtered.sort('nxcals_timestamp').show()
Dataset<Row> data = DataQuery.builder(spark).byVariables()
    .system("CMW")
    .startTime("2018-09-01 00:00:00.000").endTime("2018-09-31 23:59:59.999")
    .variable("SPS.BQM:BUNCH_INTENSITIES")
    .buildDataset();

Dataset<Row> data_filtered = data.join(broadcast(funds_filtered), "nxcals_timestamp");

System.out.println("data = " + data.count());
System.out.println("data filtered = " + data_filtered.count());

data_filtered.sort("nxcals_timestamp").show();

Note

Please note the usage of broadcast hint. More details can be found here.

Click to see expected application output...
data =  29701
data filtered =  91
+-------------------+--------------------+----------------+--------------------+
|   nxcals_timestamp|        nxcals_value|nxcals_entity_id|nxcals_variable_name|
+-------------------+--------------------+----------------+--------------------+
|1535932815735000000|[[3028.3342, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535932837335000000|[[3232.1318, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535932880535000000|[[3157.6013, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535932923735000000|[[3266.3342, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535932945335000000|[[3129.2654, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535932966935000000|[[3271.799, 0.0, ...|           57307|SPS.BQM:BUNCH_INT...|
|1535933010135000000|[[2827.5322, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933031735000000|[[3250.2654, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933053335000000|[[3317.333, 0.0, ...|           57307|SPS.BQM:BUNCH_INT...|
|1535933074935000000|[[2886.9983, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933139735000000|[[2868.7346, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933226135000000|[[3078.3315, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933247735000000|[[3109.0674, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933269335000000|[[3272.6013, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933355735000000|[[2857.0017, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933398935000000|[[3191.5984, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933420535000000|[[2906.8005, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933463735000000|[[3241.5347, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933485335000000|[[3034.9326, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
|1535933506935000000|[[2956.6658, 0.0,...|           57307|SPS.BQM:BUNCH_INT...|
+-------------------+--------------------+----------------+--------------------+
only showing top 20 rows