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.

before 19.03.2019

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)

after 19.03.2019

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:

Python

from nxcals.api.extraction.data.builders import *
from pyspark.sql.functions import broadcast

Java

```java

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:

Python

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

Java

```java

List 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:

Python

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

Java

```java

Dataset 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