Hi,
I am very new to MySQL/MariaDB, but it happens that I need to work on the data generated from a workload manager SLURM stored in MariaDB.
May I know that is it possible and how to generate the the following table (TRES Allocation Table) from the Job and TRES Tables given below?
Many Thanks!
~Dominic
TRES Allocation Table +--------+----------------------------------------------------------------------------------+
| id_job | cpu | mem | energy | node | billing | gpu | gpu:v100s | gpu:a100 |
+--------+----------------------------------------------------------------------------------+
| 327241 | 128 | 448000| | 1 | 128 | | | |
| 327242 | | | | | | | | |
| 327243 | 4 | 51200 | 18446744073709551614 | 1 | 4 | 1 | | |
| 327244 | 4 | 51200 | 18446744073709551614 | 1 | 4 | 1 | | |
| 327245 | 4 | 51200 | 18446744073709551614 | 1 | 4 | 1 | | |
+--------+----------------------------------------------------------------------------------+
Job Table
+--------+---------------------------------------------------+
| id_job | tres_alloc | +--------+---------------------------------------------------+
| 327241 | 1=128,2=448000,4=1,5=128 |
| 327242 | |
| 327243 | 1=4,2=51200,3=18446744073709551614,4=1,5=4,1001=1 |
| 327244 | 1=4,2=51200,3=18446744073709551614,4=1,5=4,1001=1 |
| 327245 | 1=4,2=51200,3=18446744073709551614,4=1,5=4,1001=1 | +--------+---------------------------------------------------+
TRES Table
+------+---------+-----------+
| id | type | name |
+------+---------+-----------+
| 1 | cpu | |
| 2 | mem | |
| 3 | energy | |
| 4 | node | |
| 5 | billing | |
| 6 | fs | disk |
| 7 | vmem | |
| 8 | pages | |
| 1001 | gres | gpu |
| 1002 | gres | gpu:v100s |
| 1003 | gres | gpu:a100 |
+------+---------+-----------+
You probably could do it, but it wouldn't be easy.
But your real problem here is how your data is stored. It does not
follow first normal form - it has multiple values in a single column.
Better would be to parse the output of your workload manager or
otherwise see if you can change the format. What you have will cause
you no end of problems.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 297 |
Nodes: | 16 (0 / 16) |
Uptime: | 124:26:23 |
Calls: | 6,662 |
Files: | 12,212 |
Messages: | 5,334,763 |