T-SQL Tuesday #127: Non SQL Tips and tricks

T-SQL Tuesday

Last time I have joined the T-SQL Tuesday was about life hacks, and this month thanks to Kenneth Fisher’s (blog|twitter) invitation I will show you something I have used recently.

While ago I had to draw some diagrams. At that time I did not have MS Visio available. Additionally, I wanted to use something I can easily update and store in a repository – like a code-based solution.

When working on some of the graphs, I found Graphviz and would like to share this with you today.

What is Graphviz?

I came across Graphviz which is an open-source graph visualization software initiated by AT&T Labs Research. It can process the graphs that are written in the DOT language.

What is the DOT language?

In short, it is a graph description language that has few keywords like graph, digraph, node, edge. You cannot miss it has something to do with graphs. Below is an example of a simple graph:

/* Example of a simple graph */

graph MyGraph{
  "Everybody" -- "Loves"
  "Loves" -- "Graphs"
  "Loves" -- "GraphViz!"
}

Or a directed graph (note the keyword digraph):

// directed graph below
// note duplicated line and two edges
digraph MyDirectedGraph{
  "Everybody" -> "Loves"
  "Loves" -> "Graphs"
  "Loves" -> "Graphs"
  "GraphViz" ->  "Loves"
}
Graphviz - simple directed graph

When we use keyword strict will remove extra edges:

strict digraph MyDirectedGraph{
  "Everybody" -> "Loves"
  "Loves" -> "Graphs"
  "Loves" -> "Graphs"
  "GraphViz" ->  "Loves"
}
Graphviz - simple strict directed graph

Nodes and edges come in different shapes too:

digraph MyShapes {
  Diamond [shape=diamond]
  Box [shape=box]
  Circle [shape=circle]

  Diamond -> Box [style=dashed, color=grey]
  Diamond -> Circle [color="black:invis:black"]
  Diamond -> Regular [penwidth=5, arrowhead=none]
 }

As you can see the names of the nodes are their labels too, but this can be changed by adding custom labels with extra formatting.

digraph Labels {
  node [shape=record fontname=Arial];
  a  [label="one\ltwo three\lfour five six seven\l"]
  b  [label="one\ntwo three\nfour five six seven"]
  c  [label="one\rtwo three\rfour five six seven\r"]

  a -> b -> c
}

If one graph is not enough and you would like to use some more complex structure Graphviz comes with clusters. For that use a keyword subgraph and name your subgraphs starting with cluster:

digraph G {

  subgraph cluster_0 {
    style=filled;
    color=lightgrey;
    label = "process #1";
    node [style=filled,color=white];
    a0 -> a1 -> a2 -> a3;
}
  subgraph cluster_1 {
    node [style=filled];
    b0 -> b1 -> b2 -> b3;
    label = "process #2";
    color=blue
}
    start -> a0, b0;
    a1 -> b3;
    b2 -> a3 -> a0;
    a3, b3 -> end;
    start [shape=Mdiamond];
    end [shape=Msquare];
 }
Graphviz - simple strict directed graph

Possibilities are practically limitless. Check Graphviz gallery out for more examples.

Multiple engines

DOT supports multiple layouts that are depending on the type of your graph. Here is the list:

  • dot – “hierarchical” or layered drawings of directed graphs. This is the default tool to use if edges have directionality.
  • neato – “spring model” layouts.  This is the default tool to use if the graph is not too large (about 100 nodes) and you don’t know anything else about it. Neato attempts to minimize a global energy function, which is equivalent to statistical multi-dimensional scaling.
  • fdp – “spring model” layouts similar to those of neato, but does this by reducing forces rather than working with energy.
  • sfdp – multiscale version of fdp for the layout of large graphs.
  • twopi – radial layouts, after Graham Wills 97. Nodes are placed on concentric circles depending their distance from a given root node.
  • circo – circular layout, after Six and Tollis 99, Kauffman and Wiese 02. This is suitable for certain diagrams of multiple cyclic structures, such as certain telecommunications networks.

More about that can be found in the Documentation > Layout manual pages.

Where to graph?

Now you have an idea what Graphviz and DOT are and you would like to start creating your graphs. Below I am sharing a few ways to do so.

Online graph editors

There is a number of online editors. I am picking one to demonstrate how easy it can be to create graphs and share the output. A simple and quick editor can be found here https://dreampuf.github.io/GraphvizOnline. There is syntax highlighting, as well as an easy way to pick up the graph engine and format to save the output. If you want you can share the graph with others too. For instance, that’s the link to my graph.

Graphviz in online editor

Visual Studio Code extension

In some scenarios, you might be reluctant to use online services when you are not sure where your graph “travels”. Luckily, there are some offline solutions, like VS Code extension for example.

You can find a handful of extensions for the VS Code. I have used this one and can recommend it as a quick offline tool: joaompinto.vscode-graphviz.

To toggle a Preview use [CTRL] + [SHIFT] + [V] and if you want to open preview to the side use [CTRL] + [K] [V].

The last two buttons at the bottom allow you to [1] save the output as SVG and [2] show the output in the browser.

Graphviz in VS Code

PowerShell cmdlet

There is even a PowerShell module to help you out generate graphs via command line! Kevin Marquette (blog|twitter) created PSGraph that uses Graphviz behind the scenes.

There is good documentation of this module on GitHub, so I encourage you to take a look. Here is how you can install the module (if you are not familiar with the Chocolatey read this blog post:

# Install GraphViz from the Chocolatey repo
Register-PackageSource -Name Chocolatey -ProviderName Chocolatey -Location http://chocolatey.org/api/v2/
Find-Package graphviz | Install-Package -ForceBootstrap

# Install PSGraph from the Powershell Gallery
Find-Module PSGraph | Install-Module

# Import Module
Import-Module PSGraph

First graph from PowerShell:

graph MyPSGraph {
    edge this graph
    edge graph was
    edge was made
    edge graph in
    edge in PowerShell
} | Export-PSGraph -ShowGraph -OutputFormat svg
Graphviz in PSGraph (PowerShell)

Summary

The Graphviz helped me to quickly create some diagrams for documentation and modify it even quicker without a need for heavy software to be installed on my laptop.

The number of way to generate the graphs is also helpful. From online tools to offline add-ins or scripts. There is more to explore in this topic.

Did you use any graphing/diagramming tools? Maybe you used Graphviz too? Please share your experience.

Thank you,
Mikey

1 Comment on "T-SQL Tuesday #127: Non SQL Tips and tricks"


Leave a Reply