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

On 11/23/2022 8:57 PM, SH Chin wrote:
> 
> 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.

-- 
==================
Remove the "x"'s from my email address
Jerry Stuckle
[email protected]
==================