Create an ETL job for extracting 2s data from accelerator control system databases

[martyngigg]

We were kindly provided a copy, dated August 2024, of the Influx database storing various aspects of the control system channels.

Create an ETL job to extract the machinestate data into the lakehouse.

In a "real" version we'd likely want to connect directly to the MQTT broker that pushes the data to Influx.

[martyngigg]

After discussions with the controls team:

• Influx contains only data from Vsystem using the Vsystem channel naming scheme. While the PVEcho bridge is mirroring data between EPICS and Vsystem it is not replicating everything, i.e. new equipment that only uses EPICS. It contains data from >2018-2019 onwards.
• The EPICS archiver appliance archives data from EPICS PVs and contains data from ~2021-2022. The PVEcho system echos Vsystem channels using the new PV names into the EPICS archiver but only from when the system was setup; there was no backfill.
• The PV naming scheme is likely to evolve, for example there may become standardized variable names for things like temp=temperature, press=pressure etc we need to cope with that.
• Some equipment will never move to EPICS so will only exist in Vsystem/Influx.

The new PV naming scheme allows for better grouping around location/equipment and offers a solution to present users with a easier with better groupings of PV/channel data.

The controls team have code to generate a dictionary of lookup names "Vsystem channel name" -> "EPICS PVs". This would be useful to compute the tables that users will see when charting CS channels.