How to transform a field with comma separated string in to fields

[SH Chien]

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 |
+------+---------+-----------+

[Jerry Stuckle]

Post by SH Chien
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.

[SH Chien]

Jerry Stuckle 在 2022年11月25日 星期五凌晨1:43:20 [UTC+8] 的信中寫道：

Post by Jerry Stuckle
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.

Noted with thanks!