CODEWITHSUNDEEP
cluster-computingvertica
GriGrim
GriGrim

Reputation: 2921

How to determine buddy nodes dependencies in Vertica

How can I find in Vertica (Enterprise mode, K-safety 1) node dependencies so that I could build a node graph like this?

enter image description here

The following query:

select n.name, d.dependency_id
from v_internal.VS_NODE_DEPENDENCIES d
inner join v_internal.VS_NODES n on d.node_oid = n.oid
where d.dependency_id < (select count (*) from v_internal.VS_NODES)

Returns this:

name          | dependency_id
--------------+--------------
v_db_node0001 |             0
v_db_node0001 |             1
v_db_node0002 |             0
v_db_node0002 |             2
v_db_node0003 |             1
v_db_node0003 |             2

It's not clear from this resultset which node depends on which.

Upvotes: 0

Views: 31

Answers (1)

marcothesane
marcothesane

Reputation: 6749

One way of doing it could be:

WITH
nodecount(nodecount) AS (
  SELECT COUNT(*) FROM vs_nodes
)
SELECT
  dependency_id
, MIN(REGEXP_SUBSTR(name,'node\d{4}')) AS node_bd0
, MAX(REGEXP_SUBSTR(name,'node\d{4}')) AS node_bd1
, MAX(ref_count)                       AS dep_count
FROM vs_node_dependencies
JOIN vs_nodes ON (node_oid = oid)
CROSS JOIN nodecount
GROUP BY
  dependency_id
, nodecount
HAVING COUNT(*) < nodecount
;
dependency_id node_bd0 node_bd1 dep_count
0 node0001 node0002 23
1 node0001 node0003 23
2 node0002 node0003 23

The disadvantage is that it does not reflect that dependency id 1 is the reverses of dependency id 0, and that therefore node_b0 and node_b1 should be swapped for dependency 1.

But I for one can work with this query.

Edit after some thinking, and some input:

Add a query that returns the same starting node twice, with the higher ending node - swap_this - and use this query as a swapping source for the affected row from the pairings Common Table Expression (CTE). Including intermediate results, hoping they're self-explanatory:

WITH
nodecount(nodecount) AS (
  SELECT COUNT(*) FROM vs_nodes
)
,
pairings AS ( -- this was the original main query
  SELECT
    dependency_id
  , MIN(REGEXP_SUBSTR(name,'node\d{4}')) AS node_bd0
  , MAX(REGEXP_SUBSTR(name,'node\d{4}')) AS node_bd1
  , MAX(ref_count)                       AS dep_count
  FROM vs_node_dependencies 
  JOIN vs_nodes ON (node_oid = oid)
  CROSS JOIN nodecount
  GROUP BY 
    dependency_id
  , nodecount
  HAVING COUNT(*) < nodecount
-- out SELECT * FROM pairings;
-- out  dependency_id | node_bd0 | node_bd1 | dep_count 
-- out ---------------+----------+----------+-----------
-- out              0 | node0001 | node0002 |        38
-- out              2 | node0002 | node0003 |        38
-- out              1 | node0001 | node0003 |        38
)
,
swap_this AS (
  SELECT
    node_bd0
  , MAX(node_bd1) AS node_bd1
  FROM pairings
  GROUP BY 1 HAVING COUNT(*) > 1
-- out SELECT * FROM swap_this;
-- out  node_bd0 | node_bd1 
-- out ----------+----------
-- out  node0001 | node0003
)
SELECT
  p.dependency_id
, COALESCE(swp.node_bd1,p.node_bd0) AS node_bd0
, COALESCE(swp.node_bd0,p.node_bd1) AS node_bd1
, dep_count
FROM pairings p 
LEFT JOIN swap_this swp USING(node_bd0,node_bd1)
ORDER BY node_bd0
;
dependency_id node_bd0 node_bd1 dep_count
0 node0001 node0002 38
2 node0002 node0003 38
1 node0003 node0001 38

Upvotes: 0

Related Questions