Entity Relationship Diagram from Name Pairs, Redshft

Question

I am handing over a Redshift database with plenty of views. I want to visualize their dependencies.

I made the select below, receiving ~500 rows with first two columns showing names of two related entities.

SELECT
    dependent_view.relname as dependent_entity_name
    , source_table.relname as entity_name
    , source_table.relkind as entity_type
    , count(distinct pg_attribute.attname) as joined_column_count
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid
JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid
    AND pg_depend.refobjsubid = pg_attribute.attnum
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE
source_ns.nspname = 'public'
AND pg_attribute.attnum > 0
GROUP BY 1,2,3
ORDER BY 1,2;

I want

Diagram with
- 1 node per unique entity name
- 1 line connecting two related nodes per row.
To work with the result in Draw.io or similar

I don't want

Draw it manually

I tried

Using Smartdraw - Draws entities without connections
Using VisualParadigm - Crashes
ModelXtractor - permission denied to pg_class_info

Is there any way to achieve this? An existing software tool or a script?

@bruno Is there any way to achieve this? An existing software tool or a script? - Edited it in. — mattved, Aug 19 '20 at 09:50
ok you ask for a tool, this was my question, but sorry I cannot help you for that — bruno, Aug 19 '20 at 09:55
Why did you tag this with UML when you're actually looking for an ERD? — qwerty_so, Aug 19 '20 at 13:10
@qwerty_so Did not realize the first tag is becomes the title tag. Babysteps... — mattved, Aug 19 '20 at 13:50

Christophe · Accepted Answer · 2020-08-19T12:00:21.177

There are certainly ways to generate that. However, the huge number of elements in the graph might make it unreadable and unusable.

My first try would be plantuml. The format is straightforward, and if it would fail, you’d fail fast.

In a more general way this is a classical graph vizualization problem, with nodes and edges. So you could as well use a more specialized graph visualisation algorithm for example using force-directed layout, which self-organizes the nodes, and quickly identifies the "hubs" that are at the center of many related groups. The advantage is that these algorithms are designed to present much more complex graphs than a couple of hundreds relations.

If you do not want to dig into the complexity of a Fruchterman-Reingold algorithm or similar alternatives, you could go for some libraries (you now have some search terms) or even open-source tools such as Gephi.

Entity Relationship Diagram from Name Pairs, Redshft

1 Answers1