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