From Newsgroup: comp.databases.mysql


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


--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

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
stucklex.jerryx@gmail.com
==================

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

Jerry Stuckle oL? 2022o||11uLe25uuN uyfuLfE|oociuO?1:43:20 [UTC+8] tUaE+iE+!o>2oUoN+U

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!
--- Synchronet 3.21d-Linux NewsLink 1.2