3b_data_integration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Section 3b: Data Integration\n",
    "\n",
    "Creation process of the joined dataset `prom_slurm_joined.parquet` that is already ready-to-use uploaded to the Zenodo repository.\n",
    "Runtime of the notebook was around 1 hour and it needs 50-100 GB storage including sratch space in `./checkpoint`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data integration: we combine job and node data by joining all job data collected by SLURM with individual timestamps collected by Prometheus. This figure was generated manually with a diagram editor.\n",
    "\n",
    "![image](plots/section_3/data-integration.png)\n",
    "\n",
    "Fig. 2: An example of the data integration process. We\n",
    "match each job record to the fine granular 30s-interval\n",
    "timestamps of the node dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from util.read_and_print_df import *\n",
    "from util.handle_nan import *\n",
    "from util.extract_json_attributes import *\n",
    "from util.plotting import *\n",
    "import pyspark.sql.functions as F\n",
    "from pyspark.sql.types import LongType\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Directory already exists\n"
     ]
    }
   ],
   "source": [
    "# define here your path for scratch space (checkpoint folder for dataframes)\n",
    "checkpoint_folder = \"checkpoint\"\n",
    "\n",
    "def create_directory(directory_name):\n",
    "    if not os.path.exists(directory_name):\n",
    "        try:\n",
    "            os.mkdir(directory_name)\n",
    "            print(f\"Directory created successfully\")\n",
    "        except OSError as e:\n",
    "            print(f\"Error creating directory '{directory_name}': {e}\")\n",
    "    else:\n",
    "        print(f\"Directory already exists\")\n",
    "\n",
    "create_directory(checkpoint_folder)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Assigning 541 GB of memory per spark driver and executor, and use 126 cores.\n"
     ]
    }
   ],
   "source": [
    "spark = get_spark_session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+\n",
      "|id       |timestamp          |node |node_time_seconds|node_load15|node_power_usage|up |node_netstat_Tcp_OutSegs|node_netstat_Tcp_InErrs|node_context_switches_total|node_load5|node_load1|node_memory_Active_bytes|node_netstat_Tcp_RetransSegs|node_netstat_Udp_InErrors|node_memory_Dirty_bytes|node_ambient_temp|node_netstat_Icmp_InMsgs|node_netstat_Udp_InDatagrams|node_intr_total|node_netstat_Tcp_InSegs|node_memory_Percpu_bytes|node_boot_time_seconds|node_netstat_Udp_OutDatagrams|node_netstat_Icmp_InErrors|node_procs_blocked|node_netstat_Icmp_OutMsgs|node_memory_MemFree_bytes|node_procs_running|node_forks_total|node_hwmon_temp_celsius_min|node_hwmon_temp_celsius_mean|node_hwmon_temp_celsius_max|node_filesystem_avail_bytes_sum|node_filesystem_files_sum|node_network_transmit_bytes_total_sum|node_filesystem_device_error_sum|node_disk_written_bytes_total_sum|node_filesystem_free_bytes_sum|node_thermal_zone_temp_min|node_thermal_zone_temp_mean|node_thermal_zone_temp_max|node_disk_read_bytes_total_sum|nvidia_gpu_memory_used_bytes_sum|nvidia_gpu_temperature_celsius_0|nvidia_gpu_temperature_celsius_1|nvidia_gpu_temperature_celsius_2|nvidia_gpu_temperature_celsius_3|nvidia_gpu_temperature_celsius_min|nvidia_gpu_temperature_celsius_mean|nvidia_gpu_temperature_celsius_max|node_arp_entries_sum|nvidia_gpu_fanspeed_percent_min|nvidia_gpu_fanspeed_percent_mean|nvidia_gpu_fanspeed_percent_max|node_filesystem_files_free_sum|nvidia_gpu_power_usage_milliwatts_0|nvidia_gpu_power_usage_milliwatts_1|nvidia_gpu_power_usage_milliwatts_2|nvidia_gpu_power_usage_milliwatts_3|nvidia_gpu_power_usage_milliwatts_min|nvidia_gpu_power_usage_milliwatts_sum|nvidia_gpu_power_usage_milliwatts_mean|nvidia_gpu_power_usage_milliwatts_max|node_filesystem_size_bytes_sum|node_disk_writes_completed_total_sum|nvidia_gpu_duty_cycle_min|nvidia_gpu_duty_cycle_mean|nvidia_gpu_duty_cycle_max|node_network_transmit_packets_total_sum|node_udp_queues_sum|node_network_receive_bytes_total_sum|node_network_receive_packets_total_sum|node_network_receive_multicast_total_sum|node_disk_io_now_sum|node_rapl_package_joules_total_sum|node_network_receive_drop_total_sum|timestamp_seconds|node_network_transmit_bytes_total_sum_delta|node_disk_written_bytes_total_sum_delta|node_disk_read_bytes_total_sum_delta|node_disk_writes_completed_total_sum_delta|node_network_transmit_packets_total_sum_delta|node_network_receive_bytes_total_sum_delta|node_network_receive_packets_total_sum_delta|node_network_receive_multicast_total_sum_delta|node_rapl_package_joules_total_sum_delta|node_network_receive_drop_total_sum_delta|timestamp_seconds_delta|node_rapl_package_power_sum|gpu_node|\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+\n",
      "|106894969|2022-10-11 04:17:00|r28n3|1.66546E9        |3.98       |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |4.2       |4.16      |2.35916E10              |271230.0                    |0.0                      |16384.0                |25.0             |423.0                   |7272060.0                   |1.41723E10     |1.43194E9              |8.20511E7               |1.66134E9             |7401900.0                    |198.0                     |0.0               |419.0                    |1.57859E11               |4.0               |3.75065E7       |26.0                       |29.19                       |36.0                       |2.564558897152E12              |6.8497933E8              |8.46171547648E12                     |0.0                             |1.705329791488E12                |2.564612583424E12             |31.0                      |33.5                       |36.0                      |3.7710436352E10               |1.5011938304E10                 |59.0                            |32.0                            |35.0                            |34.0                            |32.0                              |40.0                               |59.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435874E8                  |104137.0                           |16758.0                            |27738.0                            |21007.0                            |16758.0                              |169640.0                             |42410.0                               |104137.0                             |2.602202996736E12             |7.3244452E7                         |0.0                      |15.0                      |60.0                     |1.956308731E9                          |0.0                |2.6822646841991E13                  |3.843764456E9                         |0.0                                     |0.0                 |348559.42                         |0.0                                |1665461820       |89727.0                                    |37376.0                                |0.0                                 |8.0                                       |97.0                                         |19008.0                                   |111.0                                       |0.0                                           |2675.9099999999744                      |0.0                                      |30.0                   |89.19699999999915          |1       |\n",
      "|106894970|2022-10-11 04:17:30|r28n3|1.66546E9        |3.89       |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |3.92      |3.04      |2.35326E10              |271230.0                    |0.0                      |16384.0                |25.0             |423.0                   |7272060.0                   |1.41723E10     |1.43197E9              |8.20511E7               |1.66134E9             |7401910.0                    |198.0                     |0.0               |419.0                    |1.56576E11               |22.0              |3.75068E7       |25.0                       |28.23                       |34.0                       |2.564558897152E12              |6.8497933E8              |8.461716147725E12                    |0.0                             |1.705329832448E12                |2.564612583424E12             |30.0                      |31.5                       |33.0                      |3.7710436352E10               |1.5011938304E10                 |56.0                            |32.0                            |35.0                            |35.0                            |32.0                              |39.5                               |56.0                              |229.0               |40.0                           |40.75                           |41.0                           |6.84435874E8                  |75301.0                            |16863.0                            |27634.0                            |21074.0                            |16863.0                              |140872.0                             |35218.0                               |75301.0                              |2.602202996736E12             |7.3244457E7                         |0.0                      |0.0                       |0.0                      |1.956313201E9                          |0.0                |2.682418133668E13                   |3.843935316E9                         |0.0                                     |0.0                 |350707.96                         |0.0                                |1665461850       |671245.0                                   |40960.0                                |0.0                                 |5.0                                       |4470.0                                       |1.534494689E9                             |170860.0                                    |0.0                                           |2148.5400000000373                      |0.0                                      |30.0                   |71.61800000000125          |1       |\n",
      "|106894971|2022-10-11 04:18:00|r28n3|1.66546E9        |3.8        |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |3.64      |2.23      |2.358E10                |271230.0                    |0.0                      |8192.0                 |25.0             |423.0                   |7272060.0                   |1.41724E10     |1.43197E9              |8.20511E7               |1.66134E9             |7401910.0                    |198.0                     |0.0               |419.0                    |1.71775E11               |18.0              |3.75068E7       |26.0                       |28.08                       |33.0                       |2.564558897152E12              |6.8497933E8              |8.461716223075E12                    |0.0                             |1.705329928704E12                |2.564612583424E12             |30.0                      |31.5                       |33.0                      |3.7710436352E10               |1.5011938304E10                 |54.0                            |32.0                            |35.0                            |34.0                            |32.0                              |38.75                              |54.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435874E8                  |74283.0                            |16835.0                            |27373.0                            |21099.0                            |16835.0                              |139590.0                             |34897.5                               |74283.0                              |2.602202996736E12             |7.3244472E7                         |0.0                      |1.5                       |6.0                      |1.956313281E9                          |0.0                |2.6824181349974E13                  |3.843935417E9                         |0.0                                     |0.0                 |352836.64                         |0.0                                |1665461880       |75350.0                                    |96256.0                                |0.0                                 |15.0                                      |80.0                                         |13294.0                                   |101.0                                       |0.0                                           |2128.679999999993                       |0.0                                      |30.0                   |70.95599999999976          |1       |\n",
      "|106894972|2022-10-11 04:18:30|r28n3|1.66546E9        |3.71       |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |3.39      |1.75      |2.35799E10              |271230.0                    |0.0                      |16384.0                |25.0             |423.0                   |7272060.0                   |1.41724E10     |1.43197E9              |8.20511E7               |1.66134E9             |7401910.0                    |198.0                     |0.0               |419.0                    |1.71782E11               |17.0              |3.75068E7       |25.0                       |27.92                       |32.0                       |2.564558897152E12              |6.8497933E8              |8.461716278853E12                    |0.0                             |1.705330016768E12                |2.564612583424E12             |30.0                      |32.0                       |34.0                      |3.7710436352E10               |1.5011938304E10                 |52.0                            |32.0                            |35.0                            |34.0                            |32.0                              |38.25                              |52.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435874E8                  |73393.0                            |16807.0                            |27234.0                            |21013.0                            |16807.0                              |138447.0                             |34611.75                              |73393.0                              |2.602202996736E12             |7.3244481E7                         |0.0                      |0.0                       |0.0                      |1.956313328E9                          |0.0                |2.682418135776E13                   |3.843935496E9                         |0.0                                     |0.0                 |354952.28                         |0.0                                |1665461910       |55778.0                                    |88064.0                                |0.0                                 |9.0                                       |47.0                                         |7786.0                                    |79.0                                        |0.0                                           |2115.640000000014                       |0.0                                      |30.0                   |70.5213333333338           |1       |\n",
      "|106894973|2022-10-11 04:19:00|r28n3|1.66546E9        |3.86       |672.0           |1.0|1.94796E9               |0.0                    |1.05184E12                 |3.88      |4.49      |3.73465E10              |271230.0                    |0.0                      |8380420.0              |25.0             |423.0                   |7272070.0                   |1.41726E10     |1.43213E9              |8.20511E7               |1.66134E9             |7401920.0                    |198.0                     |0.0               |419.0                    |1.50142E11               |24.0              |3.75074E7       |26.0                       |30.69                       |36.0                       |2.564558811136E12              |6.8497933E8              |8.461719749204E12                    |0.0                             |1.705351074816E12                |2.564612497408E12             |35.0                      |35.5                       |36.0                      |3.7710436352E10               |1.5011938304E10                 |51.0                            |32.0                            |35.0                            |34.0                            |32.0                              |38.0                               |51.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435865E8                  |73411.0                            |16632.0                            |27784.0                            |20959.0                            |16632.0                              |138786.0                             |34696.5                               |73411.0                              |2.602202996736E12             |7.3245375E7                         |0.0                      |2.25                      |9.0                      |1.956334291E9                          |0.0                |2.6831838806489E13                  |3.844791768E9                         |0.0                                     |0.0                 |357469.73                         |0.0                                |1665461940       |3470351.0                                  |2.1058048E7                            |0.0                                 |894.0                                     |20963.0                                      |7.657448729E9                             |856272.0                                    |0.0                                           |2517.4499999999534                      |0.0                                      |30.0                   |83.91499999999844          |1       |\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+---------+\n",
      "|id   |submit_date        |start_date         |end_date           |node  |nodetypes|numnodes|numcores|state    |\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+---------+\n",
      "|58607|2022-01-14 17:39:50|2022-01-15 06:39:07|2022-01-15 14:30:47|r15n11|normal   |1       |16      |COMPLETED|\n",
      "|58786|2022-01-14 17:39:50|2022-01-15 06:39:07|2022-01-15 16:27:53|r11n8 |normal   |1       |16      |COMPLETED|\n",
      "|58669|2022-01-14 17:39:50|2022-01-15 06:41:37|2022-01-15 15:41:45|r14n23|normal   |1       |16      |COMPLETED|\n",
      "|58548|2022-01-14 17:39:50|2022-01-15 06:42:37|2022-01-15 13:38:40|r12n3 |normal   |1       |16      |COMPLETED|\n",
      "|58848|2022-01-14 17:39:50|2022-01-15 06:46:38|2022-01-15 17:11:13|r25n29|normal   |1       |16      |COMPLETED|\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_prom = spark.read.parquet(path_node_dataset)\n",
    "df_slurm = spark.read.parquet(path_job_dataset)\n",
    "\n",
    "df_prom.show(5, False)\n",
    "df_slurm.show(5, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('id', 'decimal(20,0)'), ('timestamp', 'timestamp'), ('node', 'string'), ('node_time_seconds', 'double'), ('node_load15', 'double'), ('node_power_usage', 'double'), ('up', 'double'), ('node_netstat_Tcp_OutSegs', 'double'), ('node_netstat_Tcp_InErrs', 'double'), ('node_context_switches_total', 'double'), ('node_load5', 'double'), ('node_load1', 'double'), ('node_memory_Active_bytes', 'double'), ('node_netstat_Tcp_RetransSegs', 'double'), ('node_netstat_Udp_InErrors', 'double'), ('node_memory_Dirty_bytes', 'double'), ('node_ambient_temp', 'double'), ('node_netstat_Icmp_InMsgs', 'double'), ('node_netstat_Udp_InDatagrams', 'double'), ('node_intr_total', 'double'), ('node_netstat_Tcp_InSegs', 'double'), ('node_memory_Percpu_bytes', 'double'), ('node_boot_time_seconds', 'double'), ('node_netstat_Udp_OutDatagrams', 'double'), ('node_netstat_Icmp_InErrors', 'double'), ('node_procs_blocked', 'double'), ('node_netstat_Icmp_OutMsgs', 'double'), ('node_memory_MemFree_bytes', 'double'), ('node_procs_running', 'double'), ('node_forks_total', 'double'), ('node_hwmon_temp_celsius_min', 'double'), ('node_hwmon_temp_celsius_mean', 'double'), ('node_hwmon_temp_celsius_max', 'double'), ('node_filesystem_avail_bytes_sum', 'double'), ('node_filesystem_files_sum', 'double'), ('node_network_transmit_bytes_total_sum', 'double'), ('node_filesystem_device_error_sum', 'double'), ('node_disk_written_bytes_total_sum', 'double'), ('node_filesystem_free_bytes_sum', 'double'), ('node_thermal_zone_temp_min', 'double'), ('node_thermal_zone_temp_mean', 'double'), ('node_thermal_zone_temp_max', 'double'), ('node_disk_read_bytes_total_sum', 'double'), ('nvidia_gpu_memory_used_bytes_sum', 'double'), ('nvidia_gpu_temperature_celsius_0', 'double'), ('nvidia_gpu_temperature_celsius_1', 'double'), ('nvidia_gpu_temperature_celsius_2', 'double'), ('nvidia_gpu_temperature_celsius_3', 'double'), ('nvidia_gpu_temperature_celsius_min', 'double'), ('nvidia_gpu_temperature_celsius_mean', 'double'), ('nvidia_gpu_temperature_celsius_max', 'double'), ('node_arp_entries_sum', 'double'), ('nvidia_gpu_fanspeed_percent_min', 'double'), ('nvidia_gpu_fanspeed_percent_mean', 'double'), ('nvidia_gpu_fanspeed_percent_max', 'double'), ('node_filesystem_files_free_sum', 'double'), ('nvidia_gpu_power_usage_milliwatts_0', 'double'), ('nvidia_gpu_power_usage_milliwatts_1', 'double'), ('nvidia_gpu_power_usage_milliwatts_2', 'double'), ('nvidia_gpu_power_usage_milliwatts_3', 'double'), ('nvidia_gpu_power_usage_milliwatts_min', 'double'), ('nvidia_gpu_power_usage_milliwatts_sum', 'double'), ('nvidia_gpu_power_usage_milliwatts_mean', 'double'), ('nvidia_gpu_power_usage_milliwatts_max', 'double'), ('node_filesystem_size_bytes_sum', 'double'), ('node_disk_writes_completed_total_sum', 'double'), ('nvidia_gpu_duty_cycle_min', 'double'), ('nvidia_gpu_duty_cycle_mean', 'double'), ('nvidia_gpu_duty_cycle_max', 'double'), ('node_network_transmit_packets_total_sum', 'double'), ('node_udp_queues_sum', 'double'), ('node_network_receive_bytes_total_sum', 'double'), ('node_network_receive_packets_total_sum', 'double'), ('node_network_receive_multicast_total_sum', 'double'), ('node_disk_io_now_sum', 'double'), ('node_rapl_package_joules_total_sum', 'double'), ('node_network_receive_drop_total_sum', 'double'), ('timestamp_seconds', 'bigint'), ('node_network_transmit_bytes_total_sum_delta', 'double'), ('node_disk_written_bytes_total_sum_delta', 'double'), ('node_disk_read_bytes_total_sum_delta', 'double'), ('node_disk_writes_completed_total_sum_delta', 'double'), ('node_network_transmit_packets_total_sum_delta', 'double'), ('node_network_receive_bytes_total_sum_delta', 'double'), ('node_network_receive_packets_total_sum_delta', 'double'), ('node_network_receive_multicast_total_sum_delta', 'double'), ('node_rapl_package_joules_total_sum_delta', 'double'), ('node_network_receive_drop_total_sum_delta', 'double'), ('timestamp_seconds_delta', 'float'), ('node_rapl_package_power_sum', 'double'), ('gpu_node', 'int')]\n",
      "[('id', 'int'), ('submit_date', 'timestamp'), ('start_date', 'timestamp'), ('end_date', 'timestamp'), ('node', 'string'), ('nodetypes', 'string'), ('numnodes', 'int'), ('numcores', 'int'), ('state', 'string')]\n"
     ]
    }
   ],
   "source": [
    "print(df_prom.dtypes)\n",
    "print(df_slurm.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 11:======================>                              (86 + 114) / 200]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame slurm - Min Timestamp: 2021-12-26 23:06:31\n",
      "DataFrame slurm - Max Timestamp: 2022-11-01 13:59:18\n",
      "DataFrame slurm range in days: 310\n",
      "DataFrame prom - Min Timestamp: 2022-06-30 18:00:30\n",
      "DataFrame prom - Max Timestamp: 2022-11-22 11:20:30\n",
      "DataFrame prom range in days: 145\n",
      "Intersection in days: 124\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# check date ranges and intersection\n",
    "\n",
    "df_slurm_timestamps = df_slurm.select(F.min(\"start_date\").alias(\"min\"), F.max(\"end_date\").alias(\"max\")).first()\n",
    "df_prom_timestamps = df_prom.select(F.min(\"timestamp\").alias(\"min\"), F.max(\"timestamp\").alias(\"max\")).first()\n",
    "\n",
    "print(\"DataFrame slurm - Min Timestamp:\", df_slurm_timestamps[\"min\"])\n",
    "print(\"DataFrame slurm - Max Timestamp:\", df_slurm_timestamps[\"max\"])\n",
    "print(\"DataFrame slurm range in days:\", (df_slurm_timestamps[\"max\"] - df_slurm_timestamps[\"min\"]).days + 1)\n",
    "\n",
    "print(\"DataFrame prom - Min Timestamp:\", df_prom_timestamps[\"min\"])\n",
    "print(\"DataFrame prom - Max Timestamp:\", df_prom_timestamps[\"max\"])\n",
    "print(\"DataFrame prom range in days:\", (df_prom_timestamps[\"max\"] - df_prom_timestamps[\"min\"]).days + 1)\n",
    "\n",
    "max_intersect_ts = min(df_slurm_timestamps[\"max\"], df_prom_timestamps[\"max\"]) \n",
    "min_intersect_ts = max(df_slurm_timestamps[\"min\"], df_prom_timestamps[\"min\"])\n",
    "print(\"Intersection in days:\", (max_intersect_ts - min_intersect_ts).days + 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:8: SyntaxWarning: invalid escape sequence '\\['\n",
      "<>:8: SyntaxWarning: invalid escape sequence '\\['\n",
      "/tmp/ipykernel_1479792/2595143471.py:8: SyntaxWarning: invalid escape sequence '\\['\n",
      "  node_ids = F.regexp_extract(g, \"n\\[?([0-9,]+)\", 1)\n",
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>node</th>\n",
       "      <th>nodez</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1616160</td>\n",
       "      <td>r10n[13,19,25],r11n[19,20,22,23,24,28],r12n[4,...</td>\n",
       "      <td>[r10n13, r10n19, r10n25, r11n19, r11n20, r11n2...</td>\n",
       "      <td>2022-07-12 11:48:24</td>\n",
       "      <td>2022-07-12 11:48:39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>539517</td>\n",
       "      <td>r10n[7,11,13,17,19,26,30],r11n[14,23,24,30,32]...</td>\n",
       "      <td>[r10n7, r10n11, r10n13, r10n17, r10n19, r10n26...</td>\n",
       "      <td>2022-04-27 08:49:05</td>\n",
       "      <td>2022-04-27 08:49:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>539521</td>\n",
       "      <td>r10n[7,11,13,17,19,26,30],r11n[14,23,24,30,32]...</td>\n",
       "      <td>[r10n7, r10n11, r10n13, r10n17, r10n19, r10n26...</td>\n",
       "      <td>2022-04-27 08:51:55</td>\n",
       "      <td>2022-04-27 08:52:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>539518</td>\n",
       "      <td>r10n[7,11,13,17,19,26,30],r11n[14,23,24,30,32]...</td>\n",
       "      <td>[r10n7, r10n11, r10n13, r10n17, r10n19, r10n26...</td>\n",
       "      <td>2022-04-27 08:49:37</td>\n",
       "      <td>2022-04-27 08:50:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>539516</td>\n",
       "      <td>r10n[7,11,13,17,19,26,30],r11n[14,23,24,30,32]...</td>\n",
       "      <td>[r10n7, r10n11, r10n13, r10n17, r10n19, r10n26...</td>\n",
       "      <td>2022-04-27 08:48:23</td>\n",
       "      <td>2022-04-27 08:49:01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                               node  \\\n",
       "0  1616160  r10n[13,19,25],r11n[19,20,22,23,24,28],r12n[4,...   \n",
       "1   539517  r10n[7,11,13,17,19,26,30],r11n[14,23,24,30,32]...   \n",
       "2   539521  r10n[7,11,13,17,19,26,30],r11n[14,23,24,30,32]...   \n",
       "3   539518  r10n[7,11,13,17,19,26,30],r11n[14,23,24,30,32]...   \n",
       "4   539516  r10n[7,11,13,17,19,26,30],r11n[14,23,24,30,32]...   \n",
       "\n",
       "                                               nodez          start_date  \\\n",
       "0  [r10n13, r10n19, r10n25, r11n19, r11n20, r11n2... 2022-07-12 11:48:24   \n",
       "1  [r10n7, r10n11, r10n13, r10n17, r10n19, r10n26... 2022-04-27 08:49:05   \n",
       "2  [r10n7, r10n11, r10n13, r10n17, r10n19, r10n26... 2022-04-27 08:51:55   \n",
       "3  [r10n7, r10n11, r10n13, r10n17, r10n19, r10n26... 2022-04-27 08:49:37   \n",
       "4  [r10n7, r10n11, r10n13, r10n17, r10n19, r10n26... 2022-04-27 08:48:23   \n",
       "\n",
       "             end_date  \n",
       "0 2022-07-12 11:48:39  \n",
       "1 2022-04-27 08:49:33  \n",
       "2 2022-04-27 08:52:33  \n",
       "3 2022-04-27 08:50:16  \n",
       "4 2022-04-27 08:49:01  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def extract_nodes(c):\n",
    "    \n",
    "    def process_rack(g):\n",
    "        # data for a rack is in the r13n[1,2,3] or r13n1 format\n",
    "        # The r is lost during a previous split\n",
    "        # We use the identify and extract rack and node identifiers\n",
    "        rack_id = F.regexp_extract(g, \"([0-9]+)n\", 1)\n",
    "        node_ids = F.regexp_extract(g, \"n\\[?([0-9,]+)\", 1)\n",
    "        node_id_list = F.split(node_ids, \",\")\n",
    "        combined_ids = F.transform(node_id_list, lambda nid: F.concat(lit(\"r\"), rack_id, lit(\"n\"), nid))\n",
    "        return combined_ids\n",
    "        \n",
    "    splits = F.split(c, \",r\")\n",
    "    all_racks = F.transform(splits, lambda x: process_rack(x))\n",
    "    return F.flatten(all_racks)\n",
    "\n",
    "(df_slurm.orderBy(F.length(\"node\").desc())\n",
    ".withColumn(\"nodez\", extract_nodes(col(\"node\")))\n",
    ".select(\"id\",\"node\",\"nodez\", \"start_date\", \"end_date\")# , \"metadata\")\n",
    ".limit(5).toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes_extracted_df  =(df_slurm.orderBy(F.length(\"node\").desc())\n",
    "            .withColumn(\"nodez\", extract_nodes(col(\"node\")))\n",
    "            .select(\"*\", F.dayofyear(\"start_date\").alias(\"dayofyear\"),  F.dayofyear(\"end_date\").alias(\"dayofyear_end\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+---------+\n",
      "|id       |timestamp          |node |node_time_seconds|node_load15|node_power_usage|up |node_netstat_Tcp_OutSegs|node_netstat_Tcp_InErrs|node_context_switches_total|node_load5|node_load1|node_memory_Active_bytes|node_netstat_Tcp_RetransSegs|node_netstat_Udp_InErrors|node_memory_Dirty_bytes|node_ambient_temp|node_netstat_Icmp_InMsgs|node_netstat_Udp_InDatagrams|node_intr_total|node_netstat_Tcp_InSegs|node_memory_Percpu_bytes|node_boot_time_seconds|node_netstat_Udp_OutDatagrams|node_netstat_Icmp_InErrors|node_procs_blocked|node_netstat_Icmp_OutMsgs|node_memory_MemFree_bytes|node_procs_running|node_forks_total|node_hwmon_temp_celsius_min|node_hwmon_temp_celsius_mean|node_hwmon_temp_celsius_max|node_filesystem_avail_bytes_sum|node_filesystem_files_sum|node_network_transmit_bytes_total_sum|node_filesystem_device_error_sum|node_disk_written_bytes_total_sum|node_filesystem_free_bytes_sum|node_thermal_zone_temp_min|node_thermal_zone_temp_mean|node_thermal_zone_temp_max|node_disk_read_bytes_total_sum|nvidia_gpu_memory_used_bytes_sum|nvidia_gpu_temperature_celsius_0|nvidia_gpu_temperature_celsius_1|nvidia_gpu_temperature_celsius_2|nvidia_gpu_temperature_celsius_3|nvidia_gpu_temperature_celsius_min|nvidia_gpu_temperature_celsius_mean|nvidia_gpu_temperature_celsius_max|node_arp_entries_sum|nvidia_gpu_fanspeed_percent_min|nvidia_gpu_fanspeed_percent_mean|nvidia_gpu_fanspeed_percent_max|node_filesystem_files_free_sum|nvidia_gpu_power_usage_milliwatts_0|nvidia_gpu_power_usage_milliwatts_1|nvidia_gpu_power_usage_milliwatts_2|nvidia_gpu_power_usage_milliwatts_3|nvidia_gpu_power_usage_milliwatts_min|nvidia_gpu_power_usage_milliwatts_sum|nvidia_gpu_power_usage_milliwatts_mean|nvidia_gpu_power_usage_milliwatts_max|node_filesystem_size_bytes_sum|node_disk_writes_completed_total_sum|nvidia_gpu_duty_cycle_min|nvidia_gpu_duty_cycle_mean|nvidia_gpu_duty_cycle_max|node_network_transmit_packets_total_sum|node_udp_queues_sum|node_network_receive_bytes_total_sum|node_network_receive_packets_total_sum|node_network_receive_multicast_total_sum|node_disk_io_now_sum|node_rapl_package_joules_total_sum|node_network_receive_drop_total_sum|timestamp_seconds|node_network_transmit_bytes_total_sum_delta|node_disk_written_bytes_total_sum_delta|node_disk_read_bytes_total_sum_delta|node_disk_writes_completed_total_sum_delta|node_network_transmit_packets_total_sum_delta|node_network_receive_bytes_total_sum_delta|node_network_receive_packets_total_sum_delta|node_network_receive_multicast_total_sum_delta|node_rapl_package_joules_total_sum_delta|node_network_receive_drop_total_sum_delta|timestamp_seconds_delta|node_rapl_package_power_sum|gpu_node|dayofyear|\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+---------+\n",
      "|106894969|2022-10-11 04:17:00|r28n3|1.66546E9        |3.98       |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |4.2       |4.16      |2.35916E10              |271230.0                    |0.0                      |16384.0                |25.0             |423.0                   |7272060.0                   |1.41723E10     |1.43194E9              |8.20511E7               |1.66134E9             |7401900.0                    |198.0                     |0.0               |419.0                    |1.57859E11               |4.0               |3.75065E7       |26.0                       |29.19                       |36.0                       |2.564558897152E12              |6.8497933E8              |8.46171547648E12                     |0.0                             |1.705329791488E12                |2.564612583424E12             |31.0                      |33.5                       |36.0                      |3.7710436352E10               |1.5011938304E10                 |59.0                            |32.0                            |35.0                            |34.0                            |32.0                              |40.0                               |59.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435874E8                  |104137.0                           |16758.0                            |27738.0                            |21007.0                            |16758.0                              |169640.0                             |42410.0                               |104137.0                             |2.602202996736E12             |7.3244452E7                         |0.0                      |15.0                      |60.0                     |1.956308731E9                          |0.0                |2.6822646841991E13                  |3.843764456E9                         |0.0                                     |0.0                 |348559.42                         |0.0                                |1665461820       |89727.0                                    |37376.0                                |0.0                                 |8.0                                       |97.0                                         |19008.0                                   |111.0                                       |0.0                                           |2675.9099999999744                      |0.0                                      |30.0                   |89.19699999999915          |1       |284      |\n",
      "|106894970|2022-10-11 04:17:30|r28n3|1.66546E9        |3.89       |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |3.92      |3.04      |2.35326E10              |271230.0                    |0.0                      |16384.0                |25.0             |423.0                   |7272060.0                   |1.41723E10     |1.43197E9              |8.20511E7               |1.66134E9             |7401910.0                    |198.0                     |0.0               |419.0                    |1.56576E11               |22.0              |3.75068E7       |25.0                       |28.23                       |34.0                       |2.564558897152E12              |6.8497933E8              |8.461716147725E12                    |0.0                             |1.705329832448E12                |2.564612583424E12             |30.0                      |31.5                       |33.0                      |3.7710436352E10               |1.5011938304E10                 |56.0                            |32.0                            |35.0                            |35.0                            |32.0                              |39.5                               |56.0                              |229.0               |40.0                           |40.75                           |41.0                           |6.84435874E8                  |75301.0                            |16863.0                            |27634.0                            |21074.0                            |16863.0                              |140872.0                             |35218.0                               |75301.0                              |2.602202996736E12             |7.3244457E7                         |0.0                      |0.0                       |0.0                      |1.956313201E9                          |0.0                |2.682418133668E13                   |3.843935316E9                         |0.0                                     |0.0                 |350707.96                         |0.0                                |1665461850       |671245.0                                   |40960.0                                |0.0                                 |5.0                                       |4470.0                                       |1.534494689E9                             |170860.0                                    |0.0                                           |2148.5400000000373                      |0.0                                      |30.0                   |71.61800000000125          |1       |284      |\n",
      "|106894971|2022-10-11 04:18:00|r28n3|1.66546E9        |3.8        |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |3.64      |2.23      |2.358E10                |271230.0                    |0.0                      |8192.0                 |25.0             |423.0                   |7272060.0                   |1.41724E10     |1.43197E9              |8.20511E7               |1.66134E9             |7401910.0                    |198.0                     |0.0               |419.0                    |1.71775E11               |18.0              |3.75068E7       |26.0                       |28.08                       |33.0                       |2.564558897152E12              |6.8497933E8              |8.461716223075E12                    |0.0                             |1.705329928704E12                |2.564612583424E12             |30.0                      |31.5                       |33.0                      |3.7710436352E10               |1.5011938304E10                 |54.0                            |32.0                            |35.0                            |34.0                            |32.0                              |38.75                              |54.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435874E8                  |74283.0                            |16835.0                            |27373.0                            |21099.0                            |16835.0                              |139590.0                             |34897.5                               |74283.0                              |2.602202996736E12             |7.3244472E7                         |0.0                      |1.5                       |6.0                      |1.956313281E9                          |0.0                |2.6824181349974E13                  |3.843935417E9                         |0.0                                     |0.0                 |352836.64                         |0.0                                |1665461880       |75350.0                                    |96256.0                                |0.0                                 |15.0                                      |80.0                                         |13294.0                                   |101.0                                       |0.0                                           |2128.679999999993                       |0.0                                      |30.0                   |70.95599999999976          |1       |284      |\n",
      "|106894972|2022-10-11 04:18:30|r28n3|1.66546E9        |3.71       |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |3.39      |1.75      |2.35799E10              |271230.0                    |0.0                      |16384.0                |25.0             |423.0                   |7272060.0                   |1.41724E10     |1.43197E9              |8.20511E7               |1.66134E9             |7401910.0                    |198.0                     |0.0               |419.0                    |1.71782E11               |17.0              |3.75068E7       |25.0                       |27.92                       |32.0                       |2.564558897152E12              |6.8497933E8              |8.461716278853E12                    |0.0                             |1.705330016768E12                |2.564612583424E12             |30.0                      |32.0                       |34.0                      |3.7710436352E10               |1.5011938304E10                 |52.0                            |32.0                            |35.0                            |34.0                            |32.0                              |38.25                              |52.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435874E8                  |73393.0                            |16807.0                            |27234.0                            |21013.0                            |16807.0                              |138447.0                             |34611.75                              |73393.0                              |2.602202996736E12             |7.3244481E7                         |0.0                      |0.0                       |0.0                      |1.956313328E9                          |0.0                |2.682418135776E13                   |3.843935496E9                         |0.0                                     |0.0                 |354952.28                         |0.0                                |1665461910       |55778.0                                    |88064.0                                |0.0                                 |9.0                                       |47.0                                         |7786.0                                    |79.0                                        |0.0                                           |2115.640000000014                       |0.0                                      |30.0                   |70.5213333333338           |1       |284      |\n",
      "|106894973|2022-10-11 04:19:00|r28n3|1.66546E9        |3.86       |672.0           |1.0|1.94796E9               |0.0                    |1.05184E12                 |3.88      |4.49      |3.73465E10              |271230.0                    |0.0                      |8380420.0              |25.0             |423.0                   |7272070.0                   |1.41726E10     |1.43213E9              |8.20511E7               |1.66134E9             |7401920.0                    |198.0                     |0.0               |419.0                    |1.50142E11               |24.0              |3.75074E7       |26.0                       |30.69                       |36.0                       |2.564558811136E12              |6.8497933E8              |8.461719749204E12                    |0.0                             |1.705351074816E12                |2.564612497408E12             |35.0                      |35.5                       |36.0                      |3.7710436352E10               |1.5011938304E10                 |51.0                            |32.0                            |35.0                            |34.0                            |32.0                              |38.0                               |51.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435865E8                  |73411.0                            |16632.0                            |27784.0                            |20959.0                            |16632.0                              |138786.0                             |34696.5                               |73411.0                              |2.602202996736E12             |7.3245375E7                         |0.0                      |2.25                      |9.0                      |1.956334291E9                          |0.0                |2.6831838806489E13                  |3.844791768E9                         |0.0                                     |0.0                 |357469.73                         |0.0                                |1665461940       |3470351.0                                  |2.1058048E7                            |0.0                                 |894.0                                     |20963.0                                      |7.657448729E9                             |856272.0                                    |0.0                                           |2517.4499999999534                      |0.0                                      |30.0                   |83.91499999999844          |1       |284      |\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+---------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "prom_subset_df = df_prom.withColumn(\"dayofyear\", F.dayofyear(\"timestamp\"))\n",
    "prom_subset_df.show(5, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------------------+-------------------+-------------------+------+---------+--------+--------+---------+--------+---------+-------------+---------+\n",
      "|id     |submit_date        |start_date         |end_date           |node  |nodetypes|numnodes|numcores|state    |nodez   |dayofyear|dayofyear_end|diff_days|\n",
      "+-------+-------------------+-------------------+-------------------+------+---------+--------+--------+---------+--------+---------+-------------+---------+\n",
      "|800246 |2022-05-17 13:07:46|2022-05-17 13:57:44|2022-05-22 01:53:05|r15n15|normal   |1       |16      |FAILED   |[r15n15]|137      |142          |5        |\n",
      "|1444090|2022-06-14 21:06:26|2022-06-14 21:32:18|2022-06-19 01:32:22|r15n11|normal   |1       |16      |TIMEOUT  |[r15n11]|165      |170          |5        |\n",
      "|1444354|2022-06-14 11:58:42|2022-06-14 12:01:47|2022-06-19 11:01:53|r26n5 |normal   |1       |16      |TIMEOUT  |[r26n5] |165      |170          |5        |\n",
      "|1445771|2022-06-15 10:00:31|2022-06-15 10:17:22|2022-06-20 10:17:47|r25n30|normal   |1       |16      |TIMEOUT  |[r25n30]|166      |171          |5        |\n",
      "|1465727|2022-06-16 13:08:58|2022-06-16 13:13:34|2022-06-21 12:13:55|r12n2 |normal   |1       |16      |TIMEOUT  |[r12n2] |167      |172          |5        |\n",
      "|1444355|2022-06-14 11:58:42|2022-06-14 12:02:48|2022-06-19 11:02:53|r26n26|normal   |1       |16      |TIMEOUT  |[r26n26]|165      |170          |5        |\n",
      "|953299 |2022-05-23 17:11:30|2022-05-23 18:41:58|2022-05-28 04:25:33|r26n23|normal   |1       |16      |COMPLETED|[r26n23]|143      |148          |5        |\n",
      "|1444356|2022-06-14 11:58:42|2022-06-14 12:08:09|2022-06-19 11:08:23|r27n18|normal   |1       |16      |TIMEOUT  |[r27n18]|165      |170          |5        |\n",
      "|1466874|2022-06-16 13:08:58|2022-06-16 21:31:39|2022-06-21 20:31:59|r25n19|normal   |1       |16      |TIMEOUT  |[r25n19]|167      |172          |5        |\n",
      "|1444357|2022-06-14 11:58:42|2022-06-14 12:08:09|2022-06-19 11:08:23|r27n30|normal   |1       |16      |TIMEOUT  |[r27n30]|165      |170          |5        |\n",
      "+-------+-------------------+-------------------+-------------------+------+---------+--------+--------+---------+--------+---------+-------------+---------+\n",
      "only showing top 10 rows\n",
      "\n",
      "1596963\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+-------+--------+---------+-------------+---------+------------------+\n",
      "|id   |submit_date        |start_date         |end_date           |node  |nodetypes|numnodes|numcores|state  |nodez   |dayofyear|dayofyear_end|diff_days|diff_days_exploded|\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+-------+--------+---------+-------------+---------+------------------+\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |0                 |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |1                 |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |2                 |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |3                 |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |4                 |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |5                 |\n",
      "|97305|2022-01-14 23:49:02|2022-01-16 05:02:14|2022-01-21 05:02:17|r15n20|normal   |1       |16      |TIMEOUT|[r15n20]|16       |21           |5        |0                 |\n",
      "|97305|2022-01-14 23:49:02|2022-01-16 05:02:14|2022-01-21 05:02:17|r15n20|normal   |1       |16      |TIMEOUT|[r15n20]|16       |21           |5        |1                 |\n",
      "|97305|2022-01-14 23:49:02|2022-01-16 05:02:14|2022-01-21 05:02:17|r15n20|normal   |1       |16      |TIMEOUT|[r15n20]|16       |21           |5        |2                 |\n",
      "|97305|2022-01-14 23:49:02|2022-01-16 05:02:14|2022-01-21 05:02:17|r15n20|normal   |1       |16      |TIMEOUT|[r15n20]|16       |21           |5        |3                 |\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+-------+--------+---------+-------------+---------+------------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1702895\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+-------+--------+---------+-------------+---------+------------------+---------------------+\n",
      "|id   |submit_date        |start_date         |end_date           |node  |nodetypes|numnodes|numcores|state  |nodez   |dayofyear|dayofyear_end|diff_days|diff_days_exploded|running_date_exploded|\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+-------+--------+---------+-------------+---------+------------------+---------------------+\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |0                 |2022-01-16           |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |1                 |2022-01-17           |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |2                 |2022-01-18           |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |3                 |2022-01-19           |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |4                 |2022-01-20           |\n",
      "|97304|2022-01-14 23:49:01|2022-01-16 05:02:14|2022-01-21 05:02:17|r10n19|normal   |1       |16      |TIMEOUT|[r10n19]|16       |21           |5        |5                 |2022-01-21           |\n",
      "|97305|2022-01-14 23:49:02|2022-01-16 05:02:14|2022-01-21 05:02:17|r15n20|normal   |1       |16      |TIMEOUT|[r15n20]|16       |21           |5        |0                 |2022-01-16           |\n",
      "|97305|2022-01-14 23:49:02|2022-01-16 05:02:14|2022-01-21 05:02:17|r15n20|normal   |1       |16      |TIMEOUT|[r15n20]|16       |21           |5        |1                 |2022-01-17           |\n",
      "|97305|2022-01-14 23:49:02|2022-01-16 05:02:14|2022-01-21 05:02:17|r15n20|normal   |1       |16      |TIMEOUT|[r15n20]|16       |21           |5        |2                 |2022-01-18           |\n",
      "|97305|2022-01-14 23:49:02|2022-01-16 05:02:14|2022-01-21 05:02:17|r15n20|normal   |1       |16      |TIMEOUT|[r15n20]|16       |21           |5        |3                 |2022-01-19           |\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+-------+--------+---------+-------------+---------+------------------+---------------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 38:>                                                         (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1702895\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# get difference in days\n",
    "nodes_extracted_df_exploded = nodes_extracted_df.withColumn(\"diff_days\", F.dayofyear(\"end_date\") - F.dayofyear(\"start_date\"))\n",
    "nodes_extracted_df_exploded = nodes_extracted_df_exploded.orderBy(\"diff_days\", ascending=False)\n",
    "nodes_extracted_df_exploded.show(10, False)\n",
    "print(nodes_extracted_df_exploded.count())\n",
    "\n",
    "# explode diff_days\n",
    "nodes_extracted_df_exploded = nodes_extracted_df_exploded.withColumn(\"diff_days_exploded\", F.explode(F.sequence(lit(0), col(\"diff_days\"))))\n",
    "nodes_extracted_df_exploded.show(10, False)\n",
    "print(nodes_extracted_df_exploded.count())\n",
    "\n",
    "# add diff days to start_date\n",
    "nodes_extracted_df_exploded = nodes_extracted_df_exploded.withColumn(\"running_date_exploded\", F.expr(\"date_add(start_date, diff_days_exploded)\"))\n",
    "nodes_extracted_df_exploded.show(10, False)\n",
    "print(nodes_extracted_df_exploded.count())\n",
    "\n",
    "nodes_extracted_df_exploded = nodes_extracted_df_exploded.drop(\"diff_days\", \"diff_days_exploded\")\n",
    "nodes_extracted_df_exploded = nodes_extracted_df_exploded.withColumn(\"dayofyear_running\", F.dayofyear(\"running_date_exploded\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Jobs count: \t\t\t\t1596963\n",
      "Jobs smaller 30s: \t\t\t778741 (48.76%)\n",
      "Jobs within one day: \t\t\t1548216 (96.95%)\n",
      "Jobs within multiple days: \t\t48649 (3.05%)\n",
      "Sum Job hours smaller 30s: \t\t1230.76 (0.07%)\n",
      "Sum Job hours within one day: \t\t539026.07 (32.04%)\n",
      "Sum Job hours within multiple days: \t1143148.5 (67.96%)\n"
     ]
    }
   ],
   "source": [
    "jobs_count = df_slurm.count()\n",
    "jobs_count_smaller_30s = df_slurm.filter((F.col(\"end_date\").cast(LongType()) - F.col(\"start_date\").cast(LongType())) < 30).count()\n",
    "jobs_count_within_one_day = df_slurm.filter((F.dayofyear(F.col(\"start_date\")) == F.dayofyear(F.col(\"end_date\")))).count()\n",
    "jobs_count_within_multiple_days = df_slurm.filter((F.dayofyear(F.col(\"start_date\")) < F.dayofyear(F.col(\"end_date\")))).count()\n",
    "\n",
    "jobs_sum_hours_smaller_30s = df_slurm.filter((F.col(\"end_date\").cast(LongType()) - F.col(\"start_date\").cast(LongType())) < 30).select(F.sum((F.col(\"end_date\").cast(LongType()) - F.col(\"start_date\").cast(LongType())) / 3600)).first()[0]\n",
    "jobs_sum_hours_within_one_day = df_slurm.filter((F.dayofyear(F.col(\"start_date\")) == F.dayofyear(F.col(\"end_date\")))).select(F.sum((F.col(\"end_date\").cast(LongType()) - F.col(\"start_date\").cast(LongType())) / 3600)).first()[0]\n",
    "jobs_sum_hours_within_multiple_days = df_slurm.filter((F.dayofyear(F.col(\"start_date\")) < F.dayofyear(F.col(\"end_date\")))).select(F.sum((F.col(\"end_date\").cast(LongType()) - F.col(\"start_date\").cast(LongType())) / 3600)).first()[0]\n",
    "\n",
    "print(f\"Jobs count: \\t\\t\\t\\t{jobs_count}\")\n",
    "print(f\"Jobs smaller 30s: \\t\\t\\t{jobs_count_smaller_30s} ({builtins.round(jobs_count_smaller_30s / jobs_count * 100, 2)}%)\")\n",
    "print(f\"Jobs within one day: \\t\\t\\t{jobs_count_within_one_day} ({builtins.round(jobs_count_within_one_day / jobs_count * 100, 2)}%)\")\n",
    "print(f\"Jobs within multiple days: \\t\\t{jobs_count_within_multiple_days} ({builtins.round(jobs_count_within_multiple_days / jobs_count * 100, 2)}%)\")\n",
    "print(f\"Sum Job hours smaller 30s: \\t\\t{builtins.round(jobs_sum_hours_smaller_30s, 2)} ({builtins.round(jobs_sum_hours_smaller_30s / (jobs_sum_hours_smaller_30s + jobs_sum_hours_within_one_day + jobs_sum_hours_within_multiple_days) * 100, 2)}%)\")\n",
    "print(f\"Sum Job hours within one day: \\t\\t{builtins.round(jobs_sum_hours_within_one_day, 2)} ({builtins.round(jobs_sum_hours_within_one_day / (jobs_sum_hours_within_one_day + jobs_sum_hours_within_multiple_days) * 100, 2)}%)\")\n",
    "print(f\"Sum Job hours within multiple days: \\t{builtins.round(jobs_sum_hours_within_multiple_days, 2)} ({builtins.round(jobs_sum_hours_within_multiple_days / (jobs_sum_hours_within_one_day + jobs_sum_hours_within_multiple_days) * 100, 2)}%)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "prom_subset_df = prom_subset_df.withColumnRenamed(\"id\", \"prom_id\")\n",
    "prom_subset_df = prom_subset_df.withColumnRenamed(\"node\", \"prom_node\")\n",
    "prom_subset_df = prom_subset_df.withColumnRenamed(\"dayofyear\", \"prom_dayofyear\")\n",
    "\n",
    "nodes_extracted_df_exploded = nodes_extracted_df_exploded.withColumnRenamed(\"id\", \"slurm_id\")\n",
    "nodes_extracted_df_exploded = nodes_extracted_df_exploded.withColumnRenamed(\"node\", \"slurm_node\")\n",
    "nodes_extracted_df_exploded = nodes_extracted_df_exploded.withColumnRenamed(\"dayofyear\", \"slurm_dayofyear\")\n",
    "\n",
    "prom_subset_df = prom_subset_df.filter((col(\"timestamp\") >= min_intersect_ts) & (col(\"timestamp\") <= max_intersect_ts))\n",
    "nodes_extracted_df_exploded = nodes_extracted_df_exploded.filter((col(\"start_date\") >= min_intersect_ts) & (col(\"end_date\") <= max_intersect_ts))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 63:>                                                     (0 + 126) / 200]\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# checkpoint subset with all comlumns to read it in at the end again and re-join column to combined df\n",
    "prom_subset_df = checkpoint_df(prom_subset_df, spark, f\"{checkpoint_folder}/prom_subset_df_updated.parquet\")\n",
    "nodes_extracted_df_exploded = checkpoint_df(nodes_extracted_df_exploded, spark, f\"{checkpoint_folder}/nodes_extracted_df_exploded_updated.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "prom_subset_df = prom_subset_df.select(\"prom_id\", \"prom_node\", \"prom_dayofyear\", \"timestamp\")\n",
    "prom_subset_df = checkpoint_df(prom_subset_df, spark, f\"{checkpoint_folder}/prom_subset_df_updated_selected.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 74:=====================================================>(149 + 1) / 150]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "93950875\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# 15 min\n",
    "df_combined = (prom_subset_df.join(nodes_extracted_df_exploded)\n",
    ".where(prom_subset_df.prom_dayofyear == nodes_extracted_df_exploded.dayofyear_running)\n",
    ".where(F.array_contains(nodes_extracted_df_exploded.nodez, prom_subset_df.prom_node))\n",
    ".filter(prom_subset_df.timestamp >= nodes_extracted_df_exploded.start_date)\n",
    ".filter(prom_subset_df.timestamp <= nodes_extracted_df_exploded.end_date)\n",
    ")\n",
    "\n",
    "print(df_combined.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 78:======================================>                   (2 + 1) / 3]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+---------+--------------+-------------------+--------+-------------------+-------------------+-------------------+----------+---------------+--------+--------+---------+-------+---------------+-------------+---------------------+-----------------+\n",
      "|prom_id |prom_node|prom_dayofyear|timestamp          |slurm_id|submit_date        |start_date         |end_date           |slurm_node|nodetypes      |numnodes|numcores|state    |nodez  |slurm_dayofyear|dayofyear_end|running_date_exploded|dayofyear_running|\n",
      "+--------+---------+--------------+-------------------+--------+-------------------+-------------------+-------------------+----------+---------------+--------+--------+---------+-------+---------------+-------------+---------------------+-----------------+\n",
      "|67659139|r37n1    |243           |2022-08-31 12:55:00|1930815 |2022-08-31 12:54:40|2022-08-31 12:54:40|2022-08-31 12:56:19|r37n1     |shared_52c_384g|1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "|67659140|r37n1    |243           |2022-08-31 12:55:30|1930815 |2022-08-31 12:54:40|2022-08-31 12:54:40|2022-08-31 12:56:19|r37n1     |shared_52c_384g|1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "|67659141|r37n1    |243           |2022-08-31 12:56:00|1930815 |2022-08-31 12:54:40|2022-08-31 12:54:40|2022-08-31 12:56:19|r37n1     |shared_52c_384g|1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "|67659147|r37n1    |243           |2022-08-31 12:59:00|1931555 |2022-08-31 12:58:56|2022-08-31 12:58:56|2022-08-31 17:38:35|r37n1     |shared         |1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "|67659148|r37n1    |243           |2022-08-31 12:59:30|1931555 |2022-08-31 12:58:56|2022-08-31 12:58:56|2022-08-31 17:38:35|r37n1     |shared         |1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "|67659149|r37n1    |243           |2022-08-31 13:00:00|1931555 |2022-08-31 12:58:56|2022-08-31 12:58:56|2022-08-31 17:38:35|r37n1     |shared         |1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "|67659150|r37n1    |243           |2022-08-31 13:00:30|1931555 |2022-08-31 12:58:56|2022-08-31 12:58:56|2022-08-31 17:38:35|r37n1     |shared         |1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "|67659151|r37n1    |243           |2022-08-31 13:01:00|1931555 |2022-08-31 12:58:56|2022-08-31 12:58:56|2022-08-31 17:38:35|r37n1     |shared         |1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "|67659152|r37n1    |243           |2022-08-31 13:01:30|1931555 |2022-08-31 12:58:56|2022-08-31 12:58:56|2022-08-31 17:38:35|r37n1     |shared         |1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "|67659153|r37n1    |243           |2022-08-31 13:02:00|1931555 |2022-08-31 12:58:56|2022-08-31 12:58:56|2022-08-31 17:38:35|r37n1     |shared         |1       |16      |COMPLETED|[r37n1]|243            |243          |2022-08-31           |243              |\n",
      "+--------+---------+--------------+-------------------+--------+-------------------+-------------------+-------------------+----------+---------------+--------+--------+---------+-------+---------------+-------------+---------------------+-----------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_combined.show(10, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# 35 min\n",
    "df_combined = checkpoint_df(df_combined, spark, f\"{checkpoint_folder}/df_combined_updated.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "prom_subset_df = spark.read.parquet(f\"{checkpoint_folder}/prom_subset_df_updated.parquet\")\n",
    "df_combined = df_combined.join(prom_subset_df, df_combined.prom_id == prom_subset_df.prom_id, \"inner\").drop(prom_subset_df.prom_id).drop(prom_subset_df.prom_node).drop(prom_subset_df.prom_dayofyear).drop(prom_subset_df.timestamp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 93:>                                                         (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------------------+---------+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------------+--------+-------------------+-------------------+-------------------+----------+---------+--------+--------+---------+-------+---------------------+-----------------------+-------------+\n",
      "|prom_id|timestamp          |prom_node|node_time_seconds|node_load15|node_power_usage|up |node_netstat_Tcp_OutSegs|node_netstat_Tcp_InErrs|node_context_switches_total|node_load5|node_load1|node_memory_Active_bytes|node_netstat_Tcp_RetransSegs|node_netstat_Udp_InErrors|node_memory_Dirty_bytes|node_ambient_temp|node_netstat_Icmp_InMsgs|node_netstat_Udp_InDatagrams|node_intr_total|node_netstat_Tcp_InSegs|node_memory_Percpu_bytes|node_boot_time_seconds|node_netstat_Udp_OutDatagrams|node_netstat_Icmp_InErrors|node_procs_blocked|node_netstat_Icmp_OutMsgs|node_memory_MemFree_bytes|node_procs_running|node_forks_total|node_hwmon_temp_celsius_min|node_hwmon_temp_celsius_mean|node_hwmon_temp_celsius_max|node_filesystem_avail_bytes_sum|node_filesystem_files_sum|node_network_transmit_bytes_total_sum|node_filesystem_device_error_sum|node_disk_written_bytes_total_sum|node_filesystem_free_bytes_sum|node_thermal_zone_temp_min|node_thermal_zone_temp_mean|node_thermal_zone_temp_max|node_disk_read_bytes_total_sum|nvidia_gpu_memory_used_bytes_sum|nvidia_gpu_temperature_celsius_0|nvidia_gpu_temperature_celsius_1|nvidia_gpu_temperature_celsius_2|nvidia_gpu_temperature_celsius_3|nvidia_gpu_temperature_celsius_min|nvidia_gpu_temperature_celsius_mean|nvidia_gpu_temperature_celsius_max|node_arp_entries_sum|nvidia_gpu_fanspeed_percent_min|nvidia_gpu_fanspeed_percent_mean|nvidia_gpu_fanspeed_percent_max|node_filesystem_files_free_sum|nvidia_gpu_power_usage_milliwatts_0|nvidia_gpu_power_usage_milliwatts_1|nvidia_gpu_power_usage_milliwatts_2|nvidia_gpu_power_usage_milliwatts_3|nvidia_gpu_power_usage_milliwatts_min|nvidia_gpu_power_usage_milliwatts_sum|nvidia_gpu_power_usage_milliwatts_mean|nvidia_gpu_power_usage_milliwatts_max|node_filesystem_size_bytes_sum|node_disk_writes_completed_total_sum|nvidia_gpu_duty_cycle_min|nvidia_gpu_duty_cycle_mean|nvidia_gpu_duty_cycle_max|node_network_transmit_packets_total_sum|node_udp_queues_sum|node_network_receive_bytes_total_sum|node_network_receive_packets_total_sum|node_network_receive_multicast_total_sum|node_disk_io_now_sum|node_rapl_package_joules_total_sum|node_network_receive_drop_total_sum|timestamp_seconds|node_network_transmit_bytes_total_sum_delta|node_disk_written_bytes_total_sum_delta|node_disk_read_bytes_total_sum_delta|node_disk_writes_completed_total_sum_delta|node_network_transmit_packets_total_sum_delta|node_network_receive_bytes_total_sum_delta|node_network_receive_packets_total_sum_delta|node_network_receive_multicast_total_sum_delta|node_rapl_package_joules_total_sum_delta|node_network_receive_drop_total_sum_delta|timestamp_seconds_delta|node_rapl_package_power_sum|gpu_node|prom_dayofyear|slurm_id|submit_date        |start_date         |end_date           |slurm_node|nodetypes|numnodes|numcores|state    |nodez  |slurm_dayofyear_start|slurm_dayofyear_running|dayofyear_end|\n",
      "+-------+-------------------+---------+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------------+--------+-------------------+-------------------+-------------------+----------+---------+--------+--------+---------+-------+---------------------+-----------------------+-------------+\n",
      "|75     |2022-07-13 09:54:00|r10n1    |1.65771E9        |1.0        |88.0            |1.0|5271780.0               |0.0                    |2.32116E10                 |1.0       |1.0       |2.46374E9               |125.0                       |0.0                      |8192.0                 |25.0             |205.0                   |12543.0                     |8.87952E8      |5200320.0              |7798780.0               |1.65734E9             |25376.0                      |146.0                     |0.0               |205.0                    |9.58022E10               |17.0              |3889620.0       |31.0                       |33.06                       |38.0                       |1.882554228736E12              |2.30697002E8             |1.600206941E9                        |0.0                             |3.0746944512E10                  |1.882607915008E12             |38.0                      |38.0                       |38.0                      |1.285970432E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |223.0               |0.0                            |0.0                             |0.0                            |2.30090296E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |527423.0                            |0.0                      |0.0                       |0.0                      |5516219.0                              |0.0                |1.7779111706E10                     |7120755.0                             |2.0                                     |0.0                 |256000.51                         |0.0                                |1657698840       |31482.0                                    |34304.0                                |0.0                                 |4.0                                       |15.0                                         |1785.0                                    |20.0                                        |0.0                                           |1422.4500000000116                      |0.0                                      |30.0                   |47.41500000000039          |0       |194           |1627898 |2022-07-12 21:41:31|2022-07-12 21:41:32|2022-07-13 17:23:52|r10n1     |normal   |1       |16      |CANCELLED|[r10n1]|193                  |194                    |194          |\n",
      "|189    |2022-07-13 10:51:00|r10n1    |1.65771E9        |1.01       |88.0            |1.0|5302890.0               |0.0                    |2.32127E10                 |1.07      |1.1       |2.49732E9               |125.0                       |0.0                      |12288.0                |25.0             |205.0                   |12578.0                     |8.89613E8      |5230610.0              |7798780.0               |1.65734E9             |25519.0                      |146.0                     |0.0               |205.0                    |9.57612E10               |27.0              |3898050.0       |31.0                       |32.88                       |38.0                       |1.882545840128E12              |2.30697002E8             |1.608195905E9                        |0.0                             |3.1029157376E10                  |1.8825995264E12               |40.0                      |40.0                       |40.0                      |1.285970432E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |223.0               |0.0                            |0.0                             |0.0                            |2.30090299E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |532020.0                            |0.0                      |0.0                       |0.0                      |5549427.0                              |0.0                |1.7786147367E10                     |7154239.0                             |2.0                                     |0.0                 |156753.12                         |0.0                                |1657702260       |33928.0                                    |33792.0                                |0.0                                 |4.0                                       |30.0                                         |36994.0                                   |32.0                                        |0.0                                           |1424.0                                  |0.0                                      |30.0                   |47.46666666666667          |0       |194           |1627898 |2022-07-12 21:41:31|2022-07-12 21:41:32|2022-07-13 17:23:52|r10n1     |normal   |1       |16      |CANCELLED|[r10n1]|193                  |194                    |194          |\n",
      "|215    |2022-07-13 11:04:00|r10n1    |1.65771E9        |1.01       |88.0            |1.0|5310520.0               |0.0                    |2.32129E10                 |1.04      |1.02      |2.49732E9               |125.0                       |0.0                      |20480.0                |25.0             |205.0                   |12584.0                     |8.89995E8      |5238060.0              |7798780.0               |1.65734E9             |25547.0                      |146.0                     |0.0               |205.0                    |9.5762E10                |11.0              |3899900.0       |31.0                       |32.76                       |38.0                       |1.882543767552E12              |2.30697002E8             |1.610069242E9                        |0.0                             |3.1066502144E10                  |1.882597453824E12             |38.0                      |38.0                       |38.0                      |1.285970432E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |223.0               |0.0                            |0.0                             |0.0                            |2.30090301E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |533091.0                            |0.0                      |0.0                       |0.0                      |5557581.0                              |0.0                |1.7787759107E10                     |7162394.0                             |2.0                                     |0.0                 |193897.5                          |0.0                                |1657703040       |32254.0                                    |13824.0                                |0.0                                 |3.0                                       |22.0                                         |1959.0                                    |20.0                                        |0.0                                           |1425.6300000000047                      |0.0                                      |30.0                   |47.52100000000016          |0       |194           |1627898 |2022-07-12 21:41:31|2022-07-12 21:41:32|2022-07-13 17:23:52|r10n1     |normal   |1       |16      |CANCELLED|[r10n1]|193                  |194                    |194          |\n",
      "|239    |2022-07-13 11:16:00|r10n1    |1.65771E9        |1.02       |88.0            |1.0|5317450.0               |0.0                    |2.32132E10                 |1.07      |1.23      |2.4838E9                |125.0                       |0.0                      |7622660.0              |25.0             |205.0                   |12592.0                     |8.90342E8      |5244810.0              |7798780.0               |1.65734E9             |25579.0                      |146.0                     |0.0               |205.0                    |9.57766E10               |19.0              |3901460.0       |31.0                       |33.53                       |38.0                       |1.882545762304E12              |2.30697002E8             |1.611793505E9                        |0.0                             |3.1081740288E10                  |1.882599448576E12             |39.0                      |39.0                       |39.0                      |1.285970432E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |223.0               |0.0                            |0.0                             |0.0                            |2.30090301E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |533254.0                            |0.0                      |0.0                       |0.0                      |5564929.0                              |0.0                |1.7789191308E10                     |7169762.0                             |2.0                                     |0.0                 |228267.48                         |0.0                                |1657703760       |856609.0                                   |1.4364672E7                            |0.0                                 |48.0                                      |5483.0                                       |954861.0                                  |5434.0                                      |0.0                                           |1490.75                                 |0.0                                      |30.0                   |49.69166666666667          |0       |194           |1627898 |2022-07-12 21:41:31|2022-07-12 21:41:32|2022-07-13 17:23:52|r10n1     |normal   |1       |16      |CANCELLED|[r10n1]|193                  |194                    |194          |\n",
      "|325    |2022-07-13 11:59:00|r10n1    |1.65771E9        |1.0        |88.0            |1.0|5335130.0               |0.0                    |2.32139E10                 |1.0       |1.0       |2.49833E9               |125.0                       |0.0                      |8192.0                 |25.0             |205.0                   |12608.0                     |8.91567E8      |5261920.0              |7798780.0               |1.65734E9             |25679.0                      |146.0                     |0.0               |205.0                    |9.57609E10               |14.0              |3907080.0       |31.0                       |32.88                       |39.0                       |1.882544795648E12              |2.30697002E8             |1.61686704E9                         |0.0                             |3.1180724224E10                  |1.88259848192E12              |39.0                      |39.0                       |39.0                      |1.285970432E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |223.0               |0.0                            |0.0                             |0.0                            |2.30090304E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |536499.0                            |0.0                      |0.0                       |0.0                      |5584233.0                              |0.0                |1.7793349774E10                     |7189149.0                             |2.0                                     |0.0                 |88860.7                           |0.0                                |1657706340       |32526.0                                    |59392.0                                |0.0                                 |8.0                                       |23.0                                         |2477.0                                    |24.0                                        |0.0                                           |1426.4700000000012                      |0.0                                      |30.0                   |47.54900000000004          |0       |194           |1627898 |2022-07-12 21:41:31|2022-07-12 21:41:32|2022-07-13 17:23:52|r10n1     |normal   |1       |16      |CANCELLED|[r10n1]|193                  |194                    |194          |\n",
      "|334    |2022-07-13 12:03:30|r10n1    |1.65771E9        |1.02       |88.0            |1.0|5341460.0               |0.0                    |2.3214E10                  |1.06      |1.06      |2.46477E9               |125.0                       |0.0                      |12288.0                |25.0             |205.0                   |12614.0                     |8.91721E8      |5268130.0              |7798780.0               |1.65734E9             |25691.0                      |146.0                     |0.0               |205.0                    |9.57924E10               |11.0              |3908120.0       |31.0                       |32.76                       |38.0                       |1.882544799744E12              |2.30697002E8             |1.618075576E9                        |0.0                             |3.1217639424E10                  |1.882598486016E12             |39.0                      |39.0                       |39.0                      |1.285970432E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |223.0               |0.0                            |0.0                             |0.0                            |2.30090305E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |537506.0                            |0.0                      |0.0                       |0.0                      |5590716.0                              |0.0                |1.7794629694E10                     |7195588.0                             |2.0                                     |0.0                 |101793.68                         |0.0                                |1657706610       |32001.0                                    |26112.0                                |0.0                                 |4.0                                       |20.0                                         |2169.0                                    |25.0                                        |0.0                                           |1427.4199999999983                      |0.0                                      |30.0                   |47.58066666666661          |0       |194           |1627898 |2022-07-12 21:41:31|2022-07-12 21:41:32|2022-07-13 17:23:52|r10n1     |normal   |1       |16      |CANCELLED|[r10n1]|193                  |194                    |194          |\n",
      "|642    |2022-07-13 14:37:30|r10n1    |1.65772E9        |1.0        |88.0            |1.0|5419710.0               |0.0                    |2.32168E10                 |1.02      |1.0       |2.49841E9               |125.0                       |0.0                      |8192.0                 |25.0             |205.0                   |12688.0                     |8.96171E8      |5344370.0              |7798780.0               |1.65734E9             |26064.0                      |146.0                     |0.0               |205.0                    |9.57488E10               |17.0              |3929020.0       |31.0                       |32.71                       |38.0                       |1.882533392384E12              |2.30697002E8             |1.638497255E9                        |0.0                             |3.1595456512E10                  |1.882587078656E12             |38.0                      |38.0                       |38.0                      |1.285970432E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |224.0               |0.0                            |0.0                             |0.0                            |2.30090315E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |548575.0                            |0.0                      |0.0                       |0.0                      |5674771.0                              |0.0                |1.7812102818E10                     |7280197.0                             |2.0                                     |0.0                 |17502.31                          |0.0                                |1657715850       |31978.0                                    |60928.0                                |0.0                                 |7.0                                       |18.0                                         |2185.0                                    |22.0                                        |0.0                                           |1424.5600000000013                      |0.0                                      |30.0                   |47.48533333333338          |0       |194           |1627898 |2022-07-12 21:41:31|2022-07-12 21:41:32|2022-07-13 17:23:52|r10n1     |normal   |1       |16      |CANCELLED|[r10n1]|193                  |194                    |194          |\n",
      "|720    |2022-07-13 15:16:30|r10n1    |1.65773E9        |1.02       |92.0            |1.0|5442400.0               |0.0                    |2.32175E10                 |1.06      |1.26      |2.49841E9               |125.0                       |0.0                      |7688190.0              |25.0             |205.0                   |12713.0                     |8.97315E8      |5366510.0              |7798780.0               |1.65734E9             |26161.0                      |146.0                     |0.0               |205.0                    |9.57502E10               |20.0              |3934590.0       |31.0                       |33.24                       |38.0                       |1.882531086336E12              |2.30697002E8             |1.644088589E9                        |0.0                             |3.1700376576E10                  |1.882584772608E12             |39.0                      |39.0                       |39.0                      |1.285970432E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |225.0               |0.0                            |0.0                             |0.0                            |2.30090315E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |550944.0                            |0.0                      |0.0                       |0.0                      |5698963.0                              |0.0                |1.7817032542E10                     |7304658.0                             |2.0                                     |0.0                 |128715.03                         |0.0                                |1657718190       |946737.0                                   |2.8551168E7                            |0.0                                 |65.0                                      |6317.0                                       |1224332.0                                 |6259.0                                      |0.0                                           |1517.3099999999977                      |0.0                                      |30.0                   |50.57699999999992          |0       |194           |1627898 |2022-07-12 21:41:31|2022-07-12 21:41:32|2022-07-13 17:23:52|r10n1     |normal   |1       |16      |CANCELLED|[r10n1]|193                  |194                    |194          |\n",
      "|796    |2022-07-13 15:54:30|r10n1    |1.65773E9        |1.08       |88.0            |1.0|5458880.0               |0.0                    |2.32182E10                 |1.14      |1.07      |2.49842E9               |125.0                       |0.0                      |8192.0                 |25.0             |205.0                   |12730.0                     |8.98382E8      |5382520.0              |7798780.0               |1.65734E9             |26244.0                      |146.0                     |0.0               |205.0                    |9.57509E10               |18.0              |3939480.0       |31.0                       |32.76                       |38.0                       |1.882531160064E12              |2.30697002E8             |1.648707087E9                        |0.0                             |3.1784612864E10                  |1.882584846336E12             |39.0                      |39.0                       |39.0                      |1.285970432E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |225.0               |0.0                            |0.0                             |0.0                            |2.30090315E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |554115.0                            |0.0                      |0.0                       |0.0                      |5716804.0                              |0.0                |1.7820761364E10                     |7322704.0                             |2.0                                     |0.0                 |237013.46                         |0.0                                |1657720470       |31843.0                                    |46592.0                                |0.0                                 |4.0                                       |19.0                                         |1669.0                                    |18.0                                        |0.0                                           |1424.5799999999872                      |0.0                                      |30.0                   |47.48599999999957          |0       |194           |1627898 |2022-07-12 21:41:31|2022-07-12 21:41:32|2022-07-13 17:23:52|r10n1     |normal   |1       |16      |CANCELLED|[r10n1]|193                  |194                    |194          |\n",
      "|1171   |2022-07-13 19:02:00|r10n1    |1.65774E9        |1.0        |88.0            |1.0|5717970.0               |0.0                    |2.32232E10                 |0.99      |1.22      |3.74301E9               |150.0                       |0.0                      |118784.0               |25.0             |205.0                   |13876.0                     |9.03582E8      |5661680.0              |7995390.0               |1.65734E9             |27762.0                      |146.0                     |0.0               |205.0                    |8.96948E10               |16.0              |3966660.0       |31.0                       |32.59                       |38.0                       |1.880041385984E12              |2.30697002E8             |1.824895739E9                        |0.0                             |3.4901662208E10                  |1.880095072256E12             |39.0                      |39.0                       |39.0                      |1.286425088E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |238.0               |0.0                            |0.0                             |0.0                            |2.30090244E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912248332288E12             |571793.0                            |0.0                      |0.0                       |0.0                      |5983608.0                              |0.0                |2.1884344185E10                     |7967705.0                             |2.0                                     |0.0                 |173766.3                          |0.0                                |1657731720       |210796.0                                   |2.2018048E7                            |0.0                                 |900.0                                     |1377.0                                       |1635659.0                                 |1464.0                                      |0.0                                           |1474.4099999999744                      |0.0                                      |30.0                   |49.146999999999146         |0       |194           |1630536 |2022-07-13 18:57:47|2022-07-13 18:57:48|2022-07-14 04:46:19|r10n1     |normal   |1       |16      |COMPLETED|[r10n1]|194                  |194                    |195          |\n",
      "+-------+-------------------+---------+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------------+--------+-------------------+-------------------+-------------------+----------+---------+--------+--------+---------+-------+---------------------+-----------------------+-------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# order columns again, so that prom_subset_df columns are first, then the remaining df_combined columns\n",
    "df_combined = df_combined.select(prom_subset_df.columns + [col for col in df_combined.columns if col not in prom_subset_df.columns])\n",
    "df_combined = df_combined.withColumnRenamed(\"slurm_dayofyear\", \"slurm_dayofyear_start\")\n",
    "df_combined = df_combined.withColumnRenamed(\"dayofyear_running\", \"slurm_dayofyear_running\")\n",
    "df_combined = df_combined.drop(\"running_date_exploded\")\n",
    "# swap last 2 columns\n",
    "df_combined = df_combined.select(df_combined.columns[:-2] + df_combined.columns[-1:] + df_combined.columns[-2:-1])\n",
    "df_combined.show(10, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Size: 93950875 x 105\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 107:====================================================>(197 + 3) / 200]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------------------+---------+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------------+--------+-------------------+-------------------+-------------------+----------+---------+--------+--------+-------+-------+---------------------+-----------------------+-------------+\n",
      "|prom_id|timestamp          |prom_node|node_time_seconds|node_load15|node_power_usage|up |node_netstat_Tcp_OutSegs|node_netstat_Tcp_InErrs|node_context_switches_total|node_load5|node_load1|node_memory_Active_bytes|node_netstat_Tcp_RetransSegs|node_netstat_Udp_InErrors|node_memory_Dirty_bytes|node_ambient_temp|node_netstat_Icmp_InMsgs|node_netstat_Udp_InDatagrams|node_intr_total|node_netstat_Tcp_InSegs|node_memory_Percpu_bytes|node_boot_time_seconds|node_netstat_Udp_OutDatagrams|node_netstat_Icmp_InErrors|node_procs_blocked|node_netstat_Icmp_OutMsgs|node_memory_MemFree_bytes|node_procs_running|node_forks_total|node_hwmon_temp_celsius_min|node_hwmon_temp_celsius_mean|node_hwmon_temp_celsius_max|node_filesystem_avail_bytes_sum|node_filesystem_files_sum|node_network_transmit_bytes_total_sum|node_filesystem_device_error_sum|node_disk_written_bytes_total_sum|node_filesystem_free_bytes_sum|node_thermal_zone_temp_min|node_thermal_zone_temp_mean|node_thermal_zone_temp_max|node_disk_read_bytes_total_sum|nvidia_gpu_memory_used_bytes_sum|nvidia_gpu_temperature_celsius_0|nvidia_gpu_temperature_celsius_1|nvidia_gpu_temperature_celsius_2|nvidia_gpu_temperature_celsius_3|nvidia_gpu_temperature_celsius_min|nvidia_gpu_temperature_celsius_mean|nvidia_gpu_temperature_celsius_max|node_arp_entries_sum|nvidia_gpu_fanspeed_percent_min|nvidia_gpu_fanspeed_percent_mean|nvidia_gpu_fanspeed_percent_max|node_filesystem_files_free_sum|nvidia_gpu_power_usage_milliwatts_0|nvidia_gpu_power_usage_milliwatts_1|nvidia_gpu_power_usage_milliwatts_2|nvidia_gpu_power_usage_milliwatts_3|nvidia_gpu_power_usage_milliwatts_min|nvidia_gpu_power_usage_milliwatts_sum|nvidia_gpu_power_usage_milliwatts_mean|nvidia_gpu_power_usage_milliwatts_max|node_filesystem_size_bytes_sum|node_disk_writes_completed_total_sum|nvidia_gpu_duty_cycle_min|nvidia_gpu_duty_cycle_mean|nvidia_gpu_duty_cycle_max|node_network_transmit_packets_total_sum|node_udp_queues_sum|node_network_receive_bytes_total_sum|node_network_receive_packets_total_sum|node_network_receive_multicast_total_sum|node_disk_io_now_sum|node_rapl_package_joules_total_sum|node_network_receive_drop_total_sum|timestamp_seconds|node_network_transmit_bytes_total_sum_delta|node_disk_written_bytes_total_sum_delta|node_disk_read_bytes_total_sum_delta|node_disk_writes_completed_total_sum_delta|node_network_transmit_packets_total_sum_delta|node_network_receive_bytes_total_sum_delta|node_network_receive_packets_total_sum_delta|node_network_receive_multicast_total_sum_delta|node_rapl_package_joules_total_sum_delta|node_network_receive_drop_total_sum_delta|timestamp_seconds_delta|node_rapl_package_power_sum|gpu_node|prom_dayofyear|slurm_id|submit_date        |start_date         |end_date           |slurm_node|nodetypes|numnodes|numcores|state  |nodez  |slurm_dayofyear_start|slurm_dayofyear_running|dayofyear_end|\n",
      "+-------+-------------------+---------+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------------+--------+-------------------+-------------------+-------------------+----------+---------+--------+--------+-------+-------+---------------------+-----------------------+-------------+\n",
      "|7247237|2022-07-04 09:36:00|r10n1    |1.65693E9        |0.01       |36.0            |1.0|4.44149E10              |3.0                    |5.00418E11                 |0.03      |0.0       |1.48482E9               |7.77034E7                   |0.0                      |28672.0                |25.0             |3881.0                  |389774.0                    |5.57261E10     |4.00803E10             |3.73555E7               |1.65245E9             |575147.0                     |3084.0                    |0.0               |3937.0                   |9.66585E10               |16.0              |2.00848E8       |24.0                       |25.94                       |27.0                       |1.882518376448E12              |2.30697018E8             |1.49033265206096E14                  |0.0                             |6.820708131328E12                |1.88257206272E12              |27.0                      |27.0                       |27.0                      |1.27323355648E12              |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112403E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7581968E7                         |0.0                      |0.0                       |0.0                      |4.4495787469E10                        |0.0                |1.53721498700109E14                 |4.3807245067E10                       |5.0                                     |0.0                 |139492.16                         |0.0                                |1656920160       |120275.0                                   |64512.0                                |0.0                                 |9.0                                       |908.0                                        |489438.0                                  |919.0                                       |0.0                                           |355.5199999999895                       |0.0                                      |30.0                   |11.850666666666317         |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "|7247238|2022-07-04 09:36:30|r10n1    |1.65693E9        |0.04       |92.0            |1.0|4.4415E10               |3.0                    |5.00418E11                 |0.13      |0.4       |1.90806E9               |7.77034E7                   |0.0                      |20480.0                |24.0             |3884.0                  |389789.0                    |5.57262E10     |4.00803E10             |3.73555E7               |1.65245E9             |575168.0                     |3084.0                    |0.0               |3940.0                   |9.60294E10               |7.0               |2.00848E8       |29.0                       |30.35                       |35.0                       |1.88251797504E12               |2.30697018E8             |1.490332688947E14                    |0.0                             |6.820708657664E12                |1.882571661312E12             |36.0                      |36.0                       |36.0                      |1.273237013504E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112386E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7581984E7                         |0.0                      |0.0                       |0.0                      |4.44958015E10                          |0.0                |1.5372171330995E14                  |4.3807279219E10                       |5.0                                     |0.0                 |140898.18                         |0.0                                |1656920190       |3688604.0                                  |526336.0                               |3457024.0                           |16.0                                      |14031.0                                      |2.14609841E8                              |34152.0                                     |0.0                                           |1406.0199999999895                      |0.0                                      |30.0                   |46.86733333333299          |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "|7247239|2022-07-04 09:37:00|r10n1    |1.65693E9        |0.07       |92.0            |1.0|4.4415E10               |3.0                    |5.00418E11                 |0.21      |0.64      |2.01654E9               |7.77034E7                   |0.0                      |319488.0               |24.0             |3884.0                  |389811.0                    |5.57262E10     |4.00803E10             |3.73555E7               |1.65245E9             |575190.0                     |3084.0                    |0.0               |3940.0                   |9.59138E10               |18.0              |2.00848E8       |28.0                       |29.35                       |31.0                       |1.882517716992E12              |2.30697018E8             |1.49033274931836E14                  |0.0                             |6.820711450624E12                |1.882571403264E12             |31.0                      |31.0                       |31.0                      |1.273237013504E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112386E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582127E7                         |0.0                      |0.0                       |0.0                      |4.4495820191E10                        |0.0                |1.53721737819803E14                 |4.380729964E10                        |5.0                                     |0.0                 |142128.92                         |0.0                                |1656920220       |6037136.0                                  |2792960.0                              |0.0                                 |143.0                                     |18691.0                                      |2.4509853E7                               |20421.0                                     |0.0                                           |1230.7400000000198                      |0.0                                      |30.0                   |41.02466666666733          |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "|7247240|2022-07-04 09:37:30|r10n1    |1.65693E9        |0.1        |92.0            |1.0|4.4415E10               |3.0                    |5.00418E11                 |0.29      |0.78      |7.69547E9               |7.77034E7                   |0.0                      |385024.0               |24.0             |3884.0                  |389815.0                    |5.57263E10     |4.00804E10             |3.73555E7               |1.65245E9             |575194.0                     |3084.0                    |0.0               |3940.0                   |8.94145E10               |18.0              |2.00848E8       |30.0                       |32.65                       |43.0                       |1.882517069824E12              |2.30697018E8             |1.49033547689061E14                  |0.0                             |6.820711699968E12                |1.882570756096E12             |47.0                      |47.0                       |47.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112364E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.758214E7                          |0.0                      |0.0                       |0.0                      |4.4495849466E10                        |0.0                |1.53722812316861E14                 |4.3807409073E10                       |5.0                                     |0.0                 |143521.57                         |0.0                                |1656920250       |2.72757225E8                               |249344.0                               |503808.0                            |13.0                                      |29275.0                                      |1.074497058E9                             |109433.0                                    |0.0                                           |1392.6499999999942                      |0.0                                      |30.0                   |46.421666666666475         |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "|7247241|2022-07-04 09:38:00|r10n1    |1.65693E9        |0.32       |92.0            |1.0|4.44151E10              |3.0                    |5.00418E11                 |0.93      |3.52      |1.74739E10              |7.77034E7                   |0.0                      |20480.0                |24.0             |3884.0                  |389816.0                    |5.57264E10     |4.00805E10             |3.73555E7               |1.65245E9             |575195.0                     |3084.0                    |0.0               |3940.0                   |7.96437E10               |44.0              |2.00849E8       |35.0                       |36.94                       |38.0                       |1.882517356544E12              |2.30697018E8             |1.49039008555263E14                  |0.0                             |6.820712170496E12                |1.882571042816E12             |38.0                      |38.0                       |38.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112364E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582154E7                         |0.0                      |0.0                       |0.0                      |4.4495959415E10                        |0.0                |1.53728275518616E14                 |4.3807519084E10                       |5.0                                     |0.0                 |145379.41                         |0.0                                |1656920280       |5.460866202E9                              |470528.0                               |0.0                                 |14.0                                      |109949.0                                     |5.463201755E9                             |110011.0                                    |0.0                                           |1857.8399999999965                      |0.0                                      |30.0                   |61.927999999999884         |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "|7247242|2022-07-04 09:38:30|r10n1    |1.65693E9        |1.21       |92.0            |1.0|4.44151E10              |3.0                    |5.00421E11                 |3.54      |13.05     |1.75257E10              |7.77034E7                   |0.0                      |8192.0                 |24.0             |3884.0                  |389816.0                    |5.57265E10     |4.00805E10             |3.73555E7               |1.65245E9             |575195.0                     |3084.0                    |0.0               |3940.0                   |8.03395E10               |37.0              |2.00852E8       |38.0                       |38.82                       |40.0                       |1.88251736064E12               |2.30697018E8             |1.49039008925704E14                  |0.0                             |6.820712352256E12                |1.882571046912E12             |40.0                      |40.0                       |40.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582172E7                         |0.0                      |0.0                       |0.0                      |4.4495960397E10                        |0.0                |1.53728275768021E14                 |4.3807519828E10                       |5.0                                     |0.0                 |147683.42                         |0.0                                |1656920310       |370441.0                                   |181760.0                               |0.0                                 |18.0                                      |982.0                                        |249405.0                                  |744.0                                       |0.0                                           |2304.0100000000093                      |0.0                                      |30.0                   |76.80033333333364          |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "|7247243|2022-07-04 09:39:00|r10n1    |1.65693E9        |2.16       |92.0            |1.0|4.44151E10              |3.0                    |5.00423E11                 |6.18      |20.11     |1.83562E10              |7.77034E7                   |0.0                      |12288.0                |24.0             |3884.0                  |389817.0                    |5.57266E10     |4.00805E10             |3.73555E7               |1.65245E9             |575196.0                     |3084.0                    |0.0               |3940.0                   |7.9513E10                |24.0              |2.00853E8       |37.0                       |38.06                       |39.0                       |1.88251736064E12               |2.30697018E8             |1.49039009056459E14                  |0.0                             |6.820712361984E12                |1.882571046912E12             |39.0                      |39.0                       |39.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582174E7                         |0.0                      |0.0                       |0.0                      |4.4495961003E10                        |0.0                |1.53728275847529E14                 |4.3807520314E10                       |5.0                                     |0.0                 |150013.72                         |0.0                                |1656920340       |130755.0                                   |9728.0                                 |0.0                                 |2.0                                       |606.0                                        |79508.0                                   |486.0                                       |0.0                                           |2330.2999999999884                      |0.0                                      |30.0                   |77.67666666666628          |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "|7247244|2022-07-04 09:39:30|r10n1    |1.65693E9        |2.66       |92.0            |1.0|4.44151E10              |3.0                    |5.00423E11                 |7.27      |19.03     |2.16091E10              |7.77034E7                   |0.0                      |24576.0                |24.0             |3885.0                  |389818.0                    |5.57269E10     |4.00805E10             |3.73555E7               |1.65245E9             |575197.0                     |3085.0                    |0.0               |3941.0                   |7.62446E10               |20.0              |2.0088E8        |34.0                       |35.41                       |37.0                       |1.88251736064E12               |2.30697018E8             |1.49039009239008E14                  |0.0                             |6.820712547328E12                |1.882571046912E12             |37.0                      |37.0                       |37.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582195E7                         |0.0                      |0.0                       |0.0                      |4.4495961544E10                        |0.0                |1.53728275967042E14                 |4.3807520792E10                       |5.0                                     |0.0                 |152217.74                         |0.0                                |1656920370       |182549.0                                   |185344.0                               |0.0                                 |21.0                                      |541.0                                        |119513.0                                  |478.0                                       |0.0                                           |2204.0199999999895                      |0.0                                      |30.0                   |73.46733333333299          |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "|7247245|2022-07-04 09:40:00|r10n1    |1.65693E9        |3.22       |92.0            |1.0|4.44151E10              |3.0                    |5.00424E11                 |8.54      |19.88     |1.95872E10              |7.77034E7                   |0.0                      |8192.0                 |24.0             |3885.0                  |389818.0                    |5.57271E10     |4.00805E10             |3.73555E7               |1.65245E9             |575197.0                     |3085.0                    |0.0               |3941.0                   |7.82755E10               |32.0              |2.00897E8       |35.0                       |35.82                       |37.0                       |1.88251736064E12               |2.30697018E8             |1.4903900927161E14                   |0.0                             |6.82071259648E12                 |1.882571046912E12             |37.0                      |37.0                       |37.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582204E7                         |0.0                      |0.0                       |0.0                      |4.4495961564E10                        |0.0                |1.53728275986423E14                 |4.3807520833E10                       |5.0                                     |0.0                 |154462.48                         |0.0                                |1656920400       |32602.0                                    |49152.0                                |0.0                                 |9.0                                       |20.0                                         |19381.0                                   |41.0                                        |0.0                                           |2244.74000000002                        |0.0                                      |30.0                   |74.82466666666733          |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "|7247246|2022-07-04 09:40:30|r10n1    |1.65693E9        |3.99       |128.0           |1.0|4.44151E10              |3.0                    |5.00427E11                 |10.33     |22.67     |1.96501E10              |7.77034E7                   |0.0                      |24576.0                |24.0             |3885.0                  |389818.0                    |5.57272E10     |4.00805E10             |3.73555E7               |1.65245E9             |575203.0                     |3085.0                    |0.0               |3941.0                   |7.82083E10               |33.0              |2.00902E8       |33.0                       |34.65                       |36.0                       |1.882517336064E12              |2.30697018E8             |1.49039009308653E14                  |0.0                             |6.820712673792E12                |1.882571022336E12             |37.0                      |37.0                       |37.0                      |1.273237648384E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582213E7                         |0.0                      |0.0                       |0.0                      |4.4495961609E10                        |0.0                |1.53728276051126E14                 |4.3807520901E10                       |5.0                                     |0.0                 |156770.8                          |0.0                                |1656920430       |37043.0                                    |77312.0                                |131072.0                            |9.0                                       |45.0                                         |64703.0                                   |68.0                                        |0.0                                           |2308.319999999978                       |0.0                                      |30.0                   |76.94399999999926          |0       |185           |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|r10n1     |normal   |1       |16      |TIMEOUT|[r10n1]|185                  |185                    |190          |\n",
      "+-------+-------------------+---------+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------------+--------+-------------------+-------------------+-------------------+----------+---------+--------+--------+-------+-------+---------------------+-----------------------+-------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_combined = df_combined.orderBy(['prom_node', 'timestamp', 'start_date'])\n",
    "print(\"Size:\", df_combined.count(), \"x\", len(df_combined.columns))\n",
    "df_combined.show(10, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 112:================================>                   (126 + 74) / 200]\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 112:====================================================>(199 + 1) / 200]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------+-------------------+-------------------+-------------------+---------+--------+--------+-------+-----------+\n",
      "|prom_id|timestamp          |node |node_time_seconds|node_load15|node_power_usage|up |node_netstat_Tcp_OutSegs|node_netstat_Tcp_InErrs|node_context_switches_total|node_load5|node_load1|node_memory_Active_bytes|node_netstat_Tcp_RetransSegs|node_netstat_Udp_InErrors|node_memory_Dirty_bytes|node_ambient_temp|node_netstat_Icmp_InMsgs|node_netstat_Udp_InDatagrams|node_intr_total|node_netstat_Tcp_InSegs|node_memory_Percpu_bytes|node_boot_time_seconds|node_netstat_Udp_OutDatagrams|node_netstat_Icmp_InErrors|node_procs_blocked|node_netstat_Icmp_OutMsgs|node_memory_MemFree_bytes|node_procs_running|node_forks_total|node_hwmon_temp_celsius_min|node_hwmon_temp_celsius_mean|node_hwmon_temp_celsius_max|node_filesystem_avail_bytes_sum|node_filesystem_files_sum|node_network_transmit_bytes_total_sum|node_filesystem_device_error_sum|node_disk_written_bytes_total_sum|node_filesystem_free_bytes_sum|node_thermal_zone_temp_min|node_thermal_zone_temp_mean|node_thermal_zone_temp_max|node_disk_read_bytes_total_sum|nvidia_gpu_memory_used_bytes_sum|nvidia_gpu_temperature_celsius_0|nvidia_gpu_temperature_celsius_1|nvidia_gpu_temperature_celsius_2|nvidia_gpu_temperature_celsius_3|nvidia_gpu_temperature_celsius_min|nvidia_gpu_temperature_celsius_mean|nvidia_gpu_temperature_celsius_max|node_arp_entries_sum|nvidia_gpu_fanspeed_percent_min|nvidia_gpu_fanspeed_percent_mean|nvidia_gpu_fanspeed_percent_max|node_filesystem_files_free_sum|nvidia_gpu_power_usage_milliwatts_0|nvidia_gpu_power_usage_milliwatts_1|nvidia_gpu_power_usage_milliwatts_2|nvidia_gpu_power_usage_milliwatts_3|nvidia_gpu_power_usage_milliwatts_min|nvidia_gpu_power_usage_milliwatts_sum|nvidia_gpu_power_usage_milliwatts_mean|nvidia_gpu_power_usage_milliwatts_max|node_filesystem_size_bytes_sum|node_disk_writes_completed_total_sum|nvidia_gpu_duty_cycle_min|nvidia_gpu_duty_cycle_mean|nvidia_gpu_duty_cycle_max|node_network_transmit_packets_total_sum|node_udp_queues_sum|node_network_receive_bytes_total_sum|node_network_receive_packets_total_sum|node_network_receive_multicast_total_sum|node_disk_io_now_sum|node_rapl_package_joules_total_sum|node_network_receive_drop_total_sum|timestamp_seconds|node_network_transmit_bytes_total_sum_delta|node_disk_written_bytes_total_sum_delta|node_disk_read_bytes_total_sum_delta|node_disk_writes_completed_total_sum_delta|node_network_transmit_packets_total_sum_delta|node_network_receive_bytes_total_sum_delta|node_network_receive_packets_total_sum_delta|node_network_receive_multicast_total_sum_delta|node_rapl_package_joules_total_sum_delta|node_network_receive_drop_total_sum_delta|timestamp_seconds_delta|node_rapl_package_power_sum|gpu_node|slurm_id|submit_date        |start_date         |end_date           |nodetypes|numnodes|numcores|state  |slurm_nodes|\n",
      "+-------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------+-------------------+-------------------+-------------------+---------+--------+--------+-------+-----------+\n",
      "|7247237|2022-07-04 09:36:00|r10n1|1.65693E9        |0.01       |36.0            |1.0|4.44149E10              |3.0                    |5.00418E11                 |0.03      |0.0       |1.48482E9               |7.77034E7                   |0.0                      |28672.0                |25.0             |3881.0                  |389774.0                    |5.57261E10     |4.00803E10             |3.73555E7               |1.65245E9             |575147.0                     |3084.0                    |0.0               |3937.0                   |9.66585E10               |16.0              |2.00848E8       |24.0                       |25.94                       |27.0                       |1.882518376448E12              |2.30697018E8             |1.49033265206096E14                  |0.0                             |6.820708131328E12                |1.88257206272E12              |27.0                      |27.0                       |27.0                      |1.27323355648E12              |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112403E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7581968E7                         |0.0                      |0.0                       |0.0                      |4.4495787469E10                        |0.0                |1.53721498700109E14                 |4.3807245067E10                       |5.0                                     |0.0                 |139492.16                         |0.0                                |1656920160       |120275.0                                   |64512.0                                |0.0                                 |9.0                                       |908.0                                        |489438.0                                  |919.0                                       |0.0                                           |355.5199999999895                       |0.0                                      |30.0                   |11.850666666666317         |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "|7247238|2022-07-04 09:36:30|r10n1|1.65693E9        |0.04       |92.0            |1.0|4.4415E10               |3.0                    |5.00418E11                 |0.13      |0.4       |1.90806E9               |7.77034E7                   |0.0                      |20480.0                |24.0             |3884.0                  |389789.0                    |5.57262E10     |4.00803E10             |3.73555E7               |1.65245E9             |575168.0                     |3084.0                    |0.0               |3940.0                   |9.60294E10               |7.0               |2.00848E8       |29.0                       |30.35                       |35.0                       |1.88251797504E12               |2.30697018E8             |1.490332688947E14                    |0.0                             |6.820708657664E12                |1.882571661312E12             |36.0                      |36.0                       |36.0                      |1.273237013504E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112386E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7581984E7                         |0.0                      |0.0                       |0.0                      |4.44958015E10                          |0.0                |1.5372171330995E14                  |4.3807279219E10                       |5.0                                     |0.0                 |140898.18                         |0.0                                |1656920190       |3688604.0                                  |526336.0                               |3457024.0                           |16.0                                      |14031.0                                      |2.14609841E8                              |34152.0                                     |0.0                                           |1406.0199999999895                      |0.0                                      |30.0                   |46.86733333333299          |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "|7247239|2022-07-04 09:37:00|r10n1|1.65693E9        |0.07       |92.0            |1.0|4.4415E10               |3.0                    |5.00418E11                 |0.21      |0.64      |2.01654E9               |7.77034E7                   |0.0                      |319488.0               |24.0             |3884.0                  |389811.0                    |5.57262E10     |4.00803E10             |3.73555E7               |1.65245E9             |575190.0                     |3084.0                    |0.0               |3940.0                   |9.59138E10               |18.0              |2.00848E8       |28.0                       |29.35                       |31.0                       |1.882517716992E12              |2.30697018E8             |1.49033274931836E14                  |0.0                             |6.820711450624E12                |1.882571403264E12             |31.0                      |31.0                       |31.0                      |1.273237013504E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112386E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582127E7                         |0.0                      |0.0                       |0.0                      |4.4495820191E10                        |0.0                |1.53721737819803E14                 |4.380729964E10                        |5.0                                     |0.0                 |142128.92                         |0.0                                |1656920220       |6037136.0                                  |2792960.0                              |0.0                                 |143.0                                     |18691.0                                      |2.4509853E7                               |20421.0                                     |0.0                                           |1230.7400000000198                      |0.0                                      |30.0                   |41.02466666666733          |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "|7247240|2022-07-04 09:37:30|r10n1|1.65693E9        |0.1        |92.0            |1.0|4.4415E10               |3.0                    |5.00418E11                 |0.29      |0.78      |7.69547E9               |7.77034E7                   |0.0                      |385024.0               |24.0             |3884.0                  |389815.0                    |5.57263E10     |4.00804E10             |3.73555E7               |1.65245E9             |575194.0                     |3084.0                    |0.0               |3940.0                   |8.94145E10               |18.0              |2.00848E8       |30.0                       |32.65                       |43.0                       |1.882517069824E12              |2.30697018E8             |1.49033547689061E14                  |0.0                             |6.820711699968E12                |1.882570756096E12             |47.0                      |47.0                       |47.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112364E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.758214E7                          |0.0                      |0.0                       |0.0                      |4.4495849466E10                        |0.0                |1.53722812316861E14                 |4.3807409073E10                       |5.0                                     |0.0                 |143521.57                         |0.0                                |1656920250       |2.72757225E8                               |249344.0                               |503808.0                            |13.0                                      |29275.0                                      |1.074497058E9                             |109433.0                                    |0.0                                           |1392.6499999999942                      |0.0                                      |30.0                   |46.421666666666475         |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "|7247241|2022-07-04 09:38:00|r10n1|1.65693E9        |0.32       |92.0            |1.0|4.44151E10              |3.0                    |5.00418E11                 |0.93      |3.52      |1.74739E10              |7.77034E7                   |0.0                      |20480.0                |24.0             |3884.0                  |389816.0                    |5.57264E10     |4.00805E10             |3.73555E7               |1.65245E9             |575195.0                     |3084.0                    |0.0               |3940.0                   |7.96437E10               |44.0              |2.00849E8       |35.0                       |36.94                       |38.0                       |1.882517356544E12              |2.30697018E8             |1.49039008555263E14                  |0.0                             |6.820712170496E12                |1.882571042816E12             |38.0                      |38.0                       |38.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.30112364E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582154E7                         |0.0                      |0.0                       |0.0                      |4.4495959415E10                        |0.0                |1.53728275518616E14                 |4.3807519084E10                       |5.0                                     |0.0                 |145379.41                         |0.0                                |1656920280       |5.460866202E9                              |470528.0                               |0.0                                 |14.0                                      |109949.0                                     |5.463201755E9                             |110011.0                                    |0.0                                           |1857.8399999999965                      |0.0                                      |30.0                   |61.927999999999884         |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "|7247242|2022-07-04 09:38:30|r10n1|1.65693E9        |1.21       |92.0            |1.0|4.44151E10              |3.0                    |5.00421E11                 |3.54      |13.05     |1.75257E10              |7.77034E7                   |0.0                      |8192.0                 |24.0             |3884.0                  |389816.0                    |5.57265E10     |4.00805E10             |3.73555E7               |1.65245E9             |575195.0                     |3084.0                    |0.0               |3940.0                   |8.03395E10               |37.0              |2.00852E8       |38.0                       |38.82                       |40.0                       |1.88251736064E12               |2.30697018E8             |1.49039008925704E14                  |0.0                             |6.820712352256E12                |1.882571046912E12             |40.0                      |40.0                       |40.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582172E7                         |0.0                      |0.0                       |0.0                      |4.4495960397E10                        |0.0                |1.53728275768021E14                 |4.3807519828E10                       |5.0                                     |0.0                 |147683.42                         |0.0                                |1656920310       |370441.0                                   |181760.0                               |0.0                                 |18.0                                      |982.0                                        |249405.0                                  |744.0                                       |0.0                                           |2304.0100000000093                      |0.0                                      |30.0                   |76.80033333333364          |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "|7247243|2022-07-04 09:39:00|r10n1|1.65693E9        |2.16       |92.0            |1.0|4.44151E10              |3.0                    |5.00423E11                 |6.18      |20.11     |1.83562E10              |7.77034E7                   |0.0                      |12288.0                |24.0             |3884.0                  |389817.0                    |5.57266E10     |4.00805E10             |3.73555E7               |1.65245E9             |575196.0                     |3084.0                    |0.0               |3940.0                   |7.9513E10                |24.0              |2.00853E8       |37.0                       |38.06                       |39.0                       |1.88251736064E12               |2.30697018E8             |1.49039009056459E14                  |0.0                             |6.820712361984E12                |1.882571046912E12             |39.0                      |39.0                       |39.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582174E7                         |0.0                      |0.0                       |0.0                      |4.4495961003E10                        |0.0                |1.53728275847529E14                 |4.3807520314E10                       |5.0                                     |0.0                 |150013.72                         |0.0                                |1656920340       |130755.0                                   |9728.0                                 |0.0                                 |2.0                                       |606.0                                        |79508.0                                   |486.0                                       |0.0                                           |2330.2999999999884                      |0.0                                      |30.0                   |77.67666666666628          |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "|7247244|2022-07-04 09:39:30|r10n1|1.65693E9        |2.66       |92.0            |1.0|4.44151E10              |3.0                    |5.00423E11                 |7.27      |19.03     |2.16091E10              |7.77034E7                   |0.0                      |24576.0                |24.0             |3885.0                  |389818.0                    |5.57269E10     |4.00805E10             |3.73555E7               |1.65245E9             |575197.0                     |3085.0                    |0.0               |3941.0                   |7.62446E10               |20.0              |2.0088E8        |34.0                       |35.41                       |37.0                       |1.88251736064E12               |2.30697018E8             |1.49039009239008E14                  |0.0                             |6.820712547328E12                |1.882571046912E12             |37.0                      |37.0                       |37.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582195E7                         |0.0                      |0.0                       |0.0                      |4.4495961544E10                        |0.0                |1.53728275967042E14                 |4.3807520792E10                       |5.0                                     |0.0                 |152217.74                         |0.0                                |1656920370       |182549.0                                   |185344.0                               |0.0                                 |21.0                                      |541.0                                        |119513.0                                  |478.0                                       |0.0                                           |2204.0199999999895                      |0.0                                      |30.0                   |73.46733333333299          |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "|7247245|2022-07-04 09:40:00|r10n1|1.65693E9        |3.22       |92.0            |1.0|4.44151E10              |3.0                    |5.00424E11                 |8.54      |19.88     |1.95872E10              |7.77034E7                   |0.0                      |8192.0                 |24.0             |3885.0                  |389818.0                    |5.57271E10     |4.00805E10             |3.73555E7               |1.65245E9             |575197.0                     |3085.0                    |0.0               |3941.0                   |7.82755E10               |32.0              |2.00897E8       |35.0                       |35.82                       |37.0                       |1.88251736064E12               |2.30697018E8             |1.4903900927161E14                   |0.0                             |6.82071259648E12                 |1.882571046912E12             |37.0                      |37.0                       |37.0                      |1.273237517312E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582204E7                         |0.0                      |0.0                       |0.0                      |4.4495961564E10                        |0.0                |1.53728275986423E14                 |4.3807520833E10                       |5.0                                     |0.0                 |154462.48                         |0.0                                |1656920400       |32602.0                                    |49152.0                                |0.0                                 |9.0                                       |20.0                                         |19381.0                                   |41.0                                        |0.0                                           |2244.74000000002                        |0.0                                      |30.0                   |74.82466666666733          |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "|7247246|2022-07-04 09:40:30|r10n1|1.65693E9        |3.99       |128.0           |1.0|4.44151E10              |3.0                    |5.00427E11                 |10.33     |22.67     |1.96501E10              |7.77034E7                   |0.0                      |24576.0                |24.0             |3885.0                  |389818.0                    |5.57272E10     |4.00805E10             |3.73555E7               |1.65245E9             |575203.0                     |3085.0                    |0.0               |3941.0                   |7.82083E10               |33.0              |2.00902E8       |33.0                       |34.65                       |36.0                       |1.882517336064E12              |2.30697018E8             |1.49039009308653E14                  |0.0                             |6.820712673792E12                |1.882571022336E12             |37.0                      |37.0                       |37.0                      |1.273237648384E12             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |342.0               |0.0                            |0.0                             |0.0                            |2.3011237E8                   |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.912249626624E12             |1.7582213E7                         |0.0                      |0.0                       |0.0                      |4.4495961609E10                        |0.0                |1.53728276051126E14                 |4.3807520901E10                       |5.0                                     |0.0                 |156770.8                          |0.0                                |1656920430       |37043.0                                    |77312.0                                |131072.0                            |9.0                                       |45.0                                         |64703.0                                   |68.0                                        |0.0                                           |2308.319999999978                       |0.0                                      |30.0                   |76.94399999999926          |0       |1603441 |2022-07-04 09:35:36|2022-07-04 09:35:36|2022-07-09 03:44:42|normal   |1       |16      |TIMEOUT|[r10n1]    |\n",
      "+-------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------+-------------------+-------------------+-------------------+---------+--------+--------+-------+-----------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_combined_renamed = df_combined.drop('prom_dayofyear', 'slurm_node', 'slurm_dayofyear_start', 'slurm_dayofyear_running', 'dayofyear_end')\n",
    "df_combined_renamed = df_combined_renamed.withColumnRenamed('nodez', 'slurm_nodes')\n",
    "df_combined_renamed = df_combined_renamed.withColumnRenamed('prom_node', 'node')\n",
    "\n",
    "df_combined_renamed.show(10, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df_combined_renamed = df_combined_renamed.orderBy(['node', 'timestamp', 'start_date'])\n",
    "df_combined_renamed.write.parquet(path_job_node_joined_dataset, mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Job Dataset Size: 1596963 x 9 Start Date: 2021-12-26 23:06:31 End Date: 2022-11-01 13:59:18\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+---------+\n",
      "|id   |submit_date        |start_date         |end_date           |node  |nodetypes|numnodes|numcores|state    |\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+---------+\n",
      "|58607|2022-01-14 17:39:50|2022-01-15 06:39:07|2022-01-15 14:30:47|r15n11|normal   |1       |16      |COMPLETED|\n",
      "|58786|2022-01-14 17:39:50|2022-01-15 06:39:07|2022-01-15 16:27:53|r11n8 |normal   |1       |16      |COMPLETED|\n",
      "|58669|2022-01-14 17:39:50|2022-01-15 06:41:37|2022-01-15 15:41:45|r14n23|normal   |1       |16      |COMPLETED|\n",
      "|58548|2022-01-14 17:39:50|2022-01-15 06:42:37|2022-01-15 13:38:40|r12n3 |normal   |1       |16      |COMPLETED|\n",
      "|58848|2022-01-14 17:39:50|2022-01-15 06:46:38|2022-01-15 17:11:13|r25n29|normal   |1       |16      |COMPLETED|\n",
      "+-----+-------------------+-------------------+-------------------+------+---------+--------+--------+---------+\n",
      "only showing top 5 rows\n",
      "\n",
      "Node Dataset Size: 127827719 x 91 Start Date: 2022-06-30 18:00:30 End Date: 2022-11-22 11:20:30\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+\n",
      "|id       |timestamp          |node |node_time_seconds|node_load15|node_power_usage|up |node_netstat_Tcp_OutSegs|node_netstat_Tcp_InErrs|node_context_switches_total|node_load5|node_load1|node_memory_Active_bytes|node_netstat_Tcp_RetransSegs|node_netstat_Udp_InErrors|node_memory_Dirty_bytes|node_ambient_temp|node_netstat_Icmp_InMsgs|node_netstat_Udp_InDatagrams|node_intr_total|node_netstat_Tcp_InSegs|node_memory_Percpu_bytes|node_boot_time_seconds|node_netstat_Udp_OutDatagrams|node_netstat_Icmp_InErrors|node_procs_blocked|node_netstat_Icmp_OutMsgs|node_memory_MemFree_bytes|node_procs_running|node_forks_total|node_hwmon_temp_celsius_min|node_hwmon_temp_celsius_mean|node_hwmon_temp_celsius_max|node_filesystem_avail_bytes_sum|node_filesystem_files_sum|node_network_transmit_bytes_total_sum|node_filesystem_device_error_sum|node_disk_written_bytes_total_sum|node_filesystem_free_bytes_sum|node_thermal_zone_temp_min|node_thermal_zone_temp_mean|node_thermal_zone_temp_max|node_disk_read_bytes_total_sum|nvidia_gpu_memory_used_bytes_sum|nvidia_gpu_temperature_celsius_0|nvidia_gpu_temperature_celsius_1|nvidia_gpu_temperature_celsius_2|nvidia_gpu_temperature_celsius_3|nvidia_gpu_temperature_celsius_min|nvidia_gpu_temperature_celsius_mean|nvidia_gpu_temperature_celsius_max|node_arp_entries_sum|nvidia_gpu_fanspeed_percent_min|nvidia_gpu_fanspeed_percent_mean|nvidia_gpu_fanspeed_percent_max|node_filesystem_files_free_sum|nvidia_gpu_power_usage_milliwatts_0|nvidia_gpu_power_usage_milliwatts_1|nvidia_gpu_power_usage_milliwatts_2|nvidia_gpu_power_usage_milliwatts_3|nvidia_gpu_power_usage_milliwatts_min|nvidia_gpu_power_usage_milliwatts_sum|nvidia_gpu_power_usage_milliwatts_mean|nvidia_gpu_power_usage_milliwatts_max|node_filesystem_size_bytes_sum|node_disk_writes_completed_total_sum|nvidia_gpu_duty_cycle_min|nvidia_gpu_duty_cycle_mean|nvidia_gpu_duty_cycle_max|node_network_transmit_packets_total_sum|node_udp_queues_sum|node_network_receive_bytes_total_sum|node_network_receive_packets_total_sum|node_network_receive_multicast_total_sum|node_disk_io_now_sum|node_rapl_package_joules_total_sum|node_network_receive_drop_total_sum|timestamp_seconds|node_network_transmit_bytes_total_sum_delta|node_disk_written_bytes_total_sum_delta|node_disk_read_bytes_total_sum_delta|node_disk_writes_completed_total_sum_delta|node_network_transmit_packets_total_sum_delta|node_network_receive_bytes_total_sum_delta|node_network_receive_packets_total_sum_delta|node_network_receive_multicast_total_sum_delta|node_rapl_package_joules_total_sum_delta|node_network_receive_drop_total_sum_delta|timestamp_seconds_delta|node_rapl_package_power_sum|gpu_node|\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+\n",
      "|106894969|2022-10-11 04:17:00|r28n3|1.66546E9        |3.98       |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |4.2       |4.16      |2.35916E10              |271230.0                    |0.0                      |16384.0                |25.0             |423.0                   |7272060.0                   |1.41723E10     |1.43194E9              |8.20511E7               |1.66134E9             |7401900.0                    |198.0                     |0.0               |419.0                    |1.57859E11               |4.0               |3.75065E7       |26.0                       |29.19                       |36.0                       |2.564558897152E12              |6.8497933E8              |8.46171547648E12                     |0.0                             |1.705329791488E12                |2.564612583424E12             |31.0                      |33.5                       |36.0                      |3.7710436352E10               |1.5011938304E10                 |59.0                            |32.0                            |35.0                            |34.0                            |32.0                              |40.0                               |59.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435874E8                  |104137.0                           |16758.0                            |27738.0                            |21007.0                            |16758.0                              |169640.0                             |42410.0                               |104137.0                             |2.602202996736E12             |7.3244452E7                         |0.0                      |15.0                      |60.0                     |1.956308731E9                          |0.0                |2.6822646841991E13                  |3.843764456E9                         |0.0                                     |0.0                 |348559.42                         |0.0                                |1665461820       |89727.0                                    |37376.0                                |0.0                                 |8.0                                       |97.0                                         |19008.0                                   |111.0                                       |0.0                                           |2675.9099999999744                      |0.0                                      |30.0                   |89.19699999999915          |1       |\n",
      "|106894970|2022-10-11 04:17:30|r28n3|1.66546E9        |3.89       |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |3.92      |3.04      |2.35326E10              |271230.0                    |0.0                      |16384.0                |25.0             |423.0                   |7272060.0                   |1.41723E10     |1.43197E9              |8.20511E7               |1.66134E9             |7401910.0                    |198.0                     |0.0               |419.0                    |1.56576E11               |22.0              |3.75068E7       |25.0                       |28.23                       |34.0                       |2.564558897152E12              |6.8497933E8              |8.461716147725E12                    |0.0                             |1.705329832448E12                |2.564612583424E12             |30.0                      |31.5                       |33.0                      |3.7710436352E10               |1.5011938304E10                 |56.0                            |32.0                            |35.0                            |35.0                            |32.0                              |39.5                               |56.0                              |229.0               |40.0                           |40.75                           |41.0                           |6.84435874E8                  |75301.0                            |16863.0                            |27634.0                            |21074.0                            |16863.0                              |140872.0                             |35218.0                               |75301.0                              |2.602202996736E12             |7.3244457E7                         |0.0                      |0.0                       |0.0                      |1.956313201E9                          |0.0                |2.682418133668E13                   |3.843935316E9                         |0.0                                     |0.0                 |350707.96                         |0.0                                |1665461850       |671245.0                                   |40960.0                                |0.0                                 |5.0                                       |4470.0                                       |1.534494689E9                             |170860.0                                    |0.0                                           |2148.5400000000373                      |0.0                                      |30.0                   |71.61800000000125          |1       |\n",
      "|106894971|2022-10-11 04:18:00|r28n3|1.66546E9        |3.8        |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |3.64      |2.23      |2.358E10                |271230.0                    |0.0                      |8192.0                 |25.0             |423.0                   |7272060.0                   |1.41724E10     |1.43197E9              |8.20511E7               |1.66134E9             |7401910.0                    |198.0                     |0.0               |419.0                    |1.71775E11               |18.0              |3.75068E7       |26.0                       |28.08                       |33.0                       |2.564558897152E12              |6.8497933E8              |8.461716223075E12                    |0.0                             |1.705329928704E12                |2.564612583424E12             |30.0                      |31.5                       |33.0                      |3.7710436352E10               |1.5011938304E10                 |54.0                            |32.0                            |35.0                            |34.0                            |32.0                              |38.75                              |54.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435874E8                  |74283.0                            |16835.0                            |27373.0                            |21099.0                            |16835.0                              |139590.0                             |34897.5                               |74283.0                              |2.602202996736E12             |7.3244472E7                         |0.0                      |1.5                       |6.0                      |1.956313281E9                          |0.0                |2.6824181349974E13                  |3.843935417E9                         |0.0                                     |0.0                 |352836.64                         |0.0                                |1665461880       |75350.0                                    |96256.0                                |0.0                                 |15.0                                      |80.0                                         |13294.0                                   |101.0                                       |0.0                                           |2128.679999999993                       |0.0                                      |30.0                   |70.95599999999976          |1       |\n",
      "|106894972|2022-10-11 04:18:30|r28n3|1.66546E9        |3.71       |672.0           |1.0|1.94794E9               |0.0                    |1.05184E12                 |3.39      |1.75      |2.35799E10              |271230.0                    |0.0                      |16384.0                |25.0             |423.0                   |7272060.0                   |1.41724E10     |1.43197E9              |8.20511E7               |1.66134E9             |7401910.0                    |198.0                     |0.0               |419.0                    |1.71782E11               |17.0              |3.75068E7       |25.0                       |27.92                       |32.0                       |2.564558897152E12              |6.8497933E8              |8.461716278853E12                    |0.0                             |1.705330016768E12                |2.564612583424E12             |30.0                      |32.0                       |34.0                      |3.7710436352E10               |1.5011938304E10                 |52.0                            |32.0                            |35.0                            |34.0                            |32.0                              |38.25                              |52.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435874E8                  |73393.0                            |16807.0                            |27234.0                            |21013.0                            |16807.0                              |138447.0                             |34611.75                              |73393.0                              |2.602202996736E12             |7.3244481E7                         |0.0                      |0.0                       |0.0                      |1.956313328E9                          |0.0                |2.682418135776E13                   |3.843935496E9                         |0.0                                     |0.0                 |354952.28                         |0.0                                |1665461910       |55778.0                                    |88064.0                                |0.0                                 |9.0                                       |47.0                                         |7786.0                                    |79.0                                        |0.0                                           |2115.640000000014                       |0.0                                      |30.0                   |70.5213333333338           |1       |\n",
      "|106894973|2022-10-11 04:19:00|r28n3|1.66546E9        |3.86       |672.0           |1.0|1.94796E9               |0.0                    |1.05184E12                 |3.88      |4.49      |3.73465E10              |271230.0                    |0.0                      |8380420.0              |25.0             |423.0                   |7272070.0                   |1.41726E10     |1.43213E9              |8.20511E7               |1.66134E9             |7401920.0                    |198.0                     |0.0               |419.0                    |1.50142E11               |24.0              |3.75074E7       |26.0                       |30.69                       |36.0                       |2.564558811136E12              |6.8497933E8              |8.461719749204E12                    |0.0                             |1.705351074816E12                |2.564612497408E12             |35.0                      |35.5                       |36.0                      |3.7710436352E10               |1.5011938304E10                 |51.0                            |32.0                            |35.0                            |34.0                            |32.0                              |38.0                               |51.0                              |229.0               |41.0                           |41.0                            |41.0                           |6.84435865E8                  |73411.0                            |16632.0                            |27784.0                            |20959.0                            |16632.0                              |138786.0                             |34696.5                               |73411.0                              |2.602202996736E12             |7.3245375E7                         |0.0                      |2.25                      |9.0                      |1.956334291E9                          |0.0                |2.6831838806489E13                  |3.844791768E9                         |0.0                                     |0.0                 |357469.73                         |0.0                                |1665461940       |3470351.0                                  |2.1058048E7                            |0.0                                 |894.0                                     |20963.0                                      |7.657448729E9                             |856272.0                                    |0.0                                           |2517.4499999999534                      |0.0                                      |30.0                   |83.91499999999844          |1       |\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+\n",
      "only showing top 5 rows\n",
      "\n",
      "Job-Node Dataset Size: 93950875 x 100 Start Date: 2022-06-30 18:01:30 End Date: 2022-11-01 13:59:00\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------+-------------------+-------------------+-------------------+---------+--------+--------+-------+-----------+\n",
      "|prom_id  |timestamp          |node |node_time_seconds|node_load15|node_power_usage|up |node_netstat_Tcp_OutSegs|node_netstat_Tcp_InErrs|node_context_switches_total|node_load5|node_load1|node_memory_Active_bytes|node_netstat_Tcp_RetransSegs|node_netstat_Udp_InErrors|node_memory_Dirty_bytes|node_ambient_temp|node_netstat_Icmp_InMsgs|node_netstat_Udp_InDatagrams|node_intr_total|node_netstat_Tcp_InSegs|node_memory_Percpu_bytes|node_boot_time_seconds|node_netstat_Udp_OutDatagrams|node_netstat_Icmp_InErrors|node_procs_blocked|node_netstat_Icmp_OutMsgs|node_memory_MemFree_bytes|node_procs_running|node_forks_total|node_hwmon_temp_celsius_min|node_hwmon_temp_celsius_mean|node_hwmon_temp_celsius_max|node_filesystem_avail_bytes_sum|node_filesystem_files_sum|node_network_transmit_bytes_total_sum|node_filesystem_device_error_sum|node_disk_written_bytes_total_sum|node_filesystem_free_bytes_sum|node_thermal_zone_temp_min|node_thermal_zone_temp_mean|node_thermal_zone_temp_max|node_disk_read_bytes_total_sum|nvidia_gpu_memory_used_bytes_sum|nvidia_gpu_temperature_celsius_0|nvidia_gpu_temperature_celsius_1|nvidia_gpu_temperature_celsius_2|nvidia_gpu_temperature_celsius_3|nvidia_gpu_temperature_celsius_min|nvidia_gpu_temperature_celsius_mean|nvidia_gpu_temperature_celsius_max|node_arp_entries_sum|nvidia_gpu_fanspeed_percent_min|nvidia_gpu_fanspeed_percent_mean|nvidia_gpu_fanspeed_percent_max|node_filesystem_files_free_sum|nvidia_gpu_power_usage_milliwatts_0|nvidia_gpu_power_usage_milliwatts_1|nvidia_gpu_power_usage_milliwatts_2|nvidia_gpu_power_usage_milliwatts_3|nvidia_gpu_power_usage_milliwatts_min|nvidia_gpu_power_usage_milliwatts_sum|nvidia_gpu_power_usage_milliwatts_mean|nvidia_gpu_power_usage_milliwatts_max|node_filesystem_size_bytes_sum|node_disk_writes_completed_total_sum|nvidia_gpu_duty_cycle_min|nvidia_gpu_duty_cycle_mean|nvidia_gpu_duty_cycle_max|node_network_transmit_packets_total_sum|node_udp_queues_sum|node_network_receive_bytes_total_sum|node_network_receive_packets_total_sum|node_network_receive_multicast_total_sum|node_disk_io_now_sum|node_rapl_package_joules_total_sum|node_network_receive_drop_total_sum|timestamp_seconds|node_network_transmit_bytes_total_sum_delta|node_disk_written_bytes_total_sum_delta|node_disk_read_bytes_total_sum_delta|node_disk_writes_completed_total_sum_delta|node_network_transmit_packets_total_sum_delta|node_network_receive_bytes_total_sum_delta|node_network_receive_packets_total_sum_delta|node_network_receive_multicast_total_sum_delta|node_rapl_package_joules_total_sum_delta|node_network_receive_drop_total_sum_delta|timestamp_seconds_delta|node_rapl_package_power_sum|gpu_node|slurm_id|submit_date        |start_date         |end_date           |nodetypes|numnodes|numcores|state  |slurm_nodes|\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------+-------------------+-------------------+-------------------+---------+--------+--------+-------+-----------+\n",
      "|125386868|2022-10-30 09:32:30|r25n4|1.66712E9        |16.06      |176.0           |1.0|4.32769E9               |0.0                    |2.67928E10                 |16.05     |16.0      |2.00029E9               |1175070.0                   |0.0                      |12288.0                |25.0             |0.0                     |234347.0                    |8.77586E9      |4.64425E9              |1.09445E7               |1.66635E9             |258538.0                     |0.0                       |0.0               |1.0                      |9.62139E10               |26.0              |1682100.0       |50.0                       |53.33                       |57.0                       |1.963307532288E12              |2.38511102E8             |1.0253696029202E13                   |0.0                             |7.37327323136E11                 |1.96336121856E12              |51.0                      |54.0                       |57.0                      |1.302772736E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |229.0               |0.0                            |0.0                             |0.0                            |2.37951035E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.992225050624E12             |2256747.0                           |0.0                      |0.0                       |0.0                      |4.329223443E9                          |0.0                |1.0405040591706E13                  |4.881443618E9                         |1.0                                     |0.0                 |176445.24                         |0.0                                |1667122350       |35987.0                                    |30208.0                                |0.0                                 |4.0                                       |55.0                                         |10072.0                                   |89.0                                        |0.0                                           |3514.4899999999907                      |0.0                                      |30.0                   |117.14966666666636         |0       |2316366 |2022-10-25 18:45:47|2022-10-25 18:49:53|2022-10-30 18:50:14|normal   |1       |16      |TIMEOUT|[r25n4]    |\n",
      "|125386869|2022-10-30 09:33:00|r25n4|1.66712E9        |16.06      |176.0           |1.0|4.32769E9               |0.0                    |2.67928E10                 |16.05     |16.0      |1.99542E9               |1175070.0                   |0.0                      |24576.0                |25.0             |0.0                     |234365.0                    |8.77599E9      |4.64425E9              |1.09445E7               |1.66635E9             |258557.0                     |0.0                       |0.0               |1.0                      |9.6216E10                |20.0              |1682150.0       |50.0                       |53.39                       |57.0                       |1.963307532288E12              |2.38511102E8             |1.0253696509649E13                   |0.0                             |7.373273344E11                   |1.96336121856E12              |51.0                      |54.0                       |57.0                      |1.302772736E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |229.0               |0.0                            |0.0                             |0.0                            |2.37951036E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.992225050624E12             |2256750.0                           |0.0                      |0.0                       |0.0                      |4.329223551E9                          |0.0                |1.040504060351E13                   |4.881443726E9                         |1.0                                     |0.0                 |179957.46                         |0.0                                |1667122380       |480447.0                                   |11264.0                                |0.0                                 |3.0                                       |108.0                                        |11804.0                                   |108.0                                       |0.0                                           |3512.220000000001                       |0.0                                      |30.0                   |117.07400000000004         |0       |2316366 |2022-10-25 18:45:47|2022-10-25 18:49:53|2022-10-30 18:50:14|normal   |1       |16      |TIMEOUT|[r25n4]    |\n",
      "|125386870|2022-10-30 09:33:30|r25n4|1.66712E9        |16.06      |176.0           |1.0|4.32769E9               |0.0                    |2.67928E10                 |16.04     |16.0      |1.99506E9               |1175070.0                   |0.0                      |8192.0                 |25.0             |0.0                     |234383.0                    |8.77611E9      |4.64425E9              |1.09445E7               |1.66635E9             |258575.0                     |0.0                       |0.0               |1.0                      |9.62162E10               |26.0              |1682180.0       |49.0                       |53.33                       |57.0                       |1.963307532288E12              |2.38511102E8             |1.0253696543688E13                   |0.0                             |7.37327414272E11                 |1.96336121856E12              |51.0                      |54.0                       |57.0                      |1.302772736E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |229.0               |0.0                            |0.0                             |0.0                            |2.37951036E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.992225050624E12             |2256759.0                           |0.0                      |0.0                       |0.0                      |4.329223592E9                          |0.0                |1.0405040608628E13                  |4.881443796E9                         |1.0                                     |0.0                 |183470.06                         |0.0                                |1667122410       |34039.0                                    |79872.0                                |0.0                                 |9.0                                       |41.0                                         |5118.0                                    |70.0                                        |0.0                                           |3512.600000000006                       |0.0                                      |30.0                   |117.08666666666686         |0       |2316366 |2022-10-25 18:45:47|2022-10-25 18:49:53|2022-10-30 18:50:14|normal   |1       |16      |TIMEOUT|[r25n4]    |\n",
      "|125386871|2022-10-30 09:34:00|r25n4|1.66712E9        |16.05      |176.0           |1.0|4.32769E9               |0.0                    |2.67928E10                 |16.03     |16.0      |1.9965E9                |1175070.0                   |0.0                      |12288.0                |25.0             |0.0                     |234401.0                    |8.77623E9      |4.64425E9              |1.09445E7               |1.66635E9             |258593.0                     |0.0                       |0.0               |1.0                      |9.6215E10                |25.0              |1682220.0       |49.0                       |53.33                       |57.0                       |1.963307532288E12              |2.38511102E8             |1.0253698302107E13                   |0.0                             |7.37327432704E11                 |1.96336121856E12              |52.0                      |54.5                       |57.0                      |1.302772736E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |229.0               |0.0                            |0.0                             |0.0                            |2.37951035E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.992225050624E12             |2256762.0                           |0.0                      |0.0                       |0.0                      |4.329223885E9                          |0.0                |1.0405040634076E13                  |4.881443954E9                         |1.0                                     |0.0                 |186980.38                         |0.0                                |1667122440       |1758419.0                                  |18432.0                                |0.0                                 |3.0                                       |293.0                                        |25448.0                                   |158.0                                       |0.0                                           |3510.320000000007                       |0.0                                      |30.0                   |117.01066666666689         |0       |2316366 |2022-10-25 18:45:47|2022-10-25 18:49:53|2022-10-30 18:50:14|normal   |1       |16      |TIMEOUT|[r25n4]    |\n",
      "|125386872|2022-10-30 09:34:30|r25n4|1.66712E9        |16.09      |176.0           |1.0|4.3277E9                |0.0                    |2.67928E10                 |16.13     |16.39     |1.99282E9               |1175070.0                   |0.0                      |7753730.0              |25.0             |0.0                     |234410.0                    |8.77638E9      |4.64426E9              |1.09445E7               |1.66635E9             |258602.0                     |0.0                       |0.0               |1.0                      |9.62175E10               |21.0              |1682700.0       |50.0                       |53.5                        |57.0                       |1.963307470848E12              |2.38511102E8             |1.0253700088106E13                   |0.0                             |7.37364604928E11                 |1.96336115712E12              |51.0                      |54.0                       |57.0                      |1.302772736E9                 |0.0                             |0.0                             |0.0                             |0.0                             |0.0                             |0.0                               |0.0                                |0.0                               |229.0               |0.0                            |0.0                             |0.0                            |2.37951036E8                  |0.0                                |0.0                                |0.0                                |0.0                                |0.0                                  |0.0                                  |0.0                                   |0.0                                  |1.992225050624E12             |2257681.0                           |0.0                      |0.0                       |0.0                      |4.329235199E9                          |0.0                |1.0405042366638E13                  |4.881455202E9                         |1.0                                     |0.0                 |190493.28                         |0.0                                |1667122470       |1785999.0                                  |3.7172224E7                            |0.0                                 |919.0                                     |11314.0                                      |1732562.0                                 |11248.0                                     |0.0                                           |3512.899999999994                       |0.0                                      |30.0                   |117.09666666666648         |0       |2316366 |2022-10-25 18:45:47|2022-10-25 18:49:53|2022-10-30 18:50:14|normal   |1       |16      |TIMEOUT|[r25n4]    |\n",
      "+---------+-------------------+-----+-----------------+-----------+----------------+---+------------------------+-----------------------+---------------------------+----------+----------+------------------------+----------------------------+-------------------------+-----------------------+-----------------+------------------------+----------------------------+---------------+-----------------------+------------------------+----------------------+-----------------------------+--------------------------+------------------+-------------------------+-------------------------+------------------+----------------+---------------------------+----------------------------+---------------------------+-------------------------------+-------------------------+-------------------------------------+--------------------------------+---------------------------------+------------------------------+--------------------------+---------------------------+--------------------------+------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------------------------------+-----------------------------------+----------------------------------+--------------------+-------------------------------+--------------------------------+-------------------------------+------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-------------------------------------+-------------------------------------+--------------------------------------+-------------------------------------+------------------------------+------------------------------------+-------------------------+--------------------------+-------------------------+---------------------------------------+-------------------+------------------------------------+--------------------------------------+----------------------------------------+--------------------+----------------------------------+-----------------------------------+-----------------+-------------------------------------------+---------------------------------------+------------------------------------+------------------------------------------+---------------------------------------------+------------------------------------------+--------------------------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------+-----------------------+---------------------------+--------+--------+-------------------+-------------------+-------------------+---------+--------+--------+-------+-----------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_slurm = spark.read.parquet(path_job_dataset)\n",
    "print(\"Job Dataset Size:\", df_slurm.count(), \"x\", len(df_slurm.columns), \"Start Date:\", df_slurm.select(F.min(\"start_date\")).first()[0], \"End Date:\", df_slurm.select(F.max(\"end_date\")).first()[0])\n",
    "df_slurm.show(5, False)\n",
    "\n",
    "df_prom = spark.read.parquet(path_node_dataset)\n",
    "print(\"Node Dataset Size:\", df_prom.count(), \"x\", len(df_prom.columns), \"Start Date:\", df_prom.select(F.min(\"timestamp\")).first()[0], \"End Date:\", df_prom.select(F.max(\"timestamp\")).first()[0])\n",
    "df_prom.show(5, False)\n",
    "\n",
    "df_combined = spark.read.parquet(path_job_node_joined_dataset)\n",
    "print(\"Job-Node Dataset Size:\", df_combined.count(), \"x\", len(df_combined.columns), \"Start Date:\", df_combined.select(F.min(\"timestamp\")).first()[0], \"End Date:\", df_combined.select(F.max(\"timestamp\")).first()[0])\n",
    "df_combined.show(5, False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}