cluster computing - How to determine buddy nodes dependencies in Vertica - Stack Overflow

admin管理员组

文章数量:1287533

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

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.

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

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.

• cluster-computing
• vertica

Share Improve this question Follow asked Feb 24 at 17:27 GriGrimGriGrim 2,92111 gold badge2424 silver badges3737 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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

本文标签： cluster computingHow to determine buddy nodes dependencies in VerticaStack Overflow